updating bulk data into a HANA table from Excel or csv file.

[Former Member]

Hi,

I want to know how to update bulk data into a HANA table.

Ex: considering Raw data is already existing in Hana Table having 9000 records..

Table structure is like Column 1, Column 2 are composite primary key, i want to update column 3 data of some 5000+ records, so the new data are presenting in an excel sheet. i want to replace new column 3 data into a hana existing table by checking through Column 1 & column 2 as primary key condition...

I know the syntax for update

update SCHEMA.TABLE_name
set COLUMN_NAME = VALUE  WHERE CONDITION

but same method i cannot write 5750 times.

so i want to update through excel or csv with bulk.

for testing i enclosed a sample data in a file..to know the flow..

original_price_fact_data is the raw data...

updating_price_fact_data is the updating file..

Please suggest how to resolve this issue..

Thanks,

Thara.

[Former Member · Accepted solution]

Hi Thara,

you can achieve this by uploading the CSV data into two different tables and then creating a stored procedure that calls a cursor to loop through each row in the table to be uploaded and update the corresponding row in the final table.

For ex:

Follow these steps to upload data into a table

1) create a file (this is called as the control file ) with content as:

import data

into table "SCHEMA"."TABLE"

from '/tmp/FILENAME_CONTAINING_CSV_DATA'

record delimited by '\n'

field delimited by ','

optionally enclosed by '"'

error log /tmp/log.err

2) Next run the below command referencing the control file name created in step 1

import from '/tmp/CONTROL_FILENAME' with threads 100

this will run the import job with 100 parallel threads.

A sample stored procedure that i have wrote (haven't compiled, guess it will work) can be as :

create procedure uploadNewPrice()

as begin

declare cursor c1 for select flav_id,seasoin_id,price from pricing_upload;

declare flav_id varchar(20);

declare season_id varchar(20);

declare pricing_upload decimal;

open c1;

FETCH IFMENU_CUR INTO flav_id,season_id, pricing_upload;

WHILE ( NOT(IFMENU_CUR::NOTFOUND) ) DO

  update procing_final set price=:pricing_upload where season_id=:season_id and flav_id=:flav_id;

  FETCH IFMENU_CUR INTO flav_id,season_id, pricing_upload;

END WHILE ;

close c1;

end

Regards,

Lalu George

[Former Member · Accepted solution]

Hi Lalu George,

Thank you for your  response.

I have tried above method, but getting an error like

identifier must be declared: IFMENU_CUR:

Please suggest..

Thanks,

Thara.

[Former Member · Accepted solution]

My bad,

create procedure uploadNewPrice()

as begin

declare cursor IFMENU_CUR for select flav_id,seasoin_id,price from pricing_upload;

declare flav_id varchar(20);

declare season_id varchar(20);

declare pricing_upload decimal;

open IFMENU_CUR;

FETCH IFMENU_CUR INTO flav_id,season_id, pricing_upload;

WHILE ( NOT(IFMENU_CUR::NOTFOUND) ) DO

  update pricing_final set price=:pricing_upload where season_id=:season_id and flav_id=:flav_id;

  FETCH IFMENU_CUR INTO flav_id,season_id, pricing_upload;

END WHILE ;

close IFMENU_CUR;

end

Can you try this?

PS: I am sorry, i don't have a Hana box in front of me now

Regards,

Lalu George

[Former Member · Accepted solution]

Hi Lalu George,

Thank you.

It executed successfully, & able to update in the required table using above stored procedure method.

with necessary condition checking..

But one thing is we should call that Stored Procedure manually with call stored_procedure in sql console.

I tried with other method like calling this stored procedure inside trigger.

But i got message like "feature not supported: call procedure is not supported in trigger"

is it any other method such that,

calling that stored procedure  dynamically say around every minute/ hour/day.

such that no manual process will arise for calling that particular stored procedure ..

Thanks,

Thara

[Former Member · Accepted solution]

Hi Thara,

Great!

And unfortunately, triggers do not support procedure calls, nor cursors (in the current versions available) within their body. You can schedule a script written in shell scripting inside the cron scheduler to call the hana procedure. Just a thought.

Regards,

Lalu George

[Former Member · Accepted solution]

Hi Lalu George,

Thank you for your guidelines..

But unfortunately i am using Developer edition of hana studio  version 80 windows based.

i am not having any idea on shell scripting & cron scheduler, & if i am not wrong those commands has to be execute in PPK file no?

Is their any other option that without using Shell Scripting can we able to execute in hana studio itself...

Thanks,

Thara

[Former Member · Accepted solution]

Hi Thara,

I am sorry, i do not have any other ideas as of now, but if you are using Windows environment, then you can schedule the same inside the task scheduler.

1) create a text file and name it as script.bat

2) open the file created in notepad

3) write this line and save it

     <Path to hana client>\hdbsql -jx -i <instance_number> -u <Username> -p <password> "call <SCHEMA_NAME>.<PROC_NAME>()"

These steps would need HANA Client to be installed in your Windows server.

Open task scheduler and configure the task with the executable program as the .bat file that we created in the above steps.

PS: The cron scheduler is an utility in linux , similar to Windows task scheduler.

Regards,

Lalu George