把ods层不合规的数据导出到csv文件包括表头

1. 获取所有表字段信息 table_desc.sh

#!/bin/bash
# 遍历表名，获取表字段信息
arrayList=("ods_12_newepay_payment" 
"ods_13_newepay_return" 
"ods_15_pos_detail" 
"ods_16_pos_tender" 
"ods_31_tmall_head"
"ods_32_tmall_detail"
"ods_33_tmall_o2o_head"
"ods_34_tmall_o2o_detail"
)

length=${#arrayList[@]}

for ((i=0; i < length; i ++))
do
        cmd=${arrayList[i]}
        hive -e "use crm_ods; desc ${cmd}" | awk '{print $1 }' >> ${cmd}.txt

done

2. 拆分字典字段extends；并过滤掉分区信息 。

#!/bin/bash
# 删除表字段信息中的分区信息

a=`ls *.txt`

OLD_IFS="$IFS"
IFS=" "
arr=($a)
IFS="$OLD_IFS"
for s in ${arr[@]}
do
    echo ""
    <!--字符串切分，获取表名-->
    tbName=${s%.txt*}
    <!--替换字典字段-->
    sed -i 's/extends/extends[ERR_CODE]/' $s
    sed -i '/extends/a extends[ERR_MESSAGE]' $s
    # extends[ERR_CODE]
    # extends[ERR_MESSAGE]

    sed '/#/,$d' $s >> $tbName.md
    rm -rf $s

done

3. 组装字段为一行字符串，用逗号分开，输出到csv文件

#!/bin/bash
# 
# 拼接当前文件夹下所有的md文件内容，用逗号分割 输出到scv文件
# 
mkdir csv_rest

<!--获取当前文件夹下的.md文件数组-->
a=`ls *.md`

OLD_IFS="$IFS"

<!--IFS ： 分割符为空格-->
IFS=" "
arr=($a)
IFS="$OLD_IFS"
for s in ${arr[@]}
do
    echo ""
    tbName=${s%.md*}
    <!--遍历每个文件内容，拼接成一个字符串输出到csv,作为表头-->
    echo `(cat $s)` | sed 's/ /,/g' >>  $tbName.csv
    rm -rf $s
done

4. 执行sql查询，并将查询结果输出到对应的csv文件中

#!/bin/bash

ct_date=`date +%Y%m%d`

exe_date=1000-01-01

hive_sql="select

epay_entity,
store_id,
pos_machine_nbr,
pos_order_nbr,
epay_platform_order_nbr,
epay_platform_name,
order_status,
order_gross_amt,
order_discount_amt,
order_return_amt,
order_amt,
payment_dt,
wechat_open_id,
wechat_exist_follow_flag,
alipay_user_id,
etl_file_path,
etl_file_line_no,
member_type,
extends['ERR_CODE'],
extends['ERR_MESSAGE'],
biz_date,
data_invalid_type,
etl_channel,
etl_date
from crm_ods.ods_12_newepay_payment where data_invalid_type <> 'NONE' and to_date(etl_date) = '$exe_date' order by etl_channel, biz_date,etl_file_path,etl_file_line_no "

<!--sed 命令，用逗号替换\t，g全局替换-->

hive -e "$hive_sql" |sed 's/[\t]/,/g' >> ${ct_date}_ods_12_newepay_payment_result.csv