Batch loading data (original) (raw)

Stay organized with collections Save and categorize content based on your preferences.

You can load data into BigQuery from Cloud Storage or from a local file as a batch operation. The source data can be in any of the following formats:

You can also use BigQuery Data Transfer Service to set up recurring loads from Cloud Storage into BigQuery.

Try it for yourself

If you're new to Google Cloud, create an account to evaluate how BigQuery performs in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

Try BigQuery free

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document, and create a dataset to store your data.

Required permissions

To load data into BigQuery, you need IAM permissions to run a load job and load data into BigQuery tables and partitions. If you are loading data from Cloud Storage, you also need IAM permissions to access the bucket that contains your data.

Permissions to load data into BigQuery

To load data into a new BigQuery table or partition or to append or overwrite an existing table or partition, you need the following IAM permissions:

Each of the following predefined IAM roles includes the permissions that you need in order to load data into a BigQuery table or partition:

Additionally, if you have the bigquery.datasets.create permission, you can create and update tables using a load job in the datasets that you create.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Permissions to load data from Cloud Storage

To get the permissions that you need to load data from a Cloud Storage bucket, ask your administrator to grant you theStorage Admin (roles/storage.admin) IAM role on the bucket. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to load data from a Cloud Storage bucket. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to load data from a Cloud Storage bucket:

You might also be able to get these permissions with custom roles or other predefined roles.

Create a dataset

Create a BigQuery dataset to store your data.

Loading data from Cloud Storage

BigQuery supports loading data from any of the following Cloud Storage storage classes:

To learn how to load data into BigQuery, see the page for your data format:

To learn how to configure a recurring load from Cloud Storage into BigQuery, seeCloud Storage transfers.

Location considerations

You cannot change the location of a dataset after it is created, but you can make a copy of the dataset or manually move it. For more information, see:

Retrieving the Cloud Storage URI

To load data from a Cloud Storage data source, you must provide the Cloud Storage URI.

The Cloud Storage resource path contains your bucket name and your object (filename). For example, if the Cloud Storage bucket is namedmybucket and the data file is named myfile.csv, the resource path would begs://mybucket/myfile.csv.

BigQuery does not 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, does not work in BigQuery:gs://bucket/my//object//name.

To retrieve the Cloud Storage resource path:

  1. Open the Cloud Storage console.
    Cloud Storage console
  2. Browse to the location of the object (file) that contains the source data.
  3. Click on the name of the object.
    The Object details page opens.
  4. Copy the value provided in the gsutil URI field, which begins withgs://.

For Google Datastore exports, only one URI can be specified, and it must end with .backup_info or .export_metadata.

Wildcard support for Cloud Storage URIs

If your data is separated into multiple files, you can use an asterisk (*) wildcard to select multiple files. Use of the asterisk wildcard must follow these rules:

Examples:

gs://mybucket/fed-samples/fed-sample*  
gs://mybucket/fed-samples/*.csv  
gs://mybucket/fed-samples/fed-sample*.csv

When using the bq command-line tool, you might need to escape the asterisk on some platforms.

You can't use an asterisk wildcard when you load Datastore or Firestore export data from Cloud Storage.

Limitations

You are subject to the following limitations when you load data into BigQuery from a Cloud Storage bucket:

Depending on the format of your Cloud Storage source data, there may be additional limitations. For more information, see:

Loading data from local files

You can load data from a readable data source (such as your local machine) by using one of the following:

When you load data using the Google Cloud console or the bq command-line tool, a load job is automatically created.

To load data from a local data source:

Console

  1. Open the BigQuery page in the Google Cloud console.
    Go to the BigQuery page
  2. In the Explorer panel, expand your project and select a dataset.
  3. Expand theActions option and click Open.
  4. In the details panel, click Create table.
  5. On the Create table page, in the Source section:
    • For Create table from, select Upload.
    • For Select file, click Browse.
    • Browse to the file, and click Open. Note that wildcards and comma-separated lists are not supported for local files.
    • For File format, select CSV, JSON (newline delimited),Avro, Parquet, or ORC.
  6. On the Create table page, in the Destination section:
    • For Project, choose the appropriate project.
    • For Dataset, choose the appropriate dataset.
    • In the Table field, enter the name of the table you're creating in BigQuery.
    • Verify that Table type is set to Native table.
  7. In the Schema section, enter the schemadefinition.
    • For CSV and JSON files, you can check the Auto-detect option to enable schema auto-detect. Schema information is self-described in the source data for other supported file types.
    • You can also enter schema information manually by:
      * Clicking Edit as text and entering the table schema as a JSON array:
      * Using Add Field to manually input the schema.
  8. Select applicable items in the Advanced options section For information on the available options, seeCSV optionsand JSON options.
  9. Optional: In the Advanced options choose the write disposition:
    • Write if empty: Write the data only if the table is empty.
    • Append to table: Append the data to the end of the table. This setting is the default.
    • Overwrite table: Erase all existing data in the table before writing the new data.
  10. Click Create Table.

bq

Use the bq load command, specify the source_format, and include the path to the local file.

(Optional) Supply the --location flag and set the value to yourlocation.

If you are loading data in a project other than your default project, add the project ID to the dataset in the following format:PROJECT_ID:DATASET.

bq --location=LOCATION load
--source_format=FORMAT
PROJECT_ID:DATASET.TABLE
PATH_TO_SOURCE
SCHEMA

Replace the following:

In addition, you can add flags for options that let you control how BigQuery parses your data. For example, you can use the--skip_leading_rows flag to ignore header rows in a CSV file. For more information, see CSV optionsand JSON options.

Examples:

The following command loads a local newline-delimited JSON file (mydata.json) into a table named mytable in mydataset in your default project. The schema is defined in a local schema file named myschema.json.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    ./mydata.json \
    ./myschema.json

