Simplifying Data Transfer from Netezza to Amazon S3

By Omar Fadl

Properly managing data is a top priority for the government, not only to safeguard sensitive information and protect the privacy of individuals but also to provide insights for decision-making. As government agencies evolve and modernize their internal systems, it’s crucial that they adopt streamlined data transfer processes for efficient and secure data movement. For example, consider a scenario where an agency has opted to migrate data from an IBM® Netezza® database to an Amazon Simple Storage Service (Amazon S3) bucket for advanced analytics and archival purposes. How can they ensure seamless data transfer and management?

One way is to use a robust script designed to copy data from the existing Netezza database to the Amazon S3 bucket. This script serves as an automated solution to facilitate the transfer of large datasets, ensuring data integrity and security during the migration process. Let’s delve into the key components and benefits of this approach.

Advantages of the Approach

The script leverages the powerful combination of Netezza’s query capabilities and data formatting options as well as Amazon S3’s secure storage infrastructure. By compressing data with GZIP and utilizing Amazon S3’s robust copy command, this approach optimizes data transfer efficiency while ensuring data security and integrity. Furthermore, the script provides the flexibility to adapt the process to other destinations, such as PostGreSQL, showcasing its versatility.

Code

The following script copies a table to Amazon S3. In this example, the source database is Netezza, so the script invokes nzsql to run the query (parameters explained at NZSQL Command-line Options, and time is used to output length of run). It then pipes the results to GZIP for compression, although this is optional. Finally, it pipes the compressed output to the S3 copy command. The assumed operating system is Linux.

#!/bin/bash

export NZ_HOST=”
export NZ_DB=”
export NZ_USER=”
export NZ_PASSWORD=”

export AWS_ACCESS_KEY_ID=”
export AWS_SECRET_ACCESS_KEY=”

# pipe table into gzip, then into S3 copy
time nzsql -h $NZ_HOST -d $NZ_DB -u $NZ_USER -pw $NZ_PASSWORD -F ‘|’ -A -t -c “select * from tablename” -P null=’null’ |\
gzip |\
aws s3 cp – s3://bucketname/filename.gz –region us-east-1 –endpoint-url https://bucket.vpce-***.s3.us-east-1.vpce.amazonaws.com –expected-size 54760833024000

To run the script in the background and capture output in a log file, execute the following at the command line:

nohup ./script.sh > ./script.log 2>&1 &

To see that the script is running, run the following at the command line:
watch -n 60 “ps -ef | grep yourusername”

If the destination is PostGreSQL instead of S3, here is an example script:
#!/bin/bash

export NZ_HOST=”
export NZ_DB=”
export NZ_USER=”
export NZ_PASSWORD=”

export PG_HOST=”
export PG_PORT=’5432′
export PG_DB=”
export PG_USER=”
export PGPASSWORD=”

# pipe table into psql
nzsql -h $NZ_HOST -d $NZ_DB -u $NZ_USER -pw $NZ_PASSWORD -F ‘|’ -A -t -c “select * from schemaname.tablename” -P null=’null’ |\
psql -h $PG_HOST -p $PG_PORT -d $PG_DB -u $PG_USER -c “COPY schemaname.tablename from STDIN WITH (FORMAT csv, HEADER FALSE, delimiter E’|’, NULL ‘null’ ) ”

Explaining the Code

The script starts by exporting Netezza and Amazon credentials, ensuring secure authentication. It then uses the `nzsql` command to extract data from the Netezza database, specifying parameters such as host, database, user, and password. The query selects data from a specific table and formats the output with the delimiter ‘|’ for easy processing. The script then pipes the output through GZIP for compression and transfers it to the designated S3 bucket using the `aws s3 cp` command.

The Amazon S3 copy command is documented at cp — AWS CLI 1.29.71 Command Reference (amazon.com). Specifically, the –expected-size parameter is needed only when uploading streams larger than 50 GB. Use a value large enough for your stream. Also, the –endpoint-url parameter can be used to access your S3 service-specific endpoint and ensure that the traffic does not traverse the open internet.

If the source table is too large to be managed effectively in one shot, the select statement can include conditions to retrieve subsets of the rows. This can be based on ID column values or internal values such as row ID or data slice ID. The script would have to define the subsets in a way that would ultimately cover all the rows in the table.

Alternative Options and Considerations

While the presented approach efficiently transfers data from Netezza to Amazon S3, there are alternative methods worth considering. Organizations might explore leveraging AWS Database Migration Service (AWS DMS) for streamlined data migration across various database management systems. Additionally, AWS Glue can facilitate ETL (Extract, Transform, Load) operations, offering a comprehensive solution for data integration and transformation.

Conclusion

Efficient data transfer is vital for seamless government operations, and the script presented here offers a reliable and secure method to move data from Netezza to Amazon S3. By combining the strengths of Netezza’s query capabilities and Amazon S3’s storage infrastructure, this script ensures the secure and efficient transfer of data. By adopting this streamlined approach, agencies can enhance their data management capabilities. They can foster data-driven insights and informed decision-making while protecting both individual privacy and national security.
For more information and a detailed walkthrough of the script, refer to the provided code snippets and reach out to me. Streamlining data transfer processes lays a robust foundation for organizations to harness the power of their data assets effectively.

IBM® Netezza® and Amazon Simple Storage Service (Amazon S3) are trademarks of their respective owners.