How to associate an Entity with a pipeline

Data Factory pipelines can be defined in a JSON format. Using that JSON, they can be created programmatically using the Data Factory API.

There is a key component in Sidra, the Data Factory Manager, that composes the pipelines JSON from the templates and parameters stored in the Sidra Core metadata database and use it to create the pipeline in Data Factory.

In order to perform the parameter substitution in the templates, Data Factory Manager needs to know which pipeline to be launched for which Entity. This relationship is stored in the metadata database in the EntityPipeline table.

There are two ways to create a new Entity-pipeline relationship:

  • Add a new Entity-pipeline relationship by creating an SQL script to insert the relationship in the database.
  • Add a new Entity-pipeline relationshop by using the Sidra API endpoint to associate Entities and pipelines.

EntityPipeline general information

This is the information about an Entity-pipeline relationship that must be included when it is added to the metadata database:

Column Description
IdPipeline [Required] Identifier of the pipeline
IdEntity [Required] Identifier of the related Entity

Considerations:

  • All the Entities must be associated to the FileIngestionDatabricks pipeline, since it will be used by all the Entities -no matter the method used to add their Assets to the platform- to ingest the raw copy of the Asset into Databricks.
  • Pipelines have a Globally Unique Identifier (GUID) that is unique above all the Sidra installations. This GUID is stored in the Pipeline table in the column ItemId. It is recommended to use the ItemId to get the Id of the pipeline.
  • The ItemId of the FileIngestionDatabricks is F8CD4AD9-DCAE-4463-9185-58292D14BE99.

Add Entity-pipeline relationship using an SQL script

Add an SQL script following the naming conventions to the Scripts\CoreContext folder or to the place configured in the DatabaseBuilder from where it retrieves the scripts. A sample of the script to add a relationship between an Entity and a pipeline can be found below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- DECLARE
DECLARE @Id_Entity INT = 100 -- Id of the entity  
DECLARE @Id_Pipeline INT = 20 -- The Id of the pipeline. If it is not know, it should be retrieved using the ItemId of the pipeline

DECLARE @Id_Ingestion_Pipeline INT =
(
    SELECT [Id]
    FROM [DataIngestion].[Pipeline]
    WHERE [ItemId] = 'F8CD4AD9-DCAE-4463-9185-58292D14BE99'
)

-- ROLLBACK
DELETE FROM [DataIngestion].[EntityPipeline]
WHERE [IdEntity] = @Id_Entity AND [IdPipeline] IN (@Id_Pipeline, @Id_Ingestion_Pipeline)

-- SCRIPT
INSERT [DataIngestion].[EntityPipeline] 
     ([IdEntity],   [IdPipeline]) 
VALUES 
     (@Id_Entity,   @Id_Pipeline)
    ,(@Id_Entity,   @Id_Ingestion_Pipeline)

Add Entity-pipeline relationship using Sidra API

Sidra API requires requests to be authenticated, the section How to use Sidra API explains how to create an authenticated requests. For the rest of the document, it is going to be supposed that Sidra API is deployed in the following URL: 

1
https://core-mycompany-dev-wst-api.azurewebsites.net

Before creating an relationship between an Entity and a pipeline, it is required to know the Id of each of them.

Associate an entity to a pipeline

The only required pieces if information are the Ids of the Entity and the pipeline, and include them in the URL of the request:

Request

1
POST https://core-mycompany-dev-wst-api.azurewebsites.net/api/metadata/entities/{idEntity}/pipelines/{idPipeline}?api-version=1.0

Response

1
2
3
4
5
6
7
[{
        "idEntity": 100,
        "idPipeline": 20,
        "entityName": "MyEntityName",
        "pipelineName": "MyPipelineName"
    }
]