How to update datatype in Big query table?

0
votes

I have just started my POC in Big Query. I would like to know how to update/modify the column definition/schema in an existing table.

bq mk -t market.cust custid:integer,grp:integer,odate:string bq update -t market.cust custid:string,grp:integer,odate:string

**Error:
spanda2040@instance-3:~/data$ bq update -t market.cust custid:string,grp:integer,odate:string
BigQuery error in update operation: Provided Schema does not match Table arboreal-height-175822:market.cust. Field custid has changed type from INTEGER to STRING**

Table Schema:

Last modified Schema Total Rows Total Bytes Expiration Time Partitioning Labels

02 Oct 13:38:29 |- custid: integer 0 0 |- grp: integer |- odate: string

2
google-bigquery
You have a low rate. Important on SO - you can vote on answer. Vote up answers that are helpful. ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! - Mikhail Berlyant

2 Answers

1
votes

Using SELECT will incur some co$t as you will need to scan the whole table

Below is super simple approach that costs $0.00

  1. Export your existing Table into GCS
  2. Load from GCS to new Table with same schema as your original table with exception of that column to be of fixed type
  3. You done. $0 cost!!

I just did this to sanity-check and it works as a charm

when you will get comfortable with this approach - you can even load into the same table - with WRITE_TRUNCATE for writeDisposition property

0
votes

Run a query that replaces the existing table with the results of a query that casts the data as you'd like:

#standardSQL
SELECT a, b, c, CAST(d as INT64) AS d
FROM `p.t.d`

Update, based on comment - If I want to change an integer to string:

#standardSQL
SELECT a, b, c, CAST(d as STRING) AS d
FROM `p.t.d`