hive表增量抽取到oracle数据库的通用程序(二)

hive表增量抽取到oracle数据库的通用程序(一)

前一篇介绍了java程序的如何编写、使用以及引用到的依赖包。这篇接着上一篇来介绍如何在oozie中使用该java程序。

在我的业务中,分为两段:

1. 查询hive表中的信息,通过oozie可以设置不同的变量作为增量查询的条件。

2. 将hive查询到的信息写入到oracle中。

 

对应oozie中的workflow文件如下:

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<workflow-app xmlns="uri:oozie:workflow:0.4" name="wf_${job_name}_day">
    <start to="hive-node"/>

    <!-- 统计day: dm_guba_loginlog -->
    <action name="hive-node" retry-max="10" retry-interval="3">
        <hive xmlns="uri:oozie:hive-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <job-xml>${hive_site_path}</job-xml>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <script>script.q</script>
            <param>tmp_table=tmp_dm_xxx_day</param>
            <param>params_dt=${params_dt}</param>
        </hive>
        <ok to="java-node"/>
        <error to="senderror"/>
    </action>

    <!-- 注意:hive_hql,rdms_presql语句结尾不能使用分号 -->
    <action name="java-node">
    <java>
        <job-tracker>${jobTracker}</job-tracker>
        <name-node>${nameNode}</name-node>
        <configuration>
        <property>
        <name>mapred.job.queue.name</name>
        <value>${queueName}</value>
        </property>
        </configuration>
        <main-class>com.exe.Hive2RMDS</main-class>
        <arg>--hive_url</arg>
        <arg>jdbc:hive2://xx.xx.xx.xx:10000/default</arg>
        <arg>--hive_hql</arg>
        <arg>select field1,field2,field3,dim,period,period_value from dw_dm.dm_xxx where period = 'day' and period_value = ${params_dt}</arg>
        <arg>--rdms_driver</arg>
        <arg>oracle.jdbc.driver.OracleDriver</arg>
        <arg>--rdms_url</arg>
        <arg>jdbc:oracle:thin:@xx.xx.xx.xx:1521:test001</arg>
        <arg>--rdms_username</arg>
        <arg>DW_test</arg>
        <arg>--rdms_password</arg>
        <arg>DW_test</arg>
        <arg>--rdms_tableName</arg>
        <arg>DW_DM.DM_xxx_TEST</arg>
        <arg>--rdms_columnNames</arg>
        <arg>field1,field2,field3,dim,period,period_value</arg>
        <arg>--rdms_presql</arg>
        <arg>delete from DW_DM.DM_xxx_TEST where period = 'day' and period_value = '${params_dt}'</arg>    
    
    </java>
    <ok to="end"/>
    <error to="senderror"/>
    </action>
    
<!--出错发送邮件 --> <action name="senderror"> <email xmlns="uri:oozie:email-action:0.1"> <to>xxx@qq.com</to> <subject>${job_name} log error in ${params_dt}</subject> <body>Error: ${params_dt} ,error message[${wf:errorMessage(wf:lastErrorNode())}]</body> </email> <ok to="fail"/> <error to="fail"/> </action> <kill name="fail"> <message>workflow: ${wf:id()}, error message[${wf:errorMessage(wf:lastErrorNode())}]</message> </kill> <end name="end"/> </workflow-app>

 

以下为在oozie中的执行过程提示信息:

 

总结:

该程序相对来说比较通用,可以在导入oracle前,预先执行一段sql语句用于清除oracle中已有的记录。

通过批处理导入,执行效率比较高。

通常用于将汇总的结果导出到关系型数据库中,然后通过关系型数据库直接展示到BI报表系统中。

 

posted @ 2018-05-22 12:40  硅谷工具人  阅读(1496)  评论(0编辑  收藏  举报
成功之道,在于每个人生阶段都要有不同的目标,并且通过努力实现自己的目标,毕竟人生不过百年! 所有奋斗的意义在于为个人目标实现和提升家庭幸福,同时能推进社会进步和国家目标! 正如古人讲的正心诚意格物致知,修身齐家治国平天下。