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…
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 & ImpalaExample:SELECT REVERSE(‘slipknot’);In case if you want to reverse a…
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’…
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…
Concatenate rows (group concatenation) in MySQL, Hive, SQL Server and Oracle
The CONCAT() function joins the input strings or different column values and returns as a single string. However, if we need to concatenate rows of strings (rows from the same column) into a single string with a desired separator, this will not work. There comes a function that fulfills this requirement and this function known…
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,…
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 function removes…
Big Data and SQL 