Handling newline character while executing Bigquery cmd via Python

user_405396 0
Viewed 55

I have sql query in a string variable that I am executing via Python. The problem I am facing is that I can't pass '\n' in the string variable. When using CHR(13) || CHR(10) I get following error -

Argument 2 to STRING_AGG must be a literal or query parameter

Query -

sql_stmt="""
    SELECT 
    STRING_AGG(COLUMN_NAME || ' ' || DATA_TYPE || ',' , CHR(13)) AS COLUMN_LIST
    FROM  `prj_name.dataset_name.INFORMATION_SCHEMA.COLUMNS`
"""

I am then using bigquery to execute the above statement using the client.query(sql_stmt) command. If I pass '\n' as command query is failing as string is getting split into two parts and failing.

Can someone tell me how I can handle this situation?

google-bigquery python
2 Answers

Try with sql_stmt=r"""...""" (note the r) and using \n instead of CHR(13)

user_147640380

As @Jaytiger mentined you can use '\\n' instead of CHR(13).

from google.cloud import bigquery

client = bigquery.Client(project="prj_name")

query = """
SELECT  STRING_AGG(COLUMN_NAME || ' ' || DATA_TYPE || ',' , '\\n') AS COLUMN_LIST
FROM  `prj_name.dataset_name.INFORMATION_SCHEMA.COLUMNS`
"""

job = client.query(query)
res = job.result()
for r in res:
    print(r.COLUMN_LIST)
user_75864290
Related