Netezzaフルマネージド・サービス(NPSaaS)でデータレイクハウス（AWS S3のParquetファイルにアクセス）

# 目次
1. はじめに
2. 前提
3. 手順
    1. AWS S3のアクセスキー等を取得
    2. Parquetファイルをダウンロード (Yellow taxi trip data)
    3. ParquetファイルをS3バケットに保存
    4. nzsqlを導入（Netezza SQLのコマンドライン・インターフェース）
    5. （オプション）NPSaaSのユーザーとデータベースを作成
    6. NPSaaSでParquetファイルの外部表を作成
    7. NPSaaSからParquetファイルのデータをSELECT
4. エラー例
5. 参考資料


# 1. はじめに
オブジェクト・ストレージをデータウェアハウス(DWH)のデータ待避場所として活用できます。オブジェクト・ストレージ上のファイル・フォーマットとしてテキスト・ファイル（CSV等）よりもパフォーマンスが良いバイナリ・ファイルを使って、例えばDWH内部ストレージの最新データと、オブジェクト・ストレージの過去データを組み合わせて分析を行う、といったことができます。

本記事ではバイナリ・フォーマットの1つであるParquetファイルをNetezzaフルマネージド・サービス(NPSaaS)から読む方法を説明します。方法としてはオブジェクト・ストレージを用いる方法とデータレイクハウス製品(watsonx.data)を用いる方法があり、本記事では前者の方法を紹介します。

Parquetファイルは列単位でデータを保持するので、CSVファイルのように全列を読む必要がなく、また列単位で圧縮された状態でオブジェクト・ストーレジに保存されます。このためパフォーマンスがCSVファイルより良く、またファイルの大きさも削減できる、といった特徴を持ちます。

