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

Microsoft Power BI: Latest Release Enhances Connection to Vertica

Kathy Taylor authored this post. 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 […]

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

Master Blog Series: Vertica Database Administrators

This blog post was authored by Soniya Shah. 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 […]