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 Cloud Platform 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;
   

3. With SAP HANA, express edition, the following commands can be executed to export and import from a directory on the SAP HANA filesystem 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';
   

This step will import an ESRI shapefile containing points of interest near the Bella Ciente hotel in the city of Longview Texas. A search can then be performed to return the 3 closest golf courses to the hotel.

At this time, the import option in the import wizard for ESRI shapefiles is only available in the SAP HANA Cloud, HANA database.

1. At the ARCGIS Hub, search for Points of Interest in and around Longview, Texas.

   

   Scroll through the results and choose the selection below.

   

2. Choose to download the data as a shapefile.

   

3. Unzip the downloaded file to a temporary directory and then convert the extracted contents to a tar.gz.

   tar -cvzf LongViewPOI.tar.gz Points_of*.*
   

4. Start the import data wizard, choose Import ESRI Shapefiles and select the LongViewPOI.tar.gz file.

   

5. Choose to import the ESRI shapefile into the schema HOTEL.

   Within the downloaded ESRI shapefile, there is a file named Points_of_Interest.prj. This file mentions the spatial reference system used by this ESRI shapefile. Specify WGS 84 as the spatial reference system.

   

   Additional details on spatial reference systems can be found at SAP HANA Spatial Reference for SAP HANA Cloud.

6. The following statements rename the imported table and then show the 3 closest golf courses to the Bella Cliente hotel.

   RENAME TABLE "HOTEL"."Points_of_Interest" TO HOTEL.POI_LONGVIEW;
   SELECT TOP 3 NAME, ADDRESS, LINKED_URL, /* Bella Ciente location */ NEW ST_Point('POINT (-94.71868866754436 32.504451132677)', 4326).ST_Distance(NEW ST_Point(SHAPE.ST_AsWKT(), 4326), 'kilometer') * 0.621371 as dist from HOTEL.POI_LONGVIEW WHERE FCODE = 'GOLF COURSE' order by dist;
   

   

   For additional details, see ST_Point Type and ST_Distance Method.

   The latitude and longitude used in the query can be obtained for a given address via context menu for a marker in Google Maps.

   

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.

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 Container Name>:<generated shared access string minus the leading?><@Storage Account>/<File 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 command using the SQL statement export 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 command using SQL the statement import 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;
   

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