- 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.
- 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.
Below are the different options we can use as part of import options.
- 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.
- 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).
- 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>.
- 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.
- 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
- 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