Hive建表语法和参数记录_hive建表语句参数

Hive是一个基于Hadoop的数据仓库工具，可以将结构化数据映射到HDFS存储（建表对应在HDFS建了一个文件夹），并提供类SQL查询语言-HiveQL，Hive可以将HQL语句转换为MR任务执行。
本文记录Hive建表的常用语法和参数。

建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name (
[col_name data_type [COMMENT col_comment], ...]
)
[COMMENT table_comment]
[PARTITIONED BY(col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[ROW FORMAT DELIMITED FIELDS TERMINATED BY row_format] 
[STORED AS file_format]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)]
1
2
3
4
5
6
7
8
9
10

关键词含义

external

external关键字可以制定创建外部表。Hive建表默认创建内部表，保存表的结构（元数据）和表数据，相应删除时同时删除二者；创建外部表只管理表结构，可以使用location关键字指定已经存在的文件。

location

location /path/fie_name指定表在HDFS中的路径

数据类型

• 数值型：
  tinyint,smallint,int,bigint,float,double,decimal(m,n),numeric
• 字符型：
  string,varchar,char
• 日期时间型：
  timestamp,date,interval
• 布尔型：
  boolean
• 复合数据类型：
  array,map,struct

数据类型实例

create table dbname.tableName(
id int,
field_a bigint,
field_b float,
field_c double,
field_d decimal(10,3),
field_e boolean,
field_f string,
field_g timestamp,
field_h date,
field_i array<string>,
field_j map<int,string>,
field_k struct<name:string, age:int>
)
row format delimited 
fields terminated by ',' -- 列之间用','分割
collection items terminated by '-'   -- 集合之间用'-'分割
map keys terminated by ':'     -- 键值之间用':'分割

-- 查询
select 
	id,
	field_a,
	field_b,
	field_c,
	field_d,
	field_e,
	field_f,
	field_g,
	field_h,
	field_i[0], -- array格式类似Python列表，使用从0开始的位置号选择数据
	field_i[1],
	field_j[<int1>], -- key格式类似Python字典，使用键名称选择值数据
	field_k.name -- struct格式使用.选择数据
from
	dbname.tableName
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

row format

row format关键字用于指定行的格式。

• row format delimited只能使用单字符分隔符处理一般格式文件。

row format delimited 
	fields terminated by ',' -- 列之间用','分割，默认分隔符'\001'
	collection items terminated by '-'   -- 集合元素之间用'-'分割
	map keys terminated by ':'     -- 键值之间用':'分割
	lines terminated by '\t'  -- 行之间通过'\t'制表符分割，默认通过'\n'换行符分割
1
2
3
4
5

partition by

partition by(field_name string,..)关键字可以按照指定的列进行分区。同一分区的数据存储在同一文件夹，查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文件夹下本分区下的文件，避免全表数据扫描，提高了查询效率。
分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上

create table dbname.tableName(
	field_name1 string,
	field_name2 string
)
partitioned by (p_field string,pmt string comment '年月')

-- 分区表中插入数据
-- 1、手动加载数据
load data local inpath '/path/xxxx.csv' into table tableName partition (pmt='2023-10');

-- 2、insert插入数据
insert into tableName partition(p_field='aaa',pmt='2023-10')
select
	field_name1,
	field_name2
from
	tmp_tableName
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

更多分区表创建和插入数据内容请参考另一篇文章Hive创建分区表并插入数据

clustered by

clustered by(field_name,..) sorted by(field_name asc|desc,..) into n buckets 创建分桶表，根据指定字段和排序方式，并将表分为指定数量的桶。查询时只需遍历一个桶或部分桶的数据，提升查询效率。
分桶的本质是将表文件拆分多个文件，分桶字段必须存在表字段中

create table dbname.tableName_bucket(
	field_name1 string,
	field_name2 string
)
clustered by (field_name1) sorted by (field_name1 desc) into 4 buckets
row format delimited 
fields terminated by ','

-- 分桶表插入数据
insert into dbname.tableName_bucket
select * from tableName

-- 如果数据量过少不会产生分桶，可以设置打开强制分桶开关
set hive.enforce.bucketing=true; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

stored as

stored as后可加textfile、sequencefile、parquet、orc指定文件存储格式。

-- textfile是Hive默认存储格式，可以避免各种编码和数据错乱问题，但查询效率低
stroed as textfile
-- sequencefile以二进制键值对存储数据，查询效率高，但是存储空间消耗大
stored as sequencefile
-- parquet更高效的压缩，适用于字段多，无更新，只查询部分列的情况
stored as parquet
-- orc列式存储，压缩效率高，高效的列存取查询效率
stored as orc
-- 指定orc格式并使用snappy压缩
stored as orc tblproperties ("orc.compress"="SNAPPY")

-- 为了提高计算和存储效率
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
TBLPROPERTIES('parquet.compression'='SNAPPY')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19