Back to Blog

Export Data Out of BigQuery – Options And Tools

BigQuery is a warehouse where you can store and query tons of data accumulated from multiple sources. There are a few options for importing data to BigQuery. And what if you need to export data from BigQuery, for example, to share a piece of a dataset with third parties? The “Export” button should do the job, right? It’s not as obvious as it seems. 

Read on to learn the details and discover the really actionable ways to export data out of BigQuery.

BigQuery export: data types and limitations

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. We’ll focus on this in the next section. Other formats, such as Excel, or destinations are not supported in the native BigQuery functionality. In addition, there are a bunch of other export limitations:

  • Downloading as a local file is not supported.
  • 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.

What about exporting query results from BigQuery?

The exporting capabilities for the queries are better implemented since you can variate between options:

  • Export as a CSV file to Google Drive or download to your device.
  • Export as a JSON file to Google Drive or download to your device.
  • Export as a Google Sheets file to Google Drive.
  • Copy to Clipboard.

Note: Saving results as a separate BigQuery table is of no interest to us since the data remains within BigQuery.

Nevertheless, the BigQuery native exporting function is rather shallow. However, there are third-party options that can expand it. One of them is Coupler.io, which allows you to export BigQuery data to a spreadsheet app – Google Sheets or Excel. These are two popular destinations for a better manipulation of your data. Let’s see how it works.

How to export data from BigQuery

First, sign up to Coupler.io –  you can do this with your Google account. Click “Add new importer“, name it whatever you want, and go to the source setup. 

Source application – BigQuery

  • Select BigQuery as a source application. Click “Continue“.
  • 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}`

Click “Continue” to go to the destination setup.

Destination application – Google Sheets or Excel

  • Choose the destination app you want to export BigQuery data – Google Sheets or Excel.

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

  • Depending on the application you select, Excel or Google Sheets, the flow will be mostly the same. 
    • You’ll need to connect to your account, either Google or Microsoft.
    • You’ll need to choose a spreadsheet on your Google Drive or a workbook on your OneDrive to load BigQuery data.
    • You’ll be able to configure the optional parameters, such as the first cell 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!

Schedule

Coupler.io allows you to automate export of your BQ data on a schedule, 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

This is quite beneficial if you want to keep your exported dataset up to date without manually exporting data all the time. 

Now let’s see how this method works in practice.

Google BigQuery export 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 set up source and destination as described above and use the SQL query where you select the entire table:

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

For this example, we won’t automate data export, so let’s simply click “Save and Run” to export data to a spreadsheet. There you go:

In a similar way, you can export 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

We’ve queried our dataset with the following SQL query:

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

The native functionality allows you to export the query results to Google Drive as CSV, JSON, or Google Sheets, or download to a local device as CSV or JSON. However, we need to pull the results to an Excel file. Coupler.io can easily do the job. All you need to do is select 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.

BigQuery export data options you can rely on

So, the options you have include:

  • Manual export of BigQuery tables to Cloud Console
  • Manual export of query results to CSV, JSON, and Google Sheets
  • Automated export of queries to Excel and Google Sheets with Coupler.io

Of course, you can always use a client library to enable programmatic data export of BigQuery data to your destination. However, coding skills are required. 

If you need a no-code approach, Coupler.io seems to be the best fit for exporting data and query results out of BigQuery. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free