Join External CSV 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.

The examples use a sample river flow data set. To use your own data, replace the table and column names, and authorization object. See Variable Substitutions for Examples for the credentials and location values for the sample data set.

    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 that is used to populate 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_csv. See Setting Up to Run CSV Examples.
  8. Join the dimension and foreign tables:
    SELECT DISTINCT name(CHAR(60))
      FROM riverflow_csv rf, rivernames rn
     WHERE rf.payload..site_no = rn.site_no
       AND rf.payload..Precipitation > 0.1
     ORDER BY 1;

    Your result will be similar to the following:

    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