Load data to datalake

[#1]

hi community,

how do I upload data from an excel or csv file to datalake in sap hana cloud directly?

best regards.

[markmumy · Accepted solution]

Assuming that this is a continuation of the other question/post that you have?

If the data was already loaded into a Hana Cloud table and you want to move it into the data lake, the process is fairly straight forward.

Here are the basic steps, hope this helps:

1. Create the HANA table
2. Load data into the HANA table
3. Create the data lake table
4. Create the HANA virtual table that points to the data lake table
5. Verify the virtual table by running a select from it
6. Move data from HANA to data lake table
7. Delete data from the HANA table, if you wish

Here is some sample code for everything. There are ways to do certain steps with database explorer, but I am old school and prefer reproducible SQL.

-- create HANA table
CREATE TABLE LOAD_TEST (
        "A0" INT, "A1" INT, "A2" INT, "A3" INT, "A4" INT, "A5" INT, "A6" INT, "A7" INT, "A8" INT, "A9" INT
);


-- load in some dummy data
insert into LOAD_TEST values ( 0,1,2,3,4,5,6,7,8,9);
insert into LOAD_TEST select * from LOAD_TEST;
insert into LOAD_TEST select * from LOAD_TEST;


-- create the HDL table
call "SYSRDL#CG".REMOTE_EXECUTE('
drop table if exists LOAD_TEST;
CREATE TABLE LOAD_TEST (
        "A0" INT, "A1" INT, "A2" INT, "A3" INT, "A4" INT, "A5" INT, "A6" INT, "A7" INT, "A8" INT, "A9" INT );
');

-- creates the HANA SDA table for the table created above
-- HDL uses SYSRDL#CG for every remote database
-- HDL uses SYSRDL#CG_SOURCE for every remote server
-- "<NULL>" is just a dummy placeholder as IQ/HDL ignore this parameter anyway

drop table HDL_LOAD_TEST; -- will error if it doesn't exist

CREATE VIRTUAL TABLE HDL_LOAD_TEST AT "SYSRDL#CG_SOURCE"."<NULL>"."SYSRDL#CG"."LOAD_TEST";

-- get counts and make sure virtual table works
select 'HANA cnt: ' || count(*) from LOAD_TEST;
select 'HDL cnt: ' || count(*) from HDL_LOAD_TEST;

-- copy data
insert into HDL_LOAD_TEST select * from LOAD_TEST;

-- get final counts to see that they are the same
select 'HANA cnt: ' || count(*) from LOAD_TEST;
select 'HDL cnt: ' || count(*) from HDL_LOAD_TEST;

[markmumy · Accepted solution]

I should add that at the end of Q1, the next release of the Data Lake will have a feature that allows you to directly connect to it. When you can do this, you will then have the ability to upload a file directly from your client into the Data Lake, without having to load it into HANA first.

[Eisson · Accepted solution]

thank you for your response, I was very helpful.