Function sql

python/pathway/internals/sql/__init__.py:7–86

r''' Run a SQL query on Pathway Live Data Framework tables. Args: query: the SQL query to execute. kwargs: the association name: table used for the execution of the SQL query. \ Each name:table pair links a Pathway Live Data Framework table to a table name used i

(query: str, **kwargs: table.Table)

@check_arg_types
def sql(query: str, **kwargs: table.Table) -> table.Table:
    r'''
    Run a SQL query on Pathway Live Data Framework tables.

    Args:
        query: the SQL query to execute.
        kwargs: the association name: table used for the execution of the SQL query. \
            Each name:table pair links a Pathway Live Data Framework table to a table name used in the SQL query.

    Example:

    >>> import pathway as pw
    >>> t = pw.debug.table_from_markdown(
    ...     """
    ...       A  | B
    ...       1  | 2
    ...       4  | 3
    ...       4  | 7
    ...     """
    ... )
    >>> ret = pw.sql("SELECT * FROM tab WHERE A<B", tab=t)
    >>> pw.debug.compute_and_print(ret, include_id=False)
    A | B
    1 | 2
    4 | 7

    Supported SQL keywords and operations:
    SELECT, WHERE, boolean expressions, arithmetic operations, \
    GROUP BY, HAVING, AS (alias), UNION, INTERSECTION, JOIN, and WITH.

    Table and column names are case-sensitive.

    Specificities of the Pathway Live Data Framework:
    - `id` is a reserved key word for columns, every Pathway Live Data Framework table has a special column \
    `id`. This column is not captured by `*` expressions in SQL.
    - Order of columns might not be preserved with respect to SELECT query.
    - Pathway Live Data Framework reducers (pw.count, pw.sum, etc.) aggregate over None values, while SQL \
    aggregation functions (COUNT, SUM, etc.) skip NULL values.
    - UNION requires matching column names.
    - INTERSECT requires matching column names.

    Limited support:
    - Subqueries are supported but fragile -- they depend on a set of query rewriting routines \
    from the `sqlglot <https://github.com/tobymao/sqlglot>`_ library.
    - Additionally, using the `id` column in subqueries is fragile.
    - LIKE, ANY, ALL, EXISTS are not supported, or only supported in a very weak state.

    Unsupported operations:
    - ordering operations: ORDER BY, LIMIT, SELECT TOP
    - INSERT INTO (Pathway Live Data Framework tables are immutable)
    - The Pathway Live Data Framework does not support anonymous columns: they might work but
      we do not guarantee their behavior.
    - INTERSECT does not support INTERSECT ALL.
    - COALESCE, IFNULL are not supported.
    - FULL JOIN and NATURAL JOIN are not supported.
    - CAST is not supported

    ''&#x27;