hive建表的标准格式及其建表属性详解2

原创

涤生手记 2021-08-27 15:51:04 博主文章分类：Hive实战和企业数仓建模 ©著作权

文章标签 hive建表语句详解 hive 建表语句 hive建表 hive建表格式 create table hive 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者涤生手记的原创作品，请联系作者获取转载授权，否则将追究法律责任

建表方式一：demo级建表

hive (fdm_sor)> create table my_tmp (id int,ids string );

查看建表语句（工作中经常使用的语句）

hive (fdm_sor)> show create tablemy_tmp;

CREATE TABLE `my_tmp`(
 `id` int,
 `ids` string)
ROW FORMAT SERDE
 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STOREDAS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
 'hdfs://Hadoop2/user/finance/hive/warehouse/fdm_sor.db/my_tmp'
TBLPROPERTIES (
 'transient_lastDdlTime'='1522135161')

可以看出实际上在hive底层建表默认了很多属性(比如反引号的使用),用户可以选择自定义，也可以选择默认。当然你如果把所有的默认属性配置写到建表语句里，也不会错，只是等价于上面的DEMO版。注意我这里使用的是默认的default库。

1.row format行列分割符，默认是行是\n分割，字段分隔符为ascii码的控制符\001（即Ctrl+A）

如上面表，my_tmp，执行插入数据：
   insert into  table my_tmp  select 102,"jack" from aaaa limit1
