Introduction to tables (original) (raw)

A BigQuery table contains individual records organized in rows. Each record is composed of columns (also called fields).

Every table is defined by a schema that describes the column names, data types, and other information. You can specify the schema of a table when it is created, or you can create a table without a schema and declare the schema in the query job or load job that first populates it with data.

Use the format projectname.datasetname.tablename to fully qualify a table name when using GoogleSQL, or the format projectname:datasetname.tablename to fully qualify a table name when using the bq command-line tool.

Table types

The following sections describe the table types that BigQuery supports.

• Standard BigQuery tables: structured data stored in BigQuery storage.
• External tables: tables that reference data stored outside BigQuery.
• Views: logical tables that are created by using a SQL query.

Standard BigQuery tables

Standard BigQuery tables contain structured data and are stored in BigQuery storage in a columnar format. You can also store references to unstructured data in standard tables by using struct columns that adhere to theObjectRefformat. For more information about working with ObjectRef values, seeSpecify ObjectRef columns in table schemas.

BigQuery has the following table types:

• Tables, which have a schema and every column in the schema has a data type.
  For information about how to create tables, see Create tables.
• Table clones, which are lightweight, writeable copies of BigQuery tables. BigQuery only stores the delta between a table clone and its base table.
  For information about how to create table clone, see Create table clones.
• Table snapshots, which are point-in-time copies of tables. They are read-only, but you can restore a table from a table snapshot. BigQuery stores bytes that are different between a snapshot and its base table, so a table snapshot typically uses less storage than a full copy of the table.
  For information about how to create table snapshots, see Create table snapshots.

External tables

External tables are stored outside out of BigQuery storage and refer to data that's stored outside of BigQuery. For more information, seeIntroduction to external data sources. External tables include the following types:

• BigLake tables, which reference structured data stored in data stores such as Cloud Storage, Amazon Simple Storage Service (Amazon S3), and Azure Blob Storage. These tables let you enforce fine-grained security at the table level.
  For information about how to create BigLake tables, see the following topics:
  • Cloud Storage
  • Amazon S3
  • Blob Storage
• Object tables, which reference unstructured data stored in data stores such as Cloud Storage.
  For information about how to create object tables, see Create object tables.
• Non-BigLake external tables, which reference structured data stored in data stores such as Cloud Storage, Google Drive, and Bigtable. Unlike BigLake tables, these tables don't let you enforce fine-grained security at the table level.
  For information about how to create non-BigLake external tables, see the following topics:
  • Cloud Storage
  • Google Drive
  • Bigtable

Views

Views are logical tables that are defined by using a SQL query. These include the following types:

• Views, which are logical tables that are defined by using SQL queries. These queries define the view that is run each time the view is queried.
  For information about how to create views, see Create views.
• Materialized views, which are precomputed views that periodically cache the results of the view query. The cached results are stored in BigQuery storage.
  For information about how to create materialized views, see Create materialized views.

Table limitations

BigQuery tables are subject to the following limitations:

• Table names must be unique per dataset.
• When you export BigQuery table data, the only supported destination is Cloud Storage.
• When you use an API call, enumeration performance slows as you approach 50,000 tables in a dataset.
• The Google Cloud console can display up to 50,000 tables for each dataset.

For information about BigQuery external table limitations, see the following topics:

• BigLake tables
• Object tables
• External tables

Table quotas

Quotas and limits apply to the different types of jobs you can run against tables, including the following quotas:

• Load data into tables (load jobs)
• Export data from tables (export jobs)
• Query table data (query jobs)
• Copy tables (copy jobs)

For more information about all quotas and limits, see Quotas and limits.

Table pricing

When you create and use tables in BigQuery, your charges are based on how much data is stored in the tables and partitions and on the queries you run against the table data:

• For information about storage pricing, see Storage pricing.
• For information about query pricing, see Query pricing.

Many table operations are free, including loading, copying, and exporting data. Though free, these operations are subject to BigQueryquotas and limits. For information about all free operations, see Free operations on the pricing page.

Table security

To control access to tables in BigQuery, seeControl access to resources with IAM.

What's next

• Learn how to create and use tables.
• Learn how to manage tables.
• Learn how to modify table schemas.
• Learn about working with table data.