Export and Import Data and Schema with SAP HANA Database Explorer

The following tables list the different options available in the SAP HANA database explorer to export and import data from a single table or view. Step 3 will cover import and export of catalog objects which can include the SQL to recreate the object as well as additional objects such as functions and procedures.

Methods to export tables or views

Methods to import into tables

Export and import using cloud storage from Amazon, Microsoft Azure and Alibaba Cloud is covered in the final step of this tutorial.

The following steps will attempt to demonstrate some of these options.

1. Enter the SQL statement below.

   SELECT * FROM HOTEL.MAINTENANCE;
   

   Left-click on the download toolbar item.

   

   Choose Download.

   

   Note, there is a setting that controls the number of results displayed which may need to be adjusted for tables with larger results.

   

2. Enter the SQL statement below to delete the rows in the table. They will be added back in the next step.

   DELETE FROM HOTEL.MAINTENANCE;
   

3. Right-click on the maintenance table and choose Import Data.

   

   Browse to the previously downloaded CSV file and complete the wizard.

   

   Note that header row in the data.csv file is used to set the initial values of the source column to database column mappings.

   

   After completing the wizard, the contents of the maintenance table should now be the same as it was before the previously executed delete statement.

1. With SAP HANA Cloud, an export data wizard is available.

   

   It can be used to export data to cloud storage providers such as Amazon S3, Microsoft Azure, and Alibaba Cloud OSS. Additionally, the SAP BTP offers an object store. A detailed example of the setup and usage of a cloud storage provider will be provided in Step 5 of this tutorial.

   

   The wizard makes use of the export into statement. An example is shown below:

   EXPORT INTO PARQUET FILE 'azure://danstestsa:sp=racwdl&st=2021-01-09T13:00:46Z&se=2021-01-10T13:00:46Z&sv=2019-12-12&sr=c&sig=TP%2BVYhcvSPDc4DZxcls6vN%2BCLHDNagedbei2IuEZsWU%3D@myblobcontainer/maintenance.parquet' FROM HOTEL.MAINTENANCE;
   

2. The import data wizard has a corresponding option to import from cloud storage providers.

   

   The wizard makes use of the import from statement. An example is shown below:

   DELETE FROM HOTEL.MAINTENANCE;
   IMPORT FROM PARQUET FILE 'azure://danstestsa:sp=racwdl&st=2021-01-09T13:00:46Z&se=2021-01-10T13:00:46Z&sv=2019-12-12&sr=c&sig=TP%2BVYhcvSPDc4DZxcls6vN%2BCLHDNagedbei2IuEZsWU%3D@myblobcontainer/maintenance.parquet' INTO HOTEL.MAINTENANCE WITH ERROR LOG 'error_log.txt' FAIL ON INVALID DATA;
   

3. With SAP HANA, express edition, the following statements can be executed to export and import from a directory on the SAP HANA file system assuming that the directory exists and the user hxeadm has permission to access it.

   EXPORT INTO '/tmp/export/maintenance.csv' FROM HOTEL.MAINTENANCE WITH COLUMN LIST IN FIRST ROW;
   DELETE FROM HOTEL.MAINTENANCE;
   ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') set ('import_export', 'csv_import_path_filter') = '/tmp/export' WITH RECONFIGURE;
   IMPORT FROM CSV FILE '/tmp/export/maintenance.csv' INTO HOTEL.MAINTENANCE WITH COLUMN LIST IN FIRST ROW ERROR LOG 'error_log.txt' FAIL ON INVALID DATA;
   

The following tables list the different options available in the SAP HANA database explorer to export and import catalog objects.

Methods to export catalog objects

Methods to import catalog objects

* SAP HANA Cloud, HANA database only

** Max file size in archive is 8 GB (SAP Note 2907201).

Export and import using cloud storage from Amazon, Microsoft Azure and Alibaba Cloud is covered in the final step of this tutorial.

Similar to the first section, the maintenance table will be exported and re-imported. The export statement and the associated export catalog wizard have additional options, including the ability to include other schema objects such as functions and procedures as well as the option to include the SQL statements to recreate the objects.

1. Right-click on the maintenance table and choose Export Catalog Objects.

   

   Choose Local Computer for the export location, provide a name for the Local Archive, select an export format and press Export.

   

   Examine the available export format options.

   

   Note that Binary Raw is the binary format for SAP HANA Cloud and Binary Data is the format option for SAP HANA as a Service and SAP HANA on-premise.

2. Examine the exported file. Notice that it contains the SQL to recreate the table as well as the data of the table.

   

3. Enter the SQL statement below to drop the table. It will be added back in the next step.

   DROP TABLE HOTEL.MAINTENANCE;
   

4. Right-click on the tables folder and choose Import Catalog.

   

   Browse to the previously downloaded .tar.gz file and complete the wizard.

   

   The contents of the maintenance table should now be the same as it was before the previously executed drop statement.

SAP HANA Cloud enables users to access cloud storage for database imports and exports. Users may choose to minimize the movement of their data by selecting the same cloud provider that their SAP HANA Cloud instance is running.

The following steps walk through the process of using Microsoft Azure storage service as a target for an export catalog operation. Similar steps can be used for other storage providers and the export data wizard.

1. Log in to the Microsoft Azure Portal.

2. Create a resource group.

   

3. Create a storage Service

   

4. Create a blob container.

   

5. Generate an API key.

   

   Specify that the permissions and the expiry time.

   

   Copy the generated query string and paste it into a text editor. This will be used in step 7.

   

