Purpose of the article: This article provides a detailed explanation of migrating data from a PostgreSQL to Snowflake using command line tools. It covers the setup, extraction, table creation, data import, and verification stages of the migration process.
Intended Audience: This article targets database administrators and developers who need to migrate data between PostgreSQL and Snowflake using command line interfaces.
Tools and Technology: PostgreSQL, Snowflake, pg_dump, snowsql
Keywords: PostgreSQL, Snowflake, pg_dump, snowsql, Command Line Tools
Objectives
This post explains how to migrate data from PostgreSQL to Snowflake using command line tools. In addition, we will go over the setup, data extraction, and loading stages to ensure a seamless and efficient data migration.
Requirement:
Before starting, ensure you have the following:
- Access to PostgreSQL database and Snowflake.
- Installation and configuration of pg dump for PostgreSQL.
- Installation and configuration of SnowSQL for Snowflake.
Tools and Technologies:
- PostgreSQL: Open-source relational database management system used as the source database.
- pg_dump: CMD-line utility for PostgreSQL to export data.
- Snowflake: Cloud-based data warehousing service.
- SnowSQL: CMD-line client for interacting with Snowflake.
- Operating System: Windows system capable of running PostgreSQL and Snowflake command-line tools.
- Command Line Interface (CLI): Used to execute pg_dump and SnowSQL commands.
- Shell Scripting: Optional, for automating the sequence of commands.
Step 1: Extract Data from PostgreSQL
After completing the installation and configuration of pg_dump with PostgreSQL, export the data from PostgreSQL into an SQL file using the following command.
“pg_dump –host <‘host’> –port <‘port’> –username <‘username’> –dbname <‘dbname’> –table <‘table_name’> –data-only –column-inserts –file <output_file.sql>”
Explanation:
- –host: database server.
- –port: port number (default: 5432).
- –username: Username for authentication.
- –dbname: Name of the database.
- –table: table to dump.
- –data-only: Excludes table structure, includes only data.
- –column-inserts: Uses INSERT statements with explicit column names.
- –file: Specifies the destination file for the SQL output.
Data in PostgreSQL
Once the above command is executed, it will request a PostgreSQL password.
Exporting data from PostgreSQL
We will find all the above credentials in PostgreSQL properties.
Reference
Step 2: Create Table in Snowflake
Before importing data into Snowflake, construct the target table structure within Snowflake. Use SnowSQL to execute the necessary SQL command.
“snowsql -a snowflake_account -u username -d database -s schema -q “CREATE OR REPLACE TABLE table_name (column_definitions);”
Explanation:
- -a: Snowflake account.
- -u: username.
- -d: database.
- -s: schema.
- -q: Executes the SQL query.
Creating table in Snowflake
Step 3: Import Data into Snowflake
“snowsql -a snowflake_account -u username -d database -s schema -f exported_sql_file.sql”
Explanation:
- -f: Specifies the SQL file to be executed.
Upon executing the above command, you will be prompted to enter the Snowflake password. This will trigger an SQL file in Snowflake, loading the data into the target table.
Importing data into Snowflake
Step 4: Verify Data in Snowflake
After importing the data from the SQL export file into the Snowflake table, verify the data to confirm its accurate importation.
“snowsql -a snowflake_account -u username -d database -s schema -q “SELECT * FROM <table_name>;”
Verifying Data in Snowflake via Command Line
Verifying data in Snowflake database
Advantages:
- Automates data migration, reducing manual effort and potential for human error.
- Ensures data integrity and consistency during the transfer from PostgreSQL to Snowflake.
- Uses command-line tools that can be easily integrated into scripts for scheduled and repeatable tasks.
Disadvantages:
- Requires familiarity with command-line tools and both database environments.
- Relies on correct configuration and availability of pg_dump and SnowSQL, which may vary across different systems.
- Limited built-in error handling; issues during migration may require manual intervention and troubleshooting.
Conclusion:
By following these steps, you can successfully migrate a table from PostgreSQL to Snowflake, ensuring that all data is accurately transferred and validated. This guide provides a reliable process for database administrators and developers working with data migration tasks.
Author Bio:
Ayyappa Reddy VEMPALLI
Associate Software Engineer - Data Engineering-Analytics
I’m Ayyappa Reddy VEMPALLI, and I've been with MOURI Tech for 9 months as Associate Software Engineer in Data Engineering. I have good skills in DBT, Snowflake, PostgreSQL and Python coding language.
