/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******       Accessing data in an Object Store with a NOS Foreign Table
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/


/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Create the authorization object
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



CREATE AUTHORIZATION PublicAuth
USER ''
PASSWORD '';




/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Create the Foreign Table 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



CREATE FOREIGN TABLE csvdata
, EXTERNAL SECURITY PublicAuth
USING (LOCATION ('/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/'));

CREATE FOREIGN TABLE parquetdata
, EXTERNAL SECURITY PublicAuth
USING (LOCATION ('/S3/s3.amazonaws.com/td-usgs-public/PARQUETDATA/'));

CREATE FOREIGN TABLE jsondata
, EXTERNAL SECURITY PublicAuth
USING (LOCATION ('/S3/s3.amazonaws.com/td-usgs-public/JSONDATA/'));




/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Show Table 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



SHOW TABLE csvdata;

SHOW TABLE parquetdata;

SHOW TABLE jsondata;




/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Select with path filtering for a specific site_no 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



SELECT TOP 10 site_no, GageHeight2, Flow  
FROM csvdata
WHERE $path.$site_no = 9513780;

SELECT TOP 10 site_no, GageHeight2, Flow  
FROM parquetdata
WHERE $path.$var2 = 9513780;

SELECT TOP 10 Payload.site_no, Payload.GageHeight2, Payload.Flow 
FROM jsondata
WHERE $path.$var2 = 9513780;



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Use READ_NOS to view the SCHEMA of the json data 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



SELECT * FROM (
    LOCATION ='/S3/S3.amazonaws.com/td-usgs-public/JSONDATA/'
    ACCESS_ID =''
    ACCESS_KEY =''
    RETURNTYPE ='NOSREAD_SCHEMA')
 AS D;



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Create a view on top of the jsondata Foreign table 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



 REPLACE VIEW jsondata_view AS (
    SELECT
           CAST($path.$var2 AS int) AS site_no,
           CAST(payload.datatime AS TIMESTAMP(0) FORMAT'Y4-MM-DDBHH:MI') AS datatime,
           CAST(payload.Flow AS decimal(3,2)) AS Flow,
           CAST(payload.GageHeigh AS decimal(3,2)) AS GageHeigh,
           CAST(payload.Precipitation AS decimal(3,2)) AS Precipitation,
           CAST(payload.GageHeight2 AS decimal(3,2)) AS GageHeight2
    FROM jsondata);



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Query the jsondata_view 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



SELECT TOP 10 site_no, GageHeight2, Flow  
FROM jsondata_view
WHERE site_no = 9513780;