How to add a new Attribute¶
An Attribute in Sidra represents one of the fields or columns of an Entity.
So, after the creation of a new Entity, the next step is to create new Attributes for that Entity, one for each of the fields/columns of the Asset that is represented by the Entity.
If the Entity is not created yet, this tutorial How to add a new Entity can be used to create it.
Attributes provide details about the structure of the Asset and they will be used, along with Provider and Entity, to generate pipelines, transfer queries, data lake table creations, etc.
Attributes are stored in the Attribute table in the Sidra Core metadata database.
As it happens with other members of the metadata model, there are two ways to create a new Attribute:
- Add an Attribute by creating an SQL script to insert the new Attribute in the core metadata database.
- Add an Attribute by using the Sidra API endpoint to add new Attributes.
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 an Attribute 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 Attribute can be found below:
-- ATTRIBUTE CONFIGURATION
DECLARE @Id_Entity INT = 5327 -- Id of the Entity to which the Attribute belongs
DECLARE @Id_Provider INT = 1985
DECLARE @Id_Dsu INT = 1
DECLARE @SecurityPath_Attribute VARCHAR(100) = CONCAT(@Id_Dsu, '/', @Id_Provider, '/', @Id_Entity)
DECLARE @Order_Attribute SMALLINT =
(
SELECT COALESCE(MAX([Order]) +1, 1)
FROM [DataIngestion].[Attribute]
WHERE [IdEntity] = @Id_Entity
)
-- ATTRIBUTE CREATION
INSERT INTO [DataIngestion].[Attribute]
([IdEntity],[Name],HiveType,MaxLen,IsNullable,NeedTrim,RemoveQuotes,ReplacedText,ReplacementText,
SpecialFormat,TreatEmptyAsNull,IsPrimaryKey,[Order],IsCalculated,IsPartitionColumn,IsMetadata,SQLType,
ValidationText,[Description],SourceName,ParentSecurityPath,IsEncrypted,DataMask,ItemId)
VALUES
(@Id_Entity,'MyNewAttributeViaScript','INT',NULL,1,0,0,NULL,NULL,
NULL,0,1, @Order_Attribute,0,0,0,'INT',
NULL,NULL,NULL,@SecurityPath_Attribute,0,NULL,NEWID()),
(@Id_Entity,'MyNewAttributeViaScript','STRING',NULL,1,0,0,NULL,NULL,
NULL,0,1, @Order_Attribute+1,0,0,0,'DATETIME2',
NULL,NULL,NULL,@SecurityPath_Attribute,0,NULL,NEWID()),
(@Id_Entity,'MyNewAttributeViaScript','BOOLEAN',NULL,1,0,0,NULL,NULL,
NULL,0,1, @Order_Attribute+2,0,0,0,'BIT',
NULL,NULL,NULL,@SecurityPath_Attribute,0,NULL,NEWID())
Adding Sidra system Attributes¶
In the most common scenarios, the script above can be used to add the Sidra system Attributes. It is just required to setup the @Id_Entity to the appropriate value.
Add an Attribute by using the 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:
Before creating an Attribute, it is required to know the Id of the Entity to which the Attribute belongs.
Create a new Attribute¶
By using the Sidra API some of the fields of the Attribute are automatically populated -like Id or ParentSecurityPath-. Once the rest of the information of the Attribute is gathered, it can be created using the following request:
Request¶
And adding the following content as part of the body:
Request body¶
{
"idEntity": 1200,
"name": "MyNewAttributeviaAPI",
"description": "Description",
"hiveType": "INT",
"sqlType": "INT",
"isMetadata": false,
"isPartitionColumn": false,
"isCalculated": false,
"order": 1,
"isPrimaryKey": false,
"treatEmptyAsNull": false,
"removeQuotes": false,
"needTrim": false,
"isNullable": true,
"isEncrypted":false,
"dataMask":null,
"sourceType":"INT",
"replacedText":null,
"replacementText":null,
"specialFormat":null,
"maxLen":100
}
The response will return the Id of the Attribute created. A different request must be done for each of the Attributes that want to be created, since, currently, Sidra API does not support creating Attributes in bulk.
Response¶
{
"id": 1500,
"attributeFormats": null,
"tags": null,
"sqlType": "INT",
"isMetadata": false,
"isPartitionColumn": false,
"isCalculated": false,
"order": 1,
"isPrimaryKey": false,
"treatEmptyAsNull": false,
"specialFormat": null,
"replacementText": null,
"replacedText": null,
"removeQuotes": false,
"needTrim": false,
"isNullable": true,
"maxLen": 100,
"hiveType": "INT",
"name": "MyNewAttributeviaAPI",
"idEntity": 1200,
"validationText": null,
"description": "Description",
"sourceName": "MyNewAttributeviaAPI",
"isEncrypted": false,
"itemId": "191b09bb-9127-40ed-8549-19be9ccc848e",
"idProvider": 10,
"idDataStorageUnit": 1,
"dataMask": null,
"parentSecurityPath": "1/10/1200",
"securityPath": "1/10/1200",
"sourceType": "INT"
}
Adding Sidra system Attributes¶
In order to add the system Attributes, the same request can be used but using the following content in the body:
Information
The fields `idEntity` and `order` must be properly configured.
Request body¶
{
"idEntity": 1200,
"name": "MyNewAttributeviaAPI2",
"description": "Description",
"hiveType": "INT",
"sqlType": "INT",
"isMetadata": true,
"isPartitionColumn": false,
"isCalculated": false,
"order": 2,
"isPrimaryKey": false,
"treatEmptyAsNull": false,
"removeQuotes": false,
"needTrim": false,
"isNullable": false,
"isEncrypted":false,
"dataMask":null,
"sourceType":"INT",
"replacedText":null,
"replacementText":null,
"specialFormat":"BLOCK__OFFSET__INSIDE__FILE",
"maxLen":100
}
{
"idEntity": 1200,
"name": "MyNewAttributeviaAPI3",
"description": "Description",
"hiveType": "STRING",
"sqlType": "DATETIME2",
"isMetadata": true,
"isPartitionColumn": false,
"isCalculated": true,
"order": 3,
"isPrimaryKey": false,
"treatEmptyAsNull": false,
"removeQuotes": false,
"needTrim": false,
"isNullable": false,
"isEncrypted":false,
"dataMask":null,
"sourceType":"INT",
"replacedText":null,
"replacementText":null,
"specialFormat":"FROM_UNIXTIME(UNIX_TIMESTAMP())",
"maxLen":100
}
{
"idEntity": 1200,
"name": "MyNewAttributeviaAPI4",
"description": "Description",
"hiveType": "BOOLEAN",
"sqlType": "BIT",
"isMetadata": true,
"isPartitionColumn": false,
"isCalculated": true,
"order": 4,
"isPrimaryKey": false,
"treatEmptyAsNull": false,
"removeQuotes": false,
"needTrim": false,
"isNullable": false,
"isEncrypted":false,
"dataMask":null,
"sourceType":"INT",
"replacedText":null,
"replacementText":null,
"specialFormat":"FALSE",
"maxLen":100
}
For more information about the Attribute fields, please check this page.