airflow.providers.google.cloud.hooks.bigquery

This module contains a BigQuery Hook, as well as a very basic PEP 249 implementation for BigQuery.

Module Contents

Classes

BigQueryHook

Interact with BigQuery. This hook uses the Google Cloud connection.

BigQueryConnection

BigQuery does not have a notion of a persistent connection. Thus, these

BigQueryBaseCursor

The BigQuery base cursor contains helper methods to execute queries against

BigQueryCursor

A very basic BigQuery PEP 249 cursor implementation. The PyHive PEP 249

Functions

split_tablename(table_input, default_project_id[, ...])

Attributes

log

BigQueryJob

airflow.providers.google.cloud.hooks.bigquery.log[source]
airflow.providers.google.cloud.hooks.bigquery.BigQueryJob[source]
class airflow.providers.google.cloud.hooks.bigquery.BigQueryHook(gcp_conn_id=GoogleBaseHook.default_conn_name, delegate_to=None, use_legacy_sql=True, location=None, api_resource_configs=None, impersonation_chain=None, labels=None)[source]

Bases: airflow.providers.google.common.hooks.base_google.GoogleBaseHook, airflow.providers.common.sql.hooks.sql.DbApiHook

Interact with BigQuery. This hook uses the Google Cloud connection.

Parameters

  • gcp_conn_id (str) – The Airflow connection used for GCP credentials.

  • delegate_to (Optional[str]) – This performs a task on one host with reference to other hosts.

  • use_legacy_sql (bool) – This specifies whether to use legacy SQL dialect.

  • location (Optional[str]) – The location of the BigQuery resource.

  • api_resource_configs (Optional[Dict]) – This contains params configuration applied for Google BigQuery jobs.

  • impersonation_chain (Optional[Union[str, Sequence[str]]]) – This is the optional service account to impersonate using short term credentials.

  • labels (Optional[Dict]) – The BigQuery resource label.

conn_name_attr = gcp_conn_id[source]
default_conn_name = google_cloud_bigquery_default[source]
conn_type = gcpbigquery[source]
hook_name = Google Bigquery[source]
get_conn()[source]

Returns a BigQuery PEP 249 connection object.

get_service()[source]

Returns a BigQuery service object.

get_client(project_id=None, location=None)[source]

Returns authenticated BigQuery Client.

Parameters

  • project_id (Optional[str]) – Project ID for the project which the client acts on behalf of.

  • location (Optional[str]) – Default location for jobs / datasets / tables.

Returns

Return type

google.cloud.bigquery.Client

get_uri()[source]

Override DbApiHook get_uri method for get_sqlalchemy_engine()

get_sqlalchemy_engine(engine_kwargs=None)[source]

Get an sqlalchemy_engine object.

Parameters

engine_kwargs – Kwargs used in create_engine().

Returns

the created engine.

get_records(sql, parameters=None)[source]

Executes the sql and returns a set of records.

Parameters

  • sql – the sql statement to be executed (str) or a list of sql statements to execute

  • parameters – The parameters to render the SQL query with.

abstract insert_rows(table, rows, target_fields=None, commit_every=1000, replace=False, **kwargs)[source]

Insertion is currently unsupported. Theoretically, you could use BigQuery’s streaming API to insert rows into a table, but this hasn’t been implemented.

get_pandas_df(sql, parameters=None, dialect=None, **kwargs)[source]

Returns a Pandas DataFrame for the results produced by a BigQuery query. The DbApiHook method must be overridden because Pandas doesn’t support PEP 249 connections, except for SQLite. See:

https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L447 https://github.com/pydata/pandas/issues/6900

Parameters

  • sql (str) – The BigQuery SQL to execute.

  • parameters (Optional[Union[Iterable, Mapping]]) – The parameters to render the SQL query with (not used, leave to override superclass method)

  • dialect (Optional[str]) – Dialect of BigQuery SQL – legacy SQL or standard SQL defaults to use self.use_legacy_sql if not specified

  • kwargs – (optional) passed into pandas_gbq.read_gbq method

table_exists(dataset_id, table_id, project_id)[source]

Checks for the existence of a table in Google BigQuery.

Parameters

  • project_id (str) – The Google cloud project in which to look for the table. The connection supplied to the hook must provide access to the specified project.

  • dataset_id (str) – The name of the dataset in which to look for the table.

  • table_id (str) – The name of the table to check the existence of.

table_partition_exists(dataset_id, table_id, partition_id, project_id)[source]

Checks for the existence of a partition in a table in Google BigQuery.

Parameters

  • project_id (str) – The Google cloud project in which to look for the table. The connection supplied to the hook must provide access to the specified project.

  • dataset_id (str) – The name of the dataset in which to look for the table.

  • table_id (str) – The name of the table to check the existence of.

  • partition_id (str) – The name of the partition to check the existence of.

