Table: gcp_bigquery_table - Query BigQuery Tables using SQL

BigQuery is a fully-managed, serverless data warehouse that enables super-fast SQL queries using the processing power of Google's infrastructure. It allows you to analyze large datasets in real-time with a SQL-like syntax, without the need to manage the underlying infrastructure. BigQuery is designed to be not only fast, but also easy to use, and cost-effective.

Table Usage Guide

The gcp_bigquery_table table provides insights into BigQuery Tables within Google Cloud Platform (GCP). As a data analyst or data engineer, explore table-specific details through this table, including creation time, expiration time, labels, and more. Utilize it to uncover information about tables, such as those with specific labels, the status of tables, and the verification of table metadata.

Examples

Basic info

Discover the segments that are part of your Google Cloud BigQuery data, including their creation time and geographical location. This can aid in understanding the distribution and age of your data across different regions.

select
  table_id,
  dataset_id,
  self_link,
  creation_time,
  location
from
  gcp_bigquery_table;

select
  table_id,
  dataset_id,
  self_link,
  creation_time,
  location
from
  gcp_bigquery_table;

List tables that are not encrypted using CMK

Discover the segments that are potentially vulnerable due to the absence of encryption using a Customer-Managed Key (CMK). This aids in identifying areas that require enhanced security measures.

select
  table_id,
  dataset_id,
  location,
  kms_key_name
from
  gcp_bigquery_table
where
  kms_key_name is null;

select
  table_id,
  dataset_id,
  location,
  kms_key_name
from
  gcp_bigquery_table
where
  kms_key_name is null;

List tables which do not have owner tag key

Discover the segments that lack an owner tag key within your Google Cloud Platform's BigQuery datasets. This can be beneficial for identifying potential areas of unclaimed resources or orphaned datasets that need attention.

select
  dataset_id,
  location
from
  gcp_bigquery_table
where
  tags -> 'owner' is null;

select
  dataset_id,
  location
from
  gcp_bigquery_table
where
  json_extract(tags, '$.owner') is null;

Query examples

bigquery_tables_for_kms_key

Control examples

Schema for gcp_bigquery_table

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
clustering_fields	jsonb		One or more fields on which data should be clustered.
creation_time	timestamp with time zone		The time when this table was created, in milliseconds since the epoch.
dataset_id	text	=	The ID of the dataset containing this table.
description	text		A user-friendly description of this table.
etag	text		A hash of the table metadata, used to ensure there were no concurrent modifications to the resource when attempting an update.
expiration_time	timestamp with time zone		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. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created tables.
external_data_configuration	jsonb		Describes the data format, location, and other properties of a table stored outside of BigQuery.
id	text		An opaque ID uniquely identifying the table.
kind	text		The type of resource ID.
kms_key_name	text		Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table.
labels	jsonb		The labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key.
last_modified_time	timestamp with time zone		The time when this table was last modified.
location	text		The GCP multi-region, region, or zone in which the resource is located.
materialized_view	jsonb		Describes materialized view definition.
num_bytes	bigint		The size of this table in bytes, excluding any data in the streaming buffer.
num_long_term_bytes	bigint		The number of bytes in the table that are considered 'long-term storage'.
num_physical_bytes	bigint		The physical size of this table in bytes, excluding any data in the streaming buffer.
num_rows	bigint		The number of rows of data in this table, excluding any data in the streaming buffer.
project	text	=, !=, ~~, ~~, !~~, !~~	The GCP Project in which the resource is located.
range_partitioning	jsonb		If specified, configures range partitioning for this table.
require_partition_filter	boolean		If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified.
schema_fields	jsonb		Describes the fields in a table.
self_link	text		A URL that can be used to access this resource again.
snapshot_time	timestamp with time zone		The time at which the base table was snapshot.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
streaming_buffer	jsonb		Contains information regarding this table's streaming buffer, if one is present.
table_id	text	=	The ID of the table resource.
tags	jsonb		A map of tags for the resource.
time_partitioning	jsonb		If specified, configures time-based partitioning for this table.
title	text		Title of the resource.
type	text		The type of table. Possible values are: TABLE, VIEW.
view	jsonb		dditional details for a view.
view_query	text		A query that BigQuery executes when the view is referenced.
view_use_legacy_sql	boolean		True if view is defined in legacy SQL dialect, false if in standard SQL.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp

You can pass the configuration to the command with the --config argument:

steampipe_export_gcp --config '<your_config>' gcp_bigquery_table