Is it possible to pass query parameters? (`:param` or `?`)

[shner-elmo]

Hey, I'm trying to port a query from duckdb to datafusion using the Python connector, the issue I'm facing, is adding query parameters.
Is there currently a way that I can pass query parameters ?

import datafusion
import pyarrow.dataset as ds

dataset = ds.dataset(...)

ctx = datafusion.SessionContext()
ctx.register_dataset('dataset', dataset)
table = ctx.sql("SELECT * FROM dataset WHERE col BETWEEN ? AND ?", parameters=[1, 10]).to_arrow_table()
## TypeError: SessionContext.sql() got an unexpected keyword argument 'parameters'

[MrPowers]

Yea, parameterized queries would be awesome, especially syntax like this:

ctx.sql(
    "select country, count(*) as num_ppl from {person_df} group by country",
    person_df=person_df,
).show()

[timsaucer]

I don't fully understand what is needed here. Can you not just do

ctx.sql(
    f"select country, count(*) as num_ppl from {person_df} group by country"
).show()

[MrPowers]

@timsaucer - In this example, person_df is a DataFrame object, not a table name. This saves the user from having to register the table (the engine just does this under the hood). It was recently added to Spark and is a surprisingly nice dev quality of life improvement!

[timsaucer]

Oh, interesting. As a pure dataframe user, I hadn't understood the request. I appreciate the clarification!

[shner-elmo]

Parametrized queries are even more important for when you want to do: WHERE col IN big_list_of_values.

A year later, I again need to choose a library for reading parquet files, but I had to settle with ducks because data fusion doesn't support this

[timsaucer]

I just tried a quick proof of concept. To get this to work, I did need a name for the table. Would the following be an acceptable solution?

from datafusion import SessionContext
ctx = SessionContext()
ctx.register_parquet("customers", "examples/tpch/data/customer.parquet")
df_customer = ctx.table("customers")
ctx.sql("select c_custkey, c_name from {df}", df=df_customer)

[timsaucer]

I think that if we change all of the read_x like read_parquet over to call register_x and auto generate a table name, then we can support removing the register line and make this work. I tested with parquet with success.

[kylebarron]

Yea, parameterized queries would be awesome, especially syntax like this:

ctx.sql(
"select country, count(*) as num_ppl from {person_df} group by country",
person_df=person_df,
).show()

@MrPowers I think a cleaner API here could be to implement replacement scans like DuckDB #1244

[MrPowers]

@kylebarron - I prefer the Spark parameterized syntax because it's not reliant on local scope. I've fought with the DuckDB syntax in the past cause you can lose the scope when you move to a new function, for example.

BTW, it'd be cool if we could parameterize lots of different values, not just DataFrames. This would have been a better example:

from datetime import date

ctx.sql("""
    SELECT country, COUNT(*) as num_ppl 
    FROM {person_df} 
    WHERE date_column > {cutoff_date}
    GROUP BY country
""", 
person_df=person_df,
cutoff_date=date(2020, 1, 1)
).show()

The extra bonus point implementation would be if the user could add custom parameterization logic for their own objects!

[kylebarron]

I think parameterization is important, but I think most important for strings to avoid SQL injection.

I think Arrow parametrization could make sense, but I think it should be a second PR after.

WHERE date_column > {cutoff_date}

It's not clear to me that there's a 1:1 correspondence between a Python date and the correct SQL string. I think having arbitrary parameterization logic would be hard. (Or you could tell the user to implement __str__, but then we're back to string interpolation.

[MrPowers]

@kylebarron - yea, here's a blog post on how PySpark does the parameterization. Not all Python objects are parameterizable, just the common ones used in SQL queries. You're right that the parameters need to get sanitized - thanks for calling that out!!

[kylebarron]

Should there be another issue dedicated to query sanitization? I'm not an expert on what needs to be done to achieve that

[timsaucer]

We have two issues and two PRs addressing this vs direct replacement.

#1244 requests replacement scans and #1247 addresses it.

#964 addresses this issue and was brought up again today at the community meeting.

@MrPowers and @kylebarron as the two people requesting these features, can you give a better sense of how strongly you feel about us going one direction or the other? I don't think we should try to do both.

Tagging @kosiew who has done a lot of work on his PR.