Quick Tips

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

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

Displaying the Current Schema

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

What Version of Vertica am I Running?

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

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

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

Getting your Node Hardware Specs: Quick Tip

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

SQL Reserved Words: Quick Tip

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

Loading Negative (BC) Dates: Quick Tip

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

Superfast Table Copy (Revisited): Quick Tip

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

Handling OLE DB Square Brackets: Quick Tip

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

Vertica Quick Tip: Monitoring Changes to Configuration Parameters

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

Vertica Quick Tip: Replacing an Empty String

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

Vertica Quick Tip: Empty String Vs. NULL

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

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

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

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

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

Vertica Quick Tip: Monitoring the Ancient History Mark

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

Vertica Quick Tip: Determining Table Columns that Contain NULL Values

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

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

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

Vertica Quick Tip: Keeping DbVisualizer Alive

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

Vertica Quick Tip: Remove Duplicate Values from a String

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

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

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

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

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

Vertica Quick Tip: Who am I?

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

Vertica Quick Tip: Eon Mode

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

Vertica Quick Tip: Using vsql Almost Anywhere

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

Vertica Quick Tip: User Defined Functions to the Rescue

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

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

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

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

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

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

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

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

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

Vertica Quick Tip: Getting the Root of a Number

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

Vertica Quick Tip: Using a Preferred Editor in VSQL

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

Vertica Quick Tip: Extract Time from a Date

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

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

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

Vertica Quick Tip: Converting Intervals to Numeric

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

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

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

Vertica Quick Tip: Simulating DML Operations on External Tables

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

Vertica Quick Tip: How old am I?

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

Vertica Quick Tip: Monitoring CPU Usage

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

Vertica Quick Tip: Projection Create Types

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

Vertica Quick Tip: Summary of Cluster State

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

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

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

Vertica Quick Tip: Case Insensitive Session Queries

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

Vertica Quick Tip: Automatically Close an Idle Session

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

Vertica Quick Tip: EXPLAIN Plan in JSON Format

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

Vertica Quick Tip: On the Fly Date Dimension

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

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

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

Vertica Quick Tip: My SQL History

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

Vertica Quick Tip: Getting a Word Count

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

Vertica Quick Tip: Parsing a String as Rows

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

Vertica Quick Tip: Repeating

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

Vertica Quick Tip: Splitting a String into Rows

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

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

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

Vertica Quick Tip: What

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

Vertica Quick Tip: Keeping Track of Data Load File Sources

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

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

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

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

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

Vertica Quick Tip: Checking User Role Membership

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

Vertica Quick Tip: Increasing the Performance of a Rebalance

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

Vertica Quick Tip: Expiring a User’s Password

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

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

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

Vertica Quick Tip: Renaming a View

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

Vertica Quick Tip: Viewing Query Error Information

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

Vertica Quick Tip: Setting a Client Connection Label

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

Vertica Quick Tip: Analyzing Table Statistics by Column

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

Vertica Tip: The System Table for System Tables

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

Vertica Quick Tip: Sampling Data from the Entire Table

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

Vertica Quick Tip: Superfast Table Copy

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

Vertica Quick Tip: Add a Time Zone

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

Vertica Quick Tip: Default Size of the NUMBER Data Type

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

Vertica Quick Tip: Dynamically Split Up a String

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

Vertica Quick Tip: Proper Ordering of IP Addresses

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

Vertica Quick Tip: The <=> operator

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

Vertica Quick Tip: A Truly Unique Constraint

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

Vertica Quick Tip: Lightning Fast Text Search

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

Vertica Quick Tip: Generating a Random String

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

Vertica Quick Tip: Which Rows Will Commit?

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

Vertica Quick Tip: Date Arithmetic with Intervals

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

Vertica Quick Tip: Date Arithmetic

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

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

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

Vertica Quick Tip: Generating a Random Date

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

Vertica Quick Tip: The LIMIT Analytic Function

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

Vertica Quick Tip: How to Query for NaN Values

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