Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

HIVE - Concatenates all columns easily

Highlighted

HIVE - Concatenates all columns easily

New Contributor

I want to write hql that concatenates all columns in a table with comma separated.

ex)

target_table

col1col2col3col4
a01b01100X1
a02b02200X2
a03b03300X3


Execute SELECT query

SELECT 
   COL1
  ,COL2
  ,COL3
  ,COL4
  ,CONCAT???(T1.*) AS CONCAT_COL -- SQL for Concatenates all columns
 FROM target_table AS T1

Expected result

col1col2col3col4CONCAT_COL
a01b01100X1a01,b01,100,X1
a02b02200X2a02,b02,100,X2
a03b03300X3a03,b03,300,X3
  • CONCAT(COL1, ',', COL2, ',', COL2, ...) is a one of idea ,however if target table has too many columns or number of columns will increase in the future, I have to write long hql and it is difficult to manage.
  • CONCAT_WS(',' , T1.*) FROM target_tbl AS T1 is FAILED 'NullPointerException null'

Is there anyone has good idea for this ??

Thank you.

2 REPLIES 2

Re: HIVE - Concatenates all columns easily

New Contributor

I'm not sure what your original goal is - the following approach may help; or possibly not...

Beeline has some output format features which enables it to output the resultset in csv/tsv2 format. see: https://community.hortonworks.com/questions/25789/how-to-dump-the-output-from-beeline.html?childToVi...

I think with `concat_ws` you are very close; but I'm afraid its not possible to convert a struct into an array (or at least into a map - in which case you might be able to use `map_values` to obtain the array)

Re: HIVE - Concatenates all columns easily

New Contributor

You need to use CONCAT_WS (separator,col1,col2,...colX) :

select CONCAT_WS ('',c12,c13,c14,c15,c16) FROM your_table;

,

Use CONCAT_WS , where you give the separator as 1st term and then the list of columns to concat :

select CONCAT_WS (' ',c12,c13,c14,c15,c16) from your_table) ;