Best ways to extract COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|........|COLUMNn|va

carmac

Hi All,

I need to extract around 500+ columns and 5+ millions rows data of below type records.

COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|........|COLUMNn|valuen|

with t as

     (select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column

        from dual)

select *

from (  select *

from t);

Thank you very much for your consideration.

-Thanks

mathguy

500+ columns? That already suggests there's something seriously wrong with your data model.

In any case - standard INSTR and SUBSTR should be quite a bit faster than regular expressions. Something like this:

with  t as (    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column    from   dual  )select substr(data_column, instr(data_column, '|', 1, 1) + 1,               instr(data_column, '|', 1, 2) - instr(data_column, '|', 1, 1) - 1) as column1     , substr(data_column, instr(data_column, '|', 1, 3) + 1,              instr(data_column, '|', 1, 4) - instr(data_column, '|', 1, 3) - 1) as column2     , substr(data_column, instr(data_column, '|', 1, 5) + 1,              instr(data_column, '|', 1, 6) - instr(data_column, '|', 1, 5) - 1) as column3     , substr(data_column, instr(data_column, '|', 1, 7) + 1,              instr(data_column, '|', 1, 8) - instr(data_column, '|', 1, 7) - 1) as column4from   t;COLUMN1  COLUMN2  COLUMN3  COLUMN4-------- -------- -------- --------value1   value2   value3   value4 

Left to you for your amusement - write a bit of code, in whatever language, to generate the SQL statement to do this for "500+ columns" - you definitely don't want to do that by hand, right?

mathguy

500+ columns? That already suggests there's something seriously wrong with your data model.

In any case - standard INSTR and SUBSTR should be quite a bit faster than regular expressions. Something like this:

with  t as (    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column    from   dual  )select substr(data_column, instr(data_column, '|', 1, 1) + 1,               instr(data_column, '|', 1, 2) - instr(data_column, '|', 1, 1) - 1) as column1     , substr(data_column, instr(data_column, '|', 1, 3) + 1,              instr(data_column, '|', 1, 4) - instr(data_column, '|', 1, 3) - 1) as column2     , substr(data_column, instr(data_column, '|', 1, 5) + 1,              instr(data_column, '|', 1, 6) - instr(data_column, '|', 1, 5) - 1) as column3     , substr(data_column, instr(data_column, '|', 1, 7) + 1,              instr(data_column, '|', 1, 8) - instr(data_column, '|', 1, 7) - 1) as column4from   t;COLUMN1  COLUMN2  COLUMN3  COLUMN4-------- -------- -------- --------value1   value2   value3   value4 

Left to you for your amusement - write a bit of code, in whatever language, to generate the SQL statement to do this for "500+ columns" - you definitely don't want to do that by hand, right?

carmac

Thanks for your response,

The column and value pairs are not fixed in every row. Some time I may get 100 column value pairs and some time I get 10  column value pairs ....

is the above query works that scenario also?

Thanks in advance.

Saubhik

carmac wrote:Thanks for your response,The column and value pairs are not fixed in every row. Some time I may get 100 column value pairs and some time I get 10 column value pairs ....is the above query works that scenario also?Thanks in advance.

It's your problem and So, YOU need to provide the full test case, "Some time I may get 100 column value pairs and some time I get 10 column value pairs" what does this actually mean?

Can you get something like CLOUM3|val3|COMULN4|val4, where some values are missing at the beginning? or it's always be missing at the trailing end like 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3, can anything be missing in the middle? Is the missing value means NULL or something else? Is, the COLUMNn part will be there always? is that COLUMNn significant? Is the separator will be there always like COLUMN1|value1|||COLUMN3|value3COLUMN4|value4  What is your database version?

carmac

Hi,

My database version is 11g and my data appears like below.

COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|

COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|

COLUMN2|value2|COLUMN3|value3|

COLUMN1|value1|COLUMN4|value4|

COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|

COLUMN4|value4|

COLUMN3|value3|

COLUMN1|value1|COLUMN2|value2|

COLUMN2|value2|

COLUMN1|value1|

-Thanks

Ranagal

Dude,

Your data is the perfect example for the worst design. Have you ever come across a design concept called 'Normalization' ? If not, kindly ready what 1 NF is. If you understand, go for 2NF, 3NF and even BCNF.

If you can, then change the current design to follow the Normalization rules. If you can't change the design at this phase, then bring it to manager's notice and only then go for what is known as Dynamic SQL.

