Please add information_schema (ANSI SQL norm)

[monetdb-team]

Date: 2019-12-11 21:04:03 +0100
From: Simon AUBERT <<simon.aubert>>
To: Martin van Dinther <<martin.van.dinther>>
Version: 11.35.3 (Nov2019)
CC: martin.van.dinther

Last updated: 2020-01-14 21:46:48 +0100

Comment 27481

Date: 2019-12-11 21:04:03 +0100
From: Simon AUBERT <<simon.aubert>>

Hello,

According to wikipedia :
"In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database"

I think such a feature would help the support of monetdb by more tools (such as Alteryx.. ) and it's never a bad thing when you follow the SQL norm.

Best regards,

Simon

Comment 27495

Date: 2019-12-19 13:56:40 +0100
From: Martin van Dinther <<martin.van.dinther>>

Hello Simon,
Thanks for reporting this request.
This feature is on our ToDo-list, so we will let you know once it is available.

Question: I guess Alteryx connects to MonetDB via the ODBC interface. ODBC (and JDBC) support standardised programming interfaces to retrieve schema information (such as SQLTables(), SQLColumns()). Usually database tools use those APIs to get the database metadata. Does Alteryx not use those APIs to retrieve schema information?

Comment 27499

Date: 2019-12-19 20:58:08 +0100
From: Simon AUBERT <<simon.aubert>>

Hello Martin,

First of all, I'm very happy this feature is on your todo. Thanks :)

To answer your question, from what I have understood to Alteryx in this point, it throws a set of query to guess what database it is. Something like :
Mar 01 11:37:21.318 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select USER(), APPLICATION_ID() from system.iota

Mar 01 11:37:22.863 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select USER as USER_NAME from SYSIBM.SYSDUMMY1

Mar 01 11:37:23.454 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select * from rdb$relations

Mar 01 11:37:23.546 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select first 1 dbinfo('version', 'full') from systables
etc, etc, etc...

Then, there is also the Visual Query Builder that's supposed to show a treemap of databases/schemas/tables/columns. As of today, this Visual Query Builder doesn't retrieve the columns in MonetDB. Logically, if information_schema is implemented, as it's in the norm, it should work without having the guys @ Alteryx hard coding the sys schema.

I don't know about SQLColumns(), I guess it isn't used.

For my information, do you plan to add tables in the information_schema or just views such as SQL Server?

Best regards,

Simon

Comment 27513

Date: 2020-01-14 18:13:04 +0100
From: Martin van Dinther <<martin.van.dinther>>

Hello Simon,

The information_schema will contain system views.

Can you provide us some SQL queries which actually query the information_schema as generated by Alteryx?

Does Visual Query Builder show you currently the database?
And in it the schemas?
And in those the available tables?
So only the columns (of a table or view) are not shown?

FYI: SQLTables() and SQLColums() are C functions standardised in the ODBC API, not SQL tables or functions. So you do not call them using SQL but from compiled C code.
If the schemas and tables and shown Visual Query Builder, but the columns not, it may be a problem in the MonetDB ODBC driver SQLColumns() implementation.
Can you confirm that Alteryx is using MonetDB ODBC driver to connect to MonetDB server?
If so, could you make a log file of the ODBC calls made. This log file can be created by turning logging on in the ODBC Driver Manager.

Comment 27514

Date: 2020-01-14 21:46:48 +0100
From: Simon AUBERT <<simon.aubert>>

Hello Martin,

Thanks for the answer.

1/Yes, Alteryx uses the ODBC driver. It's some kind of what they call "generic odbc connection". Some databases (PSQL, MSQL, Vertica, Oracle..) have a dedicated entry in the menu.

2/Indeed, I can see the schemas and the tables, not the columns.

3/I will get back to you as soo as I can on the queries/log you ask

Best regards,

Simon

[simonaubertbd]

Hello @monetdb-team

Any news on that ? It was on your todolist last year.

Best regards,

Simon

[mvdvm]

The upcoming feature release (Dec 2023) will have information_schema schema with SQL standard views for schemata, tables, columns, views, etc.

[simonaubertbd]

Hello @mvdvm This is great ! Good job and thanks to the team. :)

[mvdvm]

This has been released in Dec2023 release. See: https://www.monetdb.org/release-notes/dec2023/
For details see: https://www.monetdb.org/documentation/user-guide/sql-catalog/information_schema/