BigQuery- Mystery "unclosed string literal" error from execute immediate statement

user_7311091 0
Viewed 2914

I'm working on a script that creates a table while setting the table description metadata. Eventually this will take variable table names and table descriptions as input, hence the need for the execute immediate statement. For now to keep things cleaner I'm just setting the variables as static strings. I would like the table description to have multiple new lines, as I will be including a few different pieces of information there that I'd like not to be all on one line for legibility reasons.

My code (below) looks correct to me, however when I run it, I'm getting an error:

Invalid EXECUTE IMMEDIATE sql string ` CREATE OR REPLACE TABLE `project.dataset.example_table` OPTIONS ( description = ' This is a table description. It uses multiple lines. ' ) AS ( Select 1 ) `, Syntax error: Unclosed string literal at [12:19]`

I'm really not seeing where this unclosed string literal is. If I copy the SQL string from the error message and run it, it works just fine. The only thing I can think of is if it might have something to do with a newline character in the table_description variable?

Declare table_name string;
Declare table_description string;

Set table_name = 'project.dataset.example_table';
Set table_description =
"""
This is a table description.

It uses multiple lines.
""";

EXECUTE IMMEDIATE """
CREATE OR REPLACE TABLE `""" || table_name || """`
OPTIONS (
    description = '""" || table_description || """'
) AS (
Select 1
)
""";
google-bigquery
1 Answers

Below is fixed version - just compare to see few "fixes"

Declare table_name string;
Declare table_description string;

Set table_name = 'project.dataset.example_table';
Set table_description =
"""
This is a table description.

It uses multiple lines.
""";

EXECUTE IMMEDIATE   """
CREATE OR REPLACE TABLE `""" || table_name || """`
OPTIONS (
    description = '''""" || table_description || """'''
) AS (
Select 1 as col_name
)
""";
user_52219440
Related