Quick Tips

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

Single Sign-On for Support: New Login for Passport Users

As part of the Micro Focus ongoing IT transformation, we have consolidated single sign-on systems to improve your experience of interacting with Micro Focus. All Passport users need to create a new account. To maintain your current access rights, you need to register with the same e-mail address you used with passport. If you use […]

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

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

Before Upgrading: Identify and Remove Unsupported Projections

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

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

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

Default a Column’s Value: Quick Tip

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

Display View Column Data Types: Quick Tip

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

Insert Text Into Text: Quick Tip

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

Reset Your Session: Quick Tip

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

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

Cascading Schema Ownership

Jim Knicely authored this tip.</br? Yesterday’s quick tip revealed that as of Vertica 9.1.1, you can transfer the ownership of a schema to another user. But what about the underlying schema objects (i.e., TABLES, VIEWS, etc.)?</br? By default, the ALTER SCHEMA…OWNER TO command does not affect ownership of objects in the target schema or the […]

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

Permanently Attach a Comment to a Query: Quick Tip

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

Comparing Two Times: Quick Tip

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

Avoid a Division by Zero Error with the NULLIF and NULLIFZERO Functions: Quick Tip

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

Determining Candidate Segmentation Keys: Quick Tip

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

Comparing Two Dates: Quick Tip

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

Table Last Accessed

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

Determining the Per-Node Storage of a Segmented Table

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

Changing the Default Database Time Zone

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

Vertica Quick Tip: Keepalive settings for AWS Network Load Balancer

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

Counting Days, Week Days and Weekend Days Across the Years: Quick Tip

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

Synonyms: Quick Tip

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

Average Date: Quick Tip

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!

The Case of Object Names: Quick Tip

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

Changing the Data Type of a Column in an External Table: Quick Tip

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

Limiting a User’s Open Session Count: Quick Tip

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

NULL Equals NULL with NULLSEQUAL: Quick Tip

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

Handling NULL Equality in a WHERE Clause: Quick Tip

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

Faster CTAS Statements: Quick Tip

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

Analyze Statistics at the Schema Level (Part 2): Quick Tip

Jim Knicely authored this tip. The ANALYZE_STATISTICS function only accepts a table/projection/column name as input. In yesterday’s Vertica Quick Tip we learned how to get Vertica to generate and execute ANALYZE_STATISTICS SQL statements, one for each table in a given schema. It was an okay solution, but not very convenient. A better option would be […]

Analyze Statistics at the Schema Level (Part 1): Quick Tip

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

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, ‘(?<=\, |^)(.+?)\, (?=(.+\, )?\1(\, |$))’, ”) FROM test; c1 | regexp_replace ————————+—————- A, B, B, A | B, […]

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

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’s the Distance Between Two Points

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