FluidQuery1.6を使用してNetezza環境とSQLServer環境のデータ連携をやってみた。

###**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.png](https://qiita-image-store.s3.amazonaws.com/0/94920/37ba94e8-24a2-4917-4f58-88d02ce71eff.png)

![FluidQuery1.6_5.png](https://qiita-image-store.s3.amazonaws.com/0/94920/e4541e7b-a087-ee1c-1471-e5140760d448.png)

![FluidQuery1.6_3.png](https://qiita-image-store.s3.amazonaws.com/0/94920/77b1f88d-71d2-5868-6aa0-ec86ddf6fede.png)
###**FluidQuery1.6を使用してNetezza環境とOracle DB環境のデータ連携をやってみた**
***
**テスト実施にあたっての事前準備作業**
・Netezza環境の準備:Netezza Emulatorのダウンロードとセットアップ
参考:[PC上でメモリ5GBで動く! Netezza Software Emulatorを使ってみた]
        (http://http://qiita.com/ishida330/items/8a23ae32b34b9aa461a0)
・SQLServer環境の準備:SQLServer2014評価版とサンプルDBの作成
参考: [SQLServer2014 評価版ダウンロードサイト](https://www.microsoft.com/ja-jp/evalcenter/evaluate-sql-server-2014)　
・Netezza Analytics Packageの取得とインストール(テスト環境はINZA3.2.1を導入済)
  

今回は自前のクライアントPC環境にNetezza Emulatorを導入して、仮想Netezza環境をクライアントPC環境にセットアップしました。そしてSQLServer2014評価版もダウンロードして同じくPC上に導入。クライアントPC上でNetezza のDBとSQLServerを稼動させ、データ連携ができるか試してみました。

![FluidQuery1.6_4_SQL.png](https://qiita-image-store.s3.amazonaws.com/0/94920/85f27a3d-939b-4e7d-a488-8c997d6ef12e.png)

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

次に解凍されたインストールファイルディレクトリ以下にある下記コマンドを実行します。

```lang:
$./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ドライバを下記サイトよりダウンロードを行いインストールします。
[Microsoft JDBC Driver 4.2 for SQL Server、Microsoft JDBC Driver 4.1 for SQL Server、および Microsoft JDBC Driver 4.0 for SQL Server](
https://www.microsoft.com/ja-jp/download/details.aspx?id=11774)

2.JDBCドライバは下記ディレクトリに以下に配置、展開しておきます。
/nz/export/ae/products/fluidquery/libs/generic/MSSQL

3.XML構成ファイルの作成を行います。
(例:/nz/export/ae/products/fluidquery/mssql2014.xmlを作成)

```lang:
<?xml version="1.0" encoding="UTF-8"?>
<connection>
<driverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClass>
<classPath>/nz/export/ae/products/fluidquery/libs/generic/MSSQL/</classPath>
<connectionURL>jdbc:sqlserver://${HOSTNAME}:${PORT};database=${DATABASE};CHARSET=UTF8
</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 を作成)

```lang:
<?xml version="1.0" encoding="UTF-8"?>
<dataMappings>
<mappings>
<mapping>
<sourceType>varchar</sourceType>
<targetType>
<name>National_variable</name>
</targetType>
</mapping>
</mappings>
</dataMappings>
```
※SQLServer側のvarcharで定義されたカラムに日本語が格納されている場合、Netezzaにそのまま持っていくと文字化けします。そのため、上記マッピング設定を事前に行うことで文字化けの問題を回避することが可能です。

###**Data Connector 構成**
***
Data Connector(Fluid Query)の構成を下記コマンドにておこないます。
**コマンド実行例) nz2mssql.conf というData Connector構成ファイルを作成**

```lang:
$  ./fqConfigure.sh  --host 192.168.1.100 –port 1433 --provider generic 
    --service generic --connectionXmlPath 　mssql2014.xml –config nz2mssql.conf  
    --database NZ --username sa --password password --dataTypesXmlPath mapping.xml 
```
※ディレクトリは/nz/export/ae/products/fluidquery直下で実行

###**Data Connector 登録**
***
**実行例)nz2mssql.conf構成ファイルからnz2mssqlというdata connectorファンクションを作成**

```lang:
$ ./fqRegister.sh --udtf nz2mssql --db demodb --config nz2mssql.conf
Functions and credentials are successfully registered in database “demodb".
```

![FluidQuery1.6_7.png](https://qiita-image-store.s3.amazonaws.com/0/94920/7a8b4371-6434-823a-eba4-ab252f8c61c5.png)

###**Data Connector 使用例**
***
Data Connector APIは下記のとおり。(nz2mssqlはUDTF名のため変更できます。)
**例) nz2mssql(’DB名’, ’スキ-マ名.表名’,’SQL’,’カラム定義’,’文字サイズ’));**

![FluidQuery1.6_8.png](https://qiita-image-store.s3.amazonaws.com/0/94920/a7e330dc-43c1-256f-7abb-a4767565ecf7.png)

下記にData Connector使用例を記述します。
例1) MSSQL側のDB名NZのSHOHIN表の結果をNetezza側に表示させる。

```lang:
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL (nz2mssql('NZ','SHOHIN'));
 SHOHIN_CODE |               SHOHIN_NAME                |           CATEGORY_S           |         CATEGORY_M         | CATEGORY_L
-------------+------------------------------------------+--------------------------------+----------------------------+------------
        2963 | **屋  ＥＥＸポテ****－ズ                 | その他パン（含む中華まん）     | パン                       | デイリー
        2961 | **屋  鰹**ぼろ                           | ふりかけ                       | ふりかけ                   | 加工食品
        5252 | **－セ－  ソフティモスピ－ディクレンジ   | クレンジング                   | クレンジング               | 家庭用品
        4589 | **サヒ  ワンダゼロマックス               | コーヒー飲料                   | 飲料                       | 加工食品
        1374 | **  沖縄黒糖（カチ割りタイプ）           | 全国銘
```

例2) Oracle側で実行するクエリ条件を細かく指定した形で実行。結果のみをNetezza側に表示させる。

