Labeling Queries: Quick Tip

Posted November 28, 2018 by Jim Knicely, Big Data Solutions Architect, Vertica

To quickly identify queries for profiling and debugging purposes, include the LABEL hint. Later you can search the QUERY_REQUESTS and QUERY_PROFILES systems table for the label!

Example: dbadmin=> CREATE TABLE big_table_test AS /*+ LABEL(big_table_test_label) */ SELECT * FROM big_table; CREATE TABLE dbadmin=> SELECT request FROM query_requests WHERE request_label = 'big_table_test_label'; request --------------------------------------------------------------------------------- CREATE TABLE big_table_test AS /*+ LABEL(big_table_test_label) */ SELECT * FROM big_table; INSERT INTO public.big_table_test SELECT big_table.c1, big_table.c2, big_table.c3, big_table.c4 FROM public.big_table (2 rows) dbadmin=> SELECT query FROM query_profiles WHERE identifier = 'big_table_test_label'; query -------------------------------------------------------------------------------- CREATE TABLE big_table_test AS /*+ LABEL(big_table_test_label) */ SELECT * FROM big_table; INSERT INTO public.big_table_test SELECT big_table.c1, big_table.c2, big_table.c3, big_table.c4 FROM public.big_table (2 rows) Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Profiling/LabelQueries.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETABLE.htm

Have fun!