Export query results to a file

This document describes how to save query results as a file, such as CSV or JSON.

Download query results to a local file

Downloading query results to a local file is not supported by the bq command-line toolor the API.

To download query results as a CSV or newline-delimited JSON file, use theGoogle Cloud console:

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. Clickadd_boxSQL query.

3. Enter a valid GoogleSQL query in theQuery editor text area.

4. Optional: To change the processing location, clickMore and selectQuery settings. ForData location,choose thelocation of your data.

5. ClickRun.

6. When the results are returned, clickSave results and select theformat or location where you want to save the results.

   The file is downloaded to your browser's default download location.

Save query results to Google Drive

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Saving query results to Google Drive is not supported by the bq command-line tool orthe API.

You might get an error when you try to save the BigQuery resultsto Google Drive. This error is due to the Drive SDK APIbeing unable to access Google Workspace. To resolve the issue,you must enable your user account toaccess Google Drivewith the Drive SDK API.

To save query results to Google Drive, use the Google Cloud console:

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. Clickadd_boxSQL query.

3. Enter a valid GoogleSQL query in theQuery editor text area.

4. ClickRun.

5. When the results are returned, clickSave results.

6. UnderGoogle Drive, selectCSV orJSON. When you save results to Google Drive, you cannot choose the location. Results are always saved to the root "My Drive" location.

7. It may take a few minutes to save the results to Google Drive. When the results are saved, you receive a dialog message that includes the filename —bq-results-[TIMESTAMP]-[RANDOM_CHARACTERS].[CSV or JSON].

   

8. In the dialog message, clickOpen to open the file, or navigate to Google Drive and clickMy Drive.

Save query results to Google Sheets

Saving query results to Google Sheets is not supported by the bq command-line tool orthe API.

You might get an error when you try to open the BigQuery resultsfrom Google Sheets. This error is due to the Drive SDK APIbeing unable to access Google Workspace. To resolve the issue,you must enable your user account toaccess Google Sheetswith the Drive SDK API.

To save query results to Google Sheets, use the Google Cloud console:

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. Clickadd_boxSQL query.

3. Enter a valid GoogleSQL query in theQuery editor text area.

4. Optional: To change the processing location, clickMore and selectQuery settings. ForData location,choose thelocation of your data.

5. ClickRun.

6. When the results are returned, click theSave results and selectGoogle Sheets.

7. If necessary, follow the prompts to log into your user account andclickAllow to give BigQuery permission to write the datato your Google DriveMY Drive folder.

   After following the prompts, you should receive an email confirming that BigQuery client tools have been connected to your user account. The email contains information on the permissions you granted along with steps to remove the permissions.

8. When the results are saved, a message similar to the following appearsbelow the query results in the Google Cloud console:Saved to Sheets as"results-20190225-103531. Open. Click the link in the message to view yourresults in Google Sheets, or navigate to yourMy Drive folder and open thefile manually.

   When you save query results to Google Sheets, the filename begins withresults-[DATE] where[DATE] is today's date in the formatYYYYMMDD.

   Note: Saving results to Google Sheets is not supported by the bq command-line toolor the API. For more information, seeUsing Connected Sheets.

Troubleshoot saving results to Google Sheets

When saving data from BigQuery to Google Sheets, you mightfind that some cells in the sheets are blank. This happens when the data youare writing to the cell exceeds the Google Sheets limit of 50,000 characters.To resolve this, use astring functionin the GoogleSQL query to split the column with the long data into two or morecolumns, then save the result to sheets again.

Save query results to Cloud Storage

You can export your query results to Cloud Storage in the Google Cloud consolewith the following steps:

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. Clickadd_boxSQL query.

3. Enter a valid GoogleSQL query in theQuery editor text area.

4. ClickRun.

5. When the results are returned, clickSave results>Cloud Storage.

6. In theExport to Google Cloud Storage dialog:

   • ForGCS Location, browse for the bucket,folder, or file where you want to export the data.
   • ForExport format, choose the format for your exported data: CSV,JSON (Newline Delimited), Avro, or Parquet.
   • ForCompression, select a compression format or selectNone forno compression.

7. ClickSave to export the query results.

To check on the progress of the job, expand theJob history pane andlook for the job with theEXTRACT type.

What's next

• Learn how to programmaticallyexport a table to a JSON file.
• Learn aboutquotas for extract jobs.
• Learn aboutBigQuery storage pricing.