Nowadays, more and more organizations are using Google Cloud BigQuery to solve their needs for a cloud-based enterprise data warehouse. If you’re one of them, one of the problems you might face is how to move your data from an on-premises (on-prem) solution to Google Cloud Platform without harming your current setup and production performance.
In this post, we take a look at a very specific problem where you (the client)
Basically, you don’t want to change anything at the moment, but you would like to use BigQuery in parallel. Our challenge is to create a pipeline that is able to stream the data out of PostgreSQL to BigQuery. The hard part is that we have to take into account the server’s limitations. It has to also handle initial data loads and streaming at the same time, considering a maximum table size of ~200GB.
Composer: Cloud Composer is a fully managed workflow orchestration service that empowers you to author, schedule, and monitor pipelines that span clouds and on-prem data centers. Built on the popular Apache Airflow open-source project and operated using the Python programming language, Cloud Composer is free from lock-in and easy to use.
Pub/Sub: Cloud Pub/Sub is a simple, reliable, scalable foundation for stream analytics/event-driven computing systems and it’s a global messaging and event ingestion service.
Google Cloud Storage: Google Cloud Storage (GCS) is a flexible, scalable, and durable storage option. You can read and write files to GCS buckets from almost anywhere, so you can use buckets as common storage between your instances, Google App Engine, your on-prem systems, and other cloud services.
BigQuery: BigQuery is Google’s serverless, highly scalable, enterprise data warehouse designed to make all your data analysts productive at an unmatched price-performance. Because there is no infrastructure to manage, you can focus on analyzing data to find meaningful insights using familiar SQL without the need for a database administrator.
Docker: Docker is a tool designed to make it easier to create, deploy, and run applications using containers. Containers allow a developer to package up an application with all of the parts it needs, such as libraries and other dependencies, and ship it all out as one package.
Here, we review only the most interesting part of efficiently exporting JSON from PostgreSQL: the Java Docker application’s psql command that does the export. It’s challenging. Sometimes it has to export a few megabytes, but when adding a new table, it might have to export 200GB+. And while it’s exporting, the database is still being used in production.
So, we have to be memory and IO efficient as much as possible so as not to harm your application. The only way we can do this without consuming loads of memory is to use PostgreSQL’s COPY. COPY differs from other solutions (such as redirecting query results to a file), because it’s able to stream the data out to standard output or to a file, so a small amount of memory is enough. The problem is that it’s able to export only to CSV. And the problem with CSV is that the data in the database is so complex CSV’s format can’t handle it. Therefore, we choose an another output format, JSON.
To solve this problem, the query itself has to return a JSON. Then we only have to stream it out to a file or stdout via COPY. It’s very easy actually:
The problem with this though, is that when it’s exported via COPY, COPY will ruin the JSON’s format, because COPY by default uses double quotes (") as a CSV column separator. The solution is a little bit hacky, but works really well:
This replaces the default double quotes with a backspace character during the escape stage. In this way, COPY won’t ruin the JSON structure as it’ll look for backspace characters as column separators. Using this solution, we have a file that contains JSON data in one CSV column separated with backspaces. We simply have to replace those backspace characters in the file in every row:
Note: Depending on the data, we can replace a backspace with any other character that complies with the QUOTE parameter’s requirements. At this point, the data is ready to upload with gsutil to Cloud Storage.
As always, there are good and bad things about this.
On the on-prem side, this solution is an efficient way to stream out data. Also, by using Pub/Sub, we avoid any VPN/Firewall issues easily as we don’t need any network hacks. It’s a scalable, robust solution.
In this solution, Composer provides the orchestration which was invented for use in more complex scenarios. Here it’s a little bit of overkill, as it spins up three machines at a minimum.
In the end, we have a robust, scalable solution. It’s able to export a valid JSON from a 1TB database without consuming loads of memory and harming the database. These JSON files can then be easily imported into BigQuery from GCS so you can use them.
