In the following example, create and populate a database table, create a foreign table, and join the two tables.
- To run NOS-related commands, log on to the database as a user with the required privileges.
- If the rivernames table already exists, skip the rest of the steps in the Prerequisite section.
-
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 ); -
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.
-
Populate the dimension table:
INSERT INTO rivernames SELECT payload..site_no, payload..name FROM nos_rivernames;
- 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.
-
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
Prerequisite
Join the Dimension Table and External Data