Thanks, Google!

Joy King authored this post. This week began with a compliment from Google that made me so proud on behalf of Vertica! On Wednesday, July 25, at the Google Cloud Next 2018 event, Google launched a Machine Learning (ML) beta with two algorithms – linear and logistic regression – and also confirmed that these new […]

What are your Data Loading Preferences?

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

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

Convenience But at What Cost?

Joy King authored this post. Convenience is so important to me. Move quickly and get things done. That’s probably why I am so addicted to Amazon Prime despite the fact that I could save money if I would just do a little research and find better prices for the things I buy. My impatience and […]

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

Sending Emails from the Vertica Database

Maurizio Felici authored this post. You can create an external procedure to send alert emails from the Vertica database. For more information on external procedures see Using External Procedures in the Vertica documentation. Follow the procedure below to create an external procedure for sending emails. NOTE: These procedures were performed on the Linux OpenSUSE distribution. […]

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

Master Blog Series : Vertica in Eon Mode

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

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

How many seconds since midnight?

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

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

The Internet of Things (IoT) and Smart Metering

Joy King authored this post. Every month, my husband receives a driver’s report from Chevrolet, providing him with “feedback” on his driving behavior – everything from the intensity of his braking to the number of lane changes without a blinker to the distance and speed he drove in his Silverado truck. The report also provides […]

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

Using Java UDX in Vertica

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

DataGals, Colin, Meg, and Micro Focus

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

Cerner Wins TDWI Best Practices Award

Steve Sarsfield authored this post. It’s amazing to see our customers ‘crush it’ when it comes to their implementation and best practices industry awards. That’s why I was so happy to hear that Cerner, a long-time Vertica customer, is the recipient of TDWI’s Best practices award for data warehouse. I want to congratulate Cerner on […]