Hive推数到Oracle的分区表

最新推荐文章于 2023-12-12 18:09:17 发布

Lvbey

最新推荐文章于 2023-12-12 18:09:17 发布

阅读量368

点赞数

分类专栏：大数据 hive 文章标签： hadoop sqoop

本文链接：https://blog.csdn.net/lvbey/article/details/118494912

版权

hive 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

大数据

6 篇文章 0 订阅

订阅专栏

本文介绍了在数据量过千万级别的场景下，如何在Oracle中创建和管理分区表，以减少索引对效率的影响。通过一个存储过程动态增加或删除分区，并展示了Hive如何将数据推送到Oracle的分区表中，同时使用循环推数脚本来处理多个分区。这种方法优化了大数据环境下的数据迁移流程。

摘要由CSDN通过智能技术生成

需求前提

数据量过千万级；
要求少用索引（因为索引的引入会导致推数效率低下）；
优化的字段是非数字和时间型的（数字或时间型字段，直接用Oracle的动态分区即可，参见：Oracle 11g 的 interval 分区）

推数到分区表的前提

Oracle需要在开始之初便建立分区表，因为分区表不能动态由非分区表转化；
推数过程中需要动态增加或者删除分区数据，建议采用一个存储过程来实现；
需要循环推数，因为Hive只能一次性推一个分区到Oracle端（或者说目前是这样设计的）；

干正事

建立Oracle 的分区表

CREATE TABLE table_name (
     month_id       VARCHAR2(60)
    ,other_column1  VARCHAR2(40)
    ,other_column2  VARCHAR2(40)
)
PARTITION BY LIST ( month_id ) ( 
    PARTITION p_202100 VALUES ( '202100' ) 
    --因为建表时需要最少指定一个分区，我们这里就指定不会被用到的月份(不存在202100月)作为分区
);

建立可以动态增加或者删除分区数据的存储过程

CREATE PROCEDURE p_dml_partition_table (
    in_table_name       VARCHAR2
   ,in_partition_name   VARCHAR2
   ,in_partition_value  VARCHAR2
) IS
    v_partition_num NUMBER(10);
BEGIN
  --查找是否存在分区
    SELECT
        COUNT(1)
      INTO v_partition_num
      FROM
        user_tab_partitions
     WHERE
            table_name = upper(in_table_name)
           AND partition_name = upper(in_partition_name);
     
  --如果存在分区, 则先删除该分区
    IF v_partition_num > 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' DROP PARTITION ' || in_partition_name;
    END IF;
  
  --添加分区
    EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' ADD PARTITION ' || in_partition_name || ' VALUES('|| in_partition_value || ')';

END;
/

Hive端推数脚本 hive_to_oracle.sh（只列出核心部分）

    ORA_PARTITION_NAME="P_$OP_DATE"
    ORA_PARTITION_VALUE="$OP_DATE"

    # 创建/删除分区
    echo ""
    echo "`date "+%Y-%m-%d %H:%M:%S"` 删除Oracle目标表${TARGET_TABLE}分区${ORA_PARTITION_VALUE}的数据......."
    echo ""
    sqoop eval -D mapred.job.queue.name=root.myqueue \
    --connect ${JDBC_DRIVER} \
    --username ${USERNAME} \
    --password ${PASSWORD} \
    --verbose                       \
    --e "call p_create_table_partition('$TARGET_TABLE', '$ORA_PARTITION_NAME', '$ORA_PARTITION_VALUE')"

    # 导出数据至Oracle
    echo ""
    echo "`date "+%Y-%m-%d %H:%M:%S"` 开始导出数据至Oracle目标表${TARGET_TABLE}的分区${ORA_PARTITION_VALUE}......."
    echo ""
    sqoop export --table ${TARGET_TABLE} \
        --connect ${JDBC_DRIVER}  \
        --username ${USERNAME} \
        --password ${PASSWORD} \
        --export-dir /user/hive/warehouse/${SROUCE_DB}.db/${SOURCE_TABLE}/${PARTITION_KEY}/* \
        --columns ${COLUMN_STR} \
        --outdir ${SHELL_PATH}/java_file/ \
        --input-fields-terminated-by '\011' \
        --input-lines-terminated-by '\n' \
        --input-null-string '\\N' \
        --input-null-non-string '\\N'

Hive推数命令(循环调用推数脚本)：

#改进于20210712，经测试，Oracle支持并发分区推数，于是改成并发执行

V_MONTH=$START_MONTH
 # 导出最近半年数据至Oracle
while [ $V_MONTH -le $END_MONTH ]; 
do

    #改进于20210712，经测试，Oracle支持并发分区推数，于是改成并发执行
    {
        hive_to_oracle.sh --OP_DATE ${V_MONTH} --table_name table_name
    }&

    

    V_MONTH=`date -d ${V_MONTH:0:4}"-"${V_MONTH:4:2}"-""01"+"1 month" +"%Y%m"`
done