Skip to content

How to add a new Entity

This tutorial explains how to to add a new Entity in Sidra.

Using the Sidra API to create a new Entity is preferred, since the API will perform the needed validation checks, redusing the chances for errors.

This is required for example when a new type of file has to be loaded from a FTP server or new Entities are going to be pulled from an API. When adding a new Entity, there are two possible scenarios:

  • Scenario 1: the Entity belongs to an existing Provider.
  • Scenario 2: A new Provider must be configured. If the Provider is not created yet, this tutorial How to add a new Provider can be used to create it. Entities are stored in the Entity table in the Sidra Core metadata database.

There are two ways to create a new Entity in Sidra:

  • Create a new Entity by creating an SQL script to insert the new Entity in the database.
  • Create a new Entity by using the Sidra API endpoint to add new Entities.

The method used will depend, for example, on having access to the source code of the solution -where the SQL scripts must be placed to be executed- or having to integrate with a third-party tool. In the latter case, the Sidra API is the recommended approach.

Information

Take into account that Sidra metadata tables do not allow for space characters in the names of Attributes or Entities. From version 2022.R2 1.12.2 update, the metadata API will sanitize spaces in characters if they are provided in Entity or Attribute names, by replacing with `_` character. Note that, for adding metadata, the name sanitation will be only done by the API, the SQL scripts will not do it, so it will be required to avoid spaces in Attribute or Entities names by this method.

Add a new Entity by 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 new Entity can be found below:

-- ENTITY CONFIGURATION

DECLARE @Id_Provider INT = 10
DECLARE @Id_Dsu INT = '{ID DSU}'
DECLARE @EntityItemId UNIQUEIDENTIFIER = NEWID()
DECLARE @CurrentDate DATETIME2 = GETUTCDATE()
DECLARE @EntityName VARCHAR(MAX) = 'MyNewEntity'
DECLARE @Entity_RegularExpression NVARCHAR(MAX) = '^myentity_((?<year>\d{4})-(?<month>\d{2})-(?<day>\d{2}))_((?<hour>\d{2})-(?<minute>\d{2})-(?<second>\d{2})).csv'
DECLARE @EntityFormat VARCHAR(10) = 'csv'
DECLARE @EntityEncoding VARCHAR(50) = 'UTF-8'
DECLARE @EntityDelimiter VARCHAR(6) = '\u0009'
DECLARE @EntityHeaderLines INT = 1
DECLARE @EntityProperties VARCHAR(MAX) = CONCAT('{"sourceTableName": "[staging].[', @EntityName, ']"}')
DECLARE @Entity_ParentSecurityPath VARCHAR(100) = CONCAT(@Id_Dsu, '/', @Id_Provider)

-- ENTITY CREATION

INSERT INTO [DataIngestion].[Entity]
    ([IdProvider], [Name], [TableName], [RegularExpression], 
    [StartValidDate], [EndValidDate], [Serde], [SerdeProperties], 
    [Encoding], [HeaderLines], [FieldDelimiter], [LastUpdated],
    [LastDeployed], [Format], [NullText], [ReCreateTableOnDeployment],
    [RowDelimiter], [FilesPerDrop], [SourcePath], [Description],
    [AdditionalProperties], [IdTableFormat], [GenerateDeltaTable],
    [ParentSecurityPath], [CreationDate], [ItemId])
VALUES
    (@Id_Provider, @EntityName, @EntityName, @Entity_RegularExpression,
    DATEADD(DAY, -1, @CurrentDate), NULL, NULL, NULL,
    @EntityEncoding, @EntityHeaderLines, @EntityDelimiter, GETUTCDATE(),
    NULL, @EntityFormat, NULL, 1,
    NULL, 1, NULL, NULL,
    @EntityProperties, 3, 0,
    @Entity_ParentSecurityPath, GETUTCDATE(), @EntityItemId)

Add a new Entity by 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:

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

This is the sequence of requests required to create a new Entity, some of the request are used to gather information about the Entity, if that information is already available, those requests will not be necessary:

