Support Questions

## Need help in Hive Query

Solved Go to solution

## 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

## 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 |+-----------+-----------------------+----------------------+-------+-------+  ```

Best regards

2 REPLIES 2

## 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 |+-----------+-----------------------+----------------------+-------+-------+  ```