数据类型

数字类

类型	长度	备注
TINYINT	1字节	有符号整型
SMALLINT	2字节	有符号整型
INT	4字节	有符号整型
BIGINT	8字节	有符号整型
FLOAT	4字节	有符号单精度浮点数
DOUBLE	8字节	有符号双精度浮点数
DECIMAL	–	可带小数的精确数字字符串

日期时间类

类型	长度	备注
TIMESTAMP	–	时间戳，内容格式：yyyy-mm-dd hh:mm:ss[.f…]
DATE	–	日期，内容格式：YYYY­MM­DD
INTERVAL	–	–

字符串类

类型	长度	备注
STRING	–	字符串
VARCHAR	字符数范围1 - 65535	长度不定字符串
CHAR	最大的字符数：255	长度固定字符串

Misc类

类型	长度	备注
BOOLEAN	–	布尔类型 TRUE/FALSE
BINARY	–	字节序列

复合类

类型	长度	备注
ARRAY	–	包含同类型元素的数组，索引从0开始 ARRAY<data_type>
MAP	–	字典 MAP<primitive_type, data_type>
STRUCT	– 结构体 STRUCT<col_name : data_type [COMMENT col_comment], …>
UNIONTYPE	–	联合体 UNIONTYPE<data_type, data_type, …>

复合类测试

创建表

create table union_testnew(foo uniontype<int, double, string, array<string>, map<string, string>>
)
row format delimited
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile;

数据准备

vim union_test.log 
0,1
1,3.0
2,world
3,wade:tom:polly
4,k1^Dv1:k2^Dv2

导入数据

hive> load data local inpath './union_test.log' overwrite into table union_testnew;

查询数据

hive> select * from union_testnew;
OK
union_testnew.foo
{0:1}
{1:3.0}
{2:"world"}
{3:["wade","tom","polly"]}
{4:{"k1":"v1","k2":"v2"}}
Time taken: 0.225 seconds, Fetched: 5 row(s)

Hive 官方文档：LanguageManual Types

2 创建表

所有的操作在 mydb。

CREATE DATABASE IF NOT EXISTS mydb;
use mydb;

2.1 CREATE TABLE

CREATE TABLE IF NOT EXISTS employees (name STRING,salary FLOAT,subordinates ARRAY<STRING>,deductions MAP<STRING, FLOAT>,address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

2.2 CREATE TABLE LIKE

CREATE TABLE IF NOT EXISTS mydb.employee2 LIKE mydb.employees;

SHOW TABLES

hive> SHOW TABLES;
OK
employee2
employees
Time taken: 0.063 seconds, Fetched: 2 row(s)
hive> 

hive> SHOW TABLES IN mydb;
OK
employee2
employees
Time taken: 0.05 seconds, Fetched: 2 row(s)

hive> SHOW TABLES 'empl.*';
OK
employee2
employees
Time taken: 0.049 seconds, Fetched: 2 row(s)

DESC table

DESC

hive> DESC employees;
OK
name                	string              	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    
Time taken: 0.078 seconds, Fetched: 5 row(s)

DESC EXTENDED

hive> DESC EXTENDED employees;
OK
name                	string              	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    Detailed Table Information	Table(tableName:employees, dbName:mydb, owner:houzhizhen, createTime:1635924848, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:salary, type:float, comment:null), FieldSchema(name:subordinates, type:array<string>, comment:null), FieldSchema(name:deductions, type:map<string,float>, comment:null), FieldSchema(name:address, type:struct<street:string,city:string,state:string,zip:int>, comment:null)], location:file:/user/hive/warehouse/mydb.db/employees, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{mapkey.delim=, collection.delim=, serialization.format=, line.delim=\n, field.delim=}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=0, numRows=0, rawDataSize=0, COLUMN_STATS_ACCURATE={\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"address\":\"true\",\"deductions\":\"true\",\"name\":\"true\",\"salary\":\"true\",\"subordinates\":\"true\"}}, numFiles=0, transient_lastDdlTime=1635924848, bucketing_version=2}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false, catName:hive, ownerType:USER)	
Time taken: 0.178 seconds, Fetched: 7 row(s)

DESC FORMATTED

hive> DESC FORMATTED employees;
OK
# col_name            	data_type           	comment             
name                	string              	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    # Detailed Table Information	 	 
Database:           	mydb                	 
OwnerType:          	USER                	 
Owner:              	houzhizhen          	 
CreateTime:         	Wed Nov 03 15:34:08 CST 2021	 
LastAccessTime:     	UNKNOWN             	 
Retention:          	0                   	 
Location:           	file:/user/hive/warehouse/mydb.db/employees	 
Table Type:         	MANAGED_TABLE       	 
Table Parameters:	 	 COLUMN_STATS_ACCURATE	{\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"address\":\"true\",\"deductions\":\"true\",\"name\":\"true\",\"salary\":\"true\",\"subordinates\":\"true\"}}bucketing_version   	2                   numFiles            	0                   numRows             	0                   rawDataSize         	0                   totalSize           	0                   transient_lastDdlTime	1635924848          # Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	 
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Storage Desc Params:	 	 collection.delim    	\u0002              field.delim         	\u0001              line.delim          	\n                  mapkey.delim        	\u0003              serialization.format	\u0001              
Time taken: 0.099 seconds, Fetched: 39 row(s)

Managed Tables and External Tables

Managed Tables

当删除表时，不仅删除对应的元数据，还删除对应的文件。

External Tables

REATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

当删除表时，不删除对应的文件，仅删除对应的元数据。

Partitioned, Managed Tables

CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

SHOW PARTITIONS

hive> SHOW PARTITIONS employees;
OK
country=US/state=AB
country=US/state=AC
country=US/state=CA

DESC PARTITION

hive> DESC employees PARTITION(country='US',state='AB');
OK
name                	string              	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    
country             	string              	                    
state               	string              	                    # Partition Information	 	 
# col_name            	data_type           	comment             
country             	string              	                    
state               	string              	                    
Time taken: 0.162 seconds, Fetched: 12 row(s)

DESC EXTENDED PARTITION

Output has location of partition.

hive> DESC EXTENDED employees PARTITION(country='US',state='AB');
OK
name                	string              	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    
country             	string              	                    
state               	string              	                    # Partition Information	 	 
# col_name            	data_type           	comment             
country             	string              	                    
state               	string              	                    Detailed Partition Information	Partition(values:[US, AB], dbName:mydb, tableName:employees, createTime:1635928495, lastAccessTime:0, sd:StorageDescriptor(cols:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:salary, type:float, comment:null), FieldSchema(name:subordinates, type:array<string>, comment:null), FieldSchema(name:deductions, type:map<string,float>, comment:null), FieldSchema(name:address, type:struct<street:string,city:string,state:string,zip:int>, comment:null), FieldSchema(name:country, type:string, comment:null), FieldSchema(name:state, type:string, comment:null)], location:file:/user/hive/warehouse/mydb.db/employees/country=US/state=AB, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), parameters:{transient_lastDdlTime=1635928495, totalSize=1, numRows=0, rawDataSize=0, numFiles=1}, catName:hive)	
Time taken: 0.192 seconds, Fetched: 14 row(s)

ALTER TABLE

PARTITION

ADD PARTITION

ALTER TABLE employees ADD IF NOT EXISTS PARTITION(country='US',state='AD') LOCATION 'file:/user/hive/warehouse/mydb.db/employees/country=US/state=AD';

SET LOCATION OF PARTITION

ALTER TABLE employees PARTITION(country='US',state='AB') SET LOCATION 'file:/user/hive/warehouse/mydb.db/employees/country=US/state=AC';

DROP PARTITION

ALTER TABLE employees DROP IF EXISTS PARTITION(country='US',state='AB');

CHANGE COLUMNS

hive> DESC employees;
OK
name                	string              	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    
country             	string              	                    
state               	string              	                    # Partition Information	 	 
# col_name            	data_type           	comment             
country             	string              	                    
state               	string              	                    
Time taken: 0.069 seconds, Fetched: 12 row(s)

CHANGE COLUMN NAME AND TYPE

ALTER TABLE employees CHANGE COLUMN name full_name VARCHAR(255);

hive> DESC employees;
OK
full_name           	varchar(255)        	                    
salary              	float               	                    
subordinates        	array<string>       	                    
deductions          	map<string,float>   	                    
address             	struct<street:string,city:string,state:string,zip:int>	                    
country             	string              	                    
state               	string              	                    # Partition Information	 	 
# col_name            	data_type           	comment             
country             	string              	                    
state               	string              	                    
Time taken: 0.056 seconds, Fetched: 12 row(s)

CHANGE COLUMN POSITION

hive> ALTER TABLE employees CHANGE COLUMN salary salary VARCHAR(255);
OK
Time taken: 0.135 seconds

SET name after salary

hive> ALTER TABLE employees CHANGE COLUMN full_name name String COMMENT 'comment'  AFTER salary;
OK
Time taken: 0.119 seconds

SET name first

hive> ALTER TABLE employees CHANGE COLUMN name name String COMMENT 'comment'  FIRST;
OK
Time taken: 0.119 seconds

Adding columns

Add new columns to the end of the existing columns.

ALTER TABLE employees ADD COLUMNS (age INT,sex boolean);

Deleting or Replacing Columns

Removes all the existing columns and replaces them with the new columns specified.

ALTER TABLE employees REPLACE COLUMNS (
`name` string COMMENT 'comment', `salary` varchar(255), `subordinates1` array<string>, `deductions` map<string,float>, `address` struct<street:string,city:string,state:string,zip:int>, `age` int, `sex` boolean,married boolean);

如果因为格式对不上报错，只能先删除，再重建。

Alter Table Properties

ALTER TABLE employees SET TBLPROPERTIES(
'notes'=' The employees of the whole company');

Alter Storage Properties

hive> show partitions employees;
OK
country=US/state=AC
country=US/state=AD
country=US/state=CA

Set File Format

ALTER TABLE employees PARTITION(country='US',state='AC') SET FILEFORMAT SEQUENCEFILE;

SET SERDE

ALTER TABLE table_name SET SERDE 'com.example.JSONSerDe' 
WITH SERDEPROPERTIES('prop1' = 'value1','prop2' = 'value2',
)

SET Serde Properties

ALTER TABLE table_name
SET SERDEPROPERTIES('prop1' = 'value1','prop2' = 'value2',
)

Cluster by, Sorted by, Buckets

ALTER TABLE employees CLUSTERED BY (name)
SORTED BY (age)
INTO 48 BUCKETS;

TOUCH

ALTER TABLE ... TOUCH statement is used to trigger these hooks.

ALTER TABLE employees TOUCH PARTITION(country='US',state='AC');

NODROP (Disabled in Hive 3)

ALTER TABLE employees PARTITION(country='US',state='AC') ENABLE NO_DROP;

OFFLINE (Disabled in Hive 3)

ALTER TABLE employees PARTITION(country='US',state='AC') ENABLE OFFLINE;

ARCHIVE and UNCHARIVE (Disabled in Hive 3)

ARCHIVE captures the partition files into a Hadoop archive(HAR) file.

ALTER TABLE employees ARCHIVE PARTITION(country='US',state='AC');