information_schema support for ANSI SQL Support

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

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited December 2019

    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

  • edited December 2019

    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

  • 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.

  • 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

  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.