Vertica Blog

Vertica Blog

DBadmin

Using admintools to List Nodes for a Particular Database: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 13, 2018

Jim Knicely authored this post. The Vertica Administration tools allow you to easily perform administrative tasks, such as quickly viewing information and statuses of all database nodes via the list_allnodes tool. If I only want to see the IP addresses and states of the nodes from a particular database, I could combine the Linux grep...

Database and Node Uptime: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 10, 2018

You can query the DATABASES system table to find out the last time your Vertica database started and you can get the cluster node up times by querying the NODE_STATES system table. dbadmin=> SELECT node_name, MAX(event_timestamp) node_uptime dbadmin-> FROM node_states dbadmin-> WHERE node_state = 'UP' dbadmin-> GROUP BY node_name dbadmin-> ORDER BY node_name; node_name |...

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!

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

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

Converting CHAR and VARCHAR to BINARY or VARBINARY

Sarah Lemaire, Manager, Vertica Documentation
August 16, 2018

Jim Knicely authored this tip. As of Vertica 9.1.1, explicit coercion (casting) from CHAR and VARCHAR data types to either BINARY or VARBINARY data types is supported! Prior to Vertica 9.1.1: dbadmin=> INSERT INTO test_coercion SELECT 'Convert me!'; OUTPUT -------- 1 (1 row) dbadmin=> SELECT c1::VARBINARY FROM test_coercion; ERROR 2366: Cannot cast type varchar to...

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

Change the Owner of a Schema

Sarah Lemaire, Manager, Vertica Documentation
August 13, 2018

Jim Knicely authored this tip. As of Vertica 9.1.1, you can now transfer the ownership of a schema to another user! dbadmin=> CREATE USER etl_user; CREATE USER dbadmin=> ALTER SCHEMA my_etl_schema OWNER TO etl_user; ALTER SCHEMA dbadmin=> SELECT schema_name, schema_owner FROM schemata WHERE schema_name = 'my_etl_schema'; schema_name | schema_owner ---------------+-------------- my_etl_schema | etl_user (1 row)...
Programmer

Find the Number of Days Passed and Remaining in the Relative Year

Sarah Lemaire, Manager, Vertica Documentation
August 9, 2018

Jim Knicely authored this post. Although there aren’t any specific functions that will return the number of days that have passed and that are remaining in a given year, you can combine a few of Vertica’s built-in date functions to find these numbers. You can encapsulate the date logic above into several user-defined functions that...