gcp_bigquery_table – Creates a GCP Table

New in version 2.8.

Synopsis

  • A Table that belongs to a Dataset .

Requirements

The below requirements are needed on the host that executes this module.

  • python >= 2.6

  • requests >= 2.18.4

  • google-auth >= 1.3.0

Parameters

Parameter Choices/Defaults Comments
auth_kind
string / required
    Choices:
  • application
  • machineaccount
  • serviceaccount
The type of credential used.
dataset
-
Name of the dataset.
description
-
A user-friendly description of the dataset.
encryption_configuration
-
Custom encryption configuration.
kms_key_name
-
Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key.
expiration_time
-
The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely.
external_data_configuration
-
Describes the data format, location, and other properties of a table stored outside of BigQuery. By defining these properties, the data source can then be queried as if it were a standard BigQuery table.
autodetect
boolean
    Choices:
  • no
  • yes
Try to detect schema and format options automatically. Any option specified explicitly will be honored.
bigtable_options
-
Additional options if sourceFormat is set to BIGTABLE.
column_families
-
List of column families to expose in the table schema along with their types.
columns
-
Lists of columns that should be exposed as individual fields as opposed to a list of (column name, value) pairs.
encoding
-
    Choices:
  • TEXT
  • BINARY
The encoding of the values when the type is not STRING.
field_name
-
If the qualifier is not a valid BigQuery field identifier, a valid identifier must be provided as the column field name and is used as field name in queries.
only_read_latest
boolean
    Choices:
  • no
  • yes
If this is set, only the latest version of value in this column are exposed .
qualifier_string
- / required
Qualifier of the column.
type
-
    Choices:
  • BYTES
  • STRING
  • INTEGER
  • FLOAT
  • BOOLEAN
The type to convert the value in cells of this column.
encoding
-
    Choices:
  • TEXT
  • BINARY
The encoding of the values when the type is not STRING.
family_id
-
Identifier of the column family.
only_read_latest
boolean
    Choices:
  • no
  • yes
If this is set only the latest version of value are exposed for all columns in this column family .
type
-
    Choices:
  • BYTES
  • STRING
  • INTEGER
  • FLOAT
  • BOOLEAN
The type to convert the value in cells of this column family.
ignore_unspecified_column_families
boolean
    Choices:
  • no
  • yes
If field is true, then the column families that are not specified in columnFamilies list are not exposed in the table schema .
read_rowkey_as_string
boolean
    Choices:
  • no
  • yes
If field is true, then the rowkey column families will be read and converted to string.
compression
-
    Choices:
  • GZIP
  • NONE
The compression type of the data source.
csv_options
-
Additional properties to set if sourceFormat is set to CSV.
allow_jagged_rows
boolean
    Choices:
  • no
  • yes
Indicates if BigQuery should accept rows that are missing trailing optional columns .
allow_quoted_newlines
boolean
    Choices:
  • no
  • yes
Indicates if BigQuery should allow quoted data sections that contain newline characters in a CSV file .
encoding
-
    Choices:
  • UTF-8
  • ISO-8859-1
The character encoding of the data.
field_delimiter
-
The separator for fields in a CSV file.
quote
-
The value that is used to quote data sections in a CSV file.
skip_leading_rows
-
Default:
"0"
The number of rows at the top of a CSV file that BigQuery will skip when reading the data.
google_sheets_options
-
Additional options if sourceFormat is set to GOOGLE_SHEETS.
skip_leading_rows
-
Default:
"0"
The number of rows at the top of a Google Sheet that BigQuery will skip when reading the data.
ignore_unknown_values
boolean
    Choices:
  • no
  • yes
Indicates if BigQuery should allow extra values that are not represented in the table schema .
max_bad_records
-
Default:
"0"
The maximum number of bad records that BigQuery can ignore when reading data .
schema
-
The schema for the data. Schema is required for CSV and JSON formats.
fields
-
Describes the fields in a table.
description
-
The field description.
fields
-
Describes the nested schema fields if the type property is set to RECORD .
mode
-
    Choices:
  • NULLABLE
  • REQUIRED
  • REPEATED
Field mode.
name
-
Field name.
type
-
    Choices:
  • STRING
  • BYTES
  • INTEGER
  • FLOAT
  • TIMESTAMP
  • DATE
  • TIME
  • DATETIME
  • RECORD
Field data type.
source_format
-
    Choices:
  • CSV
  • GOOGLE_SHEETS
  • NEWLINE_DELIMITED_JSON
  • AVRO
  • DATASTORE_BACKUP
  • BIGTABLE
