https://blog.csdn.net/qq_26442553/article/details/79747906
建表方式一: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版。
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的显示情况,可读性差。
2.默认存储格式TextFILE存储格式,hive常用的有三种存储格式,具体参考我的其他博客篇,TEXTFILE存储格式内容可读性好,没有加密。
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:使用自定义储存格式建表
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;