SAP HANA – SQL Data Import Export Statements

Hello Everyone,

In this article, we are going to look at different SQL Data Import Export Statements (DIES) available in SAP HANA along with their syntax and examples.

 

Different statements available in SAP HANA are,

EXPORT
IMPORT
IMPORT FROM
IMPORT SCAN

These statements are used to either import or export the objects from or to SAP HANA system.

Let’s look at details about each statement below.

EXPORT

EXPORT:

  • The EXPORT statement is used to exports tables, views, column views, synonyms, sequences, or procedures in the specified format as BINARY or CSV.

Data stored in “no logging” tables can only be exported in CSV format. For global temporary tables, only the table catalog can be exported. The export of local temporary tables is not supported.

Detailed results of an export are stored in the session-local temporary table #EXPORT_RESULT.

Syntax: EXPORT <export_import_object_name_list> AS <export_format> INTO <path> [WITH <export_option_list>]

We have following options while exporting objects using EXPORT statement in SAP HANA.

Export_import_object_name:

  • We can export all the objects from all schemas or all the objects from single schema or specific objects from single or multiple schemas. We need to use below syntax’s depending on the scenario.
    • ALL – to export all the objects from all the schemas.
    • <schema_name>.* – to export all the objects from one schema.
    • <schema_name>.<object_name> – to export specific objects from one or multiple schemas.

Export_format:

  • We have two different export formats. They are
    • BINARY
    • CSV

1

Path:

When using a distributed system, the FULL_PATH must point to a shared disk. For security reasons, the path may not contain symbolic links and may not point inside the database instance folder, except its ‘backup’ and ‘work’ subfolders. Examples for valid export path (assuming the database instance is located at /usr/sap/HDB/HDB00):

 ‘/tmp’
 ‘/usr/sap/HDB/HDB00/backup’
 ‘/usr/sap/HDB/HDB00/work’

Export_options: 

  • Below are the different options we can use as part of export options.
  • REPLACE:
    • REPLACE removes previously exported data on disk and replaces it with the new export. If the REPLACE option is not specified, an error will be thrown if previously exported data already exists in the specified export directory.
  • CATALOG ONLY:
    • Only the database catalog will be exported.
  • NO DEPENDENCIES:
    • The underlying dependencies of an export object will not be exported.
  • SCRAMBLE BY:
    • Obfuscates CSV format exported data. This option can be used to provide an export of the structure of our database with the string data scrambled so that it cannot easily be interpreted. When the optional <scramble_seed> is not specified, a default scramble seed is used. SAP recommends that the <scramble_seed> should be at least 8 to 10 characters.

Only CSV format character string data will be scrambled by this parameter.

STRIP:

  • Attributes that can be reconstructed at import time will not be stored in the export file. This option can reduce the size of the export file in some cases.

THREADS:

  • The THREADS parameter specifies how many objects will be exported in parallel, the default is 1. Increasing number of threads reduces export time, but can also negatively affect database system performance.
  • Following items should be considered when using this parameter:
    • When exporting a single table THREADS has no effect.
    • When exporting a view or procedure 2 or more threads should be used, up to the number of dependent objects.
    • When exporting a whole schema consider using more than 10 threads. With a maximum being the number of CPU cores in the system.
    • When exporting a whole BW / ERP system database with tens of thousands of tables using the ALL keyword, a large number of threads can be used (up to 256).

We can check the export monitoring status using M_EXPORT_BINARY_STATUS view under SYS schema with the help of connection id.

We can even cancel/abort the import process using the below statement.

  • ALTER SYSTEM CANCEL WORK IN SESSION ‘<CONNECTION_ID>’

Detailed results of the last execution of EXPORT statement are stored in the following session-local temporary table.

Below is the table structure of #EXPORT_RESULT

2

Examples:

  1. Export table ORDERS_DATA from SAP_STUDENT schema with REPLACE AND SCRAMBLE options.

Ans: EXPORT SAP_STUDENT.ORDERS_DATA AS CSV INTO ‘/tmp’ WITH REPLACE SCRAMBLE

  1. Export all the objects from SAP_STUDENT schema WITH REPLACE and THREADS 10.

Ans: EXPORT SAP_STUDENT.”*” AS CSV INTO ‘/tmp’ WITH REPLACE THREADS 10

Check Also

Capture

SAP HANA – SQL Session Management Statements

Hello Everyone, In this article, we are going to look at different SQL Session Management …

Leave a Reply

Your email address will not be published. Required fields are marked *

four × three =