A View Owner Needs Access to the Underlying Objects: Quick Tip

Jim Knicely authored this tip. You can use the ALTER VIEW … OWNER TO command to change a Vertica database view’s ownership. It’s important to know that a View’s owner must also have access to the underlying objects referenced by the view! Exanple: dbadmin=> SELECT user; current_user ————– dbadmin (1 row) dbadmin=> CREATE SCHEMA test; […]

Preserving Objects Owned by a Dropped User: Quick Tip

Jim Knicely authored this tip. When you drop a user with the CASCADE option, all objects owned by that user are lost forever! If you’d prefer to save those objects, you first set the GlobalHeirUserName security parameter to a user who will inherit objects after their owners are dropped. This setting ensures preservation of data […]

List Table Rows by ROS Container: Quick Tip

Jim Knicely authored this tip. A ROS (Read Optimized Store) container is a set of rows stored in a particular group of files. ROS containers are created by operations like Moveout or COPY DIRECT. You can query the STORAGE_CONTAINERS system table to see ROS containers. You can use the LEAD_STORAGE_OID function to list the rows […]

Tracking Save Points: Quick Tip

Jim Knicely authored this tip. The SAVEPOINT SQL command creates a special mark, called a savepoint, inside a transaction. A savepoint allows all commands that are executed after it was established to be rolled back, restoring the transaction to the state it was in at the point in which the savepoint was established. Savepoints can […]

Keep Data Collector Information for a Set Interval: Quick Tip

Jim Knicely authored this tip. The Data Collector retains history of important system activities and records essential performance and resource utilization counters. You probably know that you can set a size restraint (memory and disk space in kilobytes) for the specified Data Collector table on all nodes. But if you are looking to keep records […]

Turn Off Row Counts in VSQL: Quick Tip

Jim Knicely authored this tip. The SET NOCOUNT command in SQL Server will stop the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. You can accomplish the same thing in Vertica by shutting off the footer. Example: [dbadmin@s18384357 ~]$ vsql -c “SELECT * FROM […]

Generate a Notice Not an Error When Adding a Duplicate Column to a Table: Quick Tip