The data format.
source_uris
-
The fully-qualified URIs that point to your data in Google Cloud. For Google Cloud Storage URIs: Each URI can contain one '*' wildcard character and it must come after the 'bucket' name. Size limits related to load jobs apply to external data sources. For Google Cloud Bigtable URIs: Exactly one URI can be specified and it has be a fully specified and valid HTTPS URL for a Google Cloud Bigtable table. For Google Cloud Datastore backups, exactly one URI can be specified. Also, the '*' wildcard character is not allowed.
friendly_name
-
A descriptive name for this table.
labels
-
The labels associated with this dataset. You can use these to organize and group your datasets .
name
-
Name of the table.
project
string
The Google Cloud Platform project to use.
schema
-
Describes the schema of this table.
fields
-
Describes the fields in a table.
description
-
The field description. The maximum length is 1,024 characters.
fields
-
Describes the nested schema fields if the type property is set to RECORD.
mode
-
    Choices:
  • NULLABLE
  • REQUIRED
  • REPEATED
The field mode.
name
-
The field name.
type
-
    Choices:
  • STRING
  • BYTES
  • INTEGER
  • FLOAT
  • TIMESTAMP
  • DATE
  • TIME
  • DATETIME
  • RECORD
The field data type.
scopes
list
Array of scopes to be used.
service_account_contents
string
A string representing the contents of a Service Account JSON file.
This should not be passed in as a dictionary, but a string that has the exact contents of a service account json file (valid JSON)
service_account_email
string
An optional service account email address if machineaccount is selected and the user does not wish to use the default email.
service_account_file
path
The path of a Service Account JSON file if serviceaccount is selected as type.
state
-
    Choices:
  • present ←
  • absent
Whether the given object should exist in GCP
table_reference
-
Reference describing the ID of this table.
dataset_id
-
The ID of the dataset containing this table.
project_id
-
The ID of the project containing this table.
table_id
-
The ID of the the table.
time_partitioning
-
If specified, configures time-based partitioning for this table.
expiration_ms
-
Number of milliseconds for which to keep the storage for a partition.
type
-
    Choices:
  • DAY
The only type supported is DAY, which will generate one partition per day.
view
-
The view definition.
use_legacy_sql
boolean
    Choices:
  • no
  • yes
