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

比眉伴天荒 2021-09-23 06:52 1008阅读 0赞

**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'