BLOB data insert into Table in SAP HANA

[Former Member]

Hello Hana Experts,

Is it possible to achieve below scenario in SAP Hana?

Scenario: We are able to insert the .csv file(data size is huge)  into SAP HANA with column type : BLOB is successful as shown in below image:

After this we want to read the content of the .csv and split the data into smaller parts with help of delimiter(like , or / etc)  or other conditions and put the data into another table and display as column based on the result.

1. How to read the content of the BLOB data which is unknown. Is it achieve by writing the SQL procedure language or any other techniques.

2. Please suggest us the better solutions how we can achieve such scenario in SAP HANA.

Thanking you.

Regards,

Pad

[Former Member]

Hello Lars,

Thank you so much for reply.

Consider CSV file will have  dummy data and insert into table (Data type : BLOB). For example:

In the below screen, we can able to see the data in DATA column like this-

Now we need to read the data from this column and extract one by one with delimiter "," and move into another table.  Is this scenario able to achieve it?.

I have used the substring to check the data but it doesn't support for BLOB data.

Thanks,

Pad

[lbreddemann]

Now you're just repeating what you want to do and not answering the question.

Of course a BLOB doesn't support SUBSTR() because that is a character function.

If you want it to work you might want to use a CLOB (as in Character-LOB) instead of a BLOB (as in Binary LOB).

Still, the whole scenario doesn't make much sense so far.

Where does the bulk data in the LOB column come from?

Why don't use a normal ETL process to get this data into SAP HANA tables?

[Former Member]

Hello Lars,

Even we do not know the data content details. Consider it will be the employee master data set which will directly uploaded into the HANA table and we need to check the data column by column as per the condition which is defined. Once Check is successful extract the data and put into another table.

From the UI screen they will extract the data again in the .csv file.

This is kind of one business cycle they will perform.

Upload the .csv into CLOB/BLOB --> Write SQL queries to read the data and perform the check->once check ok->Move into another table->UI extract the data in .csv file.

Thanks,

Pad

[lbreddemann]

You described a normal ETL scenario with the only difference that you seem to insist on doing the input parsing in SQL.

To me that is just making life hard for yourself without getting anything out of it.

[Former Member]

Hello Lars,

Actually its a customer design and we need to follow it. So we need advice from your side.

Thanks,

Pad

[lbreddemann]

I think what's required here is another discussion of the approach and what it implies. Blindly following some apparently misguided design won't do any good.

There must be reasons for why the design looks like this. Something that your customer wanted to achieve by it, since by itself it doesn't really have any value to it.

Maybe it was to keep the "original" source data available somewhere.

Maybe it was that they wanted to keep everything within SAP HANA.

I don't know what the reason for their design is and before that is not clear, the only advise (beside the one already given) can only be: clarify the reasons and goals that the design should achieve.

[lbreddemann]

This post reads eerily like another one I answered just last week (too lazy to dig it out now, but this whole thing reads a lot like "FAILED TO SEARCH" and "CROSS POSTING").

So, my advise is: search before posting questions!

Besides that, the whole requirement sounds as if you want to build an ETL solution on top of SAP HANA with plain SQL and the use of BLOBS.

The obvious first question here is of course: why BLOBS when dealing with CSV (character data)?

And of course as well: why would you want to do that in the first place?

There's SAP Data Services, there's SAP HANA Enterprise Information Management option and there are tons of other ETL tools that could handle this requirement.

Technically you can do loop over CLOB data and try to get out single column values (SUBSTR works on CLOBS), but you would still need to dynamically build a target structure.

Honestly, this approach doesn't seem to be thought through all that much.

my 2 ct.