本記事の内容は[NPSaaSマニュアル](https://cloud.ibm.com/docs/netezza?topic=netezza-overview_singularity&locale=en)に沿ったものですが、アクセスキーの値やURLをどこから取得するのか、マニュアルだけではわかりにくい点を具体例によって補足します。

Parquetファイルの例としてニューヨーク市のタクシー運行データ(Yellow Taxi Trip Records)を使います。日本で公開されているParquetファイルを探しましたが、本記事作成時点（2023年11月）では[e-Govデータポータル](https://data.e-gov.go.jp/data/dataset/?organization=default)等で見当たらないため、NPSaaSマニュアルと同様にYellow Taxi Trip Recordsを使います。


# 2. 前提
- AWSアカウントが必要です。
S3を利用するために必要です。


- AWS上のNetezzaフルマネージドサービス(NPSaaS)のデータベース接続情報が必要です。
    - データベースのエンドポイント(URL)、ユーザー名、パスワードが必要です。確認方法を次の資料にまとめました。
	    - [Qiita記事：Netezzaフルマネージド・サービス(NPSaaS)にアクセスするための資格情報確認](https://qiita.com/yamasakk/items/1b204a926c6f4295a3f8)

	- ユーザーが外部表作成権限CREATE EXTERNAL TABLEを持っている必要があります。（参考：[NPSaaSマニュアル](https://www.ibm.com/docs/en/netezza?topic=et-create-external-table-command-2)）
	この権限をユーザーに付与する方法を本記事の手順5に記載しました。

    - 注意事項
        NPSaaSをAWSまたはAzureで利用できますが、Azureにおいては本記事で紹介する機能にまだ対応していません。（参考：[NPSaaSマニュアル](https://cloud.ibm.com/docs/netezza?topic=netezza-overview_singularity&locale=en)）
    - 参考
    [NPSaaSインスタンス作成画面](https://cloud.ibm.com/nzsaas/create)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/700959/babe4735-8ec8-6715-c7c1-a53bf925fc5a.png)


# 3. 手順1) AWS S3のアクセスキー等を取得
NPSaaSからS3にアクセスするとき、次の情報が必要です。
    - アクセスキー
    - シークレット・アクセスキー
    - リージョン

例えば次のQiita記事の手順1と2を参考にして、上の情報を取得します。
- [Db2 Warehouse on Cloud の外部表をS3上に作成する方法](https://qiita.com/yamasakk/items/fbec6e918d1edc9b8db3)


# 3. 手順2) Parquetファイルをダウンロード (Yellow taxi trip data)
次のページからダウンロードします。
- [TLC Trip Record Data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page)

Yellow Taxi Trip Records (PARQUET)を2つダウンロードします。本記事では例として2023年1月と2022年1月のファイルを使います。なお、2023年2月以降のファイルを使うと、本記事作成時点（2023年11月）ではエラー`Unsupported column type`が起きてしまいました。将来、解消すると思います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/700959/9320f467-3d64-9af2-4294-4b02539cf91c.png)
ダウンロードしたファイルの名前は次のようになります。
- yellow_tripdata_2022-01.parquet
- yellow_tripdata_2023-01.parquet


# 3. 手順3) ParquetファイルをS3バケットに保存
バケットを作成します。例えば次のQiita記事の手順3を参考にしてください。
- [Db2 Warehouse on Cloud の外部表をS3上に作成する方法](https://qiita.com/yamasakk/items/fbec6e918d1edc9b8db3)

バケット名は任意ですが、本記事では`bt20231117b`とします。このバケットにフォルダを作成します。フォルダ名も任意ですが、本記事では`yellowtaxi`とします。
https://s3.console.aws.amazon.com/s3/buckets
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/700959/d4f85fb2-0e64-5c1e-dc68-68d4f5fc6b7a.png)
このフォルダに前節でダウンロードしたファイル2つを保存します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/700959/8d16f992-5ed7-a0ac-99a2-f40f4190715f.png)


# 3. 手順4) nzsqlを導入（Netezza SQLのコマンドライン・インターフェース）
SQLをNPSaaS Web画面から実行することもできますが、本記事ではコマンドと出力をテキストとしてコピーすることが容易なnzsqlを使います。

Windows環境に導入する場合、例えば次の記事を参考にしてください。
- Qiita記事：[高速データウェアハウス Netezza にクライアント接続する（Windows編）](https://qiita.com/yamasakk/items/5893de8ad9b2d058f615)

Linux環境に導入する場合も上の記事と同様です。該当マニュアルはこちらです。
- NPSaaSマニュアル：[Installing and uninstalling the client tools software](https://www.ibm.com/docs/en/netezza?topic=dls-installing-uninstalling-client-tools-software-2)

Windows環境のコマンドプロンプト、あるいはLinux環境のターミナルを起動します。本記事ではWindows環境を使います。

次のようにnzsqlを起動してNPSaaSに接続します。`-h`オプションにNPSaaSデータベースのエンドポイントを指定します。本記事で用いるユーザー名を`us_smpl`、データベース名を`db_smpl`、スキーマ名を`sc_smpl`とします。
```
C:\Users\xxxx>nzsql -h xxxx.data-warehouse.cloud.ibm.com -d db_smpl -u us_smpl -pw "xxxxxxxxxxxxxxxx"
Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type:  \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL enabled connection. Cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256

DB_SMPL.SC_SMPL(US_SMPL)=>
```


# 3. 手順5) （オプション）NPSaaSのユーザーとデータベースを作成
新たにユーザーを作成して外部表作成権限を付与する場合は、管理ユーザー`admin`として次のようにコマンドを実行します。なお、nzsqlのプロンプト`SYSTEM.ADMIN(ADMIN)=>`を簡略化して`=>`と表示します。
```
C:\Users\xxxx>nzsql -h xxxx.data-warehouse.cloud.ibm.com -d system -u admin -pw "xxxxxxxxxxxxxxxx"
=> CREATE USER us_smpl WITH PASSWORD 'xxxxxxxxxxxxxxxx';
=> GRANT EXTERNAL TABLE TO us_smpl;
=> \dpu us_smpl
                                             User object permissions for user 'US_SMPL'
 Database Name | Schema Name | Object Name | L S I U D T L A D B L G O E C R X A | D G U S T E X Q Y V M I B R C S H F A L P N S R
---------------+-------------+-------------+-------------------------------------+-------------------------------------------------
 GLOBAL        | GLOBAL      | GLOBAL      |                                     |             X
(1 rows)
Object Privileges
    (L)ist (S)elect (I)nsert (U)pdate (D)elete (T)runcate (L)ock
    (A)lter (D)rop a(B)ort (L)oad (G)enstats Gr(O)om (E)xecute
    Label-A(C)cess Label-(R)estrict Label-E(X)pand Execute-(A)s
Administration Privilege
    (D)atabase (G)roup/Role (U)ser (S)chema (T)able T(E)mp E(X)ternal
    Se(Q)uence S(Y)nonym (V)iew (M)aterialized View (I)ndex (B)ackup
    (R)estore va(C)uum (S)ystem (H)ardware (F)unction (A)ggregate
    (L)ibrary (P)rocedure U(N)fence (S)ecurity Scheduler (R)ule
```
データベース`db_smpl`を作成して上のユーザー`us_smpl`をオーナーにします。
```
=> CREATE DATABASE db_smpl;
=> ALTER DATABASE db_smpl OWNER TO us_smpl;
```

# 3. 手順6) NPSaaSでParquetファイルの外部表を作成
ユーザー`us_smpl`でデータベース`db_smpl`に接続して、作業用スキーマ`sc_smpl`を作成します。
```
C:\Users\xxxx>nzsql -h xxxx.data-warehouse.cloud.ibm.com -d db_smpl -u us_smpl -pw "xxxxxxxxxxxxxxxx"
=> CREATE SCHEMA db_smpl.sc_smpl;
=> ALTER DATABASE db_smpl SET DEFAULT SCHEMA sc_smpl;
=> SET SCHEMA sc_smpl;
```

次のマニュアルを参考にして手順を進めます。
- NPSaaSマニュアル：[Querying data from data lakes](https://cloud.ibm.com/docs/netezza?topic=netezza-querying_singularity&locale=en)

外部表を作成する前にexternal datasourceを作成してアクセスキー等を設定します。External datasourceの名前は任意ですが、本記事では`ds_smpl`とします。4つのパラメータの中でACCESSKEYIDとSECRETACCESSKEYに手順1で取得した値を設定します。BUCKETに手順2で作成したバケット名を指定します。REGIONに手順1で取得したリージョンを設定します（本記事では`ap-northeast-1`）。
```
=> SET ENABLE_EXTERNAL_DATASOURCE = 1;

=> CREATE EXTERNAL DATASOURCE ds_smpl ON AWSS3 USING
   (
       ACCESSKEYID 'xxxxxxxxxxxxxxxxxxxx'
       SECRETACCESSKEY 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
       BUCKET 'bt20231117b'
       REGION 'ap-northeast-1'
   );

=> SELECT * FROM _V_EXTERNAL_DATA_SOURCE;
 OBJID  | OBJDB  | SCHEMAID |   OBJNAME    |       KEY       |                                           VALUE
--------+--------+----------+--------------+-----------------+-------------------------------------------------------------------------------------------
 311212 | 310265 |   310598 | DS_SMPL      | from            | awss3
 311212 | 310265 |   310598 | DS_SMPL      | region          | ap-northeast-1
 311212 | 310265 |   310598 | DS_SMPL      | bucket          | bt20231117b
 311212 | 310265 |   310598 | DS_SMPL      | bucket          | xxxxxxxxxxx
 311212 | 310265 |   310598 | DS_SMPL      | secretaccesskey | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 311212 | 310265 |   310598 | DS_SMPL      | accesskeyid     | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
```
ダウンロードしたParquetファイルを対象として外部表を作成します。
```
=> CREATE EXTERNAL TABLE s3_yt202201 ON ds_smpl USING (
       DATAOBJECT ('yellowtaxi/yellow_tripdata_2022-01.parquet')
       FORMAT 'PARQUET'
   );

=> CREATE EXTERNAL TABLE s3_yt202301 ON ds_smpl USING (
       DATAOBJECT ('yellowtaxi/yellow_tripdata_2023-01.parquet')
       FORMAT 'PARQUET'
   );

=> SELECT b.objname, a.format, a.remotesource FROM _t_external a INNER JOIN _t_object b
     ON a.relid=b.objid
     ORDER BY b.objname;
   OBJNAME   | FORMAT  | REMOTESOURCE
-------------+---------+--------------
 S3_YT202201 | PARQUET | S3
 S3_YT202301 | PARQUET | S3
```


# 3. 手順7) NPSaaSからParquetファイルのデータをSELECT
2022年1月のparquetファイルをSELECTしてみます。
```
=> SELECT count(*) FROM s3_yt202201;
  COUNT
---------
 2463931

=> SELECT * FROM s3_yt202201 LIMIT 2;
 VENDORID | TPEP_PICKUP_DATETIME | TPEP_DROPOFF_DATETIME | PASSENGER_COUNT | TRIP_DISTANCE | RATECODEID | STORE_AND_FWD_FLAG | PULOCATIONID | DOLOCATIONID | P
AYMENT_TYPE | FARE_AMOUNT | EXTRA | MTA_TAX | TIP_AMOUNT | TOLLS_AMOUNT | IMPROVEMENT_SURCHARGE | TOTAL_AMOUNT | CONGESTION_SURCHARGE | AIRPORT_FEE
----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+--------------+--
------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+----------------------+-------------
        1 | 2022-01-01 00:35:40  | 2022-01-01 00:53:29   |               2 |           3.8 |          1 | N                  |          142 |          236 |
          1 |        14.5 |     3 |     0.5 |       3.65 |            0 |                   0.3 |        21.95 |                  2.5 |           0
        1 | 2022-01-01 00:33:43  | 2022-01-01 00:42:07   |               1 |           2.1 |          1 | N                  |          236 |           42 |
          1 |           8 |   0.5 |     0.5 |          4 |            0 |                   0.3 |         13.3 |                    0 |           0
```
次のマニュアルを参考にして、2つ目のparquetファイル（2023年1月）をロードします。
- NPSaaSマニュアル：[Ingesting data from data lakes](https://cloud.ibm.com/docs/netezza?topic=netezza-ingest_singularity&locale=en)

```
=> CREATE TABLE yt202301 AS SELECT * FROM s3_yt202301;
INSERT 0 3066766

=> select * from yt202301 limit 2;
 VENDORID | TPEP_PICKUP_DATETIME | TPEP_DROPOFF_DATETIME | PASSENGER_COUNT | TRIP_DISTANCE | RATECODEID | STORE_AND_FWD_FLAG | PULOCATIONID | DOLOCATIONID | P
AYMENT_TYPE | FARE_AMOUNT | EXTRA | MTA_TAX | TIP_AMOUNT | TOLLS_AMOUNT | IMPROVEMENT_SURCHARGE | TOTAL_AMOUNT | CONGESTION_SURCHARGE | AIRPORT_FEE
----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+--------------+--
------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+----------------------+-------------
        2 | 2023-01-01 00:32:10  | 2023-01-01 00:40:36   |               1 |          0.97 |          1 | N                  |          161 |          141 |
          2 |         9.3 |     1 |     0.5 |          0 |            0 |                     1 |         14.3 |                  2.5 |           0
        2 | 2023-01-01 00:55:08  | 2023-01-01 01:01:27   |               1 |           1.1 |          1 | N                  |           43 |          237 |
          1 |         7.9 |     1 |     0.5 |          4 |            0 |                     1 |         16.9 |                  2.5 |           0
```
次のマニュアルを参考にして、上でロードした表（2023年1月）と外部表（2022年1月）をUNIONして、月毎の乗客数を集計します。
- NPSaaSマニュアル：[Merging and querying data](https://cloud.ibm.com/docs/netezza?topic=netezza-merging_singularity&locale=en)

```
=> SELECT
       extract(year from tpep_pickup_datetime) AS year,
       extract(month from tpep_pickup_datetime) AS month,
       sum(passenger_count) AS passenger_count
   FROM (
       SELECT tpep_pickup_datetime, passenger_count FROM yt202301
       UNION ALL
       SELECT tpep_pickup_datetime, passenger_count FROM s3_yt202201
   ) AS a
   GROUP BY year,month ORDER BY year,month;

 YEAR | MONTH | PASSENGER_COUNT
------+-------+-----------------
 2008 |    12 |              12
 2009 |     1 |              11
 2021 |    12 |              62
 2022 |     1 |         3324042
 2022 |     2 |              21
 2022 |     3 |              11
 2022 |     4 |               2
 2022 |     5 |               8
 2022 |    10 |              13
 2022 |    12 |              29
 2023 |     1 |         4080755
 2023 |     2 |              16
```
2022年1月と2023年1月以外のレコードが含まれていますが、これは無視することにして、2023年1月の方が乗客が増えています。最新データだけをNPSaaSに登録して、古いデータをオブジェクト・ストレージ(S3)に保持している時、このように両者を組み合わせた分析が可能となります。

なお、NPSaaSの表を外部表経由でParquetファイルにアンロード（エクスポート）することはできません。エラー`Path does not exist`が起きます。アンロード対象となるのはCSVファイル等の区切り文字を用いるテキスト・ファイルです。
    - [参考：NPSaaSマニュアル](https://www.ibm.com/docs/en/netezza?topic=et-loads-unloads-2)
    You can unload data from a user table into an external table and load data from an external table into a user table by using the text-delimited format.


# 4. エラー例
アクセスキー、シークレット・アクセスキーが間違っている場合、次のエラーメッセージが表示されます。
```
=> SELECT * FROM s3_yt202301 LIMIT 2;
ERROR:  Failed to open bt20231117b/yellowtaxi/yellow_tripdata_2023-01.parquet: When reading information for key 'yellowtaxi/yellow_tripdata_2023-01.parquet' in bucket 'bt20231117b': AWS Error [code 15]: No response body.
```
バケット名、フォルダ名、ファイル名が間違っている場合、次のエラーメッセージが表示されます。
```
=> SELECT * FROM s3_yt202301 LIMIT 2;
ERROR:  Failed to open bt20231117c/yellowtaxi/yellow_tripdata_2023-01.parquet: Path does not exist 'bt20231117c/yellowtaxi/yellow_tripdata_2023-01.parquet'
```
リージョンが間違っている場合、次のエラーメッセージが表示されます。
```
=> SELECT * FROM s3_yt202301 LIMIT 2;
ERROR:  Failed to open bt20231117b/yellowtaxi/yellow_tripdata_2023-00.parquet: When reading information for key 'yellowtaxi/yellow_tripdata_2023-00.parquet' in bucket 'bt20231117b': AWS Error [code 99]: curlCode: 6, Couldn't resolve host name
```


# 5. 参考資料
- NPSaaSマニュアル
    - Installing and uninstalling the client tools software
https://www.ibm.com/docs/en/netezza?topic=dls-installing-uninstalling-client-tools-software-2

	- CREATE EXTERNAL TABLE command
https://www.ibm.com/docs/en/netezza?topic=et-create-external-table-command-2

    - Querying data from data lakes - Overview
https://cloud.ibm.com/docs/netezza?topic=netezza-overview_singularity&locale=en

    - Querying data from data lakes
https://cloud.ibm.com/docs/netezza?topic=netezza-querying_singularity&locale=en

    - Ingesting data from data lakes
https://cloud.ibm.com/docs/netezza?topic=netezza-ingest_singularity&locale=en

    - Merging and querying data
https://cloud.ibm.com/docs/netezza?topic=netezza-merging_singularity&locale=en

	- Loads and unloads using external tables
https://www.ibm.com/docs/en/netezza?topic=et-loads-unloads-2

    補足：英文マニュアルの方が日本語よりも新しい場合があるため、英文マニュアルのURLを表示しています。

- Qiita記事
    - Netezzaフルマネージド・サービス(NPSaaS)にアクセスするための資格情報確認
https://qiita.com/yamasakk/items/1b204a926c6f4295a3f8

    - Db2 Warehouse on Cloud の外部表をS3上に作成する方法
https://qiita.com/yamasakk/items/fbec6e918d1edc9b8db3

    - 高速データウェアハウス Netezza にクライアント接続する（Windows編）
https://qiita.com/yamasakk/items/5893de8ad9b2d058f615

- Yellow taxi trip data（Parquetファイル）
https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

- NPSaaSインスタンス作成画面
https://cloud.ibm.com/nzsaas/create