information_schema support for ANSI SQL Support
simon_aubert_bd
Community Edition User ✭
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 Vertica by many more tools and it's never a bad thing when you follow the SQL norm. Standard, normalization and interoperability matter. Especially when your competition has it (these last years, it has been implemented in Hive or Monet DB as an example)
Best regards,
Simon
0
Comments
Vertica provides the information schema as read only system tables and views that let you monitor your database and evaluate settings of its objects. You can query these tables just as you do other tables, depending on privilege requirements.
See: https://docs.vertica.com/24.1.x/en/sql-reference/system-tables/
Hello @mosheg . Correct me if I'm wrong but it's
V_CATALOG schema
V_MONITOR schema
even if it's the same usage, it's clearly not the same name
The system tables in v_catalog schema provide information about the objects in a database like tables, constraints, users, projections, and so on. This is what the "information schema" is all about.
For example: V_CATALOG.columns provides all tables columns information.
See: https://docs.vertica.com/24.1.x/en/sql-reference/system-tables/v-catalog-schema/columns/
Hi all,
yes @mosheg you're right we can find the same relevant information, I think @simon_aubert_bd's question is about the official ANSI specification of this schema that we don't support.
You would need to manually implement it.
Hello @VValdar Yes, that's my point. But it's more a feature request that a question I think respecting the ANSI specification could be benefit for Vertica users and sales.
According to chatGPT (I'm really too honest)
Best regards,
Simon
Well, we are in very good company.
Last time I checked
They all have a system view/table named "columns" or similar, one named "tables" or similar, etc. in a differently named schema.
Vertica has the "v_catalog" schema.
All or nearly all DBMS-s offer a way to pre-set a default schema.
In PostgreSQL and Vertica it's
SET SEARCH_PATH TO schema1,schema2,schema3;
its effect is that, if you goSELECT * FROM foo
,foo
is searched inschema1
, if not found inschema2
, etc. until it's found. Andv_catalog
is in the schema by defaul. The default search path is<_current_user_>, public, v_catalog, v_monitor, v_internal, v_func
.So -
SELECT table_name FROM tables;
, for example, will always work.We never felt the urgency to explicitly create an alias for our
v_catalog
schema.As I already stated a long time ago in this discussion thread - feel free to create your own schema.
run the script below as dbadmin from vsql:
@marcothesane Indeed you're not alone. But allow me to add a few words about it :
-Oracle and Microsoft are leaders in db for several decades, they just don't care about SQL norm.
-DB2 is legacy for 8 years now
-All are "declining" according to https://db-engines.com/en/ranking_trend (Well, it's relative and subject to interpretation but it looks like market share is also declining)
-on the opposite side, the most successful db these last years, like Snowflake, respect the information _schema.
-you're supposed to ambition to do better than competitors
The clean way to deal with metadata in a relational database management system, in my eyes, is to use a standard supported by all of them as of now:
It's the collection of functions like SQLTables(), SQLColumns(), SQLPrimaryKeys(), SQLForeignKeys() , as supported by the JDBC and ODBC standard.
Every RDBMS has a JDBC and an ODBC driver supporting them.
And, as Vertica has no indexes, and no materialised views, we have no way of supporting the standard anyway...
Hello @marcothesane
I will be humble : I don't know what is the cleanest way to deal with metadata. On the other side what I know is that not all software are developped with jdbc/odbc specifications in mind, even not always jdbc/odbc connectivity at all.
If you take Grafana, it uses a go plugin. In python, you have dedicated libraries, in Alteryx, there are several SQL queries send to database in order to determine where the table catalog is located :
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
Mar 01 11:37:23.707 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select #01/01/01# as AccessDate
Mar 01 11:37:23.868 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: exec sp_server_info 1
Mar 01 11:37:24.093 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select top (0) * from INFORMATION_SCHEMA.INDEXES
Mar 01 11:37:24.219 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: SELECT SERVERPROPERTY('edition')
Mar 01 11:37:24.423 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select DATABASE() as
database
, VERSION() asversion
Mar 01 11:37:24.635 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select * from sys.V_$VERSION at where RowNum<2
Mar 01 11:37:25.230 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select cast(version() as char(10)), (select 1 from pg_catalog.pg_class) as t
Mar 01 11:37:25.415 INFO 5264 HardyDataEngine::Prepare: Incoming SQL: select NAME from sqlite_master
So, yes, you can argue it's dirty (and in Alteryx case, I will totally agree) but that's the current situation.
But there's more : a lot of developers (like myself) only have to right to write sql query, we can't pass SQLTables() etc... in a Qlik Sense/Tableau/Alteryx/other bi tools. So when I write a Qlik Sense to retrieve all the tables, I have to find the table catalog. And if I have several RDBMS, I will have to maintain, duplicate, code while, in my dream, world, I would use a standard.
"And, as Vertica has no indexes, and no materialised views, we have no way of supporting the standard anyway..."
A dummy table with 0 rows can totally do the job
Best regards,
Simon