Jim Knicely authored this tip. When adding a column to a table in Vertica 9.2, the optional clause IF NOT EXISTS generates an informational message if the column already exists under the specified name. If you omit this option and the column exists, Vertica generates a ROLLBACK error message. Example: dbadmin=> CREATE TABLE test (c1 […]

What You Never Knew About Vertica Could Surprise You

Paige Roberts authored this post. I just started working on the Vertica team. As the “new guy,” my first few weeks of work have been largely about cramming as much Vertica information into my brain as possible in the shortest time possible. I’ve been aware of the Vertica Analytics Platform for a while. I used […]

Swapping Schemas: Quick Tip

Jim Knicely authored this tip. Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. But be careful with hardcoded schema names in SQL code like views! They won’t be swapped. Example: dbadmin=> create schema schema1; CREATE SCHEMA dbadmin=> create schema schema2; CREATE SCHEMA […]

Confirming if a Data Load was Apportioned: Quick Tip

Jim Knicely authored this tip. When running a COPY command, a parser can use more than one database node to load a single input source in parallel. This approach is referred to as apportioned load and is often much faster than loading from a single node. One way of confirming that a data load took […]

Sort Data from Within a View: Quick Tip

Jim Knicely authored this tip. If a view definition includes an ORDER BY clause, Vertica ignores it. But you can force Vertica to sort the view data on the ORDER BY columns by adding a LIMIT clause. If you don’t know how many records to limit, simply limit by some arbitrary large value! Example: dbadmin=> […]

Copy Table History: Quick Tip

Jim Knicely authored this tip. The COPY_TABLE function copies one table to another… FAST! Down the road I might want to know if a table was created as a copy using the COPY_TABLE function. To find out, I can query the data collector table DC_COPY_TABLES. Example: dbadmin=> SELECT COUNT(*) FROM big_fact; COUNT ———– 200000000 (1 […]

Restarting a Downed Node: Quick Tip

Jim Knicely authored this tip. I’m a big fan of scripting with admintools which provides us with many database tools. One of those awesome tools, that I just became familiar with, is command_start. It allows us to start a downed node, with one caveat: you have to run the command on the node that is […]

Plotting data with vsql and gnuplot

Maurizio Felici authored this post. We all know how plotting data is a very useful – and widely adopted – technique. Linux (and MacOS) offer a wonderful command line tool to plot data called gnuplot. You can install and run gnuplot on one of your Vertica nodes so you don’t have to move data out […]

What’s New in Vertica 9.2?

Soniya Shah authored this post. The latest version of Vertica was released earlier this month and with it comes a host of new features. In Vertica 9.2, we introduced several new features including: Eon Mode Updates Management Console changes Performance improvements Upgrade and installation changes Voltage SecureData updates Eon Mode Updates The Eon Mode updates […]

Handling Expression Errors in COPY Statements: Quick Tip

Jim Knicely authored this tip. We can transform data as it is loaded via a COPY command using a FILLER column and a COPY expression. In the example below, a COPY expression uses the LOWER function to convert uppercase text to lowercase as it is loaded. Example: dbadmin=> CREATE TABLE test (a INT, b VARCHAR(10)); […]

Encode Projection Columns with Zstandard Compression: Quick Tip

Jim Knicely authored this tip. CREATE TABLE test (a VARCHAR(10000), b VARCHAR(10000), c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000)); CREATE TABLE dbadmin=> CREATE PROJECTION test_pr dbadmin-> (a ENCODING BZIP_COMP, dbadmin(> b ENCODING GZIP_COMP, dbadmin(> c ENCODING ZSTD_COMP, dbadmin(> d ENCODING ZSTD_FAST_COMP, dbadmin(> e ENCODING ZSTD_HIGH_COMP) AS dbadmin-> SELECT a, b, c, d, e dbadmin-> FROM test; […]

Display Tables Referenced by a View: Quick Tip

Jim Knicely authored this tip. Vertica 9.2 introduces the new VIEW_TABLES system table that shows details about view-related dependencies, including the table that reference a view, its schema, and owner. Example: dbadmin=> CREATE TABLE base_table (c INT); CREATE TABLE dbadmin=> CREATE TABLE base_table2 (c INT); CREATE TABLE dbadmin=> CREATE OR REPLACE VIEW base_table_vw AS dbadmin-> […]

Calculate the Catalog Size in Memory on Each Node: Quick Tip

Jim Knicely authored this tip. The RESOURCE_POOL_STATUS system table provides the current state of built-in and user-defined resource pools on each node, including memory usage. The METADATA built-in pool tracks memory allocated for catalog data and storage data structures. Knowing those two facts, we can calculate catalog memory usage on all nodes by querying the […]

Labeling Queries: Quick Tip

Jim Knicely authored this tip. To quickly identify queries for profiling and debugging purposes, include the LABEL hint. Later you can search the QUERY_REQUESTS and QUERY_PROFILES systems table for the label! Example: dbadmin=> CREATE TABLE big_table_test AS /*+ LABEL(big_table_test_label) */ SELECT * FROM big_table; CREATE TABLE dbadmin=> SELECT request FROM query_requests WHERE request_label = ‘big_table_test_label’; […]

Create a User Defined SQL Function to Calculate Fibonacci Numbers: Quick Tip

Jim Knicely authored this tip. The Fibonacci Sequence is the series of numbers (i.e. 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, …) where the next number in the sequence is found by taking the sum of the previous two numbers. Calculating these numbers in SQL would be a bit complicated if it […]

Calculate Future Thanksgiving Dates: Quick Tip

Jim Knicely authored this tip. In the United States, Thanksgiving is always celebrated on the fourth Thursday of November. In Canada, Thanksgiving is celebrated the second Monday in October. So that you don’t miss another holiday at home with mom, make sure to plan ahead and calculate (using SQL) future Thanksgiving dates! Example: dbadmin=> SELECT […]

Announcing Vertica 9.x Training: Predictive Analytics Using Machine Learning

Drea Brandford authored this post. In today’s data-driven world, creating a competitive advantage depends on your ability to transform massive volumes of data into meaningful insights. Companies that use advanced analytics and machine learning are twice as likely to be top quartile financial performers, and three times more likely to execute effective decisions. On November […]

Insert Spaces Into a Character String: Quick Tip

Jim Knicely authored this tip. In many SQL relational database you will have to use the RPAD function to insert spaces into a character string. That also works in Vertica. However, for a more robust solution, Vertica provides the built-in function SPACE which returns the specified number of blank spaces. Example: dbadmin=> SELECT ‘ABC’ || […]

Find the Version of Vertica that Created a Database: Quick Tip

Jim Knicely authored this tip. You can run the VERSION() function as one method of displaying the current version of Vertica. Example: dbadmin=> SELECT version(); version ———————————— Vertica Analytic Database v9.1.1-4 (1 row) But what if you want to know the version of Vertica running when you created the current database? For that info you […]

Rejected Data Table Row Number: Quick Tip

Jim Knicely authored this tip. When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file. Be aware that when a COPY encounters […]

Reload Data from a Rejected Data Table: Quick Tip

Jim Knicely authored this tip. When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause, will save rejected data into a table. If you realize there is a modification to the COPY command that will allow those rejected records to load successfully, you can re-run the updated COPY command against […]

Handling Cast Conversion Load Errors: Quick Tip

Jim Knicely authored this tip. The nifty cast ::! returns all cast failures as NULL instead of generating an error if a the data type cannot be coerced. This cast feature, combined with the FILLER option of the COPY command, is very useful for loading data when data types aren’t playing nice. Example: dbadmin=> CREATE […]

Return All Cast Failures as NULL: Quick Tip

Jim Knicely authored this post. When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you attempt to cast conflicting conversions, such as trying to convert a varchar expression that contains letters to an integer. However, using […]

Microsoft Power BI: Latest Release Enhances Connection to Vertica

Kathy Taylor authored this post. We are excited to announce the new Vertica connector introduced in the October 2018 release of Microsoft Power BI: • With PowerBI Desktop, Vertica is now fully supported using DirectQuery Mode (push-down optimization). • With Power BI Service (Cloud Offering – Saas), DirectQuery mode is now supported with Vertica using […]

Display Canceled Queries: Quick Tip

Jim Knicely authored this tip. We can cancel a long running query in vsql by typing CTRL+C. The data collector table DC_CANCELS tracks queries that were stopped in this manner. Example: dbadmin=> SELECT table_name, component, description dbadmin-> FROM data_collector dbadmin-> WHERE component = ‘Cancels’; table_name | component | description ———–+———–+—————— dc_cancels | Cancels | Canceled […]

Calculate Request Queue Length: Quick Tip

Jim Knicely authored this post. The RESOURCE_ACQUISITIONS system table retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session. From this system table, you can calculate how long a request was queued in a resource pool […]

Concatenate non-NULL Values from a Group: Quick Tip

Jim Knicely authored this post. Vertica 9.1.1-4 introduces an extremely useful aggregate function named LISTAGG, which returns a string with concatenated non-NULL values from a group. Example: dbadmin=> SELECT * FROM test ORDER BY group_id; group_id | name ———-+——— 1 | ANDRIUS 1 | DAVE 1 | JIM 1 | KRISTEN 2 | BRYAN 2 […]

Simplify String Literals with Dollar-Quoted String Literals: Quick Tip

Jim Knicely authored this post. The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant […]

Re-Compute a Table Column’s Default Value Immediately: Quick Tip

Jim Knicely authored this tip. Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! When you update the value in a base column, you will need to re-compute the value […]

Re-Compute a Table Column’s Default Value: Quick Tip

Jim Knicely authored this tip. You can specify a table column’s default value using a DEFAULT expression. Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! To see how, check […]

Before Upgrading: Identify and Remove Unsupported Projections

With Vertica 9.1 and 9.2, Vertica has removed support for the following projections: Vertica 9.1: Projection buddies with different SELECT and ORDER BY clauses. All projection buddies must specify columns in the same order. The Vertica database regards projections with non-compliant buddies as unsafe. Vertica 9.2: Pre-join and range segmentation projections. If a table’s only […]

Derive a Table Column’s Default Value from another Column: Quick Tip

Jim Knicely authored this tip. You can specify a table column’s default value using a DEFAULT expression. To see how, check out this quick tip. When you set a default value, Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. In addition, that […]

Default a Column’s Value: Quick Tip

Jim Knicely authored this tip You can specify a table column’s default value using a DEFAULT expression. If the operation omits a value for the column, Vertica evaluates the DEFAULT expression and sets the column on load operations. Example: dbadmin=> CREATE TABLE a (b INT, c TIMESTAMP DEFAULT sysdate); CREATE TABLE dbadmin=> INSERT INTO a […]

Display View Column Data Types: Quick Tip

Jim Knicely authored this tip. Like table columns, view columns also have a data type. You can display the data types of view columns by querying the VIEW_COLUMNS system table. Example: dbadmin=> CREATE VIEW MyAccount_View AS SELECT * FROM public.MyAccount; CREATE VIEW dbadmin=> SELECT column_name, data_type FROM view_columns WHERE table_schema = ‘public’ AND table_name = […]

Insert Text Into Text: Quick Tip

Jim Knicely authored this tip. The built-in Vertica INSERT function injects a character string into a specified location in another character string. Example: dbadmin=> SELECT insert(‘Vertica is AWESOME!’, 12, 0, ‘EXTREMELY ‘); insert ——————————- Vertica is EXTREMELY AWESOME! (1 row) Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/INSERT.htm Have fun!

Reset Your Session: Quick Tip

Jim Knicely authored this tip. The SHOW command “shows” run-time parameters for the current session. dbadmin=> SHOW all; name | setting —————————–+————————————————— locale | en_US@collation=binary (LEN_KBINARY) autocommit | off standard_conforming_strings | on escape_string_warning | on multipleactiveresultsets | off datestyle | ISO, MDY intervalstyle | plain timezone | US/Eastern search_path | “$user”, public, v_catalog, v_monitor, v_internal […]

Vertica Intern Shares His Experience

Devosh Mathivanan authored this post. Hi, my name is Devosh Mathivanan currently working towards my Masters in Computer Science at Boston University. Though I don’t concentrate on any specialized area at school, I am very interested in Database and Infrastructure Engineering. Looking to take advantage of this interest, I recently completed my summer internship at […]

Conveniently Display Key Projection Info: Quick Tip

Jim Knicely authored this tip. The Vertica GET_PROJECTION_STATUS function can be called to quickly display key projection facts such as segmentation columns, verified fault tolerance, and statistics status. Examples: dbadmin=> SELECT get_projection_status(‘segs_b0’); get_projection_status ——————————————————————————– Current system K is 1. # of Nodes: 3. public.segs_b0 [Segmented: Yes] [Seg Cols: “public.segs.c”] [K: 1] [public.segs_b1] [Safe: Yes] [UptoDate: […]

Checking the Validity of a Table Audit: Quick Tip

Jim Knicely authored this tip. The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table. Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100. Example: First […]

Generate an Auto-Incrementing Alphabetical Series Using SQL: Quick Tip

Jim Knicely authored this tip. One of the very first Vertica questions I was asked from a client was how to generate an auto-incrementing alphabetical series using SQL for use as a primary key in a table. For example, the user wanted to generate this result set: AA AB AC AD AE … ZX ZY […]

Display Null Query Result With an Alternate Text: Quick Tip

Jim Knicely authored this post. In vsql, the /pset meta-command is used to set options that control how Vertica formats query result output. One of my favorites is the null option which allows you to specify an alternative value for null in the result set of a query. Example: dbadmin=> \pset NULL ‘Unknown’; \pset: unknown […]

Special Date/Time Formatting: Quick Tip

Jim Knicely authored this tip. Vertica supports several special date/time values for our convenience. All of these values need to be written in single quotes when used as constants in SQL statements. My favorite is ALLBALLS which is named so because the time digits look like balls (I had to Google that). Example: dbadmin=> SELECT […]

Change the VSQL Result Set Border: Quick Tip

Jim Knicely authored this tip. vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. The results from vsql are formatted nicely with a border between columns. You are probably used to viewing border style 1, the default, but you may prefer border styles 0 or 2! Example: […]

Find Which System Tables Have a Particular Column Name: Quick Tip

Jim Knicely authored this tip. There are over 180 built-in Vertica system tables in the V_CATALOG and V_MONITOR schemas. Trying to remember which system tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.SYSTEM_COLUMNS system table! Example: dbadmin=> SELECT table_schema, table_name, column_name dbadmin-> FROM v_catalog.system_columns dbadmin-> WHERE column_name = ‘anchor_table_name’ dbadmin-> […]

Find Which Tables Have a Particular Column Name: Quick Tip

Jim Knicely authored this tip. Your Vertica database probably contains hundreds, if not thousands, of tables. Trying to remember which tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.COLUMNS system table! Example: Which tables have a column named “DateKey”? dbadmin=> SELECT table_schema, table_name, column_name dbadmin-> FROM v_catalog.columns dbadmin-> WHERE column_name […]

Database Storage – Raw and Compressed Size: Quick Tip

Jim Knicely authored this post. One of the files generated by the /opt/vertica/scripts/collect_diag_dump.sh script details the raw and compressed size for every table in the Vertica database. At the bottom of the report there is a “total” line that shows the database raw and compressed size! Example [dbadmin@vertica8 ~]$ /opt/vertica/scripts/collect_diag_dump.sh -c -s false Database Password: […]

DbVisualizer Free for Vertica Distribution Updates

Stephen Crossman authored this post Recently, there have been some changes in how DbVisualizer Free for Vertica is distributed. Previously, there were standard DbVisualizer Free and Pro Edition distributions available on the DbVisualizer web site, and there was a special DbVisualizer Free for Vertica distribution available on the Vertica Marketplace. Now, in an effort to […]

Display Last SQL Run Time: Quick Tip

Jim Knicely authored this post. When the \timing meta-command in vsql is set to “on”, Vertica will return how long (in milliseconds) each SQL statement runs. That’s great! But what if you forgot to turn it on prior to executing a long running query? Instead of turning it on then re-executing that long running query, […]

Displaying the Administration Tools Process ID: Quick Tip

Jim Knicely authored this post. From within vsql you can run the Linux ps command to display the Administration Tools process on the local node. However, it’s a lot easier to use the GETPID function. Example: dbadmin=> \! ps -C vertica -o pid PID 233418 dbadmin=> SELECT getpid(); getpid ——– 233418 (1 row) dbadmin=> SELECT […]

Rotating Column Data Using SQL Analytics: Quick Tip

Jim Knicely authored this post. Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis, reporting tasks, and allow for some cool data manipulation. Example: I have the following table of names where each name has a unique ID. I want to rotate the values in column NAME up […]

Return Zero Instead of NULL: Quick Tip

Jim Knicely authored this post. The built-in Vertica function ZEROIFNULL evaluates to 0 for NULL values in a table’s column. Example: dbadmin=> CREATE TABLE zin (C INT); CREATE TABLE dbadmin=> INSERT INTO zin SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO zin SELECT NULL; OUTPUT ——– 1 (1 row) dbadmin=> SELECT c, zeroifnull(c) […]

Viewing Previously Generated Explain Plans: Quick Tip

Jim Knicely authored this post. The EXPLAIN command returns a formatted description of the Vertica optimizer’s plan for executing the specified statement. Example: dbadmin=> SELECT current_trans_id(), current_statement(); current_trans_id | current_statement ——————-+——————- 45035996273868061 | 1 (1 row) dbadmin=> EXPLAIN SELECT * FROM fact JOIN dim USING (c2); QUERY PLAN ——————————————————————————————————————————————- QUERY PLAN DESCRIPTION: —————————— EXPLAIN SELECT […]

Display the Current Statement Number Within the Current Transaction: Quick Tip

Jim Knicely authored this post. A Transaction in Vertica is one or more operations that are executed as a unit of work. At the user level, transactions occur in the current session by a user or script running one or more SQL statements. A transaction has a unique Transaction ID and the “units of work” […]

My Intern Experience @ Vertica

Zherui Cao authored this post. Hello, I am Zherui Cao. I am a currently studying for my Masters Degree in computing and information technology at the University of Pittsburgh. A kind Pitt alumni informed me of a summer internship opportunity at Vertica. With database development experience, I quickly became a good candidate for an internship. […]

Changing the Owner of a View: Quick Tip

Jim Knicely authored this tip. We can change the owner of a database view with the ALTER VIEW … OWNER TO command. Example: dbadmin=> CREATE TABLE test (c1 INT); CREATE TABLE dbadmin=> INSERT INTO test SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> CREATE VIEW test_vw AS SELECT c1 FROM test; CREATE VIEW dbadmin=> SELECT […]

Setting Variables on the VSQL Command Line: Quick Tip

Jim Knicely authored this post. The vsql client provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. The -v, –variable, and –set options can be used to set a variables value from the command line. Example: The following simple SQL […]

Enabling Inter-Node Spread Encryption: Quick Tip

Jim Knicely authored this tip Internode SSL allows you to use SSL to secure communication between nodes within a Vertica cluster. It is important to secure communications between Vertica nodes if you do not trust the network between the nodes. The EncryptSpreadComm configuration parameter enables encryption on the control channel. Example: dbadmin=> SELECT parameter_name, current_value, […]

Exiting a DbVisualizer Script Following an Error: Quick Tip

Jim Knicely authored this tip. After reading yesterday’s Vertica Quick Tip “Exiting a vsql Script Following an Error”, a client asked if the ON_ERROR_STOP variable is available in the popular third party Vertica client tool DbVisualizer. The answer to that is no, as ON_ERROR_STOP is a Vertica vsql client specific setting. However, many clients, including […]

Exiting a vsql Script Following an Error: Quick Tip

Jim Knicely authored this tip. By default, if a vsql script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to “on” in a vsql script and an error occurs during processing, the script terminates immediately. Example: dbadmin=> CREATE TABLE tbl1 (pkid […]

The Parts of a Session ID: Quick Tip

Jim Knicely authored this post. A Vertica Session is an occurrence of a user interacting with a database through the use of SQL statements. Each Session has an associated Session Identifier stored in the SESSION_ID column of many of the Vertica system tables. The Session ID is a unique identifier within the cluster at any […]

Pure Vertica SQL Graph Flavors

Moshe Goldberg authored this post. The art and science of the best sales conversation is “Visual story telling”. This document shows how Vertica VSQL alone can visualize query results in color and graphics. The idea presented is to provide dynamic visuals, based only on Vertica VSQL built-in capabilities. All the graph examples below were drawn […]

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

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. Example: [dbadmin@vertica01 ~]$ admintools -t list_allnodes Node | Host | State | Version | DB ————————-+—————+——-+—————–+————– v_port_5432_node0001 | 192.168.2.200 | DOWN | vertica-9.1.1.1 | […]

Faster Data Loads with Apportioned Load: Quick Tip

Jim Knicely authored this tip. Vertica can divide the work of loading data, taking advantage of parallelism to speed up the operation. One supported type of parallelism is called apportioned load. An apportioned load divides a single large file or other single source into segments (portions), which are assigned to several nodes to be loaded […]

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. Example: dbadmin=> CREATE TABLE my_favorites_things (pk INT PRIMARY KEY, thing VARCHAR(100) NOT […]

Database and Node Uptime: Quick Tip

Jim Knicely authored this tip. 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. Example: dbadmin=> SELECT database_name, start_time dbadmin-> FROM databases; database_name | start_time —————+——————————- test_db | 2018-09-06 14:33:07.301363-04 (1 row) […]

Mimicking Enumerated Types: Quick Tip

Jim Knicely authored this tip. I used to work a lot with MySQL. It had a cool data type called “Enumerated Types”. Example in MySQL: (myadmin@localhost) [jimk]> CREATE TABLE e (ecol ENUM(‘Bill’, ‘Sam’, ‘Jack’)); Query OK, 0 rows affected (0.10 sec) (myadmin@localhost) [jimk]> INSERT INTO e VALUES(‘Bill’); Query OK, 1 row affected (0.00 sec) (dbadmin@localhost) […]

Changing the Field Separator in VSQL: Quick Tip

Jim Knicely authored this tip. 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 […]

Monitoring Resource Pool Cascade Events: Quick Tip

Jim Knicely authored this tip. 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! Example: […]

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 […]

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. Example: dbadmin=> SELECT * FROM dim; c2 | c3 —-+—— 1 | TEST (1 row) dbadmin=> EXPORT TO PARQUET (directory = ‘/home/dbadmin/dim’) AS SELECT * FROM dim; Rows Exported ————— 1 (1 […]

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 […]

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. Example: dbadmin=> CREATE TABLE base_table (c INT); CREATE TABLE 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 […]

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? dbadmin=> SELECT TO_CHAR(2^63, ‘9,999,999,999,999,999,999’) “Extremely BIG!”; Extremely BIG! —————————- […]

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. Example: dbadmin=> CREATE USER test; CREATE USER […]

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 […]

Data Preparation Tools – Technical Brief

Curtis Bennett authored this blog Vertica supports a number of industry standard data preparation tools for use in the data science life-cycle. In addition to the functions described here, Vertica has a wide array of analytic capabilities which can be leveraged for additional data preparations including time-series analytics (with missing value imputation), analytic windowing and […]

vertica-python Becomes Vertica’s First Officially Supported Open Source Database Client

Tom Wall authored this post. We are very excited to announce that vertica-python, the open source python database client for Vertica created by Uber Technologies Inc., will officially become a supported database client for Vertica! This is a great milestone for Vertica and our community of users and developers. Not only do we expect to […]

Thanks, Google!

Joy King authored this post. This week began with a compliment from Google that made me so proud on behalf of Vertica! On Wednesday, July 25, at the Google Cloud Next 2018 event, Google launched a Machine Learning (ML) beta with two algorithms – linear and logistic regression – and also confirmed that these new […]

Convenience But at What Cost?

Joy King authored this post. Convenience is so important to me. Move quickly and get things done. That’s probably why I am so addicted to Amazon Prime despite the fact that I could save money if I would just do a little research and find better prices for the things I buy. My impatience and […]

Sending Emails from the Vertica Database

Maurizio Felici authored this post. You can create an external procedure to send alert emails from the Vertica database. For more information on external procedures see Using External Procedures in the Vertica documentation. Follow the procedure below to create an external procedure for sending emails. NOTE: These procedures were performed on the Linux OpenSUSE distribution. […]

Vertica Quick Tip: Keepalive settings for AWS Network Load Balancer

Serge Bonte authored this post. Network Load Balancers are one of the three types of load balancers supported by Amazon’s Elastic Load Balancing. See: https://docs.aws.amazon.com/elasticloadbalancing/latest/network/introduction.html Since load balancers act as a proxy between clients (such as JDBC) and Vertica servers, it is important to understand how AWS’s NLB handle idle timeouts for connections. The idle […]

The Internet of Things (IoT) and Smart Metering

Joy King authored this post. Every month, my husband receives a driver’s report from Chevrolet, providing him with “feedback” on his driving behavior – everything from the intensity of his braking to the number of lane changes without a blinker to the distance and speed he drove in his Silverado truck. The report also provides […]

Cerner Wins TDWI Best Practices Award

Steve Sarsfield authored this post. It’s amazing to see our customers ‘crush it’ when it comes to their implementation and best practices industry awards. That’s why I was so happy to hear that Cerner, a long-time Vertica customer, is the recipient of TDWI’s Best practices award for data warehouse. I want to congratulate Cerner on […]

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 […]

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 […]

What’s New in Vertica 8.1: Connecting to Vertica Updates

Vertica 8.1 includes the following product enhancements to Connecting to Vertica. Functional Updates to \timing The \timing metafunction has been enhanced so you can use the following commands to toggle \timing on or off based on its current setting: •\timing – turns timing on or off depending on its current state. For example if timing […]

What’s New in Vertica 8.1: Security Updates

Vertica 8.1 includes the following enhancements to Vertica security. Function to Verify Kerberos Configuration The function KERBEROS_CONFIG_CHECK allows you to test your Kerberos configuration of the Vertica cluster. Running this function checks: • Whether or not Kerberos services are available. • If a keytab file exists • If the Kerberos configuration parameters are set in […]

Create and Assign Roles

A role is a collection of privileges that can be granted to one or more users or roles. Assigning roles prevents you from having to manually grant sets of privileges for each individual user. For the most part, creating and assigning roles is fairly straightforward. However, the user to which roles are assigned needs to […]

Dynamic Row and Column Access Policies

The content of this blog post is based on an article authored by Maurizio Felici. The Vertica Analytic Database access policies act on columns and rows to provide extra security on data in your tables. You can create flexible access policies that limit which users can access certain data by applying the access policy to […]

Using Inherited Privileges

New to Vertica 7.2.0, the inherited privileges feature provides a one-step method for enabling privileges at the schema level.