Changing Column Data Types in BigQuery: A Step-by-Step Guide

Modifying the data type of a column in BigQuery requires understanding the concepts of coercible and castable data types.

 

·      A coercible data type can be automatically converted into another type without loss of information, such as converting an INT64 to a NUMERIC.

 

·      A castable data type requires explicit conversion and may involve potential data loss, like converting a STRING to a DATE.

 

1. Changing to a Coercible Data Type

For coercible data types, you can use the ALTER TABLE statement

ALTER TABLE `project.dataset.table`
ALTER COLUMN column_name
SET DATA TYPE new_data_type;

 

 For instance, in the employee table, the employee_id column is of type INTEGER. It can be converted to BIGNUMERIC using the following statement: 

ALTER TABLE `project.dataset.employee`
ALTER COLUMN employee_id
SET DATA TYPE BIGNUMERIC;

After executing the statement, refresh the schema, you can observe that the type of column employee_id is converted to BIGNUMERIC.  

 

2. Changing to a Castable Data Type

For castable data types, you need to create a new table with the desired schema and cast the column accordingly:

 

Syntax

 

CREATE TABLE `project.dataset.new_table` AS
SELECT
  column1,
  column2,
  CAST(column_to_change AS new_data_type) AS column_to_change,
  column4
FROM
  `project.dataset.old_table`;

The following SQL snippet creates the employee_new table in project.dataset by selecting specific columns from employee, while converting the employee_id column to an INTEGER type.  

 

CREATE TABLE `project.dataset.employee_new` AS
SELECT
  first_name,
  last_name,
  CAST(employee_id AS INTEGER) AS employee_id,
  email
FROM
  `project.dataset.employee`;

This operation ensures that the employee_new table retains the selected columns while transforming employee_id into an integer data type.

 

2.1 How to change the column type in existing table only?

Write the SELECT Query:

SELECT  
    CAST(employee_id AS INTEGER) AS employee_id,  
    first_name,  
    last_name,  
    email,  
    hire_date,  
    department,  
    salary,  
    my_age  
FROM `project.dataset.employee`  
WHERE true;

 

Configure Query Settings:

Click on More → Query Settings

 

In the Query Settings form:

·      Set Destination to "Set a destination table for query results"

·      Choose Destination table write preference as "Overwrite table"

·      Choose the same dataset and table as destination table.

 

Click the Save button to apply settings.

 

Run the query to store the results in the specified destination table.

 

Now go back to the schema tab, you can observe that the employee_id column data type is changed to INTEGER.

 

 

Previous                                                    Next                                                    Home