/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******         Exploring data in an Object Store with the READNOS Operator
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             List the location (or URI) and size of each object 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/

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


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


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

/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Disover the fields included in the objects
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/

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

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

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

/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Query the object store with the READNOS operator
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/


SELECT TOP 10 site_no, GageHeight2, Flow 
FROM(
    LOCATION ='/S3/S3.amazonaws.com/td-usgs-public/CSVDATA/'
    ACCESS_ID =''
    ACCESS_KEY =''
    ) AS D ;

SELECT TOP 10 site_no, GageHeight2, Flow 
FROM(
    LOCATION ='/S3/S3.amazonaws.com/td-usgs-public/PARQUETDATA/'
    ACCESS_ID =''
    ACCESS_KEY =''
    ) AS D;
    

SELECT TOP 10 Payload.site_no, Payload.GageHeight2, Payload.Flow 
FROM(
    LOCATION ='/S3/S3.amazonaws.com/td-usgs-public/JSONDATA/'
    ACCESS_ID =''
    ACCESS_KEY =''
    ) AS D;