Bulk import framework uses Data group and data descriptor objects. Import groups are DataGroup objects of type "IMPORT". Each DataGroup has one or more DataDescriptor objects associated to it. Relationship is many-to-many via DataGroup.descriptors field that specifies a comma separated list of descriptor names. By design the relationship is not enforced to provide flexibility between DataGroup and DataDescriptor. E.g. it is possible to have DataGroup implementation where descriptors are specified within DataGroup.descriptors as XML.
How DataGroup and DataDescriptor are processed is up to DataDescriptorResolver implementors.
Bulk import uses ImportDataDescriptorResolverImpl implementation that uses DataGroup objects of type "IMPORT" and resolves ImportDescriptor objects within given group which are later used by the ImportService.
Figure 1: Import descriptor resolution
Out of the box version specific configuration for import groups is described in detail Manual ImpEx.
It is possible to add custom groups and data descriptors from the Admin to create import and export configurations required. Later these groups can be used in the import and export wizards in the operations section of Admin.
Note that data federation is fully integrated into ImpEx API and therefore all imported and exported records will be validated against the authorisations given to the user that requested import/export operation.
Anatomy to CSV import descriptor
Basic import descriptor defines the following sections:
- Optional Mode - MERGE (default) or DELETE
- Optional Context
- Entity type - to give hints to import service as to what type of import entity we are working with and what data federation rules to apply.
- Import file descriptor - specifies how CSV data is to be processed (i.e. encoding, delimiters, text qualifier)
- Select/Insert/Delete statements - used by import service to manipulate CSV row tuples and look up data in database
- Import columns - describes what columns in CSV file match to what properties in target entity
Query section allows to look up existing objects in the system to be updated with data from CSV tuple. In MERGE mode the data is either inserted (if look up query does not return existing object) or updated. For deletions "DELETE" mode must be set in the descriptor.
Query section can contain following queries:
|select-sql||HSQL||Look up existing objects to be updated/deleted|
|insert-sql||SQL||Native insert to speed up import||Bypasses hibernate processes and thus not eligible for cache evictions or audit tracing|
|delete-sql||HSQL||Native delete to speed up import||Bypasses hibernate processes and thus not eligible for cache evictions or audit tracing|
"insert-sql" and "delete-sql" are optional. When these configurations are omitted standard Hibernate save() and delete() methods are invoked on objects retrieved by the "select-sql".
All queries support templating mechanism where values from CSV tuples can be used as part of the query. All that is necessary is to specify placeholders in "select-sql", "insert-sql" or "delete-sql" which is represented by column name enclosed by curly brackets.
In the example above there are two placeholders "name" and "carrier" that will be populated using values in columns 0 and 10 respectively. Note that even though carrier is FK_FIELD the template value is raw value from CSV column and not the CarrierEntity object.
Each column under specified index is bound to property on the target entity specified by "name". Binding type is defined by the "field-type" configuration, which will set strategy for processing data in a given column.
|FIELD||Single Value field (Also used as PK if has lookup query to check for update entities).|
|FK_FIELD||Foreign key field. (Uses look up queries to look up parent objects).|
|SLAVE_INLINE_FIELD||Defines sub tuple which uses various columns to populate its object. This kind of field uses sub descriptor to define sub tuple columns.|
|SLAVE_TUPLE_FIELD||Defines sub tuple which is encoded fully inside current field and has no access to other fields. This kind of field uses sub descriptor to define sub tuple columns.|
All CSV data is assumed to be text and to give import descriptor hint for other data types "data-type" property can be specified.
|DATETIME||Date value. Default date format is: "yyyy-MM-dd hh:mm:ss". For example: 2014-01-24 16:54:00 is 24th January 2014 16:54|
Note that all decimals must be of type java.math.BigDecimal as rounding errors in Double and Float may alter original value. Hence those low precision java types are not supported by the system.
The values themselves also can be preprocessed in various ways.
The simplest one is to enforce character limit on the column thus avoiding database data size violation errors. This is accomplished by regular expression.
For example to select only up to 255 characters from CSV column a regular expression could be used:
Group specifies which part to use. So it is possible to create any regular expression and select the required group from the column data to use as import value.
To set a constant value "value-constant" tag could be used that will treat value inside the tag as if there was a column in CSV with this value.
Master objects in slave columns
"use-master-object" tag that accepts boolean flag allows to specify that value for given property is current master object. Master objects are available for sub import descriptors from SLAVE_INLINE_FIELD and SLAVE_TUPLE_FIELD.
For display values which can be translated in different language additional "language" hint can be supplied that contains two letter language code.
Columns 1, 2 and 3 are all mapped to displayName property on the target entity. The "language" tag allows to diversify which column represents which language value.
Below is an example of skuprices.csv format from the demo import (that corresponds to the above import descriptor). The header line is purely for human reference an is completely ignored by the import process. Textual data is enclosed by quotes ( " ) thus giving opportunity to specify special characters such as new line. The separator used is semicolon ( ; )
Anatomy of image import descriptor
Image descriptors are somewhat specialised and contain far less configurations.
The entity type for image import descriptors is always "IMAGE". Import file descriptor is fairly standard and can be reused.
"select-sql" defined the type of the image data object in this instance "/imgvault/brand/" which is Brand entity.
|select-sql||Image entity type|
|/imgvault/category/||master catalog categories and content|
|/imgvault/product/||products and SKU|
The column 0 definition is purely for readability purposes and is not used by the image service. All data objects' codes are inferred from the image file name as explained in the.
Anatomy of XML import descriptor 3.6.0+
XML import and export build on the same concepts as any other import and export. Descriptor definition specifies the import descriptor resolver that will parse the configurations. Import director service will invoke the XML import or export service to perform the action.
Because XML import and export have a well defined schema the only option available is specifying appropriate handler to use. For exports all handler have "pretty" format by suffixing the name of the handler with "_PRETTY".
Here is the full list of XML descriptor available out of the box:
The following handlers are supported OOTB:
|Association||PRODUCT||PRODUCT||Created automatically during product import|
|Brand||PRODUCT,BRAND||BRAND||Created automatically during product import|
|Category||CATEGORY,PRODUCT,PRODUCT_CATEGORIES||CATEGORY||Created automatically during product import|
|CustomerOrder||CUSTOMERORDER||CUSTOMERORDER||Create ONLY for now|
|CustomerOrderDelivery||CUSTOMERORDER||CUSTOMERORDER||Create ONLY for now|
|CustomerOrderDeliveryDet||CUSTOMERORDER||CUSTOMERORDER||Create ONLY for now|
|CustomerOrderDet||CUSTOMERORDER||CUSTOMERORDER||Create ONLY for now|
|ProductSku||PRODUCT,SKU||PRODUCT,SKU||Included in product but can run standalone as SKU|
|PromotionCouponUsage||CUSTOMERORDER||CUSTOMERORDER||Create ONLY for now|