CODEWITHSUNDEEP
google-bigquery
Ben P
Ben P

Reputation: 3369

Creating a table from a BigQuery query, with field descriptions

I am using BigQuery DDL to create tables from queries, for example:

CREATE OR REPLACE TABLE `test.bens_test_table` AS
SELECT "test data" AS my_field

When creating tables in this way there are no descriptions on the field in the BigQuery UI:

enter image description here

I would like to add field and table descriptions when creating a table. I have been reading through the documentation and I can create an empty table with field and table descriptions:

CREATE OR REPLACE TABLE `test.bens_test_table`
(my_field STRING OPTIONS(description="A description."),
my_int INT64 OPTIONS(description="A number field.")
)
OPTIONS(
  description="Hello, this is my test table.", 
  friendly_name="My friendly table"
)

However when I try to combine these two to create a table from a query, I get an error when trying to add in the descriptions:

CREATE OR REPLACE TABLE `test.bens_test_table` AS
(SELECT "test data" AS my_field)
OPTIONS(
  description="Hello, this is my test table.", 
  friendly_name="Ben is cool"
)

Can this be done using the BigQuery UI?

Upvotes: 0

Views: 4292

Answers (1)

Javier Roger
Javier Roger

Reputation: 279

Change the order of the OPTIONS and the AS blocks. Check the docs for more query syntax information.

CREATE OR REPLACE TABLE `test.bens_test_table`
(my_field STRING OPTIONS(description="A description."),
my_int INT64 OPTIONS(description="A number field.")
)
OPTIONS(
  description="Hello, this is my test table.", 
  friendly_name="Ben is cool"
)
AS (SELECT "test data" AS my_field, 1 AS my_int)

Upvotes: 3

Related Questions