Understanding Column Mode in BigQuery and How to Modify It?

In BigQuery, each column in a table has a mode that defines how values are stored and whether they can be null. The three modes available are:

 

·      NULLABLE: The column can contain NULL values.

·      REQUIRED: The column cannot have NULL values (must always contain a value).

·      REPEATED: The column can store an array of values.

 

Changing column modes is sometimes necessary when modifying schemas to allow or restrict NULL values. However, in BigQuery, mode changes have limitations.

 

Changing Column Mode in BigQuery

You can change a column mode from REQUIRED to NULLABLE, but you cannot change a column from NULLABLE to REQUIRED directly.

 

To change a column from REQUIRED to NULLABLE, use the following SQL command:

ALTER TABLE project.dataset.table_name 
ALTER COLUMN column_name DROP NOT NULL;

This command allows NULL values in a column that was previously REQUIRED.

 

For example, I have employees table, where it has four fields (id, name, age and mail) and all are mandatory.

 

You can change the mode from REQUIRED to NULLABLE in two ways

 

Approach 1: Using Alter statement 

ALTER TABLE myProject.myDataset.employees 
ALTER COLUMN age DROP NOT NULL;

 Upon successful execution of the statement, you can the mode for the column age is set to NULLABLE. 

 

Approach 2: Using User interface.

 

Click on “Edit schema” button on employees schema section.

 

Click on the options next to the Mode section for the field you want to modify, then select the desired mode. For example, I changed the mode of the mail field to NULLABLE and saved the changes.

 

Now you can observe that the mode for mail field is set to NULLABLE. 

 

However, if you need to change from NULLABLE to REQUIRED, BigQuery does not support this operation directly. A workaround is to:

 

·      Create a new table with the desired schema (where the column is REQUIRED).

·      Copy data from the existing table to the new table, ensuring there are no NULL values.

 

Previous                                                    Next                                                    Home