BigQuery Export Data – Options And Tools

BigQuery is a warehouse where you can store and query tons of data accumulated from multiple sources. Therefore, in most cases, BigQuery acts as a destination for your data – where you import information to. At the same time, it becomes a data source when you need to export data from BigQuery, for example, to share a piece of a dataset with stakeholders or use specific information for a report. Therefore, we created this tutorial to answer the most common questions about BigQuery data export, such as ‘How you can export data?’, ‘What formats are available?’, ‘Can you automate data exports?’, and so on. Read on to learn the details and discover actionable ways to export data out of BigQuery.

What are the options to export data from BigQuery?

Basically, there are three ways to pull your data from BigQuery:

• Coupler.io. Easily export a BigQuery table or SQL query results to a destination of your choice. You can load data to reports in spreadsheets, such as Google Sheets and Microsoft Excel, visualize queries in Looker Studio and Power BI, and transfer data to another BigQuery project or even a data warehouse. The added value of Coupler.io is that it allows you to automate your data flow to have your BigQuery data synced to the destination on a schedule. It’s of great help if the data gets updated regularly, and you want to work on fresh data in the destination.
• Manual export. This allows you to export BigQuery tables to Cloud Console and pull query results to CSV, JSON, and Google Sheets.
• Programmatic data export. You can always use a client library to enable programmatic data export of BigQuery data to your destination. However, coding skills are required. 

Which one is the best for you? It depends on your requirements. However, the most actionable approach to exporting data from BigQuery is the one that allows you to automate data flow. 

Automate BigQuery export data with Coupler.io

To demonstrate how Coupler.io works for automating data flows, we show examples of the two most common destinations for BigQuery data export: Google Sheets and Microsoft Excel. To make you a complete expert, we also discuss how to proceed in the opposite direction to import data from any source supported by Coupler.io into BigQuery. So, let’s get down to this.

Export BigQuery table to a spreadsheet

When you need to quickly share data or create a simple report out of your BigQuery data, spreadsheet software like Google Sheets is a perfect destination. Coupler.io lets you connect BigQuery to Google Sheets in 3 simple steps:

Step 1: Collect data from BigQuery

• To start the export process, just click Proceed in the widget below.

Source application – BigQuery

• Sign up to Coupler.io for free (if you haven’t done so yet). Next, connect to your Google BigQuery account. For this, you’ll need to select a Google Cloud key file stored on your device and click Save.

• Enter the SQL query to export a table or a specific data range out of BigQuery. For example, to export an entire table, your SQL query string should look like this:

SELECT * FROM `{project}.{data-set}.{table}`

In our example, the SQL query looks as follows:

SELECT * FROM `test-project-310805.Test.Xero data`

Note: Coupler.io lets you pull data from multiple sources at once to enhance your data reporting. For example, you can collect data from multiple tables, join several queries to build a single report, and so on. To achieve it, once you’re done with BigQuery, choose another source under Add a few more sources and configure it appropriately. Repeat the steps for next sources of your choice. Check out the available BigQuery integrations.

Step 2: Transform and organize data from BigQuery on the fly

Now, you can use the options above your data view to optionally modify your BigQuery data before it reaches Google Sheets. With Coupler.io, you can:

• Edit columns according to your liking (hide, reorder, rename, etc.)
• Filter and sort data entries
• Create custom columns with values calculated based on your own formulas
• Blend data from the same or different apps/services 

Thanks to that, you can clean up and customize your data before it reaches the destination.

Once ready, proceed to the next wizard step to set up Google Sheets.

Step 3: Load query to Google Sheets and schedule refresh

• Connect your Google account. Then choose a spreadsheet on your Google Drive and a sheet where to load the data from BigQuery. You can also create a new spreadsheet by simply typing its name in the Spreadsheet field.

Optional parameters include the cell range where to import your data range, import mode, and last updated column. Next, proceed to the last step, Flow settings.

