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
  location: us-central1

Note: If you have any trouble with instantiating the resource, refer to Troubleshoot Config Connector.