Join External Data and Database Tables | NOS | VantageCloud Lake - Join External Data and Database Tables - Teradata VantageCloud Lake

Lake - Manage and Move Data
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-05-16
dita:mapPath
atx1683670417382.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
atx1683670417382

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.

Before proceeding, verify with your database administrator that you have the correct privileges and an authorization object.

    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 external object storage-to-database join, a simulation is required. Typically, you join to an existing table in your environment and do not need these simulation steps.
    As a solution a small dimension table has been placed in external object storage to:
    • Make the join practical.
    • Make sure all users trying this example have the same version of the table data.
    CREATE MULTISET TABLE rivernames (
 site_no INT,
 name CHAR(90) 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 MyAuthObj
USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/RIVERS/rivers.csv') );

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

  5. Populate the dimension table:
    INSERT INTO rivernames
SELECT site_no, name
FROM nos_rivernames;

    6. Join the Dimension Table and External Data

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

    Your result is similar to the following:

    name
----------------------------------
CIBECUE CREEK NEAR OVERGAARD
CRIR LWR MAIN DRAIN BLW TYSON WW
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