Join External Parquet Data and Database Tables | NOS | Teradata Vantage - 17.10 - Joining External Data and Database Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1214-171K
Language
English (United States)

In the following example, create and populate a database table, create a foreign table, and join the two tables.

    Prerequisite

  1. To run NOS-related commands, log on to the database as a user with the required privileges.
  2. If the rivernames table already exists, skip the rest of the steps in the Prerequisite section.
  3. Create the database table:
    In the absence of a local database table to represent the database side of the object store-to-database join, a simulation is required. Typically, you would join to an existing table that is already in your environment and these simulation steps would not be needed.
    As a solution a small dimension table has been placed in an external object store to:
    • Make the join practical
    • Make sure that all users trying this example have the same version of the table data
    CREATE SET TABLE rivernames(
    site_no CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
    name CHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
    UNIQUE PRIMARY INDEX ( site_no ) ;
  4. Create the foreign table or ask your database administrator to create the foreign table to be used for populating the dimension table:
    CREATE FOREIGN TABLE nos_rivernames
    , EXTERNAL SECURITY authorization_object
    USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/RIVERS/rivers.csv') );

    Replace LOCATION with your path to RIVERS/rivers.csv.

  5. Insert data into the dimension table:
    INSERT INTO rivernames
    SELECT CAST(site_no AS CHAR(8)), name
    FROM nos_rivernames;
  6. Join the Dimension Table and External Data

  7. If not already done, create the foreign table or ask your database administrator to create the foreign table called riverflow_parquet. See Setting Up to Run Parquet Examples.
  8. Join the dimension and foreign tables:
    SELECT DISTINCT name(CHAR(100))
    FROM riverflow_parquet rf, rivernames rn
    WHERE rf.site_no = rn.site_no
    AND rf.Precipitation > 0.1
    ORDER BY 1;

    Result:

    name
    ---------------------------------
    GILA RIVER AT KELVIN
    LITTLE COLORADO RIVER AT WOODRUFF
    NEW RIVER NEAR ROCK SPRINGS
    POLACCA WASH NEAR SECOND MESA
    PUERCO RIVER NEAR CHAMBERS
    SALT RIVER NEAR CHRYSOTILE