How to Create a Table in BigQuery

This guide includes different ways to create a table in Google BigQuery. You will see that you can create a table through the following methods:

• CREATE TABLE command
• CREATE TABLE command from a SELECT query
• Upload from CSV
• Upload from Google Sheets

You will also see examples for the CREATE TABLE IF NOT EXISTS syntax. This handling prevents errors if the table name is already used.

You cannot set a default value in BigQuery, but I will show examples to handle this in BigQuery using views.

You can also check out the official documentation from Google BigQuery.

Prerequisites

Things to keep in mind before you can start creating your tables in BigQuery.

Permissions and Roles

To create tables in BigQuery, a user should be given the following permissions:

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.jobs.create

To grant these three permissions, you can assign two roles through the IAM & Admin section:

• BigQuery Data Editor (or BigQuery Data Owner)
• BigQuery Job User (or BigQuery User)

Alternatively, the Owner or BigQuery Admin roles will also allow a user to create tables. However, these are powerful roles, so grant them only when necessary.

If a user is missing any of these permissions, you will get the following error:

Access Denied: Project <project>: User does not have <permission> permission in project <project>.

Dataset

The dataset must already be created before you can create the table. If the dataset is missing, you will get the following error:

Not found: Dataset <project>:<dataset>

In BigQuery, we follow this hierarchy: Project > Dataset(s) > Table(s). So, if you want to create the following table:

Project: yuichi-otsuka
Dataset: sandbox
Table: employee

Make sure that the project yuichi-otsuka and dataset sandbox are already created.

Create Table Using the CREATE TABLE Script

Here’s an example of a script to create a table in BigQuery.

Project: yuichi-otsuka
Dataset: sandbox
Table: employee

CREATE TABLE `yuichi-otsuka.sandbox.employee`
(
  EmployeeID INT64,
  FirstName STRING,
  LastName STRING,
  HireDate DATE,
  City STRING,
  Salary FLOAT64,
  Terminated BOOL
)
-- PARTITION BY HireDate
-- CLUSTER BY City
;

Message:

This statement created a new table named yuichi-otsuka:sandbox.employee.

The PARTITION BY and CLUSTER BY are optional.

You can choose from the following BigQuery data types for each column:

• INT64
• NUMERIC
• FLOAT64
• STRING
• BOOL
• BYTES
• DATE
• TIME
• DATETIME
• TIMESTAMP
• ARRAY
• STRUCT
• GEOGRAPHY

Create Table with a REQUIRED Column

You can add the NOT NULL keyword for each column that you want to be mandatory or required.

CREATE TABLE `yuichi-otsuka.sandbox.employee`
(
  EmployeeID INT64 NOT NULL,
  FirstName STRING NOT NULL,
  LastName STRING NOT NULL,
  HireDate DATE,
  City STRING,
  Salary FLOAT64,
  Terminated BOOL
)
;

In the query above, the columns EmployeeID, FirstName, and LastName cannot be null.

CREATE TABLE IF NOT EXISTS

If you attempt to create a table whose name is already taken, you will get the following error:

Already Exists: Table yuichi-otsuka:sandbox.employee

To prevent this error, you can add the IF NOT EXISTS keyword.

CREATE TABLE IF NOT EXISTS `yuichi-otsuka.sandbox.employee`
(
  EmployeeID INT64 NOT NULL,
  FirstName STRING NOT NULL,
  LastName STRING,
  HireDate DATE,
  City STRING,
  Salary FLOAT64,
  Terminated BOOL
)
;

The next time that you attempt to create on an existing table, you will only get a warning.

A table named yuichi-otsuka:sandbox.employee already exists.

What is the Create Table Schema?

The Schema is the column definition, or in technical terms, the column metadata.

You can create a table using the BigQuery Console on your browser. The Schema section is where you can specify information for each column.

BigQuery can automatically detect the schema if you are creating a table from an existing file such as CSV, Google Sheets, or JSON.

However, in most cases, you want to specify the information yourself, to make sure that each column has the correct information.

Schema Mode: REQUIRED, NULLABLE, and REPEATABLE

For each column, you can choose one of three Mode values:

• REQUIRED: cannot be empty or NULL
• NULLABLE: can have empty or NULL values
• REPEATABLE: can have an array having multiple values

Schema Columns: Edit as text

In the create table window, you will see the “Edit as text” button, which is disabled by default.

If you enable this, you can add a block of text that already includes all the column information.

The format of the text is simple if there are no required columns. It’s a colon-pairing of columns and their data types, separated by a comma.

EmployeeID:INTEGER,FirstName:STRING,LastName:STRING,HireDate:DATE,City:STRING,Salary:NUMERIC,Terminated:BOOLEAN

If you have at least one required column, the format changes into a JSON format.

To differentiate between a required and a nullable column, you change the mode to REQUIRED.

[
    {"name": "EmployeeID", "type": "INTEGER", "mode": "REQUIRED"},
    {"name": "FirstName",  "type": "STRING",  "mode": "NULLABLE"},
    {"name": "LastName",   "type": "STRING",  "mode": "NULLABLE"},
    {"name": "HireDate",   "type": "DATE",    "mode": "NULLABLE"},
    {"name": "City",       "type": "STRING",  "mode": "NULLABLE"},
    {"name": "Salary",     "type": "NUMERIC", "mode": "NULLABLE"},
    {"name": "Terminated", "type": "BOOLEAN", "mode": "NULLABLE"}
]

Schema Column: Manually adding each field

If you decide to manually insert each field, add in the following information for each column:

• Name
• Type
• Mode (REQUIRED, NULLABLE, or REPEATABLE)

Schema Column: Setting the Default Value

In other database platforms, when you create a table, you can specify a default value.

However, BigQuery does not provide this option when creating the tables.

