Vertica Blog

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

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

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

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

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

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

This blog post was authored by Soniya Shah. Are you a database administrator looking for ways to get the most from your Vertica database? If so, this post is for you. You’re already familiar with the technicalities of Vertica – the Tuple Mover, deletes, projections, and more. If you’re looking to get started, check out […]

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

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

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

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

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

Arash Jalal Zadeh Fard authored this post. Vertica has a set of machine learning tools covering a wide spectrum of advanced data analysis functionalities like data discovery, data preparation, model training, model evaluation, and model management. The goal of this blog post is to provide a hands-on example demonstrating how the built-in distributed Random Forest […]

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

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!

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

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

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

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

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

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

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

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

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

Vertica is looking for outstanding software engineers and QA engineers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big Data workloads. […]

Vertica is looking for outstanding software engineers and QA engineers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big Data workloads. […]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Vertica is looking for outstanding software engineers, QA engineers, and technical writers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big […]

Vertica is looking for outstanding software engineers, QA engineers, and technical writers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big […]

Vertica is looking for outstanding software engineers, QA engineers, and technical writers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big […]

Vertica is looking for outstanding software engineers, QA engineers, and technical writers. We are hiring full-time employees and summer 2019 interns in our Cambridge, MA and Pittsburgh, PA offices. You’ll have the opportunity to add your innovative ideas to our flagship product, massively scalable database that is used for many of the most challenging Big […]

We all love to win and it’s even better when Vertica and our customers win! Earlier this week at the Strata Conference in New York City, our customer Cerner won the “Most Innovative Product” award – a product that is powered by Vertica! Cerner is a leading supplier of health information technology solutions and services. […]

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

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

We’ve posted a new Product Management feedback survey and we’re wondering what you think about our SDK and how you use the UDx’s. We appreciate all your feedback! You can find the survey here. https://in.hotjar.com/s?siteId=438341&surveyId=109476

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

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

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

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

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

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

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

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

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

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

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

This blog post was authored by Lisa Donaghue. In Vertica 9.1, Management Console (MC) introduces improved monitoring for external tables and HCatalog data, and the ability to revive Eon Mode databases in a new cluster. MC also includes management and monitoring capabilities for databases running in Eon Mode, a database mode previously in beta. Eon […]

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

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

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

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

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

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

This post was authored by Soniya Shah. Are you a new Vertica user? If so, you’re probably wondering where to start. We’re here to help you on your big data analytics journey, from understanding Vertica terminology to making the most of your resources. If you find yourself asking questions like What does the Tuple Mover […]

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! Example: Prior to Vertica 9.1.1: dbadmin=> CREATE TABLE test_coercion (c1 VARCHAR(100)); CREATE TABLE dbadmin=> INSERT INTO test_coercion SELECT ‘Convert me!’; OUTPUT ——– 1 (1 row) dbadmin=> SELECT c1::VARBINARY […]

Jim Knicely authored this post. Vertica comes with some nifty command-line tools like vsql, admintools, and vbr. If you need assistance using any of these, you can run them with the –-help parameter to view the options available for each tool. Example: [dbadmin@s18384357 ~]$ /opt/vertica/bin/vbr –help vertica backup and restore script help -h –help print […]

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

Jim Knicely authored this tip.

Jim Knicely authored this tip. As of Vertica 9.1.1, you can now transfer the ownership of a schema to another user! Example: dbadmin=> SELECT schema_name, schema_owner FROM schemata WHERE schema_name = ‘my_etl_schema’; schema_name | schema_owner —————+————– my_etl_schema | dbadmin (1 row) dbadmin=> CREATE USER etl_user; CREATE USER dbadmin=> ALTER SCHEMA my_etl_schema OWNER TO etl_user; ALTER […]

