kettle抽取数据到impala

用kettle加工，加载数据到impala上

1. 首先确认数据来源，例如 DB2数据库里的某些数据加工一下 传到impala

表输入组件里加工数据EXTRACT 输出到HDFS的指定路径（impala表的LOCATION）

2. kettle加工数据上传到HDFS指定路径下

impala表数据不能update和delete，若要把实时数据加载进impala表，对于数据量较大的表（可以在impala里创建 临时表TEXTFILE 格式的，正式表PARQUET格式的 并分区；每次实时加工的数据传到临时表里，再把临时表的数据overwrite 到正式表）

TEXTFILE格式的表在创建时要指定字段分隔符 （一般常用ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 或者 ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'）

kettle 加工 拖拽相关组件如下图：

若源数据量很大，例如订单数据每天千万条，每次要更新最近3天的数据，可以按日期生成txt文件，每天一个文件， FILE_NAME 自定义文件目录，根据日期指定数据存放在HSFS上的位置'hdfs://IP:8020/user/hive/warehouse/test.db/order_tmp/dt_day_id='||substr(char(DT_DAY_ID),1,8)||'/order_'||substr(char(DT_DAY_ID),1,8) FILE_NAME
数据放在按日期命名的文件下/user/hive/warehouse/test.db/order_tmp/dt_id=20190301/order_20190301.txt

如果数据量不大一个txt文件即可

输入impala表指定的分隔符，编码选择UTF-8，如果分隔符不一致会造成传入NULL数据（另外如果分隔符是特殊字符要转义），编码不是UTF-8会乱码

如果源字段是string型，选择最小宽度可以一键快速搞定去掉左右两端空格。

3. 关于impala的操作

远程到impala服务器上，输入impala-shell进入impala数据库环境

show databases;    ##查看所有数据库
use test;    ##连接test库
show tables;   ##查看test库下所有表
show create table test.order_tmp; ##查看test.order_tmp表创建的表结构和格式
create table test.order_tmp(
order_cd STRING,
row_cd STRING,
dt_id Int,
order_qty STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
WITH SERDEPROPERTIES ('field.delim'=',', 'serialization.format'=',')
STORED AS TEXTFILE  
LOCATION 'hdfs://dcbigdatamgr2:8020/user/hive/warehouse/test.db/order_tmp' ;    
##备注：其中dcbigdatamgr2是HDFS的hostname

create table test.order(
order_cd STRING,
row_cd STRING,
order_qty STRING
)
PARTITIONED BY (dt_year_id Int, dt_month_id Int, dt_id Int) 
STORED AS PARQUET   
LOCATION 'hdfs://dcbigdatamgr2:8020/user/hive/warehouse/test.db/order_txt'
TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='false', 'avro.schema.url'='hdfs://dcbigdatamgr2:8020/user/hive/warehouse/test.db/order/.metadata/schemas/1.avsc', 
'kite.compression.type'='snappy', 'numFiles'='0', 'numRows'='-1', 'rawDataSize'='-1', 'totalSize'='0') 

数据传到hdfs指定路径之后（备注：TEXTFILE格式的表，执行load后，txt文件会消失）

load data inpath '/user/hive/warehouse/test.db/order_txt/' into table test.order_txt;
refresh test.order_txt;

kettle中，用运行SSH命令组件加载数据到impala表(impala-shell命令)，impala数据库可以select到数据了

impal服务器的IP，用户名和密码 测试连接


若impala表是PARQUET格式的分区表，txt文件先load到TEXTFILE格式的临时表中，之后再 insert overwrite table table_nm partition(dt_XX_id) select * from dbnm.table_nm_tmp

备注：

• overwrite 是全表更新，insert into 是追加数据；
• impala表中的数据不能根据where 条件 delete， 只可以truncate table；
• 如果每次取前3天的数据，每天执行job会有重复数据，数据量又很大，创建表时指定分区 PARTITIONED BY（静态分区）例如年、月、日分区，每次加载数据 overwrite分区数据；

TEXTFILE格式表数据加载到PARQUET格式表中，TEXTFILE格式临时表没有分区，PARQUET格式的表有分区

insert overwrite table test.order partition(dt_year_id,dt_month_id,dt_id) select order_cd, row_cd, order_qty, cast(left(cast(dt_id as string),4) as int) as dt_year_id, cast(left(cast(dt_id as string),6) as int) as dt_month_id, dt_d from test.order_tmp where dt_id between ${BDATE_ID} and ${EDATE_ID};

备注：分区字段不能出现在创建表的字段中，查询数据时会发现分区字段会出现在表字段列的最后列，分区字段其实是虚列

SSH命令：

impala-shell -i impala服务器IP:21000 -q  "load data inpath '/user/hive/warehouse/test.db/order_tmp/' into table test.order_tmp;"
impala-shell -i impala服务器IP:21000 -q  "refresh test.order_tmp;"
impala-shell -i impala服务器IP:21000 -q  "insert overwrite table test.order partition(dt_year_id,dt_month_id,dt_id) select order_cd, row_cd, order_qty, cast(left(cast(dt_id as string),4) as int) as dt_year_id, cast(left(cast(dt_id as string),6) as int) as dt_month_id, dt_d from test.order_tmp;"
impala-shell -i impala服务器IP:21000 -q  "refresh test.order;"

临时表TEXTFILE格式表也是分区表，数据insert overwrite到正式表PARQUET格式的分区表

create table test.order_tmp(
order_cd STRING,
row_cd STRING,
order_qty STRING
)
PARTITIONED BY (dt_id Int) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
WITH SERDEPROPERTIES ('field.delim'=',', 'line.delim'='\n', 'serialization.format'=',')
STORED AS TEXTFILE    
LOCATION 'hdfs://dcbigdatamgr2:8020/user/hive/warehouse/test.db/order_tmp' ;  

hdfs 文件存放路径：hdfs://ip:8020/user/hive/warehouse/test.db/order_tmp/dt_id='||substr (char(DT_ID),1,8)||'/order_'||substr (char(DT_ID),1,8) FILE_NAME

order_tmp TEXTFILE格式的表是分区表，如果hdfs 路径下有文件，但在impala中select 不到数据，动态分区需要手动添加分区后才能查到相应数据

alter table test.order_tmp add partition(dt_id=20190301)；

用脚本添加一年的分区

#!/bin/bash 
currentday=20190301
##先添加20190101至今的分区
count=59
while [[ $count -ge 0 ]] 
do 
 currentday=`date +"%Y%m%d" -d"$count day ago"` 
 count=$(($count-1))
 echo "currentday: "$currentday 
 impala-shell -i 10.20.2.1:21000 -q "alter table test.order_tmp add partition(dt_id=$currentday);"
done

#!/bin/bash 
currentday=20190301
##先添加今天到20191231的分区
count=306
while [[ $count -ge 0 ]] 
do 
 currentday=`date +"%Y%m%d" -d"$count day"` 
 count=$(($count-1))
 echo "currentday: "$currentday 
 impala-shell -i 10.20.2.1:21000 -q "alter table test.order_tmp add partition(dt_id=$currentday);"
done

hdfs用到的命令：

hdfs dfs -ls /user/hive/warehouse/test.db/order_txt/  ##查看hdfs指定路径下的文件
hdfs dfs -cat /user/hive/warehouse/test.db/order_txt/order_txt.txt ##查看TXT文件的内容
hdfs dfs -rm -r /user/hive/warehouse/test.db/order_tmp/*  ##删除order_tmp 目录下的文件