Vertica Blog

Vertica Blog

DBadmin

Busy traffic jam

Find the Busiest Hour in the Day for I/O

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: Have fun!
Tips and Tricks Orange Button

Theoretical Maximum Number of Rows Per Table Per Node

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!

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!

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

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

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: Have fun!
Lock

Looking into details of locking

(thanks  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

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

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

The Vertica HASH function calculates a hash value over the function arguments, producing a value in the range 0 <= x < 2^63. When calculating a hash value, the HASH function takes into consideration the data type and size of the original value. Be careful when comparing hashed column values from different tables where the...
Helpful Tips in blue text with magnifying glass

Loading Data Stored in Scientific Notation to an Integer Column

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

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: Have fun!