You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by GitBox <gi...@apache.org> on 2022/06/15 18:39:32 UTC
[GitHub] [arrow-adbc] lidavidm opened a new pull request, #18: Add table/column reflection
lidavidm opened a new pull request, #18:
URL: https://github.com/apache/arrow-adbc/pull/18
Adds methods to get table schema/columns, and implements all the metadata methods for SQLite.
The methods differ slightly from Flight SQL. Instead of returning a serialized schema with the table list, a separate method is added to get the Arrow schema. Also, an JDBC/ODBC-style GetColumns method was added.
Fixes #14.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1160597922
This is similar to what MySQL does -- others (like Postgres, SQL Server, DuckDB (?)) would store the `fk_` columns in a detail table `constraint_column_usage` (we would use a list of structs here). I have found that approach to be slightly more natural for interpreting the relationships later on.
The format you propose here is more like a "wide" form, the Postgres version would be a "longer" form.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1159732527
I wonder if we also should offer a way to expose primary and foreign key, and perhaps unique constraints. This allows learning the data model from a remote database.
I have devised a "minimum variant" of a sane `INFORMATION_SCHEMA` that allows storing this information. Of course no database (except perhaps DuckDB) implements this schema, tweaks are needed everywhere else. Essentially this consists of three new views, linked 1:n: `tables` <- `table_constraints` <- `key_column_usage` <-`constraint_column_usage` . This would only require an extra column in our "tables" element, implementers could leave it empty. What do you think?
I'd be excited to see it here, happy to provide guidance if we agree to move forward.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1160634749
Ah, something more like this then?
```
/// CONSTRAINT_SCHEMA is a Struct with fields:
///
/// Field Name | Field Type | Comments
/// -------------------------|-----------------------|---------
/// constraint_name | utf8 |
/// constraint_type | utf8 not null | (1)
/// column_names | list<utf8> not null | (2)
/// column_usage | list<USAGE_SCHEMA> | (3)
///
/// 1. One of 'CHECK', 'FOREIGN KEY', 'PRIMARY KEY', or 'UNIQUE'.
/// 2. The columns on the current table that are constrained, in
/// order.
/// 3. For FOREIGN KEY only, the referenced table and columns.
///
/// USAGE_SCHEMA is a Struct with fields:
///
/// Field Name | Field Type | Comments
/// -------------------------|-----------------------|---------
/// fk_catalog | utf8 |
/// fk_db_schema | utf8 |
/// fk_table | utf8 |
/// fk_column_name | utf8 |
```
This would be easier to extend with additional columns if we need more metadata.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1160714111
Yes, that looks about right -- and is still createable with *one single query* on DuckDB and perhaps Postgres.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1158027202
Hmm, that sounds reasonable. So the schema would be something like this?
```
catalog_name: utf8
catalog_schemas: list<struct<schema_name: str, schema_tables: list<...>>>
```
And if you filtered by (say) depth=catalog, you'd instead get
```
catalog_name: utf8
catalog_schemas: list<struct<schema_name: str>>
```
I suppose this shouldn't be too painful to assemble/work with, either (possibly we can add a second entrypoint for a flattened version if needed).
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm merged pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm merged PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1159135921
Implemented for SQLite. It's a little tedious to implement for SQLite but isn't especially complicated, at least.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1158037665
I'm not sure how difficult unpacking will be, and perhaps if filtering by catalog name we would only get
```
schema_name: utf8
schema_tables: list<...>
```
because the catalog name will be fixed. TBD.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1159752806
Great! I think I can come up with a SQL query that returns this result for Postgres right from the database, but this is useful only if we support getting nested data from the database.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1159733606
Since we have all the other metadata here, I think it's reasonable to add that information as well. So the 'tables' would now be something like:
```
table_name: utf8
table_type: utf8
table_columns: list<struct<...>>
table_constraints: list<struct<...>>
```
Flight SQL also has some calls that get "which foreign keys on other tables reference this table" which maybe we should include too. (Or maybe if those are annoying to model, we can keep those separate, and optional to implement.)
Finally, I was thinking we could keep `GetTableTypes` and `GetTableSchema` since they're kind of separate from the metadata.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1164433932
Removed the "old" metadata methods in favor of the new one. I'll merge this for now but we can keep iterating. Thanks Kirill!
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1160481520
Is something like this what you were thinking? (This has less information than what Flight SQL provides but feels minimal, unless we also want to reflect the cascade/delete rules)
```
/// CONSTRAINT_SCHEMA is a Struct with fields:
///
/// Field Name | Field Type | Comments
/// -------------------------|-----------------------|---------
/// constraint_name | utf8 |
/// constraint_type | utf8 not null | (1)
/// column_names | list<utf8> not null | (2)
/// fk_catalog | utf8 | (3)
/// fk_db_schema | utf8 | (3)
/// fk_table | utf8 | (3)
/// fk_column_names | list<utf8> | (3)
///
/// 1. One of 'CHECK', 'FOREIGN KEY', 'PRIMARY KEY', or 'UNIQUE'.
/// 2. The columns on the current table that are constrained, in
/// order.
/// 3. For FOREIGN KEY only, the referenced table and columns.
```
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1158246255
Fair enough, we can go with a static output schema for type stability.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1161903416
Implemented for SQLite, though the constraint metadata ignores the column name filter for simplicity.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] krlmlr commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
krlmlr commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1158022230
Thanks. I wonder if we need that many entry points.
If we assume a hierarchy like catalog -> schema -> table -> column, could we have one single entry point that returns a nested data structure of (potentially) all catalogs with all schemas, tables, and columns (all nested)? We would offer two ways of filtering:
- filter by depth: return details on the catalog, schema, table, or column level
- filter by name: catalog name, then perhaps schema name, then perhaps table name, then perhaps column name
Many databases with `information_schema` could implement all of this with a single query plus a local nesting operation, Postgres + DuckDB could do a single nested query.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1158040008
Hmm, the filters (at least currently, and in Flight SQL) can be patterns as well as fixed strings.
If filtering by catalog name gives you the schemas directly, that starts to sound like having different entrypoints to me, just exposed a little differently.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-adbc] lidavidm commented on pull request #18: Add table/column reflection
Posted by GitBox <gi...@apache.org>.
lidavidm commented on PR #18:
URL: https://github.com/apache/arrow-adbc/pull/18#issuecomment-1156813679
CC @hannes, @krlmlr. This is roughly patterned off of Flight SQL (and seems similarish to DBI from quick look as well)
I think we discussed an MSSQL style `information_schema` or other special table for this metadata instead at some point, but as with Flight SQL, that makes the implementor's job much harder unless they already have this feature, and it means the client doesn't know the syntax they need to use to query/filter said special table.
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org