FluidQueryとは何か？
FluidQueryはIBM社のDWHアプライアンスであるPuredata System for Analytics(Netezza)のアドオン機能(無償)であり、Netezza環境からSQLを通して他のRDBMSやHadoop環境のデータを取得することができる非常に便利な機能です。興味深い点として、RDBMSの表データをNetezza側にコピーできることはもちろん、RDBMSに対して実行したSQL結果セットをNetezzaに持ってきたり、RDBMS側で実行したSQLの結果セットとNetezza側の表を結合させてユーザーが求める結果を取得するといったことも可能です。RDBMSの違いを意識することなく、1つの論理的なデータウェアハウスのような感じで様々なデータソースへのアクセスができるようになります。
また履歴データやアクセス負荷の低いものは他のHadoop環境にデータを移動させることが可能です。またHadoop上にあるデータに対してもSQLを解してNetezzaからデータを引っ張ってくることができます。
FluidQuery 1.6概要
FluidQuery1.6を使用してNetezza環境とOracle DB環境のデータ連携をやってみた
テスト実施にあたっての事前準備作業
・Netezza環境の準備:Netezza Emulatorのダウンロードとセットアップ
参考:PC上でメモリ5GBで動く! Netezza Software Emulatorを使ってみた
・Oracle DB環境の準備:Oracle DB評価版とサンプルDBの作成
参考: Oracle DB 評価版ダウンロードサイト
・Netezza Analytics Packageの取得とインストール(テスト環境はINZA3.2.1を導入済)
今回は自前のクライアントPC環境にNetezza Emulatorを導入して、仮想Netezza環境をクライアントPC環境にセットアップしました。そしてOracle DB評価版もダウンロードして同じくPC上に導入。クライアントPC上でNetezza のDBとOracle DBを稼動させ、データ連携ができるか試してみました。
FluidQueryのインストール前提としてのNPSバージョン、INZAパッケージのバージョンがあります。FluidQuery、INZAパッケージについてはNetezza代理店・サポートに問い合わせることで、無償で入手可能です。
今回FluidQueryを使って参照する先のOracle DBにはサンプルDBとしてNZというDBにサンプルデータとして明細表(MEISAI), 商品表(SHOHIN)、店舗表(SHOP)を用意、さらにTPCHのデータ1GB分をOracle DBにロードした上でNetezzaからOracle DBの取得できるか試してみました。
FluidQuery1.6パッケージのインストール
入手したFluidQuery導入パッケージファイル(nz-fluidquery-v1.6.0.0.tar.gz)をNetezza SMPホストの任意のディレクトリに保存、解凍作業をおこないます。
例:$ tar xzvf nz-fluidquery-version.tar.gz
次に解凍されたインストールファイルディレクトリ以下にある下記コマンドを実行します。
$./fluidquery_install.sh
-------------------------------------------------------------------------------
IBM Fluid Query Installer
(C) Copyright IBM Corp. 2015 All rights reserved.
Version 1.6.0.0 [Build 151110-258]
-------------------------------------------------------------------------------
Installing connector...
Checking if IBM Netezza Analytics is installed...
IBM Netezza Analytics is already installed. Ok.
Checking if IBM Netezza Analytics cartridge is installed...
IBM Netezza Analytics cartridge is already installed. Ok.
Checking for previous installation of IBM Fluid Query...
A previous installation of IBM Fluid Query was found.
Existing FluidQuery Version: 1.5.0.1 [Build 150731-40]
New FluidQuery Version: 1.6.0.0 [Build 151110-258]
Would you like to upgrade the existing IBM Fluid Query installation? (y/n) [y] y
Backing up existing installation files of IBM Fluid Query to /nz/export/ae/products/fluidquery.backup.2015-12-03.18:25:22
Backup of the existing installation of IBM Fluid Query created...
Copying connector files...
Copying license to /opt/nz/licenses/ifq...
Copying uninstaller to /nz/export/ae/products/fluidquery...
Installing Java...
Checking if backup of IBM Netezza Analytics Java exists...
Backup of IBM Netezza Analytics Java already exists...
Installation successful! Package installed in /nz/export/ae/products/fluidquery
For details see the log file at /nz/var/log/fluidquery_install/fluidquery_install.2015-12-03.18:25:15.log
インストールが成功すると下記ディレクトリにファイルが展開されています。
/nz/export/ae/products/fluidquery/
JDBCドライバセットアップと構成ファイル作成
1.JDBCドライバを下記サイトよりダウンロードを行いインストールします。
参考) Oracle Database 11g Release 2 (11.2.0.4) JDBC Drivers
2.JDBCドライバは下記ディレクトリに以下に配置、展開しておきます。
/nz/export/ae/products/fluidquery/libs/oracle/oracle
3.XML構成ファイルの作成を行います。
(例:/nz/export/ae/products/fluidquery/nz2ora.xmlを作成)
<?xml version="1.0" encoding="UTF-8"?>
<connection>
<driverClass>oracle.jdbc.driver.OracleDriver</driverClass>
<classPath>/nz/export/ae/products/fluidquery/libs/oracle/oracle/</classPath>
<connectionURL>jdbc:oracle:thin:@{$HOSTNAME}:{$PORT}/{$DATABASE}</connectionURL>
<jdbc-properties>
<property>
<name>user</name>
<value>{$USERNAME}</value>
</property>
<property>
<name>password</name>
<value>{$PASSWORD}</value>
</property>
</jdbc-properties>
</connection>
4.データ型変換定義が必要な場合は、データマッピング用XML構成ファイルの作成。
(例:/nz/export/ae/products/fluidquery/mapping.xml を作成)
<?xml version="1.0" encoding="UTF-8"?>
<dataMappings>
<mappings>
<mapping>
<sourceType>varchar2</sourceType>
<targetType>
<name>National_variable</name>
</targetType>
</mapping>
</mappings>
</dataMappings>
※Oracle側のvarcharで定義されたカラムに日本語が格納されている場合、Netezzaにそのまま持っていくと文字化けします。そのため、上記マッピング設定を事前に行うことで文字化けの問題を回避することが可能です。
Data Connector 構成
1.Data Connector(Fluid Query)の構成を下記コマンドにておこないます。
コマンド実行例: nz2ora.conf というData Connector構成ファイルを作成するコマンド
$ ./fqConfigure.sh --host 192.168.1.100 --port 1521 --config nz2ora
--provider oracle --service ORACLE --database nz --username nz
--dataTypesXmlPath /nz/export/ae/products/fluidquery/mapping.xml
--connectionXmlPath /nz/export/ae/products/fluidquery/nz2ora_gen.xml
※ディレクトリは/nz/export/ae/products/fluidquery直下で実行
Data Connector 登録
実行例) nz2ora.conf 構成ファイルから nz2oraというdata connector ファンクションを作成する際の例
1.Data Connector(Fluid Query)の登録をNetezza側のDBに対して登録します。
./fqRegister.sh --udtf nz2ora --db demodb --config nz2ora
Functions and credentials are successfully registered in database “demodb".
Data Connector 使用例
Data Connector APIは下記のとおり。(nz2oraはUDTF名のため変更できます。)
例) nz2ora(’DB名’, ’スキ-マ名.表名’,’SQL’,’カラム定義’,’文字サイズ’));
下記にData Connector使用例を記述します。
例1) Oracle側のDB名NZのSHOHIN表の結果をNetezza側に表示させる。
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL (nz2ora('NZ','SHOHIN'));
SHOHIN_CODE | SHOHIN_NAME | CATEGORY_S | CATEGORY_M | CATEGORY_L
-------------+------------------------------------------+--------------------------------+----------------------------+------------
2963 | **屋 ＥＥＸポテ****－ズ | その他パン（含む中華まん） | パン | デイリー
2961 | **屋 鰹**ぼろ | ふりかけ | ふりかけ | 加工食品
5252 | **－セ－ ソフティモスピ－ディクレンジ | クレンジング | クレンジング | 家庭用品
4589 | **サヒ ワンダゼロマックス | コーヒー飲料 | 飲料 | 加工食品
1374 | ** 沖縄黒糖（カチ割りタイプ） | 全国銘
例2) Oracle側で実行するクエリ条件を細かく指定した形で実行。結果のみをNetezza側に表示させる。
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL ( nz2ora('','','select s.shop_name as SHOP_NAME,sum(m.amount) AMOUNT from meisai_1G m, shop s where s.shop_code = m.shop_code group by s.shop_name, amount order by m.amount desc'));
SHOP_NAME | AMOUNT
--------------------+--------
いわ**店 | 81160
お台**店 | 202900
さい****店 | 227248
つく**店 | 133914
アリ****店 | 85218
シオ****ト店 | 121740
セン****ルタワー店 | 20290
例3) Oracle ⇒ Netezza間で列定義型を意図的にキャストしたい場合は第4引数に新しい列定義を記述する。(varchar⇒nvarchar型への変換例)
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL (nz2ora('','SHOHIN','','SHOHIN_CODE int, SHOHIN_NAME nvarchar(30), CATEGORY_S nvarchar(30),CATEGORY_M nvarchar(30), CATEGORY_L nvarchar(30)'));
例4) Oracle ⇒ Netezza間でストリング型のサイズを変更したい場合、第5引数にサイズを記入します。
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL (nz2ora('NZ','SHOHIN','',40));
例5) oracle DB側の表を元に同じ表をNetezza側に作成する。(Create table xxx as xxx)
DEMODB.ADMIN(ADMIN)=> CREATE TABLE NZ_SHOHIN as SELECT * FROM TABLE WITH FINAL (nz2ora('NZ','SHOHIN'));
例6) Netezza側の表とoracle DB側の表を結合させて結果を取得する。
SELECT n.n_name,o.o_orderstatus,count(*),
sum(cast(o.o_totalprice as decimal(15,2))),count(distinct c.c_custkey) FROM nation n
LEFT JOIN customer c ON n.n_nationkey = c.c_nationkey
LEFT JOIN (select * from table with final (nz2ora(‘NZ’,‘orders’)) ) o ON o.o_custkey = c_custkey
WHERE o.o_totalprice >= 100000 GROUP BY n.n_name, o.o_orderstatus;
例7) ビュー定義の中にData Connectorを組み入れて必要なデータを取得できるようにする。
DEMODB.ADMIN(ADMIN)=> CREATE OR REPLACE VIEW O_SHOHIN_V AS SELECT * FROM TABLE WITH FINAL ( nz2ora('NZ','SHOHIN'));
CREATE VIEW
DEMODB.ADMIN(ADMIN)=> select * from O_SHOHIN_V limit 3;
SHOHIN_CODE | SHOHIN_NAME | CATEGORY_S | CATEGORY_M | CATEGORY_L
-------------+----------------------------------------+----------------------------+--------------+------------
2963 | **屋 ＥＥＸ******チ－ズ | その他パン（含む中華まん） | パン | デイリー
2961 | **屋 ******** | ふりかけ | ふりかけ | 加工食品
5252 | **－セ－ ソフティモスピ*****クレンジ | クレンジング | クレンジング | 家庭用品
(3 rows)
以上です。なかなか便利ですねー。
ちょっとしたデータを連携するときにはもちろん威力を発揮しますし、Oracle DB側表をNetezza側に持ってきたい場合にも役に立ちそうです。次はSQL Serverとのデータ連携も試してみようかと思います。