hive建表语句（包括txt、Orc和分区）

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

txt格式  无分区

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

use an_pafc_safe;
create table an_pafc_safe.sx_ela_bp_info
(
id_ela_bp_info   string,
code             string,
agent_no         string,
operation_time   string,
product_no       string,
info_no          string,
created_by       string,
created_date     string,
updated_by       string,
updated_date     string,
openid           string,
page             string
)
row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;

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

 

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

Orc格式  无分区

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

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;

 

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

有分区的hive表

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

use an_pafc_safe;
drop table an_pafc_safe.life_mbi_epics_info_yb;
create table an_pafc_safe.life_mbi_epics_info_yb
(
DEPTNO                 string,
POLNO                  string,
PLAN_CODE              string,
PAYMENT_DATE           string,
TOT_MODAL_PREM         string,
REGION_CODE            string,
AMT_TYPE               string,
PREM_TYPE              string,
BUSINESS_TYPE          string,
BUSINESS_SRC           string,
OP_TYPE                string,
POL_YR                 string,
LCD                    string,
FCD                    string,
PK_SERIAL              string,
bank_channel_type      string
)
PARTITIONED BY ( `day` string)  
stored as orcfile;

进入hive后，使用对应的集市库和设置相应的参数，然后执行表数据插入操作：

示例一：
 

use an_pafc_safe;
set mapred.job.queue.name=queue_0101_01;
set hive.exec.dynamic.partition=true;  
set hive.exec.dynamic.partition.mode=nonstrict; 
set hive.exec.max.dynamic.partitions.pernode = 1000;
set hive.exec.max.dynamic.partitions=1000;
insert overwrite table  an_pafc_safe.life_mbi_epics_info_yb partition(day) 
select * from an_pafc_safe.life_mbi_epics_info_yb1  

示例二：
 

insert overwrite table  an_pafc_safe.bas_ela_agent_info partition(day) 
select 
id            
,code          
,parent_code   
,type          
,type_no       
,type_name     
,channel       
,agent         
,openid            
,created_date as share_read_time
,referer       
,share_source  
,share_channel 
,detail_no     
,wxbind       
,substr(created_date,1,10) as statis_date
from sx_360_safe.sx_lvl_trace_info 
where substr(created_date,1,10) <'2017-01-01' 
and substr(created_date,1,10) <'2017-09-28'  ;