/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******         Writing data to an Object Store with the WRITE_NOS Operator
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Create an authorization object
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/


REPLACE AUTHORIZATION WriteAuth_NOS_S3
AS  
USER 'YOUR_ACCESS_KEY_ID' 
PASSWORD 'YOUR_ACCESS_SECRET_KEY';



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Order data on each amp by location before writing the data
******                         with (default) Range Naming
**************************************************************************************
**************************************************************************************
**************************************************************************************/


SELECT * FROM WRITE_NOS
(ON 
 (SELECT orderid, custid, orderstatus, ordertotal, YEAR(orderdate) as theYear, 
  		 Month(orderdate) as theMonth, orderdate, orderpriority, clerk, location, shippriority 
  FROM TRNG_DS.orders_1600 
  WHERE theYear = 2018)  
LOCAL ORDER BY location
USING 
 LOCATION('/s3/s3.amazonaws.com/nos-hands-on-us-west-2-trainee33/orders_1600-v1/') 
 AUTHORIZATION(WriteAuth_NOS_S3)
 STOREDAS('PARQUET')
) AS d ;


/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******             Order data on each amp by location before writing the data
******                              with DISCRETE Naming 
**************************************************************************************
**************************************************************************************
**************************************************************************************/


SELECT * FROM WRITE_NOS
(ON 
 (SELECT orderid, custid, orderstatus, ordertotal, YEAR(orderdate) as theYear, 
  			 Month(orderdate) as theMonth, orderdate, orderpriority, clerk, location, shippriority 
  FROM TRNG_DS.orders_1600 
  WHERE theYear = 2018)  
LOCAL ORDER BY location
USING 
 LOCATION('/s3/s3.amazonaws.com/nos-hands-on-us-west-2-trainee33/orders_1600-v2/') 
  AUTHORIZATION(WriteAuth_NOS_S3)
 STOREDAS('PARQUET')
 NAMING ('DISCRETE')
) AS d ;

/*************************************************************************************
**************************************************************************************
**************************************************************************************
******                  Redistribute the rows by location with HASH BY
******             Order data on each amp by location before writing the data
******                           with DISCRETE Naming
**************************************************************************************
**************************************************************************************
**************************************************************************************/


SELECT * FROM WRITE_NOS
(ON 
 (SELECT orderid, custid, orderstatus, ordertotal, YEAR(orderdate) as theYear, 
  			 Month(orderdate) as theMonth, orderdate, orderpriority, clerk, location, shippriority 
  FROM TRNG_DS.orders_1600 
  WHERE theYear = 2018)  
HASH BY location LOCAL ORDER BY location
USING 
 LOCATION('/s3/s3.amazonaws.com/nos-hands-on-us-west-2-trainee33/orders_1600-v3/') 
  AUTHORIZATION(WriteAuth_NOS_S3)
 STOREDAS('PARQUET')
 NAMING ('DISCRETE')
) AS d ;



/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******                Query the object store with the READ_NOS 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/

SELECT *
FROM(
    LOCATION ='/s3/s3.amazonaws.com/nos-hands-on-us-west-2-trainee33/orders_1600-v3/'
    AUTHORIZATION=WriteAuth_NOS_S3
    SAMPLE_PERC='0.10'
    ) AS D
    WHERE location1 = 8;

    

/*************************************************************************************
**************************************************************************************
**************************************************************************************
******                  
******                               EXTRAS
******                           
**************************************************************************************
**************************************************************************************
**************************************************************************************/


    
/*************************************************************************************
**************************************************************************************
**************************************************************************************
******                  Redistribute the rows by location with HASH BY
******             Order data on each amp by location before writing the data
******                           with DISCRETE Naming
**************************************************************************************
**************************************************************************************
**************************************************************************************/


SELECT * FROM WRITE_NOS
(ON 
 (SELECT orderid, custid, orderstatus, ordertotal, YEAR(orderdate) as theYear, 
  			 Month(orderdate) as theMonth, orderdate, orderpriority, clerk, location, shippriority 
  FROM TRNG_DS.orders_1600 
  WHERE theYear = 2018)  
HASH BY location LOCAL ORDER BY location, theMonth
USING 
 LOCATION('/s3/s3.amazonaws.com/nos-hands-on-us-west-2-trainee33/orders_1600-v4/') 
  AUTHORIZATION(WriteAuth_NOS_S3)
 STOREDAS('PARQUET')
 NAMING ('DISCRETE')
) AS d ;


/*************************************************************************************
**************************************************************************************
**************************************************************************************
******
******                Creating a Foreign Table 
******
**************************************************************************************
**************************************************************************************
**************************************************************************************/

CREATE FOREIGN TABLE orders_NOS_SE
, EXTERNAL SECURITY  WriteAuth_NOS_S3
USING (LOCATION ('/s3/s3.amazonaws.com/nos-hands-on-us-west-2-trainee33/orders_1600-v4/'));

show table jsondata_write;