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 Yes
Supports IAM Audit Configs No
IAM External Reference Format

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

Config Connector Default Average Reconcile Interval In Seconds 600

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
  kmsKeyVersion: string
expirationTime: integer
externalDataConfiguration:
  autodetect: boolean
  avroOptions:
    useAvroLogicalTypes: boolean
  compression: string
  connectionId: string
  csvOptions:
    allowJaggedRows: boolean
    allowQuotedNewlines: boolean
    encoding: string
    fieldDelimiter: string
    quote: string
    skipLeadingRows: integer
  fileSetSpecType: string
  googleSheetsOptions:
    range: string
    skipLeadingRows: integer
  hivePartitioningOptions:
    mode: string
    requirePartitionFilter: boolean
    sourceUriPrefix: string
  ignoreUnknownValues: boolean
  jsonOptions:
    encoding: string
  maxBadRecords: integer
  metadataCacheMode: string
  objectMetadata: string
  parquetOptions:
    enableListInference: boolean
    enumAsString: boolean
  referenceFileSchemaUri: string
  schema: string
  sourceFormat: string
  sourceUris:
  - string
friendlyName: string
materializedView:
  allowNonIncrementalDefinition: boolean
  enableRefresh: boolean
  query: string
  refreshIntervalMs: integer
maxStaleness: string
rangePartitioning:
  field: string
  range:
    end: integer
    interval: integer
    start: integer
requirePartitionFilter: boolean
resourceID: string
schema: string
tableConstraints:
  foreignKeys:
  - columnReferences:
      referencedColumn: string
      referencingColumn: string
    name: string
    referencedTable:
      datasetId: string
      projectId: string
      tableId: string
  primaryKey:
    columns:
    - 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

Allowed value: The `name` field of a `BigQueryDataset` resource.

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

Immutable. 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

Allowed value: The `selfLink` field of a `KMSCryptoKey` resource.

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/

encryptionConfiguration.kmsKeyVersion

Optional

string

The self link or full name of the kms key version used to encrypt this table.

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.avroOptions

Optional

object

Additional options if source_format is set to "AVRO".

externalDataConfiguration.avroOptions.useAvroLogicalTypes

Required*

boolean

If sourceFormat is set to "AVRO", indicates whether to interpret logical types as the corresponding BigQuery data type (for example, TIMESTAMP), instead of using the raw type (for example, INTEGER).

externalDataConfiguration.compression

Optional

string

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

externalDataConfiguration.connectionId

Optional

string

The connection specifying the credentials to be used to read external storage, such as Azure Blob, Cloud Storage, or S3. The connectionId can have the form "{{project}}.{{location}}.{{connection_id}}" or "projects/{{project}}/locations/{{location}}/connections/{{connection_id}}".

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.fileSetSpecType

Optional

string

Specifies how source URIs are interpreted for constructing the file set to load. By default source URIs are expanded against the underlying storage. Other options include specifying manifest files. Only applicable to object storage systems.

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.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.

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.jsonOptions

Optional

object

Additional properties to set if sourceFormat is set to JSON.".

externalDataConfiguration.jsonOptions.encoding

Optional

string

The character encoding of the data. The supported values are UTF-8, UTF-16BE, UTF-16LE, UTF-32BE, and UTF-32LE. The default value is UTF-8.

externalDataConfiguration.maxBadRecords

Optional

integer

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

externalDataConfiguration.metadataCacheMode

Optional

string

Metadata Cache Mode for the table. Set this to enable caching of metadata from external data source.

externalDataConfiguration.objectMetadata

Optional

string

Object Metadata is used to create Object Tables. Object Tables contain a listing of objects (with their metadata) found at the sourceUris. If ObjectMetadata is set, sourceFormat should be omitted.

externalDataConfiguration.parquetOptions

Optional

object

Additional properties to set if sourceFormat is set to PARQUET.".

externalDataConfiguration.parquetOptions.enableListInference

Optional

boolean

Indicates whether to use schema inference specifically for Parquet LIST logical type.

externalDataConfiguration.parquetOptions.enumAsString

Optional

boolean

Indicates whether to infer Parquet ENUM logical type as STRING instead of BYTES by default.

externalDataConfiguration.referenceFileSchemaUri

Optional

string

When creating an external table, the user can provide a reference file with the table schema. This is enabled for the following formats: AVRO, PARQUET, ORC.

externalDataConfiguration.schema

Optional

string

Immutable. 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

Optional

string

Please see sourceFormat under ExternalDataConfiguration in Bigquery's public API documentation (https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#externaldataconfiguration) for supported formats. 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.allowNonIncrementalDefinition

Optional

boolean

Immutable. Allow non incremental materialized view definition. The default value is false.

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

Immutable. 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.

maxStaleness

Optional

string

The maximum staleness of data that could be returned when the table (or stale MV) is queried. Staleness encoded as a string encoding of sql IntervalValue type.

rangePartitioning

Optional

object

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

rangePartitioning.field

Required*

string

Immutable. 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.

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.

resourceID

Optional

string

Immutable. Optional. The tableId of the resource. Used for creation and acquisition. When unset, the value of `metadata.name` is used as the default.

schema

Optional

string

A JSON schema for the table.

tableConstraints

Optional

object

Defines the primary key and foreign keys.

tableConstraints.foreignKeys

Optional

list (object)

Present only if the table has a foreign key. The foreign key is not enforced.

tableConstraints.foreignKeys[]

Optional

object

tableConstraints.foreignKeys[].columnReferences

Required*

object

The pair of the foreign key column and primary key column.

tableConstraints.foreignKeys[].columnReferences.referencedColumn

Required*

string

The column in the primary key that are referenced by the referencingColumn.

tableConstraints.foreignKeys[].columnReferences.referencingColumn

Required*

string

The column that composes the foreign key.

tableConstraints.foreignKeys[].name

Optional

string

Set only if the foreign key constraint is named.

tableConstraints.foreignKeys[].referencedTable

Required*

object

The table that holds the primary key and is referenced by this foreign key.

tableConstraints.foreignKeys[].referencedTable.datasetId

Required*

string

The ID of the dataset containing this table.

tableConstraints.foreignKeys[].referencedTable.projectId

Required*

string

The ID of the project containing this table.

tableConstraints.foreignKeys[].referencedTable.tableId

Required*

string

The ID of the table. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters. Certain operations allow suffixing of the table ID with a partition decorator, such as sample_table$20190123.

tableConstraints.primaryKey

Optional

object

Represents a primary key constraint on a table's columns. Present only if the table has a primary key. The primary key is not enforced.

tableConstraints.primaryKey.columns

Required*

list (string)

The columns that are composed of the primary key constraint.

tableConstraints.primaryKey.columns[]

Required*

string

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

Immutable. 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

DEPRECATED. This field is deprecated; please use the top level field with the same name instead. 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, HOUR, MONTH, and YEAR, which will generate one partition per day, hour, month, and year, respectively.

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
observedGeneration: integer
selfLink: string
type: string
Fields
conditions

list (object)

Conditions represent 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.

observedGeneration

integer

ObservedGeneration is the generation of the resource that was most recently observed by the Config Connector controller. If this is equal to metadata.generation, then that means that the current reported status reflects the most recent desired state of the resource.

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
  requirePartitionFilter: true
  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