Join External Parquet Data and Database Tables | NOS | Teradata Vantage - 17.05 - 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.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1214-175K
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
    • Ensure 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(60) 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 DEFINER TRUSTED DefAuth
    (   Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
        PAYLOAD DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV
    )
    USING (
        LOCATION('/s3/td-usgs.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 payload..site_no, payload..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(60))
      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