Quick Tips

Extracting Characters from the Left and Right Side of a LONG VARCHAR: Quick Tip

The LEFT SQL function returns the specified characters from the left side of a string, while the RIGHT SQL function returns the specified characters from the right side of a string. Example: dbadmin=> SELECT left(‘Vertica is cool!’, 7), right(‘Everybody loves Vertica’, 7); left | right ——–+——— Vertica | Vertica (1 row) Unfortunately, the LEFT and […]

Determining the Current K-Safety: Quick Tip

K-safety sets the fault tolerance in your Vertica database cluster. The value K represents the number of times the data in the database cluster is replicated. These replicas allow other nodes to take over query processing for any failed nodes. You can view the current K-Safety of a Vertica database in several ways. Example: dbadmin=> […]

Specifying CASCADE When Dropping a Table: Quick Tip

The DROP TABLE command removes a table and its projections. Example: dbadmin=> CREATE TABLE test1 (c INT); CREATE TABLE dbadmin=> INSERT INTO test1 SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> SELECT projection_name, create_type FROM projections WHERE anchor_table_name = ‘test1’; projection_name | create_type —————–+—————— test1_super | DELAYED CREATION (1 row) dbadmin=> DROP TABLE test1; DROP […]

Ordering the List of Values Returned from LISTAGG

The LISTAGG function transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to de-normalize rows into a string of comma-separated values or other human-readable formats. Example: dbadmin=> SELECT * dbadmin-> FROM valrank; id | rank | valname —+——+——— 1 | […]

Ignore NULL Values in Non-Correlated Subqueries: Quick 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. A subquery returns data that the outer query uses as a condition to determine what data to retrieve. […]

Drop More than One Table at a Time: Quick Tip

The DROP TABLE command removes a table and its projections. You can drop more than one table at a time by specifying a comma delimited set of tables! Example: dbadmin=> CREATE TABLE test1 (c INT); CREATE TABLE dbadmin=> DROP TABLE test1; DROP TABLE That was easy. How about dropping more than one table? dbadmin=> CREATE […]

Allowing Users to Query a View Owned by Another User: Quick Tip

As we learned in yesterday’s Vertica Quick Tip A View Owner Needs Access to the Underlying Objects, the owner of a View must have direct access to the underlying objects referenced by the view! But what if another user wants to read a view owned by another user? In this case, not only must the […]

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

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

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

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

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

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

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

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

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

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

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

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

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

Jim Knicely authored this post. Although there aren’t any specific functions that will return the number of days that have passed and that are remaining in a given year, you can combine a few of Vertica’s built-in date functions to find these numbers. Example: dbadmin=> SELECT sysdate “Today”, dbadmin-> DATEDIFF(day, TRUNC(sysdate, ‘YY’), TRUNC(sysdate)) “# of […]

Displaying the Current Schema

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

What Version of Vertica am I Running?

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

Which One of these Nodes Doesn’t Belong? A Vertica Quick Tip

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

Getting your Node Hardware Specs: Quick Tip

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

SQL Reserved Words: Quick Tip

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

Loading Negative (BC) Dates: Quick Tip

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

Superfast Table Copy (Revisited): Quick Tip

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

Handling OLE DB Square Brackets: Quick Tip

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

Vertica Quick Tip: Monitoring Changes to Configuration Parameters

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

Vertica Quick Tip: Replacing an Empty String

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

Vertica Quick Tip: Empty String Vs. NULL

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

Vertica Quick Tip: Which Column in my Flattened Table caused the “Subquery used as an expression returned more than one row” Error?

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

Vertica Quick Tip: Tuple Mover (TM) Assigned More Resources in Vertica 9.1

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

Vertica Quick Tip: Monitoring the Ancient History Mark

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

Vertica Quick Tip: Determining Table Columns that Contain NULL Values

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

Vertica Quick Tip: Getting Better Performance via External Table Row Counts

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

Vertica Quick Tip: Keeping DbVisualizer Alive

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

