System Table Metadata !! (desc)

[adrian.oprea]

How ca i get the " desc " of a table in vertica ??

-i know like this - for user schema

Code: Select all

dbadmin:>\d "table name"

- but how do i do it for system schemas and tables ?
Thank you

[JimKnicely]

Hi Adrian,

In vsql we can use \dS meta-command to list the system tables, but it only shows the schema and table names. No columns are listed...

Oddly, I don't think we can describe a system table in vsql (even in Vertica 6)

However, if you have Vertica 6.0 then you can query the system_columns table to get the column names and data types for the system tables...

For instance:

Code: Select all

dbadmin=> SELECT column_name, data_type FROM system_columns WHERE table_name = 'cpu_usage';
        column_name        |  data_type
---------------------------+--------------
 node_name                 | varchar(128)
 start_time                | timestamp
 end_time                  | timestamp
 average_cpu_usage_percent | float
(4 rows)

In Vertica 5 and 6 you can query the system_tables table to get a list of system tables:

Code: Select all

dbadmin=> select * from system_tables;
 table_schema |          table_name           |                                       table_description
--------------+-------------------------------+------------------------------------------------------------------------------------------------
 v_catalog    | views                         | View information
 v_catalog    | roles                         | Role information
 v_catalog    | nodes                         | Node information
 v_catalog    | databases                     | Database information
 v_catalog    | user_transforms               | User Defined Transform Function information
 v_catalog    | license_audits                | Database license compliance computation details
 v_catalog    | projection_delete_concerns    | Projections that may have delete performance concerns
 v_catalog    | sequences                     | Sequence information
 v_catalog    | comments                      | User comments on catalog objects
 v_catalog    | passwords                     | User password history and password reuse policy
 v_catalog    | user_audits                   | User-requested database object size computation details
 v_catalog    | profile_parameters            | Profile Parameters information
 v_catalog    | profiles                      | Profile information
 v_catalog    | dual                          | Oracle(TM) compatibility DUAL table
 v_catalog    | user_functions                | User Defined Function information
 v_catalog    | resource_pools                | Information about defined resource pools, both internal and dba-created
 v_catalog    | foreign_keys                  | Foreign key information
 v_catalog    | projection_columns            | Projection columns information
 v_catalog    | user_procedures               | User procedure information
 v_catalog    | view_columns                  | View column information
 v_catalog    | system_tables                 | Displays a list of all system tables except internal ones
 v_catalog    | primary_keys                  | Primary key information
 v_catalog    | users                         | User information
 v_catalog    | table_constraints             | Constraint information
 v_catalog    | projections                   | Projection information
 v_catalog    | grants                        | Grant information
 v_catalog    | columns                       | Table column information
 v_catalog    | types                         | Information about supported data types
 v_catalog    | tables                        | Table information
 v_monitor    | session_profiles              | Per session profiling information
 v_monitor    | query_profiles                | Query Profiling
 v_monitor    | sessions                      | Information on each Session
 v_monitor    | storage_containers            | Information on each storage container
 v_monitor    | system                        | System level information
 v_monitor    | resource_queues               | Queries waiting to acquire resources
 v_monitor    | query_metrics                 | Summarized query information
 v_monitor    | resource_pool_status          | Resource pool usage Information
 v_monitor    | user_libraries                | List of user defined shared libraries
 v_monitor    | user_library_manifest         | List of exported (by library writers) objects from user defined shared libraries
 v_monitor    | projection_storage            | Storage information on each Projection
 v_monitor    | partitions                    | Partition metadata
 v_monitor    | node_resources                | Per node profiling information
 v_monitor    | tuning_recommendation_details | Tuning recommendations from workload analyzer
 v_monitor    | configuration_parameters      | Configuration Parameters information
 v_monitor    | locks                         | Lock grants and requests for all nodes
 v_monitor    | resource_rejections           | Resource Rejection Summarizations
 v_monitor    | strata                        | Information of strata used in Tuple Mover, one row per stratum
 v_monitor    | strata_structures             | Information of strata structures used in Tuple Mover, one row per strata structure
 v_monitor    | host_resources                | Per host profiling information
 v_monitor    | execution_engine_profiles     | Per EE operator profiling information
 v_monitor    | load_streams                  | Load metrics for each load stream on each node.
 v_monitor    | event_configurations          | Current Event configuration
 v_monitor    | disk_storage                  | Disk usage information
 v_monitor    | delete_vectors                | Information on delete vectors
 v_monitor    | database_connections          | List of connections to other Vertica databases
 v_monitor    | current_session               | Information on current Session
 v_monitor    | column_storage                | Information on the amount of disk storage used by each column of each projection on each node.
 v_monitor    | data_collector                | Statistics on all Usage Data Collector logs
 v_monitor    | projection_refreshes          | Historical and current projection refreshes
 v_monitor    | active_events                 | Displays all of the active events in the cluster
 v_monitor    | critical_nodes                | Nodes whose failure will result in cluster shutdown
 v_monitor    | tuning_recommendations        | Aggregated tuning recommendations from workload analyzer
 v_monitor    | database_snapshots            | Information on stored database snapshots
 v_monitor    | disk_resource_rejections      | Disk Resource Rejection Summarizations
 v_monitor    | resource_usage                | Resource usage Information
 v_monitor    | critical_hosts                | Hosts whose failure will result in cluster shutdown
 v_monitor    | tuple_mover_operations        | Current tuple mover operations
 v_monitor    | wos_container_storage         | Storage information on WOS allocator
 v_monitor    | resource_acquisitions_history | Resources used by completed queries
 v_monitor    | resource_acquisitions         | Resources in use by queries
(70 rows)

And here is a whacky way to see the column names and their data types for a system table if you have Vertica 5... for instance, I want to see the column names and their data types for the v_catalog.users system table. To do so, I can create a view based on that table and then describe the view!

Code: Select all

dbadmin=> create view public.users_vw as select * from v_catalog.users where 1=2;
CREATE VIEW
dbadmin=> \dv public.users_vw;
                     List of View Fields
 Schema |   View   |      Column       |     Type      | Size
--------+----------+-------------------+---------------+------
 public | users_vw | user_id           | int           |    8
 public | users_vw | user_name         | varchar(128)  |  128
 public | users_vw | is_super_user     | boolean       |    1
 public | users_vw | profile_name      | varchar(128)  |  128
 public | users_vw | is_locked         | boolean       |    1
 public | users_vw | lock_time         | timestamptz   |    8
 public | users_vw | resource_pool     | varchar(128)  |  128
 public | users_vw | memory_cap_kb     | varchar(20)   |   20
 public | users_vw | temp_space_cap_kb | varchar(20)   |   20
 public | users_vw | run_time_cap      | varchar(63)   |   63
 public | users_vw | all_roles         | varchar(8192) | 8192
 public | users_vw | default_roles     | varchar(8192) | 8192
(12 rows)

[adrian.oprea]

Thx