Need help in Hive Query

pal_1990 — Fri, 06 Mar 2020 15:31:14 GMT

I have a column in a table and value is (1;13004211,13004211_02_13004212,4000000003378605589,1105,2000,)need output like

Column 1	Column 2	Column 3	Column 4	Column 5
13004211	133004211_02_13004212	4000000003378605589	1105	2000

Note: I don't want the first 1;

Re: Need help in Hive Query

Ellyly — Fri, 06 Mar 2020 16:28:03 GMT

Hello @pal_1990,

I think your input is something like this:

+----------------------------------------------------+
|                    semicolon.a                     |
+----------------------------------------------------+
| 1;13004211,13004211_02_13004212,4000000003378605589,1105,2000 |
+----------------------------------------------------+

1 . You need to separate the one from other values, for this I use posexplode fonction:

select pe.i,pe.x from semicolon lateral view posexplode(split(a,';')) pe as i,x; +-------+----------------------------------------------------+ | pe.i | pe.x | +-------+----------------------------------------------------+ | 0 | 1 | | 1 | 13004211,13004211_02_13004212,4000000003378605589,1105,2000 | +-------+----------------------------------------------------+

2. You on only select where pe.i =1:

select t.x from 
(select pe.i,pe.x
from semicolon lateral view posexplode(split(a,';'))  pe as i,x) t where t.i=1 ;

+----------------------------------------------------+
| t.x                                                |
+----------------------------------------------------+
| 13004211,13004211_02_13004212,4000000003378605589,1105,2000 |
+----------------------------------------------------+

3. You split values in columns;

select split(t.x,',')[0] as col1,
       split(t.x,',')[1] as col2,
       split(t.x,',')[2] as col3,
       split(t.x,',')[3] as col4,
       split(t.x,',')[4] as col5
from 
(select pe.i,pe.x
from semicolon lateral view posexplode(split(a,';'))  pe as i,x) t where t.i=1 ;

+-----------+-----------------------+----------------------+-------+-------+
| col1 | col2 | col3 | col4 | col5 |
+-----------+-----------------------+----------------------+-------+-------+
| 13004211 | 13004211_02_13004212 | 4000000003378605589 | 1105 | 2000 |
+-----------+-----------------------+----------------------+-------+-------+

I hope it will help you.

Best regards

Re: Need help in Hive Query

pal_1990 — Wed, 11 Mar 2020 11:06:38 GMT

Its working, Thanks for your help.

question Need help in Hive Query in Support Questions

Need help in Hive Query

Re: Need help in Hive Query

Re: Need help in Hive Query