If you don't know what Dynamic SQL is, then kindly do a Google search. There are many examples.

Regards,

Ranagal

carmac

Using below I am getting the results as required, still there is some scope to optimize below query. Please advise if you have any ideas.

Really appreciate in advance.

with

  t as (

    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' as data_column

    from   dual

  )

select

    substr(data_column, instr(data_column, 'COLUMN1', 1, 1)+length('COLUMN1')+1,

        instr(substr(data_column, instr(data_column, 'COLUMN1', 1, 1)), '|',1,2) -

        instr(substr(data_column, instr(data_column, 'COLUMN1', 1, 1)), '|',1,1)-1) as col1,

    substr(data_column, instr(data_column, 'COLUMN3', 1, 1)+length('COLUMN3')+1,

        instr(substr(data_column, instr(data_column, 'COLUMN3', 1, 1)), '|',1,2) -

        instr(substr(data_column, instr(data_column, 'COLUMN3', 1, 1)), '|',1,1)-1) as col3

from t;

-Thanks

Paulzip

This is a terrible data format design, it actually shocks me how bad it is. It's inefficient, unnormalised it'll probably rely on dynamic solutions. Is this your design?  Is there any scope to change it?

William Robertson

It's almost a compressed form of JSON or XML. It might be a fun exercise to see if you can transform the raw data into one of those two formats and then use built-in tools to parse the result. Whether this will be any faster than the current approach is anybody's guess, though.

Solomon Yakobson

OP is on 11g, so no JSON. But below is JSON solution for 12C:

SELECT  TBL.ROWID RID,

        COLUMN_NAME,

        COLUMN_VALUE

  FROM  TBL,

        JSON_TABLE(

                   '[{' || RTRIM(REGEXP_REPLACE(STR,'([^|]+)\|([^|]+)\|','"COLUMN":"\1","VALUE":"\2"},{'),',{') || ']',

                   '$[*]'

                   COLUMNS(

                           COLUMN_NAME  VARCHAR2(30) PATH '$.COLUMN',

                           COLUMN_VALUE VARCHAR2(30) PATH '$.VALUE'

                          )

                  )

/

RID                COLUMN_NAME                    COLUMN_VALUE

------------------ ------------------------------ ------------------------------

AAAcDAAALAAAHQbAAA COLUMN1                        value1

AAAcDAAALAAAHQbAAA COLUMN2                        value2

AAAcDAAALAAAHQbAAA COLUMN3                        value3

AAAcDAAALAAAHQbAAA COLUMN4                        value4

AAAcDAAALAAAHQbAAB COLUMN1                        value1

AAAcDAAALAAAHQbAAB COLUMN3                        value3

AAAcDAAALAAAHQbAAB COLUMN4                        value4

AAAcDAAALAAAHQbAAC COLUMN2                        value2

AAAcDAAALAAAHQbAAC COLUMN3                        value3

AAAcDAAALAAAHQbAAD COLUMN1                        value1

AAAcDAAALAAAHQbAAD COLUMN4                        value4

AAAcDAAALAAAHQbAAE COLUMN2                        value2

AAAcDAAALAAAHQbAAE COLUMN3                        value3

AAAcDAAALAAAHQbAAE COLUMN4                        value4

AAAcDAAALAAAHQbAAF COLUMN4                        value4

AAAcDAAALAAAHQbAAG COLUMN3                        value3

AAAcDAAALAAAHQbAAH COLUMN1                        value1

AAAcDAAALAAAHQbAAH COLUMN2                        value2

AAAcDAAALAAAHQbAAI COLUMN2                        value2

AAAcDAAALAAAHQbAAJ COLUMN1                        value1

20 rows selected.

SQL>

SY.

Solomon Yakobson

XML solution - will work in 11G:

SELECT  TBL.ROWID RID,

        COLUMN_NAME,

        COLUMN_VALUE

  FROM  TBL,

        XMLTABLE(

                 '/DOC/SET'

                 PASSING XMLTYPE('<DOC>' || REGEXP_REPLACE(STR,'([^|]+)\|([^|]+)\|','<SET><COLUMN>\1</COLUMN><VALUE>\2</VALUE></SET>') || '</DOC>')

                  COLUMNS

                    COLUMN_NAME  VARCHAR2(30) PATH '/SET/COLUMN',

                    COLUMN_VALUE VARCHAR2(30) PATH '/SET/VALUE'

                 )

/

RID                COLUMN_NAME                    COLUMN_VALUE

