目次
- はじめに
- 前提
- 手順
- AWS S3のアクセスキー等を取得
- Parquetファイルをダウンロード (Yellow taxi trip data)
- ParquetファイルをS3バケットに保存
- nzsqlを導入（Netezza SQLのコマンドライン・インターフェース）
- （オプション）NPSaaSのユーザーとデータベースを作成
- NPSaaSでParquetファイルの外部表を作成
- NPSaaSからParquetファイルのデータをSELECT
- エラー例
- 参考資料
1. はじめに
オブジェクト・ストレージをデータウェアハウス(DWH)のデータ待避場所として活用できます。オブジェクト・ストレージ上のファイル・フォーマットとしてテキスト・ファイル（CSV等）よりもパフォーマンスが良いバイナリ・ファイルを使って、例えばDWH内部ストレージの最新データと、オブジェクト・ストレージの過去データを組み合わせて分析を行う、といったことができます。
本記事ではバイナリ・フォーマットの1つであるParquetファイルをNetezzaフルマネージド・サービス(NPSaaS)から読む方法を説明します。方法としてはオブジェクト・ストレージを用いる方法とデータレイクハウス製品(watsonx.data)を用いる方法があり、本記事では前者の方法を紹介します。
Parquetファイルは列単位でデータを保持するので、CSVファイルのように全列を読む必要がなく、また列単位で圧縮された状態でオブジェクト・ストーレジに保存されます。このためパフォーマンスがCSVファイルより良く、またファイルの大きさも削減できる、といった特徴を持ちます。
本記事の内容はNPSaaSマニュアルに沿ったものですが、アクセスキーの値やURLをどこから取得するのか、マニュアルだけではわかりにくい点を具体例によって補足します。
Parquetファイルの例としてニューヨーク市のタクシー運行データ(Yellow Taxi Trip Records)を使います。日本で公開されているParquetファイルを探しましたが、本記事作成時点（2023年11月）ではe-Govデータポータル等で見当たらないため、NPSaaSマニュアルと同様にYellow Taxi Trip Recordsを使います。
2. 前提
-
AWSアカウントが必要です。
S3を利用するために必要です。 -
AWS上のNetezzaフルマネージドサービス(NPSaaS)のデータベース接続情報が必要です。
-
データベースのエンドポイント(URL)、ユーザー名、パスワードが必要です。確認方法を次の資料にまとめました。
-
ユーザーが外部表作成権限CREATE EXTERNAL TABLEを持っている必要があります。（参考：NPSaaSマニュアル）
この権限をユーザーに付与する方法を本記事の手順5に記載しました。 -
注意事項
NPSaaSをAWSまたはAzureで利用できますが、Azureにおいては本記事で紹介する機能にまだ対応していません。（参考：NPSaaSマニュアル）
-
3. 手順1) AWS S3のアクセスキー等を取得
NPSaaSからS3にアクセスするとき、次の情報が必要です。
- アクセスキー
- シークレット・アクセスキー
- リージョン
例えば次のQiita記事の手順1と2を参考にして、上の情報を取得します。
3. 手順2) Parquetファイルをダウンロード (Yellow taxi trip data)
次のページからダウンロードします。
Yellow Taxi Trip Records (PARQUET)を2つダウンロードします。本記事では例として2023年1月と2022年1月のファイルを使います。なお、2023年2月以降のファイルを使うと、本記事作成時点（2023年11月）ではエラーUnsupported column typeが起きてしまいました。将来、解消すると思います。
ダウンロードしたファイルの名前は次のようになります。
- yellow_tripdata_2022-01.parquet
- yellow_tripdata_2023-01.parquet
3. 手順3) ParquetファイルをS3バケットに保存
バケットを作成します。例えば次のQiita記事の手順3を参考にしてください。
バケット名は任意ですが、本記事ではbt20231117bとします。このバケットにフォルダを作成します。フォルダ名も任意ですが、本記事ではyellowtaxiとします。
https://s3.console.aws.amazon.com/s3/buckets
このフォルダに前節でダウンロードしたファイル2つを保存します。
3. 手順4) nzsqlを導入（Netezza SQLのコマンドライン・インターフェース）
SQLをNPSaaS Web画面から実行することもできますが、本記事ではコマンドと出力をテキストとしてコピーすることが容易なnzsqlを使います。
Windows環境に導入する場合、例えば次の記事を参考にしてください。
Linux環境に導入する場合も上の記事と同様です。該当マニュアルはこちらです。
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
外部表を作成する前に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
=> 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
=> 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マニュアル
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