根据表名获取Impala建表语句
#!/bin/bash
lis=`cat $1`
dbName=$2
for sql in ${lis[@]}
do
echo "SHOW CREATE TABLE ${dbName}.${sql};"
done
$1:表名文件
table1 table2 table3
$2:库名
执行脚本
sh executeImpalaSQL.sh impalaTable.txt swdc1019 >> execute.sql
impala-shell --quiet -B -f execute.sql >> result.txt
结果如下
"CREATE TABLE swdc1019.p_maindata (
id BIGINT NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
classify_code STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
begin_site BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
substr_length BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
coderule_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
parent_id BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
PRIMARY KEY (id)
)
PARTITION BY HASH (id) PARTITIONS 16
STORED AS KUDU
TBLPROPERTIES ('kudu.master_addresses'='shmdsjcm01:7051,shmdsjcm02:7051,shmdsjcdh01:7051')"
解析结果
- 替换”
sed -i 's/^\"//' result.txt
sed -i 's/\"/;/' result.txt
- 替换”
替换库名
sed -i 's/CREATE TABLE swdc1019./CREATE TABLE shm_ods_swdc./' result.txt
替换数据类型为String
sed -i 's/ BIGINT/ STRING/' result.txt
sed -i 's/ TIMESTAMP/ STRING/' result.txt
替换kudu master地址
sed -i 's/^TBLPROPERTIES.*);$/TBLPROPERTIES ("kudu.master_addresses"="cm1:7051,cm2:7051,kafka1:7051,kafka2:7051,kafka3:7051");/' result.txt