Пост 19. Pushdown запросов в Hadoop из Oracle RDBMS или как заставить hadoop  выполнять тяжелые запросы на своей стороне.

Доброго времени суток!

В предыдущем посте я описывал способ, с помощью которого можно прочитать данные, лежащие на HDFS, через внешнюю таблицу Oracle. Важно понимать, что в этом случае hadoop используется только как хранилище фалов (HDFS) и мы никак не задействуем мощь MapReduce.

То есть если мы пытаемся сделать join двух таблиц, он будет выполняться на стороне СУБД (поток данных будет благополучно передан на головы базы в PGA, где и будет происходить объединение). А что же делать, если мне хочется сделать JOIN двух таблиц на стороне Hadoop (использовать всю мощь MapReduce). Для этого надо сделать несколько шаманских действий:

       1)      Создать shell скрипт (назовем его 7.hive_pushdown.sh), который положим на сервер СУБД:

#!/bin/bash

export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/usr/lib/hive/lib/*

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=orcl

source /home/oracle/.bash_profile

export BASH=/bin/bash

HADOOP_CLASSPATH=':/u01/app/oracle/product/11.2.0/db_home1/jdbc/lib/ojdbc6.jar:/u01/app/oracle/product/11.2.0/db/jdbc/lib/ojdbc6.jar:/usr/lib/hive/lib/hive-metastore-0.10.0-cdh4.3.0.jar:/usr/lib/hive/lib/hive-metastore-0.10.0-cdh4.3.0.jar:/u01/app/oracle/product/11.2.0/db/jlib/oraclepki.jar:/usr/lib/hbase/hbase-0.94.6-cdh4.3.0-security.jar:/usr/lib/hive/lib/libthrift-0.9.0.jar:/usr/lib/hive/lib/libfb303-0.9.0.jar:/usr/lib/hive/lib/*:/usr/lib/hadoop/lib/*:/usr/lib/hadoop/lib/*'

export HADOOP_CLASSPATH=/u01/connectors/oraloader/jlib/*:/usr/lib/hive/conf:/u01/connectors/orahdfs/jlib/*:/u01/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/*:/u01/nosql/kv-2.0.26/lib/kvstore.jar:/usr/lib/hive/lib/*:/usr/lib/hive/lib/*

export HADOOP_CONF_DIR=/home/oracle/hadoop-conf

export JAVA_HOME=/usr/java/latest/

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin

export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/usr/lib/hive/lib/*

export OSCH_HOME=/u01/connectors/orahdfs

/usr/bin/hive -e 'drop table new.test_results';

str1="create table new.test_results as ";

str2=$*;

str3=$str1$str2

echo $str3

/usr/bin/hive -e "$str3";

sqlplus -s demouser/welcome1 <<+EOF

drop table demouser.test_results;

+EOF

/usr/bin/hadoop jar $OSCH_HOME/jlib/orahdfs.jar \

       oracle.hadoop.exttab.ExternalTable \

       -conf /home/oracle/scripts/conf/7.create_table_from_hive_push.xml \

       -createTable

На вход этой программе подается SQL который надо выполнить. Скрипт выполняет его (предварительно удалив временную таблицу hive, если она осталась с прошлого раза):

/usr/bin/hive -e 'drop table new.test_results';

str1="create table new.test_results as ";

str2=$*;

str3=$str1$str2

echo $str3

/usr/bin/hive -e "$str3";

После чего лезет в СУБД Oracle и удаляет таблицу результатов (так же с прошлого раза):

sqlplus -s demouser/welcome1 <<+EOF

drop table demouser.test_results;

+EOF

После чего создает внешнюю таблицу в Oracle c по шаблону только что созданной  hive таблицы:

/usr/bin/hadoop jar $OSCH_HOME/jlib/orahdfs.jar \

       oracle.hadoop.exttab.ExternalTable \

       -conf /home/oracle/scripts/conf/7.create_table_from_hive_push.xml \

       -createTable

Конфиг (точнее пример), необходимый OSCH прилагается наже:

<?xml version="1.0"?>

 <configuration>

  <property>

    <name>hive.metastore.uris</name>

    <value>thrift://localhost:9083</value>

  </property>

    <property>

      <name>oracle.hadoop.exttab.tableName</name>

      <value>test_results</value>

    </property>

    <property>

      <name>oracle.hadoop.exttab.locationFileCount</name>

      <value>4</value>

    </property>

    <property>

      <name>oracle.hadoop.exttab.hive.databaseName</name>

      <value>new</value>

    </property>

    <property>

      <name>oracle.hadoop.exttab.sourceType</name>

      <value>hive</value>

    </property>

    <property>

      <name>oracle.hadoop.exttab.hive.tableName</name>

      <value>test_results</value>

    </property>

    <property>

      <name>oracle.hadoop.exttab.defaultDirectory</name>

      <value>HADOOP_DIR</value>

    </property>

    <property>

      <name>oracle.hadoop.connection.user</name>

      <value>demouser</value>

    </property>

    <property>

      <name>oracle.hadoop.connection.wallet_location</name>

      <value>/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/</value>

    </property>

    <property>

      <name>oracle.hadoop.connection.tnsEntryName</name>

      <value>orcl</value>

    </property>

    <property>

      <name>oracle.hadoop.connection.tns_admin</name>

      <value>/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/</value>

    </property>

</configuration>

Это делается один раз. Потом используется.

     2)      Создать в СУБД Oracle Java процедуру для вызова внешних процедур операционной системы (тоже делается только один раз ):

create or replace and compile java source named "OsUtils" as

import java.io.IOException;

import java.io.InputStream;

import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

class StreamGobbler

  extends Thread {

  InputStream is;

  String type;

  StreamGobbler (InputStream is, String type) {

    this.is = is;

    this.type = type;

  }

  public void run () {

    try {

      InputStreamReader isr = new InputStreamReader(is);

      BufferedReader br = new BufferedReader(isr);

      String line;

      while ((line = br.readLine()) != null) {

        OsUtils.result.append(line).append("\n");

      }

    } catch (IOException ioe) {

      ioe.printStackTrace();

    }

  }

}

public class OsUtils {

  public static StringBuffer result;

  public static int runCommand (String command)

    throws Throwable {

    result = new StringBuffer();

    Runtime rt = Runtime.getRuntime();

    Process proc = rt.exec(command);

    // Any error message?

    StreamGobbler errorGobbler = new StreamGobbler(proc.getErrorStream(), "ERROR");

    // Any output?

    StreamGobbler outputGobbler = new StreamGobbler(proc.getInputStream(), "OUTPUT");

    // Kick them off.

    errorGobbler.start();

    outputGobbler.start();

    // Any error?

    proc.waitFor();

    System.out.println(result.toString());

    return proc.exitValue();

  }

}

/

create or replace function run_os_command (p_cmd in varchar2) return number as

  language java name 'OsUtils.runCommand (java.lang.String) return int';

/

Назначим права:

exec dbms_java.grant_permission('DEMOUSER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '' );

/

exec dbms_java.grant_permission('DEMOUSER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );

/

exec dbms_java.grant_permission('DEMOUSER', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );

/

commit;

/

exit;

     3)      Используем предыдущие 2 механизма для вызова ad-hoc запроса и выполнения его на стороне Hadoop (вызываем из базы данных Oracle):

set serveroutput on;

declare

i number;

v_sql varchar2(256):='select max(a_num) from new.tab_on_hadoop';

str   varchar2(1024);

begin   

                str:='/home/oracle/scripts /7.hive_pushdown.sh '||v_sql;

                dbms_output.enable(100000);

                i:=run_os_command(str);

                dbms_output.put_line(i);

end;

/

select * from  "DEMOUSER"."TEST_RESULTS";

/

exit;

Вот как то так. Описанный выше метод не истина в последней инстанции это только один из способов совместного применения СУБД и Hadoop. Минусы описанного выше способа: при каждом запросе удаляется и создается таблица в Oracle и Hadoop (а это изменение в словаре данных, что далеко не бесплатно). Вторым минусом является однопоточность данного решения. Только один пользователь может выполнять такой запрос. По поводу удаления если у вас предполагается один и тот же запрос, который будет многократно выполняться, можно использовать Oracle temporary table и каждый раз делать туда вставку.

Все вышенаписанное лишь выглядит страшно. Попробуйте и вы сами поймете весь профит который можно получить от «слонов». Если есть вопросы – welcome!