You can import data from comma-separated values (CSV) into the SAP HANA tables using the SAP HANA Extended Application Services (SAP HANA XS) table-import feature.
In SAP HANA XS, you create a table-import scenario by setting up an import-model file, a import-data file, and one or more comma-separated value (CSV) files containing the content you want to import into the specified SAP HANA table. The import-model file links the import operation to one or more target tables. The table definition (for example, in the form of a .hdbtable file) can either be created separately or be part of the table-import scenario itself.
To use the SAP HANA XS table-import feature to import data into an SAP HANA table, you need to understand the following table-import concepts:
● Table-Import Model
You define the table-import model in a configuration file that specifies the data fields to import and import target tables of each data field.
● Table-Import Data
You define the table-import data in a configuration file that specifies how to link the CSV data files to the respective data field and, in turn, the target tables via the table-import model.
● Table-Import Extension
You define the table-import extension in a configuration file that modifies the relationship to an existing table import data file.
CSV Data File Constraints
The following constraints apply to the table-import feature in SAP HANA XS: ● The number of table columns must match the number of CSV columns.
● There must not be any incompatibilities between the data types of the table columns and the data types of the CSV columns.
● Overlapping data in data files is not supported.
● The target table of the import must not be modified (or appended to) outside of the data-import operation. If the table is used for storage of application data, this data may be lost during any operation to re-import or update the data.
6.10.1 Table-Import Model
The table-import model is a configuration file that you use to specify the parameters associated with the list of comma-separated-value (CSV) files that you want to import. The table-import model also specifies, for each parameter, the target table into which the data in the linked-data files is imported.
You can declare a parameter as optional. If a parameter is optional, it is not necessary to assign a value to this parameter in the implementing table import data object. To declare a variable as optional in the table-import model, add the keyword optional before the variable, as illustrated in the example below.
optional listCsvFile csvList;
If you want to add comments to your table-import model, use two back slashes (//) as illustrated in the example below.
listCsvFile fileList; //this is the csv file list
Example
Example Table-Import Model File listCsvFile csvFiles;
import csvFiles "TISCHEMA" "TiPackage::TiTable";
6.10.2 Table-Import Data
The table-import data configuration file enables you to specify which table-import model is to be implemented, for example, TiModel.hdbtim and how to handle the data to be imported from the comma-separated list to the target database table. The file-list parameters created in the table import-model file are assigned to lists of data- file links, for example, comma-separated values (.csv) files. In the example illustrated below, the .csv is called TiCsv.csv.
If you want to add comments to your table-import data-definition file, use two back slashes (//) as illustrated in the following example:
implements TiPackage:TiModel.hdbtim; //This is a the import model For each CSV file it is possible to define the following options in the data-definition (.hdbtid) file: ● Final
Example
Example Table-Import Data File
implements TiPackage:TiModel.hdbtim; //This is a comment csvFiles = ["TiPackage:TiCsv.csv"];
final
Use the final keyword with the csvFiles or csvFilesExt to you can prevent modifications of values assigned to file lists, for example, in a table-import extension. To declare an option as final in the table-import data file, add the keyword final before the option, as illustrated in the example below.
final csvFiles = ["acme.com.mypackage:data.csv"];
6.10.3 Table-Import Extensions
The table-import feature enables you to modify the initial settings for variables defined in table-import configuration files by specifying new settings for defined variables in separate extension files.
If both a table-import model and a table-import data file exist, then you can modify initial variable assignments made in one table-import data file in a subsequent table-import data files (so called extensions) - as long as the variables are not declared explicitly as final in a previous file. The modification takes place only on the data values of variables, for example, in a table-import-extension data file, where values can be augmented or be replaced. The result is a chain of table-import data artifacts. The end result of a successful table import operation is based on the resulting definition of the file-list parameter as a result of all data files in the chain.
Note
You can only create a linear chain of extensions. It is not permitted to create two table import data files modifying the same base table import data files.
The following example illustrates a simple table-import model named timodel.hdbtim in the package TiPackage.
csvFileList csvList;
import csvList "TISCHEMA" "TiPackage::TiTable";
The following example illustrates the contents of a simple table-import data file named tidata.hdbtid, which implements the table-import model defined in timodel.hdbtim illustrated above.
implements TiPackage:timodel.hdbtim; csvList = ["TiPackage:mydata.csv"];
The following example illustrates how to extend the definition in the table-import data file tidata.hdbtid illustrated above.
modifies sap.myPackage:tidata.hdbtid;
csvList = csvList + ["TiPackage:myotherdata.csv"];
After activation of the table-import files, the data from both CSV files (TiPackage:mydata.csv, and TiPackage:myotherdata.csv) is imported into the target table.
6.10.4 Table-Import Configuration-File Syntax
When you define the details of the table-import operation, you use the following file-list types to enable the processing of certain data formats:
● csvFileList
Used to specify list of comma-separated values (CSV) file links. The linked CSV files are imported into the target table specified in the table import model. The import process parses and validates the CSV file format.
Example
Table Import Model Example
The following example shows how to use the option csvFileList in the table-import model definition: listCsvFile csvFiles;
import csvFiles "TISCHEMA" "TiPackage::TiTable";
Example
Table Import Data Example
The following example shows how to use the option csvFileList in the table-import data definition: implements TiPackage:TiModel.hdbtim;
csvFiles = ["TiPackage:TiCsv.csv"];