17.05 - Example: Joining External Data in a Foreign Table to Relational Data - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

In this example, external data in the foreign table riverflow_join is joined to the database table rivernames to determine when a river location reached a specified height.

Table Definitions

Following is the definition for the foreign table riverflow_join with external data on Amazon S3.

Create the DefAuth authorization object, if not already done. See Example: Creating an Authorization Object.

CREATE FOREIGN TABLE riverflow_join
 , EXTERNAL SECURITY INVOKER TRUSTED DefAuth
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE)
USING
(
      LOCATION  ('/s3/td-usgs.s3.amazonaws.com/DATA/09380000/2018')
      PATHPATTERN  ('$data/$siteno/$year/$month/$day')
      ROWFORMAT('{"record_delimiter":"\n", "character_set":"UTF8"}')
 )
NO PRIMARY INDEX ;

The table rivernames contains columns for site numbers and corresponding location names.

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 );

Sample Query and Results

This statement returns the location names where the river height reached 10.11. The riverflow_join table alias is rf and the rivernames table alias is rn.

SELECT name,rf.payload.GageHeight
FROM riverflow_join rf, rivernames rn
WHERE rf.payload.site_no = rn.site_no
AND rf.payload.GageHeight = 10.11;

Below is a sample of the results output.

name                                   Payload.GageHeight
----------------------------------------------- ------------------
COLORADO RIVER AT LEES FERRY, AZ                 10.11
COLORADO RIVER AT LEES FERRY, AZ                 10.11
COLORADO RIVER AT LEES FERRY, AZ                 10.11
COLORADO RIVER AT LEES FERRY, AZ                 10.11
COLORADO RIVER AT LEES FERRY, AZ                 10.11