------------------ ------------------------------ ------------------------------

AAAcDAAALAAAHQbAAA COLUMN1                        value1

AAAcDAAALAAAHQbAAA COLUMN2                        value2

AAAcDAAALAAAHQbAAA COLUMN3                        value3

AAAcDAAALAAAHQbAAA COLUMN4                        value4

AAAcDAAALAAAHQbAAB COLUMN1                        value1

AAAcDAAALAAAHQbAAB COLUMN3                        value3

AAAcDAAALAAAHQbAAB COLUMN4                        value4

AAAcDAAALAAAHQbAAC COLUMN2                        value2

AAAcDAAALAAAHQbAAC COLUMN3                        value3

AAAcDAAALAAAHQbAAD COLUMN1                        value1

AAAcDAAALAAAHQbAAD COLUMN4                        value4

AAAcDAAALAAAHQbAAE COLUMN2                        value2

AAAcDAAALAAAHQbAAE COLUMN3                        value3

AAAcDAAALAAAHQbAAE COLUMN4                        value4

AAAcDAAALAAAHQbAAF COLUMN4                        value4

AAAcDAAALAAAHQbAAG COLUMN3                        value3

AAAcDAAALAAAHQbAAH COLUMN1                        value1

AAAcDAAALAAAHQbAAH COLUMN2                        value2

AAAcDAAALAAAHQbAAI COLUMN2                        value2

AAAcDAAALAAAHQbAAJ COLUMN1                        value1

20 rows selected.

SQL>

SY.

carmac

Thank you all I really appreciate your help.

Solomon Yakobson

Hierarchical no regexp solution (will work in 11G):

SELECT  ROWID RID,

        SUBSTR(STR,INSTR('|' || STR,'|',1,2 * LEVEL - 1),INSTR(STR,'|',1,2 * LEVEL - 1) - INSTR('|' || STR,'|',1,2 * LEVEL - 1)) COLUMN_NAME,

        SUBSTR(STR,INSTR(STR,'|',1,2 * LEVEL - 1) + 1,INSTR(STR,'|',1,2 * LEVEL) - INSTR(STR,'|',1,2 * LEVEL - 1) - 1) COLUMN_VALUE

  FROM  TBL

  CONNECT BY ROWID = PRIOR ROWID

         AND PRIOR SYS_GUID() IS NOT NULL

         AND INSTR(STR,'|',1,2 * LEVEL) != 0

/

RID                COLUMN_NAME     COLUMN_VALUE

------------------ --------------- ---------------

AAAcDAAALAAAHQbAAA COLUMN1         value1

AAAcDAAALAAAHQbAAA COLUMN2         value2

AAAcDAAALAAAHQbAAA COLUMN3         value3

AAAcDAAALAAAHQbAAA COLUMN4         value4

AAAcDAAALAAAHQbAAB COLUMN1         value1

AAAcDAAALAAAHQbAAB COLUMN3         value3

AAAcDAAALAAAHQbAAB COLUMN4         value4

AAAcDAAALAAAHQbAAC COLUMN2         value2

AAAcDAAALAAAHQbAAC COLUMN3         value3

AAAcDAAALAAAHQbAAD COLUMN1         value1

AAAcDAAALAAAHQbAAD COLUMN4         value4

AAAcDAAALAAAHQbAAE COLUMN2         value2

AAAcDAAALAAAHQbAAE COLUMN3         value3

AAAcDAAALAAAHQbAAE COLUMN4         value4

AAAcDAAALAAAHQbAAF COLUMN4         value4

AAAcDAAALAAAHQbAAG COLUMN3         value3

AAAcDAAALAAAHQbAAH COLUMN1         value1

AAAcDAAALAAAHQbAAH COLUMN2         value2

AAAcDAAALAAAHQbAAI COLUMN2         value2

AAAcDAAALAAAHQbAAJ COLUMN1         value1

20 rows selected.

SQL>

SY.

mathguy

carmac wrote:Hi,My database version is 11g and my data appears like below.COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4| COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|COLUMN2|value2|COLUMN3|value3|COLUMN1|value1|COLUMN4|value4| COLUMN2|value2|COLUMN3|value3|COLUMN4|value4| COLUMN4|value4| COLUMN3|value3|COLUMN1|value1|COLUMN2|value2|COLUMN2|value2|COLUMN1|value1|-Thanks

First, let's be clear regarding the desired output.

