Cloudera Impala String Functions

SPACE function in Apache Hive & Apache Impala

There are some functions that hardly come into use. Often this kind of function would have some other replacement that we use. SPACE is one of the functions introduced by Cloudera’s Impala, which returns the concatenated string to the specified number of spaces. This function is available in Apache Hive too. Let’s see what it

Continue reading

REVERSE Function in Apache Hive & Apache Impala

REVERSE(STRING a)The REVERSE function accepts a character expression as its argument, and returns a string of the same length, but the ordinal position of each logical character is reversed. However, this only works for STRING. This function and its behaviour is same in both Apache Hive & Impala Example:SELECT REVERSE(‘slipknot’); In case if you want

Continue reading

TRANSLATE & REPLACE functions in Cloudera’s Impala & Apache Hive

This article introduces the new TRANSLATE and REPLACE string functions available from version 2.9.0 of Cloudera Impala. Both of these functions look identical, shifting letters from one to the other. There is a major difference between them, however. Let’s see how close these functions are: SELECT REPLACE (‘Flat_720’, ‘_’, ‘#’);SELECT TRANSLATE (‘Flat_720′,’_’,’#’); Result:Both returns “Flat#720′

Continue reading

String Functions in Impala – STRLEFT, STRRIGHT & SUBSTR

String functions are used to execute an input string operation and return the output string. There are many built-in string functions available on almost all RDBMS platforms and big data tools such as Hive and Impala. Names can differ slightly from each other, but the functionality is the same. For eg, the LEFT and RIGHT

Continue reading

Split equivalent in Impala

Split function splits the data based on the delimiter provided and it is mostly used function in Apache Hive. This function is not available in Impala. However, there is an alternative to it. Let us first see the usage of the “split” function in Hive. Below is the patient’s blood pressure variations information. TableName: PatientsDataSystolic-Diastolic122/80, 122/83,

Continue reading

Trimming Functions in Apache Hive & Apache Impala

Trimming functions help to remove spaces from a given string. These functions are often used in data cleansing operations during the ETL cycle. While this is not a new innovation for SQL lovers, there is something that Cloudera’s Impala is offering that everybody would appreciate. Let’s look into the trimming functions one by one. LTRIM:This

Continue reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s