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:

• Manual export. This allows you to export BigQuery tables to Cloud Console and pull query results to CSV, JSON, and Google Sheets.
• Automated export using a third-party solution. This allows you to export tables and queries to different destinations such as Excel or Google Sheets, and even automate this dataflow. The destinations and features for BigQuery data export depend on the tool you choose. For example, with Coupler.io, you can schedule exports of BigQuery data to Google Sheets, Excel, and other BigQuery projects.
• 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. 

BigQuery export data method to automate reporting & analytics

We decided to start with the introduction of the automated way of exporting data from BigQuery using Coupler.io.

Coupler.io is a data automation and analytics platform. It provides an ETL solution to automate BigQuery data exports at a custom frequency like every hour or even 15 minutes. The destination for the exported data is cloud-based: either Google Sheets, Microsoft Excel, or BigQuery. Yes, you can transfer data from BigQuery to BigQuery (another project or dataset).

This method is quite beneficial if you want to keep your exported dataset up to date without manually exporting data all the time. Let’s see how it works in practice.

Export BigQuery table to a spreadsheet

In the project called test-project-310805, we have a dataset called Test and a table called Xero data.

To export this BigQuery table to Google Sheets, we need to do the following:

• Sign up to Coupler.io, click Add new importer, and select the source app (Bigquery) and destination app (Google Sheets). After that, you’ll need to configure the connection to these selected apps.

Source application – BigQuery

• Connect to Google BigQuery account. For this, you’ll need to select a Google Cloud key file stored on your device and click Save. 

Here are detailed instructions on how to get a Google Cloud key file.

• 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`

Click Continue to go to the destination setup.

Destination application – Google Sheets

• 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 configure optional parameters, such as the cell range where to import your data range, import mode, and last updated column.

That’s it! You can click Save and Run to export your data from BigQuery right away. Data enthusiasts, however, are eager to know what is hidden behind the Continue button 🙂 Let’s discover!

Note: Coupler.io also supports BigQuery as a destination for multiple supported sources. Check out the available BigQuery integrations.

Schedule

Coupler.io allows you to set a BigQuery scheduled export, i.e., automate exports of your BQ data at a custom frequency, such as every hour or every day. To implement this, you need to toggle on the Automatic data refresh and configure it:

• Choose interval
• Choose days of the week
• Choose time preferences
• Choose time zone

Eventually, you need to click Save and Run to export data from BigQuery to a spreadsheet. Here is what the result looks like:

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, we need to pull the following SQL query into an Excel file.

SELECT * FROM `test-project-310805.Test.Xero data` 
WHERE subtotal > 300 
  AND type = "ACCREC"

Coupler.io can easily do this BigQuery export data job. The flow is the same as we described in the previous section. However, you only need to choose Excel as a destination app, connect to your Microsoft account, and select a workbook stored on OneDrive. 

And don’t forget to copy and paste your SQL query to a respective field when setting up the source. 

In a few moments, after you’ve clicked Save and Run, welcome your query results in your 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.

What you can use the BigQuery scheduled export for?

The capability to export data from BigQuery on a schedule is beneficial for different reporting and analytical needs. You can create a report that will update automatically at the set frequency thus relieving you from manual routing on data refresh. Or you can have a self-updating dashboard that will let you keep your finger on the pulse of your project. For example, the following dashboard on ad campaign performance uses the BigQuery scheduled exports of data from BigQuery to Google Sheets, which is connected to Looker.

If you want a dashboard like this or another custom analytical solution for your business, check out Coupler.io’s data analytics consulting service. The team of data experts can solve different tasks related to data automation management, visualization, and many more.

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 only 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 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. At the same time, you can check out other third-party options if you want to automate dataflow to another place. Choose wisely and good luck with your data!