Concatenate multiple columns into one in hive

Use concat_ws function to concatenate values with ^ as a delimiter.

Example with constants:

hive> select concat_ws('^','ABCD','10', 'XYZ');OKABCD^10^XYZ

Command with column names after shell variable substitution should look like this:

 select concat_ws('^',col1,col2,col3) as result from table;

In the shell it will look like this:

colnames=col1,col2,col3hive -e "select concat_ws('^',${colnames}) as result from table"

If columns are not string, wrap them with cast as string using shell, this will allow concat_ws work with strings and not-string columns.

Example

colnames=col1,col2,col3colnames2=$(echo "cast( $colnames as string)" | sed "s/,/ as string), cast( /g")echo "$colnames2"

Output:

cast( col1 as string), cast( col2 as string), cast( col3 as string)

Use new variable to pass to hive as in the previous example.

CodeHunter