hive建表语句（不同的数据分隔符存储数据，包括txt、orc、分区）

最新推荐文章于 2024-07-27 10:45:01 发布

Small-Apple

最新推荐文章于 2024-07-27 10:45:01 发布

阅读量4.6k

点赞数

本文链接：https://blog.csdn.net/qq_36732988/article/details/78779813

版权

------------------------------------------------------------------

指定分隔符

------------------------------------------------------------------

use an_pafc_safe;
 create table an_pafc_safe.agg_lbk_toapp_bussiness_app_wide
(
calc_date          string, 
branch0_code       string,
branch0_name       string,
branch1_code       string,
branch1_name       string,
branch2_code       string,
branch2_name       string,
branch3_code       string,
branch3_name       string,
title              string,
measures_code      string,
measures_name      string,
sum_data_day       decimal(38,10),
sum_data_month     decimal(38,10),
sum_data_year      decimal(38,10),
data_lastyear_d    decimal(38,10),
data_lastyear_m    decimal(38,10),
data_lastyear_y    decimal(38,10),
day_inc            decimal(38,10),
month_inc          decimal(38,10),
year_inc           decimal(38,10),
month_comple_deno  decimal(38,10),    
year_comple_deno   decimal(38,10),    
month_comple       decimal(38,10),
year_comple        decimal(38,10),
data_day_sor       string,
data_month_sor     string,
data_year_sor      string,
day_inc_sor        string,
month_inc_sor      string,
year_inc_sor       string,
month_comple_sor   string,
year_comple_sor    string,
system_order_fm    string
)
partitioned by (day  string)
row format delimited fields terminated by '\001' stored as TEXTFILE ;

----delimited fields terminated by '\t'  通过'\t'分割字段
----lines terminated by '\n'            通过'\n'结束一行字段

------------------------------------------------------------------

使用默认分隔符，不指定分隔符

------------------------------------------------------------------

use an_pafc_safe;

 create table an_pafc_safe.dim_olp_bank_table_grade_l
(
branch_grade    decimal(38,0)  ,
branch0_code    string         ,
branch0_name    string         ,
branch1_code    string         ,
branch1_name    string         ,
branch2_code    string         ,
branch2_name    string         ,
branch3_code    string         ,
branch3_name    string         ,
branch4_code    string         ,
branch4_name    string         ,
branch5_code    string         ,
branch5_name    string         ,
branch6_code    string         ,
branch6_name    string       
)
stored as orcfile;