- Developers
- Tutorials
- Export and Import Data and Schema with SAP HANA Database Explorer
Export and Import Data and Schema with SAP HANA Database Explorer
-
Join the conversation on Facebook
-
Join the conversation on Twitter
-
Subscribe to the YouTube Channel
-
Join the conversation on LinkedIn
-
View our projects on GitHub
-
Share via email
Export and Import Data and Schema with SAP HANA Database Explorer
You will learn
- How to export and import data using the export and import data wizards, SQL commands export into and import from, and the download option in the SQL console results tab
- How to import
ESRI shapefiles
using the import data wizard - How to export and import schema objects using export and import catalog wizards and the SQL commands export and import
- How to use cloud storage providers as a target when exporting or importing
Prerequisites
- An SAP HANA database such as SAP HANA Cloud trial or the SAP HANA, express edition that includes the SAP HANA database explorer
- You have completed the first 3 tutorials in this group.
The following steps will demonstrate a few ways to export and import data such as the contents of tables or views as well how to export and import database schema.
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
Method | Version | Target | Format(s) |
---|---|---|---|
Export from SQL Console | All | local computer | CSV |
Export data wizard | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Parquet |
Export into statement | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Parquet |
Export into statement | SAP HANA on-premise | SAP HANA file system | CSV |
Methods to import into tables
Method | Version | Source | Format(s) | Notes |
---|---|---|---|---|
Import data wizard | All | local computer | CSV | 2 GB max, 1 MB per col |
Import data wizard | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Parquet | |
Import data wizard | SAP HANA Cloud, HANA database | local computer | ESRI shapefiles |
Archive must be a tar.gz |
Import data wizard | SAP HANA on-premise | SAP HANA file system | CSV | Target table can be created |
Import from statement | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Parquet | |
Import from statement | SAP HANA on-premise | SAP HANA file system | CSV | |
Insert into table name select from statement | All | local or remote tables | select statement |
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.
-
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.
-
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;
-
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.
-
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;
-
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 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.
-
At the ARCGIS Hub, search for
Points of Interest in and around Longview, Texas
.Scroll through the results and choose the selection below.
-
Choose to download the data as a
shapefile
. -
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*.*
-
Start the import data wizard, choose Import ESRI Shapefiles and select the
LongViewPOI.tar.gz
file. -
Choose to import the
ESRI shapefile
into the schema HOTEL.Within the downloaded
ESRI shapefile
, there is a file namedPoints_of_Interest.prj
. This file mentions the spatial reference system used by thisESRI 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.
-
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
Method | Version | Target | Format(s) | Limitations |
---|---|---|---|---|
Export catalog wizard | All | Local computer | CSV, Binary, *Parquet | 2 GB max, ** |
Export catalog wizard | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Binary, Parquet | ** |
Export catalog wizard | SAP HANA on-premise | SAP HANA file system | CSV, Binary | ** |
Export statement | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Binary, Parquet | |
Export statement | SAP HANA on-premise | HANA file system | CSV, Binary data | ** |
Methods to import catalog objects
Method | Version | Source | Format(s) | Limitations |
---|---|---|---|---|
Import catalog wizard | All | Local computer | CSV, Binary | 2 GB max, ** |
Import catalog wizard | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Binary, Parquet | ** |
Import catalog wizard | SAP HANA on-premise | SAP HANA file system | CSV, Binary | 2 GB max per object, ** |
Import statement | SAP HANA Cloud, HANA database | S3, Azure, Alibaba OSS | CSV, Binary, Parquet | ** |
Import statement | SAP HANA on-premise | SAP HANA file system | CSV, Binary | ** |
* 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.
-
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.
-
Examine the exported file. Notice that it contains the SQL to recreate the table as well as the data of the table.
-
Enter the SQL statement below to drop the table. It will be added back in the next step.
DROP TABLE HOTEL.MAINTENANCE;
-
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.
-
Log in to the Microsoft Azure Portal.
-
Create a resource group.
-
Create a storage Service
-
Create a blob container.
-
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.
-
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.
-
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;
-
Enter the SQL statement below to drop the table. It will be added back in the next step.
DROP TABLE HOTEL.MAINTENANCE;
-
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
.