Different Methods to Load Data from Amazon S3 into Netezza Table

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:Netezza
  • Reading time:4 mins read

Amazon as a cloud based service gaining popularity. Amazon web services (AWS) provides on-demand cloud computing platforms and storage services. 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 how to load data from Amazon S3 into Netezza tables.

Load Data from Amazon S3 into Netezza Table

You may also interested in loading Netezza data to S3 bucket:

We will be using Amazon AWS CLI to load data from Amazon S3 into Netezza tables.

Install Amazon Web Services Command Line Interface – AWS CLI

The AWS Command Line Interface (AWS CLI) is a unified tool that provides a consistent interface for interacting with AWS. You can use AWS CLI commands to interact with S3 bucket. Follow below link to install AWS CLI on Linux system.

Once you have AWS CLI installed and configured with your access and secret key, next step would be to load data from Amazon S3 into Netezza table.

Load Data from Amazon S3 into Netezza Table

There are three ways you can load data from Amazon S3 into Netezza table:

  • Download flat file from S3 to local using AWS CLI and use nzload to load table
  • Load Amazon S3 stream output data using nzload
  • Download flat file from S3 to local using web console and use nzload to load table

Download flat file from S3 to local using AWS CLI and use nzload to load table

In this method, you have to download or copy data file from Amazon S3 to local folder using AWS CLI and then use nzload to load that file.

Download or copy File from S3 Bucket to Local Folder

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

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

vithal@vithal-Inspiron-3558:~$ ls -ltr test_patient.txt
-rw-rw-r-- 1 vithal vithal 42 Nov 14 17:57 test_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 test_patient -df test_patient.txt -Delimiter ',' 
Load session of table 'TEST_PATIENT' completed successfully

vithal@vithal-Inspiron-3558:~$ nzsql -host 192.168.0.106 -u admin -pw password -db training -F ',' -A -t -c "select * from test_patient"
4,DEF
2,BCa
6,AAA
5,EFG
1,ABd
3,CDr
7,BBB
vithal@vithal-Inspiron-3558:~$

Load Amazon S3 stream output data using nzload

Following combination of aws cli and nzload command will load the S3 stream data into Netezza table

vithal@vithal-Inspiron-3558:~$ aws s3 cp s3://testnetezzabucket/upload/test.txt - | nzload -host 192.168.0.106 -db training -u admin -pw password -t test_patient -df test_patient.txt -Delimiter ','
Load session of table 'TEST_PATIENT' completed successfully

vithal@vithal-Inspiron-3558:~$ 
TRAINING.ADMIN(ADMIN)=> select * from test_patient;
 ID | NAME 
----+------
 4 | DEF
 2 | BCa
 6 | AAA
 5 | EFG
 1 | ABd
 3 | CDr
 7 | BBB
(7 rows)

Download flat file from S3 to local using AWS Management Console and use nzload to load table

The final easy step is to download required data file from Amazon S3 bucket using web console.

You have to login using AWS credentials and select file that you want to download to local folder and download it.

Load Data from Amazon S3 into Netezza Table and Example

Tags: ,