Vertica Blog

OpenText Vertica 23.3 – the Smarter Data Lakehouse

Posted July 31, 2023 by Paige Roberts, Vertica Open Source Relations Manager

Read More

Unveiling the Most Recent Version of the Vertica Grafana Data Source Plugin

Amrita Akshay, Information Developer
October 12, 2023

With over 380K downloads, the Vertica Grafana Data Source plugin just got an upgrade! The plugin was migrated from the deprecated older Grafana toolkit to align with Grafana's new Create-Plugin tool. This accelerates the plugin development with their modern build set up that requires no additional configuration. Additionally, the Vertica SQL Go driver received an...
Quick Tip on a blue enter key on a keyboard

Setting Session Authorization to Troubleshoot

Sruthi Anumula, Senior Database Support Engineer
September 29, 2023

There are possible scenarios in which a dbadmin would want to run queries as another user to troubleshoot or test. You can use SET SESSION AUTHORIZATION to impersonate another user and run queries. Let's understand this with an example. Here we create a user named test, resource pool named userpool, and make this a default...

Special Date/Time Formatting: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 15, 2018

Jim Knicely authored this tip. Vertica supports several special date/time values for our convenience. All of these values need to be written in single quotes when used as constants in SQL statements. My favorite is ALLBALLS which is named so because the time digits look like balls (I had to Google that). Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Expressions/DateTimeExpressions.htm...
Three 3D arrows, different colors pointing in different directions

Change the VSQL Result Set Border: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 11, 2018

Jim Knicely authored this tip. vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. The results from vsql are formatted nicely with a border between columns. You are probably used to viewing border style 1, the default, but you may prefer border styles 0 or 2! Helpful...

Find Which System Tables Have a Particular Column Name: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 10, 2018

Jim Knicely authored this tip. There are over 180 built-in Vertica system tables in the V_CATALOG and V_MONITOR schemas. Trying to remember which system tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.SYSTEM_COLUMNS system table! Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/SYSTEM_COLUMNS.htm Have fun!

Subscribe For Email Updates

Sign-up and select Vertica in your preferences to receive our monthly Vertica newsletter.

Sign-up

Modern Database Analytics

Vertica is Coming to Harvard: Friday, October 12, 2018!

Sarah Lemaire, Manager, Vertica Documentation
October 10, 2018

Vertica is looking for outstanding software engineers and QA engineers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big Data workloads....
Programmer

Find Which Tables Have a Particular Column Name: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 9, 2018

Jim Knicely authored this tip. Your Vertica database probably contains hundreds, if not thousands, of tables. Trying to remember which tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.COLUMNS system table! Which tables have a column named “DateKey”? Which tables have a column with a name that contains the text...

Database Storage – Raw and Compressed Size: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 8, 2018

Jim Knicely authored this post. One of the files generated by the /opt/vertica/scripts/collect_diag_dump.sh script details the raw and compressed size for every table in the Vertica database. At the bottom of the report there is a “total” line that shows the database raw and compressed size! Example Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Diagnostics/ExportingProfilingData.htm Have fun!

Explore Popular Topics

SQL
DBadmin
Advanced Analytics
machine learning
Best Practices
Cloud
VSQL
Eon Mode
Analytics
Partner Integrations
Vertica
Open Source
AWS
IoT
Time series

DbVisualizer Free for Vertica Distribution Updates

Phil Molea, Sr. Information Developer, Vertica
October 8, 2018

Stephen Crossman authored this post Recently, there have been some changes in how DbVisualizer Free for Vertica is distributed. Previously, there were standard DbVisualizer Free and Pro Edition distributions available on the DbVisualizer web site, and there was a special DbVisualizer Free for Vertica distribution available on the Vertica Marketplace. Now, in an effort to...

Display Last SQL Run Time: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 4, 2018

Jim Knicely authored this post. When the \timing meta-command in vsql is set to “on”, Vertica will return how long (in milliseconds) each SQL statement runs. That’s great! But what if you forgot to turn it on prior to executing a long running query? Instead of turning it on then re-executing that long running query,...

Displaying the Administration Tools Process ID: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 3, 2018

Jim Knicely authored this post. From within vsql you can run the Linux ps command to display the Administration Tools process on the local node. However, it’s a lot easier to use the GETPID function. Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Monitoring/Vertica/MonitoringProcessStatusPs.htm Have fun!
Database Server Room

Rotating Column Data Using SQL Analytics: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 2, 2018

Jim Knicely authored this post. Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis, reporting tasks, and allow for some cool data manipulation. I have the following table of names where each name has a unique ID. I want to rotate the values in column NAME up by...

Return Zero Instead of NULL: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 1, 2018

Jim Knicely authored this post. The built-in Vertica function ZEROIFNULL evaluates to 0 for NULL values in a table’s column. Whoa! What happened in that last statement? Since NULL by itself does not have a data type, you’ll need to cast it to a valid data type for the function! Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Null/ZEROIFNULL.htm Have fun!

Viewing Previously Generated Explain Plans: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 27, 2018

Jim Knicely authored this post. The EXPLAIN command returns a formatted description of the Vertica optimizer's plan for executing the specified statement. dbadmin=> EXPLAIN SELECT * FROM fact JOIN dim USING (c2); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT * FROM fact JOIN dim USING (c2); Access Path: +-JOIN MERGEJOIN(inputs presorted) [Cost: 25,...