LoadFromGcs

Load data from GCS to BigQuery.

yaml
type: "io.kestra.plugin.gcp.bigquery.LoadFromGcs"

Examples

Load an avro file from a gcs bucket

yaml
id: gcp_bq_load_from_gcs
namespace: company.team

tasks:
  - id: http_download
    type: io.kestra.plugin.core.http.Download
    uri: https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv

  - id: csv_to_ion
    type: io.kestra.plugin.serdes.csv.CsvToIon
    from: "{{ outputs.http_download.uri }}"
    header: true

  - id: ion_to_avro
    type: io.kestra.plugin.serdes.avro.IonToAvro
    from: "{{ outputs.csv_to_ion.uri }}"
    schema: |
      {
        "type": "record",
        "name": "Order",
        "namespace": "com.example.order",
        "fields": [
          {"name": "order_id", "type": "int"},
          {"name": "customer_name", "type": "string"},
          {"name": "customer_email", "type": "string"},
          {"name": "product_id", "type": "int"},
          {"name": "price", "type": "double"},
          {"name": "quantity", "type": "int"},
          {"name": "total", "type": "double"}
        ]
      }

  - id: load_from_gcs
    type: io.kestra.plugin.gcp.bigquery.LoadFromGcs
    from:
      - "{{ outputs.ion_to_avro.uri }}"
    destinationTable: "my_project.my_dataset.my_table"
    format: AVRO
    avroOptions:
      useAvroLogicalTypes: true

Load a csv file with a defined schema

yaml
id: gcp_bq_load_files_test
namespace: company.team

tasks:
  - id: load_files_test
    type: io.kestra.plugin.gcp.bigquery.LoadFromGcs
    destinationTable: "myDataset.myTable"
    ignoreUnknownValues: true
    schema:
      fields:
        - name: colA
          type: STRING
        - name: colB
          type: NUMERIC
        - name: colC
          type: STRING
    format: CSV
    csvOptions:
      allowJaggedRows: true
      encoding: UTF-8
      fieldDelimiter: ","
    from:
      - gs://myBucket/myFile.csv

Properties

autodetect booleanstring

Experimental Automatic inference of the options and schema for CSV and JSON sources.

avroOptions AbstractLoad-AvroOptions

Avro parsing options.

clusteringFields array

SubType string

The clustering specification for the destination table.

createDisposition string

Possible Values

CREATE_IF_NEEDEDCREATE_NEVER

Whether the job is allowed to create tables.

csvOptions AbstractLoad-CsvOptions

Csv parsing options.

destinationTable string

The table where to put query results.

If not provided, a new table is created.

format string

Possible Values

CSVJSONAVROPARQUETORC

The source format, and possibly some parsing options, of the external data.

from array

SubType string

Google Cloud Storage source data

