从 watsonx.data 查询数据

准备工作

在示例中，使用的是 2021 年 1 月和 2022 年 1 月公开的黄色出租车“纽约出租车之旅记录数据 和”{: external}。 要遵循此示例，请确保数据位于可访问的S3存储桶中，并且表已加载到watsonx.data中的HiveMetastore 服务器 (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 数据源选项和身份验证选项的详细信息，请参阅 创建外部数据源。

一个数据库只能分配一个数据源，如果需要另一个数据源，则必须创建另一个数据库。

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)