然后数据插入后通过hadoop命令将表中数据文件下载到本地
   hadoop  fs -get /user/hive/warehouse/fdm_sor.db/tmp_ids/*  ./
最后打开的结果如下：
   102jack

如下，windows下查看：这就是hive默认字段分隔符\001的显示情况，可读性差。

hive建表的标准格式及其建表属性详解2_hive建表

2.默认存储格式TextFILE存储格式，hive常用的有三种存储格式，具体参考我的其他博客篇，TEXTFILE存储格式内容可读性好，没有加密。

hive建表的标准格式及其建表属性详解2_hive建表

3.默认内部表（管理表）都是默认hive数据仓库路径下库名+表明的存储路径。

4.TBLPROPERTIES，用户可以在此添加一些关于表的描述，系统也会自动生成一些信息，如表创建时间

建表方式二：定义分隔符和分区

自定义分区和自定义行间字段分隔符（若要自定义行分隔符，要重写INPUTFORMAT和outputformat类。）

hive (fdm_sor)> create table my_merge（
              > ids string ,
              > curtermspastdue string)
              > PARTITIONED BY (
              >   `statis_date` string) 
              > ROW FORMAT DELIMITED FIELDSTERMINATED BY ',' ;

hive (fdm_sor)> show create tablemy_merge;

CREATE TABLE `my_merge`(
 `ids` string,
 `curtermspastdue` string)
PARTITIONED BY (
 `statis_date` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY','
STORED AS INPUTFORMAT
 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
 'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/my_merge'
TBLPROPERTIES (
 'transient_lastDdlTime'='1522135917')

注意：分区一定要写在row format之前，否则报错。这里自定义的列的分割符是‘，’，无法直接定义行的分割符，如果要定义行要重写INPUTFORMAT和outputformat类。这里使用的数据存储格式还是默认的TEXTFILE。注意下系统show create table后建表语句的变化。

建表方式三：自定义文件存储格式

hive (fdm_sor)> CREATE  TABLE `my_merge1`(
              >   `ids` string,
              >   `curtermspastdue` string)
              > PARTITIONED BY (
              >   `statis_date` string)
              > ROW FORMAT DELIMITED
              >   FIELDS TERMINATED BY ','
              > STORED AS RCFILE
              > TBLPROPERTIES (
              >   'transient_lastDdlTime'='1522135917',
              >   'author'='9527') ;

注意：STORED AS RCFILE等价于下面这种写法，底层会将STORED AS RCFILE转换成如下格式。

 STORED AS INPUTFORMAT
 'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
 OUTPUTFORMAT
 'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'

hive (fdm_sor)> SHOW CREATE TABLEMY_MERGE1;
CREATE TABLE `MY_MERGE1`(
 `ids` string,
 `curtermspastdue` string)
PARTITIONED BY (
 `statis_date` string)
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
 'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
OUTPUTFORMAT
 'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
LOCATION
 'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/my_merge1'
TBLPROPERTIES (
 'author'='9527',
 'transient_lastDdlTime'='1522136172')

像这种stored as rcfile存储的文件。具体关于hive常见的文件存储格式参考后续博客

建表方式四:自定义文件存储位置

hive (fdm_sor)> CREATE  TABLE `my_merge2`(
              >   `ids` string,
              >   `curtermspastdue` string)
              > PARTITIONED BY (
              >   `statis_date` string)
              > ROW FORMAT DELIMITED
              >   FIELDS TERMINATED BY ','
              > STORED AS RCFILE
              >LOCATION  'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/1111111111111'

注意，一般外部表创建时要定义存储位置location,但是管理表一般都会默认有存储位置，但是默认虽有，用户也可以自定义存储位置，只是存储位置没有外部表定义的那么随意。同样外部表需要自定义存储位置，其实不定义也没有关系，系统会自动使用默认的存储位置。实际开发中，外部表使用的相对较少。

建表方式5：使用自定义储存格式建表

注意：这里使用的文件存储方式公司自定义的类，列的分割符是|$@|,行的分隔符是|\n|

add jar /home/bigdata/software/hive/ext-lib/suning-hive-inputformat.jar;
CREATE TABLE fdm_ssa.ssa_lbsadmprd1_T_ORDER
(ID BIGINT commet'id，唯一标志',
ORDER_NO STRING comment'序号',
PRODUCT_ID BIGINT,
PERIOD_ID BIGINT,
CUST_SERVICE_REMARK STRING,
ACTIVE STRING,
CREATED_TIME STRING,
UPDATED_TIME STRING
)partitioned by( statis_date string)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS
INPUTFORMAT
'com.suning.hive.input.CommonTextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

日常开发实际建表格式：

1.表要有注释，没有注释的表后续无法维护

2.表要有固定的命名规则，符合公司的命名规则

3.数据存储切分格式要符合公司要求，比如这里数据仓库的复制层使用的就是自定义存储个。SOR层存储格式则是RCFILE.

下面才是符合公司规范的建表格式：

1.数据仓库复制层SSA层建表格式；
add jar /home/bigdata/software/hive/ext-lib/suning-hive-inputformat.jar;
create table fdm_ssa.ssa_cpl_tb_repay_detail_d
(
 ID              varchar(50)      comment 'ID'        
,LOAN_NO         varchar(50)      comment '借据号'
,TOTAL_AMOUNT    decimal(16,4)    comment '总金额'
,PRINCIPAL       decimal(16,4)    comment '本金'
,INTEREST        decimal(16,4)    comment '利息'
,AMERCE          decimal(16,4)    comment '罚息'
,REPAY_TIME      varchar(30)      comment '还款时间'
,STATUS          VARCHAR(10)      comment '{状态{100:申请中,200:还款成功,500:还款失败,505:失效}}'
)comment '还款明细表'
partitioned by (stat_date varchar(8) COMMENT '时间分区字段{"FORMAT":"YYYYMMDD"}')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'com.suning.hive.input.CommonTextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

2.数据仓库库第二层SOR,存储格式为RCFILE
CREATE TABLE IF NOT EXISTS FDM_SOR.SOR_EVT_ICSS_T_INS_PREMIUM_PAY(
 ID                               bigint             COMMENT 'ID'
,CHANNEL_PAY_NO                   VARCHAR(32)        COMMENT '渠道支付号'
,CHANNEL_ORDER_NO                 VARCHAR(32)        COMMENT '渠道订单号'
,POLICY_NO                        VARCHAR(32)        COMMENT '保单号'
,ETL_TIME                         varchar(19)        COMMENT 'ETL时间{"FORMAT":"yyyy-MM-dd HH:mm:ss"}'
,ETL_UPDATE_TIME                  varchar(19)        COMMENT 'ETL时间{"FORMAT":"yyyy-MM-dd HH:mm:ss"}'
)comment '保费支付表'
STORED AS RCFILE;