The following command loads a local CSV file (mydata.csv) into a table named mytable in mydataset in myotherproject. The schema is defined inline in the formatFIELD:DATA_TYPE, FIELD:DATA_TYPE.

    bq load \
    --source_format=CSV \
    myotherproject:mydataset.mytable \
    ./mydata.csv \
    qtr:STRING,sales:FLOAT,year:STRING

The following command loads a local CSV file (mydata.csv) into a table named mytable in mydataset in your default project. The schema is defined using schema auto-detection.

    bq load \
    --autodetect \
    --source_format=CSV \
    mydataset.mytable \
    ./mydata.csv

C#

Before trying this sample, follow the C# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, use the update options class for the appropriate format from theJobCreationOptionsbase class instead of UploadCsvOptions.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, set the DataFormatproperty of the NewReaderSource to the appropriate format.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, set the FormatOptionsto the appropriate format.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, set the metadata parameter of theloadfunction to the appropriate format.

PHP

Before trying this sample, follow the PHP setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, set the sourceFormatto the appropriate format.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, set the LoadJobConfig.source_format propertyto the appropriate format.

Ruby

Before trying this sample, follow the Ruby setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Ruby API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

The following code demonstrates how to load a local CSV file to a new BigQuery table. To load a local file of another format, set the format parameter of theTable#load_jobmethod to the appropriate format.

Limitations

Loading data from a local data source is subject to the following limitations:

Load job capacity

Similar to the on-demand mode for queries, load jobs by default use a shared pool of slots. BigQuery doesn't guarantee the available capacity of this shared pool or load job throughput.

To increase throughput or predictably control the capacity of your load jobs, you can create a slot reservationand assign dedicated PIPELINE slots to run load jobs. For more information, seeReservation assignments.

Loading compressed and uncompressed data

For Avro, Parquet, and ORC formats, BigQuery supports loading files where the file data has been compressed using a supported codec. However, BigQuery doesn't support loading files in these formats that have themselves been compressed, for example by using the gzip utility.

The Avro binary format is the preferred format for loading both compressed and uncompressed data. Avro data is faster to load because the data can be read in parallel, even when the data blocks are compressed. For a list of supported compression codecs, seeAvro compression.

Parquet binary format is also a good choice because Parquet's efficient, per-column encoding typically results in a better compression ratio and smaller files. Parquet files also leverage compression techniques that allow files to be loaded in parallel. For a list of supported compression codecs, seeParquet compression.

The ORC binary format offers benefits similar to the benefits of the Parquet format. Data in ORC files is fast to load because data stripes can be read in parallel. The rows in each data stripe are loaded sequentially. To optimize load time, use a data stripe size of approximately 256 MB or less. For a list of supported compression codecs, seeORC compression.

For other data formats such as CSV and JSON, BigQuery can load uncompressed files significantly faster than compressed files because uncompressed files can be read in parallel. Because uncompressed files are larger, using them can lead to bandwidth limitations and higher Cloud Storage costs for data staged in Cloud Storage prior to being loaded into BigQuery. Keep in mind that line ordering isn't guaranteed for compressed or uncompressed files. It's important to weigh these tradeoffs depending on your use case.

In general, if bandwidth is limited, compress your CSV and JSON files by usinggzip before uploading them to Cloud Storage. gzip is the only supported file compression type for CSV and JSON files when loading data into BigQuery. If loading speed is important to your app and you have a lot of bandwidth to load your data, leave your files uncompressed.

Appending to or overwriting a table

You can load additional data into a table either from source files or by appending query results. If the schema of the data does not match the schema of the destination table or partition, you can update the schema when you append to it or overwrite it.

If you update the schema when appending data, BigQuery allows you to:

If you are overwriting a table, the schema is always overwritten. Schema updates are not restricted when you overwrite a table.

In the Google Cloud console, use the Write preference option to specify what action to take when you load data from a source file or from a query result. The bq command-line tool and the API include the following options:

Console option bq tool flag BigQuery API property Description
Write if empty None WRITE_EMPTY Writes the data only if the table is empty.
Append to table --noreplace or --replace=false; if--replace is unspecified, the default is append WRITE_APPEND (Default) Appends the data to the end of the table.
Overwrite table --replace or --replace=true WRITE_TRUNCATE Erases all existing data in a table before writing the new data.

Quota policy

For information about the quota policy for batch loading data, seeLoad jobs on the Quotas and limits page.

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

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()

Pricing

There is no charge for batch loading data into BigQuery using the shared slot pool. For more information, seeBigQuery data ingestion pricing.

Example use case

Suppose there is a nightly batch processing pipeline that needs to be completed by a fixed deadline. Data needs to be available by this deadline for further processing by another batch process to generate reports to be sent to a regulator. This use case is common in regulated industries such as finance.

Batch loading of data with load jobsis the right approach for this use case because latency is not a concern provided the deadline can be met. Ensure your Cloud Storage bucketsmeet the location requirementsfor loading data into the BigQuery dataset.

The result of a BigQuery load job is atomic; either all records get inserted or none do. As a best practice, when inserting all data in a single load job, create a new table by using the WRITE_TRUNCATE disposition of the JobConfigurationLoadresource. This is important when retrying a failed load job, as the client might not be able to distinguish between jobs that have failed and the failure caused by for example in communicating the success state back to the client.

Assuming data to be ingested has been successfully copied to Cloud Storage already, retrying with exponential backoff is sufficient to address ingestion failures.

It's recommended that a nightly batch job doesn't hit thedefault quotaof 1,500 loads per table per day even with retries. When loading data incrementally, the default quota is sufficient for running a load job every 5 minutes and have unconsumed quota for at least 1 retry per job on average.