It is an interesting request. Maybe we can just inject the df table into the SQL statements? But one argument is if user wants to use SQL, why they don't just use SQL with BQ? @tswast @TrevorBergeron WDYT?

I was just talking with @shanecglass and @aliciawilliams about the potential for a similar feature.

I was thinking of providing a SQL engine to our equivalent of https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.eval.html, but mimicking the pandasql interface could be even more natural. Looks like it's MIT license (https://github.com/yhat/pandasql/blob/master/LICENSE.txt) so we could pull some of that into third_party if needed.

As an alternative, I've just mailed #1293 which will add a bigframes.bigquery.sql_scalar() method that takes a SQL format string and a list of Series objects.

Usage example:

import bigframes.pandas as bpd
import bigframes.bigquery as bbq
import pandas as pd
import pyarrow as pa

s = bpd.Series(["1.5", "2.5", "3.5"])
s = s.astype(pd.ArrowDtype(pa.decimal128(38, 9)))
bbq.sql_scalar("ROUND({0}, 0, 'ROUND_HALF_EVEN')", [s])

Output:

0    2.000000000
1    2.000000000
2    4.000000000
dtype: decimal128(38, 9)[pyarrow]

I'm hoping to have this feature in our next release. I suspect this will provide a sufficient "escape hatch" for most, but not all, use cases that a pandasql-like API would provide. @NiloFreitas would work for you?

Aside: re JSON_EXTRACT, we also have provided such functionality in Python via the bigframes.bigquery.json_extract(series, json_path) function, which has already been released.

Usage example:

import bigframes.pandas as bpd
import bigframes.bigquery as bbq

s = bpd.Series(['{"class": {"students": [{"id": 5}, {"id": 12}]}}'])
bbq.json_extract(s, json_path="$.class")

Output:

0    {"students":[{"id":5},{"id":12}]}
dtype: string

Somewhat inspired by: https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.SparkSession.sql.html#pyspark.sql.SparkSession.sql

I'm thinking about something like a tables={df1: bf_df, df2: pandas_df} argument to read_gbq. If set, we parse the SQL with SQLGlot and look to see if any table IDs match the ones listed in the dictionary.

In the first iteration, we can call to_gbq() on each referenced object. Maybe add a copy=False argument in case the df already refers to a table.