Default values can only be handled when querying them, using the IFNULL function.

SELECT
  EmployeeID,
  FirstName,
  IFNULL(FirstName, 'N/A') AS FirstNameDefault
FROM (
  SELECT 1 AS EmployeeID, 'James' AS FirstName
  UNION ALL SELECT 2, 'Nadia'
  UNION ALL SELECT 3, NULL
)
;

Result:

EmployeeID	FirstName	FirstNameDefault
1	James	James
2	Nadia	Nadia
3	null	N/A

Schema Column: Maximum Length

A column can have at most 1024 characters and is case-insensitive.

Create Table from a Query using AS SELECT

You can create a table from the result of another query by using the CREATE TABLE AS SELECT keyword.

CREATE TABLE `yuichi-otsuka.sandbox.employee_active`
AS
SELECT
  EmployeeID,
  FirstName,
  LastName
FROM
  `yuichi-otsuka.sandbox.employee`
WHERE
  Terminated = F
;

In this method, the schema (or column definitions) for the new table will be based on the query result.

Therefore, if you want to enforce a data type, you can use a CAST function on that specific column.

CREATE TABLE `yuichi-otsuka.sandbox.employee_active`
AS
SELECT
  CAST(EmployeeID AS STRING) AS EmployeeID,
  FirstName,
  LastName
FROM
  `yuichi-otsuka.sandbox.employee`
WHERE
  Terminated = F
;

You also need to make sure that each column has a name. In the following query, the first column does not have a name or alias:

CREATE TABLE `yuichi-otsuka.sandbox.employee_active`
AS
SELECT
  EmployeeID + 1000,
  FirstName,
  LastName
FROM
  `yuichi-otsuka.sandbox.employee`
WHERE
  Terminated = F
;

Since column 1 has no name or alias, you will get the following error:

CREATE TABLE columns must be named, but column 1 has no name at …

Create Table from CSV

You can create a table from a CSV file in BigQuery using the GCP Console. This method creates the table and inserts records at the same time.

I will use this CSV as an example:

EmployeeID,FirstName,LastName,HireDate,City,Salary,Terminated
1,Sun,Bak,2020-01-28,Seoul,120000,F
2,Nomi,Marks,2020-02-11,San Francisco,110000,T
3,Kala,Dandekar,2020-03-31,Mumbai,140000,F
4,Riley,Blue,2020-04-18,London,90000,F
5,Wolfgang,Bogdanow,2020-05-20,Berlin,115000,T
6,Lito,Rodriguez,2020-06-19,Mexico City,250000,F
7,Will,Gorski,2020-07-04,Chicago,180000,T

Project: yuichi-otsuka
Dataset: sandbox
Table: employee_csv

Open the Create Table Schema Window

Click on the dataset on the left-side of your console. In my case, it’s the sandbox dataset.

After selecting the dataset, you will see more details in the bottom-right section of your console.

Click on the Create Table button.

Clicking on that button will bring up the Create table window.

Fill up the first section:

1. Source
   • Create table from: Upload / Drive (if in a Google Drive)
   • Select file / Drive URI: select your own file / link to the file in Google Drive
   • File format: CSV
2. Destination
   • Search for a project
   • Project name: select your project
   • Dataset name: select your dataset
   • Table name: name your own table

When filling up the Schema section, I recommend keeping the Auto detect disabled.

Create Table Advanced Options

One important section to change is the Header rows to skip and set it to 1 if you have a header column.

Create Table from CSV – Sample Result

After setting up the table, a job will be created for that task. This job usually takes a few seconds. You can also look at the Job history to monitor the job status.

Once the table is successfully created, the sample table should look like this:

Create Table from Google Sheets

You can also create a table from a Google Sheets file in BigQuery using the GCP Console. Aside from creating a table, you can also insert the records at the same time.

I will use this Google Sheets data as an example:

Project: yuichi-otsuka
Dataset: sandbox
Table: employee_google_sheet

Open the Create Table Schema Window

Click on the dataset on the left-side of your console. In my case, it’s the sandbox dataset.

After selecting the dataset, you will now see more details in the bottom-right section of your console.

Click on the Create Table button.

Clicking on that button will bring up the Create table window.

Fill up the first section:

1. Source
   • Create table from: Drive
   • Select Drive URI: link to the file
   • File format: Google Sheet
   • Sheet range: specify your sheet and range
     • unspecified (defaults to the first sheet)
     • Sheet1 (sheet range only)
     • Sheet1!A1:G8 (sheet + cell range)
2. Destination
   • Search for a project
   • Project name: select your project
   • Dataset name: select your dataset
   • Table name: name your own table

Sheet Range is Important. If you specify the sheet only (without the cell range), the query automatically detects new records, and includes them in the query result.

Thus, if you expect the sheet to have additional rows, make sure to either set a generous cell range, such as A1:G1000, or do not include the cell range.

Now, when filling up the Schema section, I recommend enabling the Auto detect schema.

Create Table Advanced Options

One important section to change is the Header rows to skip and set it to 1 if you have a header column.

Create Table from Google Sheets – Sample Result

After setting up the table, a job will be created for that task. This job usually takes a few seconds. You can also look at the Job history to monitor the job status.

Once the table is successfully created, the sample table should look like this:

There is no Preview tab available in Google Sheets, but you can click on the Query Table button to view your table.

You can run this query to see a preview of your table:

SELECT * 
FROM `yuichi-otsuka.sandbox.employee_google_sheet` 
LIMIT 1000;

Conclusion

There are various ways to create a table in Google BigQuery.

You can use the standard CREATE TABLE scripts, as well as import from CSV files.

Since it is a Google product, you can also create a table from files in Google Drive, as well as Google Sheets.

What’s your preferred way of creating a table in BigQuery?