Coupler.io lets you automatically export your data at a custom frequency (e.g. every hour) to keep it updated in the destination. To auto sync your BQ query, toggle Automatic data refresh on and configure time and date settings appropriately.

• In the end, click Save and run to export data from BigQuery to Google Sheets.

Now, when you open the destination spreadsheet, you’ll see it filled with the BigQuery data you’ve just exported.

In a similar way, you can connect BigQuery to Excel by changing the destination app and workbook. Let’s see how this works in the next example.

BigQuery export query results to a workbook

The native functionality allows you to export query results from BigQuery to Google Drive as CSV, JSON, or Google Sheets. You can also download queries to a local device as CSV or JSON. However, for maximum convenience, you can use Coupler.io to directly pull your SQL query results into Excel. Click Proceed below to begin.

• Configure BigQuery as the data source in a similar fashion to Google Sheets, remembering to paste your query code under SQL query:

• Proceed with optional steps to add other data sources and apply transformations to the data loaded into Coupler.io.
• Next, connect to your Microsoft account and select a workbook stored on OneDrive and a worksheet to load your data from BigQuery.

If you want, enable and configure automatic data refresh just as you did for Google Sheets. Finally, click Save and Run, to export your query results from BigQuery to an Excel workbook.

With Coupler.io ETL tool, you can not only automate exports of data out of BigQuery but also copy BigQuery tables to other datasets or projects.

Where you can export query results from BigQuery

For your convenience, we’ve divided the destinations available with Coupler.io according to the purpose of data export:

Data collaboration and reporting

To create simple reports and share data with external and internal stakeholders, export query results to spreadsheet apps. Depending on your stakeholder’s ecosystem, Coupler.io offers an easy way to export data to the most popular tools such as:

• Google Sheets
• Microsoft Excel

Data visualization

Exporting to BI tools allows users to visualize their BigQuery data. With Coupler.io, you get the following rich choice of BI tool integrations:

• Looker Studio
• Power BI
• Tableau
• Qlik Sense

Data backup

Wish to simply back up some of your critical BigQuery data? Or don’t want to keep all eggs in one basket to be on the safe side? In Coupler.io, you can find the following data destinations that can serve as your BigQuery data backup:

• BigQuery — Choose the same app/service as the source and destination, but configure a different BigQuery project for the destination to back up your data in that different project.  
• Redshift — Backing your BigQuery data up in Amazon’s competitive solution, you not only increase your data security but also stop being dependent on one data warehouse vendor.
• PostgreSQL — if you want to go open source, this might be the best choice. In addition, PostgreSQL has some compelling advantages, the most notable being more predictable costs of querying, advanced SQL features (complex joins, subqueries, etc.), or better control over infrastructure (cloud vs. on-premises server).

Better work on tasks and projects

Monday.com is a work management platform that you can use as a data destination in Coupler.io. Moving your data from BigQuery to monday.com might be beneficial for a number of reasons. For example, to create tasks automatically based on BigQuery data, to offer data-based insights where the actual work takes place, etc.

Support for incompatible tools & solutions

Use Coupler.io to quickly get BigQuery data into a JSON file. You can then use the file in an app/service that natively doesn’t offer BigQuery integration to transfer the data. JSON is a very popular lightweight data-interchange format that every data processing tool is likely to support.

As you can see, Coupler.io is quite a versatile tool that fits perfectly a number of data export purposes. However, it gives you even more possibilities when going the other way around, back to BigQuery. 

Export from and to BigQuery with one tool

With Coupler.io, you can not only easily export information from BigQuery but also supply BigQuery with flows of fresh data from +60 apps & services! All you need to do is complete these 3 steps with the tool’s intuitive wizard:

Step 1: Connect to desired data source(s)

• In the widget below, choose the desired source of your data (BigQuery is already selected as the destination) and click Proceed.

You’ll be able to sign up to Coupler.io for free if you haven’t used the tool yet.

• Connect to your data source (in our example, it’s Shopify), and configure all the required steps and (if you wish) the optional ones.

