Wow, hello again blogosphere. Time for a shift in thinking here. Since I work for Vertica now, I'm going to use this space (hopefully regularly) to blog about all things Big Data and Vertica-related. So, let's dig right in with an interesting (to me!) Vertica-related topic around system tables.
As you probably aware, there is a table in Vertica called system_tables. It contains entries for 114 (in 6.1.2) tables. One of these tables is a table called data_collector. Data_collector has 149 rows. Each of these rows describes a table in the "dc" schema which is part of the "data collector" set of tables. These tables are used by the Management Console. They aren't documented and could (and do!) change between releases. That being said, you can find some interesting stuff in the data_collector tables.
But last, and certainly not least, is another undocumented table called "vs_system_tables". I'm going to go out on a limb here and speculate that the "vs" stands for "Vertica System". Other candidates include "Very Secretive" or "Veritable Smorgasbord". The jury is still deliberating.
vs_system_tables, like system_tables is a table which contains descriptions of ALL tables, hidden or otherwise. vs_system_table also includes all data_collector tables and system_table entries as well. So, if you're curious for a unique set for each, you'd want to do something like the following:
SELECT * FROM data_collector ;
COUNT
-------
149
(1 row)
SELECT COUNT(*) FROM vs_system_tables
WHERE table_name NOT ILIKE 'dc_%'
AND table_name NOT IN (SELECT table_name FROM system_tables);
count
-------
116
(1 row)
That gives you a total of 379 tables. But not so fast - not all of these are actually tables. Several are actually views. Which ones, you ask? Well, the ones defined in the vs_system_views table. Of course.
select count(*) from vs_system_views ;
count
-------
100
(1 row)
So, out of the 379 "tables" we've identified, 100 of them are actually views. Which begs the question - can we change them? Most definitely.
Let's take a look a the "columns" view.
select data_type, data_type_length, character_maximum_length from columns limit 5 ;
data_type | data_type_length | character_maximum_length
-------------+------------------+--------------------------
varchar(20) | 20 | 20
varchar(20) | 20 | 20
int | 8 |
int | 8 |
float | 8 |
(5 rows)
Here, we can see that the columns table presents us with two different fields (among others) which define the length of the datatype. If the column is a character type, we have a second column which defines maximum length. This is kind of redundant. So, I'd like to modify the view to just show me "length" of the column, irrespective of whether it is a character field or non-character field. We could do this by modifying the view DDL in the vs_system_views table.
select * from vs_system_views where view_name = 'columns';
For brevity's sake, I'm not going to reproduce the results of that query here. But you can see that the table gives me the schema, and view_name, as well as a description and the "create view" DDL statement. But here are the columns in question:
vcs."attlen" as "data_type_length",
case when vcs.atttypid in (8, 9, 17, 117) and vcs.atttypmod <> -1 then vcs.attlen else null end as "character_maximum_length",
We can see here that "attlen" is being used in two different locations. I want to simplify this to just include it once and be done with it. So, we can eliminate the more complex second line, and just keep the "attlen" as "data_type_length" column. I'm going to go one more, and just name it "length" for simplicity.
With any view, you can run the query you've got without the "create view" portion of it to see if it gives you the results you want. If you like what you see, then build your view. For our test, we're going to give it a different name. In this case, "columns2".
create view "v_catalog"."columns2" as select vcs."t_oid" as "table_id",
vcs."nspname" as "table_schema",
vcs."relname" as "table_name",
vcs."systab" as "is_system_table",
vcs."t_oid" || '-' || vcs."attnum" as column_id,
vcs."attname" as "column_name",
vcs."typname" as "data_type",
vcs."atttypid" as "data_type_id",
vcs."attlen" as "length",
case when vcs.atttypid = 16 and vcs.atttypmod <> -1 then ((vcs.atttypmod-4)>>16) else null end as "numeric_precision",
case when vcs.atttypid = 16 and vcs.atttypmod <> -1 then ((vcs.atttypmod-4) & 1023) else null end as "numeric_scale",
case when vcs.atttypid = 12 and vcs.atttypmod <> -1 then vcs.atttypmod else null end as "datetime_precision",
case when vcs.atttypid = 14 and vcs.atttypmod <> -1 then (vcs.atttypmod & 65535) else null end "interval_precision",
vcs."attnum" as "ordinal_position",
not vcs."attnotnull" as "is_nullable",
case when vcs."attisidentity" then null else vcs."adsrc" end as "column_default",
vcs."attisidentity" as "is_identity"
from "v_internal"."vs_columns" as vcs
where vcs."nspname" <> 'v_internal' and vcs."nspname" <> 'v_monitor' and vcs."nspname" <> 'v_catalog';
ROLLBACK 5447: View columns2 cannot be created under system schema v_catalog
Whoa! It certainly didn't like that. We can't modify or create entries in these system tables. No matter how much we beg or plead. We'll just have to create it as a regular plain ol' view.
create view "public"."my_columns" as select vcs."t_oid" as "table_id",
vcs."nspname" as "table_schema",
... etc.
That works. In theory, I could create "my" own versions of many of the system tables, if I'm inclined to make minor changes to them.
Enjoy!
As you probably aware, there is a table in Vertica called system_tables. It contains entries for 114 (in 6.1.2) tables. One of these tables is a table called data_collector. Data_collector has 149 rows. Each of these rows describes a table in the "dc" schema which is part of the "data collector" set of tables. These tables are used by the Management Console. They aren't documented and could (and do!) change between releases. That being said, you can find some interesting stuff in the data_collector tables.
But last, and certainly not least, is another undocumented table called "vs_system_tables". I'm going to go out on a limb here and speculate that the "vs" stands for "Vertica System". Other candidates include "Very Secretive" or "Veritable Smorgasbord". The jury is still deliberating.
vs_system_tables, like system_tables is a table which contains descriptions of ALL tables, hidden or otherwise. vs_system_table also includes all data_collector tables and system_table entries as well. So, if you're curious for a unique set for each, you'd want to do something like the following:
SELECT
COUNT(*) FROM system_tables ;
COUNT
-------
114
(1
row)
SELECT * FROM data_collector ;
COUNT
-------
149
(1 row)
SELECT COUNT(*) FROM vs_system_tables
WHERE table_name NOT ILIKE 'dc_%'
AND table_name NOT IN (SELECT table_name FROM system_tables);
count
-------
116
(1 row)
That gives you a total of 379 tables. But not so fast - not all of these are actually tables. Several are actually views. Which ones, you ask? Well, the ones defined in the vs_system_views table. Of course.
select count(*) from vs_system_views ;
count
-------
100
(1 row)
So, out of the 379 "tables" we've identified, 100 of them are actually views. Which begs the question - can we change them? Most definitely.
Let's take a look a the "columns" view.
select data_type, data_type_length, character_maximum_length from columns limit 5 ;
data_type | data_type_length | character_maximum_length
-------------+------------------+--------------------------
varchar(20) | 20 | 20
varchar(20) | 20 | 20
int | 8 |
int | 8 |
float | 8 |
(5 rows)
Here, we can see that the columns table presents us with two different fields (among others) which define the length of the datatype. If the column is a character type, we have a second column which defines maximum length. This is kind of redundant. So, I'd like to modify the view to just show me "length" of the column, irrespective of whether it is a character field or non-character field. We could do this by modifying the view DDL in the vs_system_views table.
select * from vs_system_views where view_name = 'columns';
For brevity's sake, I'm not going to reproduce the results of that query here. But you can see that the table gives me the schema, and view_name, as well as a description and the "create view" DDL statement. But here are the columns in question:
vcs."attlen" as "data_type_length",
case when vcs.atttypid in (8, 9, 17, 117) and vcs.atttypmod <> -1 then vcs.attlen else null end as "character_maximum_length",
We can see here that "attlen" is being used in two different locations. I want to simplify this to just include it once and be done with it. So, we can eliminate the more complex second line, and just keep the "attlen" as "data_type_length" column. I'm going to go one more, and just name it "length" for simplicity.
With any view, you can run the query you've got without the "create view" portion of it to see if it gives you the results you want. If you like what you see, then build your view. For our test, we're going to give it a different name. In this case, "columns2".
create view "v_catalog"."columns2" as select vcs."t_oid" as "table_id",
vcs."nspname" as "table_schema",
vcs."relname" as "table_name",
vcs."systab" as "is_system_table",
vcs."t_oid" || '-' || vcs."attnum" as column_id,
vcs."attname" as "column_name",
vcs."typname" as "data_type",
vcs."atttypid" as "data_type_id",
vcs."attlen" as "length",
case when vcs.atttypid = 16 and vcs.atttypmod <> -1 then ((vcs.atttypmod-4)>>16) else null end as "numeric_precision",
case when vcs.atttypid = 16 and vcs.atttypmod <> -1 then ((vcs.atttypmod-4) & 1023) else null end as "numeric_scale",
case when vcs.atttypid = 12 and vcs.atttypmod <> -1 then vcs.atttypmod else null end as "datetime_precision",
case when vcs.atttypid = 14 and vcs.atttypmod <> -1 then (vcs.atttypmod & 65535) else null end "interval_precision",
vcs."attnum" as "ordinal_position",
not vcs."attnotnull" as "is_nullable",
case when vcs."attisidentity" then null else vcs."adsrc" end as "column_default",
vcs."attisidentity" as "is_identity"
from "v_internal"."vs_columns" as vcs
where vcs."nspname" <> 'v_internal' and vcs."nspname" <> 'v_monitor' and vcs."nspname" <> 'v_catalog';
ROLLBACK 5447: View columns2 cannot be created under system schema v_catalog
Whoa! It certainly didn't like that. We can't modify or create entries in these system tables. No matter how much we beg or plead. We'll just have to create it as a regular plain ol' view.
create view "public"."my_columns" as select vcs."t_oid" as "table_id",
vcs."nspname" as "table_schema",
... etc.
That works. In theory, I could create "my" own versions of many of the system tables, if I'm inclined to make minor changes to them.
Enjoy!
