Vertica Blog

Vertica Blog

VSQL

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!

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...

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...

Changing the Field Separator in VSQL: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 5, 2018

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It’s very common to want to export data in CSV (Comma-Separated Values) format. To do that you can change the default | (vertical bar) field separator to a comma via the fieldsep option of the pset meta-command. Wait...

Displaying the Current Schema

James Knicely, Vertica Field Chief Technologist
August 8, 2018

Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can use the CURRENT_SCHEMA function to display the name of the current schema (i.e., the first “valid” schema in the user’s search path). dbadmin=> SHOW search_path;...
Programmer

Vertica Quick Tip: Empty String Vs. NULL

James Knicely, Vertica Field Chief Technologist
May 30, 2018

An empty string ('') is treated as a NULL value in Oracle, while in Vertica an empty string is not treated as a NULL value. So when using Vertica, if you want to indicate that a column value is unknown, be sure to use NULL and not an empty string! In Oracle: Are they the...