Vertica Blog

Vertica Blog

SQL

Three 3D arrows, different colors pointing in different directions

View Vertica Features Recently Used: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 24, 2019

Curious if Vertica features are being used in your database? If so, the data collector table DC_FEATURES_USED stores recently used Vertica features (i.e. commands, meta-functions and UDXs). Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/EnablingAndDisablingDataCollector.htm Have fun!
Modern Database Analytics

Monitoring COPY Statement Events (Part 2): Quick Tip

James Knicely, Vertica Field Chief Technologist
January 23, 2019

A COPY command is typically composed of two phases. The second phase, if using the DIRECT parameter, includes sorting the data and writing the data in Disk. You can monitor the progress of this phase of the COPY command via the data collector table DC_MERGE_EVENTS. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/EnablingAndDisablingDataCollector.htm Have fun!
Programmer

Monitoring COPY Statement Events (Part 1): Quick Tip

James Knicely, Vertica Field Chief Technologist
January 23, 2019

A COPY command is typically composed of two phases. The first phase includes reading the data from the source, parsing the data, segmenting the data and sending it to the nodes after it is segmented. You can monitor the load events of this phase of the COPY command via the data collector table DC_LOAD_EVENTS. Helpful...

Nested Boolean Operators: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 18, 2019

Vertica supports the following Boolean operators: • AND • OR • NOT To nest Boolean operators, use parenthesis! Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Operators/BooleanOperators.htm Have fun!

Using VSQL as a Calculator: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 18, 2019

A calculator is something used for making mathematical calculations, in particular a small electronic device with a keyboard and a visual display. VSQL mostly fits that description! And for fun, you can also astound your friends using this old calculator trick! 1. Have a buddy pick a number between 1 and 9. 2. Now have...

Extracting Characters from the Left and Right Side of a LONG VARCHAR: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 16, 2019

The LEFT SQL function returns the specified characters from the left side of a string, while the RIGHT SQL function returns the specified characters from the right side of a string. Unfortunately, the LEFT and RIGHT SQL functions won’t work on table columns having the LONG VARCHAR data type where the data size can be...
Database Server Room

Specifying CASCADE When Dropping a Table: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 14, 2019

The DROP TABLE command removes a table and its projections. A projection with a create type of “DELAYED CREATION” is an auto-projection. Auto-projections are superprojections that Vertica automatically generates for tables. If you create a projection manually or via the Database Designer (non-auto projections), you will need to drop the table specifying the CASCADE option....

Ordering the List of Values Returned from LISTAGG

James Knicely, Vertica Field Chief Technologist
January 11, 2019

The LISTAGG function transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to de-normalize rows into a string of comma-separated values or other human-readable formats. What if I want to order the list of values that LISTAGG returns? Unfortunately, there...

Ignore NULL Values in Non-Correlated Subqueries: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 10, 2019

A subquery is a SELECT statement embedded within another SELECT statement. The embedded subquery is often referenced as the query's inner statement, while the containing query is typically referenced as the query's statement, or outer query block. A subquery returns data that the outer query uses as a condition to determine what data to retrieve....

Drop More than One Table at a Time: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 9, 2019

The DROP TABLE command removes a table and its projections. You can drop more than one table at a time by specifying a comma delimited set of tables! That was easy. How about dropping more than one table? At first you might be tempted to run a DROP TABLES command to drop multiple tables, but...

Allowing Users to Query a View Owned by Another User: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 8, 2019

As we learned in yesterday’s Vertica Quick Tip A View Owner Needs Access to the Underlying Objects, the owner of a View must have direct access to the underlying objects referenced by the view! But what if another user wants to read a view owned by another user? In this case, not only must the...

A View Owner Needs Access to the Underlying Objects: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 7, 2019

You can use the ALTER VIEW … OWNER TO command to change a Vertica database view’s ownership. It’s important to know that a View’s owner must also have access to the underlying objects referenced by the view! Exanple: Great! The user, USER1, can read from the view as expected. But what happens when I change...