watsonx.data からのデータの照会

開始前に

例では、2021年1月と2022年1月のイエロー・タクシーに関する、一般に入手可能な ニューヨークのタクシー走行記録データ「{: external}」が使用されている。 この例に従うには、データがアクセス可能なS3バケットにあり、テーブルがwatsonx.dataにロードされ、Hiveメタストア・サーバー（HMS）のApacheIcebergテーブルにロードされていることを確認します。

1. 必要な metastoreuri を使用してデータベースを作成します。

外部データソースは、管理者がユーザーにキーを直接提供することなく、S3/Azureへのアクセスを許可することを可能にする。

例S3）：

LOCALDB.ADMIN(ADMIN)=> create database mylake with metastoreuri 'thrift://mymetastoreserverhostname:9083' catalogtype 'hive' on awss3 using ( ACCESSKEYID 'xxxx' SECRETACCESSKEY 'xxxx' BUCKET 'example-bucket' REGION 'us-east-1');
NOTICE:  589 tables from the datalake are available in MYLAKE

例Azure）：

create database mylake with metastoreuri 'thrift://mymetastoreserverhostname:9083' catalogtype 'hive' on azureblob using (ACCOUNT 'xxxx' KEY 'xxxx' CONTAINER 'example_container');
NOTICE:  589 tables from the datalake are available in MYLAKE

Azureのデータソースオプションと認証オプションの詳細については、CREATE EXTERNAL DATASOURCE を参照してください。

1つのデータベースには1つのデータソースしか割り当てることができませんので、別のデータソースが必要な場合は、別のdatalakeデータベースを作成する必要があります。

2.データベースに接続する。

LOCALDB.ADMIN(ADMIN)=> \c mylake

これで、 mylake データベースに接続できました。

3. 使用可能なスキーマをリストします。

MYLAKE.NETEZZA_SCHEMA(ADMIN)=> show schema;

デフォルトでは、 NETEZZA_SCHEMA という予約済みスキーマに接続されます。

出力:

DATABASE |                   SCHEMA                   | OWNER
----------+--------------------------------------------+-------
MYLAKE   | DEFAULT                                    | ADMIN
MYLAKE   | DEFINITION_SCHEMA                          | ADMIN
MYLAKE   | DEMO                                       | ADMIN
MYLAKE   | INFORMATION_SCHEMA                         | ADMIN
MYLAKE   | NETEZZA_SCHEMA                             | ADMIN
MYLAKE   | TAXIDATA                                   | ADMIN
MYLAKE   | TEST                                       | ADMIN
(7 rows)

4. スキーマ・リストから、接続先のスキーマを設定します。

MYLAKE.NETEZZA_SCHEMA(ADMIN)=> set schema taxidata;
SET SCHEMA

絶対パス **mydb.myschema.mytable からの SELECT ***を使用してデータを照会することもできます。

5. 使用可能な表をリストします。

MYLAKE.TAXIDATA(ADMIN)=> show table;

出力:

DATABASE |  SCHEMA  |          TABLE           |      TYPE      | OWNER
----------+----------+--------------------------+----------------+-------
MYLAKE   | TAXIDATA | YELLOW_TAXI_JANUARY_2022 | DATALAKE TABLE | ADMIN
MYLAKE   | TAXIDATA | YELLOW_TAXI_JANUARY_2021 | DATALAKE TABLE | ADMIN
(2 rows)

6. 必要なテーブルから *を選択します。

MYLAKE.TAXIDATA(ADMIN)=> select * from YELLOW_TAXI_JANUARY_2021 limit 1;

出力:

VENDORID | TPEP_PICKUP_DATETIME | TPEP_DROPOFF_DATETIME | PASSENGER_COUNT | TRIP_DISTANCE | RATECODEID | STORE_AND_FWD_FLAG | PULOCATIONID | DO
LOCATIONID | PAYMENT_TYPE | FARE_AMOUNT | EXTRA | MTA_TAX | TIP_AMOUNT | TOLLS_AMOUNT | IMPROVEMENT_SURCHARGE | TOTAL_AMOUNT | CONGESTION_SURCHARGE |AIRPORT_FEE
----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+---
-----------+--------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+------------------
----+-------------
        1 | 2021-01-01 00:30:10  | 2021-01-01 00:36:12   |               1 |           2.1 |          1 | N                  |          142 |
        43 |            2 |           8 |     3 |     0.5 |          0 |            0 |                   0.3 |         11.8 |
2.5 |
(1 row)

  • 2022 年 1 月にニューヨークのタクシーで旅行した乗客の総数を確認するには、以下を実行します。

    MYLAKE.TAXIDATA(ADMIN)=> select sum(PASSENGER_COUNT) FROM YELLOW_TAXI_JANUARY_2022;

    出力:

    SUM
---------
3324167
(1 row)

  • 午前 1:00 から午前 6:00 の間に乗客が最も多かったベンダーを識別するには、以下を実行します。

    MYLAKE.TAXIDATA(ADMIN)=> SELECT VENDORID, SUM(PASSENGER_COUNT) as "passengers" FROM YELLOW_TAXI_JANUARY_2022 WHERE TPEP_PICKUP_DATETIME::time > '1:00am'AND "TPEP_PICKUP_DATETIME"::time < '6:00am' GROUP BY VENDORID;

    出力:

    VENDORID | passengers
----------+------------
       2 |     122251
       1 |      40807
       6 |
       5 |
(4 rows)