Passing parameter works fine for SQL but not Hadoop SQL in Python string

Question

I am working on being able to pass date parameters into my SQL queries I am running in Python. The parameters work for for my regular SQL queries but are not working for my Hive SQL queries and I am not sure why? I am getting a "The SQL contains 0 parameter markers, but 1 parameters were supplied" error but only for Hive SQL. This code runs as intended:

    conn_sql = pyodbc.connect(f"DRIVER{{NetezzaSQL}};SERVER=server1;PORT=xxxx;DATABASE=SYSTEM;UID=xxxxxx;PWD=xxxxxx;")

# Define Cursor
cus_sql=conn_sql.cursor()

conn_hadoop = pyodbc.connect("DSN=Hive_Connection", autocommit=True)
start_date = input("Enter start date: ")
end_date = input("Enter end date: ")

sql = """SELECT user_id
    FROM table
    WHERE DATE_TIME >= CAST(? AS varchar(25)) AND a.DATE_TIME < CAST(? AS varchar(25))"""
df = pd.read_sql(sql,conn_sql,params=[start_date,end_date])

The below code throws the 0 param error.

with pyodbc.connect("DSN=Hive_Connection", autocommit=True) as conn_hadoop:
    df = pd.read_sql("""SELECT user_id
    FROM table
    WHERE date_time >= ? and date_time < end_time
    """,conn_hadoop,params=[start_time,end_time])

I cannot figure out what is causing that. Do I need a different format for parameter passing for a Hive connection? I didn't see anything on that in my search but maybe I missed it.

Rishu Shrivastava · Accepted Answer · 2020-07-01T20:38:18.163

1

"The SQL contains 0 parameter markers, but 1 parameters were supplied" error

This error is primarily because hive is unable to detect any existence of parameters. The way to pass parameters in a hive query is using ${hiveconf:<param name>}. So basically your query in hive becomes as below:

with pyodbc.connect("DSN=Hive_Connection", autocommit=True) as conn_hadoop:
   df = pd.read_sql("""SELECT user_id
           FROM table
           WHERE date_time >= '${hiveconf:start_time}' and date_time < '${hiveconf:end_time}'
        """,conn_hadoop,params=[start_time,end_time])

Check this answer on How to set variables in HIVE scripts

edited Jul 01 '20 at 20:38

answered Jul 01 '20 at 20:07

Rishu Shrivastava

3,745
1
20
41

i saw this in some of the native Hive code. I tried using it in my SQL with and without single quotes around the hiveconf but the statement is still seeing 0 params. – Jeff R Jul 01 '20 at 20:24
1

Sorry my mistake. you would need to include the single quotes. Updated my answer. If it still doesn't work. You could try to update the parameters using string replace of sql query before executing it via pyodbc. – Rishu Shrivastava Jul 01 '20 at 20:38
yea i tried with single quotes, still no luck. Replacing the params in a SQL string with .format() prior to executing the query did work though. That was a very good idea, thank you. – Jeff R Jul 01 '20 at 20:51

score 0 · Answer 2 · answered Jul 01 '20 at 20:55

With Rishu's help found a work around using a SQL string and .format() to replace params before execution.

sql = """SELECT user_id
    FROM table
    WHERE date_time >= '{}' and date_time < '{}'
    """.format(start_date,end_date)

with pyodbc.connect("DSN=Hive_Connection", autocommit=True) as conn_hadoop:
    df = pd.read_sql(sql,conn_hadoop)

Passing parameter works fine for SQL but not Hadoop SQL in Python string

2 Answers2