Vertica Blog

Vertica Blog

Quick Tips

Electronic medical record on digital smart device

Vertica in Good Health: Ingesting, Securing, and Analyzing Healthcare Data

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
September 29, 2020

Electronic Medical Records and new personal healthcare technology, particularly medical devices, generate a huge amount of data. Organizations including Cerner and Philips Healthcare rely on Vertica for data analysis. How can Vertica help you analyze this data? Vertica excels at data ingest and provides hundreds of built-in SQL and analytic functions with broad integration with...
Quick Tip - blue button

Copy a Table with an Identity to a Table with an Identity on the Same Database Keeping the Identities in Sync

James Knicely, Vertica Field Chief Technologist
May 25, 2020

An IDENTITY associates a table column with a sequence. This sequence automatically increments the column value as new rows are added. There are some noteable restrictions on IDENTITY columns: You cannot INSERT values manually into an IDENTITY column You cannot UPDATE the values of an IDENTITY column once they has been populated via the sequence...
Visual Storytelling

VSQL, Visual Storytelling through Query Language

Moshe Goldberg, Vertica System Engineer
May 21, 2020

This tip expands on my earlier post: “Pure Vertica SQL Graph Flavors” Visual story telling has not just the ability to capture attention, it make things simple to understand. Consider the following to boost your query result set readability. What is the visual correlation between CPU utilization and Canary Queries elapsed time?      ...
Helpful Tips message on post-it note

Export to CSV File With Fields Enclosed by Quotes

James Knicely, Vertica Field Chief Technologist
March 29, 2020

It's easy to export table data to a CSV file using vsql. Although there isn't a VSQL option to enclose each output field in quotes, you can accomplish that task with the Linux sed command. Once the formatting looks good, you can redirect the output to your CSV file: Helpful Links: Exporting Data Using VSQL...
Helpful Tips text with hand and marker over data center background

Define a Vertica Flattened Table Column as Having Both DEFAULT and SET USING Constraints

James Knicely, Vertica Field Chief Technologist
January 31, 2020

Columns in a flattened table can query other tables with constraints DEFAULT and SET USING . Vertica executes DEFAULT queries only on new rows when they are added to the flattened table, through load operations such as INSERT and COPY. Thereafter, changes in the original data sources have no effect on the flattened table. Vertica...
Helpful Tips in blue text with magnifying glass

Viewing Parquet Export Events More Easily

James Knicely, Vertica Field Chief Technologist
January 27, 2020

The EXPORT TO PARQUET command exports a table, columns from a table, or query results to files in the Parquet format. When you run EXPORT TO PARQUET information about the files created during the export is stored in the Vertica log. It's no fun combing through a Vertica log looking for those particular records. Good...
Hand writing the text: Helpful Tips

Clearing the Screen in VSQL

James Knicely, Vertica Field Chief Technologist
January 27, 2020

Lots of old school folks use VSQL for administrative tasks. As you enter commands, eventually the command prompt will end up at the bottom left of the screen making it a little difficult to read any additional output from your commands. You can clear the screen and move the prompt back up to the top...
Business man showing profitable stock market graph hologram over a tablet.

Finding Patterns in Stock Prices With Vertica

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
January 21, 2020

Vertica can ingest data from many sources and enable SQL-based preparation and analytics. Here is another real-world example: Let's use Vertica to find patterns in a public data set of stock and (Exchange-Traded Fund) ETF prices from Kaggle. I’ll use the following data source in this post: https://www.kaggle.com/borismarjanovic/price-volume-data-for-all-us-stocks-etfs This data source provides several thousand files...
Compass rose with true north pointing to "Helpful Tips" text

Which Database Mode Is My Database Using?

James Knicely, Vertica Field Chief Technologist
January 14, 2020

You can run Vertica in either Eon Mode or Enterprise Mode. The primary difference between Eon and Enterprise Modes is where they store data. Eon Mode stores data in a shared object store called communal storage, while Enterprise Mode stores data across the filesystems of the database nodes. If you are working with a new...
Tips and Tricks Orange Button

Moving a Table to a New Schema and Inheriting the New Schema Privileges

James Knicely, Vertica Field Chief Technologist
December 6, 2019

You can move a table from one schema to another schema with a simple ALTER TABLE command. But, you might be surprised that the table will not inherit the new schema’s privileges even if the new schema was created using the DEFAULT INCLUDE PRIVILEGES directive. After moving the table you will need to manually alter...

Are Your Columns too Wide?

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
November 14, 2019

This tip expands on the earlier post on encoding and compression at Checking and Improving Column Compression and Encoding When you have millions to billions of rows, data type becomes a bit more important: even an extra 10 bytes per row across a huge data set will impact storage or performance (or both!). When I...

Checking and Improving Column Compression and Encoding

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
November 7, 2019

When working with terabytes of data, storage and transfer become major time and cost sinks. Vertica can help minimize storage cost and transfer time with column compression and encoding. How can we identify Vertica tables that might benefit from compression? Information about column size and current compression is stored across column_storage and projection_columns table. The...