Query dei dati da watsonx.data

Prima di iniziare

Negli esempi, vengono utilizzati i dati di registrazione dellecorse dei taxi di New York disponibili pubblicamente, relativi al " {: external} dei taxi gialli nel gennaio 2021 e 2022. Per seguire questo esempio, assicurarsi che i dati siano in un bucket S3 accessibile e che la tabella sia stata caricata in watsonx.data in una tabella Apache Iceberg nel server Hive Metastore (HMS).

1. Creare un database utilizzando il metastoreuri richiesto.

Le origini dati esterne consentono all'amministratore di concedere l'accesso a S3/Azure senza fornire le chiavi direttamente all'utente.

EsempioS3):

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

EsempioAzure):

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

Per ulteriori dettagli sulle opzioni di origine dei dati e sulle opzioni di autenticazione per Azure, consultare CREATE EXTERNAL DATASOURCE.

A un database può essere assegnata una sola fonte di dati; se si desidera un'altra fonte di dati, è necessario creare un altro database datalake.

2. Connettersi al database.

LOCALDB.ADMIN(ADMIN)=> \c mylake

Sei ora connesso al database mylake.

3. Elencare gli schemi disponibili.

MYLAKE.NETEZZA_SCHEMA(ADMIN)=> show schema;

Per impostazione predefinita, si ottiene la connessione a uno schema riservato denominato NETEZZA_SCHEMA.

Output:

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. Dall'elenco degli schemi, impostare lo schema a cui si desidera connettersi.

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

Puoi anche eseguire una query dei tuoi dati utilizzando un percorso completo SELECT * da mydb.myschema.mytable.

5. Elencare le tabelle disponibili.

MYLAKE.TAXIDATA(ADMIN)=> show table;

Output:

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. Selezionare * da la tabella richiesta.

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

Output:

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)

  • Per identificare il numero totale di passeggeri che hanno viaggiato con taxi a New York nel mese di gennaio 2022, eseguire:

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

    Output:

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

  • Per identificare il fornitore che ha avuto il maggior numero di passeggeri tra le 1:00 AM e le 6:00 AM, eseguire:

    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;

    Output:

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