Flexible File Uploads - The Basics

Learn the basics of Flexible File Uploads. Create a custom source, define a file input and define an SQL-query to import data from a CSV into entities and relationships.

Intro

Flexible File Uploads are a powerful way to import data from systems that do not offer technical integration options such as APIs, database access, or directory protocols. They are also ideal for sources where data changes infrequently and only needs to be updated periodically (e.g. weekly, monthly, quarterly or even yearly).

This guide explains how to configure the Flexible File Upload feature step by step.

Prerequisites

Before you start, make sure that:

• You have “Data Source Admin” or “Admin” permissions in Elimity

• You know which entities, attributes and relationships you want to model in Elimity

• You have an example file (.csv, .json, etc.) that contains the data you want to import into Elimity

For the purpose of this guide we will use a simple data source named “HRify” from which we will import "Employees" as an entity type with a few attributes.

Step 1 - Create a custom source

You can skip this step if you already have a configured source.

In the main left navigation bar, go to "Administration" -> "Sources":

Click "Add Source":

Now select the "Custom" data source by clicking on "Set up":

In the next screen click “Set up”:

Here, we can give a name to our new custom source. In our case this will be “HRify”.

Now you can define the data model either manually or via an export file.

The easiest way is to select "Upload Export file" and use this file to import the data model:

1KB

HRify-data-model.json

Open

In case you want to do it manually you can select "Manually" and click "Continue":

In case you chose the manual flow, we need to create at least one entity type in our source. In our case we create an entity type "Employees" like this:

You need to define the singular and plural forms and add an icon representing the entity type. Of course this entity type can be anything from devices, roles, folders, users, etc. depending on the source you are planning to connect.

After clicking on confirm you’ll see that we now have set up a source with one entity type in Elimity. You can now also see this new source in your list of sources in the main left navigation bar.

If you chose the manual creation of the data model you'll have to add the correct attributes to the "Employees" entity type. All of the attributes are of type String, except for start_date and active. They are of type Date and Boolean :

• first_name

• last_name

• email

• department

• job_title

• employment_type

• start_date (type: date)

• active (type: boolean)

Step 2 - Create a file definition

Now that our source is configured we can start defining the file(s) and query/queries needed to import data in Elimity.

In the main left navigation bar, go to "Sources", select the correct source ("HRify" in our case) and select the "File Uploads" tab.

You should see the following view:

First we start by creating a "definition". Click "Add Definition" to create one:

There are a few different configuration options for ‘a definition’:

• A label is required for a specific file upload. In our case we want to import employee data so we add a definition with label: "employees". This label doesn’t have to be the same as the filename.

• The "Accept" field allows to only highlight files of a certain type when performing the upload. Here we choose for .csv as we only want users to be able to upload a .csv file.

• You can also allow multiple files to be uploaded by enabling the "Allow multiple files" checkbox. This can be useful if for example the data you want to upload is divided into different files.

Click "Confirm". We have now created our very first file definition, which will be used as a source for querying data (see next step):

Step 3 - Define a query to import data

Now that we have created our file definition, we will create a query which will parse data from the file.

Before we dive into specific queries, let’s first explain the engine behind this feature.

Behind the scenes, Elimity uses DuckDB (a RDBMS that supports SQL) to query uploaded data.

It can be useful to review the DuckDB documentation to become familiar with the technology.

In particular, the section on reading from CSV files is especially useful.

As you see in the image below there are two important things to note before we start creating our first query:

1. The file definition creates an SQL query parameter $p0 which can be used to reference the file itself in any of the defined queries.

2. The specific queries can be configured under "Entity queries" (or under "Relationship queries" if these are part of our data model). For the purpose of this article we left out Relationship queries.

Let’s start with the minimal required query to import data into Elimity. From there we can add extra attributes to finalize the query. Let’s take the CSV below as an example:

Our file has data on the following columns:

• employee_id

• first_name

• last_name

• email

• department

• job_title

• employment_type

• start_date

• active

In Elimity we only need 2 columns to make a successful data import, namely, id and name. These must be the first two columns in the result set of our query.

For example, the following query returns two columns. The first one containing data from the employee_id column and the second one containing a concatenation of both the first_name column and the last_name column:

SELECT
  employee_id,
  (first_name || ' ' || last_name)
FROM READ_CSV($p0)

We could have worked with aliases but this is not required or isn’t used in mapping the result set to Elimity’s attributes. Only the order in which the columns are returned is important. First the id column, secondly the name column.

When we click "Upload and import", data is imported in our entity type "Employees":

Although the entity type "Employees" has many different attribute types other than id and name, we only need those two to be able to create entities. All the other attributes currently have a value of Not assigned as can be seen in the screenshot above. This was expected and is considered a good practice to get an initial version up and running.

Now that we have our first minimal query up and running we can extend it with attributes we would like to import. We do this be both defining a correct query (with the attributes in the right order) and selecting the attributes to include:

SELECT
  employee_id AS id,
  first_name || ' ' || last_name AS name,
  CAST(active AS BOOLEAN),
  department,      
  email,
  employment_type,    
  first_name,        
  job_title,                         
  last_name,                                        
  CAST(start_date AS DATE)
FROM READ_CSV($p0)

As you see in the query above, the attributes (coming after id and name) are lexicographically sorted (first active, then department, then email, then employment_type, first_name, etc.).

This is very important to keep in mind as the import relies on the order of the columns to import the data correctly.

Now, the only thing we need to do is select the attributes we want to import. In the edit query screen click "Included attribute types":

Select all the attributes you have defined in your query which you want to import in Elimity:

The amount of columns in the result set of the query should match the amount attributes you want to import into Elimity + 2 (for id and name).

For example, in our case we have 10 columns in our result set. 2 for id and name and 8 for all other attributes (active, department, email, employment type, first name, job title, last name and start date).

If we now look at our data we see that all other attributes have been populated:

Conclusion

This article demonstrated how to set up a manual CSV import in Elimity Insights with the help of a file definition and an SQL query within a custom "HRify" source for the "Employees" entity type.

The same principles that we applied to the "Employees" entity type apply to importing relationships. For relationships, the query must return two entity IDs followed by optional relationship attributes. This will be covered in a future article.