Import entities
Entities can be imported into Designer with a CSV or a JSON format. This article focuses on the CSV format which is the recommended and common way of importing entities.
See also:
Tutorials
See the below video for a quick tutorial on how to import entities.
https://youtu.be/gbeDoEZrz8YUsage
Import Entities menu is located in the Designer front page next to the package list:
Prepare an import file in the given format to add new entities or export an existing entity/package to make changes to existing entities. Import the file in the Import Entities menu.
Generate an entity import file from a source system)
Streamline the entity import process by designing a query into the information schema of a source system database that produces a compatible entity import file. An example repository for these queries can be found from Github.
CSV format
Basic requirements
Header row is mandatory
No extra whitespace between columns
Allowed column delimiters:
comma: ","
semicolon: ";"
pipe: "|"
Allowed encoding:
UTF-8 (with or without BOM)
Allowed end of line characters
Windows: CR LF
Unix: LF
Columns
Column | Description | Required |
|---|---|---|
package_name | Package name where entity is placed | Yes* |
entity_dv_source | Source system name | Yes* |
datatype_dbms_product | Database management system product from which the import is generated. Attribute datatype definitions are DBMS specific. Default supported values: MS_SQL, MS_SQL_DW, MYSQL, ORACLE, POSTGRESQL, AMAZON REDSHIFT, SNOWFLAKE, AGILE_DATA_ENGINE. | Yes |
entity_uuid | Entity unique identifier. Useful when making changes to existing entities. Changing the name of an existing entity requires the use of entity_uuid. | No |
entity_schema | Database schema name | Yes |
entity_logical_name | Entity logical name. Logical name does not contain prefixes and/or suffixes (e.g. "H_" for HUBS, "F_" for FACTS...) configured in CONFIG_ENTITY_DEFAULTS. | Yes |
entity_type | Default supported values: SOURCE, STAGE, HUB, SAT, SAT_C, S_SAT, S_SAT_C, LINK, PIT, BRIDGE, REF, DIM, FACT, FLAT, LOGICAL, GENERIC. | Yes |
entity_physical_type | Supported values: TABLE, VIEW, MATERIALIZED_VIEW, METADATA_ONLY | No |
entity_description | Entity description/comment | No |
entity_is_soft_deleted | Supported values: Y, N or 1, 0 | No |
attribute_uuid | Attribute unique identifier. Useful when making changes to existing attributes. Changing the name of an existing attribute requires the use of attribute_uuid. | No |
attribute_name | Attribute name | Yes |
attribute_datatype | Datatype with selected datatype_dbms_product. See list of supported data types in Import Entities menu. Edit datatypes with CONFIG_DATATYPES. | Yes |
attribute_length | Defines attribute length/size for specific data types such as strings. Must be left empty for other data types. | Yes** |
attribute_precision | Defines the number of digits in a number for fixed-point numeric data types. For example, the number 1234.56 has a precision of 6. Must be left empty for other data types. | Yes** |
attribute_scale | Defines the number of decimals in a number for fixed-point numeric data types. For example, the number 1234.56 has a scale of 2. Must be left empty for other data types. | Yes** |
attribute_nullable | Defines if the attribute accepts null values. Supported values: Y, N or 1, 0 | Yes |
attribute_position | Defines the ordinal position of the attribute in the entity. Note that depending on the target database management system, changing the attribute order in an existing entity might not have an effect unless FORCE RECREATION is used. | Yes |
technical_attribute_type | System attribute type that can have transformations and load automation patterns related to it. See TECHNICAL_ATTRIBUTE_TYPE. | No |
attribute_description | Attribute description/comment | No |
attribute_compression_type | AMAZON REDSHIFT ONLY: Encoding type for attribute, see Amazon Redshift documentation for further information. | No |
attribute_is_soft_deleted | Supported values: Y, N or 1, 0 | No |
attribute_default_value | Defines a default value or expression for an attribute. | No |
attribute_collation_value | Collation definition, see COLLATION VALUE. | No |
attribute_masking_policy | Attribute MASKING POLICY | No |
primary_key_name | Primary key name, if the attribute is part of a primary key. | No |
primary_key_position | Ordinal position of the attribute in a primary key, if the attribute is part of a primary key. | No |
foreign_key_name | Foreign key name, if the attribute is part of a foreign key. | No |
foreign_key_parent_entity_schema | Foreign key's referenced entity's schema name, if the attribute is part of a foreign key. | No |
foreign_key_parent_entity_name | Foreign key's referenced entity's name, if the attribute is part of a foreign key. | No |
foreign_key_parent_entity_attribute_name | Foreign key's referenced entity's attribute name, if the attribute is part of a foreign key. | No |
foreign_key_position | Ordinal position of the attribute in a foreign key, if the attribute is part of a foreign key. | No |
* At least one is required.
** Depends on the datatype.
Notes
Source system is defined with the following attributes:
dv_source or entity_dv_source (CSV)
dvSource (JSON)
However, the import will fail if there are:
Any entities using source systems that do not exist
Any non-deleted entities (e.g. a restore attempt) using source systems that are deleted
Examples
Entity import queries
Example entity import queries for different databases can be found from Github.
Entity import scripts
Example entity import scripts for detecting schema from CSV files can be found from Github.
Import file example
STG_TRIPDATA_GREEN staging table and attributes: