Viewing Internal Statements Executed by Vertica
Jim_Knicely
- Select Field - Administrator
The DC_INTERNAL_STATEMENTS Data Collector table tracks internal statements that are executed by Vertica.
Example:
dbadmin=> SELECT component, description
dbadmin-> FROM data_collector
dbadmin-> WHERE table_name = 'dc_internal_statements';
component | description
--------------------+------------------------------------
InternalStatements | Internal statements kicked started
(1 row)
dbadmin=> CREATE TABLE a_test (c1 INT, c2 VARCHAR(10));
CREATE TABLE
dbadmin=> INSERT INTO a_test SELECT 1, 'A';
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT projection_name
dbadmin=> SELECT projection_name, is_super_projection, create_type
dbadmin-> FROM projections
dbadmin-> WHERE anchor_table_name = 'a_test';
projection_name | is_super_projection | create_type
-----------------+---------------------+------------------
a_test_super | t | DELAYED CREATION
(1 row)
But I didn’t create that projection, Vertica did!
That is, Vertica created an “Auto-projection” on my behalf because I loaded data into a table that had no previously defined projections.
I can view the SQL statement that was executed by Vertica internally via the DC_INTERNAL_STATEMENTS Data Collector table:
dbadmin=> SELECT statement
dbadmin-> FROM dc_internal_statements
dbadmin-> WHERE session_id = CURRENT_SESSION();
statement
-----------------------------------------------------------------------------------------------------------------------------
CREATE PROJECTION public.a_test AS SELECT * FROM public.a_test ORDER BY c1,c2 SEGMENTED BY hash( c1,c2) ALL NODES KSAFE;
(1 row)
Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Glossary/DataCollector.htm
Have fun!
0