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

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