Writing Query Results

This document describes how to write query results.

Temporary and permanent tables

BigQuery saves all query results to a table, which can be either permanent or temporary:

  • A temporary table is a randomly named table saved in a special dataset; the table has a lifetime of approximately 24 hours. Temporary tables are not available for sharing, and are not visible using any of the standard list or other table manipulation methods.

  • A permanent table can be a new or existing table in any dataset in which you have WRITE privileges.

Writing query results to a permanent table

To save your query results in a permanent table:

Web UI

  1. Go to the BigQuery web UI.
    Go to the BigQuery web UI

  2. Click the Compose query button.

  3. Enter a valid BigQuery SQL query in the New Query text area.

  4. Click the Show Options button.

  5. Click the Select Table button in the Destination Table section.

  6. Enter a table ID and click OK.

  7. Click the Run query button.

This creates a query job that writes the output to a permanent table.

Alternately, if you forget to specify a destination table before running your query, you can copy the temporary table to a permanent table by clicking the Save as Table button in the results window.

Command-line

Use the --destination_table flag to create a permanent table based on the query results. For example, the following query creates a permanent table named happyhalloween in the mydataset dataset:

bq query --destination_table=mydataset.happyhalloween "SELECT name,number
    FROM [bigquery-public-data:usa_names.usa_1910_current]
    WHERE gender = 'M'
    ORDER BY number DESC
    LIMIT 6"

API

To save the query results to a permanent table, include a value for the jobs#configuration.query.destinationTable property.

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

To save query results to a permanent table, set the destination table to the desired TableId in a QueryJobConfiguration.

public static void runQueryPermanentTable(
    String queryString,
    String destinationDataset,
    String destinationTable,
    boolean allowLargeResults) throws TimeoutException, InterruptedException {
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(queryString)
          // Save the results of the query to a permanent table.
          // See: https://cloud.google.com/bigquery/querying-data#permanent-table
          .setDestinationTable(TableId.of(destinationDataset, destinationTable))
          // Allow results larger than the maximum response size.
          // If true, a destination table must be set.
          // See: https://cloud.google.com/bigquery/querying-data#large-results
          .setAllowLargeResults(allowLargeResults)
          .build();

  runQuery(queryConfig);
}
public static void runQuery(QueryJobConfiguration queryConfig)
    throws TimeoutException, InterruptedException {
  BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

  // Create a job ID so that we can safely retry.
  JobId jobId = JobId.of(UUID.randomUUID().toString());
  Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

  // Wait for the query to complete.
  queryJob = queryJob.waitFor();

  // Check for errors
  if (queryJob == null) {
    throw new RuntimeException("Job no longer exists");
  } else if (queryJob.getStatus().getError() != null) {
    // You can also look at queryJob.getStatus().getExecutionErrors() for all
    // errors, not just the latest one.
    throw new RuntimeException(queryJob.getStatus().getError().toString());
  }

  // Get the results.
  QueryResponse response = bigquery.getQueryResults(jobId);
  QueryResult result = response.getResult();

  // Print all pages of the results.
  while (result != null) {
    for (List<FieldValue> row : result.iterateAll()) {
      for (FieldValue val : row) {
        System.out.printf("%s,", val.toString());
      }
      System.out.printf("\n");
    }

    result = result.getNextPage();
  }
}

Writing large query results

Normally, queries have a maximum response size. If you plan to run a query that might return larger results, you can set allowLargeResults to true in your job configuration. Configuring large results requires you to specify a destination table. You incur storage charges for the destination table.

Queries with large results are subject to these limitations:

  • You must specify a destination table.
  • You cannot specify a top-level ORDER BY, TOP or LIMIT clause. Doing so negates the benefit of using allowLargeResults, because the query output can no longer be computed in parallel.
  • Window functions can return large query results only if used in conjunction with a PARTITION BY clause.

To allow large query results:

Web UI

  1. Go to the BigQuery web UI.
    Go to the BigQuery web UI

  2. Click the Compose query button.

  3. Enter a valid BigQuery SQL query in the New Query text area.

  4. Click Show Options.

  5. For Destination Table, click Select Table and type a name in the Table ID field.

  6. For Results Size, check Allow Large Results.

Command-line

Use the allow_large_results flag with the destination_table flag to create a destination table to hold the large results set:

 bq query --destination_table '[DATASET].[TABLE_NAME]' --allow_large_results "[QUERY]"

API

To enable large results, set the configuration.query.allowLargeResults property to true and specify a destination table using configuration.query.destinationTable.

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

To enable large results, set allow large results to true and set the destination table to the desired TableId in a QueryJobConfiguration.

public static void runQueryPermanentTable(
    String queryString,
    String destinationDataset,
    String destinationTable,
    boolean allowLargeResults) throws TimeoutException, InterruptedException {
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(queryString)
          // Save the results of the query to a permanent table.
          // See: https://cloud.google.com/bigquery/querying-data#permanent-table
          .setDestinationTable(TableId.of(destinationDataset, destinationTable))
          // Allow results larger than the maximum response size.
          // If true, a destination table must be set.
          // See: https://cloud.google.com/bigquery/querying-data#large-results
          .setAllowLargeResults(allowLargeResults)
          .build();

  runQuery(queryConfig);
}
public static void runQuery(QueryJobConfiguration queryConfig)
    throws TimeoutException, InterruptedException {
  BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

  // Create a job ID so that we can safely retry.
  JobId jobId = JobId.of(UUID.randomUUID().toString());
  Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

  // Wait for the query to complete.
  queryJob = queryJob.waitFor();

  // Check for errors
  if (queryJob == null) {
    throw new RuntimeException("Job no longer exists");
  } else if (queryJob.getStatus().getError() != null) {
    // You can also look at queryJob.getStatus().getExecutionErrors() for all
    // errors, not just the latest one.
    throw new RuntimeException(queryJob.getStatus().getError().toString());
  }

  // Get the results.
  QueryResponse response = bigquery.getQueryResults(jobId);
  QueryResult result = response.getResult();

  // Print all pages of the results.
  while (result != null) {
    for (List<FieldValue> row : result.iterateAll()) {
      for (FieldValue val : row) {
        System.out.printf("%s,", val.toString());
      }
      System.out.printf("\n");
    }

    result = result.getNextPage();
  }
}

Downloading and saving query results

If a query result set has fewer than 16,000 rows and is 10MB or smaller, you can download it to your local machine as a CSV or newline-delimited JSON file. Otherwise, you can only save it as a table.

BigQuery web UI screenshot of download and save buttons

Download query results

To download query results as a CSV file:

  • Click the Download as CSV button above the query results.

To download query results as a JSON file:

  • Click the Download as JSON button above the query results.

Save query results

To save query results as a table:

  • Click the Save as Table button above the query results.

To save query results to Google Sheets:

  • Click the Save to Google Sheets button above the query results. When saving to Google Sheets, the result set must have fewer than 16,000 rows and must be 10MB or smaller.
If the results set contains nested and repeated data, you cannot download the results as a CSV file, and you cannot save the results to Google Sheets.

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...