• 根据表名获取Impala建表语句

      1. #!/bin/bash
      2. lis=`cat $1`
      3. dbName=$2
      4. for sql in ${lis[@]}
      5. do
      6. echo "SHOW CREATE TABLE ${dbName}.${sql};"
      7. done
    • $1:表名文件

      table1 table2 table3

    • $2:库名

    • 执行脚本

      1. sh executeImpalaSQL.sh impalaTable.txt swdc1019 >> execute.sql
      2. impala-shell --quiet -B -f execute.sql >> result.txt
    • 结果如下

      1. "CREATE TABLE swdc1019.p_maindata (
      2. id BIGINT NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      3. name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      4. classify_code STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      5. begin_site BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      6. substr_length BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      7. coderule_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      8. parent_id BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
      9. PRIMARY KEY (id)
      10. )
      11. PARTITION BY HASH (id) PARTITIONS 16
      12. STORED AS KUDU
      13. TBLPROPERTIES ('kudu.master_addresses'='shmdsjcm01:7051,shmdsjcm02:7051,shmdsjcdh01:7051')"
    • 解析结果

      • 替换”
        1. sed -i 's/^\"//' result.txt
        2. sed -i 's/\"/;/' result.txt
    • 替换库名

      1. sed -i 's/CREATE TABLE swdc1019./CREATE TABLE shm_ods_swdc./' result.txt
      • 替换数据类型为String

        1. sed -i 's/ BIGINT/ STRING/' result.txt
        2. sed -i 's/ TIMESTAMP/ STRING/' result.txt
      • 替换kudu master地址

        1. sed -i 's/^TBLPROPERTIES.*);$/TBLPROPERTIES ("kudu.master_addresses"="cm1:7051,cm2:7051,kafka1:7051,kafka2:7051,kafka3:7051");/' result.txt