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;

concat with comma

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;

concat_ws2

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;

concat_ws

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;

concat with pipe

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

concat_ws3

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

concat with additional

Hope you liked this post.

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

5 comments

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