Vertica Quick Tip: Remove Duplicate Values from a String

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, ‘(?

Vertica Quick Tip: When Modifying a SEARCH_PATH, Don’t Forget to Include PUBLIC

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

Vertica Quick Tip: Use the Overlay Function to Easily Insert a String Into Another String

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

Vertica Quick Tip: Who am I?

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

Vertica Quick Tip: Eon Mode

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

Vertica Quick Tip: Using vsql Almost Anywhere

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

Vertica Quick Tip: User Defined Functions to the Rescue

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

Vertica Quick Tip: Get Rid of all Those Q’s

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

Vertica Quick Tip: Forget the Column Name of a System Table?

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

Vertica Quick Tip: Capitalize Only the First Letter of Each Word

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

Vertica Quick Tip: Check if a Date Range Overlaps another Date Range

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

Vertica Quick Tip: Getting the Root of a Number

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!

Vertica Quick Tip: Using a Preferred Editor in VSQL

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

Vertica Quick Tip: Extract Time from a Date

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

Vertica Quick Tip: Add Days to a Date, excluding SAT and SUN

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

Vertica Quick Tip: Converting Intervals to Numeric

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

Vertica Quick Tip: Extract Just Numbers from a String of Characters

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

Vertica Quick Tip: Simulating DML Operations on External Tables

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

Vertica Quick Tip: How old am I?

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

Vertica Quick Tip: Monitoring CPU Usage

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

Vertica Quick Tip: Projection Create Types

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

Vertica Quick Tip: Summary of Cluster State

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

Vertica Quick Tip: What’s the Maximum Column Size?

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

Vertica Quick Tip: Case Insensitive Session Queries

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

Vertica Quick Tip: Automatically Close an Idle Session

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

Vertica Quick Tip: EXPLAIN Plan in JSON Format

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

Vertica Quick Tip: On the Fly Date Dimension

This blog post was authored by Jim Knicely. The Vertica TIMESERIES Clause is an important component of time series analytics computation. It performs gap filling and interpolation (GFI) to generate time slices missing from the input records. I like to use it to create a dynamic date dimension for my data warehouse. Example: dbadmin=> CREATE […]

Vertica Quick Tip: Enable \timing from the vsql Command Line

This blog post was authored by Jim Knicely. The vsql \timing meta-command reports, in milliseconds, the length of time each SQL statement runs. Example: dbadmin=> \timing Timing is on. dbadmin=> SELECT COUNT(*) FROM big_varchar_table WHERE pk BETWEEN 1000000 AND 2000000; COUNT ——— 1000001 (1 row) Time: First fetch (1 row): 179.609 ms. All rows formatted: […]

Vertica Quick Tip: My SQL History

This blog post was authored by Jim Knicely. In vsql you can use the \s meta-command to view your command line history: Example: dbadmin=> CREATE TABLE some_table (c1 INT, c2 VARCHAR(10)); CREATE TABLE dbadmin=> INSERT INTO some_table SELECT 1, ‘TEST1’; OUTPUT ——– 1 (1 row) dbadmin=> ALTER TABLE some_table ADD COLUMN c3 int; ALTER TABLE […]

Vertica Quick Tip: Getting a Word Count

This blog post was authored by Jim Knicely. The Vertica REGEXP_COUNT function returns the number times a regular expression matches a string. You can use it to create your own user-defined SQL function that counts the number of words in a string. Example: dbadmin=> CREATE OR REPLACE FUNCTION get_word_count(x varchar) RETURN INT dbadmin-> AS dbadmin-> […]

Vertica Quick Tip: Parsing a String as Rows

This blog post was authored by Jim Knicely. A simple SQL trick makes it easy to expand an entire string into separate rows. Example: dbadmin=> SELECT substr(‘Vertica is Awesome!’, x, 1) forward_string_parse dbadmin-> FROM (SELECT row_number() OVER () x FROM tables) foo dbadmin-> WHERE substr(‘Vertica is Awesome!’, x, 1) ”; forward_string_parse ———————- V e r […]

Vertica Quick Tip: Repeating

This blog post was authored by Jim Knicely. The Vertica REPEAT function replicates a string the specified number of times, and concatenates the replicated values as a single string. Examples: dbadmin=> SELECT repeat(‘TesT’, 5); repeat ———————- TesTTesTTesTTesTTesT (1 row) dbadmin=> SELECT ‘1’ || repeat(‘0’, 100) AS “One Googolplex”; One Googolplex ——————————————————————————————————- 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (1 row) This […]

Vertica Quick Tip: Splitting a String into Rows

This blog post was authored by Jim Knicely. The Vertica Text Search feature includes a handy function named StringTokenizerDelim that you can use to split a string into rows by a given delimiter. Example: dbadmin=> SELECT * FROM test; c1 | c2 —-+——- 1 | A|B|C 2 | D|E|F (2 rows) dbadmin=> SELECT c1, words […]

Vertica Quick Tip: Getting Every n-th Row from a Table

This blog post was authored by Jim Knicely. You can use the Vertica MOD function to grab every nth row from a table. Example: dbadmin=> SELECT * FROM nth_row ORDER BY 1; id | some_data —-+———– 1 | TEST1 2 | TEST2 3 | TEST3 4 | TEST4 5 | TEST5 6 | TEST6 7 […]

Vertica Quick Tip: What

This blog post was authored by Jim Knicely. Planning a road trip to Disney? How far is it? Vertica has a built-in function called DISTANCE, which returns the distance (in kilometers) between two points. You specify the latitude and longitude of both the starting point and the ending point. You can also specify the radius […]

Vertica Quick Tip: Keeping Track of Data Load File Sources

This blog post was authored by Jim Knicely. If you are like most companies you will be loading data into a Vertica table from many different data files. And if you are like me, you’ll forget later which file a table record arrived in. Fortunately the CURRENT_LOAD_SOURCE function returns the file name used when executing […]

Vertica Quick Tip: What’s the first day of the month?

This blog post was authored by Jim Knicely. In the last Vertica Quick Tip we learned about the Vertica built-in LAST_DAY function. So where’s the FIRST_DAY function? You’ll have to create your own. So how do you compute the first day of the month for a given date? There are many ways, each involving a […]

Vertica Quick Tip: What’s the last day of the month?

This blog post was authored by Jim Knicely. The Vertica built-in LAST_DAY function returns the last day of the month for a specified date. This function comes in handy for leap years. Example: dbadmin=> SELECT last_day(’02/28/2018′) NOT_A_LEAP_YEAR, last_day(’02/28/2020′) A_LEAP_YEAR; NOT_A_LEAP_YEAR | A_LEAP_YEAR —————–+————- 2018-02-28 | 2020-02-29 (1 row) In one of my previous positions, an […]

Vertica Quick Tip: Checking User Role Membership

This blog post was authored by Jim Knicely. The HAS_ROLE function returns a Boolean value that indicates whether a role has been assigned to a user. Example: To create a read only user and role, do the following: dbadmin=> CREATE ROLE read_only_role; CREATE ROLE dbadmin=> CREATE USER read_only; CREATE USER dbadmin=> GRANT read_only_role TO read_only; […]

Vertica Quick Tip: Increasing the Performance of a Rebalance

This blog post was authored by Jim Knicely. Before performing a rebalance, Vertica by default will query system tables to compute the size of all projections involved in the rebalance task. This query can add significant overhead to the rebalance operation! To disable this query, set the configuration parameter RebalanceQueryStorageContainers to 0. Example: dbadmin=> SELECT […]

Vertica Quick Tip: Expiring a User’s Password

This blog post was authored by Jim Knicely. You can expire a user’s password immediately using the ALTER USER statement’s PASSWORD EXPIRE parameter. By expiring a password, you can: • Force users to comply with a change to password policy. • Set a new password when a user forgets the old password. This feature also […]

Vertica Quick Tip: VSQL Shortcuts to Move Faster on the Command Line

This blog post was authored by Jim Knicely. vsql is Vertica’s character-based, interactive, front-end utility that lets you type SQL statements and see the results. If you’ve typed a particularly long query in vsql then realize that you’d have a typo way back at the beginning of your code (i.e. you wrote SEELECT), instead of […]

Vertica Quick Tip: Renaming a View

This blog post was authored by Jim Knicely. You are probably aware that you can rename a table using the ALTER TABLE … RENAME command. Example: dbadmin=> \dt test List of tables Schema | Name | Kind | Owner | Comment ——–+——+——-+———+——— public | test | table | dbadmin | (1 row) dbadmin=> ALTER TABLE […]

Vertica Quick Tip: Viewing Query Error Information

This blog post was authored by Jim Knicely. The V_MONITOR.ERROR_MESSAGES system table tracks error and warning messages encountered while processing queries. Example: dbadmin=> CREATE TABLE 123 (c1 INT); ERROR 4856: Syntax error at or near “123” at character 14 LINE 1: CREATE TABLE 123 (c1 INT); ^ dbadmin=> SELECT event_timestamp, user_name, message FROM error_messages ORDER […]

Vertica Quick Tip: Setting a Client Connection Label

This blog post was authored by Jim Knicely. When you connect to a Vertica database you can set a client connection label to help you later identify the connection. Example: dbadmin=> SELECT set_client_label(‘Daily Load’); set_client_label ——————————– client_label set to Daily Load (1 row) dbadmin=> SELECT get_client_label(); get_client_label —————— Daily Load (1 row) dbadmin=> SELECT client_label […]

Vertica Quick Tip: Analyzing Table Statistics by Column

This blog post was authored by Jim Knicely. The ANALYZE_STATISTICS function collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. On a very large wide table it will take a significant amount of time to gather those statistics. In many situations only a few columns […]

Vertica Tip: The System Table for System Tables

This blog post was authored by Sarah Lemaire. Most of you probably know that Vertica provides system tables that allow you to monitor • System resources • Background processes • Workload • Performance • Catalog size These tables help you to profile, diagnose, and view historical data equivalent to load streams, query profiles, Tuple Mover […]

Vertica Quick Tip: Sampling Data from the Entire Table

This blog post was authored by Jim Knicely. A development or quality assurance team often times requests access to a sub-set of production data. One way to do that would be to make use of the LIMIT clause. Example: dbadmin=> SELECT COUNT(*) FROM big_number_table; COUNT ———— 1000000000 (1 row) dbadmin=> SELECT 0.05*1000000000 “5_percent”; 5_percent ————- […]

Vertica Quick Tip: Superfast Table Copy

This blog post was authored by Jim Knicely. Very often we need to make a copy of a very large table in order to do some development or quality assurance type of duties. Typically we’ll use a CREATE TABLE AS SELECT (CTAS) statement to make that copy. Example: dbadmin=> SELECT COUNT(*) FROM big_number_table; COUNT ———— […]

Vertica Quick Tip: Add a Time Zone

This blog post was authored by Jim Knicely. Vertica recognizes many time zones. However, there might come a time (zone) when you will need to reference one that is not available by default. Luckily it’s relatively easy to add a time zone to Vertica. Example: The “Hawaii Standard Time (HST)” is a default time zone […]

Vertica Quick Tip: Default Size of the NUMBER Data Type

This blog post was authored by Jim Knicely. When creating a table where you do not define a precision for a NUMBER column data type, Vertica will use a default precision of 38 digits. Often this is larger than necessary. By specifying NUMBER(37) you will potentially get better query performance and save on storage. Why? […]

Vertica Quick Tip: Dynamically Split Up a String

This blog post was authored by Jim Knicely. One of my favorite functions in Vertica is named SPLIT_PART. It splits up a string into parts by a given delimiter. Example: dbadmin=> SELECT split_part(my_text, ‘,’, 1) the_first_part, dbadmin-> split_part(my_text, ‘,’, 2) the_second_part, dbadmin-> split_part(my_text, ‘,’, 3) the_third_part, dbadmin-> split_part(my_text, ‘,’, 4) the_fourth_part dbadmin-> FROM (SELECT ‘ONE,TWO,THREE,FOUR’ […]

Vertica Quick Tip: Proper Ordering of IP Addresses

This blog post was authored by Jim Knicely. Often times we store IP addresses in a VARCHAR column in a Vertica table. When querying the data and sorting by the IP address, we see that IP addresses are sorted by its VARCHAR value instead of its numeric value. Fortunately Vertica has the INET_ATON function which […]

Vertica Quick Tip: The <=> operator

This blog post was authored by Jim Knicely. The operator performs an equality comparison like the = operator, but it returns true, instead of NULL, if both operands are NULL, and false, instead of NULL, if one operand is NULL. Example: dbadmin=> SELECT 1 = 2 “Returns FALSE”, dbadmin-> 1 2 “Returns FALSE”, dbadmin-> 1 […]

Vertica Quick Tip: A Truly Unique Constraint

This blog post was authored by Jim Knicely. According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values. A Unique Constraint in Vertica does just that! Example: dbadmin=> CREATE TABLE test (c1 INT); CREATE TABLE dbadmin=> ALTER TABLE test ADD CONSTRAINT test_uk UNIQUE […]

Vertica Quick Tip: Lightning Fast Text Search

This blog post was authored by Jim Knicely. Searching the contents of a sizeable CHAR, VARCHAR, LONG VARCHAR, VARBINARY, or LONG VARBINARY field within a table to locate a specific keyword can be quite time consuming. Especially when dealing in Big Data. Fortunately, Vertica includes a text indexing feature which allows you to query that […]

Vertica Quick Tip: Generating a Random String

This blog post was authored by Jim Knicely. We saw in a previous Vertica Quick Tip that we can create a SQL function that generates random dates. How about one that generates random strings? Example: dbadmin=> CREATE OR REPLACE FUNCTION randomstring (x INT) RETURN VARCHAR dbadmin-> AS dbadmin-> BEGIN dbadmin-> RETURN CASE x dbadmin-> WHEN […]

Vertica Quick Tip: Which Rows Will Commit?

This blog post was authored by Jim Knicely. Did you ever update a bunch of rows in a table, then forget which ones you changed? Fearing you might have updated an incorrect record, you might have to roll back and start again. Or, in Vertica you can first check which records have been modified prior […]

Vertica Quick Tip: Date Arithmetic with Intervals

This blog post was authored by Jim Knicely. In the last Vertica Quick Tip we saw how easy date arithmetic can be. Well, it can be even easier with Intervals! Example: What is today’s, yesterday’s and tomorrow’s date? dbadmin=> SELECT SYSDATE Today, dbadmin-> SYSDATE – INTERVAL ‘1 Day’ Yesterday, dbadmin-> SYSDATE + INTERVAL ‘1 Day’ […]

Vertica Quick Tip: Date Arithmetic

This blog post was authored by Jim Knicely. Date arithmetic in Vertica is extremely easy! Example: What is today’s, yesterday’s and tomorrow’s date? dbadmin=> SELECT SYSDATE Today, dbadmin-> SYSDATE – 1 Yesterday, dbadmin-> SYSDATE + 1 Tomorrow; Today | Yesterday | Tomorrow —————————-+—————————-+—————————- 2018-01-18 11:36:43.132482 | 2018-01-17 11:36:43.132482 | 2018-01-19 11:36:43.132482 (1 row) But you’re […]

Vertica Quick Tip: Avoid Using Functions on Very Large Data Sets

This blog post was authored by Jim Knicely. You can store billions and billions and billions (i.e. a lot) of records in your Vertica tables. When querying these large data sets, try to avoid using database functions like TO_DATE, TO_CHAR, NVL, etc. when unnecessary. Example: A table named BIG_DATE_TABLE has 1 billion rows and a […]

Vertica Quick Tip: Generating a Random Date

This blog post was authored by Jim Knicely. I can easily generate a random integer value using the Vertica built-in RANDOMINT function. For example: dbadmin=> SELECT randomint(10) “Random 0-9”, dbadmin-> randomint(10) “Random 0-9”, dbadmin-> randomint(10) “Random 0-9”; Random 0-9 | Random 0-9 | Random 0-9 ————+————+———— 6 | 4 | 0 (1 row) But what […]

Vertica Quick Tip: The LIMIT Analytic Function

This blog post was authored by Jim Knicely. Vertica contains an abundance of built-in SQL analytic functions. One of the lesser known but also one of the coolest is the LIMIT analytic function. Example Say I have the following table data: dbadmin => SELECT * FROM limit_test; the_date | test_num | test_desc ————+———-+———– 2018-01-10 | […]

Vertica Quick Tip: How to Query for NaN Values

This blog post was authored by Jim Knicely. We’re introducing a new series: Vertica Quick Tips! These tips are intended to give you concise information to help you get the most out of using Vertica. NaN (Not a Number) does not equal anything, not even another NaN. You can query for them using the predicate […]