Coupler.io Blog

BigQuery Export Data – Options And Tools

  • Zakhar Yung

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

    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

    Source application – BigQuery

    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:

    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

    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.

    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.

    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:

    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:

    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:

    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)

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

    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

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

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

    Try Coupler.io for free

    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:

    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:

    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: 

    BigQuery export query results to CSV

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

    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
    Exit mobile version