Vertica Blog

Vertica Blog

SQL

Using sed to Handle Empty String NULL Values Enclosed in Control Characters

James Knicely, Vertica Field Chief Technologist
March 19, 2019

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: One work around is to clean up the source file using the...

Using COPY FILLER to Handle Empty String NULL Values Enclosed in Control Characters

James Knicely, Vertica Field Chief Technologist
March 18, 2019

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: One work around is to use a COPY FILLER. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/BulkLoadingData.htm...
Three 3D arrows, different colors pointing in different directions

Monitor/Clean Up the CopyErrorLogs Directory

James Knicely, Vertica Field Chief Technologist
March 15, 2019

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

James Knicely, Vertica Field Chief Technologist
March 14, 2019

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=> INSERT INTO test SELECT 1, SYSDATE, TRANSACTION_TIMESTAMP(); OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO...
Modern Database Analytics

Simple ASCII Charting Using SQL

James Knicely, Vertica Field Chief Technologist
March 13, 2019

It is very easy to create a simple ASCII chart in Vertica using a SQL analytic function! Example: It’s a lot easier to visualize the differences in row counts when viewing them as a chart column! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/REPEAT.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/MAXAnalytic.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm

Arithmetic Bit-Shifting

James Knicely, Vertica Field Chief Technologist
March 12, 2019

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

James Knicely, Vertica Field Chief Technologist
March 11, 2019

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: Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/HEX_TO_BINARY.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/BinaryDataTypes.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/INTEGER.htm

Switch the Sign of a Number

James Knicely, Vertica Field Chief Technologist
March 8, 2019

To switch the sign of a number you can multiply it by -1. Or you can use the Vertica built-in function NUMERIC_UM to do it for you!

Are These Two String Values Like Each Other?

James Knicely, Vertica Field Chief Technologist
March 7, 2019

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. You can even include wildcard symbols!

Identify a Table’s Primary Key Columns

James Knicely, Vertica Field Chief Technologist
March 7, 2019

The Vertica system table PRIMARY_KEYS lists the columns in a table's primary key. You can use the LISTAGG function to group the columns of a composite key into a single record! Helpful Link: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/PRIMARY_KEYS.htm

Create a SQL Function to Calculate the nth Root of a Number

James Knicely, Vertica Field Chief Technologist
February 27, 2019

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. But what if I need a function to calculate the arithmetic nth root of a number? No problem. I can create my own! Helpful Links:...

Generate a Murmur Hash

James Knicely, Vertica Field Chief Technologist
February 26, 2019

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. Helpful Links: HASH Murmurhash: What is it?