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
|
1 |
concat(string_1, string_2,...string_n) |
- 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”.
|
1 |
select concat('Data','Scientist'); |
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.
|
1 |
select concat('Data',NULL); |
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.
|
1 |
select 'Data'||'Engineer'; |
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
|
1 |
concat_ws(separator, string_1, string_2 ,...string_n) |
- 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.
|
1 |
SELECT concat_ws('.', 'www', 'RevisitClass', 'com'); |
Example 2: NULL as a separator
When the separator is NULL, the concat_ws function returns the output as NULL.
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.
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.
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.
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.
|
1 2 3 4 |
select category, collect_set(Role_Name) as role_names_set from Access_control group by category; |
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.
|
1 2 3 4 |
select category, concat_ws(',',collect_set(Role_Name)) as role_names_set from Access_control group by category; |
If we want to sort the array before the concatenation, we can use sort_array function on top of collect_set function.
|
1 2 3 4 |
select category, concat_ws(',',sort_array(collect_set(Role_Name))) as role_names_set from Access_control group by category; |
As show above, the role names are sorted first and then concat_ws function has applied to it.
Recommended Articles
Your Suggestions
