BigQueryTable

Property Value
Google Cloud Service Name BigQuery
Google Cloud Service Documentation /bigquery/docs/
Google Cloud Rest Resource Name bigquery/v2/tables
Google Cloud Rest Resource Documentation /bigquery/docs/reference/rest/v2/tables
Config Connector Resource Short Names gcpbigquerytable
gcpbigquerytables
bigquerytable
Config Connector Service Name bigquery.googleapis.com
Config Connector Resource Fully Qualified Name bigquerytables.bigquery.cnrm.cloud.google.com
Can Be Referenced by IAMPolicy/IAMPolicyMember Yes
Supports IAM Conditions No
Supports IAM Audit Configs No
IAM External Reference Format

projects/{{project}}/datasets/{{dataset_id}}/tables/{{table_id}}

Custom Resource Definition Properties

Annotations

Fields
cnrm.cloud.google.com/project-id

Spec

Schema

  clustering:
  - string
  datasetRef:
    external: string
    name: string
    namespace: string
  description: string
  encryptionConfiguration:
    kmsKeyRef:
      external: string
      name: string
      namespace: string
  expirationTime: integer
  externalDataConfiguration:
    autodetect: boolean
    compression: string
    csvOptions:
      allowJaggedRows: boolean
      allowQuotedNewlines: boolean
      encoding: string
      fieldDelimiter: string
      quote: string
      skipLeadingRows: integer
    googleSheetsOptions:
      range: string
      skipLeadingRows: integer
    hivePartitioningOptions:
      mode: string
      sourceUriPrefix: string
    ignoreUnknownValues: boolean
    maxBadRecords: integer
    schema: string
    sourceFormat: string
    sourceUris:
    - string
  friendlyName: string
  materializedView:
    enableRefresh: boolean
    query: string
    refreshIntervalMs: integer
  rangePartitioning:
    field: string
    range:
      end: integer
      interval: integer
      start: integer
  schema: string
  timePartitioning:
    expirationMs: integer
    field: string
    requirePartitionFilter: boolean
    type: string
  view:
    query: string
    useLegacySql: boolean
Fields

clustering

Optional

list (string)

Specifies column names to use for data clustering. Up to four top-level columns are allowed, and should be specified in descending priority order.

clustering.[]

Optional

string

datasetRef

Required

object

datasetRef.external

Optional

string

The name of a BigQueryDataset.

datasetRef.name

Optional

string

Name of the referent. More info: https://kubernetes.io/docs/concepts/overview/working-with-objects/names/#names

datasetRef.namespace

Optional

string

Namespace of the referent. More info: https://kubernetes.io/docs/concepts/overview/working-with-objects/namespaces/

description

Optional

string

The field description.

encryptionConfiguration

Optional

object

Specifies how the table should be encrypted. If left blank, the table will be encrypted with a Google-managed key; that process is transparent to the user.

encryptionConfiguration.kmsKeyRef

Required*

object

encryptionConfiguration.kmsKeyRef.external

Optional

string

The selfLink of a KMSCryptoKey.

encryptionConfiguration.kmsKeyRef.name

Optional

string

Name of the referent. More info: https://kubernetes.io/docs/concepts/overview/working-with-objects/names/#names

encryptionConfiguration.kmsKeyRef.namespace

Optional

string

Namespace of the referent. More info: https://kubernetes.io/docs/concepts/overview/working-with-objects/namespaces/

expirationTime

Optional

integer

The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely. Expired tables will be deleted and their storage reclaimed.

externalDataConfiguration

Optional

object

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.

externalDataConfiguration.autodetect

Required*

boolean

Let BigQuery try to autodetect the schema and format of the table.

externalDataConfiguration.compression

Optional

string

The compression type of the data source. Valid values are "NONE" or "GZIP".

externalDataConfiguration.csvOptions

Optional

object

Additional properties to set if source_format is set to "CSV".

externalDataConfiguration.csvOptions.allowJaggedRows

Optional

boolean

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

externalDataConfiguration.csvOptions.allowQuotedNewlines

Optional

boolean

Indicates if BigQuery should allow quoted data sections that contain newline characters in a CSV file. The default value is false.

externalDataConfiguration.csvOptions.encoding

Optional

string

The character encoding of the data. The supported values are UTF-8 or ISO-8859-1.

externalDataConfiguration.csvOptions.fieldDelimiter

Optional

string

The separator for fields in a CSV file.

externalDataConfiguration.csvOptions.quote

Required*

string

externalDataConfiguration.csvOptions.skipLeadingRows

Optional

integer

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

externalDataConfiguration.googleSheetsOptions

Optional

object

Additional options if source_format is set to "GOOGLE_SHEETS".

externalDataConfiguration.googleSheetsOptions.range

Optional

string

Range of a sheet to query from. Only used when non-empty. At least one of range or skip_leading_rows must be set. Typical format: "sheet_name!top_left_cell_id:bottom_right_cell_id" For example: "sheet1!A1:B20"

externalDataConfiguration.googleSheetsOptions.skipLeadingRows

Optional

integer

The number of rows at the top of the sheet that BigQuery will skip when reading the data. At least one of range or skip_leading_rows must be set.

externalDataConfiguration.hivePartitioningOptions

Optional

object

When set, configures hive partitioning support. Not all storage formats support hive partitioning -- requesting hive partitioning on an unsupported format will lead to an error, as will providing an invalid specification.

externalDataConfiguration.hivePartitioningOptions.mode

Optional

string

When set, what mode of hive partitioning to use when reading data.

externalDataConfiguration.hivePartitioningOptions.sourceUriPrefix

Optional

string

