information_schema support for ANSI SQL Support

simon_aubert_bd

Hello
According to https://en.wikipedia.org/wiki/Information_schema
"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"

It appears Vertica does not support information_schema (but v_catalog https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/V_CATALOGSchema.htm), while it would be great to follow the norm. i would add, it does not require a lot of job to produce an information_schema schema and then some views in it.

Best regards,

Simon

Jim_Knicely

Hah!

https://en.wikipedia.org/wiki/Information_schema says:

RDBMSs which do not support information_schema include:

Apache Derby
Firebird
Microsoft Access
IBM Informix
Ingres
IBM DB2
Oracle Database
SAP HANA
SQLite
Sybase ASE
Sybase SQL Anywhere
Teradata
Vertica

And here...

https://dataedo.com/kb/databases/all/information_schema

simon_aubert_bd

Hello @Jim_Knicely

First of all, thanks for your answer. Yeah, a lot of RDBMS don't care about the SQL norm, including Oracle or, in a more related topic, MonetDB. This means for dataprep/dataviz/etl... etc developers to hard code according to database. As an example, Alteryx or Qlik Sense are really not easy to use with Monetdb or Firebird. On the other hand, one of the two reasons that explain the success of PSQL is to be really compliant with SQL norm. Even Apache Hive support information_schema in the last releases.
But then, I don't understand your point ? Don't you have higher standards than your competitors ?

Best regards,
Simon

Bryan_H

Few thoughts here: it looks like information_schema appears in SQL-92, but I don't see it exactly in my SQL-99 reference.
Since most RDBMS internally implement information_schema as views over internal system-specific catalog, it should be straightforward to do this for Vertica. There exists some scripts to create information_schema for Oracle, for example.
There is a Stack Overflow commenting reasons why information_schema is NOT commonly used anymore (source https://stackoverflow.com/questions/3653637/sql-server-should-i-use-information-schema-tables-over-sys-tables):

The Information_Schema views only show objects that are compatible with the SQL-92 standard. This means there is no information schema view for even quite basic constructs such as indexes (These are not defined in the standard and are left as implementation details.) Let alone any SQL Server proprietary features.

For Vertica, this would mean system-specific objects like projections, UDx, etc. would not appear in information_schema
Not all information_schema are "standard" anyways; MySQL, MS SQL, Snowflake all state in their respective documentation that the information_schema implementation has proprietary features either added or not listed, or unsupported features removed.
Is there a specific use case for which information_schema is needed? If so, I can look into adapting the Oracle scripts from SourceForge to create a basic information_schema implementation, though it would help to know the use case so I can also write some tests.

simon_aubert_bd

Hello @Bryan_H,
Thanks for your very complete answer. I really appreciate the effort you put in it.
1. "Since most RDBMS internally implement information_schema as views over internal system-specific catalog, it should be straightforward to do this for Vertica. "
I didn't want to suggest how to implement it but that's exactly what I had in mind : a script that execute at install /upgrade of the database to create the schema and the views based on the existing v_catalog.

2."For Vertica, this would mean system-specific objects like projections, UDx, etc. would not appear in information_schema"
Yep and I don't think it's an issue at all.

3."Is there a specific use case for which information_schema is needed?"
A database comparaison to be sure all the tables are imported with a very simple "standard" query? That's what come first. Tableau and Alteryx have both hard-coded routine for Vertica, so I don't have a better use case right now. However, I'm pretty sure there are several examples it would help. With a few google search I find it : https://github.com/flyway/flyway/issues/1761

And every time you gain compliance, you add value to the product. Especially if it's straightforward, to me, it's a no-brainer.

Best regards,

Simon

davepoole

We use a number or DB products in our organisation of which Vertica is one. Many of them (including Vertica) are PostGres based.
We have written simple tools to put databases under source control, auto-document the DB on a nightly basis, and also to create deployment pipelines. The PostGres commonality means that there is minimal coding difference needed to make the tools work with the different platforms. The inclusion of information_schema views would increase this commonality.
One of the biggest challenges in building (or buying) tools is putting together the pseudo-business case for them. Unfortunately a development team may not be good at selling the benefits of tooling in a way that wins hearts and minds of those who have to pay for them. Commonality becomes a force multiplier

simon_aubert_bd

Hello @Bryan_H
Is there some update on this topic?
Best regards,
Simon

marcothesane

I'm not aware of any - and the workaround could be doable, though.
I would opt for a minimal set of these tables:

• tables
• views
• columns
• primary key constraints - and their columns
• foreign key constraints - and their columns

And then, I'd CREATE SCHEMA information_schema myself - and add the views as I know they are expected from the queries that you use regularly. You'll find a match for all of the above in the v_catalog system schema of Vertica.