Concat and concat_ws function in Hive with examples

Concat function in Hive

The concat() function add two or more strings or bytes together. If any of the value is NULL, the result is also NULL. This function can take any number of input strings.

Syntax

  • string_1, string_2,…string_n – The input strings to add together.

Example 1: Add 2 strings together

In this example, we joined two strings such as “Data” and “Scientist” using concat function. It returned the output as “DataScientist”.

Concat function example in Hive
Concat function example in Hive

Example 2: NULL value in concat function

As shown below, we passed the NULL in the second argument of concat function. So it returned NULL.

Null value in the concat function
Null value in the concat function

Concatenation operator in Hive

The || operator can be used to join the strings together. It is a concatenation operator. Similar to concat() function, we can use this operator to add the strings together in Hive.

Concatenation operator in Hive
Concatenation operator in Hive

concat_ws function in Hive

The concat_ws() function adds two or more strings together with a separator. It requires at least two arguments, and uses the first argument to separate all following arguments.

If there is only one string to concatenate, concat_ws function does not append a separator.

Syntax

  • separator – It is a separator string/character
  • string_1, string_2,…string_n – The input strings to add together with separator.

Example 1: Joining string with separator

Here we used separator as dot character( . ). Hence concat_ws function has concatenated all the input strings with dot character.

concat_ws function example in Hive
concat_ws function example in Hive

Example 2: NULL as a separator

When the separator is NULL, the concat_ws function returns the output as NULL.

concat_ws with NULL as separator in Hive
concat_ws with NULL as separator in Hive

Example 3: Null value in the concat_ws function

If any of the input string is NULL, the concat_ws function ignores that value and concatenate the remaining strings with separator.

concat_ws function with NULL as input string in Hive
concat_ws function with NULL as input string in Hive

Example 4 : Single string value in the concat_ws

When there is only one string value in the concat_ws function, the separator will not be appended to the string.

concat_ws function with single string value in Hive
concat_ws function with single string value in Hive

Example 5: concat_ws function with collect_set in Hive

We have a table called Access_control in Hive. As below, It contains IAM role details for each access.

concat_ws on Hive table
concat_ws on Hive table

Our goal is to get the list of role names for each category in a row. To get the role names from a column, we are going to use collect_set function. It will give the unique role names in an array.

Collect_set example in Hive
Collect_set example in Hive

Now we are passing the array of role_names_set to concat_ws function with separator. It will add the separator comma(,) for each element and print the output in a single line.

concat_ws with collect_set example in Hive
concat_ws with collect_set example in Hive

If we want to sort the array before the concatenation, we can use sort_array function on top of collect_set function.

concat_ws function with sort_array in Hive
concat_ws function with sort_array in Hive

As show above, the role names are sorted first and then concat_ws function has applied to it.

Recommended Articles