Vertica Blog

Vertica Blog

SQL

Display the Vertica Process Memory Allocated

James Knicely, Vertica Field Chief Technologist
April 10, 2019

For common Vertica cluster configurations, there should be a single running Vertica process on each node. You can use the Linux ps command to determine the process ID of the Vertica process. Once you have the process ID, you can display the amount of memory that has been allocated to the Vertica process using the...

Display Database Statement Counts by Type

James Knicely, Vertica Field Chief Technologist
April 9, 2019

There are many types of statements that can be executed in Vertica. Examples include queries, DDL (Data Definition Language), and utility statements. To summarize all the types of statements being executed, you can query the QUERY_PROFILES system table. Example: I’d like to view a count of each statement type across my cluster since the first...
Hand writing the text: Helpful Tips

Use Time Series Analytics to Generate a List of Dates at a Specific Start Date

James Knicely, Vertica Field Chief Technologist
April 8, 2019

Time series analytics evaluate the values of a given set of variables over time and group those values into a window (based on a time interval) for analysis and aggregation. This feature comes in handy if I need to generate a list of dates using some interval, for example 1 MONTH. Example: But why did...

Truncating a Timestamp

James Knicely, Vertica Field Chief Technologist
April 5, 2019

The built-in Vertica function DATE_TRUNC truncates date and time values to the specified precision. The return value is the same data type as the input value. All fields that are less than the specified precision are set to 0, or to 1 for day and month. Example: So did the current Millennium begin on January...

Accept the EULA When Installing Vertica

James Knicely, Vertica Field Chief Technologist
April 3, 2019

After installing Vertica, you have to ACCEPT the EULA the first time you start admintools on any Vertica node. To avoid having to do that, you can accept the EULA during the install using the --accept-eula or –Y option! Example: Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/InstallationGuide/InstallingVertica/InstallVerticaScript.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/InstallationGuide/InstallingVertica/InstallingVerticaSilently.htm Have fun!

Fun April Fool’s Day Trick Using Directed Queries

James Knicely, Vertica Field Chief Technologist
April 1, 2019

Directed Queries in Vertica encapsulate information that the optimizer can use to create a query plan. Directed queries can serve the following goals: Preserve current query plans before a scheduled upgrade. In most instances, queries perform more efficiently after a Vertica upgrade. In the few cases where this is not so, you can use directed...

Properly Identifying Identifiers

James Knicely, Vertica Field Chief Technologist
March 29, 2019

Vertica Identifiers (names) of objects such as schemas, tables, projections, column names, and so on, can be up to 128 bytes in length. Unquoted SQL identifiers must begin with one of the following: Non-Unicode letters: A–Z or a-z Underscore (_) Subsequent characters in an identifier can be any combination of the following: Non-Unicode letters: A–Z...

Run the Same Query Over and Over Again in vsql

James Knicely, Vertica Field Chief Technologist
March 28, 2019

The \g vsql meta-function sends the query in the input buffer to the server. This is great for interactively testing if a previous query run time was a fluke, and you don’t feel like typing in a long SQL statement again. Example: Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!

Display a Query Title in vsql

James Knicely, Vertica Field Chief Technologist
March 27, 2019

The \C vsql meta-function allows you to specify a title string to display with query output! Example: Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!

Convert a String to a Date, Including Unconventional Dates

James Knicely, Vertica Field Chief Technologist
March 26, 2019

The built-in Vertica function TO_DATE converts a string value to a DATE type. Example: We all know that there are 31 days in March. But are there 32 or 33 days in March? No. Vertica simply extends the date returned into the next month! Also note there is no 13th month! Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TO_DATE.htm Have...
Database Server Room

Find Out When Vertica Was Last Updated

James Knicely, Vertica Field Chief Technologist
March 25, 2019

You can check the data collector table DC_UPGRADES to see when you upgraded Vertica by version! Example: Helpful Link: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Glossary/DataCollector.htm Have fun!

Simulate NULLS FIRST and NULLS LAST in the ORDER BY Clause

James Knicely, Vertica Field Chief Technologist
March 20, 2019

When your query contains the ORDER BY clause to sort the result set, alphanumeric NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC), while integer NULL data does the opposite. Example: I want the NULL values in...