Specifies whether to use BigQuery's legacy SQL for this view .
user_defined_function_resources
-
Describes user-defined function resources used in the query.
inline_code
-
An inline resource that contains code for a user-defined function (UDF). Providing a inline code resource is equivalent to providing a URI for a file containing the same code.
resource_uri
-
A code resource to load from a Google Cloud Storage URI (gs://bucket/path).

Notes

Note

  • For authentication, you can set service_account_file using the GCP_SERVICE_ACCOUNT_FILE env variable.

  • For authentication, you can set service_account_email using the GCP_SERVICE_ACCOUNT_EMAIL env variable.

  • For authentication, you can set service_account_contents using the GCP_SERVICE_ACCOUNT_CONTENTS env variable.

  • For authentication, you can set auth_kind using the GCP_AUTH_KIND env variable.

  • For authentication, you can set scopes using the GCP_SCOPES env variable.

  • Environment variables values will only be used if the playbook values are not set.

  • The service_account_email and service_account_file options are mutually exclusive.

Examples

- name: create a dataset
  gcp_bigquery_dataset:
    name: example_dataset
    dataset_reference:
      dataset_id: example_dataset
    project: "{{ gcp_project }}"
    auth_kind: "{{ gcp_cred_kind }}"
    service_account_file: "{{ gcp_cred_file }}"
    state: present
  register: dataset

- name: create a table
  gcp_bigquery_table:
    name: example_table
    dataset: example_dataset
    table_reference:
      dataset_id: example_dataset
      project_id: test_project
      table_id: example_table
    project: test_project
    auth_kind: serviceaccount
    service_account_file: "/tmp/auth.pem"
    state: present

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key Returned Description
creationTime
integer
success
The time when this dataset was created, in milliseconds since the epoch.

dataset
string
success
Name of the dataset.

description
string
success
A user-friendly description of the dataset.

encryptionConfiguration
complex
success
Custom encryption configuration.

  kmsKeyName
string
success
Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key.

expirationTime
integer
success
The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely.

externalDataConfiguration
complex
success
Describes the data format, location, and other properties of a table stored outside of BigQuery. By defining these properties, the data source can then be queried as if it were a standard BigQuery table.

  autodetect
boolean
success
Try to detect schema and format options automatically. Any option specified explicitly will be honored.

  bigtableOptions
complex
success
Additional options if sourceFormat is set to BIGTABLE.

    columnFamilies
complex
success
List of column families to expose in the table schema along with their types.

      columns
complex
success
Lists of columns that should be exposed as individual fields as opposed to a list of (column name, value) pairs.

        encoding
string
success
The encoding of the values when the type is not STRING.

        fieldName
string
success
If the qualifier is not a valid BigQuery field identifier, a valid identifier must be provided as the column field name and is used as field name in queries.

        onlyReadLatest
boolean
success
If this is set, only the latest version of value in this column are exposed .

        qualifierString
string
success
Qualifier of the column.

        type
string
success
The type to convert the value in cells of this column.

      encoding
string
success
The encoding of the values when the type is not STRING.

      familyId
string
success
Identifier of the column family.

      onlyReadLatest
boolean
success
If this is set only the latest version of value are exposed for all columns in this column family .

      type
string
success
The type to convert the value in cells of this column family.

    ignoreUnspecifiedColumnFamilies
boolean
success
If field is true, then the column families that are not specified in columnFamilies list are not exposed in the table schema .

    readRowkeyAsString
boolean
success
If field is true, then the rowkey column families will be read and converted to string.

  compression
string
success
The compression type of the data source.

  csvOptions
complex
success
Additional properties to set if sourceFormat is set to CSV.

    allowJaggedRows
boolean
success
Indicates if BigQuery should accept rows that are missing trailing optional columns .

    allowQuotedNewlines
boolean
success
Indicates if BigQuery should allow quoted data sections that contain newline characters in a CSV file .

    encoding
string
success
The character encoding of the data.

    fieldDelimiter
string
success
The separator for fields in a CSV file.

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

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

  googleSheetsOptions
complex
success
Additional options if sourceFormat is set to GOOGLE_SHEETS.

    skipLeadingRows
integer
success
The number of rows at the top of a Google Sheet that BigQuery will skip when reading the data.

  ignoreUnknownValues
boolean
success
Indicates if BigQuery should allow extra values that are not represented in the table schema .

  maxBadRecords
integer
success
The maximum number of bad records that BigQuery can ignore when reading data .

  schema
complex
success
The schema for the data. Schema is required for CSV and JSON formats.

    fields
complex
success
Describes the fields in a table.

      description
string
success
The field description.

      fields
list
success
Describes the nested schema fields if the type property is set to RECORD .

      mode
string
success
Field mode.

      name
string
success
Field name.

      type
string
success
Field data type.

  sourceFormat
string
success
The data format.

  sourceUris
list
success
The fully-qualified URIs that point to your data in Google Cloud. For Google Cloud Storage URIs: Each URI can contain one '*' wildcard character and it must come after the 'bucket' name. Size limits related to load jobs apply to external data sources. For Google Cloud Bigtable URIs: Exactly one URI can be specified and it has be a fully specified and valid HTTPS URL for a Google Cloud Bigtable table. For Google Cloud Datastore backups, exactly one URI can be specified. Also, the '*' wildcard character is not allowed.

friendlyName
string
success
A descriptive name for this table.

id
string
success
An opaque ID uniquely identifying the table.

labels
dictionary
success
The labels associated with this dataset. You can use these to organize and group your datasets .

lastModifiedTime
integer
success
The time when this table was last modified, in milliseconds since the epoch.

location
string
success
The geographic location where the table resides. This value is inherited from the dataset.

name
string
success
Name of the table.

numBytes
integer
success
The size of this table in bytes, excluding any data in the streaming buffer.

numLongTermBytes
integer
success
The number of bytes in the table that are considered "long-term storage".

numRows
integer
success
The number of rows of data in this table, excluding any data in the streaming buffer.

schema
complex
success
Describes the schema of this table.

  fields
complex
success
Describes the fields in a table.

    description
string
success
The field description. The maximum length is 1,024 characters.

    fields
list
success
Describes the nested schema fields if the type property is set to RECORD.

    mode
string
success
The field mode.

    name
string
success
The field name.

    type
string
success
The field data type.

streamingBuffer
complex
success
Contains information regarding this table's streaming buffer, if one is present. This field will be absent if the table is not being streamed to or if there is no data in the streaming buffer.

  estimatedBytes
integer
success
A lower-bound estimate of the number of bytes currently in the streaming buffer.

  estimatedRows
integer
success
A lower-bound estimate of the number of rows currently in the streaming buffer.

  oldestEntryTime
integer
success
Contains the timestamp of the oldest entry in the streaming buffer, in milliseconds since the epoch, if the streaming buffer is available.

tableReference
complex
success
Reference describing the ID of this table.

  datasetId
string
success
The ID of the dataset containing this table.

  projectId
string
success
The ID of the project containing this table.

  tableId
string
success
The ID of the the table.

timePartitioning
complex
success
If specified, configures time-based partitioning for this table.

  expirationMs
integer
success
Number of milliseconds for which to keep the storage for a partition.

  type
string
success
The only type supported is DAY, which will generate one partition per day.

type
string
success
Describes the table type.

view
complex
success
The view definition.

  useLegacySql
boolean
success
Specifies whether to use BigQuery's legacy SQL for this view .

  userDefinedFunctionResources
complex
success
Describes user-defined function resources used in the query.

    inlineCode
string
success
An inline resource that contains code for a user-defined function (UDF). Providing a inline code resource is equivalent to providing a URI for a file containing the same code.

    resourceUri
string
success
A code resource to load from a Google Cloud Storage URI (gs://bucket/path).



Status

Authors

  • Google Inc. (@googlecloudplatform)

Hint

If you notice any issues in this documentation you can edit this document to improve it.