How to Create a Table in BigQuery: Syntax and Examples

To create a table in BigQuery, use the CREATE TABLE statement with the table name and schema definition. You can define columns with their data types and optionally specify table options like partitioning or clustering.

Syntax

The basic syntax to create a table in BigQuery is:

• CREATE TABLE: Command to create a new table.
• dataset.table_name: Specify the dataset and the new table name.
• (column_name data_type, ...): Define columns and their data types.
• Optional clauses like OPTIONS() can set table properties.

sql

CREATE TABLE dataset.table_name (
  column1 STRING,
  column2 INT64,
  column3 DATE
);

Example

This example creates a table named employees in the company_data dataset with three columns: name, age, and hire_date.

sql

CREATE TABLE company_data.employees (
  name STRING,
  age INT64,
  hire_date DATE
);

Output

Query successfully executed. Table company_data.employees created.

Common Pitfalls

Common mistakes when creating tables in BigQuery include:

• Forgetting to specify the dataset before the table name, which causes errors.
• Using unsupported data types or misspelling data types.
• Trying to create a table that already exists without using CREATE OR REPLACE TABLE.
• Not defining a schema, which is required unless creating an empty table from a query.

sql

/* Wrong: Missing dataset */
CREATE TABLE employees (
  name STRING
);

/* Right: Include dataset */
CREATE TABLE company_data.employees (
  name STRING
);

Quick Reference

Clause	Description
CREATE TABLE	Starts the table creation command
dataset.table_name	Specifies where the table will be created
(column_name data_type, ...)	Defines the table schema columns and types
OPTIONS()	Optional settings like partitioning or clustering
CREATE OR REPLACE TABLE	Replaces existing table if it exists

Key Takeaways

Always specify the dataset before the table name when creating a table.

Define each column with a valid BigQuery data type in the schema.

Use CREATE OR REPLACE TABLE to overwrite an existing table safely.

Avoid common errors by checking data types and table existence before creation.

BigQuery requires a schema definition unless creating a table from a query result.