`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

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.hooks.dbapi.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(self)[source]¶

Returns a BigQuery PEP 249 connection object.

get_service(self)[source]¶

Returns a BigQuery service object.

get_client(self, 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(self)[source]¶

Override DbApiHook get_uri method for get_sqlalchemy_engine()

get_sqlalchemy_engine(self, engine_kwargs=None)[source]¶

Get an sqlalchemy_engine object.

Parameters: engine_kwargs -- Kwargs used in create_engine().
Returns: the created engine.

get_records(self, 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(self, 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(self, 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(self, 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(self, 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(self, 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

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 (Optional[Dict[str, Any]]) -- Table resource as described in documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#Table If provided all other parameters are ignored.
schema_fields (Optional[List]) -- If set, the schema field list as defined here: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs#configuration.load.schema
labels (Optional[Dict]) -- a dictionary containing labels for the table, passed to BigQuery
retry (Optional[google.api_core.retry.Retry]) -- Optional. How to retry the RPC.

Example:

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

Parameters

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

See also

https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#timePartitioning
cluster_fields (Optional[List[str]]) -- [Optional] The fields used for clustering. BigQuery supports clustering for both partitioned and non-partitioned tables. https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#clustering.fields
view (Optional[Dict]) -- [Optional] A dictionary containing definition for the view. If set, it will create 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 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(self, 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(self, 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(self, 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(self, 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(self, 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(self, 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(self, 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 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(self, 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(self, 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

dataset_id (str) -- the dataset to upsert the table into.
table_resource (Dict[str, Any]) -- a table resource. see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
project_id (Optional[str]) -- the project to upsert the table into. If None, project will be self.project_id.

Returns

Return type

Dict[str, Any]

run_table_delete(self, 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(self, 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(self, 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(self, 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(self, 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(self, 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

include_policy_tags (bool) -- If set to True policy tags will be included in the update request which requires special permissions even if unchanged see https://cloud.google.com/bigquery/docs/column-level-security#roles
dataset_id (str) -- the dataset ID of the requested table to be updated
table_id (str) -- the table ID of the table to be updated
schema_fields_updates (List[Dict[str, Any]]) -- a partial schema resource. see https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TableSchema

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(self, 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(self)[source]¶

Cancel all started queries that have not yet completed

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

Cancels a job an 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(self, 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(self, 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(self, 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(self, 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(self, 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(self, source_project_dataset_table, destination_cloud_storage_uris, compression='NONE', export_format='CSV', field_delimiter=',', print_header=True, labels=None)[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

run_query(self, 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"
}

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(self)[source]¶

The BigQueryConnection does not have anything to close

commit(self)[source]¶

The BigQueryConnection does not support transactions

cursor(self)[source]¶

Return a new Cursor object using the connection

abstract rollback(self)[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(self, *args, **kwargs)[source]¶

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

run_query(self, *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(self)[source]¶

The schema description method is not currently implemented

close(self)[source]¶

By default, do nothing

property rowcount(self)[source]¶

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

execute(self, 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(self, 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(self)[source]¶

Flush results related cursor attributes

fetchone(self)[source]¶

Fetch the next row of a query result set

next(self)[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(self, 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(self)[source]¶

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

get_arraysize(self)[source]¶

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

set_arraysize(self, arraysize)[source]¶

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

setinputsizes(self, sizes)[source]¶

Does nothing by default

setoutputsize(self, size, column=None)[source]¶

Does nothing by default

airflow.providers.google.cloud.hooks.bigquery¶

Module Contents¶

Classes¶

Attributes¶

`airflow.providers.google.cloud.hooks.bigquery`¶