dfs -put /home/etluser/lmq/query_info_20200803.txt /tmp/export/;
load data inpath ‘/tmp/export/query_info_20200803.txt’ into table ods.B055_SZMZJ_QUERY_INFO;
直接导入会导致乱码,需要用记事本把导入的数据文件转成utf8格式,导入之后就不乱码了
用python运行会报错,需要用python3
用hive上传到dfs,然后再导入,每次导入之后,dfs上的文件就会消失
直接用load data local inpath就不会
ALTER TABLE ods.query_info1 SET SERDEPROPERTIES (‘serialization.encoding’=‘GBK’);
–创建一个二级分区的表
create table tmp_emp5(emp_id int
,emp_name string
,loc_id int )
partitioned by(month string, day string)
row format delimited fields terminated by ‘\t’;
–导入数据
load data local inpath ‘file_dir/filename’ into table database_name.table_name partition (month=“202006”,day=“30”); --从本地
drop table if exists ods.query_info;
create table ods.query_info(
wjm VARCHAR(24) ,
state VARCHAR(2),
sqbh VARCHAR(32) ,
hdbh VARCHAR(40),
hddxbh VARCHAR(40),
xm VARCHAR(50),
zjlx VARCHAR(2),
zjhm VARCHAR(18),
hdqqzylx VARCHAR(10),
qqsj VARCHAR(15),
srhdqsrq VARCHAR(10),
srhdjzrq VARCHAR(10),
cchdrq VARCHAR(10),
dezcydz VARCHAR(12),
zcqsrq VARCHAR(10),
zcjzrq VARCHAR(10),
dxsqs VARCHAR(100),
wjid VARCHAR(64)
)
partitioned by(TRAN_DATE varchar(10))
row format delimited fields terminated by ‘\t’;
drop table if exists ods.query_info1;
create table ods.query_info1(
wjm VARCHAR(24) ,
tran_date VARCHAR(8) ,
state VARCHAR(2),
sqbh VARCHAR(32) ,
hdbh VARCHAR(40),
hddxbh VARCHAR(40),
xm VARCHAR(50),
zjlx VARCHAR(2),
zjhm VARCHAR(18),
hdqqzylx VARCHAR(10),
qqsj VARCHAR(15),
srhdqsrq VARCHAR(10),
srhdjzrq VARCHAR(10),
cchdrq VARCHAR(10),
dezcydz VARCHAR(12),
zcqsrq VARCHAR(10),
zcjzrq VARCHAR(10),
dxsqs VARCHAR(100),
wjid VARCHAR(64)
)
row format delimited fields terminated by ‘*’;
load data inpath ‘/tmp/export/szmzj_query_info_20201111.txt’ into table ods.QUERY_INFO1;
load data local inpath ‘/home/etluser/lmq/szmzj_query_info_20201111.txt’ into table ods.QUERY_INFO1;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ods.B055_SZMZJ_QUERY_INFO partition(TRAN_DATE)
select
wjm
,state
,sqbh
,hdbh
,hddxbh
,xm
,zjlx
,zjhm
,hdqqzylx
,qqsj
,srhdqsrq
,srhdjzrq
,cchdrq
,dezcydz
,zcqsrq
,zcjzrq
,dxsqs
,wjid
,TRAN_DATE
from ods.QUERY_INFO1;
select distinct tran_date from ods.query_info;