hive表批操作_hive批量运行sql语句怎么做

一、添加分区

注意事项：

• 有数据才添加分区，避免创建空目录
• 批量添加分区，提高效率

1.  直接添加

目录不存在时，会生成空目录；

hive -e "alter table table_name add if not exists  partition(dt='${dt}') location '${save_path}';

2.  存在添加

防止生成空目录

#!/usr/bin/env bash
  
 
#------------------------------------ 1 脚本描述 --------------------------------
# 用途描述: 这种目录（s3://save_path/20220518/00/*.log)存放的数据，加载到外表小时ods层
# 调用方式: sh -e ori_to_ods_hi.sh ${base_path} ${table_name} ${dt} ${dh}
 
 
#------------------------------------- 2 获取参数 -------------------------------
base_path=${1}
table_name=${2}
dt=${3}
dh=${4}
 
 
#------------------------------------- 3 处理逻辑  -------------------------------
## 拼接命令
path="${base_path}/${dt}/${dh}/"
add_command="alter table ${table_name} add if not exists  partition(dt='${dt}', dh='${dh}') location '${path}';"
echo "${add_command}"
 
## 路径存在，则添加分区
is_exist=$(aws s3 ls ${path})
if [[ ${is_exist} = '' ]]
then
    echo '没有数据，不需要添加分区!'
else
    hive -e "${add_command}"
fi

3.  批量添加

数据存在时才添加分区，防止生成空目录；

1)  天表模板

#!/usr/bin/env bash
  
 
#------------------------------------ 1 脚本描述 --------------------------------
# 用途描述: 这种目录（s3://save_path/dt=20220518)存放的数据，加载到表
# 调用方式: sh -e add_partition.sh base_path table_name start_date end_date
# 注意事项：1）base_path不要带/ 2）时间区间是左闭右开的
 
 
#------------------------------------- 2 获取参数 -------------------------------
base_path=${1}
table_name=${2}
start_date=${3}
end_date=${4}
 
 
#------------------------------------- 3 拼接命令  -------------------------------
## 
add_partition="alter table ${table_name} add if not exists  "
tmp=''
while [[ "${start_date}" < "${end_date}" ]]
do
    echo ${start_date}
    # 定义变量
    path="${base_path}/dt=${start_date}/"
 
    # 路径存在，则拼接
    is_exist=$(aws s3 ls ${path})
    if [[ ${is_exist} = '' ]]
    then
        echo "No data: ${path}"
    else
        tmp="${tmp} partition(dt='${start_date}') location '${path}'"
    fi
 
    # 更新时间
    start_date=$(date -d "${start_date} +1 day" +"%Y%m%d")
done
 
 
#------------------------------------- 4 执行添加  -------------------------------
if [[ ${tmp} = '' ]]
then
    echo 'No Partition!'
else
    echo "${add_partition}${tmp};"
    hive -e "${add_partition}${tmp};"
fi

2）小时表模板

#!/usr/bin/env bash
  
 
#------------------------------------ 1 脚本描述 --------------------------------
# 用途描述: 这种目录（s3://save_path/20220518/00/*.log)存放的数据，加载到外表小时ods层
# 调用方式: sh -e ori_to_ods_hi.sh base_path table_name start_time end_time
# 注意事项：1）base_path不要带/ 2）时间区间是左闭右开的
 
 
#------------------------------------- 2 获取参数 -------------------------------
base_path=${1}
table_name=${2}
start_time="${3}"
end_time="${4}"
 
 
#------------------------------------- 3 处理逻辑  -------------------------------
## 拼接命令
add_partition="alter table ${table_name} add if not exists  "
add_path=''
while [[ "${start_time}" < "${end_time}" ]]
do
    echo ${start_time}
    # 定义变量
    dt=$(date -d "${start_time}" +'%Y%m%d')
    dh=$(date -d "${start_time}" +'%H')
    path="${base_path}/${dt}/${dh}/"
    
    # 路径存在，则拼接
    is_exist=$(aws s3 ls ${path})
    if [[ ${is_exist} = '' ]]
    then
        echo "No data: ${path}"
    else
        add_path="${add_path} partition(dt='${dt}', dh='${dh}') location '${path}'"
    fi
    
    # 更新时间
    start_time=$(date -d "${start_time} +1 hour" +"%Y-%m-%d %H")
done
 
## 批量添加分区
if [[ ${add_path} = '' ]]
then
    echo '没有分区要添加！'
else
    echo "${add_partition}${add_path};"
    hive -e "${add_partition}${add_path};"
fi

二、删除分区

1.  单个删除

#!/usr/bin/env bash
  
 
#------------------------------------ 1 脚本描述 -----------------------------------
#用途描述： 串行按天删除分区
#调用方式：nohup sh -e series_delete.sh ttable_name start_date end_date > running.log 2>&1 &
 
 
#------------------------------------ 2 获取参数 ------------------------------------
table_name="${1}"
start_date="${2}"
end_date="${3}"
 
 
#------------------------------------- 4 处理逻辑 ------------------------------------
while [[ ${start_date} < ${end_date} ]]
do
    echo "alter table ${table_name} drop partition (dt='${start_date}');"
    hive -e "alter table ${table_name} drop partition (dt='${start_date}');"
    echo ""
    start_date=$(date -d "${start_date} +1 days" +"%Y%m%d")
done

2.  条件删除

