Vertica Blog

OpenText Vertica 23.3 – the Smarter Data Lakehouse

Posted July 31, 2023 by Paige Roberts, Vertica Open Source Relations Manager

Read More

Unveiling the Most Recent Version of the Vertica Grafana Data Source Plugin

Amrita Akshay, Information Developer
October 12, 2023

With over 380K downloads, the Vertica Grafana Data Source plugin just got an upgrade! The plugin was migrated from the deprecated older Grafana toolkit to align with Grafana's new Create-Plugin tool. This accelerates the plugin development with their modern build set up that requires no additional configuration. Additionally, the Vertica SQL Go driver received an...
Quick Tip on a blue enter key on a keyboard

Setting Session Authorization to Troubleshoot

Sruthi Anumula, Senior Database Support Engineer
September 29, 2023

There are possible scenarios in which a dbadmin would want to run queries as another user to troubleshoot or test. You can use SET SESSION AUTHORIZATION to impersonate another user and run queries. Let's understand this with an example. Here we create a user named test, resource pool named userpool, and make this a default...

Rejected Data Table Row Number: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 15, 2018

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 an empty line while loading...
Database Server Room

Reload Data from a Rejected Data Table: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 14, 2018

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 the rejected data in the...

Handling Cast Conversion Load Errors: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 13, 2018

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. Vertica cannot implicitly cast the NULL value to...

Subscribe For Email Updates

Sign-up and select Vertica in your preferences to receive our monthly Vertica newsletter.

Sign-up

Return All Cast Failures as NULL: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 12, 2018

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 the nifty cast ::! instead...

Microsoft Power BI: Latest Release Enhances Connection to Vertica

Kathy Taylor, Information Technology Developer, Vertica
November 12, 2018

We are excited to announce the new Vertica connector introduced in the October 2018 release of Microsoft Power BI: • With PowerBI Desktop, Vertica is now fully supported using DirectQuery Mode (push-down optimization). • With Power BI Service (Cloud Offering - Saas), DirectQuery mode is now supported with Vertica using the Power BI On-Premises Gateway....

Display Canceled Queries: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 8, 2018

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. Now I will run a simple SQL statement and hit CTRL+C after waiting for 2 seconds: Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/Glossary/DataCollector.htm Have fun!

Explore Popular Topics

SQL
DBadmin
Advanced Analytics
machine learning
Best Practices
Cloud
VSQL
Eon Mode
Analytics
Partner Integrations
Vertica
Open Source
AWS
IoT
Time series

Calculate Request Queue Length: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 7, 2018

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 before acquiring the resources it...

Concatenate non-NULL Values from a Group: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 6, 2018

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. Have fun!

Simplify String Literals with Dollar-Quoted String Literals: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 6, 2018

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 single quote syntax. Helpful Link:...
Three 3D arrows, different colors pointing in different directions

Master Blog Series: Vertica Database Administrators

Soniya Shah, Information Developer
November 5, 2018

Are you a database administrator looking for ways to get the most from your Vertica database? If so, this post is for you. You’re already familiar with the technicalities of Vertica – the Tuple Mover, deletes, projections, and more. If you’re looking to get started, check out our master blog for new users, Master Blog...

Re-Compute a Table Column’s Default Value Immediately: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 1, 2018

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 in your derived column (the...
Modern Database Analytics

Re-Compute a Table Column’s Default Value: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 31, 2018

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...