technical problem

[#1]

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 ..!

[#2 · Accepted solution]

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]     |
+-------------------------------------+------------+