Step 1. Get the identifier of the Provider

Before creating an Entity, it is required to know the Id Provider to which the Entity will be associated. If the identifier is already known, this step can be skipped. If not, this request can be made to retrieve all the Providers:

Request

GET https://core-mycompany-dev-wst-api.azurewebsites.net/api/metadata/providers/all?api-version=1.0

It will return an object with the list of Providers, including their Ids.

Response

[
    {
        "id": 10,
        "itemId": "3fa85f64-5717-4562-b3fc-2c963f66afa6", 
        "providerName": "MyNewProvider",
        "databaseName": "dwmynewprovider",
        "dataStorageUnitName": "MyDSU"
    }
]

From the previous response, the Id of the Provider can be retrived and used in the next step to populate the field idProvider.

Step 2. Create a new Entity

By using the Sidra API some of the fields of the Entity are automatically populated -like Id, ParentSecurityPath or CreationDate-. Once the rest of the information of the entity is gathered, it can be created using this request.

Request

POST https://core-mycompany-dev-wst-api.azurewebsites.net/api/metadata/entities?api-version=1.0

And adding the following content as part of the body.

Request body

{
  "filesPerDrop": 1,
  "rowDelimiter": null,
  "reCreateTableOnDeployment": true,
  "nullText": null,
  "format": "csv",
  "lastDeployed": null,
  "lastUpdated": "2022-06-20T10:00:00.885Z",
  "fieldDelimiter": "\\u0009",
  "headerLines": 1,
  "encoding": "UTF-8",
  "serdeProperties": null,
  "serde": null,
  "endValidDate": null,
  "startValidDate": "2022-06-19T10:00:00.885Z",
  "regularExpression": "^myentity_((?<year>\\d{4})-(?<month>\\d{2})-(?<day>\\d{2}))_((?<hour>\\d{2})-(?<minute>\\d{2})-(?<second>\\d{2})).csv",
  "tableName": "MyNewEntity",
  "name": "MyNewEntity",
  "description": "Description of MyNewEntity",
  "idProvider": 10,
  "additionalProperties": "{\"sourceTableName\":\"[staging].[MyNewEntity]\"}",
  "generateDeltaTable": false,
  "idTableFormat": 3,
  "sourcePath": null
}

The response will return the Id of the Entity created, which can be used, for example, for adding a new Attribute.

Response

{
  "id": 100,
  "tags": null,
  "providerName": "MyNewProvider",
  "databaseName": "dwmynewprovider",
  "dataStorageUnitId": 1,
  "creationDate": "2022-06-20T08:49:48.13",
  "detail": null,
  "entitySize": null,
  "source": ".MyNewEntity",
  "viewDefinition": null,
  "entityType": 1,
  "dsu": null,
  "imageURL": null,
  "filesPerDrop": 1,
  "rowDelimiter": null,
  "reCreateTableOnDeployment": true,
  "nullText": null,
  "format": "csv",
  "lastDeployed": null,
  "lastUpdated": "2022/06/20",
  "fieldDelimiter": "\t",
  "headerLines": 1,
  "encoding": "UTF-8",
  "serdeProperties": null,
  "serde": null,
  "endValidDate": null,
  "startValidDate": "2022/06/19",
  "regularExpression": "^myentity_((?<year>\\d{4})-(?<month>\\d{2})-(?<day>\\d{2}))_((?<hour>\\d{2})-(?<minute>\\d{2})-(?<second>\\d{2})).csv",
  "tableName": "MyNewEntity",
  "name": "MyNewEntity",
  "description": "Description of MyNewEntity",
  "idProvider": 10,
  "additionalProperties": "{\"sourceTableName\":\"[staging].[MyNewEntity]\"}",
  "generateDeltaTable": false,
  "idTableFormat": 3,
  "sourcePath": null
}

For more information about the Entity fields, please check this page.


Sidra Ideas Portal


Last update: 2022-11-10
Back to top