The fully-qualified URIs that point to source data in Google Cloud Storage (e.g. gs://bucket/path). Each URI can contain one '*' wildcard character and it must come after the 'bucket' name.

ignoreUnknownValues booleanstring

Whether BigQuery should allow extra values that are not represented in the table schema.

If true, the extra values are ignored. If false, records with extra columns are treated as bad records, and if there are too many bad records, an invalid error is returned in the job result. By default unknown values are not allowed.

impersonatedServiceAccount string

The GCP service account to impersonate.

location string

The geographic location where the dataset should reside.

This property is experimental and might be subject to change or removed.

See Dataset Location

maxBadRecords integerstring

The maximum number of bad records that BigQuery can ignore when running the job.

If the number of bad records exceeds this value, an invalid error is returned in the job result. By default, no bad record is ignored.

projectId string

The GCP project ID.

retryAuto Constant Exponential Random

Automatic retry for retryable BigQuery exceptions.

Some exceptions (especially rate limit) are not retried by default by BigQuery client, we use by default a transparent retry (not the kestra one) to handle this case. The default values are exponential of 5 seconds for a maximum of 15 minutes and ten attempts

retryMessages array

SubType string

Default ["due to concurrent update","Retrying the job may solve the problem","Retrying may solve the problem"]

The messages which would trigger an automatic retry.

Message is tested as a substring of the full message, and is case insensitive.

retryReasons array

SubType string

Default ["rateLimitExceeded","jobBackendError","backendError","internalError","jobInternalError"]

The reasons which would trigger an automatic retry.

schema object

The schema for the destination table.

The schema can be omitted if the destination table already exists, or if you're loading data from a Google Cloud Datastore backup (i.e. DATASTORE_BACKUP format option).

text

schema: 
  fields: 
    - name: colA
      type: STRING
    - name: colB
      type: NUMERIC

See type from StandardSQLTypeName

schemaUpdateOptions array

SubType string

Possible Values

ALLOW_FIELD_ADDITIONALLOW_FIELD_RELAXATION

Experimental Options allowing the schema of the destination table to be updated as a side effect of the query job.

Schema update options are supported in two cases: when writeDisposition is WRITE_APPEND; when writeDisposition is WRITE_TRUNCATE and the destination table is a partition of a table, specified by partition decorators. For normal tables, WRITE_TRUNCATE will always overwrite the schema.

scopes array

SubType string

Default ["https://www.googleapis.com/auth/cloud-platform"]

The GCP scopes to be used.

serviceAccount string

The GCP service account.

timePartitioningField string

The time partitioning field for the destination table.

timePartitioningType string

Default DAY

Possible Values

DAYHOURMONTHYEAR

The time partitioning type specification for the destination table.

writeDisposition string

Possible Values

WRITE_TRUNCATEWRITE_TRUNCATE_DATAWRITE_APPENDWRITE_EMPTY

The action that should occur if the destination table already exists.

Outputs

destinationTable string

Destination table

jobId string

The job id

rows integer

Output rows count

Metrics

bad.records counter

Unit records

the number of bad records reported in a job.

duration timer

The time it took for the task to run.

input.bytes counter

Unit bytes

The number of bytes of source data in a load job.

input.files counter

Unit files

The number of source files in a load job.

output.bytes counter

Unit bytes

The size of the data loaded by a load job so far, in bytes.

output.rows counter

Unit records

The number of rows loaded by a load job so far.

Definitions

io.kestra.core.models.tasks.retrys.Constant

interval *string

Format duration

type *object

behavior string

Default RETRY_FAILED_TASK

Possible Values

RETRY_FAILED_TASKCREATE_NEW_EXECUTION

maxAttempts integer

Minimum >= 1

maxDuration string

Format duration

warningOnRetry boolean

Default false

io.kestra.core.models.tasks.retrys.Random

maxInterval *string

Format duration

minInterval *string

Format duration

type *object

behavior string

Default RETRY_FAILED_TASK

Possible Values

RETRY_FAILED_TASKCREATE_NEW_EXECUTION

maxAttempts integer

Minimum >= 1

maxDuration string

Format duration

warningOnRetry boolean

Default false

io.kestra.plugin.gcp.bigquery.AbstractLoad-CsvOptions

allowJaggedRows booleanstring

Whether BigQuery should accept rows that are missing trailing optional columns.

If true, BigQuery treats missing trailing columns as null values. If {@code false}, records with missing trailing columns are treated as bad records, and if there are too many bad records, an invalid error is returned in the job result. By default, rows with missing trailing columns are considered bad records.

allowQuotedNewLines booleanstring

Whether BigQuery should allow quoted data sections that contain newline characters in a CSV file.

By default quoted newline are not allowed.

encoding string

The character encoding of the data.

The supported values are UTF-8 or ISO-8859-1. The default value is UTF-8. BigQuery decodes the data after the raw, binary data has been split using the values set in {@link #setQuote(String)} and {@link #setFieldDelimiter(String)}.

fieldDelimiter string

The separator for fields in a CSV file.

BigQuery converts the string to ISO-8859-1 encoding, and then uses the first byte of the encoded string to split the data in its raw, binary state. BigQuery also supports the escape sequence "\t" to specify a tab separator. The default value is a comma (',').

quote string

The value that is used to quote data sections in a CSV file.

BigQuery converts the string to ISO-8859-1 encoding, and then uses the first byte of the encoded string to split the data in its raw, binary state. The default value is a double-quote ('"'). If your data does not contain quoted sections, set the property value to an empty string. If your data contains quoted newline characters, you must also set {@link #setAllowQuotedNewLines(boolean)} property to {@code true}.

skipLeadingRows integerstring

The number of rows at the top of a CSV file that BigQuery will skip when reading the data

The default value is 0. This property is useful if you have header rows in the file that should be skipped.

io.kestra.core.models.tasks.retrys.Exponential

interval *string

Format duration

maxInterval *string

Format duration

type *object

behavior string

Default RETRY_FAILED_TASK

Possible Values

RETRY_FAILED_TASKCREATE_NEW_EXECUTION

delayFactor number

maxAttempts integer

Minimum >= 1

maxDuration string

Format duration

warningOnRetry boolean

Default false

io.kestra.plugin.gcp.bigquery.AbstractLoad-AvroOptions

useAvroLogicalTypes booleanstring

If format is set to AVRO, you can interpret logical types into their corresponding types (such as TIMESTAMP) instead of only using their raw types (such as INTEGER)

The value may be null.

​Load​From​Gcs

LoadFromGcs