create_empty_table(project_id=None, dataset_id=None, table_id=None, table_resource=None, schema_fields=None, time_partitioning=None, cluster_fields=None, labels=None, view=None, materialized_view=None, encryption_configuration=None, retry=DEFAULT_RETRY, location=None, exists_ok=True)[source]

Creates a new, empty table in the dataset. To create a view, which is defined by a SQL query, parse a dictionary to ‘view’ kwarg

Parameters

Example:

schema_fields=[{"name": "emp_name", "type": "STRING", "mode": "REQUIRED"},
               {"name": "salary", "type": "INTEGER", "mode": "NULLABLE"}]
Parameters

Example:

view = {
    "query": "SELECT * FROM `test-project-id.test_dataset_id.test_table_prefix*` LIMIT 1000",
    "useLegacySql": False
}
Parameters

  • materialized_view (Optional[Dict]) – [Optional] The materialized view definition.

  • encryption_configuration (Optional[Dict]) –

    [Optional] Custom encryption configuration (e.g., Cloud KMS keys). Example:

    encryption_configuration = {
    "kmsKeyName": "projects/testp/locations/us/keyRings/test-kr/cryptoKeys/test-key"
}

  • num_retries – Maximum number of retries in case of connection problems.

  • location (Optional[str]) – (Optional) The geographic location where the table should reside.

  • exists_ok (bool) – If True, ignore “already exists” errors when creating the table.

Returns

Created table

Return type

google.cloud.bigquery.table.Table

create_empty_dataset(dataset_id=None, project_id=None, location=None, dataset_reference=None, exists_ok=True)[source]

Create a new empty dataset: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/insert

Parameters

  • project_id (Optional[str]) – The name of the project where we want to create an empty a dataset. Don’t need to provide, if projectId in dataset_reference.

  • dataset_id (Optional[str]) – The id of dataset. Don’t need to provide, if datasetId in dataset_reference.

  • location (Optional[str]) – (Optional) The geographic location where the dataset should reside. There is no default value but the dataset will be created in US if nothing is provided.

  • dataset_reference (Optional[Dict[str, Any]]) – Dataset reference that could be provided with request body. More info: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets#resource

  • exists_ok (bool) – If True, ignore “already exists” errors when creating the dataset.

get_dataset_tables(dataset_id, project_id=None, max_results=None, retry=DEFAULT_RETRY)[source]

Get the list of tables for a given dataset.

For more information, see: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/list

Parameters

  • dataset_id (str) – the dataset ID of the requested dataset.

  • project_id (Optional[str]) – (Optional) the project of the requested dataset. If None, self.project_id will be used.

  • max_results (Optional[int]) – (Optional) the maximum number of tables to return.

  • retry (google.api_core.retry.Retry) – How to retry the RPC.

Returns

List of tables associated with the dataset.

Return type

List[Dict[str, Any]]

delete_dataset(dataset_id, project_id=None, delete_contents=False, retry=DEFAULT_RETRY)[source]

Delete a dataset of Big query in your project.

Parameters

  • project_id (Optional[str]) – The name of the project where we have the dataset.

  • dataset_id (str) – The dataset to be delete.

  • delete_contents (bool) – If True, delete all the tables in the dataset. If False and the dataset contains tables, the request will fail.

  • retry (google.api_core.retry.Retry) – How to retry the RPC.

create_external_table(external_project_dataset_table, schema_fields, source_uris, source_format='CSV', autodetect=False, compression='NONE', ignore_unknown_values=False, max_bad_records=0, skip_leading_rows=0, field_delimiter=',', quote_character=None, allow_quoted_newlines=False, allow_jagged_rows=False, encoding='UTF-8', src_fmt_configs=None, labels=None, description=None, encryption_configuration=None, location=None, project_id=None)[source]

Creates a new external table in the dataset with the data from Google Cloud Storage. See here:

https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#resource

for more details about these parameters.