Note: If you want, you can add more data sources under Add a few more sources, combine the data from them next, and import everything at once to BigQuery. 

Step 2: Transform collected data

• Next, use the features available in the Transformations step if you wish to improve your source data presentation by hiding some unnecessary columns, summarizing your data, etc. For more information, refer to this section of the article. 

Step 3: Set up BigQuery as the destination and schedule data refresh

• Connect to your Google BigQuery account. For this, you’ll need to select a Google Cloud key file stored on your device and click Save. 
• Provide the name of an existing dataset and table to which you wish to import data from the source(s). Alternatively, just type new names to create them. Make also sure Autodetect table schema is enabled to facilitate the process of interpreting the source data.

• Click Continue and move on to the Flow settings step. To have your source data synced automatically at a selected interval (e.g. once a day, once an hour, every 15 minutes, etc.), enable Automatic data refresh and configure date and time settings. Finally, click Save and Run to start the data export to BigQuery.

You’ve done it! Now, when you access BigQuery, you’ll see the newly imported data there.  

Manual BigQuery export limit and data types

Now, let’s talk about manual BigQuery data export. Every table in BigQuery has the Export button, which seems to be a key for getting data out of BigQuery.

However, it allows you to export your table to GCS – Google Cloud Storage. Another limitation is that the only available formats are CSV, JSON, Avro, and Parquet.

You can also export query results from BigQuery in different formats to Google Drive or your local device:

• Export as a CSV file to Google Drive or download it to your device.
• Export as a JSON file to Google Drive or download it to your device.
• Export as a Google Sheets file to Google Drive.
• Save query results as a BigQuery table.
• Copy to Clipboard.

We’ll focus on this in the next section. Other formats, such as Excel, or destinations are not supported in the native BigQuery export data functionality. In addition, there are a bunch of other export limitations:

• A single file for export cannot be more than 1 GB. 
• Nested and repeated data in CSV format is not supported.
• Export in JSON format converts the symbols <, >, and & to their Unicode notation – for example, sales&income will be converted to sales\u0026income.
• Exporting data from multiple tables at once is not supported.

The native BigQuery export data function is rather shallow, so it makes sense to use Coupler.io or other third-party connectors that can expand it. Nevertheless, let’s check out how you can export BigQuery tables or query results manually.

BigQuery export to CSV explained

Let’s check out two cases for BigQuery data export to CSV: 

• Export an entire table
• Export an SQL query

BigQuery export query results to CSV

• Run your SQL query. In our case, it looks like this:

SELECT * FROM `couplerio-demo.xero.paid invoices` WHERE subtotal > 300 AND type = "ACCREC"

• Click Save results => select CSV and the destination associated with its export: local device or Google Drive. You can save up to 1GB as CSV to GDrive and only 10MB locally.

Whichever BigQuery export data option you choose, the CSV file with your SQL query results will be downloaded right away either to your computer or Google Drive associated with your Google account.

Export BigQuery table to CSV

As we mentioned above, you can only export a BigQuery table as CSV to Google Cloud Storage. However, if you need to export it to your computer or Google Drive, you can do this by saving query results as explained above.

All you need to do is run an SQL query that returns your entire table, for example, like this:

SELECT * FROM `couplerio-demo.xero.paid invoices`

Then, you only have to save your results as a CSV file. That’s it.

BigQuery data export – which way is the best one

From an unbiased perspective, we have to admit that each way for BigQuery export data has its benefits and drawbacks. For one-time exports, the native functionality works fine. You need to make a couple of clicks to get your query results or an entire BigQuery table to Google Sheets, CSV, or JSON. 

For recurring exports that are useful for analytical or reporting purposes, it’s better to connect BigQuery to your destination. Coupler.io allows you to choose between Google Sheets and Microsoft Excel for BigQuery scheduled exports. It also lets you connect BigQuery to Tableau, Looker Studio, or Power BI, to name a few. Choose wisely and good luck with your data!

Automate data export to and from BigQuery with Coupler.io

Get started for free

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries??