Skip to main content

Import relationships and keys

ON THIS PAGE

Import relationships and keys

Information about primary and foreign keys shared between related entities can be imported from Excel (XLS) files. Once imported, these relationships display in several places in the UI:

  • In the Catalog module (on the Entity Details page)
  • In field detail screens (General Information and Lineage tabs) in source and discover modules
  • In Field and External Field grids, if User Preferences are selected to make Primary Key and/or Foreign Key visible
  • In prepare, primary and foreign keys in source entities are indicated for assistance with transforms involving joins

These relationships are discovered and populated in metadata table pd_field_related_field by either of the following methods:

  • Fetched from JDBC sources upon entity creation and ingest

  • Inferred by the Relationship Inference Engine

  • Imported from external metadata (described in this section)

XLSX: Structure and format specification

An Excel (.XLSX) file is created and formatted manually by end users.

The file used to import relationships and keys can populate the relationship and primary and foreign key information for type of entities [i.e., PREPARE_TARGET, USER, INTERNAL, EXTERNAL].

Validations are conducted to ensure that parent and child objects exist in the target environment and verify the relations between parent and child objects are valid.

The following relationships between parent and child entities are supported. The child objects hold the foreign key discovered by the various methods described above. [User view is a saved query and target entity created in Explore.]

Supported relationships

Parent Entity Types

Child Entity Types

External

External

Internal

Internal

Prepare_Target

Prepare_Target, User (view)

User (view)

Prepare_Target, User (view)

Excel Example: Note that User can ADD or REMOVE the Foreign Key relationship in the Action column. The columns are ordered as follows: Parent Source, Parent Entity, Parent Field, Child Source, Child Entity, Child Field, Action.

Spreadsheet layout example

Primary and foreign keys are specified at field level and action=ADD

Importing Relationships and Keys: Select IMPORT/EXPORT METADATA. Click on the radio button for Import and Select Relationships and Keys from dropdown. Choose the Excel file from laptop or local directory and select whether the relationship will be imported to external and/ or internal entities:

Import/Export Relationships and Keys

Selected spreadsheet displays in modal

Upon FINISHED Job Status, users will see Primary Keys and Foreign Keys denoted in the UI.

Import/Export Metadata finished

Job status displays as finished once spreadsheet has been imported

Catalog:Related Entities grid, the entity containing primary key will be the PARENT

Catalog Related Entities

Related entities grid in catalog displays primary and foreign key relations

Prepare: Primary Key and Foreign Keys display on the canvas.

Primary and Foreign Keys

Primary and foreign keys are denoted as PK and FK on prepare source targets GUI

External and Internal Field General Information tab

Field General Information Tab

Foreign key is checked in field general information tab

Field and External Field grids, if User Preferences are selected to make Primary Key and/or Foreign Key visible

Field and External Field Grids

Foreign keys equals true in source module field grid if defined in preferences