Via JSON
To create a custom table without using a template, it will be necessary to define its structure through a JSON file.
This JSON file must contain a wide range of information. Some of these pieces of information are mandatory, others are optional. Moreover, they might have to follow specific criteria, either for their type or for possible values.
JSON is a data format which allows you to represent structured information (like XML)
Any creating or modification of a Data Model table must necessarily be done in this format.
If you are not familiar with this format, it is possible to validate your code with the help of the following website: https://jsonlint.com/
The structure of the JSON file is the same one as the payload of API calls made to create a table.
You can find a detailed guide in the Developers Portal.
To create your own table, your JSON file can be created in advance or created/edited from the interface using the built-in JSON editor.
Click on "Create a Table", then choose the "From JSON" option, available in each tab, next to the templates.
The tab from which you import the JSON does not matter: the type of table must be determined by the file structure.
You must first specify the entity of the table.
Choosing this entity is important because it cannot be changed later. In most cases, it is recommended to have all tables in the same data model linked to the same entity.
Click on "Upload from my computer" to open a modal window. You will be prompted to upload the definition file in JSON format that you prepared beforehand. You can also edit the inserted JSON directly from the interface or even create it from scratch using the JSON editor (or paste it there).
The editor will let you know if your JSON does not follow proper JSON formatting.
Click on "Create" to launch the creation of the table. Any errors encountered during table creation will be displayed, allowing you to fix the issues in the JSON.
Components of the "Definition File"
To create your "Custom Table", you will need to provide a JSON "Definition File" which will contain the following pieces of information:
- The type of table: Interaction, Profile Linked Data or Repository. The type of table determines its behavior. For additional information about them, we invite you to read the page "Understanding the Different Types of Tables".
- The link between this Custom Table and its ‘mother’ Table (Profile or other Custom Table)
- The different fields of this Custom Table, their type and their specific parameters (such as for the creation of Profile Table attributes)
Some of these pieces of information are mandatory, others are optional. Moreover, they might have to follow specific criteria.
For additional information about these types and the criteria specific to each of these fields, we invite you to read the "Structuring your JSON Definition File" page.
Illustration of a complete JSON file
The file below provides an illustration of the format taken by a file containing every possible parameter, alongside the example values.
Visual illustration of a JSON definition file
{
"name": "OnlineOrders",
"entityName": "entity",
"type": "INTERACTIONS",
"primaryKeyAttribute": "orderId",
"creationTimeAttribute": "orderMoment",
"compositeKey": null,
"bigTable": false,
"attributes": [
{
"name": "orderId",
"valueType": "STRING",
"mandatory": true,
"indexed": true,
"unique": true,
"valueRestriction": {
"minLength": 20,
"maxLength": 20
}
},
{
"name": "storeId",
"valueType": "LONG",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": null
},
{
"name": "customerId",
"valueType": "LONG",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": null
},
{
"name": "orderMoment",
"valueType": "TIMESTAMP",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": null
},
{
"name": "amount",
"valueType": "NUMBER",
"mandatory": true,
"indexed": false,
"unique": false,
"valueRestriction": null
},
{
"name": "status",
"valueType": "STRING",
"mandatory": true,
"indexed": true,
"unique": false,
"valueRestriction": {
"acceptedValues": [
"NEW",
"SHIPPING",
"SHIPPED",
"CANCELED"
]
}
}
],
"valueAttribute": "amount",
"foreignKeys": [
{
"name": "link-to-customer",
"attribute": "customerId",
"reference": {
"tableType": "PROFILE_TABLE",
"tableId": "97",
"attribute": "customerId"
},
"onDelete": "CASCADE"
},
{
"name": "link-to-store",
"attribute": "storeId",
"reference": {
"tableType": "CUSTOM_TABLE",
"tableId": "d4d85214-66d4-4072-b98b-a86c9253b980",
"attribute": "storeId"
},
"onDelete": "CASCADE"
}
],
"eventsToTrigger": [
{
"name": "shipped-online-order",
"triggerRules": [
{
"onOperation": "UPDATE",
"onAnyAttribute": [
"status"
],
"onMatchingState": {
"stateBeforeOperation": {
"logicalOperator": "AND",
"predicates": [
{
"attribute": "status",
"operator": "!=",
"value": "SHIPPED"
}
]
},
"stateAfterOperation": {
"logicalOperator": "AND",
"predicates": [
{
"attribute": "status",
"operator": "=",
"value": "SHIPPED"
}
]
}
}
}
]
}
],
"cleaningRules": {
"oldestRecordsRule": {
"numberOfRecordsToKeep": 200000
}
},
"displayOptions": {
"displayName": "Online Orders",
"description": "The table that hosts the orders from an online store. Those orders are linked to the Customers profile table.",
"forAttributes": [
{
"name": "orderId",
"description": "The unique id of the order",
"displayName": "Online Orders ID"
},
{
"name": "storeId",
"description": "The unique id of the online store on which the order has been created.",
"displayName": "Store ID"
},
{
"name": "customerId",
"description": "The unique id of customer who has created the order.",
"displayName": "Customer ID"
},
{
"name": "orderMoment",
"description": "The moment when the order has been created",
"displayName": "Order Moment"
},
{
"name": "amount",
"description": "The amount of the order",
"displayName": "Amount"
},
{
"name": "status",
"description": "The status of the order",
"displayName": "Status"
}
],
"forEvents": [
{
"name": "shipped-online-order",
"displayName": "Shipped Online Order"
}
],
"layout": {
"defaultAttributes": [
"orderId",
"customerId",
"amount",
"status"
],
"sections": [
{
"displayName": "Identifiers",
"attributes": [
"orderId",
"customerId",
"storeId"
]
},
{
"displayName": "Other attributes",
"attributes": [
"amount",
"status",
"orderMoment"
]
}
]
}
}
}
Field types
Depending on their types, the fields must follow specific constraints:
-
STRING: string of characters [a-z, A-Z, 0-9]
- the default max number of characters for a STRING type is 255. It can be modified with a "typeValidator" parameter (see table above)
-
LONG:
- a 64bit integer
- maximum value: 9223372036854775808
- minimum value: -9223372036854775808
- no space, dot or comma
-
BOOLEAN: match one of the following values “true” or “false”
-
DATE: string of characters matching one of the following formats:
- YYYYMMDD
- YYYY-MM-DD
- dd/MM/yyyy
-
TIMESTAMP: string of characters matching one of the following formats:
- YYYYMMDD *
- YYYY-MM-DD *
- dd/MM/yyyy *
- YYYYMMDDhhmmss
- YYYY-MM-DD hh:mm:ss
- dd/MM/yyyy HH:mm:ss
- MM/dd/yyyy hh:mm:ss AM|PM
If no values are specified for "hhmmss", the values "00:00:00" will be added
- NUMERIC:
- separator must be '.'
- no character amount limit
In addition to general types, there are advanced types for specific fields, in order to ensure their format to match the format expected by Actito.
These are the following types: "EMAIL_ADDRESS","SEX","COUNTRY","LANGUAGE","PHONE_NUMBER","PERSON_TITLE". The syntax required for theses types is detailed in the "Using Predefined Attributes" page.
To make sure that fields of your table match one of these types, you will have to specify it in the "valueRestriction" parameter.
Table Index
When creating a new field, an index can be used to assist Actito in finding and classifying data quicker.
What is the index?
An index stores location of records based on fields which have been indexed. Once Actito has found the location with the help of the index, the data can be extracted by directly accessing the appropriate location. In this way, finding data will be significantly quicker when using the index.
It is therefore useful to index fields on which searches, sorting or targeting are frequently executed, or on any field associated to other tables (link key with the other tables).
For example, it is relevant to index the following fields of a table: business key + foreign key + creation date.
While indexes facilitate search and targeting, they can also slow down performance when creating or updating data.
In such cases, all table indexes will indeed have to be updated by Actito. Consequently, the number of indexed fields in a table should be limited.
A custom table cannot exceed 15 indexed fields.
Please note that a "unique" field will be automatically indexed and that the fields "id", "creationMoment" and "updateMoment" will be automatically created and indexed by default.