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 MULTISET TABLE rivernames ( site_no INT, name CHAR(90) 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 MyAuthObj USING ( LOCATION('/s3/td-usgs-public.s3.amazonaws.com/RIVERS/rivers.csv') );Replace LOCATION with your path to RIVERS/rivers.csv.
- Populate the dimension table:
INSERT INTO rivernames SELECT site_no, name FROM nos_rivernames;
- If it does not exist, create the foreign table or ask your database administrator to create the foreign table. See Setting Up to Run Examples.
- 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 will be 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
Prerequisite
Join the Dimension Table and External Data