Vertica Blog

Vertica Blog

Best Practices

Tracking the Current Transaction Start Date and Time

James Knicely, Vertica Field Chief Technologist
March 14, 2019

The built-in Vertica function TRANSACTION_TIMESTAMP returns a value of type TIME WITH TIMEZONE, which represents the start of the current transaction. It’s very useful for keeping track of when the transaction started for a group of table inserts. Example: dbadmin=> INSERT INTO test SELECT 1, SYSDATE, TRANSACTION_TIMESTAMP(); OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO...
Modern Database Analytics

Single Sign-On for Support: New Login for Passport Users

Sarah Lemaire, Manager, Vertica Documentation
February 27, 2019

As part of the Micro Focus ongoing IT transformation, we have consolidated single sign-on systems to improve your experience of interacting with Micro Focus. All Passport users need to create a new account. To maintain your current access rights, you need to register with the same e-mail address you used with passport. If you use...
padlock with key on pile of chains

Data Day Texas: Keep Your Architecture Open and Avoid Mindset Lock-in

Paige Roberts, Vertica Open Source Relations Manager
February 11, 2019

Data Day Texas is an event in Austin that was started about nine years ago by an old acquaintance of mine, Lynn Bender, who founded Global DataGeeks. The one big theme that struck me this year as running through the whole conference was the highly cooperative landscape that has developed between proprietary and open source...
Three 3D arrows, different colors pointing in different directions

Another Way to De-Duplicate Table Rows: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 8, 2019

To remove duplicate table rows it is typically better (i.e. faster) to create a temporary table that contains all of the distinct rows from a table, drop the original table, then rename the temp table to the original table’s name. The issue with that solution is that you’ll need to be sure that the original...

Combining Functions to Extract Discrete Data From a String: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 7, 2019

Vertica has 1000s of built-in functions including String Functions, which perform conversion, extraction, or manipulation operations on strings or return information about strings. Regular Expression Functions let you perform pattern matching on strings of characters. There is real power in combing Vertica functions to accomplish almost any task in SQL. I want to extract the...

Finding All Columns Storing Some Value: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 4, 2019

A long time ago a database developer asked me how she could produce a list of all table columns in the database which store a given value. Note that she was in the process of normalizing tables and wanted to find every fact table that referenced a particular string value to replace it with a...

Viewing External Table Definitions: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 1, 2019

An alternative to importing data into Vertica is to query it in place. For that we use external tables. Once an external table is created you can view its definition via the TABLES system table. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/CreatingExternalTables.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/Intro.htm Have fun!

Display Tables Referenced by a View: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
November 29, 2018

Jim Knicely authored this tip. Vertica 9.2 introduces the new VIEW_TABLES system table that shows details about view-related dependencies, including the table that reference a view, its schema, and owner. Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_TABLES.htm Have fun!
Three 3D arrows, different colors pointing in different directions

Calculate the Catalog Size in Memory on Each Node: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 28, 2018

The RESOURCE_POOL_STATUS system table provides the current state of built-in and user-defined resource pools on each node, including memory usage. The METADATA built-in pool tracks memory allocated for catalog data and storage data structures. Knowing those two facts, we can calculate catalog memory usage on all nodes by querying the system table RESOURCE_POOL_STATUS for the...

Labeling Queries: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 28, 2018

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! 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!
Modern Database Analytics

Create a User Defined SQL Function to Calculate Fibonacci Numbers: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 26, 2018

The Fibonacci Sequence is the series of numbers (i.e. 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ...) where the next number in the sequence is found by taking the sum of the previous two numbers. Calculating these numbers in SQL would be a bit complicated if it were not for the “Golden...
Programmer

Calculate Future Thanksgiving Dates: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
November 21, 2018

Jim Knicely authored this tip. In the United States, Thanksgiving is always celebrated on the fourth Thursday of November. In Canada, Thanksgiving is celebrated the second Monday in October. So that you don’t miss another holiday at home with mom, make sure to plan ahead and calculate (using SQL) future Thanksgiving dates! Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Date-Time/NEXT_DAY.htm...