The example shows how to write data from an Advanced SQL Engine table to external object store.
- If not already done, set up an authorization object and function mapping. See Setting Up Authorization for WRITE_NOS Examples.
- If not already done, set up a database table to extract data from. You can use one of your existing tables or set up a test table to use with the examples. See Setting Up a Test Table.
- Set up or obtain access to the external storage where you want to write data. Follow the instructions from your external storage vendor.
- Write a portion of data from a database table to external object store. For example, read JSON data from the test table called RiverFlowPerm and write it to your external storage:
SELECT * FROM WRITE_NOS ( ON ( SELECT * FROM RiverFlowPerm WHERE DateTime = '2018-06-27 00:00') USING LOCATION('YOUR-OBJECT-STORE-URI/20180627/') AUTHORIZATION(MyAuthObj_Write) STOREDAS('PARQUET') ) AS d;Replace LOCATION with the URI to the external object store where you want to write the data.
WRITE_NOS stores data in Parquet format.Your result will be similar to the following:
NodeId AmpId Sequence ObjectName ------ ----- -------- -------- -------------------------------------------------------------- 33 0 1 /S3/s3.amazonaws.com/ie-writenos-bucket/20180701/object_33_0_1.parquet 33 1 1 /S3/s3.amazonaws.com/ie-writenos-bucket/20180701/object_33_1_1.parquet 33 2 1 /S3/s3.amazonaws.com/ie-writenos-bucket/20180701/object_33_2_1.parquet - Create a foreign table to access the external object store you just wrote to:
CREATE FOREIGN TABLE ft20180627 ,EXTERNAL SECURITY MyAuthObj_Write USING ( LOCATION('YOUR-OBJECT-STORE-URI/20180627/') );Replace LOCATION with the URI to the external object store where you wrote the data.
- Count the number of rows in the external data:
SELECT COUNT(*) FROM ft20180701;
Result:Count(*) -------- 12
- Sample one row from the external data:
SELECT * FROM ft20180701 SAMPLE 1;
Your result will be similar to the following:
Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_3_1.parquet SiteNo 9394500 DateTime 2018-06-27 00:00 Flow .01 GageHeight1 4.75 Precipitation .00 GageHeight2 4.83The output is displayed vertically for readability.
- Compare some of the data in the database table to the data stored externally. First, count the number of rows in the database table that match the criteria specified in the WHERE clause:
SELECT COUNT(*) FROM RiverFlowPerm WHERE GageHeight1 > 7.0 AND DateTime = (TIMESTAMP '2018-07-01 00:00:00');
Result:Count(*) -------- 2
Then, count the number of lines in the external object store that match the same criteria.
SELECT COUNT(*) FROM RiverFlowPerm s, ft20180627 ft WHERE s.SiteNo = ft.SiteNo AND s.GageHeight1 > 7.0 AND s.DateTime = '2018-06-27 00:00';
Result:Count(*) -------- 2
The result from both queries should match.
- Read the Parquet schema of the offloaded data from external object store:
SELECT * FROM ( LOCATION='/S3/s3.amazonaws.com/iewritenostest/20180627/' AUTHORIZATION=MyAuthObj_Write FULLSCAN='TRUE' RETURNTYPE='NOSREAD_PARQUET_SCHEMA' ) as d;
Replace LOCATION with the URI to the external object store where you wrote the data.
Your result will be similar to the following:
ColPosition 1 Name SiteNo TdatDataType INTEGER PhysicalType INT32 LogicalType NONE Precision 0 Scale 0 MinLength 0 MaxLength 0 NullFound 1 FileType parquet Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_0_1.parquet ColPosition 1 Name SiteNo TdatDataType INTEGER PhysicalType INT32 LogicalType NONE Precision 0 Scale 0 MinLength 0 MaxLength 0 NullFound 1 FileType parquet Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_1_1.parquet [...] ColPosition 2 Name DateTime TdatDataType VARCHAR PhysicalType BYTE_ARRAY LogicalType UTF8 Precision 0 Scale 0 MinLength 16 MaxLength 16 NullFound 1 FileType parquet Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_0_1.parquet [...]The output is displayed vertically for readability.
Prerequisites
Write Data to External Object Store
Verify Offloaded Data on External Object Storage