- 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:
- Disable path filter.
Ans: ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘system’) set (‘import_export’, ‘enable_csv_import_path_filter’) = ‘false’ with reconfigure
- 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.
- 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.
- The complete path and file name of the file to import.
- The target table name, with optional schema name, where the imported data will be stored. The format should be <schema_name>.<table_name>
- We have below options as part of importing external data into SAP HANA.
- The number of threads that can be used for concurrent import. The default value is 1 and maximum allowed is 256.