Vertica Blog

Vertica Blog

Quick Tips

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!

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!
Hand with suit cuff flips shiny gold coin

Coin Flip User Defined SQL Function

In the past when my wife and I couldn’t decide which of two restaurants to go to grab dinner, we opened the Coin Flip app on one of our phones to let it decide. Now I let Vertica choose for us via a simple User Defined SQL Function! Helpful Links: Have fun!
Quick Tip - blue button

Non-DBADMIN Access to System Tables (Table by Table)

The DBADMIN can assign a delegate the SYSMONITOR role to grant full access to system tables without granting full DBADMIN access. The SYSMONITOR role applies to all tables that have the flag IS_MONITORABLE set to true in the SYSTEM_TABLES system table. What if I want a non-DBADMIN user to have full access to only a...