Load data to datalake

Eisson — Tue, 09 Feb 2021 12:57:31 GMT

hi community,

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

best regards.

Re: Load data to datalake

markmumy — Tue, 09 Feb 2021 16:09:53 GMT

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:

Create the HANA table
Load data into the HANA table
Create the data lake table
Create the HANA virtual table that points to the data lake table
Verify the virtual table by running a select from it
Move data from HANA to data lake table
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;

Re: Load data to datalake

markmumy — Tue, 09 Feb 2021 16:11:42 GMT

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.

Re: Load data to datalake

Eisson — Tue, 09 Feb 2021 16:51:32 GMT

thank you for your response, I was very helpful.

Question Re: Load data to datalake in Technology Q&A

Load data to datalake

Re: Load data to datalake

Re: Load data to datalake

Re: Load data to datalake