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.

Need help in Hive Query

Solved Go to solution
Highlighted

Need help in Hive Query

New Contributor

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Need help in Hive Query

Explorer

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

View solution in original post

2 REPLIES 2
Highlighted

Re: Need help in Hive Query

Explorer

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

View solution in original post

Highlighted

Re: Need help in Hive Query

New Contributor

Its working, Thanks for your help.

Don't have an account?
Coming from Hortonworks? Activate your account here