Concatenating Columns In Apache Hive & Apache Impala

The CONCAT function in Apache Hive incorporates two or more strings into a single string. This function’s behavior is the same as in relational database systems. Only string values are accepted by this function. If you want to combine integer values, you need to convert them to strings before concatenation.

• Note: The below specified functions are common in both Apache Hive & Apache Impala.

Syntax: CONCAT(string|binary A, string|binary B…)

Example:
SELECT fname, lname, CONCAT(fname, ‘,’, lname) as name FROM firstlastnames;

If the requirement is to delimit the columns with comma, you can use CONCAT_WS.

CONCAT_WS:
Concat_WS functions similar to Concat, but with custom separator.

SELECT fname, lname, CONCAT_WS(‘ ‘, fname, lname) FROM firstlastnames;

If no column separator is defined, it will still function and will not return an error message. This will not, however, return the output desired. Look at the example given below.

SELECT fname, lname, CONCAT_WS(fname, lname) FROM firstlastnames;

If you notice, either CONCAT and CONCAT_WS can be used to combine multiple column values. Let’s see some more examples. If the prerequisite is to delimit strings other than comma, use the below command.

SELECT fname, lname, CONCAT(fname, ‘|’, lname) as name FROM firstlastnames;

SELECT fname, lname, CONCAT_WS(‘|’. fname, lname) as name FROM firstlastnames;

As stated above, this function can take any number of input strings. Refer to the below example.

Hope you liked this post.

Please click on the follow button to receive notifications on latest posts.