Simulate NULLS FIRST and NULLS LAST in the ORDER BY Clause

When your query contains the ORDER BY clause to sort the result set, alphanumeric NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC), while integer NULL data does the opposite. Example: dbadmin=> \d test List of Fields […]

Using sed to Handle Empty String NULL Values Enclosed in Control Characters

You can load data files into a Vertica table using the COPY command. Your data can be delimited and separated by control characters. Unfortunately if you also enclose NULL values as an empty string, you might run into a data cast issue. Example: dbadmin=> \d test1 List of Fields by Tables Schema | Table | […]

Using COPY FILLER to Handle Empty String NULL Values Enclosed in Control Characters

You can load data files into a Vertica table using the COPY command. Your data can be delimited and separated by control characters. Unfortunately if you also enclose NULL values as an empty string, you might run into a data cast issue. Example: dbadmin=> \d test1 List of Fields by Tables Schema | Table | […]

Monitor/Clean Up the CopyErrorLogs Directory

The COPY statement automatically saves a copy of each rejected row in a rejected-data file. COPY also saves a corresponding explanation of what caused the rejection in an exceptions file. By default, Vertica saves both files in a database catalog subdirectory called CopyErrorLogs. After you’ve reviewed and resolved any issues with the load, it’s a […]

Tracking the Current Transaction Start Date and Time

The built-in Vertica function TRANSACTION_TIMESTAMP returns a value of type TIME WITH TIMEZONE, which represents the start of the current transaction. It’s very useful for keeping track of when the transaction started for a group of table inserts. Example: dbadmin=> SELECT * FROM test; c1 | created_timestamp | created_transaction_timestamp —-+——————-+——————————- (0 rows) dbadmin=> INSERT INTO […]

Simple ASCII Charting Using SQL

It is very easy to create a simple ASCII chart in Vertica using a SQL analytic function! Example: dbadmin=> SELECT c1 a_date, dbadmin-> count(*) cnt dbadmin-> FROM some_data_2_chart dbadmin-> GROUP BY 1 dbadmin-> ORDER BY 1 DESC; a_date | cnt ————+——— 2019-03-20 | 7600474 2019-03-19 | 4254718 2019-03-18 | 7327122 2019-03-17 | 8274362 2019-03-16 | […]

Arithmetic Bit-Shifting

A fun way to manipulate bits in Vertica SQL is with the use of arithmetic bit-shifting, which moves the bits in a number either left or right and fills in the new values with 0s. Bit-shifting left is an easy way to multiply by powers of 2, while bit-shifting right divides by powers of 2. […]

Convert an Integer to a Binary

Although there is not a built-in function in Vertica to convert an integer to a binary, you can use the Vertica TO_HEX and HEX_TO_BINARY functions to create your own! Example: dbadmin=> SELECT HEX_TO_BINARY(LPAD(TO_HEX(2 & 0xffffffff), 8, ‘0’))::VARBINARY INT_TO_BINARY; INT_TO_BINARY —————— \000\000\000\002 (1 row) dbadmin=> CREATE OR REPLACE FUNCTION int2bin (input INT) RETURN VARBINARY dbadmin-> AS […]

Switch the Sign of a Number

To switch the sign of a number you can multiply it by -1. Example: dbadmin=> SELECT col1, -1 * col1 col1_sign_switch dbadmin-> FROM test; col1 | col1_sign_switch ——+—————— 10 | -10 -10 | 10 3 | -3 -3 | 3 (4 rows) Or you can use the Vertica built-in function NUMERIC_UM to do it for […]

Are These Two String Values Like Each Other?

The Vertica built-in function LIKE is used to determine if two strings are like one another, while the NLIKE function is used to determine if two strings are not like one another. Example: dbadmin=> SELECT LIKE(‘ABCDEF’, ‘ABCDEF’); LIKE —— t (1 row) dbadmin=> SELECT NLIKE(‘ABCDEF’, ‘ABCDEF’); NLIKE ——- f (1 row) You can even include […]

Identify a Table’s Primary Key Columns

