Пост 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!
