CODEWITHSUNDEEP
jsongoogle-bigquery
719016
719016

Reputation: 10431

bigquery create table from json definition gives STORAGE_FORMAT_UNSPECIFIED error

I want to create a table by cloning the schema of an existing table, editing it by adding some columns, renaming others.

What I did is:

Find the schema of the table to clone:

bq show --format=json $dataset.$from_table | jq -c .schema

Edit it with some scripting, save as a file, e.g. schema.json (here simplified):

schema.json

{"fields":[{"mode":"NULLABLE","name":"project_name","type":"STRING"},
{"mode":"NULLABLE","name":"sample_name","type":"STRING"}]}

Then attempting to create the new table with the command below:

bq mk --table --external_table_definition=schema.json test- 
project1:dataset1.table_v1_2_2

But I am getting this error:

BigQuery error in mk operation: Unsupported storage format for external data: STORAGE_FORMAT_UNSPECIFIED

I just want this to be another table of the same type I have in the system, which I believe is Location "Google Cloud BigQuery".

Any ideas?

Upvotes: 0

Views: 1605

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

The problem is that you are using the external_table_definition flag, which is only relevant if you are creating an external table over files on GCS or Drive for example. A much easier way to go about creating the new table is to use a CREATE TABLE ... AS SELECT ... statement. As an example, suppose that I have a table T1 with columns and types

foo: INT64
bar: STRING
baz: BOOL

I want to create a new table that renames bar and changes its type, and with the addition of a column named id. I can run a query like this:

CREATE TABLE dataset.T2 AS
SELECT
  foo,
  CAST(bar AS TIMESTAMP) AS fizz,
  baz,
  GENERATE_UUID() AS id
FROM dataset.T1

If you just want to clone and update the schema without incurring any cost or copying the data, you can use LIMIT 0, e.g.:

CREATE TABLE dataset.T2 AS
SELECT
  foo,
  CAST(bar AS TIMESTAMP) AS fizz,
  baz,
  GENERATE_UUID() AS id
FROM dataset.T1
LIMIT 0

Now you'll have a new, empty table with the desired schema.

Upvotes: 2

Related Questions