# Export table data to Cloud Storage You can export BigQuery table data to Cloud Storage in CSV, JSON, Avro, or Parquet format for backup, archival, or processing in other tools. After you [load your data into BigQuery](https://docs.cloud.google.com/bigquery/docs/loading-data), you can export it by using the Google Cloud console, the bq command-line tool, the API, client libraries, or [alternative export methods](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#other_export_options). ## Export limitations When you export data from BigQuery, note the following: > [!CAUTION] > **Caution:** If you are exporting data to a Cloud Storage bucket, we strongly recommend that you disable the [Bucket Lock](https://docs.cloud.google.com/storage/docs/using-bucket-lock.md.txt#set-policy) and [Soft Delete](https://docs.cloud.google.com/storage/docs/use-soft-delete.md.txt) retention policies on your bucket. When you export to a bucket with these retention policies, BigQuery attempts to rewrite files to the bucket, which can fail if the bucket's retention policy prevents a file from being overwritten, resulting in additional incurred charges. You can re-enable these policies after the export completes. - You cannot export table data to a local file, to Google Sheets, or to Google Drive. The only supported export location is Cloud Storage. For information on saving query results, see [Downloading and saving query results](https://docs.cloud.google.com/bigquery/docs/writing-results.md.txt#downloading-saving-results-console). - You can export up to 1 GB of logical table data size to a single file. If you are exporting more than 1 GB of data, use a [wildcard](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#exporting_data_into_one_or_more_files) to export the data into multiple files. When you export data to multiple files, the size of the files will vary. To [limit the exported file size](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#limit_the_exported_file_size), you can partition your data and export each partition. For more information and recommendations about exporting large tables, see [Large table export considerations](https://docs.cloud.google.com/bigquery/docs/export-intro.md.txt#large_table_export_considerations). - The generated file size when using the `EXPORT DATA` statement is not guaranteed. - The number of files generated by an extract job can vary. - You cannot export nested and repeated data in CSV format. Nested and repeated data are supported for Avro, JSON, and Parquet exports. - When you export data in [JSON](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types.md.txt#json_type) format, [INT64](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types.md.txt#integer_types) (integer) data types are encoded as JSON strings to preserve 64-bit precision when the data is read by other systems. - You cannot export data from multiple tables in a single extract job. - You cannot choose a compression type other than `GZIP` when you export data using the Google Cloud console. - When you export a table in JSON format, the symbols `<`, `>`, and `&` are converted by using the unicode notation `\uNNNN`, where `N` is a hexadecimal digit. For example, `profit&loss` becomes `profit\u0026loss`. This unicode conversion is done to avoid security vulnerabilities. - The order of exported table data is not guaranteed unless you use the [`EXPORT DATA`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/export-statements.md.txt#export_data_statement) statement and specify an `ORDER BY` clause in the `query_statement`. When you export to multiple files, the ordered sequence is preserved globally across all generated files. - BigQuery doesn't support Cloud Storage resource paths that include multiple consecutive slashes after the initial double slash. Cloud Storage object names can contain multiple consecutive slash ("/") characters. However, BigQuery converts multiple consecutive slashes into a single slash. For example, the following resource path, though valid in Cloud Storage, doesn't work in BigQuery: `gs://bucket/my//object//name`. - Any new data loaded into BigQuery while an extract job is running won't be included in that extract job. You must create a new extract job to export the new data. - The Cloud Storage [generation number](https://docs.cloud.google.com/storage/docs/metadata.md.txt#generation-number) might change one or more times during the export process. If you run a job on the new Cloud Storage object immediately after export, don't specify a generation number. ## Before you begin Grant [Identity and Access Management (IAM)](https://docs.cloud.google.com/iam/docs.md.txt) roles that give users the necessary permissions to perform each task in this document. ### Required permissions To perform the tasks in this document, you need the following permissions. #### Permissions to export data from a BigQuery table To export data from a BigQuery table, you need the `bigquery.tables.export` IAM permission. Each of the following predefined IAM roles includes the `bigquery.tables.export` permission: - `roles/bigquery.dataViewer` - `roles/bigquery.dataOwner` - `roles/bigquery.dataEditor` - `roles/bigquery.admin` #### Permissions to run an extract job To run an extract [job](https://docs.cloud.google.com/bigquery/docs/managing-jobs.md.txt), you need the `bigquery.jobs.create` IAM permission. Each of the following predefined IAM roles includes the permissions that you need in order to run an extract job: - `roles/bigquery.user` - `roles/bigquery.jobUser` - `roles/bigquery.admin` #### Permissions to write the data to the Cloud Storage bucket To write the data to an existing Cloud Storage bucket, you need the following IAM permissions: - `storage.objects.create` - `storage.objects.delete` Each of the following predefined IAM roles includes the permissions that you need in order to write the data to an existing Cloud Storage bucket: - `roles/storage.objectAdmin` - `roles/storage.admin` For more information about IAM roles and permissions in BigQuery, see [Predefined roles and permissions](https://docs.cloud.google.com/bigquery/docs/access-control.md.txt). ## Export formats and compression types BigQuery supports the following data formats and compression types for exported data. | Data format | Supported compression types | Details | |---|---|---| | CSV | GZIP | You can control the CSV delimiter in your exported data by using the [`--field_delimiter`](https://docs.cloud.google.com/bigquery/docs/reference/bq-cli-reference.md.txt#bq_extract) bq command-line tool flag or the [`configuration.extract.fieldDelimiter`](https://docs.cloud.google.com/bigquery/docs/reference/rest/v2/Job.md.txt#jobconfigurationextract) extract job property. Nested and repeated data is not supported. | | JSON | GZIP | Nested and repeated data are supported. | | Avro | DEFLATE, SNAPPY | GZIP is not supported for Avro exports. Nested and repeated data are supported. See [Avro export details](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#avro_export_details). | | Parquet | SNAPPY, GZIP, ZSTD | Nested and repeated data are supported. See [Parquet export details](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#parquet_export_details). | ## Export data The following sections show you how to export your table data, table metadata, and query results to Cloud Storage. ### Export table data You can export table data by: - Using the Google Cloud console - Using the [`bq extract`](https://docs.cloud.google.com/bigquery/docs/reference/bq-cli-reference.md.txt#bq_extract) command in the bq command-line tool - Submitting an `extract` job using the API or client libraries Select one of the following: ### Console 1. Open the BigQuery page in the Google Cloud console. [Go to the BigQuery page](https://console.cloud.google.com/bigquery) 2. In the left pane, click **Explorer**: ![Highlighted button for the Explorer pane.](https://docs.cloud.google.com/static/bigquery/images/explorer-tab.png) If you don't see the left pane, click **Expand left pane** to open the pane. 3. In the **Explorer** pane, expand your project, click **Datasets**, and then click your dataset. 4. Click **Overview \> Tables**, and then select a table. 5. In the details pane, click **More \> Export to Google Cloud Storage**. 6. In the **Export to Google Cloud Storage** dialog: - For **GCS Location**, browse for the bucket, folder, or file where you want to export the data. - For **Export format**, choose the format for your exported data: CSV, JSON (Newline Delimited), Avro, or Parquet. - For **Compression** , select a compression format or select `None` for no compression. 7. Click **Save** to export the table. To check on the progress of the job, in the **Explorer** pane, click **Job history** , and look for an **EXTRACT** type job. To export views to Cloud Storage, use [`EXPORT DATA OPTIONS` statement](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/export-statements.md.txt). ### SQL Use the [`EXPORT DATA` statement](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/export-statements.md.txt#export_data_statement). The following example exports selected fields from a table named `mydataset.table1`:
1. In the Google Cloud console, go to the **BigQuery** page. [Go to BigQuery](https://console.cloud.google.com/bigquery) 2. In the query editor, enter the following statement: ```googlesql EXPORT DATA OPTIONS ( uri = 'gs://bucket/folder/*.csv', format = 'CSV', overwrite = true, header = true, field_delimiter = ';') AS ( SELECT field1, field2 FROM mydataset.table1 ORDER BY field1 ); ```
3. Click **Run**.
For more information about how to run queries, see [Run an interactive query](https://docs.cloud.google.com/bigquery/docs/running-queries.md.txt#queries). > [!NOTE] > **Note:** `LIMIT` clauses in `EXPORT DATA` statements can often lead to slow export job execution. For this reason, we don't recommend using `LIMIT` clauses in `EXPORT DATA` statements. ### bq Use the [`bq extract`](https://docs.cloud.google.com/bigquery/docs/reference/bq-cli-reference.md.txt#bq_extract) command with the `--destination_format` flag. (Optional) Supply the `--location` flag and set the value to your [location](https://docs.cloud.google.com/bigquery/docs/locations.md.txt). Other optional flags include: - `--compression`: The compression type to use for exported files. - `--field_delimiter`: The character that indicates the boundary between columns in the output file for CSV exports. Both `\t` and `tab` are allowed for tab delimiters. - `--print_header`: When specified, print header rows for formats that have headers such as CSV. ``` bq extract --location=location \ --destination_format format \ --compression compression_type \ --field_delimiter delimiter \ --print_header=boolean \ project_id:dataset.table \ gs://bucket/filename.ext ``` Where: - location is the name of your location. The `--location` flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to `asia-northeast1`. You can set a default value for the location using the [.bigqueryrc file](https://docs.cloud.google.com/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags). - format is the format for the exported data: `CSV`, `NEWLINE_DELIMITED_JSON`, `AVRO`, or `PARQUET`. - compression_type is a supported compression type for your data format. See [Export formats and compression types](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#export_formats_and_compression_types). - delimiter is the character that indicates the boundary between columns in CSV exports. `\t` and `tab` are accepted names for tab. - boolean is `true` or `false`. When set to `true`, header rows are printed to the exported data if the data format supports headers. The default value is `true`. - project_id is your project ID. - dataset is the name of the source dataset. - table is the table you're exporting. If you use a [partition decorator](https://docs.cloud.google.com/bigquery/docs/partitioned-tables.md.txt#partition_decorators), then you must surround the table path with single quotation marks or escape the `$` character. - bucket is the name of the Cloud Storage bucket to which you're exporting the data. The BigQuery dataset and the Cloud Storage bucket must be in the same [location](https://docs.cloud.google.com/bigquery/docs/locations.md.txt). - filename.ext is the name and extension of the exported data file. You can export to multiple files using a [wildcard](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#exporting_data_into_one_or_more_files). Examples: For example, the following command exports `mydataset.mytable` into a gzip compressed file named `myfile.csv`. `myfile.csv` is stored in a Cloud Storage bucket named `example-bucket`. ```bash bq extract \ --compression GZIP \ 'mydataset.mytable' \ gs://example-bucket/myfile.csv ``` The default destination format is CSV. To export into JSON or Avro, use the `destination_format` flag and set it to either `NEWLINE_DELIMITED_JSON` or `AVRO`. For example: ```bash bq extract \ --destination_format NEWLINE_DELIMITED_JSON \ 'mydataset.mytable' \ gs://example-bucket/myfile.json ``` The following command exports `mydataset.mytable` into an Avro file that is compressed using Snappy. The file is named `myfile.avro`. `myfile.avro` is exported to a Cloud Storage bucket named `example-bucket`. ```bash bq extract \ --destination_format AVRO \ --compression SNAPPY \ 'mydataset.mytable' \ gs://example-bucket/myfile.avro ``` The following command exports a single partition of `mydataset.my_partitioned_table` into a CSV file in Cloud Storage: ```bash bq extract \ --destination_format CSV \ 'mydataset.my_partitioned_table$0' \ gs://example-bucket/single_partition.csv ``` ### API To export data, create an `extract` job and populate the job configuration. > [!NOTE] > **Note:** If you are exporting data to the Parquet format, it might be faster to use the [BigQuery export to Parquet (via BigQuery Storage API) template](https://docs.cloud.google.com/dataflow/docs/guides/templates/provided/bigquery-to-parquet.md.txt) rather than writing a custom solution. (Optional) Specify your location in the `location` property in the `jobReference` section of the [job resource](https://docs.cloud.google.com/bigquery/docs/reference/rest/v2/jobs.md.txt). 1. Create an extract job that points to the BigQuery source data and the Cloud Storage destination. 2. Specify the source table by using the `sourceTable` configuration object that contains the project ID, dataset ID, and table ID. 3. The `destination URI(s)` property must be fully-qualified, in the format `gs://bucket/filename.ext`. Each URI can contain one '\*' wildcard character and it must come after the bucket name. 4. Specify the data format by setting the `configuration.extract.destinationFormat` property. For example, to export a JSON file, set this property to the value `NEWLINE_DELIMITED_JSON`. 5. To check the job status, call [jobs.get(job_id)](https://docs.cloud.google.com/bigquery/docs/reference/v2/jobs/get) with the ID of the job returned by the initial request. - If `status.state = DONE`, the job completed successfully. - If the `status.errorResult` property is present, the request failed, and that object will include information describing what went wrong. - If `status.errorResult` is absent, the job finished successfully, although there might have been some nonfatal errors. Nonfatal errors are listed in the returned job object's `status.errors` property. **API notes:** - As a best practice, generate a unique ID and pass it as `jobReference.jobId` when calling `jobs.insert` to create a job. This approach is more robust to network failure because the client can poll or retry on the known job ID. - Calling `jobs.insert` on a given job ID is idempotent; in other words, you can retry as many times as you like on the same job ID, and at most one of those operations will succeed. ### C# Before trying this sample, follow the C# setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery C# API reference documentation](https://docs.cloud.google.com/dotnet/docs/reference/Google.Cloud.BigQuery.V2/latest). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```c# using Google.Cloud.BigQuery.V2; using System; public class BigQueryExtractTable { public void ExtractTable( string projectId = "your-project-id", string bucketName = "your-bucket-name") { BigQueryClient client = BigQueryClient.Create(projectId); // Define a destination URI. Use a single wildcard URI if you think // your exported data will be larger than the 1 GB maximum value. string destinationUri = $"gs://{bucketName}/shakespeare-*.csv"; BigQueryJob job = client.CreateExtractJob( projectId: "bigquery-public-data", datasetId: "samples", tableId: "shakespeare", destinationUri: destinationUri ); job = job.PollUntilCompleted().ThrowOnAnyError(); // Waits for the job to complete. Console.Write($"Exported table to {destinationUri}."); } } ``` ### Go Before trying this sample, follow the Go setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Go API reference documentation](https://godoc.org/cloud.google.com/go/bigquery). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```go import ( "context" "fmt" "cloud.google.com/go/bigquery" ) // exportTableAsCompressedCSV demonstrates using an export job to // write the contents of a table into Cloud Storage as CSV. func exportTableAsCSV(projectID, gcsURI string) error { // projectID := "my-project-id" // gcsUri := "gs://mybucket/shakespeare.csv" ctx := context.Background() client, err := bigquery.NewClient(ctx, projectID) if err != nil { return fmt.Errorf("bigquery.NewClient: %v", err) } defer client.Close() srcProject := "bigquery-public-data" srcDataset := "samples" srcTable := "shakespeare" gcsRef := bigquery.NewGCSReference(gcsURI) gcsRef.FieldDelimiter = "," extractor := client.DatasetInProject(srcProject, srcDataset).Table(srcTable).ExtractorTo(gcsRef) extractor.DisableHeader = true // You can choose to run the job in a specific location for more complex data locality scenarios. // Ex: In this example, source dataset and GCS bucket are in the US. extractor.Location = "US" job, err := extractor.Run(ctx) if err != nil { return err } status, err := job.Wait(ctx) if err != nil { return err } if err := status.Err(); err != nil { return err } return nil } ``` ### Java Before trying this sample, follow the Java setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Java API reference documentation](https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/overview). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```java import com.google.cloud.RetryOption; import com.google.cloud.bigquery.BigQuery; import com.google.cloud.bigquery.BigQueryException; import com.google.cloud.bigquery.BigQueryOptions; import com.google.cloud.bigquery.Job; import com.google.cloud.bigquery.Table; import com.google.cloud.bigquery.TableId; import org.threeten.bp.Duration; public class ExtractTableToCsv { public static void runExtractTableToCsv() { // TODO(developer): Replace these variables before running the sample. String projectId = "bigquery-public-data"; String datasetName = "samples"; String tableName = "shakespeare"; String bucketName = "my-bucket"; String destinationUri = "gs://" + bucketName + "/path/to/file"; // For more information on export formats available see: // https://cloud.google.com/bigquery/docs/exporting-data#export_formats_and_compression_types // For more information on Job see: // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html String dataFormat = "CSV"; extractTableToCsv(projectId, datasetName, tableName, destinationUri, dataFormat); } // Exports datasetName:tableName to destinationUri as raw CSV public static void extractTableToCsv( String projectId, String datasetName, String tableName, String destinationUri, String dataFormat) { try { // Initialize client that will be used to send requests. This client only needs to be created // once, and can be reused for multiple requests. BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); TableId tableId = TableId.of(projectId, datasetName, tableName); Table table = bigquery.getTable(tableId); Job job = table.extract(dataFormat, destinationUri); // Blocks until this job completes its execution, either failing or succeeding. Job completedJob = job.waitFor( RetryOption.initialRetryDelay(Duration.ofSeconds(1)), RetryOption.totalTimeout(Duration.ofMinutes(3))); if (completedJob == null) { System.out.println("Job not executed since it no longer exists."); return; } else if (completedJob.getStatus().getError() != null) { System.out.println( "BigQuery was unable to extract due to an error: \n" + job.getStatus().getError()); return; } System.out.println( "Table export successful. Check in GCS bucket for the " + dataFormat + " file."); } catch (BigQueryException | InterruptedException e) { System.out.println("Table extraction job was interrupted. \n" + e.toString()); } } } ``` ### Node.js Before trying this sample, follow the Node.js setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Node.js API reference documentation](https://googleapis.dev/nodejs/bigquery/latest/index.html). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```javascript // Import the Google Cloud client libraries const {BigQuery} = require('@google-cloud/bigquery'); const {Storage} = require('@google-cloud/storage'); const bigquery = new BigQuery(); const storage = new Storage(); async function extractTableToGCS() { // Exports my_dataset:my_table to gcs://my-bucket/my-file as raw CSV. /** * TODO(developer): Uncomment the following lines before running the sample. */ // const datasetId = "my_dataset"; // const tableId = "my_table"; // const bucketName = "my-bucket"; // const filename = "file.csv"; // Location must match that of the source table. const options = { location: 'US', }; // Export data from the table into a Google Cloud Storage file const [job] = await bigquery .dataset(datasetId) .table(tableId) .extract(storage.bucket(bucketName).file(filename), options); console.log(`Job ${job.id} created.`); // Check the job's status for errors const errors = job.status.errors; if (errors && errors.length > 0) { throw errors; } } ``` ### PHP Before trying this sample, follow the PHP setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery PHP API reference documentation](https://docs.cloud.google.com/php/docs/reference/cloud-bigquery/latest/BigQueryClient). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```php use Google\Cloud\BigQuery\BigQueryClient; /** * Extracts the given table as json to given GCS bucket. * * @param string $projectId The project Id of your Google Cloud Project. * @param string $datasetId The BigQuery dataset ID. * @param string $tableId The BigQuery table ID. * @param string $bucketName Bucket name in Google Cloud Storage */ function extract_table( string $projectId, string $datasetId, string $tableId, string $bucketName ): void { $bigQuery = new BigQueryClient([ 'projectId' => $projectId, ]); $dataset = $bigQuery->dataset($datasetId); $table = $dataset->table($tableId); $destinationUri = "gs://{$bucketName}/{$tableId}.json"; // Define the format to use. If the format is not specified, 'CSV' will be used. $format = 'NEWLINE_DELIMITED_JSON'; // Create the extract job $extractConfig = $table->extract($destinationUri)->destinationFormat($format); // Run the job $job = $table->runJob($extractConfig); // Waits for the job to complete printf('Exported %s to %s' . PHP_EOL, $table->id(), $destinationUri); } ``` ### Python Before trying this sample, follow the Python setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Python API reference documentation](https://docs.cloud.google.com/python/docs/reference/bigquery/latest). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```python # from google.cloud import bigquery # client = bigquery.Client() # bucket_name = 'my-bucket' project = "bigquery-public-data" dataset_id = "samples" table_id = "shakespeare" destination_uri = "gs://{}/{}".format(bucket_name, "shakespeare.csv") dataset_ref = bigquery.DatasetReference(project, dataset_id) table_ref = dataset_ref.table(table_id) extract_job = client.extract_table( table_ref, destination_uri, # Location must match that of the source table. location="US", ) # API request extract_job.result() # Waits for job to complete. print( "Exported {}:{}.{} to {}".format(project, dataset_id, table_id, destination_uri) ) ``` ### Ruby Before trying this sample, follow the Ruby setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Ruby API reference documentation](https://googleapis.dev/ruby/google-cloud-bigquery/latest/Google/Cloud/Bigquery.html). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```ruby require "google/cloud/bigquery" def extract_table bucket_name = "my-bucket", dataset_id = "my_dataset_id", table_id = "my_table_id" bigquery = Google::Cloud::Bigquery.new dataset = bigquery.dataset dataset_id table = dataset.table table_id # Define a destination URI. Use a single wildcard URI if you think # your exported data will be larger than the 1 GB maximum value. destination_uri = "gs://#{bucket_name}/output-*.csv" extract_job = table.extract_job destination_uri do |config| # Location must match that of the source table. config.location = "US" end extract_job.wait_until_done! # Waits for the job to complete puts "Exported #{table.id} to #{destination_uri}" end ``` ### Export table metadata To export table metadata from [Iceberg tables](https://docs.cloud.google.com/bigquery/docs/iceberg-tables), use the following SQL statement: ```googlesql EXPORT TABLE METADATA FROM `[[PROJECT_NAME.]DATASET_NAME.]TABLE_NAME`; ``` Replace the following: - PROJECT_NAME: the name of the project for the table. The value defaults to the project that runs this query. - DATASET_NAME: the name of the dataset for the table. - TABLE_NAME: the name of the table. The exported metadata is located in the STORAGE_URI`/metadata` folder, where STORAGE_URI is the table's storage location set in the options. ### Export query results You can export your query results to Cloud Storage in the Google Cloud console with the following steps: 1. Open the BigQuery page in the Google Cloud console. [Go to the BigQuery page](https://console.cloud.google.com/bigquery) 2. Click **SQL query**. 3. Enter a valid GoogleSQL query in the **Query editor** text area. 4. Click **Run**. 5. When the results are returned, click **Save results** \> **Cloud Storage**. 6. In the **Export to Google Cloud Storage** dialog: - For **GCS Location**, browse for the bucket, folder, or file where you want to export the data. - For **Export format**, choose the format for your exported data: CSV, JSON (Newline Delimited), Avro, or Parquet. - For **Compression** , select a compression format or select `None` for no compression. 7. Click **Save** to export the query results. To check on the progress of the job, expand the **Job history** pane and look for the job with the `EXTRACT` type. ## Other ways to export data You can also export the results of a query by using the [`EXPORT DATA`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/export-statements.md.txt#export_data_statement) statement. You can use [`EXPORT DATA OPTIONS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/export-statements.md.txt#gcs_s3_export_option) to specify the format of the exported data. Finally, you can use a service such as [Dataflow](https://docs.cloud.google.com/dataflow/what-is-google-cloud-dataflow) to read data from BigQuery instead of exporting it from BigLake. For more information about using Dataflow to read from and write to BigQuery, see [BigQuery I/O documentation](https://beam.apache.org/documentation/io/built-in/google-bigquery). ## Avro export details BigQuery expresses Avro formatted data in the following ways: - The resulting export files are Avro container files. - Each BigQuery row is represented as an Avro record. Nested data is represented by nested record objects. - `REQUIRED` fields are represented as the corresponding Avro types. For example, a BigQuery `INTEGER` type maps to an Avro `LONG` type. - `NULLABLE` fields are represented as an Avro union of the corresponding type and "null". - `REPEATED` fields are represented as Avro arrays. - `TIMESTAMP` data types are represented as `timestamp-micros` logical type (it annotates an Avro `LONG` type) by default in both Extract jobs and Export Data SQL. (Caution: you can add `use_avro_logical_types=False` to `Export Data Options` to disable the logical type so it uses `string` type instead on timestamp column, but in Extract Jobs, it always uses the Avro logical type.) - `DATE` data types are represented as `date` logical type (it annotates an Avro `INT` types) by default in Export Data SQL, but are represented as `string` type by default in Extract jobs. (Note: you can add `use_avro_logical_types=False` to `Export Data Options` to disable the logical type, or use the flag `--use_avro_logical_types=True` to enable the logical type in Extract jobs.) - `TIME` data types are represented as `timestamp-micro` logical type (it annotates an Avro `LONG` types) by default in Export Data SQL, but are represented as `string` type by default in Extract jobs. (Note: you can add `use_avro_logical_types=False` to `Export Data Options` to disable the logical type, or use the flag `--use_avro_logical_types=True` to enable the logical type in Extract jobs.) - `DATETIME` data types are represented as Avro `STRING` types (a string type with custom named logical type `datetime`) by default in Export Data SQL, but are represented as `string` type by default in Extract jobs. (Note: you can add `use_avro_logical_types=False` to `Export Data Options` to disable the logical type, or use the flag `--use_avro_logical_types=True` to enable logical type in Extract jobs.) - [RANGE types](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types.md.txt#range_type) aren't supported in Avro export. > [!NOTE] > **Note:** The encoding of string type follows the Internet Engineering Task Force [RFC 3339](https://www.ietf.org/rfc/rfc3339.txt) spec. Parameterized `NUMERIC(P[, S])` and `BIGNUMERIC(P[, S])` data types transfer their precision and scale type parameters to the Avro decimal logical type. > [!CAUTION] > **Caution:** > > - If you export a `DATETIME` type to Avro, you cannot load the Avro file directly back into the same table schema, because the converted `STRING` won't match the schema. As a workaround, load the file into a staging table. Then use a SQL query to cast the field to a `DATETIME` type and save the result to a new table. For more information, see [Changing a column's data type](https://docs.cloud.google.com/bigquery/docs/manually-changing-schemas#changing_a_columns_data_type). > - Export Data Option `use_avro_logical_types` and Extract Job flag `--use_avro_logical_types` are applied to all the logical types at the same time once specified. The Avro format can't be used in combination with GZIP compression. To compress Avro data, use the bq command-line tool or the API and specify one of the supported compression types for Avro data: `DEFLATE` or `SNAPPY`. ## Parquet export details BigQuery converts GoogleSQL data types to the following Parquet data types: | BigQuery data type | Parquet primitive type | Parquet logical type | |---|---|---| | Integer | `INT64` | `NONE` | | Numeric | `FIXED_LEN_BYTE_ARRAY` | `DECIMAL (precision = 38, scale = 9)` | | Numeric(P\[, S\]) | `FIXED_LEN_BYTE_ARRAY` | `DECIMAL (precision = P, scale = S)` | | BigNumeric | `FIXED_LEN_BYTE_ARRAY` | `DECIMAL (precision = 76, scale = 38)` | | BigNumeric(P\[, S\]) | `FIXED_LEN_BYTE_ARRAY` | `DECIMAL (precision = P, scale = S)` | | Floating point | `FLOAT` | `NONE` | | Boolean | `BOOLEAN` | `NONE` | | String | `BYTE_ARRAY` | `STRING` `(UTF8)` | | JSON | `BYTE_ARRAY` | `STRING` `(UTF8)` | | Bytes | `BYTE_ARRAY` | `NONE` | | Date | `INT32` | `DATE` | | Datetime | `INT64` | `TIMESTAMP (isAdjustedToUTC = false, unit = MICROS)` | | Time | `INT64` | `TIME (isAdjustedToUTC = true, unit = MICROS)` | | Timestamp | `INT64` | `TIMESTAMP (isAdjustedToUTC = false, unit = MICROS)` | | Geography | `BYTE_ARRAY` | `GEOGRAPHY (edges = spherical)` | The Parquet schema represents nested data as a group and repeated records as repeated groups. For more information about using nested and repeated data in BigQuery, see [Specifying nested and repeated columns](https://docs.cloud.google.com/bigquery/docs/nested-repeated.md.txt). When you export a `JSON` column to Parquet format, it is exported as a `STRING` field. To load this Parquet file back into a `JSON` column in BigQuery, you must specify the schema in the load job (for example, by using the `--schema` flag). Auto schema detection with Parquet infers the column as a `STRING` type. > [!CAUTION] > **Caution:** If you export a `DATETIME` type to Parquet, you cannot load the Parquet file directly back into the same table schema, because the converted value won't match the schema. You can use the following workarounds for `DATETIME` types: - Load the file into a staging table. Then use a SQL query to cast the field to a `DATETIME` and save the result to a new table. For more information, see [Changing a column's data type](https://docs.cloud.google.com/bigquery/docs/managing-table-schemas.md.txt#change_a_columns_data_type). - Provide a schema for the table by using the `--schema` flag in the load job. Define the datetime column as `col:DATETIME`. The `GEOGRAPHY` logical type is represented with [GeoParquet](https://geoparquet.org) metadata added to the exported file(s). ## Exporting data into one or more files The `destinationUris` property indicates the one or more locations and filenames where BigQuery should export your files. BigQuery supports a single wildcard operator (\*) in each URI. The wildcard can appear anywhere in the filename component. Using the wildcard operator instructs BigQuery to create multiple sharded files based on the supplied pattern. The wildcard operator is replaced with a number (starting at 0), left-padded to 12 digits. For example, a URI with a wildcard at the end of the filename would create files with`000000000000` appended to the first file, and `000000000001` appended to the second file, continuing in that pattern. The following table describes several possible options for the `destinationUris` property: | `destinationUris` options || |---|---| | Single URI | Use a single URI if you are exporting table data that is 1 GB or less. This option is the most common use case, as exported data is generally less than the 1 GB maximum value. This option is not supported for the [`EXPORT DATA` statement](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/export-statements.md.txt#export_data_statement); you must use a single wildcard URI. **Property definition:** `['gs://my-bucket/file-name.json']` **Creates:** ``` gs://my-bucket/file-name.json ``` | | Single wildcard URI | A single wildcard can be used only in the filename component of the URI. Use a single wildcard URI if you think your exported data will be larger than the 1 GB maximum value. BigQuery shards your data into multiple files based on the provided pattern. The size of the exported files will vary. If your query includes an \`ORDER BY\` clause, the globally sorted result set is distributed sequentially across the generated files. **Property definition:** `['gs://my-bucket/file-name-*.json']` **Creates:** ``` gs://my-bucket/file-name-000000000000.json gs://my-bucket/file-name-000000000001.json gs://my-bucket/file-name-000000000002.json ... ``` `['gs://my-bucket/*']` **Creates:** ``` gs://my-bucket/000000000000 gs://my-bucket/000000000001 gs://my-bucket/000000000002 ... ``` | ### Limit the exported file size When you export more than 1 GB of data in a single export, you must use a wildcard to export the data into multiple files and the size of the files varies. If you need to limit the maximum size of each exported file, one option is to randomly partition your data and then export each partition to a file: 1. Determine the number of partitions you need, which is equal to the total size of your data divided by the chosen exported file size. For example, if you have 8,000 MB of data and you want each exported file to be approximately 20 MB, then you need 400 partitions. 2. Create a new table that is partitioned and clustered by a new randomly generated column called `export_id`. The following example shows how to create a new `processed_table` from an existing table called `source_table` which requires `n` partitions to achieve the chosen file size: ```googlesql CREATE TABLE my_dataset.processed_table PARTITION BY RANGE_BUCKET(export_id, GENERATE_ARRAY(0, n, 1)) CLUSTER BY export_id AS ( SELECT *, CAST(FLOOR(n*RAND()) AS INT64) AS export_id FROM my_dataset.source_table ); ``` 3. For each integer `i` between 0 and `n-1`, run an `EXPORT DATA` statement on the following query: ```googlesql SELECT * EXCEPT(export_id) FROM my_dataset.processed_table WHERE export_id = i; ``` ### Extract compressed table ### Go Before trying this sample, follow the Go setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Go API reference documentation](https://godoc.org/cloud.google.com/go/bigquery). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```go import ( "context" "fmt" "cloud.google.com/go/bigquery" ) // exportTableAsCompressedCSV demonstrates using an export job to // write the contents of a table into Cloud Storage as compressed CSV. func exportTableAsCompressedCSV(projectID, gcsURI string) error { // projectID := "my-project-id" // gcsURI := "gs://mybucket/shakespeare.csv" ctx := context.Background() client, err := bigquery.NewClient(ctx, projectID) if err != nil { return fmt.Errorf("bigquery.NewClient: %w", err) } defer client.Close() srcProject := "bigquery-public-data" srcDataset := "samples" srcTable := "shakespeare" gcsRef := bigquery.NewGCSReference(gcsURI) gcsRef.Compression = bigquery.Gzip extractor := client.DatasetInProject(srcProject, srcDataset).Table(srcTable).ExtractorTo(gcsRef) extractor.DisableHeader = true // You can choose to run the job in a specific location for more complex data locality scenarios. // Ex: In this example, source dataset and GCS bucket are in the US. extractor.Location = "US" job, err := extractor.Run(ctx) if err != nil { return err } status, err := job.Wait(ctx) if err != nil { return err } if err := status.Err(); err != nil { return err } return nil } ``` ### Java Before trying this sample, follow the Java setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Java API reference documentation](https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/overview). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```java import com.google.cloud.bigquery.BigQuery; import com.google.cloud.bigquery.BigQueryException; import com.google.cloud.bigquery.BigQueryOptions; import com.google.cloud.bigquery.ExtractJobConfiguration; import com.google.cloud.bigquery.Job; import com.google.cloud.bigquery.JobInfo; import com.google.cloud.bigquery.TableId; // Sample to extract a compressed table public class ExtractTableCompressed { public static void main(String[] args) { // TODO(developer): Replace these variables before running the sample. String projectName = "MY_PROJECT_NAME"; String datasetName = "MY_DATASET_NAME"; String tableName = "MY_TABLE_NAME"; String bucketName = "MY-BUCKET-NAME"; String destinationUri = "gs://" + bucketName + "/path/to/file"; // For more information on export formats available see: // https://cloud.google.com/bigquery/docs/exporting-data#export_formats_and_compression_types String compressed = "gzip"; // For more information on Job see: // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html String dataFormat = "CSV"; extractTableCompressed( projectName, datasetName, tableName, destinationUri, dataFormat, compressed); } public static void extractTableCompressed( String projectName, String datasetName, String tableName, String destinationUri, String dataFormat, String compressed) { try { // Initialize client that will be used to send requests. This client only needs to be created // once, and can be reused for multiple requests. BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); TableId tableId = TableId.of(projectName, datasetName, tableName); ExtractJobConfiguration extractConfig = ExtractJobConfiguration.newBuilder(tableId, destinationUri) .setCompression(compressed) .setFormat(dataFormat) .build(); Job job = bigquery.create(JobInfo.of(extractConfig)); // Blocks until this job completes its execution, either failing or succeeding. Job completedJob = job.waitFor(); if (completedJob == null) { System.out.println("Job not executed since it no longer exists."); return; } else if (completedJob.getStatus().getError() != null) { System.out.println( "BigQuery was unable to extract due to an error: \n" + job.getStatus().getError()); return; } System.out.println("Table extract compressed successful"); } catch (BigQueryException | InterruptedException e) { System.out.println("Table extraction job was interrupted. \n" + e.toString()); } } } ``` ### Node.js Before trying this sample, follow the Node.js setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Node.js API reference documentation](https://googleapis.dev/nodejs/bigquery/latest/index.html). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```javascript // Import the Google Cloud client libraries const {BigQuery} = require('@google-cloud/bigquery'); const {Storage} = require('@google-cloud/storage'); const bigquery = new BigQuery(); const storage = new Storage(); async function extractTableCompressed() { // Exports my_dataset:my_table to gcs://my-bucket/my-file as a compressed file. /** * TODO(developer): Uncomment the following lines before running the sample. */ // const datasetId = "my_dataset"; // const tableId = "my_table"; // const bucketName = "my-bucket"; // const filename = "file.csv"; // Location must match that of the source table. const options = { location: 'US', gzip: true, }; // Export data from the table into a Google Cloud Storage file const [job] = await bigquery .dataset(datasetId) .table(tableId) .extract(storage.bucket(bucketName).file(filename), options); console.log(`Job ${job.id} created.`); // Check the job's status for errors const errors = job.status.errors; if (errors && errors.length > 0) { throw errors; } } ``` ### Python Before trying this sample, follow the Python setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Python API reference documentation](https://docs.cloud.google.com/python/docs/reference/bigquery/latest). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication.md.txt#client-libs). ```python # from google.cloud import bigquery # client = bigquery.Client() # bucket_name = 'my-bucket' destination_uri = "gs://{}/{}".format(bucket_name, "shakespeare.csv.gz") dataset_ref = bigquery.DatasetReference(project, dataset_id) table_ref = dataset_ref.table("shakespeare") job_config = bigquery.job.ExtractJobConfig() job_config.compression = bigquery.Compression.GZIP extract_job = client.extract_table( table_ref, destination_uri, # Location must match that of the source table. location="US", job_config=job_config, ) # API request extract_job.result() # Waits for job to complete. ```
## Example use case This example shows how you can export data to Cloud Storage. Suppose you are streaming data to Cloud Storage from endpoint logs continuously. A daily snapshot is to be exported to Cloud Storage for backup and archival purposes. The best choice is an [extract job](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#export-data-in-bigquery) subject to certain [quotas](https://docs.cloud.google.com/bigquery/quotas.md.txt#export_jobs) and [limitations](https://docs.cloud.google.com/bigquery/docs/exporting-data.md.txt#export_limitations). Submit an extract job with the [API](https://docs.cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert.md.txt) or [client libraries](https://docs.cloud.google.com/bigquery/docs/reference/libraries.md.txt), passing in a unique ID as **`jobReference.jobId`** . Extract Jobs are asynchronous. [Check the job status](https://docs.cloud.google.com/bigquery/docs/reference/v2/jobs/get) using the unique job ID used to create the job. The job completed successfully if **`status.status`** is **`DONE`** . If **`status.errorResult`** is present, the job failed and needs to be retried. **Batch data processing** Suppose a nightly batch job is used to load data by a fixed deadline. After this load job completes, a table with statistics is materialized from a query as described in the preceding section. Data from this table is retrieved and compiled into a PDF report and sent to a regulator. Since the amount of data that needs to be read is small, use the [`tabledata.list`](https://docs.cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/list.md.txt) API to retrieve all rows of the table in JSON dictionary format. If there is more than one page of data, the results have the **`pageToken`** property set. To retrieve the next page of results, make another `tabledata.list` call and include the token value as the **`pageToken`** parameter. If the API call fails with a [5xx error](https://docs.cloud.google.com/bigquery/docs/error-messages.md.txt), retry with exponential backoff. Most 4xx errors cannot be retried. For better decoupling of BigQuery export and report generation, results should be persisted to disk. ## Quota policy For information on extract job quotas, see [Extract jobs](https://docs.cloud.google.com/bigquery/quotas.md.txt#export_jobs) on the Quotas and limits page. Usage for extract jobs is available in the `INFORMATION_SCHEMA`. The job entry in the `JOBS_BY_*` system tables for the extract job contains a `total_bytes_processed` value that can be used to monitor the aggregate usage to ensure that it stays under 50 TiB per-day. To learn how to query the `INFORMATION_SCHEMA.JOBS` view to get the `total_bytes_processed` value, see [`INFORMATION_SCHEMA.JOBS` schema](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs.md.txt#schema) ### View current quota usage You can view your current usage of query, load, extract, or copy jobs by running an `INFORMATION_SCHEMA` query to view metadata about the jobs ran over a specified time period. You can compare your current usage against the [quota limit](https://docs.cloud.google.com/bigquery/quotas#copy_jobs) to determine your quota usage for a particular type of job. The following example query uses the `INFORMATION_SCHEMA.JOBS` view to list the number of query, load, extract, and copy jobs by project: ```googlesql SELECT sum(case when job_type="QUERY" then 1 else 0 end) as QRY_CNT, sum(case when job_type="LOAD" then 1 else 0 end) as LOAD_CNT, sum(case when job_type="EXTRACT" then 1 else 0 end) as EXT_CNT, sum(case when job_type="COPY" then 1 else 0 end) as CPY_CNT FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE date(creation_time)= CURRENT_DATE() ``` You can set up a [Cloud Monitoring](https://docs.cloud.google.com/bigquery/docs/monitoring.md.txt) alerting policy that monitors the number of bytes exported. 1. In the Google Cloud console, go to the **Alerting** page: [Go to **Alerting**](https://console.cloud.google.com/monitoring/alerting)
If you use the search bar to find this page, then select the result whose subheading is **Monitoring**. 2. On the **Alerting** page, click **Create Policy**. 3. Under **Policy configuration mode** , select **Code editor (MQL or PromQL)**. 4. In the **PromQL** query editor, enter the following query: ( sum by (project_id, quota_metric, location) (increase({"serviceruntime.googleapis.com/quota/rate/net_usage", monitored_resource="consumer_quota", service="bigquery.googleapis.com"}[1m])) / max by (project_id, quota_metric, location) ({"serviceruntime.googleapis.com/quota/limit", monitored_resource="consumer_quota", service="bigquery.googleapis.com", limit_name="ExtractBytesPerDay"}) ) > 0.01 If **Auto-run** isn't enabled, then click **Run Query**. 5. Configure the rest of your alert and click **Create policy**. For detailed steps about creating PromQL-based alerting policies, see [Create PromQL-based alerting policies (Console)](https://docs.cloud.google.com/monitoring/promql/create-promql-alerts-console.md.txt). ## Troubleshooting Diagnose and troubleshoot issues with extract jobs. ### Diagnose issues using Logs Explorer To diagnose issues with extract jobs, you can use the [Logs Explorer](https://docs.cloud.google.com/logging/docs/view/logs-explorer-interface.md.txt) to review the logs for a specific extract job and identify possible errors. The following Logs Explorer filter returns information about your extract jobs: resource.type="bigquery_resource" protoPayload.methodName="jobservice.insert" (protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.query.query=~"EXPORT" OR protoPayload.serviceData.jobCompletedEvent.eventName="extract_job_completed" OR protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query=~"EXPORT") ### Exceeded extract bytes per day quota error BigQuery returns this error when the extraction exceeds the default 50 TiB daily limit in a project. For more information about extract job limits, see [Extract jobs](https://docs.cloud.google.com/bigquery/quotas.md.txt#export_jobs). **Error message** ``` Your usage exceeded quota for ExtractBytesPerDay ``` #### Diagnosis If you are exporting a table that is larger than 50 TiB, the export fails because it [exceeds the extraction limit](https://docs.cloud.google.com/bigquery/quotas.md.txt#export_jobs). If you want to [export table data](https://docs.cloud.google.com/bigquery/docs/partitioned-tables.md.txt#export_table_data) for specific table partitions, you can use a [partition decorator](https://docs.cloud.google.com/bigquery/docs/partitioned-tables.md.txt#partition_decorators) to identify the partitions to export. If you would like to gather usages of exports data over recent days, you can try the following: - [View the quotas for your project](https://docs.cloud.google.com/docs/quotas/view-manage.md.txt#view_project_quotas) with filter criteria such as `Name: Extract bytes per day` or `Metric: bigquery.googleapis.com/quota/extract/bytes` along with the Show usage chart to see your usage trend over a few days. - Alternatively you can query [`INFORMATION_SCHEMA.JOBS_BY_PROJECT`](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs.md.txt) to see your total extract bytes over a few days. For example, the following query returns the daily total bytes processed by `EXTRACT` jobs in the past seven days. ```googlesql SELECT TIMESTAMP_TRUNC(creation_time, DAY) AS day, SUM ( total_bytes_processed ) / POW(1024, 3) AS total_gibibytes_processed FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP() AND job_type = "EXTRACT" GROUP BY 1 ORDER BY 2 DESC ``` - You can then further refine the results by identifying the specific jobs that are consuming more bytes than expected. The following example returns the top 100 `EXTRACT` jobs which are consuming more than 100 GB processed over the past seven days. ```googlesql SELECT creation_time, job_id, total_bytes_processed/POW(1024, 3) AS total_gigabytes_processed FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP() AND job_type="EXTRACT" AND total_bytes_processed > (POW(1024, 3) * 100) ORDER BY total_bytes_processed DESC LIMIT 100 ``` You can alternatively use the [jobs explorer](https://docs.cloud.google.com/bigquery/docs/admin-jobs-explorer.md.txt) with filters like `Bytes processed more than` to filter for high processing jobs for a specified period of time. #### Resolution One method of resolving this quota error is to create a slot [reservation](https://docs.cloud.google.com/bigquery/docs/reservations-intro.md.txt#reservations) and [assign](https://docs.cloud.google.com/bigquery/docs/reservations-workload-management.md.txt#assignments) your project into the reservation with the `PIPELINE` job type. This method can bypass the limit check since it uses your dedicated reservations rather than a free shared slot pool. If needed, the reservation can be deleted if you want to use a shared slot pool later on. For alternative approaches that allow exporting more *than* 50 TiB, see the notes section in [Extract jobs](https://docs.cloud.google.com/bigquery/quotas.md.txt#export_jobs). ### Failed to export on nested schema error This error occurs when you try to export a table containing nested or repeated fields, such as `STRUCT` or `ARRAY` data types, to a flat-file format like CSV or newline-delimited JSON. **Error message** ``` Failed to export: Operation cannot be performed on a nested schema. Field: [COLUMN_NAME] ``` This error occurs because flat-file formats like CSV can't represent hierarchical or multi-valued data. BigQuery exports require you to *flatten* or transform this complex data into a tabular structure before it can be written in a flat-file format. In this case, the COLUMN_NAME refers to a column with [nested or repeated fields](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-nested) that need to be flattened or transformed before the data can be exported. **Resolution** Depending on your source schema, there are several methods that you can use to flatten your schema to prepare it for export. The following example shows a schema with nested `STRUCT` fields and a query that flattens them for export. In the example, the export query fails due to the nested fields in the `user_details` column. You can use the suggested fix to flatten the file structure into two columns, `user_details.name` and `user_details.email`. ```googlesql -- Schema example: root ├── id (INTEGER) ├── user_details (RECORD / STRUCT, NULLABLE) │ ├── name (STRING) │ └── email (STRING) └── created_at (TIMESTAMP) -- Export query: SELECT * FROM my_dataset.my_table; -- Suggested fix: SELECT id, user_details.name AS user_name, user_details.email AS user_email, created_at FROM my_dataset.my_table; ``` The following example shows a schema with repeated `ARRAY` fields and a query that flattens them for export. In the example, the export query fails due to the repeated fields in the `line_items` column. You can use the suggested fix which uses the `UNNEST()` operator to convert the array elements into individual rows. ```googlesql -- Schema example: root ├── order_id (STRING) ├── customer_name (STRING) └── line_items (REPEATED / ARRAY of RECORD/STRUCT, NULLABLE) ├── product_id (STRING) └── quantity (INTEGER) -- Export query: SELECT * FROM my_dataset.my_orders_table; -- Suggested fix: SELECT t.order_id, t.customer_name, item.product_id, item.quantity FROM my_dataset.my_orders_table AS t LEFT JOIN UNNEST(t.line_items) AS item ; ``` Alternatively, you can use `ARRAY_TO_STRING()` if you only need to represent an array, such as an `ARRAY`, as a single string column in your CSV file. `ARRAY_TO_STRING()` concatenates all elements of the interests array into a single string, using a comma as a separator. This transformation is useful when the exact structure of each array element isn't crucial for the export. ```googlesql -- Schema example: root ├── user_id (STRING) └── interests (REPEATED / ARRAY of STRING, NULLABLE) -- Suggested fix: SELECT user_id, ARRAY_TO_STRING(interests, ', ') AS user_interests FROM my_dataset.my_users_table; ``` ## Pricing For information on data export pricing, see the [BigQuery pricing](https://cloud.google.com/bigquery/pricing#data_extraction_pricing) page. Once the data is exported, you are charged for storing the data in Cloud Storage. For more information, see [Cloud Storage pricing](https://cloud.google.com/storage/pricing). ## Table security To control access to tables in BigQuery, see [Control access to resources with IAM](https://docs.cloud.google.com/bigquery/docs/control-access-to-resources-iam.md.txt). ## What's next - To learn more about the Google Cloud console, see [Using the Google Cloud console](https://docs.cloud.google.com/bigquery/docs/bigquery-web-ui.md.txt). - To learn more about the bq command-line tool, see [Using the bq command-line tool](https://docs.cloud.google.com/bigquery/bq-command-line-tool). - To learn how to create an application using the BigQuery API client libraries, see [Client library quickstart](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries.md.txt).