IBM Data Management Community Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems. Join / Log in
Posted On: 21st December 2020
External table has been a popular feature of Pure Data for Analytics (PDA) and Netezza Performance Server (NPS), where you can treat an external file as a database table while performing SQL operations. NPS now supports accessing files on cloud stores as external table.
You can now use files on Amazon S3 and IBM COS cloud stores for creating an external table. NPS now provides SQL syntax to CREATE EXTERNAL TABLE on cloud, INSERT INTO it and SELECT from it.
This feature is available starting NPS 11.1.0 that runs on cloud as well as on-prem
USING clause of the EXTERNAL TABLE query takes cloud store related arguments such as:
e.g.
CREATE EXTERNAL TABLE ET1 (c1 int) USING (DATAOBJECT '/et1.txt' REMOTESOURCE 'S3' ACCESSKEYID 'xxxx' SECRETACCESSKEY 'xxxx' DEFAULTREGION 'us-east-1' BUCKETURL 'test1' UNIQUEID 'netezzaET' MULTIPARTSIZEMB 200);
This query will create file netezzaET/et1.txt in bucket test1 on Amazon S3. Data will be uploaded in 200 MB chunks.
All other pre-existing external table functionality supported with external table on Cloud stores
External tables can be used for migrating data on-prem to cloud NPS. You can unload on-prem tables to cloud store using external table. E.g
CREATE EXTERNAL TABLE '/t2.dat' USING (
REMOTESOURCE 'S3'
UNIQUEID 'NetezzaET'
ACCESSKEYID '?????’
SECRETACCESSKEY '?????'
DEFAULTREGION 'US-EAST-1'
BUCKETURL 'ET-TEST'
) AS SELECT * FROM T2;
Then you can load it into cloud NPS table:
INSERT INTO T4 SELECT * FROM EXTERNAL '/t2.dat' USING (
SECRETACCESSKEY '?????’
BUCKETURL 'ET-TEST');
Read more about feature at:
https://www.ibm.com/support/knowledgecenter/SSTNZ3/com.ibm.ips.doc/postgresql/load/c_load_loading_cloud.html
https://www.ibm.com/support/knowledgecenter/SSTNZ3/com.ibm.ips.doc/postgresql/load/c_load_unloading_cloud.html
This capability has been introduced in IBM Netezza Performance Server version 11.1.0.0.