Join External JSON 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. Populate 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 it does not exist, create the foreign table or ask your database administrator to create the foreign table called riverflow_json. See Setting Up to Run JSON Examples.
  8. Join the dimension and foreign tables:
    SELECT DISTINCT name(CHAR(60))
      FROM riverflow_json rf, rivernames rn
    WHERE rf.payload.site_no = rn.site_no
      AND rf.payload.Precipitation > 0.1
    ORDER BY 1;

    Result:

    name
    -----------------------------------
    CIBECUE CREEK NEAR OVERGAARD
    COLORADO RIVER AT LEES FERRY
    GILA RIVER AT KELVIN
    LITTLE COLORADO RIVER AT WOODRUFF
    NEW RIVER NEAR ROCK SPRINGS
    NEWMAN CANYON ABOVE UPPER LAKE MARY
    POLACCA WASH NEAR SECOND MESA
    PUERCO RIVER NEAR CHAMBERS
    SALT RIVER NEAR CHRYSOTILE
    SANTA MARIA RIVER NEAR BAGDAD