In your original post, you show that you need the "values" to line up into columns, one row of input resulting in one row of output. Compare that to what Solomon Yakobson proposed in Replies 9, 10 and 12: the output is presented in an entirely different format. Your format (with multiple columns) is the proper relational arrangement, if the columns all mean different things (if they represent different attributes of the same entity). The format in SY's replies is entity-attribute-value, not a good arrangement in relational databases. That format is more appropriate when all the tokens are "of the same kind" - which doesn't seem to be your case. Anyway, you need to confirm the format you need.

Then: assuming that you do, indeed, need a "relational" output, you must know the number of columns in the output before you write the query. Most importantly, the number of columns must be the same in every row; the data you show above makes no sense. There is no concept of table in which one row has four columns, another only two columns, and some rows only have column 2 and column 4 but no column 1 and no column 3. If you can show us how that would look, then perhaps we can help, but at this point it's not clear what the desired output should look like, with the input I copied and pasted above.

What my query will do in this case: You must hard-code the number of columns. Let's call that number "n". Then, if an input row has more than n column-value pairs, the output will only show the values from the first n columns, ignoring the remaining ones. If an input row has fewer than n column-value pairs, the output will show all the values in the first columns of output, filling the remaining columns with NULL. And, if your input only has COLUMN2 and COLUMN4 values but no COLUMN1 and COLUMN3, then value2 and value4 will appear in the first two columns (not in columns 2 and 4).

Note that that's exactly what YOUR query will do. Your query that you had in the original post, and that you have now deleted for some reason. Why did you do that? That is very uncool.

If you are OK with hardcoding a (maximum) number of columns, accepting that rows that have more column-value pairs will lose information and that columns that don't have a value in the input will get NULL in the output (so, if the input has COLUMN2 and COLUMN4 values, they will appear in the proper columns in the output), that can be done too, but the code is more complicated. You will still want to do everything with standard INSTR and SUBSTR; you will NOT want to convert the inputs to JSON or XML, and you will NOT want to get the output in EAV format, only to pivot back into single rows of output for each input row; both of those operations will make execution time worse. I'll rig a solution with INSTR/SUBSTR for your example to show what I mean, when I get a few minutes.   EDIT:  Actually, now that I took a closer look at some of the Replies, I see what you are doing in Reply 6. That's the approach I have in mind. The way you wrote it is partially wrong: if the input row does not have COLUMN1 but it does have COLUMN10, it will match COLUMN10 where you are seeking 'COLUMN1' (and the length computations won't match so you won't even get 'value10' in the first column, you'll rather get something like '|value1' (chopping off the last character from 'value10' and instead showing a leading pipe symbol). But all of that can be fixed. Whether it can be made faster is a different question, I suspect not. You can try the different answers offered here; it would be good to let us know what you find when you test on your actual data.

Paulzip

William Robertson wrote:It's almost a compressed form of JSON or XML. It might be a fun exercise to see if you can transform the raw data into one of those two formats and then use built-in tools to parse the result. Whether this will be any faster than the current approach is anybody's guess, though.

Yes, when I first saw it I thought "That might be better as JSON", but you could argue it would be a lot easier to just get rid of the columnN|valueN|.... approach and just do it as a delimited file of values and consume it with an external table + merge / insert.

COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|

COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|

COLUMN2|value2|COLUMN3|value3|

COLUMN1|value1|COLUMN4|value4|

becomes....

value1|value2|value3|value4

value1||value3|value4

|value2|value3|

value1|||value4

Personally I think OP's approach is about as bad as it gets.

mathguy

Working on a solution for the inputs you show in Reply 4, I realized that your solution from Reply 6 will do even worse than I explained in Reply 13. If 'COLUMN1' is not found at all, the corresponding INSTR is 0, and the query will work from that. It will always produce something in column 1 of the output, even when there is no 'COLUMN1|' in the input.

The following solution is better. It will still go wrong if the value part of a pair may contain the substring COLUMN<number> right at the end. For example, it will give the wrong result on the following input:

COLUMN1|value is 2 * COLUMN2|COLUMN2|something|

Do you see how    COLUMN2|   appears in the input string, as part of "value1|", sooner than we expect to see it? That will make the whole thing blow up. You can mitigate it somewhat by doubling the pipe symbols first (and adding pipes at the ends of the string), but even that will fail on the following input:

COLUMN1|COLUMN2|COLUMN2|value2|

(that is, in this arrangement the value1 string is exactly 'COLUMN2')  This too can be fixed, but it becomes more and more complicated, and you still won't know for sure that you didn't miss some other obscure special case.

