Vertica Blog

Vertica Blog

DBadmin

Drop More than One Table at a Time: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 9, 2019

The DROP TABLE command removes a table and its projections. You can drop more than one table at a time by specifying a comma delimited set of tables! That was easy. How about dropping more than one table? At first you might be tempted to run a DROP TABLES command to drop multiple tables, but...

Allowing Users to Query a View Owned by Another User: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 8, 2019

As we learned in yesterday’s Vertica Quick Tip A View Owner Needs Access to the Underlying Objects, the owner of a View must have direct access to the underlying objects referenced by the view! But what if another user wants to read a view owned by another user? In this case, not only must the...

A View Owner Needs Access to the Underlying Objects: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 7, 2019

You can use the ALTER VIEW … OWNER TO command to change a Vertica database view’s ownership. It’s important to know that a View’s owner must also have access to the underlying objects referenced by the view! Exanple: Great! The user, USER1, can read from the view as expected. But what happens when I change...

Preserving Objects Owned by a Dropped User: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 4, 2019

When you drop a user with the CASCADE option, all objects owned by that user are lost forever! If you’d prefer to save those objects, you first set the GlobalHeirUserName security parameter to a user who will inherit objects after their owners are dropped. This setting ensures preservation of data otherwise lost. Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/SecurityParameters.htm...

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

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

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

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