Introduction to loading data
This document provides an overview of loading data into BigQuery.
There are several ways to ingest data into BigQuery:
- Batch load a set of data records.
- Stream individual records or batches of records.
- Use queries to generate new data and append or overwrite the results to a table.
- Use a third-party application or service.
With batch loading, you load the source data into a BigQuery table in a single batch operation. For example, the data source could be a CSV file, an external database, or a set of log files. Traditional extract, transform, and load (ETL) jobs fall into this category.
Options for batch loading in BigQuery include the following:
- Load jobs. Load data from Cloud Storage or from a local file by creating a load job. The records can be in Avro, CSV, JSON, ORC, or Parquet format.
- SQL. The
LOAD DATASQL statement loads data from one or more files into a new or existing table. You can use the
LOAD DATAstatement to load Avro, CSV, JSON, ORC, or Parquet files.
- BigQuery Data Transfer Service. Use BigQuery Data Transfer Service to automate loading data from Google Software as a Service (SaaS) apps or from third-party applications and services.
- BigQuery Storage Write API. The Storage Write API lets you batch-process an arbitrarily large number of records and commit them in a single atomic operation. If the commit operation fails, you can safely retry the operation. Unlike BigQuery load jobs, the Storage Write API does not require staging the data to intermediate storage such as Cloud Storage.
- Other managed services. Use other managed services to export data from an external data store and import it into BigQuery. For example, you can load data from Firestore exports.
When choosing a batch load method, most file-based patterns should use either
load job or
LOAD DATA SQL statement to batch load data. Other services should
generally use BigQuery Data Transfer Service or export data from Google services.
Batch loading can be done as a one-time operation or on a recurring schedule. For example, you can do the following:
- You can run BigQuery Data Transfer Service transfers on a schedule.
- You can use an orchestration service such as Cloud Composer to schedule load jobs.
- You can use a cron job to load data on a schedule.
With streaming, you continually send smaller batches of data in real time, so the data is available for querying as it arrives. Options for streaming in BigQuery include the following:
- Storage Write API. The Storage Write API supports high-throughput streaming ingestion with exactly-once delivery semantics.
- Dataflow. Use Dataflow with the Apache Beam SDK to set up a streaming pipeline that writes to BigQuery. For more information, see BigQuery I/O connector in the Apache Beam documentation and the Stream from Pub/Sub to BigQuery tutorial.
- Datastream. Datastream uses BigQuery change data capture functionality and the Storage Write API to replicate data and schema updates from operational databases directly into BigQuery. Follow this quickstart for an example of replicating from a Cloud SQL for PostgreSQL database into BigQuery.
- BigQuery Connector for SAP. The BigQuery Connector for SAP enables near real time replication of SAP data directly into BigQuery. For more information, see the BigQuery Connector for SAP planning guide.
- Pub/Sub. Pub/Sub is a messaging service you can use to coordinate streaming analytics and data integration pipelines. You can use BigQuery subscriptions to write messages directly to an existing BigQuery table.
You can use SQL to generate data and store the results in BigQuery. Options for generating data include:
Use data manipulation language (DML) statements to perform bulk inserts into an existing table or store query results in a new table.
CREATE TABLE ... ASstatement to create a new table from a query result.
Run a query and save the results to a table. You can append the results to an existing table or write to a new table. For more information, see Writing query results.
Some third-party applications and services provide connectors that can ingest data into BigQuery. The details of how to configure and manage the ingestion pipeline depend on the application. For example, to load data from external sources to BigQuery's storage, you can use Informatica Data Loader or Fivetran Data Pipelines. For more information, see Load data using a third-party application.
Choosing a data ingestion method
Here are some considerations to think about when you choose a data ingestion method.
Data source. The source of the data or the data format can determine whether batch loading or streaming is simpler to implement and maintain. Consider the following points:
If BigQuery Data Transfer Service supports the data source, transferring the data directly into BigQuery might be the simplest solution to implement.
If your data comes from Spark or Hadoop, consider using BigQuery connectors to simplify data ingestion.
For local files, consider batch load jobs, especially if BigQuery supports the file format without requiring a transformation or data cleansing step.
For application data such as application events or a log stream, it might be easier to stream the data in real time, rather than implement batch loading.
Slow-changing versus fast-changing data. If you need to ingest and analyze data in near real time, consider streaming the data. With streaming, the data is available for querying as soon as each record arrives. Avoid using DML statements to submit large numbers of individual row updates or insertions. For frequently updated data, it's often better to stream a change log and use a view to obtain the latest results. Another option is to use Cloud SQL as your online transaction processing (OLTP) database and use federated queries to join the data in BigQuery.
If your source data changes slowly or you don't need continuously updated results, consider using a load job. For example, if you use the data to run a daily or hourly report, load jobs can be less expensive and can use fewer system resources.
Another scenario is data that arrives infrequently or in response to an event. In that case, consider using Dataflow to stream the data or use Cloud Functions to call the streaming API in response to a trigger.
Reliability of the solution. BigQuery has a Service Level Agreement (SLA). However, you also need to consider the reliability of the particular solution that you implement. Consider the following points:
- With loosely typed formats such as JSON or CSV, bad data can make an entire load job fail. Consider whether you need a data cleansing step before loading, and consider how to respond to errors. Also consider using a strongly typed format such as Avro, ORC, or Parquet.
- Periodic load jobs require scheduling, using Cloud Composer, cron, or another tool. The scheduling component could be a failure point in the solution.
- With streaming, you can check the success of each record and quickly report an error. Consider writing failed messages to an unprocessed messages queue for later analysis and processing. For more information about BigQuery streaming errors, see Troubleshooting streaming inserts.
- Streaming and load jobs are subject to quotas. For information about how to handle quota errors, see Troubleshooting BigQuery quota errors.
- Third-party solutions might differ in configurability, reliability, ordering guarantees, and other factors, so consider these before adopting a solution.
Latency. Consider how much data you load and how soon you need the data to be available. Streaming offers the lowest latency of data being available for analysis. Periodic load jobs have a higher latency, because new data is only available after each load job finishes.
Load jobs use a shared pool of slots by default. A load job might wait in a pending state until slots are available, especially if you load a very large amount of data. If that creates unacceptable wait times, you can purchase dedicated slots, instead of using the shared slot pool. For more information, see Introduction to Reservations.
Query performance for external data sources might not be as high as query performance for data stored in BigQuery. If minimizing query latency is important, then we recommend loading the data into BigQuery.
Data ingestion format. Choose a data ingestion format based on the following factors:
Schema support. Avro, ORC, Parquet, and Firestore exports are self-describing formats. BigQuery creates the table schema automatically based on the source data. For JSON and CSV data, you can provide an explicit schema, or you can use schema auto-detection.
Flat data or nested and repeated fields. Avro, CSV, JSON, ORC, and Parquet all support flat data. Avro, JSON, ORC, Parquet, and Firestore exports also support data with nested and repeated fields. Nested and repeated data is useful for expressing hierarchical data. Nested and repeated fields also reduce data duplication when loading the data.
Embedded newlines. When you are loading data from JSON files, the rows must be newline delimited. BigQuery expects newline-delimited JSON files to contain a single record per line.
Encoding. BigQuery supports UTF-8 encoding for both nested or repeated and flat data. BigQuery supports ISO-8859-1 encoding for flat data only for CSV files.
Load nested and repeated data
You can load data into nested and repeated fields in the following data formats:
- JSON (newline delimited)
- Datastore exports
- Firestore exports
For information about specifying nested and repeated fields in your schema when you're loading data, see Specifying nested and repeated fields.
Load data from other Google services
Some Google services export data to BigQuery using scheduled queries, exports, or transfers. For more information about services that support exports to BigQuery, see Load data from Google services.
Other Google services support data exports initiated from BigQuery Data Transfer Service. For more information about services that support exports initiated by BigQuery Data Transfer Service, see BigQuery Data Transfer Service.
Alternatives to loading data
You don't need to load data before running queries in the following situations:
- Public datasets
- Public datasets are datasets stored in BigQuery and shared with the public. For more information, see BigQuery public datasets.
- Shared datasets
- You can share datasets stored in BigQuery. If someone has shared a dataset with you, you can run queries on that dataset without loading the data.
- External data sources
- BigQuery can run queries on certain forms of external data, without loading the data into BigQuery storage. This approach lets you take advantage of the analytic capabilities of BigQuery without moving data that is stored elsewhere. For information about the benefits and limitations of this approach, see external data sources.
- Logging files
- Cloud Logging provides an option to export log files into BigQuery. See Configure and manage sinks for more information.
To learn how to load data from Cloud Storage into BigQuery, see the documentation for your data format:
To learn how to load data from a local file, see Loading data from local files.
For information about streaming data, see Streaming data into BigQuery.
To learn how to connect BigQuery to Databricks, see Connect Databricks to BigQuery