LoginSignup
0
0
@yamasakk(Kazutaka Yamasaki)

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

Last updated at Posted at 2023-11-26

目次

  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)を用いる方法があり、本記事では前者の方法を紹介します。この機能はまだtechnology preview（ベータ版）です。

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

本記事の内容はNPSaaSマニュアルに沿ったものですが、アクセスキーの値やURLをどこから取得するのか、マニュアルだけではわかりにくい点を具体例によって補足します。

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

2. 前提

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が起きてしまいました。将来、解消すると思います。
image.png
ダウンロードしたファイルの名前は次のようになります。

  • 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
image.png
このフォルダに前節でダウンロードしたファイル2つを保存します。
image.png

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;

次のマニュアルを参考にして手順を進めます。

外部表を作成する前に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月）をロードします。

=> 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して、月毎の乗客数を集計します。

=> 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. 参考資料

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0