Error executing a query that has snowflake variables

[srod-mckesson]

Hi, community!

I have a SQL query saved in a file (e.g., file.sql).
My query begins by setting variables (see below).
I import this file using Python and execute it using Ibis with a Snowflake backend.
However, I think reference to the variable (e.g., $var1 ) breaks something, and I don't know how to fix it :(

Any thoughts or assistance would be appreciated.

PS. I've removed the variables in the SQL query itself and hardcoded their values, and this runs seamlessly in Python with Ibis but I'd like to have the variables run with Ibis too for easier code maintenance.

# error
Traceback (most recent call last):
  File "root\scripts\snowflake_io.py", line 75, in <module>
    df_raw = con.sql(query).to_pandas()
             ^^^^^^^^^^^^^^
  File "root\.venv\Lib\site-packages\ibis\backends\sql\__init__.py", line 179, in sql
    schema = self._get_schema_using_query(query)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "root\.venv\Lib\site-packages\ibis\backends\snowflake\__init__.py", line 600, in _get_schema_using_query
    with self._safe_raw_sql(sg.parse_one(query, read=dialect).limit(0)) as cur:
                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'Set' object has no attribute 'limit'

# SQL
set var1 = 10;
set var2 = 20;

select $var1

# import the file
with open(here(path, sql_file), "r", encoding="utf-8") as q:
    query = q.read()

    # read/ execute query
    df_raw = con.sql(query).to_pandas()
    print("successfully executed query")

    # clean names
    df_raw = clean_names(df_raw)
    print("used pyjanitor clean_names to clean up col headers")

    # export
    df_raw.to_parquet(tmp_out)
    print(f"data has been written to {tmp_out} as parquet")