kaxil commented on code in PR #76799:
URL: https://github.com/apache/airflow/pull/76799#discussion_r4580818687
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
Review Comment:
Could this block come from a real run? Hand-written output drifts from what
the tool returns, and a reader can't tell which one they're looking at. A
`FunctionModel` driving the toolset against a Postgres table with comments
captures it without an LLM key.
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
Review Comment:
Could this name the version? common.ai still allows
`apache-airflow-providers-common-sql>=2.2.0`, so plenty of readers will land on
the fallback and need the number to know what to upgrade to.
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the result is summarized (see
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with
``comments_omitted``, and the
+agent gets them back by asking for the columns it needs with ``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. What that costs depends on the
dialect. Where the
+dialect does not implement ``get_multi_table_comment`` itself, SQLAlchemy's
default reads the
+comments one table at a time. Based on the dialects' reflection
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On
PostgreSQL it was a single
Review Comment:
"On PostgreSQL it was a single query when tried" has no source a reader can
check, and the paragraph just before says the Snowflake and BigQuery behaviour
was read from the dialects, not run. Could the page either state what was run
(dialect version, what was counted) or drop the claim? Pinning
`snowflake-sqlalchemy` 2.1.0 and `sqlalchemy-bigquery` 1.17.3 will also go
stale with the next release.
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
Review Comment:
"For the same reason" doesn't hold: the toolset swallows any exception from
`get_table_comment` (a permission error, say), not only a missing method or a
dialect without comments. Could it say what actually happens, that the error is
logged at debug and `get_schema` returns the columns without `table_comment`?
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the result is summarized (see
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with
``comments_omitted``, and the
+agent gets them back by asking for the columns it needs with ``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. What that costs depends on the
dialect. Where the
+dialect does not implement ``get_multi_table_comment`` itself, SQLAlchemy's
default reads the
+comments one table at a time. Based on the dialects' reflection
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On
PostgreSQL it was a single
+query when tried. On such databases leave ``include_table_comments`` off for
large schemas, or pass
+a custom ``search_backend`` that builds its catalog with one dialect-specific
query, for example
+against ``INFORMATION_SCHEMA``.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables``
returns tables without
+comments rather than reading them table by table. The default search backend
then has no comments
+to match, and a query whose words appear only in comments returns nothing;
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an
optional
+``limit`` (default 10, at most 50) and returns matching tables with their
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only
their number
+and a pointer to ``search_tables`` instead of the whole list. Without a
``search_backend``
+``list_tables`` is unchanged, however many tables there are.
+
+.. code-block:: python
+
+ from airflow.providers.common.ai.toolsets.sql import SQLToolset
+ from airflow.providers.common.ai.toolsets.sql_search import
KeywordTableSearchBackend
+
+ toolset = SQLToolset(
+ db_conn_id="snowflake_hq",
+ allowed_tables=["MODEL_CRM.SF_ASTRO_ORGS",
"MODEL_ASTRO.DEPLOYMENT_IMAGE_DETAILS"],
+ search_backend=KeywordTableSearchBackend(),
+ )
+
+``KeywordTableSearchBackend`` splits the query on whitespace and matches each
word,
+case-insensitively, as a substring of the table name and of the table's
comment; column
+comments are not searched. A word found in
+the name counts three times a word found in the comment; tables matching
nothing are
+dropped and ties are ordered by name. It introspects once per toolset
instance, then
+searches that result.
+
+Every result is checked against ``allowed_tables`` before the agent sees it,
whichever
+backend produced it, so a backend that knows about more tables than the agent
may use
+cannot widen access.
+
+**Plugging in another backend.** A backend is any object with a
Review Comment:
Could this be a real subsection with a short example? Besides making it
linkable: the page recommends a backend that "builds its catalog with one
dialect-specific query" for Snowflake and BigQuery, but the protocol hands a
backend only `get_catalog`, which is the per-table path. An example showing how
such a backend gets a connection (its own hook on the same `db_conn_id`?) would
make that advice usable.
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the result is summarized (see
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with
``comments_omitted``, and the
+agent gets them back by asking for the columns it needs with ``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. What that costs depends on the
dialect. Where the
+dialect does not implement ``get_multi_table_comment`` itself, SQLAlchemy's
default reads the
+comments one table at a time. Based on the dialects' reflection
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On
PostgreSQL it was a single
+query when tried. On such databases leave ``include_table_comments`` off for
large schemas, or pass
+a custom ``search_backend`` that builds its catalog with one dialect-specific
query, for example
+against ``INFORMATION_SCHEMA``.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables``
returns tables without
+comments rather than reading them table by table. The default search backend
then has no comments
+to match, and a query whose words appear only in comments returns nothing;
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an
optional
+``limit`` (default 10, at most 50) and returns matching tables with their
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only
their number
Review Comment:
Could the page show what the agent gets back here and from `search_tables`?
Neither shape appears anywhere on the page. A `{"table_count": ..., "hint":
...}` and a `{"tables": [...]}` sample from a real run would tell people
writing prompts or their own backend what to expect.
##########
providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py:
##########
@@ -280,6 +301,8 @@ def build_schema_result(
)
full: dict[str, Any] = {"columns": selected, "column_count": len(selected)}
+ if table_comment:
+ full["table_comment"] = truncate_comment(table_comment)
Review Comment:
On the size check a few lines down: `_size(full)` includes the comments, so
a table whose columns fit but whose comments overflow `max_result_bytes` drops
straight to the summary. The agent then loses the types and most column names
it would have had without comments. Would retrying the full list with column
comments stripped (and `comments_omitted` set) before falling back to the
summary work? That keeps the feature from making `get_schema` worse on tables
that used to fit.
##########
providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py:
##########
@@ -554,23 +623,106 @@ def add(schema: str | None, name: str, display: str) ->
None:
if self._include_default_schema:
for name in inspector.get_table_names(schema=self._schema):
add(self._schema, name, name)
+ return tables
+
+ def _list_tables(self) -> str:
+ tables = self._find_allowed_tables()
+ if self._search_backend is not None and len(tables) >
self._max_listed_tables:
+ return dumps_masked(
+ {
+ "table_count": len(tables),
+ "hint": (
+ f"There are {len(tables)} tables, too many to list
(the limit is "
+ f"{self._max_listed_tables}). Use
{self._build_tool_name('search_tables')} to find the ones you need."
+ ),
+ }
+ )
+ if not self._include_table_comments:
+ return dumps_masked([display for _, _, display in tables])
+ return dumps_masked(
+ [
+ {"name": name, **({"comment": comment} if comment else {})}
+ for name, comment in self._get_table_entries(tables)
+ ]
+ )
+
+ def _get_table_entries(self, tables: list[tuple[str | None, str, str]]) ->
list[TableEntry]:
+ """Pair each table's display name with its comment, capped, read once
per schema."""
+ inspector = self._get_inspector()
+ comments = {
+ table_schema: self._get_table_comments(inspector, table_schema)
+ for table_schema in {table_schema for table_schema, _, _ in tables}
+ }
+ return [
+ (display, truncate_comment(comment) if (comment :=
comments[table_schema].get(name)) else None)
+ for table_schema, name, display in tables
+ ]
- return dumps_masked(tables)
+ def _get_catalog(self) -> list[TableEntry]:
+ """Return the exposed tables with comments, introspected on first use
and kept for this toolset."""
+ if self._catalog is None:
+ self._catalog =
self._get_table_entries(self._find_allowed_tables())
+ return self._catalog
+
+ def _search_tables(self, query: str, limit: int | None) -> str:
+ if self._search_backend is None:
+ raise ValueError("search_tables requires a search_backend")
+ # The schema's maximum is only advice to the model; the cap has to
hold here.
+ limit = min(max(_DEFAULT_SEARCH_LIMIT if limit is None else limit, 1),
_MAX_SEARCH_LIMIT)
+ found = self._search_backend.search_tables(query, limit,
get_catalog=self._get_catalog)
+ tables: list[dict[str, str]] = []
+ for display, comment in found:
+ schema, table = self._split_table_identifier(display)
+ if not self._is_ref_allowed("", schema, table):
+ continue
+ tables.append({"name": display, **({"comment":
truncate_comment(comment)} if comment else {})})
+ if len(tables) == limit:
+ break
+ return dumps_masked({"tables": tables})
+
+ @staticmethod
+ def _get_table_comments(inspector: Inspector, schema: str | None) ->
dict[str, str]:
+ """Return ``{table name: comment}`` for a schema in one call; empty
when the dialect has none."""
+ # SQLAlchemy 1.4, which common-sql still supports, has no
get_multi_table_comment.
+ # Reading comments one table at a time instead would cost a query per
table, so
+ # on 1.4 the tables simply come back without comments.
+ if (get_comments := getattr(inspector, "get_multi_table_comment",
None)) is None:
+ return {}
+ try:
+ reflected = get_comments(schema=schema)
Review Comment:
`get_multi_table_comment` takes `filter_names`, and SQLAlchemy's per-table
fallback skips any table not in it. Passing the allowed names for this schema
would tie the Snowflake/BigQuery cost to `allowed_tables` instead of the whole
schema; today two allowed tables in a 2,000-table dataset still cost 2,000
calls on a dialect that relies on the default. The docs could then say
`allowed_tables` bounds it.
##########
providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py:
##########
@@ -554,23 +623,106 @@ def add(schema: str | None, name: str, display: str) ->
None:
if self._include_default_schema:
for name in inspector.get_table_names(schema=self._schema):
add(self._schema, name, name)
+ return tables
+
+ def _list_tables(self) -> str:
+ tables = self._find_allowed_tables()
+ if self._search_backend is not None and len(tables) >
self._max_listed_tables:
+ return dumps_masked(
+ {
+ "table_count": len(tables),
+ "hint": (
+ f"There are {len(tables)} tables, too many to list
(the limit is "
+ f"{self._max_listed_tables}). Use
{self._build_tool_name('search_tables')} to find the ones you need."
+ ),
+ }
+ )
+ if not self._include_table_comments:
Review Comment:
With `include_table_comments=True` and no `search_backend`, the list has no
size bound: each comment is capped at 200 characters but the total isn't tied
to `max_result_bytes`. The name-only list was already unbounded, so this can
stay as is, but the docs only mention the shared budget under `get_schema`.
Worth a sentence in the `include_table_comments` parameter entry.
##########
providers/common/ai/src/airflow/providers/common/ai/toolsets/sql_search.py:
##########
@@ -0,0 +1,82 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+"""Table search for
:class:`~airflow.providers.common.ai.toolsets.sql.SQLToolset`."""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING, Protocol
+
+if TYPE_CHECKING:
+ from collections.abc import Callable, Sequence
+
+#: A table as the agent sees it: ``(qualified_name, comment)``. The name is
exactly what
+#: ``list_tables`` would return -- bare for the default schema,
``SCHEMA.TABLE`` otherwise --
+#: so it round-trips to ``get_schema``.
+TableEntry = tuple[str, str | None]
+
+
+class SQLTableSearchBackend(Protocol):
+ """
+ Finds tables relevant to a free-text query, for the ``search_tables`` tool.
+
+ The toolset filters whatever a backend returns through ``allowed_tables``
before the agent
+ sees it, so a backend that knows about more tables than the agent may use
cannot widen access.
+ It can still waste its own ``limit``, so an index built for the agent
should cover only the
+ tables the agent may use.
+ """
+
+ def search_tables(
+ self, query: str, limit: int, *, get_catalog: Callable[[],
Sequence[TableEntry]]
+ ) -> Sequence[TableEntry]:
+ """
+ Return up to ``limit`` tables, best match first.
+
+ :param query: The agent's free-text query.
+ :param limit: Maximum number of results wanted.
+ :param get_catalog: Lists the tables the toolset exposes, introspected
with the same
+ connection the agent queries with and cached for the toolset's
lifetime. A backend
+ with its own index (a vector store, a search service) ignores it.
+ """
+ ...
+
+
+class KeywordTableSearchBackend:
+ """
+ Default backend: case-insensitive substring match of each query word
against table names and comments.
+
+ Query words are split on whitespace. A table's score is the number of
words found in its name
+ weighted at ``_NAME_WEIGHT`` plus the number found in its comment; tables
scoring zero are
+ dropped, and ties are broken by name.
+ """
+
+ _NAME_WEIGHT = 3
+
+ def search_tables(
+ self, query: str, limit: int, *, get_catalog: Callable[[],
Sequence[TableEntry]]
+ ) -> list[TableEntry]:
+ if not (words := query.casefold().split()):
+ return []
+ scored: list[tuple[int, str, TableEntry]] = []
+ for entry in get_catalog():
+ name, comment = entry
+ name_text = name.casefold()
+ comment_text = (comment or "").casefold()
+ score = sum(self._NAME_WEIGHT * (word in name_text) + (word in
comment_text) for word in words)
Review Comment:
`name` is the display name, so for a non-default schema it is
`SCHEMA.TABLE`. A query word like `crm` then matches every table in `MODEL_CRM`
with the name weight of 3, which the docs describe as matching the "table
name". Should this match the table part only, or should the docs say the schema
is included?
##########
providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py:
##########
@@ -108,6 +113,13 @@ def _dumps(payload: Any) -> str:
return dumps_masked(payload, **_DUMP_KWARGS)
+def truncate_comment(comment: str, *, max_chars: int =
DEFAULT_MAX_COMMENT_CHARS) -> str:
Review Comment:
The cut happens before `dumps_masked`, and masking matches whole registered
secrets, so a secret that straddles the 200-character cut leaves its prefix
unmasked. Narrow, since it needs a credential in a comment, but masking first
and cutting after would avoid it.
##########
providers/common/ai/docs/agent_security.rst:
##########
@@ -186,7 +186,8 @@ How ``allowed_tables`` is enforced
When ``allowed_tables`` is set it governs every tool, not just discovery:
-- ``list_tables`` and ``get_schema`` only reveal listed tables.
+- ``list_tables``, ``get_schema`` and, when a ``search_backend`` is set,
``search_tables``
+ only reveal listed tables.
Review Comment:
Could this page also carry the identity point from the new warning in
`sql.rst`? Someone reading the security page for what `allowed_tables`
guarantees won't learn that a standalone backend indexed with another role can
leak table names when `allowed_tables` is unset.
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the result is summarized (see
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with
``comments_omitted``, and the
+agent gets them back by asking for the columns it needs with ``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. What that costs depends on the
dialect. Where the
+dialect does not implement ``get_multi_table_comment`` itself, SQLAlchemy's
default reads the
+comments one table at a time. Based on the dialects' reflection
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On
PostgreSQL it was a single
+query when tried. On such databases leave ``include_table_comments`` off for
large schemas, or pass
+a custom ``search_backend`` that builds its catalog with one dialect-specific
query, for example
+against ``INFORMATION_SCHEMA``.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables``
returns tables without
+comments rather than reading them table by table. The default search backend
then has no comments
+to match, and a query whose words appear only in comments returns nothing;
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an
optional
+``limit`` (default 10, at most 50) and returns matching tables with their
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only
their number
+and a pointer to ``search_tables`` instead of the whole list. Without a
``search_backend``
+``list_tables`` is unchanged, however many tables there are.
+
+.. code-block:: python
+
+ from airflow.providers.common.ai.toolsets.sql import SQLToolset
+ from airflow.providers.common.ai.toolsets.sql_search import
KeywordTableSearchBackend
+
+ toolset = SQLToolset(
+ db_conn_id="snowflake_hq",
+ allowed_tables=["MODEL_CRM.SF_ASTRO_ORGS",
"MODEL_ASTRO.DEPLOYMENT_IMAGE_DETAILS"],
+ search_backend=KeywordTableSearchBackend(),
+ )
+
+``KeywordTableSearchBackend`` splits the query on whitespace and matches each
word,
+case-insensitively, as a substring of the table name and of the table's
comment; column
+comments are not searched. A word found in
+the name counts three times a word found in the comment; tables matching
nothing are
+dropped and ties are ordered by name. It introspects once per toolset
instance, then
+searches that result.
+
+Every result is checked against ``allowed_tables`` before the agent sees it,
whichever
+backend produced it, so a backend that knows about more tables than the agent
may use
+cannot widen access.
Review Comment:
This only holds when `allowed_tables` is set. With the default allow-all,
`_is_ref_allowed` passes every name, so a backend indexed with a broader role
can show the agent table names and comments its connection can't see. Could
this sentence say so and point at the warning below as the control in that case?
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the result is summarized (see
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with
``comments_omitted``, and the
+agent gets them back by asking for the columns it needs with ``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. What that costs depends on the
dialect. Where the
+dialect does not implement ``get_multi_table_comment`` itself, SQLAlchemy's
default reads the
+comments one table at a time. Based on the dialects' reflection
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On
PostgreSQL it was a single
+query when tried. On such databases leave ``include_table_comments`` off for
large schemas, or pass
+a custom ``search_backend`` that builds its catalog with one dialect-specific
query, for example
+against ``INFORMATION_SCHEMA``.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables``
returns tables without
+comments rather than reading them table by table. The default search backend
then has no comments
+to match, and a query whose words appear only in comments returns nothing;
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an
optional
+``limit`` (default 10, at most 50) and returns matching tables with their
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only
their number
+and a pointer to ``search_tables`` instead of the whole list. Without a
``search_backend``
+``list_tables`` is unchanged, however many tables there are.
+
+.. code-block:: python
+
+ from airflow.providers.common.ai.toolsets.sql import SQLToolset
+ from airflow.providers.common.ai.toolsets.sql_search import
KeywordTableSearchBackend
+
+ toolset = SQLToolset(
+ db_conn_id="snowflake_hq",
+ allowed_tables=["MODEL_CRM.SF_ASTRO_ORGS",
"MODEL_ASTRO.DEPLOYMENT_IMAGE_DETAILS"],
+ search_backend=KeywordTableSearchBackend(),
+ )
+
+``KeywordTableSearchBackend`` splits the query on whitespace and matches each
word,
+case-insensitively, as a substring of the table name and of the table's
comment; column
+comments are not searched. A word found in
+the name counts three times a word found in the comment; tables matching
nothing are
+dropped and ties are ordered by name. It introspects once per toolset
instance, then
Review Comment:
"It" reads as the backend, but the toolset builds the catalog and keeps it
for the instance's lifetime, for any backend that calls `get_catalog`. Worth
saying that a table created after the first search won't show up until the
toolset is built again?
##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one:
+
+.. code-block:: json
+
+ {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of
refunds"}],
+ "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides
from the
+release that added them. With an older ``common.sql``, or on a dialect that
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A
failure reading
+the *table* comment is ignored for the same reason; a failure reading the
columns is not.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the result is summarized (see
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with
``comments_omitted``, and the
+agent gets them back by asking for the columns it needs with ``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. What that costs depends on the
dialect. Where the
+dialect does not implement ``get_multi_table_comment`` itself, SQLAlchemy's
default reads the
+comments one table at a time. Based on the dialects' reflection
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On
PostgreSQL it was a single
+query when tried. On such databases leave ``include_table_comments`` off for
large schemas, or pass
+a custom ``search_backend`` that builds its catalog with one dialect-specific
query, for example
+against ``INFORMATION_SCHEMA``.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables``
returns tables without
+comments rather than reading them table by table. The default search backend
then has no comments
+to match, and a query whose words appear only in comments returns nothing;
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an
optional
+``limit`` (default 10, at most 50) and returns matching tables with their
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only
their number
+and a pointer to ``search_tables`` instead of the whole list. Without a
``search_backend``
+``list_tables`` is unchanged, however many tables there are.
+
+.. code-block:: python
+
+ from airflow.providers.common.ai.toolsets.sql import SQLToolset
+ from airflow.providers.common.ai.toolsets.sql_search import
KeywordTableSearchBackend
+
+ toolset = SQLToolset(
+ db_conn_id="snowflake_hq",
Review Comment:
This example doesn't show the case the section is about. With two
`allowed_tables`, `list_tables` never gets near `max_listed_tables`, so
`search_tables` only adds a tool call. The names also look like internal
warehouse names (the earlier example on main already has them). Something like
this covers both:
```python
toolset = SQLToolset(
db_conn_id="warehouse",
schema="analytics",
search_backend=KeywordTableSearchBackend(),
)
```
##########
providers/common/ai/provider.yaml:
##########
@@ -615,6 +615,7 @@ toolsets:
python-modules:
- airflow.providers.common.ai.toolsets.hook
- airflow.providers.common.ai.toolsets.sql
+ - airflow.providers.common.ai.toolsets.sql_search
Review Comment:
`sql_search` has no class ending in `Toolset`, so it doesn't fit under
`toolsets`: for a module like this, `process_module` in
`dev/registry/extract_versions.py` falls back to a synthetic `SqlSearchToolset`
registry entry. Could this line and the `sql_search` entry in
`TOOLSET_TABLE_ENTRIES` go? The backend stays importable and documented in
`sql.rst`.
##########
providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py:
##########
@@ -95,7 +99,8 @@
"where the budget allows a `type_histogram` and a `sample_columns`
preview) with `truncated` "
"set, `truncated_by` naming the limit that was hit, and a `hint`. Call
{tool_name} again with "
"a `name_contains` substring to retrieve the specific columns you need
instead of the whole "
- "table."
+ "table. A column or the table itself carries a `comment` when the database
provides one; "
Review Comment:
`DataFusionToolset` builds its `get_schema` description from this template
too and never returns comments, so its agents are told about keys that won't
come. The table's key is also `table_comment`, not `comment`. Could the comment
sentence be added only for `SQLToolset`, say through a parameter on
`build_get_schema_description`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]