Jim Knicely authored this post. Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can use the CURRENT_SCHEMA function to display the name of the current schema (i.e., the first “valid” schema in the user’s […]

Jim Knicely authored this post. Table columns having the LONG VARCHAR data type can store up to 32,000,000 octets. Since there is a table row limit size of 32,768,000 bytes, how do I reference more than one related LONG VARCHAR, each having the maximum length? Simple. Use more than one table! Example: dbadmin=> CREATE TABLE […]

Jim Knicely authored this tip. The RANDOMINT(n) function returns one of the n integers from 0 through n – 1. Those are all positive integers. What if I want to include negative integers? That’s easy with a simple multiplication. Example: dbadmin=> SELECT DECODE(randomint(2), 1, 1, -1) * randomint(11) “Random INT from -10 to 10”; Random […]

Jim Knicely authored this tip. Use the built-in Vertica function GET_TABLE_PROJECTIONS to quickly summarize the details of all of the projections of a Vertica table. Example: dbadmin=> \a Output format is unaligned. dbadmin=> \t Showing only tuples. dbadmin=> SELECT get_table_projections(‘public.jim’); Current system K is 1. # of Nodes: 3. Table public.jim has 3 projections. Projection […]

We love this 2014 blog post and our intern program, so we’re bringing it back to the top of the blog. Jaimin is still here at Vertica, working hard on the core of our analytics database. Watch this space! My name is Jaimin and I work as a Software Engineer in the Distributed Query Optimizer […]

Jim Knicely authored this tip. There are several system tables like QUERY_REQUESTS that store the queries executed in the database. To help understand why a query was executed (i.e., for debugging purposes), you might want to add a comment to the SQL code. Example: dbadmin=> /* This is a SELECT from the table JIM */ […]

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

We’re back with our newest product management survey for this summer! This time we’re asking about how you load your data – everything from the ETL tools you use to how you manage your information. The answers you provide will help us fit Vertica into your infrastructure in a way that is ideal for your […]

Jim Knicely authored this tip. There are several cool built-in functions that you can use to compare two times. Example: dbadmin=> SELECT time_now AS time1, dbadmin-> time_now_minus_1_hour AS time2, dbadmin-> TIME_EQ(time_now, time_now_minus_1_hour) AS “time1 = time2”, dbadmin-> TIME_GE(time_now, time_now_minus_1_hour) AS “time1 >= time2”, dbadmin-> TIME_GT(time_now, time_now_minus_1_hour) AS “time1 > time2”, dbadmin-> TIME_LE(time_now, time_now_minus_1_hour) AS “time1

Jim Knicely authored this tip. The NULLIF built-in Vertica function compares two expressions. If the expressions are not equal, the function returns the first expression. If the expressions are equal, the function returns NULL. The NULLIFZERO built-in Vertica function evaluates to NULL if the value in the column is 0. Both come in handy when […]

Jim Knicely authored this tip. Hash segmentation allows you to segment a projection based on a built-in hash function that provides even distribution of data across multiple nodes, resulting in optimal query execution. In a projection, the data to be hashed consists of one or more column values, each having a large number of unique […]

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

Jim Knicely authored this post. There are several cool built-in functions that you can use to compare two dates. Example: dbadmin=> SELECT today, dbadmin-> yesterday, dbadmin-> DATE_EQ(today, yesterday) AS date_equal, dbadmin-> DATE_GE(today, yesterday) AS date_greater_than_or_equal, dbadmin-> DATE_GT(today, yesterday) AS date_greater_than, dbadmin-> DATE_LE(today, yesterday) AS date_less_than_or_equal, dbadmin-> DATE_LT(today, yesterday) AS date_less_than dbadmin-> FROM (SELECT sysdate::date AS […]

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

Jim Knicely authored this post. You can query the data collector table DC_PROJECTIONS_USED to ascertain when a table was last accessed and by whom. Example: dbadmin=> SELECT table_name, dbadmin-> MAX(time) AS last_access, dbadmin-> user_name dbadmin-> FROM dc_projections_used dbadmin-> WHERE table_schema = ‘public’ dbadmin-> AND table_name = ‘big_table’ dbadmin-> GROUP dbadmin-> BY table_name, dbadmin-> user_name; table_name […]

This post was authored by Soniya Shah. In Vertica 9.0 we introduced Eon Mode. Since then, there have been many improvements in recent releases. Vertica in Eon Mode is a new architecture that separates compute and storage, allowing users to take advantage of cloud economics that enable rapid scaling and shrinking of clusters in response […]

Jim Knicely authored this blog. You can join a few system tables together to find the disk space used and the row counts by node for segmented tables: Example: dbadmin=> SELECT ps.anchor_table_schema, dbadmin-> ps.anchor_table_name, dbadmin-> ps.node_name, dbadmin-> ROUND(SUM(ps.ros_used_bytes)/1024/1024/1024, 5)::NUMERIC(25,5) AS GB, dbadmin-> SUM(ps.ros_row_count) AS Rows dbadmin-> FROM v_catalog.projections p dbadmin-> JOIN v_monitor.projection_storage ps dbadmin-> USING […]

Jim Knicely authored this post. There are many reasons you might want to know the number of seconds that have passed since midnight (i.e. Event Logging). You could combine several Vertica built-in date manipulation functions to calculate the number of seconds since midnight, but the easiest way is to use the MIDNIGHT_SECONDS function! Example: dbadmin=> […]

Jim Knicely authored this tip. When you first install Vertica, the default database time zone is controlled by the TZ environment variable. If TZ is undefined, Vertica uses the operating system time zone. You can change the default database time zone later by altering the value of the TZ environment variable or the operating system […]

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

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

Jim Knicely authored this post. By combing Vertica’s built-in date formatting functions and the TIMESERIES clause, it’s easy to find the total number of days, total number of week days (Monday – Friday), and the total number of weekend days (Saturday – Sunday) that occur within a multi-year range (i.e., 2016 – 2018), including Leap […]

Jim Knicely authored this post. A synonym in Oracle is an alternative name for a database object (i.e., table, view, sequence, etc.). Synonyms work great when you want to point a user to a specify schema when a schema is not specified in a query. Although synonyms are not used in Vertica, you can instead […]

Jim Knicely authored this tip. Given a list of dates, how do I find the “average” date? Like this… Example: dbadmin=> SELECT * FROM dates; mydate ———— 2018-07-10 2018-07-12 (2 rows) dbadmin=> SELECT AVG(JULIAN_DAY(mydate)) + ‘4714-11-24 BC’::DATE avg_date dbadmin-> FROM dates; avg_date ——————— 2018-07-11 00:00:00 (1 row) Have fun!

Jim Knicely authored this tip. When creating objects in Vertica (i.e., tables), Vertica preserves the “case” of the object name in the catalog meta-data exactly as you ask. Example: dbadmin=> CREATE TABLE lower_case (c1 INT); CREATE TABLE dbadmin=> SELECT table_name FROM tables WHERE table_name ILIKE ‘%lower_case%’; table_name ———— lower_case (1 row) dbadmin=> DROP TABLE lower_case; […]

Michael Flower authored this post. Introduction Vertica has a highly extensible UDx framework, which allows external user-defined functions, parsers and data loaders to be installed onto the Vertica server. This means that a routine written in C++, R, Java or Python can be run in-database as a Vertica SQL function. This blog is based on […]

Crystal North authored this post. What is Inclusion & Diversity? What is an ERG? Those questions are asked often and I am beyond excited to answer them. Inclusion & Diversity are what fuels any sort of growth in any capacity. From family, to friends, to education, to community, to company, to organization, etc etc etc, […]

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

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

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

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

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

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

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

This blog post was authored by Moshe Goldberg. Disclaimer This document content is for informational purposes only. The content here should not be relied upon as legal advice, or to determine how GDPR applies to your organization or you. We strongly advise working with a qualified legal GDPR professional to understand how the regulation applies […]

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

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

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

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

Jim Knicely wrote this tip. The ANALYZE_STATISTICS function collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. The function accepts a table/projection/column name as input. What if you wanted to get stats for all of the tables in a schema? One option is to have […]

AHMが進んでいない場合、次のチェックリストを使用してトラブルシューティングを行います。 ステップ タスク 結果 1 Last Good Epoch（LGE）が進んでいるかどうかを確認します。 => SELECT CURRENT_EPOCH, LAST_GOOD_EPOCH, AHM_EPOCH FROM SYSTEM; LGEが進んでいる場合、Step 2 へ。 LGEが進んでいない場合、Step 5 へ。 2 すべてのノードがUPしているかどうかを確認します。 => SELECT * FROM NODES WHERE NODE_STATE = ‘UP’; すべてのノードがUPの場合、Step 3 へ。 1つ以上のノードがDOWNの場合、下記コマンドを使用してすべてのノードをUPにします。 $ admintools -t restart_node -d <database name> -s <node_name> すべてのノードがUPになった後、Step 4 へ。 3 リフレッシュが実行されていないプロジェクションがないかどうか確認します。 => SELECT PROJECTION_NAME, NODE_NAME, IS_UP_TO_DATE FROM PROJECTIONS WHERE IS_UP_TO_DATE […]

This blog post was authored by Eugenia Moreno. Query tuning in Vertica is not an exact science. Recommendations differ based on your database. This document assumes that all nodes in the cluster are UP, your Vertica configuration is ok, and that v*perf tools have been executed. The following diagram shows the query flow in Vertica: […]

If your node is not connected to spread, follow the below steps to troubleshoot the connection issue. Step Task Results 1 Check whether the spread.conf file in the catalog folder is identical across all the nodes in the cluster. $ cat spread.conf If the spread.conf flies are identical across all nodes, go to Step 2. […]

If you want to troubleshoot why the database process is not starting, follow this checklist. Step Task Results 1 Ensure Vertica is not already running on any node. $ ps –ef | grep vertica The Vertica process displays as follows: /opt/vertica/bin/vertica -D <catalog directory> -C <dbname> -n <node name> -h <host IP> -p <port> Prior […]

Jim Knicely authored this tip. The built-in VERSION function returns a VARCHAR that contains your Vertica node’s version information. Example: dbadmin=> SELECT version(); version ———————————— Vertica Analytic Database v9.1.0-2 (1 row) The Vertica version is formatted as X.Y.Z-R, where… • X.Y is the two-digit Vertica major release number, e.g., 8.1, 9.0 and 9.1 • Z […]

This blog post was authored by Shrirang Kamat. The purpose of this document is to explain how the query budget of a resource pool used by the query can influence the initial memory acquisition for a query and how it impacts query performance. For more details about how we compute the query budget, see the […]

Jim Knicely wrote this tip. We strongly recommend that you use a homogeneous hardware configuration for your Vertica cluster: Each node of the cluster should be similar in CPU, clock speed, number of cores, memory, and operating system version. We learned in the last Vertica Quick Tip that the V_CATALOG.HOST_RESOURCES system table provides a snapshot […]

Jim Knicely wrote this tip. At some point in the future, you might not remember the number of CPU sockets/processor cores and the amount of memory on each node of your Vertica cluster. Instead of having to issue several Linux commands on each node, Vertica provides the V_CATALOG.HOST_RESOURCES system table, which provides a snapshot of […]

This blog post was authored by Ding-Qiang Liu. In analytic businesses supported by Vertica, complex processing logic is sometimes unavoidable. Using ANSI SQL might cause query strings to be much longer, and will slow the query with a huge volume data to query. If using Vertica SDKs, you can encapsulate that general computing logic in […]

Jim Knicely authored this tip. Keywords are words that have a specific meaning in the SQL language. Every SQL statement contains one or more keywords. Many keywords are also reserved words. Vertica recommends that you not use reserved words as names for objects, or as identifiers. Including reserved words can make your SQL statements confusing. […]

This blog post was authored by Maurizio Felici. Time series analytics evaluate the values of a given set of variables over time and group those values into a window, based on a time interval for analysis and aggregation. There are a few types of functions that help perform time series analytics: • Event based functions […]

Positive attitude Education Passion Determination Teamwork According to Jay Blake, founder of Follow a Dream auto racing, those are the tools you need to overcome obstacles and be successful. More on that later. On June 7, The Carroll Center for the Blind inducted Micro Focus’s own Kathy Taylor into the Carroll Society. This award recognizes […]

Vertica 9.0.1-9 and later 9.0 hotfixes now support Ubuntu 16.04. However, Ubuntu 16.04 ships with a compiler that is incompatible with the Vertica C++ SDK. To compile UDxs on this platform, you must install packages for 4.8 compatibility: $ sudo apt-get remove g++ $ sudo apt-get remove gcc $ sudo apt-get install gcc-4.8 $ sudo […]

This blog post was authored by Soniya Shah. Connection load balancing automatically spreads the overhead of client connections across the cluster by redirecting connections. Each client connection a host in your Vertica cluster requires memory and processor time. If a lot of clients connect to a single host, this can affect database performance. The initiator […]

Jim Knicely authored this tip. I was asked recently how to load a negative date (one representing a BC date) into Vertica. Although negative dates are not valid in Vertica, the FILLER parameter of the COPY command can be used to load them. Example: dbadmin=> \d public.test List of Fields by Tables Schema | Table […]

Jim Knicely authored this tip. You learned from a Vertica Quick Tip back on 02/07/2018 that Vertica has the lightweight, in-memory COPY_TABLE function. This awesome function lets us copy huge tables in less than a second. I wanted to revisit this awesome feature to point out that when using the COPY_TABLE command, if the target […]

Vertica Version 9.1 introduces a new integration with Voltage SecureData. This feature lets you use encryption to protect sensitive data within your Vertica database. It works within your existing database schema and clients. You can even automate data decryption. This automation makes decryption transparent and removes the need to change your existing SQL queries. What […]

Jim Knicely authored this blog. Many Microsoft products, including SSAS, can connect to Vertica using the Vertica OLE DB driver for Windows which is installed as part of the Client Drivers and Tools for Windows. Unfortunately tools like MS SSAS can include square brackets to qualify identifiers in the queries it generates to run in […]

It’s the fourth round of Vertica product management surveys and we have really appreciated getting your feedback! In this survey, we want to know all about your data lake. We want to know what tools you use, how much data is in your lake, and the types of workloads you are running. We are hoping […]

This blog post was authored by Anh Le. Introduction As the number of features in your data set grows, it becomes harder to work with. Visualizing 2D or 3D data is straightforward, but for higher dimensions you can only select a subset of two or three features to plot at a time, or turn to […]

This blog post was authored by Soniya Shah. Machine learning seems to be everywhere these days – in the online recommendations you get on Netflix, the self-driving cars that hyped in the media, and in serious cases, like fraud detection. Data is a huge part of machine learning, and so are the key terms. Unless […]

Jim Knicely authored this tip. The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. This information is useful for identifying: • Who changed the configuration parameter value • When the configuration parameter was changed • Whether nonstandard settings were in effect in the past Example: dbadmin=> ALTER DATABASE sfdc SET EnableSSL = […]

This blog post was authored by Lisa Donaghue. In Vertica 9.1, Management Console (MC) introduces improved monitoring for external tables and HCatalog data, and the ability to revive Eon Mode databases in a new cluster. MC also includes management and monitoring capabilities for databases running in Eon Mode, a database mode previously in beta. Eon […]

This blog post was authored by Jim Knicely. In Vertica an empty string is not treated as a NULL value, so an empty string does equal an empty string. Example: dbadmin=> SELECT ”” = ”” “Are they equal?”; Are they equal? —————– t (1 row) The function REPLACE replaces all occurrences of characters in a […]

This blog post was authored by Monica Cellio. Using the Vertica SDK, you can write several types of user-defined extensions (UDxs) to add your own customizations. In a previous release the SDK added Python support for one type, scalar functions (UDSFs). In 9.1 we added Python support for transform functions (UDTFs). User-Defined Transform Functions (UDTFs) […]

This blog post was authored by Jim Knicely. An empty string (”) is treated as a NULL value in Oracle, while in Vertica an empty string is not treated as a NULL value. So when using Vertica, if you want to indicate that a column value is unknown, be sure to use NULL and not […]

This blog post was authored by Jim Knicely. A flattened table contains columns that are derived from query-expressions that must return only one row and column value, or none. If a column’s query-expression does return more than one row you will get a “Subquery used as an expression returned more than one row” error. If […]

This blog post was authored by Drea Brandford. You have had Vertica up and running in production for a while now; but you’re noticing that, while your analytic queries are executing far faster than they would in a traditional row-store database, they are still not performing quite as fast as you need them to. Chances […]

This blog post was authored by Mark Hayden. You can deploy a Vertica cluster running in Eon or Enterprise mode using EC2 instances that support EBS volumes. This document provides the steps you need to use EBS volumes that were preserved from a Vertica cluster that was terminated, and how to reattach them to revive […]

This blog post was authored by Jim Knicely. In version 9.1 and later, Vertica increases the default amount of memory assigned to the Tuple Mover (TM) resource pool from 200MB to 5% of available memory to improve performance merging out wide tables. If your General resource pool is defined by a percentage, the Tuple Mover […]

This blog post was authored by Jim Knicely. Also known as AHM, the ancient history mark is the oldest epoch whose data is accessible to historical queries. Any data that precedes the AHM is eligible to be purged. If a cluster nodes is down or if a database contains projections that need to be refreshed, […]

This blog post was authored by Jim Knicely. A client recently asked if we had a Vertica function that could return a list of columns from a table which contain NULL values. There aren’t any built-in functions for that, but there is always a fast solution available in Vertica! Here’s one! Example: dbadmin=> SELECT * […]

This blog post was authored by Steve Sarsfield. Critical Capabilities for Big Data Databases Imitation is the sincerest form of flattery, but when imitators try to match brilliant engineering against uninspiring execution in order to meet the needs of a bullet on a PowerPoint slide, the imitation tends to be more of a parody, or […]

This blog post was authored by Michael Leuchtenburg. Introduction Vertica engineers tested database performance with and without Meltdown and Spectre mitigations on both Haswell and Skylake microprocessors. As compared to no mitigation, with the fastest settings, we found a minimal, average 1% slowdown on Haswell, and an average 25% slowdown on Skylake. These results are […]

This blog post was authored by Jim Knicely. The Vertica optimizer uses its own internal logic to determine whether to join one table to another as an inner or outer input. When joining external tables, you can help out the optimizer immensely by letting it know the exact row counts of those external tables! For […]

This blog post was authored by Soniya Shah. This blog post is updated as of July 2018. With Eon mode, Vertica is a database that takes advantage of all elastic compute and scalable storage capabilities in the cloud. In Eon mode, Vertica can take advantage of cloud economics – it can be made to scale […]

This blog post was authored by Jim Knicely. Clients often use DbVisualizer as their preferred GUI tool to query Vertica. Developers and DBAs tend to leave DbVisualizer open on their desktop while they are off doing some other activity, only to return to find out that DbVisualizer has disconnected from Vertica. Most likely the Database […]

This blog post was authored by Jim Knicely. Ever wonder how to get rid of those pesky duplicate values from a string? One way of doing that is via the REGEXP_REPLACE function. Example: dbadmin=> SELECT c1, REGEXP_REPLACE(c1, ‘(?

This blog post was authored by Joy King. Vertica was born at a tipping point in the world of Enterprise Data Warehouses. It was designed from the first line of code to address new levels of data volumes and analytical performance. But it was also designed to break the tightly integrated hardware and software appliances […]

There is no excerpt because this is a protected post.

This blog post was authored by Jim Knicely. Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can change a session’s search path at any time by calling SET SEARCH_PATH. When you modify a search […]

This blog post was authored by Shrirang Kamat. Vertica in Eon Mode is a new architecture that separates compute and storage, allowing users to take advantage of cloud economics that enable rapid scaling and shrinking of clusters in response to a variable workload. Eon Mode decouples the cluster size from the data volume and lets […]

This blog post was authored by Jim Knicely. The OVERLAY function returns a VARCHAR value representing a string having had a substring replaced by another string. Example: dbadmin=> SELECT OVERLAY(‘See ya @ the Deep Dive Meetup tomorrow in St. Louis, MO!’ PLACING ‘ Vertica’ FROM 13 FOR 0) AS “Awesome Event on 05/15/2018”; Awesome Event […]

This blog post was authored by Ginger Ni. The precision-recall curve is a measure for evaluating binary classifiers. It is a basic measure derived from the confusion matrix. In Vertica 9.1, we provide a new machine learning evaluation function PRC() for calculating precision and recall values from the results of binary classifiers. Along with the […]

This blog post was authored by Sanjay Baronia. Today, cloud infrastructure has made it easier for organizations to consume services and deploy business applications with a pay-as-you-go, OPEX model. This provides a number of incentives to move data to the cloud, especially for variable workloads and use cases that require heavy compute for finite periods […]

This blog post was authored by Jim Knicely. There are several ways to figure out who is the session user. This comes in handy for logging. Example: [dbadmin@s18384357 ~]$ vsql -U jim Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon […]

This blog post was authored by Jim Knicely. You can now deploy a Vertica cluster with an Eon Mode database instead of an Enterprise mode database. In Enterprise Mode, the original Vertica “share nothing” configuration, each database node stores a portion of the data and performs a portion of the computation. In Eon Mode, computational […]

This blog post was authored by Jim Knicely. I’m an old school kind of guy so I love our vsql command line interface (CLI) tool. What’s cool is if I am on a remote Linux host, I can copy the vsql binary from the database host and run then run it locally. Example: [dbadmin@s18384357 ~]$ […]

This blog post was authored by Jim Knicely. A friend recently asked me why we don’t have certain functions available in Vertica that are offered in databases like MySQL. I shrugged it off as we have bigger fish to fry. But he kept pushing so I showed him how easy it is to implement a […]

One of the coolest aspects of Vertica is its in-database machine learning functionality. As part of our third round of product management surveys, we’re asking you to take the time to answer how you use Vertica analytics today. We’re hoping to get answers we can use to expand upon our growing analytics and machine learning […]

Every year, the ACM SIGMOD/PODS conference is held as a forum for database researchers, developers, and users to explore innovations in the field and exchange ideas. The conference is an opportunity for thought leaders to talk with each other and hear from one another through presentations, workshops, and tutorials. This year, Ben Vandiver, Shreya Prasad, […]

This blog post was authored by Jim Knicely. If you guys are like me I prefer the CLI. So admintools is my friend. However, at many client sites I see admintools is kind of ugly. So to get it looking a little better: Run this in Linux: export NCURSES_NO_UTF8_ACS=1 And add the command to your […]

This blog post was authored by Jim Knicely. If you are like me I am constantly forgetting the column names from our system tables. Use this simple trick to get the column names. Example: dbadmin=> SELECT * FROM v_catalog.tables limit 0; table_schema_id | table_schema | table_id | table_name | owner_id | owner_name | is_temp_table | […]

This blog post was authored by Jim Knicely. A few years ago when I was an Oracle DBA a client thought they’d stump me by asking how they could capitalize only the first letter of each word for a given input. I immediately said try the initcap function. She was amazed. Guess what, Vertica has […]

This blog post was authored by Crystal North. Wow. I have spent the last month reflecting every single work day as to why I love my job. I started this personal focus because I had read one too many status updates on LinkedIn that just seemed so unauthentic, so negative, or so salesy. I wanted […]

This blog post was authored by Sarah Lemaire. Before now, you could only operate your Vertica database in Enterprise Mode, the traditional Vertica architecture where your data is distributed across the local nodes. Now, Vertica 9.1, released in April, 2018, allows you to operate your database in Eon Mode, which was previously released as beta […]

This blog post was authored by Soniya Shah. In Vertica 9.1 we introduce new functionality including: • Eon Mode, now available in production environments • Machine Learning Enhancements • Management Console Updates • Voltage SecureData Integration • Python UDTF • AWS Licensing Updates • Security Updates • Upgrade and Installation Changes • S3 Session Parameter […]

This blog post was authored by Soniya Shah. The Vertica query optimizer uses statistics about the data to create a query plan, which contains a set of operations to compute the requested result. Depending on the properties of the projections defined in your database, the optimizer can choose faster and more efficient operations. This is […]

This blog post was authored by Soniya Shah. With each release, Vertica adds new features and enhancements. To access these new features, you must upgrade your database. Before performing an upgrade, you must shut down your database. Upgrade Path You should always upgrade only to the immediate next version. Upgrading to a version that is […]

This blog post was authored by Jim Knicely. The OVERLAPS Vertica built-in function evaluates two time periods and returns true when they overlap, false otherwise. Example: dbadmin=> SELECT (DATE ‘2018-04-18’, DATE ‘2018-04-21’) OVERLAPS (DATE ‘2018-04-16’, DATE ‘2018-04-19’); overlaps ———- t (1 row) dbadmin=> SELECT (DATE ‘2018-04-18’, DATE ‘2018-04-21’) OVERLAPS (DATE ‘2018-04-22’, DATE ‘2018-04-25’); overlaps ———- […]

This blog post was authored by Jim Knicely. You can find the square root of a number with the |/ operator and the cube root of a number with the ||/ operator. Example: dbadmin=> SELECT |/25 square_root_of_25, dbadmin-> ||/27 cube_root_of_27; square_root_of_25 | cube_root_of_27 ——————-+—————– 5 | 3 (1 row) Have Fun!

This blog post was authored by Ravi Gupta. We have often found that sources or operational systems that provide data for further analysis have duplicate records and these are sent to a downstream application or EDW for processing. This post shows a few scenarios of how to handle these duplicate records using various SQL options, […]

This blog post was authored by Jim Knicely. The vsql \e and \edit meta-commands let you edit the query buffer (or specified file) with an external editor. The default editor on Linux is vi and notepad.exe on Windows systems. Because vsql searches the environment variables VSQL_EDITOR, EDITOR, and VISUAL (in that order) for an editor […]

This blog post was authored by Jim Knicely. There are multiple ways to extract just the time from date in Vertica. Which method you choose depends on your preferred result data type. Example: dbadmin=> CREATE TABLE public.test AS dbadmin-> SELECT sysdate, dbadmin-> CAST(sysdate AS TIME) just_time_as_time, dbadmin-> TO_CHAR(sysdate, ‘HH:MI:SS:US’) just_time_as_varchar dbadmin-> FROM dual; CREATE TABLE […]

This blog post was authored by Shrirang Kamat. This document demonstrates how you can use hierarchical partitions, introduced in Vertica 9.0, to solve a few example use cases. For more information, see Hierarchical Partitioning. Partitioning a table helps with managing the information life cycle. Since data for partitions is segregated into different ROS containers, predicates […]

This blog post was authored by Jim Knicely. Say I want to add 12 days to today’s date April, 12, 2018. That’s easy using date arithmetic. dbadmin=> SELECT ’04-12-2018′::DATE + 12 AS today_plus_12_business_days; today_plus_12_days ——————– 2018-04-24 (1 row) But what if I want to only add 12 “business” days and exclude the “weekend” days? That’s […]

This blog post was authored by Jim Knicely. Intervals measure the difference between two points in time. Converting an interval to a numeric is easy in Vertica because we can perform operations (i.e. division) on them! Examples: dbadmin=> SELECT INTERVAL ’24 HOURS’ / INTERVAL ‘1 DAY’ days; days —— 1 (1 row) dbadmin=> SELECT INTERVAL […]

This blog post was authored by Jim Knicely. The easiest way to extract just the numbers from a string of characters is to simply remove any character that isn’t a number! Example: dbadmin=> SELECT c1, regexp_replace(c1, ‘\D’, ”) FROM bunch_of_characters; c1 | regexp_replace —————–+—————- A1B2C3D4E5 | 12345 ABCDE12345 | 12345 Phone: 555-0100 | 5550100 1!2$3%4^5 […]

This blog post was authored by Soniya Shah. A Vertica database runs on a cluster of hardware. All loads and queries running against the database take up system resources, such as CPU, memory, disk I/O, bandwidth, file handles, and more. Query performance depends on how many resources are allocated to it. In a single-user environment, […]

This blog post was authored by Curtis Bennett. According to a quick internet search, the hottest recorded temperature in Los Angeles was 109 degrees Fahrenheit, recorded on July 8th, 2017. If you had access to all kinds of weather data (spoiler alert: you do!) you could query the data and figure out the highs and […]

This blog post was authored by Jim Knicely. An external table lets us query a text file that sits outside of the database as if it were a table internal to the database. Example: dbadmin=> CREATE EXTERNAL TABLE public.mood (pk INT, mood VARCHAR(100)) AS COPY FROM ‘/home/dbadmin/mood.txt’; CREATE TABLE dbadmin=> SELECT * FROM public.mood; pk […]

This blog post was authored by Jim Knicely. The AGE_IN_YEARS function returns the difference in years between two dates, expressed as an integer. This function is very useful when I forget how old I am or how old my wife is being that today is her birthday. Example: dbadmin=> SELECT extract(year from sysdate) “Current Year”, […]

This blog post was authored by Serge Bonte. Vertica’s row and column access policies can be used to provide extra security on data in your tables. These policies are well covered in Best Practices for Creating Access Policies in Vertica and Dynamic Row and Column Access Policies. In this blog, we will explore how dynamic […]

This blog post was authored by Jim Knicely. The V_MONITOR.CPU_USAGE system table records the CPU usage history by node. Example: I can easily find the top 5 highest average CPU usage on my cluster in the previous 24 hours: dbadmin=> SELECT start_time, dbadmin-> AVG(average_cpu_usage_percent) AS avg_cpu_usage dbadmin-> FROM v_monitor.cpu_usage dbadmin-> WHERE start_time BETWEEN NOW() – […]

This blog post was authored by Jim Knicely. The DDL produced by the EXPORT_OBJECTS function typically contains a create type for a projection. Example: CREATE PROJECTION public.test_super /*+basename(test),createtype(L)*/ ( c1 ) AS SELECT test.c1 FROM public.test ORDER BY test.c1 SEGMENTED BY hash(test.c1) ALL NODES OFFSET 0; In the above example the create type is L […]

This blog post was authored by Tim Donar. We had the opportunity to enter our Vertica flight tracking demo at the University of Central Arkansas’s university raspberry pi bakeoff. We won first place, competing against a number of other raspberry pi-based projects! For more information on how to use Vertica to track commercial aircraft in […]

This blog post was authored by Shrirang Kamat. The following design considerations will help you improve the performance and memory of your Vertica queries. When creating table definitions, you should carefully choose the size of the lookup column based on your data. Properly sizing your column based on your data will help to improve performance. […]

This blog post was authored by Jim Knicely. The GET_CLUSTER_STATE_SUMMARY function can be used to quickly view the current status of your Vertica cluster. Example: All nodes are up: dbadmin=> SELECT get_cluster_state_summary(); get_cluster_state_summary ———————————————————————————————————- Cluster State: test2 UP: 4 of 4 (v_test2_node0001, v_test2_node0002, v_test2_node0003, v_test2_node0004) (1 row) One node is down: dbadmin=> \! admintools -t […]

This blog post was authored by Jim Knicely. There exists a maximum number of characters that can be stored in columns having a data type of VARCHAR or LONG VARCHAR. In case you’ve forgotten those maximum values for the version of Vertica you are using, there are two handy functions that provide this information. Example: […]

This blog post was authored by Mark Whalley. I have discussed in previous blog posts the continuous stream of messages from aircraft transponders, captured and decoded using the DUMP1090 application, which we are planning on feeding into a series of Kafka topics, prior to loading into their corresponding tables in a Vertica database. This blog […]

This blog post was authored by Jim Knicely. By default Vertica is case sensitive when it comes to comparing strings. You can change this behavior by setting the session locale to LEN_S1. Example: dbadmin=> CREATE TABLE test (c1 VARCHAR(25)); CREATE TABLE dbadmin=> INSERT INTO test SELECT ‘Vertica Rocks!’; OUTPUT ——– 1 (1 row) dbadmin=> SELECT […]

This blog post was authored by Jim Knicely. The IDLESESSIONTIMEOUT parameter can be used to close a session that has been idle for a period of time. An idle session is one that has no queries running. Example: dbadmin=> CREATE USER jim IDLESESSIONTIMEOUT ’10 seconds’; CREATE USER dbadmin=> \c – jim You are now connected […]

This blog post was authored by Crystal Farley (North). I volunteered to be the Chairperson for the virtual chapter of DataGals because I think connecting women across the world is a great way to continue to #PushforProgress. Why not bridge the gap and create a community where we encourage diversity and inclusion of everyone that […]

This blog post was authored by Steve Sarsfield. Hadoop and HDFS is capable of storing massive volumes of data, but performing analytics on Hadoop can be challenging. Despite the apparent low-cost cost of Hadoop, it is best suited for data lake and data science solutions, where the number of concurrent analytical users is low. In […]

This blog post was authored by Jim Knicely. The EXPLAIN command returns the optimizer’s query plan for executing a specified query. Example: dbadmin=> EXPLAIN SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN ’09-19-2017′ AND ’09-19-2018′; —————————— QUERY PLAN DESCRIPTION: —————————— EXPLAIN SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN ’09-19-2017′ AND ’09-19-2018′; Access Path: +-STORAGE ACCESS for […]