A Switch from Postgres to BigQuery

0

Postgres is a relational database management system (RDBMS) that is open-source and powerful. Also referred to as PostgreSQL, it is one of the most well-known tools used for data analysis on a large scale.

BigQuery on the other hand is a data warehouse from Google Cloud that does not need servers. Asides from being incredibly flexible, it encourages a rapid and successful analysis of massive quantities of data. In light of this, a lot of businesses opt to switch their data from PostgreSQL to BigQuery.

Before we begin the switching process, let us take an in-depth peek at the two platforms in question.

What makes Google BigQuery the preferred business data platform today?

Scalability and Usability

With BigQuery’s serverless structure, you can easily scale your data warehouse without hassle. This ensures your system performs at its best, even when dealing with large amounts of data. Also, setting it up is simple and you will only be charged for the resources you use.  

Better Results

It is highly unlikely for any platform to match the immense computing power of Google BigQuery, as it is simply limitless.

BigQuery has been engineered to be completely elastic, which means it can allocate the resources needed to execute your queries in seconds. It is also very well-designed for excellent query speed.

Affordability

BigQuery is fully managed and runs in the cloud, so users don’t have to worry about how it works. BigQuery lets you pay only for the resources you use. This means the need for initial hardware payments is erased which saves you money on maintenance costs.

Data Integration

When you switch to BigQuery, you can easily integrate with other Google Cloud services such as Dataflow, App Engine, Dataproc, etc.

How to switch from Postgres to BigQuery

Step 1: Retrieve Data From PostgreSQL

The most common way to retrieve data out of a PostgreSQL table is via the COPY TO command. However, if you are a novice in data extraction, you may want to try out the pg_dump tool. Both of these approaches will be talked about below.

• COPY command to retrieve data

Copy Command is the fastest way to transfer data between PostgreSQL tables and the normal file system. COPY TO command copies the SELECT query results or table contents to that file. Command syntax:

COPY table_name or query TO file_name WITH options.

• Using pg_dump to retrieve data

pg_dump backs up PostgreSQL databases. It retrieves table data as well.

pg_dump --column-inserts --data-only --table=<table> <database> > my_dump.sql

The output file table_name.sql will contain insert statements such as

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

This output needs to be translated into a CSV file. To do this, it is necessary you write a small script.

Step 2: Data Modification

When preparing data for BigQuery, there are certain basic things you need to remember asides from business rationale modifications:

• The Primary Key and Unique Key constraints are not implemented by BigQuery. That should be taken care of by your ETL process.
• CSV data should be UTF-8 encoded so it can be used with BigQuery.
• The DATE value must be YYYY-MM-DD (year-month-day), with a hyphen (-) between each part.
• Postgres and BigQuery column types somewhat differ. The majority of the types are either equivalent or convertible.
• If a text column could have a delimiter character, make sure to quote it.
• You must use a colon (:) to split the hour, minute, and second bits (hh:mm:ss) in the TIMESTAMP type.

Step 3: Send to Google Cloud Storage(GCS)

1. First, you need to sign in to your GCP account. Click on Storage on the left tab and then go to the Browser.

2. Choose the GCS bucket where you want the file to go. Select the bucket by clicking on it.

3. On the page containing details about the bucket, click the button that says “Upload files” and pick the files you want to add. After the file has been uploaded, it will be stated in the bucket.

Step 4: Upload from GCS to the BigQuery Table

You can either use web console UI or a command-line tool named bq.

a. Using the web console UI

• From the left side panel, navigate to the BigQuery console.

• If there isn’t already a Dataset, make one.

• Click the “Create dataset” option and also select “Create table” just below the Query editor.

• After clicking on the Create Table button, you will be provided with options to specify the input source and other requirements for the table.

b. Using bq command-line

With the help of this tool, uploading data to Google Cloud Storage (GCS) is so easy. Just use bq load with CSV as the source_format as illustrated below.

bq --location=[LOCATION] load --source_format=[FORMAT] [DATASET].[TABLE] [PATH_TO_SOURCE] [SCHEMA]

Before inserting the freshly extracted data into the BigQuery table, make sure to first upload the data into a staging table. Please ensure that it loads completely.

Summary

Indeed, switching from PostgreSQL to BigQuery can be a challenging endeavor. However, by carefully carrying out the migration process, you will have a smooth switch. We are trusting that this guide has given you a thorough understanding of the switching process. It begins with a brief explanation of both platforms, followed by highlighting the reasons Google BigQuery is better. Lastly, it presents the actual guide to assist you with the switch. By following the guide, you will be able to make the most of BigQuery without feeling overwhelmed by the process.