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, ...)]
关键词含义
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
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'换行符分割
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
更多分区表创建和插入数据内容请参考另一篇文章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;
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')