BigQuery: create view with comments added to the top of the query

Question

I want to create a view in BigQuery that also shows comments such as the author, date created etc.

But if I try this in the UI the comments are left out.
Is there a way to do this in the BigQuery UI?
Or are there other ways using bq client or python, or ...?

So for example if I run this:

CREATE OR REPLACE VIEW `my_project_id.my_dataset.my_view_name`
AS
-- this is my important comment. This will be a long and extensive comment.
SELECT 1 as column_a
;

BigQuery will not show the comments in the view when the UI is used to create that view:

Answer 1

Since you are doing this in SQL, not via Python, there are 3 (maybe more) options:

1. Create a CTE that only exists to host the comment

CREATE OR REPLACE VIEW `my_project_id.my_dataset.my_view_name`
AS

WITH comment AS (
    -- this is my important comment. This will be a long and extensive comment.
    SELECT NULL
)

SELECT 1 as column_a
;

2. Add the comment block after WITH from a CTE

Since the comment block exists after the first function (in this case, WITH, it retains the comment block.

CREATE OR REPLACE VIEW `my_project_id.my_dataset.my_view_name`
AS

WITH 
-- this is my important comment. This will be a long and extensive comment.

    dummyblock AS (
        SELECT NULL
    )

SELECT 1 as column_a
;

3. Put the comment in the dedicated Description field

Note that the text is left aligned, so it does not have indentation in the description field.

Also note that the whole block is surrounded with triple quotes, so that it accepts multi-line descriptions. Without this BigQuery will report errors unless the text is all on a single line.

CREATE OR REPLACE VIEW `my_project_id.my_dataset.my_view_name`
    OPTIONS(
        description=
"""
/*
some long and detailed comment
*/
"""
    ) AS (

SELECT 1 as column_a
)

Answer 2

With the python API you can do as follows:

from google.cloud import bigquery

bq_client = bigquery.Client()

view_id = "my_project_id.my_dataset.my_view_name"
view = bigquery.Table(view_id)

query = """
-- this is my important comment. This will be a long and extensive comment.
SELECT 1 as column_a
"""

view.view_use_legacy_sql = False
view.view_query = query

# if your view already exists
bq_client.delete_table(view)

# your query will now show the comment at the top
bq_client.create_table(view)

This results in the following view:

See also: https://cloud.google.com/bigquery/docs/views#python

Answer 3

• First create the view without the comment.

• Then edit the sql of that view you just created and add the comments then.

See screenshots below.

Choose Edit Query:

Choose Save view:

Answer 4

Bigquery is skipping the "---- this is my important comment. This will be a long and extensive comment." because it is treated as a comment on the SQL query and it is not treated as a separate string to be included in the creation of view.

Another option is to use bq command as shown below.

bq mk \
--use_legacy_sql=false \
--expiration 3600 \
--description "This is my view" \
--label organization:development \
--view \
'-- this is my important comment. This will be a long and extensive comment.
SELECT 1 as column_a ' \
your-dataset.your-view

My sample output: