Vertica Blog

James

Vertica Field Chief Technologist

I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!

Connect With James on

Helpful Tips message on post-it note

Export to CSV File With Fields Enclosed by Quotes

James, 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, 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, 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, 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...
Compass rose with true north pointing to "Helpful Tips" text

Which Database Mode Is My Database Using?

James, 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, 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...
Construction site crane building a blue SQL 3D text.

Get the Row Count from an Outer Table Join

James, Vertica Field Chief Technologist
October 22, 2019

Aggregate functions summarize data over groups of rows from a query result set. When using an aggregate function like COUNT with an "*" or "1" parameter value, you may get a different result when the query implements a LEFT join verses an INNER join. If you want a row count of just the rows from...
Helpful Tips message on post-it note

DIRECT Is Now the Default Load Type

James, Vertica Field Chief Technologist
October 19, 2019

Write Optimized Store (WOS) is a memory-resident data structure for short-term data storage while Read Optimized Store (ROS) is a highly optimized, read-oriented, disk storage structure, organized by projection. Prior to Vertica 9.3, by default, Vertica initially loads data into WOS. For databases created in version 9.3 and later, Vertica now uses a default load...
Create Read Update Delete in red 3D letters surrounded by 3D ones and zeroes

Vertica 9.3 Supports UPDATE and DELETE Operations on Tables Having Live Aggregate Projections!

James, Vertica Field Chief Technologist
October 17, 2019

A live aggregate projection contains columns with values that are aggregated from columns in its anchor table. When you load data into the table, Vertica aggregates the data before loading it into the live aggregate projection. On subsequent loads (for example, through INSERT or COPY) Vertica recalculates aggregations with the new data and updates the...
Hand writing the text: Helpful Tips

Tracking Raw Schema Size Over Time

James, Vertica Field Chief Technologist
September 25, 2019

There is no way to go back in time to see how large a schema was a day/month/year ago. But you can use the AUDIT function to get a “raw” size of a schema and track growth over time by storing the results from a daily audit. Using cron, you can run this daily. Helpful...
Photo of actual roadrunner running on a road showing yellow stripe in the middle

Have VSQL Beep Upon Command Completion

James, Vertica Field Chief Technologist
September 6, 2019

There are a lot of cool command line options available to VSQL. One of my favorites is the –b option which causes Vertica to “beep” when a command completes. This is very useful if you are running a very long sequence of SQL commands via a script. You can go off and do other things...
hands sweeping into a dustpan

Easy Development Schema Cleanup (i.e., Have Vertica Drop a Bunch of Tables for You)

James, Vertica Field Chief Technologist
September 5, 2019

On a development database, I have a lot of tables in the PUBLIC schema having similar names starting with “test_”. I’d like to drop all of these tables quickly. To do that, I can have Vertica generate the DROP commands and then execute them for me! P.S. Be careful with this command! Make sure when...