Parameters

  • external_project_dataset_table (str) – The dotted (<project>.|<project>:)<dataset>.<table>($<partition>) BigQuery table name to create external table. If <project> is not included, project will be the project defined in the connection json.

  • schema_fields (List) – The schema field list as defined here: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#resource

  • source_uris (List) – The source Google Cloud Storage URI (e.g. gs://some-bucket/some-file.txt). A single wild per-object name can be used.

  • source_format (str) – File format to export.

  • autodetect (bool) – Try to detect schema and format options automatically. Any option specified explicitly will be honored.

  • compression (str) – [Optional] The compression type of the data source. Possible values include GZIP and NONE. The default value is NONE. This setting is ignored for Google Cloud Bigtable, Google Cloud Datastore backups and Avro formats.

  • ignore_unknown_values (bool) – [Optional] Indicates if 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.

  • max_bad_records (int) – The maximum number of bad records that BigQuery can ignore when running the job.

  • skip_leading_rows (int) – Number of rows to skip when loading from a CSV.

  • field_delimiter (str) – The delimiter to use when loading from a CSV.

  • quote_character (Optional[str]) – The value that is used to quote data sections in a CSV file.

  • allow_quoted_newlines (bool) – Whether to allow quoted newlines (true) or not (false).

  • allow_jagged_rows (bool) – Accept rows that are missing trailing optional columns. The missing values are treated as nulls. If 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. Only applicable when source_format is CSV.

  • encoding (str) –

    The character encoding of the data. See:

    See also

    https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#externalDataConfiguration.csvOptions.encoding

  • src_fmt_configs (Optional[Dict]) – configure optional fields specific to the source format

  • labels (Optional[Dict]) – A dictionary containing labels for the BiqQuery table.

  • description (Optional[str]) – A string containing the description for the BigQuery table.

  • encryption_configuration (Optional[Dict]) –

    [Optional] Custom encryption configuration (e.g., Cloud KMS keys). Example:

    encryption_configuration = {
    "kmsKeyName": "projects/testp/locations/us/keyRings/test-kr/cryptoKeys/test-key"
}

update_table(table_resource, fields=None, dataset_id=None, table_id=None, project_id=None)[source]

Change some fields of a table.

Use fields to specify which fields to update. At least one field must be provided. If a field is listed in fields and is None in table, the field value will be deleted.

If table.etag is not None, the update will only succeed if the table on the server has the same ETag. Thus reading a table with get_table, changing its fields, and then passing it to update_table will ensure that the changes will only be saved if no modifications to the table occurred since the read.

Parameters

  • project_id (Optional[str]) – The project to create the table into.

  • dataset_id (Optional[str]) – The dataset to create the table into.

  • table_id (Optional[str]) – The Name of the table to be created.

  • table_resource (Dict[str, Any]) – Table resource as described in documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#Table The table has to contain tableReference or project_id, dataset_id and table_id have to be provided.

  • fields (Optional[List[str]]) – The fields of table to change, spelled as the Table properties (e.g. “friendly_name”).

patch_table(dataset_id, table_id, project_id=None, description=None, expiration_time=None, external_data_configuration=None, friendly_name=None, labels=None, schema=None, time_partitioning=None, view=None, require_partition_filter=None, encryption_configuration=None)[source]

Patch information in an existing table. It only updates fields that are provided in the request object.

Reference: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/patch

Parameters

  • dataset_id (str) – The dataset containing the table to be patched.

  • table_id (str) – The Name of the table to be patched.

  • project_id (Optional[str]) – The project containing the table to be patched.

  • description (Optional[str]) – [Optional] A user-friendly description of this table.

  • expiration_time (Optional[int]) – [Optional] The time when this table expires, in milliseconds since the epoch.

  • external_data_configuration (Optional[Dict]) – [Optional] A dictionary containing properties of a table stored outside of BigQuery.

  • friendly_name (Optional[str]) – [Optional] A descriptive name for this table.

  • labels (Optional[Dict]) – [Optional] A dictionary containing labels associated with this table.

  • schema (Optional[List]) –

    [Optional] If set, the schema field list as defined here: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs#configuration.load.schema The supported schema modifications and unsupported schema modification are listed here: https://cloud.google.com/bigquery/docs/managing-table-schemas Example:

    schema=[{"name": "emp_name", "type": "STRING", "mode": "REQUIRED"},
               {"name": "salary", "type": "INTEGER", "mode": "NULLABLE"}]

  • time_partitioning (Optional[Dict]) – [Optional] A dictionary containing time-based partitioning definition for the table.

  • view (Optional[Dict]) –

    [Optional] A dictionary containing definition for the view. If set, it will patch a view instead of a table: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#ViewDefinition Example:

    view = {
    "query": "SELECT * FROM `test-project-id.test_dataset_id.test_table_prefix*` LIMIT 500",
    "useLegacySql": False
}

  • require_partition_filter (Optional[bool]) – [Optional] If true, queries over the this table require a partition filter. If false, queries over the table

  • encryption_configuration (Optional[Dict]) –

    [Optional] Custom encryption configuration (e.g., Cloud KMS keys). Example:

    encryption_configuration = {
    "kmsKeyName": "projects/testp/locations/us/keyRings/test-kr/cryptoKeys/test-key"
}

insert_all(project_id, dataset_id, table_id, rows, ignore_unknown_values=False, skip_invalid_rows=False, fail_on_error=False)[source]

Method to stream data into BigQuery one record at a time without needing to run a load job

See also

For more information, see: https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/insertAll

Parameters

  • project_id (str) – The name of the project where we have the table

  • dataset_id (str) – The name of the dataset where we have the table

  • table_id (str) – The name of the table

  • rows (List) – the rows to insert

Example or rows:

rows=[{“json”: {“a_key”: “a_value_0”}}, {“json”: {“a_key”: “a_value_1”}}]

Parameters

  • ignore_unknown_values (bool) – [Optional] Accept rows that contain values that do not match the schema. The unknown values are ignored. The default value is false, which treats unknown values as errors.

  • skip_invalid_rows (bool) – [Optional] Insert all valid rows of a request, even if invalid rows exist. The default value is false, which causes the entire request to fail if any invalid rows exist.

  • fail_on_error (bool) – [Optional] Force the task to fail if any errors occur. The default value is false, which indicates the task should not fail even if any insertion errors occur.

update_dataset(fields, dataset_resource, dataset_id=None, project_id=None, retry=DEFAULT_RETRY)[source]

Change some fields of a dataset.

Use fields to specify which fields to update. At least one field must be provided. If a field is listed in fields and is None in dataset, it will be deleted.

If dataset.etag is not None, the update will only succeed if the dataset on the server has the same ETag. Thus reading a dataset with get_dataset, changing its fields, and then passing it to update_dataset will ensure that the changes will only be saved if no modifications to the dataset occurred since the read.

Parameters
patch_dataset(dataset_id, dataset_resource, project_id=None)[source]

Patches information in an existing dataset. It only replaces fields that are provided in the submitted dataset resource. More info: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/patch

Parameters
Return type

dataset https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets#resource

get_dataset_tables_list(dataset_id, project_id=None, table_prefix=None, max_results=None)[source]

Method returns tables list of a BigQuery tables. If table prefix is specified, only tables beginning by it are returned.

For more information, see: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/list

Parameters

  • dataset_id (str) – The BigQuery Dataset ID

  • project_id (Optional[str]) – The Google Cloud Project ID

  • table_prefix (Optional[str]) – Tables must begin by this prefix to be returned (case sensitive)

  • max_results (Optional[int]) – The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection.

Returns

List of tables associated with the dataset

Return type

List[Dict[str, Any]]

get_datasets_list(project_id=None, include_all=False, filter_=None, max_results=None, page_token=None, retry=DEFAULT_RETRY)[source]

Method returns full list of BigQuery datasets in the current project

For more information, see: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/list

Parameters

  • project_id (Optional[str]) – Google Cloud Project for which you try to get all datasets

  • include_all (bool) – True if results include hidden datasets. Defaults to False.

  • filter – An expression for filtering the results by label. For syntax, see https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/list#filter.

  • filter – str

  • max_results (Optional[int]) – Maximum number of datasets to return.

  • max_results – int

  • page_token (Optional[str]) – Token representing a cursor into the datasets. If not passed, the API will return the first page of datasets. The token marks the beginning of the iterator to be returned and the value of the page_token can be accessed at next_page_token of the HTTPIterator.

  • page_token – str

  • retry (google.api_core.retry.Retry) – How to retry the RPC.

get_dataset(dataset_id, project_id=None)[source]

Fetch the dataset referenced by dataset_id.

Parameters

  • dataset_id (str) – The BigQuery Dataset ID

  • project_id (Optional[str]) – The Google Cloud Project ID

Returns

dataset_resource

See also

For more information, see Dataset Resource content: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets#resource

Return type

google.cloud.bigquery.dataset.Dataset

run_grant_dataset_view_access(source_dataset, view_dataset, view_table, view_project=None, project_id=None)[source]

Grant authorized view access of a dataset to a view table. If this view has already been granted access to the dataset, do nothing. This method is not atomic. Running it may clobber a simultaneous update.

Parameters

  • source_dataset (str) – the source dataset

  • view_dataset (str) – the dataset that the view is in

  • view_table (str) – the table of the view

  • project_id (Optional[str]) – the project of the source dataset. If None, self.project_id will be used.

  • view_project (Optional[str]) – the project that the view is in. If None, self.project_id will be used.

Returns

the datasets resource of the source dataset.

Return type

Dict[str, Any]

run_table_upsert(dataset_id, table_resource, project_id=None)[source]

If the table already exists, update the existing table if not create new. Since BigQuery does not natively allow table upserts, this is not an atomic operation.

Parameters
Returns

Return type

Dict[str, Any]

run_table_delete(deletion_dataset_table, ignore_if_missing=False)[source]

Delete an existing table from the dataset; If the table does not exist, return an error unless ignore_if_missing is set to True.

Parameters

  • deletion_dataset_table (str) – A dotted (<project>.|<project>:)<dataset>.<table> that indicates which table will be deleted.

  • ignore_if_missing (bool) – if True, then return success even if the requested table does not exist.

Returns

Return type

None

delete_table(table_id, not_found_ok=True, project_id=None)[source]

Delete an existing table from the dataset. If the table does not exist, return an error unless not_found_ok is set to True.

Parameters

  • table_id (str) – A dotted (<project>.|<project>:)<dataset>.<table> that indicates which table will be deleted.

  • not_found_ok (bool) – if True, then return success even if the requested table does not exist.

  • project_id (Optional[str]) – the project used to perform the request

get_tabledata(dataset_id, table_id, max_results=None, selected_fields=None, page_token=None, start_index=None)[source]

Get the data of a given dataset.table and optionally with selected columns. see https://cloud.google.com/bigquery/docs/reference/v2/tabledata/list

Parameters

  • dataset_id (str) – the dataset ID of the requested table.

  • table_id (str) – the table ID of the requested table.

  • max_results (Optional[int]) – the maximum results to return.

  • selected_fields (Optional[str]) – List of fields to return (comma-separated). If unspecified, all fields are returned.

  • page_token (Optional[str]) – page token, returned from a previous call, identifying the result set.

  • start_index (Optional[int]) – zero based index of the starting row to read.

Returns

list of rows

Return type

List[Dict]

list_rows(dataset_id, table_id, max_results=None, selected_fields=None, page_token=None, start_index=None, project_id=None, location=None)[source]

List the rows of the table. See https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/list

Parameters

  • dataset_id (str) – the dataset ID of the requested table.

  • table_id (str) – the table ID of the requested table.

  • max_results (Optional[int]) – the maximum results to return.

  • selected_fields (Optional[Union[List[str], str]]) – List of fields to return (comma-separated). If unspecified, all fields are returned.

  • page_token (Optional[str]) – page token, returned from a previous call, identifying the result set.

  • start_index (Optional[int]) – zero based index of the starting row to read.

  • project_id (Optional[str]) – Project ID for the project which the client acts on behalf of.

  • location (Optional[str]) – Default location for job.

Returns

list of rows

Return type

List[google.cloud.bigquery.table.Row]

get_schema(dataset_id, table_id, project_id=None)[source]

Get the schema for a given dataset and table. see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource

Parameters

  • dataset_id (str) – the dataset ID of the requested table

  • table_id (str) – the table ID of the requested table

  • project_id (Optional[str]) – the optional project ID of the requested table. If not provided, the connector’s configured project will be used.

Returns

a table schema

Return type

dict

update_table_schema(schema_fields_updates, include_policy_tags, dataset_id, table_id, project_id=None)[source]

Update fields within a schema for a given dataset and table. Note that some fields in schemas are immutable and trying to change them will cause an exception. If a new field is included it will be inserted which requires all required fields to be set. See https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TableSchema

Parameters

Example:

schema_fields_updates=[
    {"name": "emp_name", "description": "Some New Description"},
    {"name": "salary", "description": "Some New Description"},
    {"name": "departments", "fields": [
        {"name": "name", "description": "Some New Description"},
        {"name": "type", "description": "Some New Description"}
    ]},
]
Parameters

project_id (Optional[str]) – The name of the project where we want to update the table.

poll_job_complete(job_id, project_id=None, location=None, retry=DEFAULT_RETRY)[source]

Check if jobs completed.

Parameters

  • job_id (str) – id of the job.

  • project_id (Optional[str]) – Google Cloud Project where the job is running

  • location (Optional[str]) – location the job is running

  • retry (google.api_core.retry.Retry) – How to retry the RPC.

Return type

bool

cancel_query()[source]

Cancel all started queries that have not yet completed

cancel_job(job_id, project_id=None, location=None)[source]

Cancel a job and wait for cancellation to complete

Parameters

  • job_id (str) – id of the job.

  • project_id (Optional[str]) – Google Cloud Project where the job is running

  • location (Optional[str]) – location the job is running

get_job(job_id=None, project_id=None, location=None)[source]

Retrieves a BigQuery job. For more information see: https://cloud.google.com/bigquery/docs/reference/v2/jobs

Parameters

  • job_id (Optional[str]) – The ID of the job. The ID must contain only letters (a-z, A-Z), numbers (0-9), underscores (_), or dashes (-). The maximum length is 1,024 characters. If not provided then uuid will be generated.

  • project_id (Optional[str]) – Google Cloud Project where the job is running

  • location (Optional[str]) – location the job is running

insert_job(configuration, job_id=None, project_id=None, location=None, nowait=False, retry=DEFAULT_RETRY, timeout=None)[source]

Executes a BigQuery job. Waits for the job to complete and returns job id. See here:

https://cloud.google.com/bigquery/docs/reference/v2/jobs

Parameters

  • configuration (Dict) – The configuration parameter maps directly to BigQuery’s configuration field in the job object. See https://cloud.google.com/bigquery/docs/reference/v2/jobs for details.

  • job_id (Optional[str]) – The ID of the job. The ID must contain only letters (a-z, A-Z), numbers (0-9), underscores (_), or dashes (-). The maximum length is 1,024 characters. If not provided then uuid will be generated.

  • project_id (Optional[str]) – Google Cloud Project where the job is running

  • location (Optional[str]) – location the job is running

  • nowait (bool) – specify whether to insert job without waiting for the result

  • retry (google.api_core.retry.Retry) – How to retry the RPC.

  • timeout (Optional[float]) – The number of seconds to wait for the underlying HTTP transport before using retry.

run_with_configuration(configuration)[source]

Executes a BigQuery SQL query. See here:

https://cloud.google.com/bigquery/docs/reference/v2/jobs

For more details about the configuration parameter.

Parameters

configuration (dict) – The configuration parameter maps directly to BigQuery’s configuration field in the job object. See https://cloud.google.com/bigquery/docs/reference/v2/jobs for details.

run_load(destination_project_dataset_table, source_uris, schema_fields=None, source_format='CSV', create_disposition='CREATE_IF_NEEDED', skip_leading_rows=0, write_disposition='WRITE_EMPTY', field_delimiter=',', max_bad_records=0, quote_character=None, ignore_unknown_values=False, allow_quoted_newlines=False, allow_jagged_rows=False, encoding='UTF-8', schema_update_options=None, src_fmt_configs=None, time_partitioning=None, cluster_fields=None, autodetect=False, encryption_configuration=None, labels=None, description=None)[source]

Executes a BigQuery load command to load data from Google Cloud Storage to BigQuery. See here:

https://cloud.google.com/bigquery/docs/reference/v2/jobs

For more details about these parameters.

Parameters

  • destination_project_dataset_table (str) – The dotted (<project>.|<project>:)<dataset>.<table>($<partition>) BigQuery table to load data into. If <project> is not included, project will be the project defined in the connection json. If a partition is specified the operator will automatically append the data, create a new partition or create a new DAY partitioned table.

  • schema_fields (Optional[List]) – The schema field list as defined here: https://cloud.google.com/bigquery/docs/reference/v2/jobs#configuration.load Required if autodetect=False; optional if autodetect=True.

  • autodetect (bool) – Attempt to autodetect the schema for CSV and JSON source files.

  • source_uris (List) – The source Google Cloud Storage URI (e.g. gs://some-bucket/some-file.txt). A single wild per-object name can be used.

  • source_format (str) – File format to export.

  • create_disposition (str) – The create disposition if the table doesn’t exist.

  • skip_leading_rows (int) – Number of rows to skip when loading from a CSV.

  • write_disposition (str) – The write disposition if the table already exists.

  • field_delimiter (str) – The delimiter to use when loading from a CSV.

  • max_bad_records (int) – The maximum number of bad records that BigQuery can ignore when running the job.

  • quote_character (Optional[str]) – The value that is used to quote data sections in a CSV file.

  • ignore_unknown_values (bool) – [Optional] Indicates if 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.

  • allow_quoted_newlines (bool) – Whether to allow quoted newlines (true) or not (false).

  • allow_jagged_rows (bool) – Accept rows that are missing trailing optional columns. The missing values are treated as nulls. If 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. Only applicable when source_format is CSV.

  • encoding (str) –

    The character encoding of the data.

    See also

    https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#externalDataConfiguration.csvOptions.encoding

  • schema_update_options (Optional[Iterable]) – Allows the schema of the destination table to be updated as a side effect of the load job.

  • src_fmt_configs (Optional[Dict]) – configure optional fields specific to the source format

  • time_partitioning (Optional[Dict]) – configure optional time partitioning fields i.e. partition by field, type and expiration as per API specifications.

  • cluster_fields (Optional[List]) – Request that the result of this load be stored sorted by one or more columns. BigQuery supports clustering for both partitioned and non-partitioned tables. The order of columns given determines the sort order.

  • encryption_configuration (Optional[Dict]) –

    [Optional] Custom encryption configuration (e.g., Cloud KMS keys). Example:

    encryption_configuration = {
    "kmsKeyName": "projects/testp/locations/us/keyRings/test-kr/cryptoKeys/test-key"
}

  • labels (Optional[Dict]) – A dictionary containing labels for the BiqQuery table.

  • description (Optional[str]) – A string containing the description for the BigQuery table.

run_copy(source_project_dataset_tables, destination_project_dataset_table, write_disposition='WRITE_EMPTY', create_disposition='CREATE_IF_NEEDED', labels=None, encryption_configuration=None)[source]

Executes a BigQuery copy command to copy data from one BigQuery table to another. See here:

https://cloud.google.com/bigquery/docs/reference/v2/jobs#configuration.copy

For more details about these parameters.

Parameters

  • source_project_dataset_tables (Union[List, str]) – One or more dotted (project:|project.)<dataset>.<table> BigQuery tables to use as the source data. Use a list if there are multiple source tables. If <project> is not included, project will be the project defined in the connection json.

  • destination_project_dataset_table (str) – The destination BigQuery table. Format is: (project:|project.)<dataset>.<table>

  • write_disposition (str) – The write disposition if the table already exists.

  • create_disposition (str) – The create disposition if the table doesn’t exist.

  • labels (Optional[Dict]) – a dictionary containing labels for the job/query, passed to BigQuery

  • encryption_configuration (Optional[Dict]) –

    [Optional] Custom encryption configuration (e.g., Cloud KMS keys). Example:

    encryption_configuration = {
    "kmsKeyName": "projects/testp/locations/us/keyRings/test-kr/cryptoKeys/test-key"
}

run_extract(source_project_dataset_table, destination_cloud_storage_uris, compression='NONE', export_format='CSV', field_delimiter=',', print_header=True, labels=None, return_full_job=False)[source]

Executes a BigQuery extract command to copy data from BigQuery to Google Cloud Storage. See here:

https://cloud.google.com/bigquery/docs/reference/v2/jobs

For more details about these parameters.

Parameters

  • source_project_dataset_table (str) – The dotted <dataset>.<table> BigQuery table to use as the source data.

  • destination_cloud_storage_uris (List[str]) – The destination Google Cloud Storage URI (e.g. gs://some-bucket/some-file.txt). Follows convention defined here: https://cloud.google.com/bigquery/exporting-data-from-bigquery#exportingmultiple

  • compression (str) – Type of compression to use.

  • export_format (str) – File format to export.

  • field_delimiter (str) – The delimiter to use when extracting to a CSV.

  • print_header (bool) – Whether to print a header for a CSV file extract.

  • labels (Optional[Dict]) – a dictionary containing labels for the job/query, passed to BigQuery

  • return_full_job (bool) – return full job instead of job id only

run_query(sql, destination_dataset_table=None, write_disposition='WRITE_EMPTY', allow_large_results=False, flatten_results=None, udf_config=None, use_legacy_sql=None, maximum_billing_tier=None, maximum_bytes_billed=None, create_disposition='CREATE_IF_NEEDED', query_params=None, labels=None, schema_update_options=None, priority='INTERACTIVE', time_partitioning=None, api_resource_configs=None, cluster_fields=None, location=None, encryption_configuration=None)[source]

Executes a BigQuery SQL query. Optionally persists results in a BigQuery table. See here:

https://cloud.google.com/bigquery/docs/reference/v2/jobs

For more details about these parameters.

Parameters

  • sql (str) – The BigQuery SQL to execute.

  • destination_dataset_table (Optional[str]) – The dotted <dataset>.<table> BigQuery table to save the query results.

  • write_disposition (str) – What to do if the table already exists in BigQuery.

  • allow_large_results (bool) – Whether to allow large results.

  • flatten_results (Optional[bool]) – If true and query uses legacy SQL dialect, flattens all nested and repeated fields in the query results. allowLargeResults must be true if this is set to false. For standard SQL queries, this flag is ignored and results are never flattened.

  • udf_config (Optional[List]) – The User Defined Function configuration for the query. See https://cloud.google.com/bigquery/user-defined-functions for details.

  • use_legacy_sql (Optional[bool]) – Whether to use legacy SQL (true) or standard SQL (false). If None, defaults to self.use_legacy_sql.

  • api_resource_configs (Optional[Dict]) – a dictionary that contain params ‘configuration’ applied for Google BigQuery Jobs API: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs for example, {‘query’: {‘useQueryCache’: False}}. You could use it if you need to provide some params that are not supported by the BigQueryHook like args.

  • maximum_billing_tier (Optional[int]) – Positive integer that serves as a multiplier of the basic price.

  • maximum_bytes_billed (Optional[float]) – Limits the bytes billed for this job. Queries that will have bytes billed beyond this limit will fail (without incurring a charge). If unspecified, this will be set to your project default.

  • create_disposition (str) – Specifies whether the job is allowed to create new tables.

  • query_params (Optional[List]) – a list of dictionary containing query parameter types and values, passed to BigQuery

  • labels (Optional[Dict]) – a dictionary containing labels for the job/query, passed to BigQuery

  • schema_update_options (Optional[Iterable]) – Allows the schema of the destination table to be updated as a side effect of the query job.

  • priority (str) – Specifies a priority for the query. Possible values include INTERACTIVE and BATCH. The default value is INTERACTIVE.

  • time_partitioning (Optional[Dict]) – configure optional time partitioning fields i.e. partition by field, type and expiration as per API specifications.

  • cluster_fields (Optional[List[str]]) – Request that the result of this query be stored sorted by one or more columns. BigQuery supports clustering for both partitioned and non-partitioned tables. The order of columns given determines the sort order.

  • location (Optional[str]) – The geographic location of the job. Required except for US and EU. See details at https://cloud.google.com/bigquery/docs/locations#specifying_your_location

  • encryption_configuration (Optional[Dict]) –

    [Optional] Custom encryption configuration (e.g., Cloud KMS keys). Example:

    encryption_configuration = {
    "kmsKeyName": "projects/testp/locations/us/keyRings/test-kr/cryptoKeys/test-key"
}

generate_job_id(job_id, dag_id, task_id, logical_date, configuration, force_rerun=False)[source]
split_tablename(table_input, default_project_id, var_name=None)[source]
class airflow.providers.google.cloud.hooks.bigquery.BigQueryConnection(*args, **kwargs)[source]

BigQuery does not have a notion of a persistent connection. Thus, these objects are small stateless factories for cursors, which do all the real work.

close()[source]

The BigQueryConnection does not have anything to close

commit()[source]

The BigQueryConnection does not support transactions

cursor()[source]

Return a new Cursor object using the connection

abstract rollback()[source]

The BigQueryConnection does not have transactions

class airflow.providers.google.cloud.hooks.bigquery.BigQueryBaseCursor(service, project_id, hook, use_legacy_sql=True, api_resource_configs=None, location=None, num_retries=5, labels=None)[source]

Bases: airflow.utils.log.logging_mixin.LoggingMixin

The BigQuery base cursor contains helper methods to execute queries against BigQuery. The methods can be used directly by operators, in cases where a PEP 249 cursor isn’t needed.

create_empty_table(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.create_empty_table

create_empty_dataset(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.create_empty_dataset

get_dataset_tables(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.get_dataset_tables

delete_dataset(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.delete_dataset

create_external_table(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.create_external_table

patch_table(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.patch_table

insert_all(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.insert_all

update_dataset(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.update_dataset

patch_dataset(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.patch_dataset

get_dataset_tables_list(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.get_dataset_tables_list

get_datasets_list(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.get_datasets_list

get_dataset(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.get_dataset

run_grant_dataset_view_access(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_grant_dataset_view_access

run_table_upsert(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_table_upsert

run_table_delete(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_table_delete

get_tabledata(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.get_tabledata

get_schema(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.get_schema

poll_job_complete(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.poll_job_complete

cancel_query(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.cancel_query

run_with_configuration(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_with_configuration

run_load(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_load

run_copy(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_copy

run_extract(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_extract

run_query(*args, **kwargs)[source]

This method is deprecated. Please use airflow.providers.google.cloud.hooks.bigquery.BigQueryHook.run_query

class airflow.providers.google.cloud.hooks.bigquery.BigQueryCursor(service, project_id, hook, use_legacy_sql=True, location=None, num_retries=5)[source]

Bases: BigQueryBaseCursor

A very basic BigQuery PEP 249 cursor implementation. The PyHive PEP 249 implementation was used as a reference:

https://github.com/dropbox/PyHive/blob/master/pyhive/presto.py https://github.com/dropbox/PyHive/blob/master/pyhive/common.py

arraysize[source]
property description[source]

Return the cursor description

close()[source]

By default, do nothing

property rowcount[source]

By default, return -1 to indicate that this is not supported

execute(operation, parameters=None)[source]

Executes a BigQuery query, and returns the job ID.

Parameters

  • operation (str) – The query to execute.

  • parameters (Optional[dict]) – Parameters to substitute into the query.

executemany(operation, seq_of_parameters)[source]

Execute a BigQuery query multiple times with different parameters.

Parameters

  • operation (str) – The query to execute.

  • seq_of_parameters (list) – List of dictionary parameters to substitute into the query.

flush_results()[source]

Flush results related cursor attributes

fetchone()[source]

Fetch the next row of a query result set

next()[source]

Helper method for fetchone, which returns the next row from a buffer. If the buffer is empty, attempts to paginate through the result set for the next page, and load it into the buffer.

fetchmany(size=None)[source]

Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available. The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned. An Error (or subclass) exception is raised if the previous call to execute() did not produce any result set or no call was issued yet.

fetchall()[source]

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

get_arraysize()[source]

Specifies the number of rows to fetch at a time with .fetchmany()

set_arraysize(arraysize)[source]

Specifies the number of rows to fetch at a time with .fetchmany()

setinputsizes(sizes)[source]

Does nothing by default

setoutputsize(size, column=None)[source]

Does nothing by default

airflow.providers.google.cloud.hooks.bigquery.split_tablename(table_input, default_project_id, var_name=None)[source]

Was this entry helpful?