When hive partition detection is requested, a common for all source uris must be required. The prefix must end immediately before the partition key encoding begins.

externalDataConfiguration.ignoreUnknownValues

Optional

boolean

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. The default value is false.

externalDataConfiguration.maxBadRecords

Optional

integer

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

externalDataConfiguration.schema

Optional

string

A JSON schema for the external table. Schema is required for CSV and JSON formats and is disallowed for Google Cloud Bigtable, Cloud Datastore backups, and Avro formats when using external tables.

externalDataConfiguration.sourceFormat

Required*

string

The data format. Supported values are: "CSV", "GOOGLE_SHEETS", "NEWLINE_DELIMITED_JSON", "AVRO", "PARQUET", and "DATSTORE_BACKUP". To use "GOOGLE_SHEETS" the scopes must include "googleapis.com/auth/drive.readonly".

externalDataConfiguration.sourceUris

Required*

list (string)

A list of the fully-qualified URIs that point to your data in Google Cloud.

externalDataConfiguration.sourceUris.[]

Required*

string

friendlyName

Optional

string

A descriptive name for the table.

materializedView

Optional

object

If specified, configures this table as a materialized view.

materializedView.enableRefresh

Optional

boolean

Specifies if BigQuery should automatically refresh materialized view when the base table is updated. The default is true.

materializedView.query

Required*

string

A query whose result is persisted.

materializedView.refreshIntervalMs

Optional

integer

Specifies maximum frequency at which this materialized view will be refreshed. The default is 1800000

rangePartitioning

Optional

object

If specified, configures range-based partitioning for this table.

rangePartitioning.field

Required*

string

The field used to determine how to create a range-based partition.

rangePartitioning.range

Required*

object

Information required to partition based on ranges. Structure is documented below.

rangePartitioning.range.end

Required*

integer

End of the range partitioning, exclusive.

rangePartitioning.range.interval

Required*

integer

The width of each range within the partition.

rangePartitioning.range.start

Required*

integer

Start of the range partitioning, inclusive.

schema

Optional

string

A JSON schema for the table.

timePartitioning

Optional

object

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

timePartitioning.expirationMs

Optional

integer

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

timePartitioning.field

Optional

string

The field used to determine how to create a time-based partition. If time-based partitioning is enabled without this value, the table is partitioned based on the load time.

timePartitioning.requirePartitionFilter

Optional

boolean

If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified.

timePartitioning.type

Required*

string

The supported types are DAY and HOUR, which will generate one partition per day or hour based on data loading time.

view

Optional

object

If specified, configures this table as a view.

view.query

Required*

string

A query that BigQuery executes when the view is referenced.

view.useLegacySql

Optional

boolean

Specifies whether to use BigQuery's legacy SQL for this view. The default value is true. If set to false, the view will use BigQuery's standard SQL

* Field is required when parent field is specified

Status

Schema

  conditions:
  - lastTransitionTime: string
    message: string
    reason: string
    status: string
    type: string
  creationTime: integer
  etag: string
  lastModifiedTime: integer
  location: string
  numBytes: integer
  numLongTermBytes: integer
  numRows: integer
  selfLink: string
  type: string
Fields
conditions

list (object)

Conditions represents the latest available observation of the resource's current state.

conditions.[]

object

conditions.[].lastTransitionTime

string

Last time the condition transitioned from one status to another.

conditions.[].message

string

Human-readable message indicating details about last transition.

conditions.[].reason

string

Unique, one-word, CamelCase reason for the condition's last transition.

conditions.[].status

string

Status is the status of the condition. Can be True, False, Unknown.

conditions.[].type

string

Type is the type of the condition.

creationTime

integer

The time when this table was created, in milliseconds since the epoch.

etag

string

A hash of the resource.

lastModifiedTime

integer

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

location

string

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

numBytes

integer

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

numLongTermBytes

integer

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

numRows

integer

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

selfLink

string

The URI of the created resource.

type

string

Describes the table type.

Sample YAML(s)

Typical Use Case

  # Copyright 2020 Google LLC
  #
  # Licensed under the Apache License, Version 2.0 (the "License");
  # you may not use this file except in compliance with the License.
  # You may obtain a copy of the License at
  #
  #     http://www.apache.org/licenses/LICENSE-2.0
  #
  # Unless required by applicable law or agreed to in writing, software
  # distributed under the License is distributed on an "AS IS" BASIS,
  # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  # See the License for the specific language governing permissions and
  # limitations under the License.
  
  apiVersion: bigquery.cnrm.cloud.google.com/v1beta1
  kind: BigQueryTable
  metadata:
    name: bigquerytablesample
    labels:
      data-source: "external"
      schema-type: "auto-junk"
  spec:
    description: "BigQuery Sample Table"
    datasetRef:
      name: bigquerytabledep
    friendlyName: bigquerytable-sample
    externalDataConfiguration:
      autodetect: true
      compression: NONE
      ignoreUnknownValues: false
      maxBadRecords: 10
      sourceFormat: CSV
      sourceUris:
        - "gs://gcp-public-data-landsat/LC08/01/044/034/LC08_L1GT_044034_20130330_20170310_01_T2/LC08_L1GT_044034_20130330_20170310_01_T2_ANG.txt"
        - "gs://gcp-public-data-landsat/LC08/01/044/034/LC08_L1GT_044034_20130330_20180201_01_T2/LC08_L1GT_044034_20130330_20180201_01_T2_ANG.txt"
  ---
  apiVersion: bigquery.cnrm.cloud.google.com/v1beta1
  kind: BigQueryDataset
  metadata:
    name: bigquerytabledep
  spec:
    friendlyName: bigquerytable-dep