hive建表和插入格式问题

本篇主要讨论建表，插表，表存储格式，字段分隔符，MR任务输入文件注意事项
有不正确的地方，还请指正

hive建表语句

下面的示例，建立表bankFilter，以 hp_settle_dt 划分分区，存储为 rcfile 格式，输入输出均为 RCFile 格式。

CREATE EXTERNAL TABLE ardb.bankFilter(
pri_key string,
log_cd string,
hp_settle_dt_1 string)
PARTITIONED BY (
hp_settle_dt string)
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe’
STORED AS INPUTFORMAT
‘org.apache.hadoop.hive.ql.io.RCFileInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.RCFileOutputFormat’
LOCATION
‘hdfs://bs1/user/tbl1’
TBLPROPERTIES ( ‘transient_lastDdlTime’=‘1537412330’);

详细说明：

• 建立分区，并指明分区字段名，表插入数据时使用：

PARTITIONED BY ( hp_settle_dt string)

• 指定存储格式为 RCFile：

ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe’
STORED AS INPUTFORMAT
‘org.apache.hadoop.hive.ql.io.RCFileInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.RCFileOutputFormat’

• 若要存储为文本文件，则需要如下设定：

ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
STORED AS INPUTFORMAT
‘org.apache.hadoop.hive.ql.io.TextInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’

• 或者直接指定：

STORED AS RCFILE
或 STORED AS TEXTFILE

• 指定新建表存储位置，一般建表时会自动创建该目录，若事先创建好该目录，可能会导致删表报错：

LOCATION
‘hdfs://bs1/user/tbl1’

• 表最近更新时间：

TBLPROPERTIES (
‘transient_lastDdlTime’=‘1537412330’);

• 此外，上述语句中没有指定字段分隔符，需要注意的是 hive 默认的分隔符是 ‘\001’ 在 linux 终端用 vim 打开是 ‘^A’，在写 MR 任务时，如果使用的分隔符为 ‘\t’, ‘,’ 等，也可以在建表时，指定分隔符
• 指定字段分隔符为 ‘\t’ ，行分隔符为 ‘\n’ :

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ LINES
TERMINATED BY ‘\n’

hive表插入语句

insert overwrite table ar_svcdb.bankFilter partition(hp_settle_dt=‘20201215’) select
pri_key,
log_cd,
hp_settle_dt
from ardb.viw_bankFilter
where hp_settle_dt =‘20201215’

• 可以在插入语句前，添加一些配置项，如队列名，压缩文件等：

SET mapreduce.job.queuename=queue12;
SET hive.exec.compress.output=true;
SET mapred.output.compress=true;
SET mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;

• 在写 MR 任务时，若使用的输入文件为 RCFile 存储格式，需要调用以下两个 jar 包，所以确认好集群是否包含这些 jar 包

org.apache.hive.hcatalog.rcfile.RCFileMapReduceInputFormat
org.apache.hadoop.hive.serde2.columnar.BytesRefArrayWritable