Vertica Blog

Vertica Blog

Quick Tips

Mimicking Enumerated Types: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 6, 2018

I used to work a lot with MySQL. It had a cool data type called "Enumerated Types". Example in MySQL: How do we do this in Vertica? With a Check Constraint! Example in Vertica: Helpful link: https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htm Have fun!

Changing the Field Separator in VSQL: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 5, 2018

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It’s very common to want to export data in CSV (Comma-Separated Values) format. To do that you can change the default | (vertical bar) field separator to a comma via the fieldsep option of the pset meta-command. Wait...

Monitoring Resource Pool Cascade Events: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 4, 2018

You can define secondary resource pools to which running queries can cascade if they exceed the initial pool's RUNTIMECAP. The RESOURCE_POOL_MOVE System Table displays the cascade event information on each node. There you can find helpful information like the source and target pools and why the cascading event occurred! Helpful link: https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_POOL_MOVE.htm Have fun!

Setting the Table-Level Active Partition Count: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
August 30, 2018

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...

Exporting to Parquet: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
August 29, 2018

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...
Modern Database Analytics

Connection Load Balancing: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
August 28, 2018

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

Phil Molea, Sr. Information Developer, Vertica
August 27, 2018

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

Phil Molea, Sr. Information Developer, Vertica
August 23, 2018

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...
Database Server Room

Listing Invalid Views: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
August 22, 2018

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

Phil Molea, Sr. Information Developer, Vertica
August 21, 2018

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

Phil Molea, Sr. Information Developer, Vertica
August 21, 2018

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';...

Cascading Schema Ownership

Sarah Lemaire, Manager, Vertica Documentation
August 13, 2018

Jim Knicely authored this tip.</br? Yesterday’s quick tip revealed that as of Vertica 9.1.1, you can transfer the ownership of a schema to another user. But what about the underlying schema objects (i.e., TABLES, VIEWS, etc.)?</br? By default, the ALTER SCHEMA…OWNER TO command does not affect ownership of objects in the target schema or the...