presto-exec-with-presql.sh

#!/usr/bin/env bash

function log_info() {
  # Usage: log_info "this is the info log message"
  echo "$(date "+%Y-%m-%d %H:%M:%S") [INFO]: $@"
}

# Presto Info
server="10.82.28.143:8080"
catalog="hive"
user=""

# Pre-SQL Database Info
pre_driver="oracle.jdbc.OracleDriver"
pre_url="jdbc:oracle:thin:@10.82.96.111:1521/ORCL"
pre_user=""
pre_pass=""

log_info ""
log_info "-----------------------  Presto Info  -----------------------"
log_info "server=${server}"
log_info "catalog=${catalog}"
log_info "user=${user}"

log_info ""
log_info "-----------------------  Pre-SQL Database Info  -----------------------"
log_info "pre_driver=${pre_driver}"
log_info "pre_url=${pre_url}"
log_info "pre_user=${pre_user}"

log_info ""
log_info "-----------------------  Pre-SQL  -----------------------"
pre_sql_tmp_file=$(mktemp)
trap "rm -f ${pre_sql_tmp_file}" INT TERM HUP EXIT

# TODO: ####################### Pre-SQL here ###################
cat > ${pre_sql_tmp_file} <<EOF
-- Pre-SQL
-- TRUNCATE TABLE WDC_TARGET.PROFILE_MAPPED_ID_P3;

EOF
cat ${pre_sql_tmp_file}

log_info ""
log_info "-----------------------  SQL  -----------------------"
sql_tmp_file=$(mktemp)
trap "rm -f ${sql_tmp_file}" INT TERM HUP EXIT

# TODO: ####################### SQL here ###################
cat > ${sql_tmp_file} <<EOF
-- SQL

EOF
cat ${sql_tmp_file}

log_info ""
log_info "-----------------------  [START] Execute Pre-SQL  -----------------------"
PRE_SQL_CMD="sqlline \
-d ${pre_driver} \
-u '${pre_url}' \
-n ${pre_user} \
-p ${pre_pass} \
-f ${pre_sql_tmp_file}
"
eval ${PRE_SQL_CMD}
log_info "-----------------------  [FINISH] Execute Pre-SQL  -----------------------"

log_info ""
log_info "-----------------------  [START] Execute SQL -----------------------"
SQL_CMD="presto
--server  ${server}
--catalog ${catalog}
--user    ${user}
--file ${sql_tmp_file}
"
eval ${SQL_CMD}
log_info "-----------------------  [FINISH] Execute SQL -----------------------"