Vertica Blog

Phil Molea

Sr. Information Developer, Vertica

Phil developed technical documentation in the areas of security and diagnostics for the Vertica Analytics Platform that enabled companies to extract value from their data at the speed and scale they need to thrive in today’s economy.

(Sadly, Phil passed away recently. He will be missed.)

Connect With Phil on

Self-Descriptive Constraint Names: Quick Tip

Jim Knicely authored this tip. Constraints set rules on what data is allowed in table columns and help maintain data integrity. PRIMARY KEY, REFERENCES (foreign key), CHECK, and UNIQUE constraint must be named. If you omit assigning a name, Vertica automatically assigns one. Those Vertica-created constraint names aren’t very descriptive. It’s a good idea to...

Setting the Table-Level Active Partition Count: Quick Tip

Jim Knicely authored this tip. The Tuple Mover assumes that all loads and updates to a partitioned table are targeted to one or more partitions that it identifies as active. In general, the partitions with the largest partition keys—typically, the most recently created partitions—are regarded as active. As the partition ages, it commonly transitions to...

Come Join our Team! Vertica is Hiring!

Vertica is looking for outstanding software developers, QA engineers, and technical writers, full-time and summer interns, to work in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product: a very large-scale database. THINK BIG! If you like to noodle over algorithms, hate to see...
Three 3D arrows, different colors pointing in different directions

How to recognize strings containing nbsp

Maurizio Felici authored this post. Word Processors and Web forms often use the non-breaking space character (nbsp) to prevent line breaks. This character looks the same as a normal space, but uses a different Unicode character with its own UTF-8 encoding. If you inadvertently cut and paste an nbsp character in your database strings, any...

Exporting to Parquet: Quick Tip

Jim Knicely authored this tip. Vertica can export a table, columns from a table, or query results to files in the Parquet format. dbadmin=> EXPORT TO PARQUET (directory = '/home/dbadmin/dim') AS SELECT * FROM dim; Rows Exported --------------- 1 (1 row) One restriction is the path to export must not exist. How do I get...

Connection Load Balancing: Quick Tip

Jim Knicely authored this tip. Each client connection to a host in the Vertica cluster requires a small overhead in memory and processor time. If many clients connect to a single host, this overhead can begin to affect the performance of the database. You can attempt to spread the overhead of client connections by dictating...
Programmer

Dealing with Subquery Restrictions: Quick Tip

Jim Knicely authored this tip. A subquery is a SELECT statement embedded within another SELECT statement. The embedded subquery is often referenced as the query's inner statement, while the containing query is typically referenced as the query's statement, or outer query block. One restriction of a subquery is that you cannot have a correlated expressions...

Ensure Data Integrity with Check Constraints: Quick Tip

Jim Knicely authored this tip. To help safeguard against “bad” data creeping into your database, Vertica supports Table “Check Constraints”. They specify a Boolean expression that evaluates a column's value on each row. If the expression resolves to FALSE for a given row, the column value is regarded as violating the constraint and Vertica will...

Listing Invalid Views: Quick Tip

Jim Knicely authored this tip. If any of the tables referenced in a view are dropped, the view becomes invalid. dbadmin=> INSERT INTO base_table SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> CREATE VIEW base_table_vw AS SELECT c FROM base_table; CREATE VIEW dbadmin=> SELECT * FROM base_table_vw; c --- 1 (1 row) dbadmin=> DROP TABLE...

Maximum Number of Rows per Load: Quick Tip

Jim Knicely authored this tip. I’m often asked if there is a maximum number of rows that Vertica can load using the bulk loader COPY command. The answer to that question is “yes”, but the number is really, really big (i.e. 2^63)! How big is that? Note that the number is actually 9,223,372,036,854,775,807 because that...

Setting the Max Memory Available by Query: Quick Tip

Jim Knicely authored this tip. Vertica 9.1.1 introduces the new Resource Pool parameter MAXQUERYMEMORYSIZE. Its value represents the maximum amount of memory the pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error. dbadmin=> CREATE RESOURCE POOL limited_by_query MAXQUERYMEMORYSIZE '1K';...

Fast Data Loading with Vertica

Curtis Bennett authored this post. Vertica is well known for its blinding query performance at big data scale, but it can also insert data at very high rates of speed. It can even load data non-stop while being queried, thus enabling real-time analysis of data. Basic Loading Methods There are many ways of loading data...