hive -e "alter table ${table_name} drop partition (dt > '20230120', dt < '20230122');"

三、统计信息

1.  统计大小

#!/usr/bin/env bash
  
 
#------------------------------------ 1 脚本描述 ----------------------------------
# 用途描述: 批量获取表的存储路径和指定日期路径的大小（默认取昨天的日期）
# 调用方式: nohup sh -e get_table_locations.sh tables dt > res.log &
 
 
#------------------------------------- 2 获取参数 ---------------------------------
## 校验表名
if [[ ${1} = '' ]]
then
    echo "error: 必须输入一个表名"
    exit 1
else
    tables="${1//,/ }"
fi
## 校验日期
if [[ ${2} = '' ]]
then
    dt=$(date  +"%Y%m%d" -d  "-1 days")
else
    dt="${2}"
fi
 
 
#------------------------------------- 3 处理逻辑  -------------------------------
for table in ${tables}
do
    base_path=$(hive -S -e "show create table ${table};" | grep s3://  | awk -F "'" '{print $2}')
    path="${base_path}/dt=${dt}/"
    echo command:" aws s3 ls ${path}  --recursive --human-readable --summarize"
    statistics=$(aws s3 ls ${path} --recursive --summarize)
    size=${statistics##*:}
    echo "result,${table},${path},${size}"
done

2.  获取路径

# 批量找出表的路
tables="
tranadm.adm_pub_rpt_app_open_dur_dist_ws
tranadm.adm_pub_rpt_app_open_dur_dist_total_ds
"
sql=""
for table in ${tables}
do
    sql=${sql}"show create table ${table};"
done
hive -e "${sql}"  | grep oss | grep oss | awk -F "'" '{print $2}' 

四、建表语句

1.  指定表名

#!/usr/bin/env bash
  
 
#------------------------------------ 1 使用方式 -----------------------------------
# 用途描述: 批量获取表结构
# 调用方式: sh -e get_show_create_tables.sh tables
 
 
#------------------------------------- 2 获取外部参数 -------------------------------
tables="${1//,/ }"
 
 
#------------------------------------- 3 拼接sql获取路径 ---------------------------
show_create_tables=''
for table in ${tables}
do
    show_create_tables="${show_create_tables} show create table ${table};"
done
 
 
#-------------------------------------- 4 输出表结构 ---------------------------------
hive -S -e "set hive.cli.print.header=flase;${show_create_tables}"

#!/usr/bin/env bash
  
 
#------------------------------------ 1 使用方式 -----------------------------------
# 用途描述: 批量获取表结构
# 调用方式: sh -e get_create_tables.sh ${tables} ${result_file}
 
 
#------------------------------------- 2 获取外部参数 -------------------------------
tables="${1//,/ }"
result_file=${2}
 
 
#------------------------------------- 3 拼接sql获取路径 ---------------------------
show_create_tables=''
for table in ${tables}
do
    show_create_tables="${show_create_tables} show create table ${table};"
done
 
 
#-------------------------------------- 4 输出表结构 ---------------------------------
# 删除规则
delete_rule="/ROW FORMAT SERDE/d; 
    /STORED AS INPUTFORMAT/d;   \
    /OUTPUTFORMAT/d;            \
    /org.apache.hadoop/d;       \
    /LOCATION/d;                \
    /ks3:/d;                    \
    /last_modified/d;           \
    /spark.sql/d;"              
 
# 查询表结构、替换、删除无效元数据
hive -S -e "set hive.cli.print.header=flase;${show_create_tables}"  \
    | sed -e "s/STORED AS INPUTFORMAT/STORED AS PARQUET/g"  \
    | sed -e "s/CREATE/;\n\nCREATE/g" \
    | sed -e "${delete_rule}" \
    > ${result_file}
 
# 补充末尾的分号
echo ";" >> ${result_file}

2.  指定库名

#!/usr/bin/env bash
  
 
#------------------------------------ 1 脚本描述 -------------------------------
# 实现思路：获取某个库下所有的表，循环拼接show create table语句，在hive上批量执行并输出到文件
# 用途描述: 获取某个库下所有的建表语句
# 脚本路径: 
# 调用方式: 
 
 
#------------------------------------- 2 获取参数 -------------------------------
## 校验库名
if [[ ${1} = '' ]]
then
    echo "error: 必须输入一个库名"
    exit 1
else
    database_name=${1}
fi
 
 
#------------------------------------- 3 处理逻辑  -------------------------------
tables=$(hive -e "use ${database_name}; show tables;")
for table in ${tables}
do
    showcreatetable="${showcreatetable}show create table ${table};"
done
hive -e "use ${database_name}; ${showcreatetable}" > ${database_name}_extract_all_tables.sql
 

五、建库语句

#!/usr/bin/env bash
  
 
#---------------------------------- 1 脚本描述 -----------------------------
# 实现思路：获取所有的库名，循环拼接show create database语句，在hive上批量执行并输出到文件
# 用途描述: 获取所有库的建表语句,不能获取库名中有特殊字符（例如：-）的建库语句
# 脚本路径: sh -e get_all_create_databases_sql.sh
# 调用方式: 
 
 
#----------------------------------- 2 处理逻辑  ----------------------------
db_names=$(hive -e "show databases;" | grep -v '-') 
for db_name in ${db_names}
do
    create_databases="${create_databases}show create database ${db_name};"
done
hive -e "${create_databases}" > all_databases.sql

---