6. In the SAP HANA database explorer, add the certificate used by Microsoft to the HANA Cloud PSE.

   select * FROM PSES;
   create pse HTTPS;
   select * from CERTIFICATES;
   create certificate from '-----BEGIN CERTIFICATE-----MIIDdzCCAl+gAwIBAgIEAgAAuTANBgkqhkiG9w0BAQUFADBaMQswCQYDVQQGEwJJ
   RTESMBAGA1UEChMJQmFsdGltb3JlMRMwEQYDVQQLEwpDeWJlclRydXN0MSIwIAYD
   VQQDExlCYWx0aW1vcmUgQ3liZXJUcnVzdCBSb290MB4XDTAwMDUxMjE4NDYwMFoX
   DTI1MDUxMjIzNTkwMFowWjELMAkGA1UEBhMCSUUxEjAQBgNVBAoTCUJhbHRpbW9y
   ZTETMBEGA1UECxMKQ3liZXJUcnVzdDEiMCAGA1UEAxMZQmFsdGltb3JlIEN5YmVy
   VHJ1c3QgUm9vdDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKMEuyKr
   mD1X6CZymrV51Cni4eiVgLGw41uOKymaZN+hXe2wCQVt2yguzmKiYv60iNoS6zjr
   IZ3AQSsBUnuId9Mcj8e6uYi1agnnc+gRQKfRzMpijS3ljwumUNKoUMMo6vWrJYeK
   mpYcqWe4PwzV9/lSEy/CG9VwcPCPwBLKBsua4dnKM3p31vjsufFoREJIE9LAwqSu
   XmD+tqYF/LTdB1kC1FkYmGP1pWPgkAx9XbIGevOF6uvUA65ehD5f/xXtabz5OTZy
   dc93Uk3zyZAsuT3lySNTPx8kmCFcB5kpvcY67Oduhjprl3RjM71oGDHweI12v/ye
   jl0qhqdNkNwnGjkCAwEAAaNFMEMwHQYDVR0OBBYEFOWdWTCCR1jMrPoIVDaGezq1
   BE3wMBIGA1UdEwEB/wQIMAYBAf8CAQMwDgYDVR0PAQH/BAQDAgEGMA0GCSqGSIb3
   DQEBBQUAA4IBAQCFDF2O5G9RaEIFoN27TyclhAO992T9Ldcw46QQF+vaKSm2eT92
   9hkTI7gQCvlYpNRhcL0EYWoSihfVCr3FvDB81ukMJY2GQE/szKN+OMY3EU/t3Wgx
   jkzSswF07r51XgdIGn9w/xZchMB5hbgF/X++ZRGjD8ACtPhSNzkE1akxehi/oCr0
   Epn3o0WC4zxe9Z2etciefC7IpJ5OCBRLbf1wbWsaY71k5h+3zvDyny67G7fyUIhz
   ksLi4xaNmjICq44Y3ekQEe5+NauQrz4wlHrQMz2nZQ/1/I6eYs9HRCwBXbsdtTLS
   R9I4LtD+gdwyah617jzV/OeBHRnDJELqYzmp-----END CERTIFICATE-----' COMMENT 'Azure';
   select CERTIFICATE_ID from CERTIFICATES where COMMENT = 'Azure';
   alter pse HTTPS add certificate <SELECTED_CERTIFICATE_ID>;
   set pse HTTPS purpose REMOTE SOURCE;
   

   Additional details can be found at Certificate Management in SAP HANA Cloud.

   The certificate string above is from the root certificate used for the Azure Portal.

   

7. Start the export catalog wizard and export the maintenance table to the storage service.

   The Azure Path is of the format:

   <Storage Account Name>:<generated shared access string minus the leading?><@Container Name>\/

   An example string is shown below:

   danstestsa:sp=racwdl&st=2021-01-09T13:00:46Z&se=2021-01-10T13:00:46Z&sv=2019-12-12&sr=c&sig=TP%2BVYhcvSPDc4DZxcls6vN%2BCLHDNagedbei2IuEZsWU%3D@myblobcontainer/maintenance

   

   Pressing the Compose button shows the parsed Azure path.

   

   After the Export button is pressed, the results can be seen in the Azure Portal.

   

   The equivalent SQL statement is shown below:

   EXPORT HOTEL.MAINTENANCE AS PARQUET INTO 'azure://danstestsa:sp=racwdl&st=2021-01-09T13:00:46Z&se=2021-01-10T13:00:46Z&sv=2019-12-12&sr=c&sig=TP%2BVYhcvSPDc4DZxcls6vN%2BCLHDNagedbei2IuEZsWU%3D@myblobcontainer/maintenance' WITH REPLACE;
   

8. Enter the SQL statement below to drop the table. It will be added back in the next step.

   DROP TABLE HOTEL.MAINTENANCE;
   

9. Import the table using the import catalog objects wizard.

   

   The contents of the maintenance table should now be the same as it was before the previously executed drop statement.

   The equivalent SQL statement is shown below:

   IMPORT HOTEL.MAINTENANCE FROM 'azure://danstestsa:sp=racwdl&st=2021-01-09T13:00:46Z&se=2021-01-10T13:00:46Z&sv=2019-12-12&sr=c&sig=TP%2BVYhcvSPDc4DZxcls6vN%2BCLHDNagedbei2IuEZsWU%3D@myblobcontainer/maintenance' WITH REPLACE;
   

   For additional details see the topic Importing and Exporting Data in the SAP HANA Cloud Administration Guide.

Congratulations! You have imported and exported data and catalog objects as well as imported an ESRI shapefile.