pepijnve commented on PR #25887: URL: https://github.com/apache/datafusion/pull/25887#issuecomment-6015448009
I spent some time surveying what's out there with AI to get a feel for our options. We'll need to make a decision on a couple of things: 1. Should `information_schema` be global or per catalog? 2. If global, which catalog is it a child of? 3. If per catalog, what does an unqualified usage of `information_schema` resolve to? Here's what I got from Claude wrt other some other systems out there | System | Global? | Where it lives? | Unqualified? | |---|---|---|---| | DuckDB | Yes, one view covers all attached databases | Once, in the `system` catalog | `system.information_schema` | | SQL Server | No; query `db.INFORMATION_SCHEMA.TABLES` per database, or use `sys.databases` plus dynamic SQL | Each database | Current database (`USE`) | | Snowflake | No; `SNOWFLAKE.ACCOUNT_USAGE` views cover the account (with latency) | Each database | Current database; errors if none is set | | Trino | No; `system.jdbc.*` and `system.metadata.*` cover all catalogs | Each catalog (generated per connector) | Session catalog; errors if none is set | | Databricks (Unity Catalog) | Opt-in via `system.information_schema` | Each catalog, plus `system.information_schema` | Current catalog (`USE CATALOG`) | | BigQuery | Region-qualified, e.g. `` `region-us`.INFORMATION_SCHEMA.TABLES `` | Per dataset or region, as a qualifier | Generally errors without a qualifier | | MySQL / MariaDB | Yes, but "databases" are really schemas; `table_catalog` is always `def` | Once, global | The global schema | | SQLite | No; query `aux.sqlite_schema` per attachment | None; each attached DB has `sqlite_schema` | `main.sqlite_schema` | | StarRocks | No; query `catalog.information_schema.tables` per catalog (external catalogs supported from v3.2) | Each catalog (`default_catalog` and each external catalog) | Current catalog (`SET CATALOG`), `default_catalog` by default | The current state of DataFusion is somewhere in between all the choices listed above: `information_schema` lives in each catalog which suggests it's partitioned per catalog, but each 'instance' is identical and is actually global/cross-catalog. This becomes particularly annoying when you're trying to work with remote catalogs. It doesn't seem desirable for a query that looks local to start accessing remote resources (e.g., I try to query the memory catalog via the information schema, and it starts accessing my Iceberg REST catalog over the internet). Given that a cross-catalog information schema currently exists, I think we at least need to retain that capability. The question then is how we expose that to the outside world. What I did in this PR is to introduce a new magic catalog named `system` that holds the global information schema. Because DataFusion only has a single default catalog value at the moment rather than a PostgreSQL style search path, you need to address this explicitly. An alternative solution could be to add a search path capability for unqualified identifier resolution. By default, we would not expose the per-catalog information schema and add `system` to the end of the search path by default. This would still introduce a behaviour change though for info schema queries in qualified form (i.e. `select * from datafusion.information_schema.tables` would not work). A hybrid where there are special resolution rules for `information_schema` specifically doesn't seem like a good idea to me. -- 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