The Vertica system table PRIMARY_KEYS lists the columns in a table’s primary key. Example: dbadmin=> \dS primary_keys List of tables Schema | Name | Kind | Description | Comment ———–+————–+——–+————————-+——— v_catalog | primary_keys | system | Primary key information | (1 row) dbadmin=> SELECT column_name, ordinal_position dbadmin-> FROM primary_keys dbadmin-> WHERE table_name = ‘test_table’; column_name […]

Revoke Access to Multiple System Tables from Multiple Users with a New Role

Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system’s resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical […]

Revoke Access From Just One System Table

Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system’s resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical […]

Create a SQL Function to Calculate the nth Root of a Number

Vertica has a built-in function to calculate the arithmetic square root a number called SQRT and another built-in function to calculate the arithmetic cube root of a number called CBRT. Example: dbadmin=> SELECT sqrt(9); sqrt —— 3 (1 row) dbadmin=> SELECT cbrt(27); cbrt —— 3 (1 row) But what if I need a function to […]

Generate a Murmur Hash

Murmur (Murmerhash) is a modern non-cryptographic hash function that has a low collision rate and high performance. It is suitable for general hash-based lookups, but not suitable for cryptographic use cases. Vertica has a built-in function called HASH. Example: dbadmin=> SELECT murmurhash(‘VERTICA MURMUR Hash!’); murmurhash ——————— 6154089896006532138 (1 row) dbadmin=> SELECT murmurhash(‘The quick brown fox […]

Assign All Users’ Granted Roles as Default Roles

By default, no roles (other than the default PUBLIC Role) are enabled at the start of a user session. You have to assign one or more of a user’s granted roles as default roles so that they are enabled when a user logs on. You do this with the ALTER USER … DEFAULT ROLE command. […]

View Tuple Mover Operation Run-time Intervals

Vertica’s Tuple Mover manages WOS and ROS data storage. To do that, the Tuple Mover performs two operations: Moveout: Moves data from WOS to ROS. Mergeout: Combines small ROS containers into larger ones and purges deleted data. The Tuple Mover operations are controlled by a set of configurable parameters. To quickly view the relevant parameter’s […]

Auditing Flex Tables Quick Tip

The AUDIT_FLEX function returns the estimated ROS size of __raw__ column in a Flex table, equivalent to the export size of the flex data in the audited objects. Example: dbadmin=> CREATE FLEX TABLE flex_tables.flex(); CREATE TABLE dbadmin=> INSERT INTO flex_tables.flex SELECT 1 c1, 1 c2; OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO flex_tables.flex SELECT […]

Tracking Table Row Counts Over Time: Quick Tip

I would like to track the row counts from all of my database tables over time. Doing so will help me predict future growth. How do I do that? Like this… Example: dbadmin=> CREATE TABLE table_row_count(audit_date DATETIME, table_schema VARCHAR(128), table_name VARCHAR(128), row_count INT) dbadmin-> ORDER BY table_schema, table_name, audit_date UNSEGMENTED ALL NODES; CREATE TABLE dbadmin=> […]

Auditing Database DDL Operations: Quick Tip

Starting with Vertica 9.2, you can now track all DDL operations (i.e. CREATE, ALTER, TRUNCATE, etc.) that are being executed in your database! The LOG_QUERIES system table provides summary information about those operations. Example: dbadmin=> SELECT issued_time, dbadmin-> user_name, dbadmin-> audit_type, dbadmin-> request_type, dbadmin-> request dbadmin-> FROM log_queries dbadmin-> WHERE session_id = current_session() dbadmin-> ORDER […]

Reset the ROUNDROBIN Load Balancing Counter: Quick Tip

The ROUNDROBIN load balancing scheme chooses the next host from a circular list of hosts in the cluster that are up. You can use the RESET_LOAD_BALANCE_POLICY function to reset the counter each host in the cluster maintains to track which host it will refer a client to when the native connection load balancing scheme is […]

Random Host Load Balancing: Quick Tip

Native connection load balancing is a feature built into the Vertica server and client libraries that helps spread the CPU and memory overhead caused by client connections across the hosts in the database. It can prevent unequal distribution of client connections among hosts in the cluster. Typically folks implement the ROUNDROBIN load balancing scheme which […]

Another Way to De-Duplicate Table Rows: Quick Tip

To remove duplicate table rows it is typically better (i.e. faster) to create a temporary table that contains all of the distinct rows from a table, drop the original table, then rename the temp table to the original table’s name. Example: dbadmin=> SELECT * FROM dups; c1 | c2 —-+—- 1 | A 1 | […]

Combining Functions to Extract Discrete Data From a String: Quick Tip

Vertica has 1000s of built-in functions including String Functions, which perform conversion, extraction, or manipulation operations on strings or return information about strings. Regular Expression Functions let you perform pattern matching on strings of characters. There is real power in combing Vertica functions to accomplish almost any task in SQL. Example: I want to extract […]

What’s the Distance Between Two Zip Codes? Quick Tip

Vertica has a built-in function called DISTANCEV which returns the distance (in kilometers) between two points using the Vincenty formula. Because the Vincenty formula includes the parameters of the WGS-84 ellipsoid model, you need not specify a radius of curvature. You specify the latitude and longitude of both the starting point and the ending point. […]

Calculate a Year’s Chinese Zodiac Animal: Quick Tip

The Chinese Zodiac (Sheng Xiao) is based on a twelve-year cycle where each year is represented by an animal sign. The twelve animal signs in order are the rat, ox, tiger, rabbit, dragon, snake, horse, sheep, monkey, rooster, dog and pig. The animal sign is calculated according to the Chinese lunar calendar. I can never […]

Finding All Columns Storing Some Value: Quick Tip

A long time ago a database developer asked me how she could produce a list of all table columns in the database which store a given value. Note that she was in the process of normalizing tables and wanted to find every fact table that referenced a particular string value to replace it with a […]

Viewing External Table Definitions: Quick Tip

An alternative to importing data into Vertica is to query it in place. For that we use external tables. Once an external table is created you can view its definition via the TABLES system table. Example: dbadmin=> \! cat /home/dbadmin/ext.txt 1|TEST1 2|TEST2 dbadmin=> CREATE EXTERNAL TABLE ext (c1 INT, c2 VARCHAR(10)) AS COPY FROM ‘/home/dbadmin/ext.txt’; […]

Generate a Random Integer Within a Range: Quick Tip

The RANDOMINT functions returns one of n integers from 0 through n – 1. If you need to generate a random integer that falls within a range of integers between X and Y, inclusive, use this simple formula: FLOOR(RANDOM() * (Y – X + 1) + X) Example: dbadmin=> SELECT FLOOR(RANDOM() * (20 – 10 […]

Seeding a Random Integer: Quick Tip

The RANDOM function returns a uniformly-distributed random number x, where 0 SELECT random() random1, random() random2, random() random3; random1 | random2 | random3 ——————-+——————-+——————- 0.206615947652608 | 0.308142327470705 | 0.124399376800284 (1 row) dbadmin=> SELECT seeded_random(1) random1, seeded_random(1) random1, dbadmin-> seeded_random(2) random2, seeded_random(2) random2; random1 | random1 | random2 | random2 ——————-+——————-+——————+—————— 0.417021998437122 | 0.417021998437122 | 0.43599490262568 […]

Seeding a Random Number: Quick Tip

The RANDOM function returns a uniformly-distributed random number x, where 0 SELECT random() random1, random() random2, random() random3; random1 | random2 | random3 ——————+——————-+——————- 0.206615947652608 | 0.308142327470705 | 0.124399376800284 (1 row) dbadmin=> SELECT random() random1; random1 ——————- 0.517856472404674 (1 row) Notice above that each time I called the RANDOM function I got a new random […]

Limiting the Amount of Memory Available to a Query: Quick Tip

Every Vertica DBA has seen a user execute some crazy, memory intensive query that starves every other user request forcing them to queue. To avoid that situation, you can use the MAXQUERYMEMORYSIZE resource pool parameter, which sets the maximum amount of memory that the pool can allocate at runtime to process a query. Example: User […]

Display a Rounded Number With a Specific Scale: Quick Tip

The Vertica ROUND function rounds a value to a specified number of decimal places, retaining the original precision and scale. Fractions greater than or equal to .5 are rounded up. Fractions less than .5 are rounded down (truncated). Example: dbadmin=> SELECT ROUND(12345.678901726198736271, 2) AS c_rounded; c_rounded ————————– 12345.680000000000000000 (1 row) If you’d prefer not to […]

View Vertica Features Recently Used: Quick Tip

Curious if Vertica features are being used in your database? If so, the data collector table DC_FEATURES_USED stores recently used Vertica features (i.e. commands, meta-functions and UDXs). Example: dbadmin=> SELECT node_name, component, description dbadmin-> FROM data_collector dbadmin-> WHERE table_name = ‘dc_features_used’; node_name | component | description ——————–+————–+——————————– v_test_db_node0001 | FeaturesUsed | Vertica features used recently […]

Monitoring COPY Statement Events (Part 2): Quick Tip

A COPY command is typically composed of two phases. The second phase, if using the DIRECT parameter, includes sorting the data and writing the data in Disk. You can monitor the progress of this phase of the COPY command via the data collector table DC_MERGE_EVENTS. Example: dbadmin->vmart@sandbox1=> SELECT DISTINCT component, description dbadmin->vmart@sandbox1–> FROM data_collector dbadmin->vmart@sandbox1–> […]

Monitoring COPY Statement Events (Part 1): Quick Tip

A COPY command is typically composed of two phases. The first phase includes reading the data from the source, parsing the data, segmenting the data and sending it to the nodes after it is segmented. You can monitor the load events of this phase of the COPY command via the data collector table DC_LOAD_EVENTS. Example: […]

Nested Boolean Operators: Quick Tip

Vertica supports the following Boolean operators: • AND • OR • NOT Example: dbadmin=> SELECT 12 “Boolean Result”; Boolean Result —————- t (1 row) dbadmin=> SELECT NOT FALSE AND TRUE “Boolean Result”; Boolean Result —————- t (1 row) dbadmin=> SELECT NOT NOT NOT FALSE OR FALSE “Boolean Result”; Boolean Result —————- t (1 row) To […]

Using VSQL as a Calculator: Quick Tip

A calculator is something used for making mathematical calculations, in particular a small electronic device with a keyboard and a visual display. VSQL mostly fits that description! Example: dbadmin=> SELECT 325.25 * 1.60934 “Miles to Kilometers”; Miles to Kilometers ——————— 523.4378350 (1 row) dbadmin=> SELECT 119.25 * 0.25 “Excellent Waitress’s Tip”; Excellent Waitress’s Tip ————————– […]

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

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; CREATE SCHEMA dbadmin=> CREATE TABLE […]

Preserving Objects Owned by a Dropped User: Quick 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 otherwise lost. Example: dbadmin=> CREATE […]

List Table Rows by ROS Container: Quick 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 from a table that are […]

Tracking Save Points: Quick 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 be nested so it can […]

Keep Data Collector Information for a Set Interval: Quick Tip

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

Turn Off Row Counts in VSQL: Quick Tip

he 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 dual;” dummy ——- X (1 […]

Generate a Notice Not an Error When Adding a Duplicate Column to a Table: Quick 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 INT, c2 INT); CREATE TABLE […]

Swapping Schemas: Quick 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 dbadmin=> create table schema1.test1 (c […]

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

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=> SELECT * FROM view_fact_table; c1 […]

Copy Table History: Quick 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 row) dbadmin=> \timing on Timing […]

Restarting a Downed Node: Quick 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 down. Example: Here I’ll use […]

Handling Expression Errors in COPY Statements: Quick 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)); CREATE TABLE dbadmin=> \! cat […]

Encode Projection Columns with Zstandard Compression: Quick Tip

Vertica 9.2 now supports the Zstandard Compression Encoding Type which offers higher compression than the BZIP and GZIP Encoding Types! Example: dbadmin=> 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 […]