In the solution below, I assume two things: (1) The pipe symbol can only appear as terminator (for "names" and "values" in column name - value pairs), and (2) the "value" part of the pairs can't have the substring COLUMN<number> at the end.  With these assumptions, I believe the following query will do what you need. Remember that input "columns" beyond what is hardcoded in the query will simply be ignored. This is illustrated in the inputs I chose.

with  t (data_column) as (    select 'COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|' from dual union all    select 'COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|'                from dual union all    select 'COLUMN2|value2|COLUMN3|value3|'                               from dual union all    select 'COLUMN1|value1|COLUMN4|value4|'                               from dual union all    select 'COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|'                from dual union all    select 'COLUMN4|value4|'                                              from dual union all    select 'COLUMN3|value3|'                                              from dual union all    select 'COLUMN1|value1|COLUMN3|value3|'                               from dual union all    select 'COLUMN2|value2|'                                              from dual union all    select 'COLUMN3|value3|COLUMN10|value10|'                             from dual  )select data_column     , substr(data_column, instr(data_column, 'COLUMN1|') + length('COLUMN1|'),              instr(data_column, '|', instr(data_column, 'COLUMN1|'), 2)                  - instr(data_column, 'COLUMN1|') - length('COLUMN1|')) as column1     , substr(data_column, instr(data_column, 'COLUMN2|') + length('COLUMN2|'),              instr(data_column, '|', instr(data_column, 'COLUMN2|'), 2)                  - instr(data_column, 'COLUMN2|') - length('COLUMN2|')) as column2     , substr(data_column, instr(data_column, 'COLUMN3|') + length('COLUMN3|'),              instr(data_column, '|', instr(data_column, 'COLUMN3|'), 2)                  - instr(data_column, 'COLUMN3|') - length('COLUMN3|')) as column3from  t;DATA_COLUMN                                                  COLUMN1  COLUMN2  COLUMN3------------------------------------------------------------ -------- -------- --------COLUMN1|value1|COLUMN2|value2|COLUMN3|value3|COLUMN4|value4| value1   value2   value3 COLUMN1|value1|COLUMN3|value3|COLUMN4|value4|                value1            value3 COLUMN2|value2|COLUMN3|value3|                                        value2   value3 COLUMN1|value1|COLUMN4|value4|                               value1                   COLUMN2|value2|COLUMN3|value3|COLUMN4|value4|                         value2   value3 COLUMN4|value4|                                                                       COLUMN3|value3|                                                                value3 COLUMN1|value1|COLUMN3|value3|                               value1            value3 COLUMN2|value2|                                                       value2         COLUMN3|value3|COLUMN10|value10|                                               value3 

William Robertson

I agree it's a horrible format, but to be fair to the OP it may come from some application that perversely insists on sending it like that. All the same, I would push back and see if anything could be done about the format, or failing that, whether some pre-processing step could be applied in Perl or Python or something that could convert it to regular CSV with all 100 columns per record, or even to XML so at least there would be a supported way to parse it.

William Robertson

Just to confirm once and for all in case I missed a detail, does the real data contain literally COLUMN4 etc, and are those actual column names in the table being loaded, or were those just simplified examples and the real date is more like CUSTOMER_ID, PURCHASE_QUANTITY, CHEESE_TYPE, UNIT_PRICE, DISCOUNT_PERCENT etc?

carmac

Hi All,

please suggest me how to extract and load below type data into another table if the column data type is CLOB. some of the rows of the length is 12000 bytes or characters.

with

  t (clob_column) as (

    select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all

    select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|'                from dual union all

    select 'CUST_NAME|value2|CUST_DEPT|value3|'                               from dual union all

    select 'CUST_ID|value1|ITEM_ID|value4|'                               from dual union all

    select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|'                from dual union all

    select 'ITEM_ID|value4|'                                              from dual union all

    select 'CUST_DEPT|value3|'                                              from dual union all

    select 'CUST_ID|value1|CUST_DEPT|value3|'                               from dual union all

    select 'CUST_NAME|value2|'                                              from dual union all

    select 'CUST_DEPT|value3|CUST_ID0|value10|'                             from dual

  )

  select * from t;

Thanks in advance.

Ranagal

This is a different question in itself than that was asked before for which you even marked the answer. Kindly open a new thread for this. You are better off re-designing the whole process of taking the data in as your current design reeks of violations of basic DB Design Rules.

Regards,

Ranagal