pyflink.dataframe.sql#

sql(query: str, *, auto_bind: bool = True, **bindings: Union[pyflink.dataframe.dataframe.DataFrame, pyflink.dataframe.udf.DataFrameFunctionWrapper]) → pyflink.dataframe.dataframe.DataFrame[source]#

Run a SQL SELECT query, returning a DataFrame.

In the SELECT query, DataFrame values can be used as temporary views, and DataFrameFunctionWrapper values can be used as temporary system functions. These temporary objects are registered only while this function runs and are dropped before the result is returned.

You can make them visible to SQL in two ways:

Auto-bind: with auto_bind=True (the default), sql scans the caller’s local and global variables for DataFrame and DataFrameFunctionWrapper instances. It registers each one under its Python variable name.
Explicit bindings: pass keyword arguments to choose the SQL name yourself, for example pf.sql("SELECT * FROM source", source=df) or pf.sql("SELECT f(a) FROM source", source=df, f=my_udf).

Notes on the differences between auto-bind and explicit bindings:

Auto-bind is best-effort. It warns and skips invalid SQL names, existing tables/views, and existing functions. It logs and skips DataFrames from a different TableEnvironment. If an auto-bind candidate cannot be registered but is already reachable through an explicit alias, the failed auto-bind attempt is skipped silently.
Explicit bindings are strict. Invalid SQL names, unsupported binding values, temporary table/view conflicts, non-built-in function conflicts, or explicitly bound DataFrames from different TableEnvironment instances raise ValueError.
Auto-bind never shadows existing SQL objects, including permanent catalog tables/views and built-in/system functions. Use explicit bindings when you intentionally want to shadow a permanent catalog table/view or a built-in/system function.

Explicit bindings take precedence over auto-bind when they use the same name.

Parameters

query – A SQL SELECT statement. Other statement kinds (INSERT, DDL) are not supported.
auto_bind – If True (default), auto-detect DataFrame and DataFrameFunctionWrapper instances in the caller’s globals and locals. See above.
**bindings – Explicit alias -> DataFrame or DataFrameFunctionWrapper mappings. Always registered, even when auto_bind is False. Take precedence over auto-bind on name collision.

Returns

A new DataFrame backed by the query result.

Note

This function is not safe under concurrent calls against the same TableEnvironment: register / sql_query / drop is not an atomic sequence. Don’t call it from multiple threads sharing one t_env.

Example

>>> import pyflink.dataframe as pf
>>> df1 = pf.from_dict({"a": [1, 2, 3], "b": ["x", "y", "z"]})
>>> df2 = pf.from_dict({"a": [1, 2, 3], "c": ["p", "q", "r"]})
>>> joined = pf.sql("SELECT * FROM df1 JOIN df2 ON df1.a = df2.a")

>>> @pf.udf
... def add_one(x: int) -> int:
...     return x + 1
>>> df = pf.from_dict({"a": [1, 2, 3]})
>>> incremented = pf.sql("SELECT add_one(a) FROM df")

>>> plus_one = pf.udf(lambda x: x + 1, return_dtype=pf.DataType.int64())
>>> incremented = pf.sql(
...     "SELECT f(a) FROM source",
...     auto_bind=False,
...     source=df,
...     f=plus_one,
... )