Easy Methods to Integrate Netezza and Amazon S3 – Steps

  • Post author:
  • Post last modified:January 19, 2023
  • Post category:Netezza
  • Reading time:9 mins read

Amazon AWS is gaining popularity as cloud based web services. It will just take few clicks to make your system or storage up and running. Amazon web services (AWS) provides on-demand cloud computing platforms and storage services (S3). Amazon S3 is fast, reliable cloud storage that is the reason most of organizations are using it to store its data. In this article, we will check easy methods to Integrate Netezza and Amazon S3 storage for data transfer between them.

Integrate Netezza and Amazon S3

You may have to connect to Amazon S3 to pull data and load into Netezza database table. The other possible way would be to copy the Netezza query results back to the Amazon S3. You have to use the Amazon provided AWS CLI for Netezza and Amazon S3 integration.

Easy Methods to Integrate Netezza and Amazon S3

You can integrate Netezza and Amazon S3 by using couple of methods:

  • Upload and download data files to/from S3 Bucket using AWS CLI
  • Upload and download data files to/from S3 Bucket using AWS Management Console

Now let us see these steps in details

Upload and download data files to/from S3 Bucket using AWS CLI

AWS CLI has made working with Amazon S3 very easy. Once you have AWS CLI installed and configured with your secret and access keys, you are all set to access Amazon S3 for all your upload and download data files.

You can also automate all your upload and download process with the help of AWS CLI commands.

Upload data files to S3 Bucket using AWS CLI

To upload the Netezza unloaded files from Local folder to the Amazon S3 use following command:

vithal@vithal-Inspiron-3558:~$ aws s3 cp data_file.txt s3://netezzabucket/upload/
upload: ./nz_data.txt to s3://testnetezzabucket/upload/data_file.txt

vithal@vithal-Inspiron-3558:~$ aws s3 ls s3:// netezzabucket/upload/
2017-11-14 09:08:31 0 
2017-11-14 18:05:04 506 data_file.txt
vithal@vithal-Inspiron-3558:~$

Uploading a Netezza standard output Stream Data to S3

AWS CLI stream feature is latest addition to the list. You can use this feature to export data straight from nzsql output to Amazon S3. You can transfer data directly from Netezza to S3 without having intermediate file. You can stream Netezza data to Amazon S3 using AWS CLI command.

Read:

Following combination of nzsql command and AWS CLI will upload Netezza stream data into Amazon S3.

vithal@vithal-Inspiron-3558:~$ nzsql -host 192.168.0.106 -u admin -pw password -db training -F ',' -A -t -c "select * from patient_1" | aws s3 cp - s3://testnetezzabucket/upload/nz_data.txt

vithal@vithal-Inspiron-3558:~$ aws s3 ls s3://testnetezzabucket/upload/
2017-11-14 09:08:31 0 
2017-11-14 18:05:04 506 nz_data.txt
vithal@vithal-Inspiron-3558:~$

Download data files from S3 Bucket to local using AWS CLI and then use nzload to insert data

To download the data files from S3 to local folder use following command:

You can follow below steps to copy file from S3 bucket to local folder:

vithal@vithal-Inspiron-3558:~$ aws s3 cp s3://netezzabucket/upload/test.txt patient.txt
download: s3://testnetezzabucket/upload/test.txt to ./patient.txt

vithal@vithal-Inspiron-3558:~$ ls -ltr patient.txt
-rw-rw-r-- 1 vithal vithal 42 Nov 14 17:57 patient.txt
vithal@vithal-Inspiron-3558:~$

Load Netezza table using nzload utility

Once you have data file stored locally then use nzload to load it to Netezza table. Follow below steps:

vithal@vithal-Inspiron-3558:~$ nzload -host 192.168.0.106 -db training -u admin -pw password -t patient -df patient.txt -Delimiter ','

Load session of table 'PATIENT' completed successfully

Load Amazon S3 stream output data using nzload

You can load the stream of Amazon S3 data directly to Netezza table without having to download it locally. This feature save storage and time both.

Read:

Following combination of nzsql command and AWS CLI will upload Netezza stream data into Amazon S3.

vithal@vithal-Inspiron-3558:~$ aws s3 cp s3://netezzabucket/upload/test.txt - | nzload -host 192.168.0.106 -db training -u admin -pw password -t patient -df patient.txt -Delimiter ','

Load session of table 'TEST_PATIENT' completed successfully

Upload and Download Data files to/from Amazon S3 Bucket using AWS Management Console

To use this method, you have to unload Netezza table to flat file and you can upload that flat file to S3 bucket.

Read:

To manually upload and download one or more files to/from Amazon S3, you have to use the AWS Management Console. Below are the steps that you have to follow:

Upload files using Management Console

  1. Login to Amazon AWS management console at https://console.aws.amazon.com/s3/
  2. Select the bucket name into which you want to upload files
  3. Drag and drop data files to console window
  4. Upload files
Integrate Netezza and Amazon S3 - upload file

Download files using Management Console

  1. Login to Amazon AWS management console at https://console.aws.amazon.com/s3/
  2. Select the file in the bucket that you want to download
  3. Select data file and click on download
Integrate Netezza and Amazon S3 - download

 

Tags: ,