Bulk export to CSV files a set of tables in an HDI Container in HANA Cloud in SAP BTP

[iperez-sofos]

Greetings.

I have the following situation:

I need to export a set of tables that I have in SAP HANA Cloud in SAP BTP (an HDI Container instance) to CSV files.

There are many tables and I would not like to have to export them one by one, with the Export functionality of DB Explorer.

It's possible? How can I do it?

At the moment the posts that I have reviewed have not been of much help to me, either because they are not current, or they do not fit the scenario that I described before.

Thank you in advance for any help, suggestions and/or links of interest.

P.S:

Although in principle I will not need to import the CSV files back into tables in a HANA Cloud DB (I need the files for local work), it would also be useful to know about a mass import option.

[apoorva_nagaraju]

Hello,

One way to export multiple HDI tables into CSV file is-

Step1: Grant Access to an SAP HDI Container's Schema

In an SQL console, connect to the database with an administrator of the target HDI container

DROP TABLE "#PRIVILEGES0";

create local temporary column table "#PRIVILEGES0" like "_SYS_DI"."TT_SCHEMA_PRIVILEGES";

insert into "#PRIVILEGES0" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('SELECT', '', '<DB_USER>');

insert into "#PRIVILEGES0" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('INSERT', '', '<DB_USER>');

insert into "#PRIVILEGES0" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('UPDATE', '', '<DB_USER>');

insert into "#PRIVILEGES0" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('DELETE', '', '<DB_USER>');

insert into "#PRIVILEGES0" ("PRIVILEGE_NAME", "PRINCIPAL_SCHEMA_NAME", "PRINCIPAL_NAME") values ('CREATE ANY', '', '<DB_USER>');

call "<Container_Schema>#DI"."GRANT_CONTAINER_SCHEMA_PRIVILEGES"("#PRIVILEGES0", "_SYS_DI"."T_NO_PARAMETERS", ?, ?, ?);

Step 2: Connect to SQL console of the HC instance

export into CSV file 'azure://<storage_account>:<SAS-token>@<container>/<object_id>' from (SELECT * FROM <HDI_TABLE1>,<HDI_TABLE2>,<HDI_TABLE3>) WITH REPLACE COLUMN LIST IN FIRST ROW;

This will export content of all the tables into single csv file.

Hope this helps!

[iperez-sofos]

Thank you very much for your response, Apoorva Nagaraju.

I don't really want to export the data from all the tables in a single, individual CSV file; rather each individual table be exported to a CSV file with the name of that table. I guess I didn't express it clearly enough.

However, from your answer, I understand that the approach to bulk download all the tables in my HDI schema, I must do it through SQL statements in the console (that is, there is no alternative through the DB Explorer UI... which was my first idea) am I right?

[apoorva_nagaraju]

Hello Isaac,

After granting access to an SAP HDI Container's Schema, you can export the catalog objects of the HDI container schema using Export Catalog Objects option in DB Explorer.

This will export all the tables under that schema and have separate CSV file for each table having data.

Same can also be done using SQL-

export "<HDI Schema>"."*" into 'azure://<storage_account>:<SAS-token>@<container>/<object_id>' with threads 8 replace;

import all from 'azure://<storage_account>:<SAS-token>@<container>/<object_id>' with rename schema <HDI Schema> to <New Schema> replace;

Note: As there is no privilege assigned for the user to DROP tables in HDI schema, rename schema is used during import.

Regards,

Apoorva