Dataload Configuration

File Template

The template is a json file with the following structure

Main configuration

Configuration item	Definition	Status
Type	File type/extension supported by the template (csv, tsv, txt, xlxs).	MANDATORY
TemplateName	Name of the template.	MANDATORY
MaxFileSize	Maximum size supported by the template (in bytes).	MANDATORY
SampleFilePath	Name of the template file corresponding to the destination table.	MANDATORY
Truncate	Command that delete all rows of the staging table (always true).	MANDATORY
Parameters	Define the parameters accepted by the template field. Depends on the extension supported.	OPTIONNAL
Database.Type	Type of database connection	MANDATORY
Database.ConnectionString	Database connection credentials: server (ex: postgresql-server) port (ex: 5432) user id password database (ex: name of the db) Enlist (ex: true)	MANDATORY
Table.Schema	Destination space (ex: staging, infomart, datavault, businessvault,…).	MANDATORY
Table.Name	Name of the (staging) table	MANDATORY

Parameters for a CSV file

Separator (ex: "\t")
Encoding (ex: "UTF-8")
DetectDoubleQuotes

Example of the JSON template file for a csv

JSON

{
 "FileTemplates": [
    {
      "Type": "CSV",
      "TemplateName": "IMDB Movies",
      "MaxFileSize": 167772160,
      "SampleFilePath": "sample.tsv",
      "Truncate": true,
      "Parameters":
      {
        "Separator": "\t",
        "Encoding": "UTF-8"
      },
      "Database": {
        "Type": "postgresql",
        "ConnectionString": "server=postgresql-server;port=5432;user id=postgres;password=dFakto2021;database=MKN_DQ_Testing;Enlist=true"
      },
      "Table": {
        "Schema": "stg",
        "Name": "movies"
      }
    }

Parameters for a XLXS file

SheetName (mandatory if multiple sheets)
ColumnOffset (optional) (-> still write ' = 0”)
RowOffset (optional) (-> still write ' = 0”)
Password (optional)

Webhooks

Configuration item	Definition	Status
Name	Name of the sink `/api/sinks/NAME`	MANDATORY
Maps	maps to be apply on the JSON event content (you can add as many map as you want)	MANDATORY
Maps.ColumnName	Name fot he target column	MANDATORY
Maps.SQLExpression	Sql expressions that will compute the content of the column	OPTIONNAL
Maps.Path	Json path to query the value to insert in the column	OPTIONNAL
Database.Type	Type of database connection	MANDATORY
Database.ConnectionString	Database connection credentials: server (ex: postgresql-server) port (ex: 5432) user id password database (ex: name of the db) Enlist (ex: true)	MANDATORY
Table.Schema	Destination space (ex: staging, infomart, datavault, businessvault,…).	MANDATORY
Table.Name	Name of the (staging) table	MANDATORY

Example configuration

CODE

  "EventSinks": [
    {
      "Name": "States",
      "Maps": [
        {
          "ColumnName": "load_date",
          "SqlExpression": "NOW()"
        },
        {
          "ColumnName": "testjson",
          "Path": "$"
        },
        {
          "ColumnName": "testbool",
          "Path": "$.testbook"
        }
      ],
      "Database": {
        "Type": "postgresql",
        "ConnectionString": "server=localhost;port=15432;user id=postgres;password=dFakto2021!;database=test;Enlist=true"
      },
      "Table": {
        "Schema": "stg",
        "Name": "sinks"
      }
    }
  ],