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.
- 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 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.
-
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_csv. See Setting Up to Run CSV Examples.
-
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
Prerequisite
Join the Dimension Table and External Data