How to alter datatype of a column in BigQuery

Question

I'm trying to change the datatype for a column in my bigquery table from INT64 to STRING with the condition it's not NULL. When I type:

ALTER TABLE table_name ALTER COLUMN id STRING NOT NULL

I get an error

Syntax error: Expected keyword DROP or keyword SET but got identifier "STRING"

How should I resolve this?

Newspaperwoman

Answer 1

Despite the fact that you have got the error due to not using SET:

ALTER TABLE  table_name
ALTER COLUMN  id SET DATA TYPE STRING

but anyway, unfortunately, it's not possible to alter from INT64 to STRING directly. What you can do is create a new table using

CAST(id AS STRING) id

Doubletalk

Answer 2

It is unsupported to change a column's data type at the moment.

Take a look at the official documentation. It explains 2 ways to manually change a column's data type. For the record:

• Using a SQL query: choose this option if you are more concerned about simplicity and ease of use, and you are less concerned about costs.
• Recreating the table: choose this option if you are more concerned about costs, and you are less concerned about simplicity and ease of use.

Acetylate

Answer 3

A more straightforward way to achieve this would be:

1. to create a new column with the correct type
2. fill it with the old column values casted into the right type
3. delete the old column
4. rename the new column to the original name

-- Step 1
ALTER TABLE table_name
ADD COLUMN id_b STRING;

-- Step 2
UPDATE table_name
SET id_B = CAST(id AS STRING)
WHERE TRUE;

-- Step 3
ALTER TABLE table_name
DROP COLUMN id;

-- Step 4
ALTER TABLE table_name
RENAME COLUMN id_B TO id;

Driven

Answer 4

Coerce a column's data type

To change a column's data type into a coercible type, use the ALTER COLUMN SET DATA TYPE DDL statement. The following example creates a table with a column of type INT64, then updates the type to NUMERIC:

Syntax: CREATE TABLE mydataset.mytable(c1 INT64);

Alter syntax:

ALTER TABLE mydataset.mytable

ALTER COLUMN c1 SET DATA TYPE NUMERIC;

---

Cast a column's data type To change a column's data type into a castable type, use a SQL query to select the table data, cast the relevant column, and overwrite the table. Casting and overwriting is not recommended for very large tables because it requires a full table scan.

The following example shows a SQL query that selects all the data from column_two and column_three in mydataset.mytable and casts column_one from DATE to STRING. The query result is used to overwrite the existing table. The overwritten table stores column_one as a STRING data type.

1. Go to BigQuery

2. In the Query editor, enter the following query to select all of the data from column_two and column_three in mydataset.mytable and to cast column_one from DATE to STRING. The query uses an alias to cast column_one with the same name. mydataset.mytable is in your default project.

3. SELECT

   column_two,

   column_three,

   CAST(column_one AS STRING) AS column_one

   FROM

   mydataset.mytable;

4.Click More and select Query settings.

5.In the Destination section, do the following:

6.Select Set a destination table for query results.

7.For Project name, leave the value set to your default project. This is the project that contains mydataset.mytable.

8.For Dataset, choose dataset.

9.In the Table Id field, enter mytable. 10.For Destination table write preference, select Overwrite table. This option overwrites mytable using the query results.

11.Optionally, choose your data's location.

12.To update the settings, click Save.

13.Click Run.

14. Refresh, When the query job completes, the data type of column_one is STRING.

For more info: https://cloud.google.com/bigquery/docs/managing-table-schemas#console_3

Raby