Vertica Blog

Announcing Vertica in Eon Mode for Amazon Linux 2, now available in AWS Marketplace

Vertica is a blazingly fast SQL analytics database, enabling enterprises to access and derive meaningful insight into big data in sub-seconds or minutes rather than hours or days. Vertica powers the world’s most data driven organizations, delivering unmatched speed and scale with the full suite of advanced analytics and in database machine learning. Vertica for […]

Changing the Data Type of a Column in an External Table: Quick Tip

Jim Knicely authored this tip. External tables let you query data stored in files that are accessible to the Vertica database, but not managed by it. When you create the external table, you have to provide column names along with their data types. What happens if you get a data type incorrect? Luckily, you can […]

Limiting a User’s Open Session Count: Quick Tip

Jim Knicely authored this tip. By default, a user can have an unlimited number of connections across the database cluster. Example: [dbadmin@s18384357 ~]$ vsql -U jim -w ‘pw’ Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q […]

NULL Equals NULL with NULLSEQUAL: Quick Tip

Jim Knicely authored this tip. The Vertica CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is similar to case and if/then/else statements in other languages. Example: dbadmin=> SELECT CASE 1 WHEN 1 THEN ‘It is 1’ ELSE ‘It is not 1’ END; case ——— It is […]

Skipping Records with Unspecified JSON Fields

Serge Bonte and Jim Knicely authored this post. Vertica provides a built-in file parser named FJSONPARSER that parses and loads a JSON file. This file can contain either repeated JSON data objects (including nested maps) or an outer list of JSON elements. For a flex table, the parser stores the JSON data in a single-value […]

Handling NULL Equality in a WHERE Clause: Quick Tip

Jim Knicely authored this post. The predicate SQL element (i.e., the WHERE clause) is a truth-test. If the predicate test is true, it returns a value. Each predicate is evaluated per row, so that when the predicate is part of an entire table SELECT statement, the statement can return multiple results. Sometimes you might want […]

Introducing the Vertica ML-Python Library

This blog post was authored by Soniya Shah. One of the coolest things about working at Vertica is our amazing intern program, which often leads to full-time hires. Last year, the Vertica-ML-Python library, also known as vpython, was started as an internship project by Badr Ouali. A year later, he works for Vertica full time […]

Be Careful with the Sequence CACHE Value

Jim Knicely authored this tip. The default session cache for a sequence is 250,000. Although you can change the cache value of a sequence, setting the value too low can adversely affect performance! Example: dbadmin=> SELECT COUNT(*) FROM ten_thousand_records; COUNT ——- 10000 (1 row) dbadmin=> CREATE SEQUENCE default_cache; CREATE SEQUENCE dbadmin=> CREATE SEQUENCE non_default_cache CACHE […]

Faster CTAS Statements: Quick Tip

Jim Knicely authored this tip. In a CREATE TABLE statement, you can specify an AS clause to create a table from a query (a.k.a. CTAS statement). When dealing with a large SELECT statement result set, your CTAS should perform much better if you specify the DIRECT load method! Example: dbadmin=> SELECT TO_CHAR(COUNT(*), ‘999,999,999,999’) row_count FROM […]

Analyze Statistics at the Schema Level (Part 2): Quick Tip

Jim Knicely authored this tip. The ANALYZE_STATISTICS function only accepts a table/projection/column name as input. In yesterday’s Vertica Quick Tip we learned how to get Vertica to generate and execute ANALYZE_STATISTICS SQL statements, one for each table in a given schema. It was an okay solution, but not very convenient. A better option would be […]