6

I made a mistake of specifying a field as integer instead of float. I found that I am not able to make correction a field once the table is created. I have to delete and re-create the table again to make things right. Does anyone know of a better way to modify a field after a table is created? Thanks.

Kim
  • 315
  • 1
  • 5
  • 10
  • 1
    After much trying, I am rather convinced that the only way is to delete and re-create. – Kim Dec 21 '17 at 06:29
  • This comment may irrelevant to you scenario. If you import using BigQuery WebUI you can click history and change column definition from the screen, then, re-import it. But if you are using `bq load` command with `--autodetect`, I cannot find the way to change schema definition. I often add `[]` after `bq load` command to indicate its schema. But once imported, I cannot find the way to edit table schema. – Fony Lew Jan 09 '18 at 06:56
  • Another option to consider are using Dataprep or Cloud DataFlow to transform the table. – Fony Lew Jan 09 '18 at 06:58
  • Looks you can not change the schema after creation of the table, but you can rename the columns: https://stackoverflow.com/questions/42395612/update-big-query-table-schema – JL-HaiNan Jan 09 '18 at 20:33

2 Answers2

3

It looks that you can not change the schema after creation of the table, but you can rename the columns by looking at the suggestions at this SO post: https://stackoverflow.com/questions/42395612/update-big-query-table-schema

JL-HaiNan
  • 146
  • 5
2

The BigQuery Doesn't allow us to modify the table schema directly. Although we can add new columns using Web UI. There are two way we can modify the Schemas after creation both of these approaches need us to recreate the table.

Method 1 Using SQL:

Write a SQL query in Query editor to Select all the required columns except the ones you want to modify.

Go to query Setting

Set Destination table having same as the Original one

Select write Preference as "Overwrite Table"

Save and run query.

Method 2 Using CLI This is an elegant way to modify the existing Schema.

Run bq show --schema --format=prettyjson project_id:dataset.table > schema_file where you need to specify project, dataset and table path.

Define "schema_file" having .json format in above command.

Modify the Mode or Name in the Json file

Update the existing table using bq update project_id:dataset.table schema

c0der512
  • 121
  • 5