Vertica Blog

Vertica Blog

DBadmin

Busy traffic jam

Find the Busiest Hour in the Day for I/O

James Knicely, Vertica Field Chief Technologist
August 2, 2019

The IO_USAGE system table provides disk I/O bandwidth usage history for the system. You can query it to find out interesting IO statistics like which hour of the day has the most IO usage on your Vertica cluster! Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/IO_USAGE.htm Have fun!
Tips and Tricks Orange Button

Theoretical Maximum Number of Rows Per Table Per Node

James Knicely, Vertica Field Chief Technologist
August 1, 2019

The Vertica Table size limit is documented to be 2^64 rows per node. Just how big is that number? That’s 18+ Sextillion rows! I’ve been tracking in a Vertica table, the number of text messages my daughter has sent/received since she first got a cell phone at the age of twelve. Now twelve years later,...

Streaming Data in One Line!

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
July 31, 2019

Remember that game show, "Name That Tune", where contestants were challenged to name a tune in as few notes as possible? Today's tip converts that for Vertica Big Data, showing how we can ingest streaming data in just one line! A simple way to stream data is to write CSV rows to a network socket....
Digital image of a trash can made of data points on blue background

Watch those Delete Vectors!

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
July 30, 2019

Vertica is very good at ingesting data, compressing it, and querying at high speed. The trade-off here is that the data is stored in large block files called ROS containers. These containers can grow to large sizes, sometimes over 10 GB, and this makes it impractical to decompress and edit the files during updates and...
Helpful Tips message on post-it note

Restore a Single Database View from a Backup

James Knicely, Vertica Field Chief Technologist
July 29, 2019

The Vertica Backup and Recovery (vbr) utility allows you to back up and restore either the full database, or one or more schema and table objects of interest. It’s a great tool for restoring a table if someone accidentally dropped it or deleted a significant amount of data by mistake. But what about a database...
Helpful Tips text with hand and marker over data center background

Avoid 1:1 Relationships in Your Data Model

James Knicely, Vertica Field Chief Technologist
July 24, 2019

1:1 relationships exist in row-oriented databases because of performance concerns. As a columnar database, there is no reason in Vertica to separate large tables out into separate structures. That is, instead of joining two large fact tables together, combine them into a single table! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm Have fun!
Lock

Looking into details of locking

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
July 23, 2019

(thanks @skeswani for query and advice) If you have a lot of concurrent queries, especially mixing DDL and DML, you might see lock contention. If you'd like to see how locks interact in your system, the following queries generate a temporary table with Gantt chart to show an ordered list of locks over time: Query (be...
Hand writing the text: Helpful Tips

Analyzing JSON Already Loaded into Vertica Regular Tables

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
July 22, 2019

Vertica flex tables allow you to query data in JSON format. But what if you've imported JSON objects into VARCHAR already? Here's how you can extract the JSON into flex tables without exporting and importing. Let's create a sample table with a JSON column to convert: Now let's create a flex table: Flex tables use...
Data Definition Language in red 3D text in a field of white 3D ones and zeros

Export CREATE OR REPLACE DDL for Database Views

James Knicely, Vertica Field Chief Technologist
July 19, 2019

The EXPORT_OBJECTS Catalog Management Function generates a SQL script you can use to recreate non-virtual catalog objects on another cluster. When you export a database view, Vertica includes the CREATE VIEW DDL in the output. But what if you want the DDL to be CREATE OR REPLACE VIEW? You can make that change with the...
Vintage businessman concept pointing on the wall wearing futuristic helmet at office

Hash Function Result Depends on Data Type and Sometimes Size

James Knicely, Vertica Field Chief Technologist
July 18, 2019

The Vertica HASH function calculates a hash value over the function arguments, producing a value in the range 0
Helpful Tips in blue text with magnifying glass

Loading Data Stored in Scientific Notation to an Integer Column

James Knicely, Vertica Field Chief Technologist
July 17, 2019

Scientific notation is a way of expressing numbers that are too big or too small to be conveniently written in decimal form. Vertica can display numbers in scientific notation in decimal form with ease. That is, unless the value is a string! You first have to convert the VARCHAR to a NUMERIC and then to...
Red and blue square dots fly from scattered into two neat rows arranged by color.

Auto-Projection Column Sort Order

James Knicely, Vertica Field Chief Technologist
July 8, 2019

Auto-projections are superprojections that Vertica automatically generates for tables, both temporary and persistent. Vertica uses the current value of the MaxAutoSortColumns configuration parameter to determine which table columns are included in the auto-projection sort order. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/ProjectionParameters.htm Have fun!