Support Questions
Find answers, ask questions, and share your expertise

technical problem

New Contributor

Hi Folks ,

I am facing one issue , i have tried all the methods but its not working can you please help on this . 

 

one column have data like below its there row wise (Original column)

 

IT Strategy& Architecture BDC India [MITX 999],Corporate & IC Solution Delivery [SVII],

Operations Solution Delivery [SVIA],

Mainframe Service [MLEM],Strategy & Architecture [MLEL]

 

the above 3 rows i need to split into two columns like below 

1st column

IT Strategy& Architecture BDC India,Corporate & IC Solution Delivery,Operations Solution Delivery,Mainframe Service,Strategy & Architecture 

 

2nd columns

 

[MITX 999],[SVII],[SVIA],[MLEM], [MLEL]

 

please help , working new for impala data lake . Thank You ..!

1 ACCEPTED SOLUTION

Accepted Solutions

Cloudera Employee

Something like this should work. It should just be a matter of using the correct string manipulation functions: https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/impala_string_functions.html

 

create table test1 (col1 string);

insert into table test1 values ("IT Strategy& Architecture BDC India [MITX 999]"), ("Corporate & IC Solution
Delivery [SVII]"), ("Operations Solution Delivery [SVIA]"), ("Mainframe Service [MLEM]"), ("Strategy & Architecture [MLEL]");

select * from test1;
+------------------------------------------------+
| col1 |
+------------------------------------------------+
| IT Strategy& Architecture BDC India [MITX 999] |
| Corporate & IC Solution Delivery [SVII] |
| Operations Solution Delivery [SVIA] |
| Mainframe Service [MLEM] |
| Strategy & Architecture [MLEL] |
+------------------------------------------------+

create table test2 as select trim(split_part(col1, ' [', 1)), trim(concat(' [', split_part(col1, ' [', 2))) fr
om test1;

select * from test2;
+-------------------------------------+------------+
| _c0 | _c1 |
+-------------------------------------+------------+
| IT Strategy& Architecture BDC India | [MITX 999] |
| Corporate & IC Solution Delivery | [SVII] |
| Operations Solution Delivery | [SVIA] |
| Mainframe Service | [MLEM] |
| Strategy & Architecture | [MLEL] |
+-------------------------------------+------------+

 

View solution in original post

1 REPLY 1

Cloudera Employee

Something like this should work. It should just be a matter of using the correct string manipulation functions: https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/impala_string_functions.html

 

create table test1 (col1 string);

insert into table test1 values ("IT Strategy& Architecture BDC India [MITX 999]"), ("Corporate & IC Solution
Delivery [SVII]"), ("Operations Solution Delivery [SVIA]"), ("Mainframe Service [MLEM]"), ("Strategy & Architecture [MLEL]");

select * from test1;
+------------------------------------------------+
| col1 |
+------------------------------------------------+
| IT Strategy& Architecture BDC India [MITX 999] |
| Corporate & IC Solution Delivery [SVII] |
| Operations Solution Delivery [SVIA] |
| Mainframe Service [MLEM] |
| Strategy & Architecture [MLEL] |
+------------------------------------------------+

create table test2 as select trim(split_part(col1, ' [', 1)), trim(concat(' [', split_part(col1, ' [', 2))) fr
om test1;

select * from test2;
+-------------------------------------+------------+
| _c0 | _c1 |
+-------------------------------------+------------+
| IT Strategy& Architecture BDC India | [MITX 999] |
| Corporate & IC Solution Delivery | [SVII] |
| Operations Solution Delivery | [SVIA] |
| Mainframe Service | [MLEM] |
| Strategy & Architecture | [MLEL] |
+-------------------------------------+------------+

 

View solution in original post