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

List Table Rows by ROS Container: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 3, 2019

A ROS (Read Optimized Store) container is a set of rows stored in a particular group of files. ROS containers are created by operations like Moveout or COPY DIRECT. You can query the STORAGE_CONTAINERS system table to see ROS containers. You can use the LEAD_STORAGE_OID function to list the rows from a table that are...
Three 3D arrows, different colors pointing in different directions

Tracking Save Points: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 2, 2019

The SAVEPOINT SQL command creates a special mark, called a savepoint, inside a transaction. A savepoint allows all commands that are executed after it was established to be rolled back, restoring the transaction to the state it was in at the point in which the savepoint was established. Savepoints can be nested so it can...

Keep Data Collector Information for a Set Interval: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 19, 2018

The Data Collector retains history of important system activities and records essential performance and resource utilization counters. You probably know that you can set a size restraint (memory and disk space in kilobytes) for the specified Data Collector table on all nodes. But if you are looking to keep records for some period of time,...

Subscribe For Email Updates

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

Sign-up

Modern Database Analytics

Turn Off Row Counts in VSQL: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 19, 2018

he SET NOCOUNT command in SQL Server will stop the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. You can accomplish the same thing in Vertica by shutting off the footer. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/CommandLineOptions/PAssignment--psetAssignment.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/psetNAMEVALUE.htm Have fun!
Programmer

Generate a Notice Not an Error When Adding a Duplicate Column to a Table: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 17, 2018

When adding a column to a table in Vertica 9.2, the optional clause IF NOT EXISTS generates an informational message if the column already exists under the specified name. If you omit this option and the column exists, Vertica generates a ROLLBACK error message. dbadmin=> ALTER TABLE test ADD COLUMN c2 INT; ROLLBACK 3145: Duplicate...
surprised data analyst

What You Never Knew About Vertica Could Surprise You

Paige Roberts, Vertica Open Source Relations Manager
December 17, 2018

I just started working on the Vertica team. As the “new guy,” my first few weeks of work have been largely about cramming as much Vertica information into my brain as possible in the shortest time possible. That’s my favorite part of working in the big data analysis world. You always have to keep learning....

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

Swapping Schemas: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 13, 2018

Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. But be careful with hardcoded schema names in SQL code like views! They won’t be swapped. dbadmin=> create schema schema2; CREATE SCHEMA dbadmin=> create table schema1.test1 (c int); CREATE TABLE dbadmin=> create table schema2.test1...

Vertica Logos

Soniya Shah, Information Developer
December 13, 2018

Confirming if a Data Load was Apportioned: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 13, 2018

Jim Knicely authored this tip. When running a COPY command, a parser can use more than one database node to load a single input source in parallel. This approach is referred to as apportioned load and is often much faster than loading from a single node. One way of confirming that a data load took...
Database Server Room

Sort Data from Within a View: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 11, 2018

If a view definition includes an ORDER BY clause, Vertica ignores it. But you can force Vertica to sort the view data on the ORDER BY columns by adding a LIMIT clause. If you don’t know how many records to limit, simply limit by some arbitrary large value! dbadmin=> CREATE VIEW some_view AS dbadmin-> SELECT...

Copy Table History: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 11, 2018

The COPY_TABLE function copies one table to another… FAST! Down the road I might want to know if a table was created as a copy using the COPY_TABLE function. To find out, I can query the data collector table DC_COPY_TABLES. dbadmin=> \timing on Timing is on. dbadmin=> SELECT copy_table('big_fact', 'big_fact_copy'); copy_table ---------------------------------------------------------------------- Created table big_fact_copy....

Restarting a Downed Node: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 6, 2018

I’m a big fan of scripting with admintools which provides us with many database tools. One of those awesome tools, that I just became familiar with, is command_start. It allows us to start a downed node, with one caveat: you have to run the command on the node that is down. Here I’ll use the...