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…)

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 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;

concat with pipe

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.

concat with additional

Hope you liked this post.

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


Leave a Reply

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

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

Facebook photo

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

Connecting to %s