```lang:
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL ( nz2mssql('','','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) MSSQLServer ⇒ Netezza間で列定義型を意図的にキャストしたい場合は第4引数に新しい列定義を記述する。(varchar⇒nvarchar型への変換例)

```lang:
DEMODB.ADMIN(ADMIN)=> SELECT * FROM TABLE WITH FINAL (nz2mssql('','SHOHIN','','SHOHIN_CODE int, SHOHIN_NAME nvarchar(30),  CATEGORY_S  nvarchar(30),CATEGORY_M nvarchar(30), CATEGORY_L nvarchar(30)'));
```

例4) MSSQLServer ⇒ Netezza間でストリング型のサイズを変更したい場合、第5引数にサイズを記入します。

```lang:
DEMODB.ADMIN(ADMIN)=>  SELECT * FROM TABLE WITH FINAL (nz2mssql('NZ','SHOHIN','',40));
```

例5) MSSQLServer DB側の表を元に同じ表をNetezza側に作成する。(Create table xxx as xxx)

```lang:
DEMODB.ADMIN(ADMIN)=>  CREATE TABLE NZ_SHOHIN as SELECT * FROM TABLE WITH FINAL (nz2mssql('NZ','SHOHIN'));
```

例6) Netezza側の表とoracle DB側の表を結合させて結果を取得する。

```lang:
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 (nz2mssql(‘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を組み入れて必要なデータを取得できるようにする。

```lang:
DEMODB.ADMIN(ADMIN)=> CREATE OR REPLACE VIEW O_SHOHIN_V AS SELECT * FROM TABLE WITH FINAL ( nz2mssql('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同様にSQLServerでもちゃんと動きました。便利ですね。

ちょっとしたデータを連携するときにはもちろん威力を発揮しますし、Oracle DB側表をNetezza側に持ってきたい場合にも役に立ちそうです。次はPostgreSQLとのデータ連携も試してみようかと思います。