编辑代码

#JSRUN引擎2.0，支持多达30种语言在线运行，全仿真在线交互输入输出。


file_name="ods_eol_cal_report_logistic_his.sh"

columns="id,
	     model,
	     eov_id,
	     type,
	     avl_group,
	     tsk,
	     lob,
	     key_parts,
	     request_owner,
	     receive_date,
	     finish_date,
	     remarks,
	     data,
	     area,
	     bill_all,
	     bill_warranty_in,
	     afr_1y,
	     afr_2y,
	     afr_3y,
	     inv_ok,
	     inv_ng,
	     inv_total,
	     rec_qty,
	     eos_last_buy_qty,
	     eos_fair_qty,
	     msi_eos_date"

# 主鍵，用於區分唯一值，多個id用英文逗號分隔
key_id="id"

file_name="${file_name##*/}"    # 文件名，不帶文件夾
name="${file_name%\.*}" # 文件名，移除後綴
name_lay="${name%%_*}"  # 第一個"_"前的名字

layer="${name_lay}_rma"	# 建立到 _rma 的架構下
tabname="${name}"


echo $name_lay

declare -A site # 创建一个数组

site["ods"]="dwd"
site["dwd"]="dws"
site["dws"]="dm"
upper_layer="${site[$name_lay]}"

echo "${site[$name_lay]}"

echo "${tabname/$name_lay/$upper_layer}"


function text_replace()
{
	# 將 回車和Tab 替換為 空格
	local local_text="$(echo "${1}" | tr "\n" " " | tr -s "\t" " ")"
	printf "${local_text}"
}

function hive_coalesce_text()
{
	local OLD_IFS="$IFS"	# 備份分隔符
	local IFS=","	# 指定新的分隔符
	local local_layer="${1}"
	local local_tabname="${2}"
	local local_columns=($(text_replace "${3}"))	# 數組，需要使用圓括號而不是引號
	local local_keys=($(text_replace "${4}"))
	local IFS="$OLD_IFS"	# 拆分為數組后還原分隔符，否則會導致字符輸出異常
	# 使用數組拆分字符串
	for i in ${local_columns[@]}
	do
		# echo "${i}"
		# 添加格式化字符
		local column+="coalesce(a.${i},b.${i}) as ${i},"
	done

	for i in ${local_keys[@]}
	do
		# 添加格式化字符
		local key+="a.${i}=b.${i} and "
	done

	local coalesce_text="${column::-1}"
	local key_text="${key::-5}"

	local sql="insert overwrite table ${local_layer}.${local_tabname}
	(select ${coalesce_text} from ${local_layer}.${local_tabname}_temp a
	full outer join ${local_layer}.${local_tabname} b on "${key_text}")"

	local sql=$(text_replace "${sql}")

	printf "${sql}"
}

echo $(hive_coalesce_text "$layer" "$tabname" "$columns" "$key_id")