SAP HANA – SQL Data Import Export Statements

IMPORT

IMPORT:

  • The IMPORT statement is used to import catalog objects (tables, views, synonyms, sequences, procedures) that have previously been exported with EXPORT statement into SAP HANA Database.
To import external data into existing tables use IMPORT FROM.

The file format (BINARY | CSV) of the file being imported will be automatically detected.

Syntax: IMPORT <export_import_object_name_list> FROM <path> [WITH <import_option_list>] [AT [LOCATION] <indexserver_host_port>]

We have following options while importing objects using IMPORT statement in SAP HANA.

Export_import_object_name:

  • We can import 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 import all the objects from all the schemas.
    • <schema_name>.* – to import all the objects from one schema.
    • <schema_name>.<object_name> – to import specific objects from one or multiple schemas.

Import_options:

Below are the different options we can use as part of import options.

  • REPLACE:
    • When specified, if a table defined in the import data currently exists in the database it will be dropped and recreated before the data is imported.
    • If the REPLACE option is not specified an error will be thrown if an existing database table is defined in the import data.
  • CATALOG ONLY:
    • Specifies that only the database catalog should be imported.
  • DATA ONLY:
    • Specifies that only the data of the import file should be imported.
    • This option can only be used when the existing table definitions in the database match with those of the data to be imported. Note: For BINARY export data, existing table data will be overwritten with the imported data.
    • For CSV format data, the import data will be appended to existing table data.
  • NO DEPENDENCIES:
    • When specified the underlying dependencies of an import object will not be imported.

THREADS:

  • The THREADS parameter specifies how many objects will be imported in parallel, the default is 1. Increasing number of threads reduces import time, but can also negatively affect database system performance.
  • Following items should be considered when using this parameter:
    • When importing a single table THREADS has no effect.
    • When importing a view or procedure 2 or more threads should be used, up to the number of dependent objects.
    • When importing a whole schema consider using more than 10 threads. With a maximum being the number of CPU cores in the system.
    • When importing 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).

RENAME SCHEMA:

  • It is possible to rename the objects’ schema during import.
  • Multiple schemas can be renamed by specifying multiple <rename_schema_token>. We cannot specify the same schema as both <source_schema> and <target_schema> in same or different <rename_schema_token>.

AT LOCATION:

  • It is possible to specify the index server at which tables are created and imported. If we specify the hostname and port, the tables will be created and imported on there.

FAIL ON INVALID DATA:

  • When specified the IMPORT command will fail unless all the entries have imported successfully.
Progress of IMPORT job can be checked in view M_IMPORT_BINARY_STATUS under SYS schema.

We can even cancel the running IMPORT job using connection ID from the session using the below statement.

ALTER SYSTEM CANCEL WORK IN SESSION ‘<CONNECTION_ID>’

The results of the IMPORT job are stored in session-local temporary table #IMPORT_RESULT and below is its structure.

3

Examples:

  1. Import all the objects from path ‘/tmp/ to SAP HANA using options REPLACE and threads 5.

Ans: IMPORT “sap_student”.”*” AS CSV FROM ‘/tmp’ WITH REPLACE THREADS 10

  1. Import all the objects from path ‘/tmp/ and rename the schema from VENKATESH to SAP_STUDENT

Ans: IMPORT “venkatesh”.”*” AS CSV FROM ‘/tmp’ WITH REPLACE THREADS 10 RENAME SCHEMA VENKATESH TO SAP_STUDENT

IMPORT FROM

IMPORT FROM:

  • The IMPORT FROM statement is used to import external data from a file into an existing table.
  • To import catalog objects (tables, views etc) that have been exported with EXPORT statement, we need to use IMPORT statement.
All in the support UTF-8 except surrogate-pair encoding.
Remember: For security reason, only CSV files located at paths defined in the csv_import_path_filter configuration parameter are allowed to be loaded using the IMPORT FROM SQL statement. This feature can be disabled using the enable_csv_import_path_filter configuration parameter.

Disable or Add paths to Import from Configuration:

Two related configuration parameters are specified in the import_export section of the indexserver (nameserver in case of multi-DB) configuration, so we can turn off this feature or update path filter like follows:

  1. Disable path filter.

Ans: ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘system’) set (‘import_export’, ‘enable_csv_import_path_filter’) = ‘false’ with reconfigure

  1. Enable path filter with specific location.

Ans: ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘system’) set (‘import_export’, ‘csv_import_path_filter’) = ‘/A;/B’ with reconfigure

  • Note that once we add a path ‘/A’ to path filter every sub-path of ‘/A’ will be automatically added as well.
  • We can also import the data from wither csv files or excel files using ‘Import’ option available in ‘Quick View’ screen of SAP HANA Studio which is front end tool for SAP HANA Database.

Please go through the article ‘Data loading into SAP HANA from Flat Files’ for more details.

Syntax: IMPORT FROM [<file_type>] <file_path> [INTO <schema_name>.<table_name>] [WITH <import_from_option_list>]

We have following options while importing external data using IMPORT FROM statement in SAP HANA.

File_type:

  • The type of the file to be imported. We can specify either comma-separated values or control file formats. File type should be either CSV FILE or CONTROL FILE.

File_Path:

  • The complete path and file name of the file to import.

Table_Name:

  • The target table name, with optional schema name, where the imported data will be stored. The format should be <schema_name>.<table_name>

Import_from_options_list:

  • We have below options as part of importing external data into SAP HANA.

THREADS:

  • The number of threads that can be used for concurrent import. The default value is 1 and maximum allowed is 256.

Contd..

 

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 *

sixteen + 19 =