Hive常用输入和压缩建表语句

Text 普通文本

create table `cp_api_log`(`line` string)
partitioned by (`date` string)
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as textfile

-- 或 stored as 直接指定输入输出格式
-- stored as 
-- inputformat 'org.apache.hadoop.mapred.TextInputFormat'
-- outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

ORC / parquet 列式存储

create external table `cp_api_log`(`line` string)
partitioned by (`date` string)
row format delimited fields terminated by '\t'
stored as orc
-- stored as parquet
location 'hdfs://hdfs://BigdataCluster/user/hive/warehouse/sm_data.db/ods/ods_cp_api'

ORC + Snappy

create external table `cp_api_log`(`line` string)
partitioned by (`date` string)
row format delimited fields terminated by '\t'
stored as orc tblproperties("orc.compress"="SNAPPY")
location 'hdfs://hdfs://BigdataCluster/user/hive/warehouse/sm_data.db/ods/ods_cp_api'

根据已有Orc表创建 Orc + Snappy 表

create table 压缩表 like 原表 tblproperties("orc.compress"="snappy");
insert overwrite table 压缩表 select * from 原表; 

根据非压缩表创建 Orc + Snappy 表并导入数据

create table 压缩表
stored as orc tblproperties("orc.compression"="snappy")
as select * from 原表; 

Parquet  + Snappy

create external table `cp_api_log`(`line` string)
partitioned by (`date` string)
row format delimited fields terminated by '\t'
stored as parquet tblproperties("parquet.compress"="SNAPPY")
location 'hdfs://hdfs://BigdataCluster/user/hive/warehouse/sm_data.db/ods/ods_cp_api'

JSON 类型输入 + Snappy压缩

create external table `cp_api_log`(`line` string)
partitioned by (`date` string)
row format serde 'com.cloudera.hive.serde.JSONSerDe' 
stored as orc tblproperties ("orc.compress"="SNAPPY")
location 'hdfs://hdfs://BigdataCluster/user/hive/warehouse/sm_data.db/ods/ods_cp_api'

LZO

create external table `cp_api_log`(`line` string)
partitioned by (`date` string)
stored as INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location 'hdfs://hdfs://BigdataCluster/user/hive/warehouse/sm_data.db/ods/ods_cp_api'