Netezza Performance Server

Announcement: External tables on cloud stores

By Abhishek Jog posted Mon December 21, 2020 07:29 AM

  
Netezza Performance Server now supports external tables on cloud stores

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:

  • REMOTESOURCE ‘S3’ indicates the file will be on cloud store
  • DATAOBJECT ‘filename’ indicates name of the file
  • ACCESSKEYID and SECRETACCESSKEY are credentials required to access cloud bucket mentioned in BUCKETURL.
  • DEFAULTREGION is the region of the bucket.
  • UNIQUEID helps segregate your data in a logical namespace in the bucket.
  • MULTIPARTSIZEMB allows you to tune performance and cost factor while uploading data to cloud.
  • ENDPOINT is the URL required to access IBM COS region. It is optional in case of Amazon S3.

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 (

REMOTESOURCE 'S3'

UNIQUEID 'NetezzaET'

ACCESSKEYID '?????’

SECRETACCESSKEY '?????’

DEFAULTREGION 'US-EAST-1'

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.

0 comments
8 views

Permalink