The Cloud Storage CSV files to BigQuery pipeline is a batch pipeline that lets you read data from CSV files stored in Cloud Storage and append the result to a BigQuery table.
The CSV files can be uncompressed or compressed in formats listed in the Compression
Enum SDK page.
Pipeline requirements
To use this template, your pipeline must meet the following requirements.
BigQuery schema JSON file
Create a JSON file that describes your BigQuery schema.
Ensure that the schema has a top-level JSON array titled BigQuery Schema
and that its
contents follow the pattern {"name": "COLUMN_NAME", "type": "DATA_TYPE"}
.
The Cloud Storage CSV files to BigQuery batch template doesn't support
importing data into
STRUCT
(Record) fields in the target BigQuery table.
The following JSON describes an example BigQuery schema:
{ "BigQuery Schema": [ { "name": "location", "type": "STRING" }, { "name": "name", "type": "STRING" }, { "name": "age", "type": "STRING" }, { "name": "color", "type": "STRING" }, { "name": "coffee", "type": "STRING" } ] }
Error table schema
The BigQuery table that stores the rejected records from CSV files must match the table schema defined here.
{ "BigQuery Schema": [ { "name": "RawContent", "type": "STRING" }, { "name": "ErrorMsg", "type": "STRING" } ] }
Template parameters
Required parameters
- inputFilePattern : The Cloud Storage path to the CSV file that contains the text to process. (Example: gs://your-bucket/path/*.csv).
- schemaJSONPath : The Cloud Storage path to the JSON file that defines your BigQuery schema.
- outputTable : The name of the BigQuery table that stores your processed data. If you reuse an existing BigQuery table, the data is appended to the destination table.
- bigQueryLoadingTemporaryDirectory : The temporary directory to use during the BigQuery loading process. (Example: gs://your-bucket/your-files/temp_dir).
- badRecordsOutputTable : The name of the BigQuery table to use to store the rejected data when processing the CSV files. If you reuse an existing BigQuery table, the data is appended to the destination table. The schema of this table must match the error table schema (https://cloud.google.com/dataflow/docs/guides/templates/provided/cloud-storage-csv-to-bigquery#GcsCSVToBigQueryBadRecordsSchema).
- delimiter : The column delimiter that the CSV file uses. (Example: ,).
- csvFormat : The CSV format according to Apache Commons CSV format. Defaults to: Default.
Optional parameters
- containsHeaders : Whether headers are included in the CSV file. Defaults to: false.
- csvFileEncoding : The CSV file character encoding format. Allowed Values are US-ASCII, ISO-8859-1, UTF-8, and UTF-16. Defaults to: UTF-8.
Run the template
Console
- Go to the Dataflow Create job from template page. Go to Create job from template
- In the Job name field, enter a unique job name.
- Optional: For Regional endpoint, select a value from the drop-down menu. The default
region is
us-central1
.For a list of regions where you can run a Dataflow job, see Dataflow locations.
- From the Dataflow template drop-down menu, select the CSV files on Cloud Storage to BigQuery (Batch) template.
- In the provided parameter fields, enter your parameter values.
- Click Run job.
gcloud
In your shell or terminal, run the template:
gcloud dataflow jobs run JOB_NAME \ --gcs-location gs://dataflow-templates-REGION_NAME/VERSION/GCS_CSV_to_BigQuery \ --region REGION_NAME \ --parameters \ inputFilePattern=PATH_TO_CSV_DATA,\ schemaJSONPath=PATH_TO_BIGQUERY_SCHEMA_JSON,\ outputTable=BIGQUERY_DESTINATION_TABLE,\ badRecordsOutputTable=BIGQUERY_BAD_RECORDS_TABLE,\ csvFormat=CSV_FORMAT,\ delimiter=DELIMITER,\ bigQueryLoadingTemporaryDirectory=PATH_TO_TEMP_DIR_ON_GCS,\ containsHeaders=CONTAINS_HEADERS,\ csvFileEncoding=CSV_FILE_ENCODING
Replace the following:
PROJECT_ID
: the Google Cloud project ID where you want to run the Dataflow jobJOB_NAME
: a unique job name of your choiceVERSION
: the version of the template that you want to useYou can use the following values:
latest
to use the latest version of the template, which is available in the non-dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/latest/- the version name, like
2023-09-12-00_RC00
, to use a specific version of the template, which can be found nested in the respective dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/
REGION_NAME
: the region where you want to deploy your Dataflow job—for example,us-central1
PATH_TO_CSV_DATA
: the Cloud Storage path to your CSV filesPATH_TO_BIGQUERY_SCHEMA_JSON
: the Cloud Storage path to the JSON file containing the schema definitionBIGQUERY_DESTINATION_TABLE
: the BigQuery destination table nameBIGQUERY_BAD_RECORDS_TABLE
: the BigQuery bad records table namePATH_TO_TEMP_DIR_ON_GCS
: the Cloud Storage path to the temp directoryDELIMITER
: CSV file delimiterCSV_FORMAT
: CSV format specification for parsing recordsCONTAINS_HEADERS
: whether the CSV files contains headersCSV_FILE_ENCODING
: encoding in the CSV files
API
To run the template using the REST API, send an HTTP POST request. For more information on the
API and its authorization scopes, see
projects.templates.launch
.
POST https://dataflow.googleapis.com/v1b3/projects/PROJECT_ID/locations/LOCATION/templates:launch?gcsPath=gs://dataflow-templates-LOCATION/VERSION/GCS_CSV_to_BigQuery { "jobName": "JOB_NAME", "parameters": { "inputFilePattern":"PATH_TO_CSV_DATA", "schemaJSONPath": "PATH_TO_BIGQUERY_SCHEMA_JSON", "outputTable":"BIGQUERY_DESTINATION_TABLE", "badRecordsOutputTable":"BIGQUERY_BAD_RECORDS_TABLE", "csvFormat":"CSV_FORMAT", "delimiter":"DELIMITER", "bigQueryLoadingTemporaryDirectory": "PATH_TO_TEMP_DIR_ON_GCS", "containsHeaders": "CONTAINS_HEADERS", "csvFileEncoding": "CSV_FILE_ENCODING" }, "environment": { "zone": "us-central1-f" } }
Replace the following:
PROJECT_ID
: the Google Cloud project ID where you want to run the Dataflow jobJOB_NAME
: a unique job name of your choiceVERSION
: the version of the template that you want to useYou can use the following values:
latest
to use the latest version of the template, which is available in the non-dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/latest/- the version name, like
2023-09-12-00_RC00
, to use a specific version of the template, which can be found nested in the respective dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/
LOCATION
: the region where you want to deploy your Dataflow job—for example,us-central1
PATH_TO_CSV_DATA
: the Cloud Storage path to your CSV filesPATH_TO_BIGQUERY_SCHEMA_JSON
: the Cloud Storage path to the JSON file containing the schema definitionBIGQUERY_DESTINATION_TABLE
: the BigQuery destination table nameBIGQUERY_BAD_RECORDS_TABLE
: the BigQuery bad records table namePATH_TO_TEMP_DIR_ON_GCS
: the Cloud Storage path to the temp directoryDELIMITER
: CSV file delimiterCSV_FORMAT
: CSV format specification for parsing recordsCONTAINS_HEADERS
: whether the CSV files contains headersCSV_FILE_ENCODING
: encoding in the CSV files
What's next
- Learn about Dataflow templates.
- See the list of Google-provided templates.