Data Loader Command Line Interface Tool Tutorial

Data Loader Command Line Interface Tool Tutorial#

This guide walks you step by step through using the Data Loader Command Line Interface (CLI) Tool.

It begins by uploading a CSV file to Squirro. In the last step, a custom source script is then implemented to implement a proprietary data format.

Installation#

To get started, make sure the Toolbox is installed. The Toolbox page also contains some basic information to familiarize yourself with how to execute the commands contained in the Squirro Toolbox.

Folder Setup#

Create a new folder on your file system and use the command line to go into this window. On Windows this is achieved with the following procedure:

Start the “Command Prompt” from the Start menu.
Type “cd FOLDER” where FOLDER is the path to the folder where you are executing the tutorial.

Download Example Data#

Download the file interaction.csv and place it in the tutorial folder.

Set up Connection#

The data loader connects with a Squirro instance. For this, three pieces of information are required:

cluster
token
project-id

A full manual on where to find thus information can be found on the Connecting to Squirro page.

Once you have this information, go to the command prompt window and type the following commands:

Setting variables

set CLUSTER="..."
set TOKEN="..."
set PROJECT_ID="..."

Replace all the “…” preferences with their actual values.

CSV Import#

The first step is to import the interactions.csv file into Squirro.

Step 1: Empty Invocation#

Initially simply execute the squirro_data_load command with the --help argument. This will output a full list of options.

Data load command

squirro_data_load --help

If you get a help output back, this confirms that the Data Loader has been correctly installed.

A full reference documentation that is a bit easier to read than this output is available on the primary Data Loader CLI Tool page.

Step 2: Provide Configuration#

The data loader command needs information about where to find the Squirro server, how to log in and into which project to import the data. In Step 1 we created variables for these settings. The way to use these variables is as follows:

Data load command

squirro_data_load ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN%

This command will fail, because further parameters are required. Those will be provided below.

Using variables here is optional and how exactly to insert them depends on the environment. The example here assumes a Microsoft Windows command prompt or Batch file.

Alternatively the parameters can also be hard-coded into the command. In that case, the command would look something like this:

Data load command

squirro_data_load ^
    --cluster https://squirro-cluster.example.com ^
    --project-id pB0JyUihQsOXGyLaUiFPDw ^
    --token 2df…6ba

All the further examples on this page will be using the approach using variables to facilitate copy/paste of the examples.

Step 3: Select Source Type and Source File#

The next step is to specify what data source is being imported. The first parameter for that is --source-type which selects the built-in source type to use. Depending on the source type additional parameters is required. For the “csv” source type that is the --source-file which points to the path where the CSV file can be found.

Data load command

squirro_data_load ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv

This command will still fail, because the mapping is missing. This is the next step

Step 4: Data Mapping#

Squirro has a specific [Item format] when importing data. The data loader needs to know which input fields correspond to which Squirro item field. That is done mainly through the --map-* arguments, such as -map-id or --map-title.

The following command is the first one that will execute and actually import some items from the CSV file:

Data load command

squirro_data_load ^
    -v ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv ^
    --map-id InteractionID ^
    --map-title InteractionSubject

Open the Squirro project and there are four interactions that have been indexed into the project. They will only have a title, no other information.

Step 5: Map additional data#

By mapping a few additional fields, the Squirro items are almost complete. The following command adds the creation date as well as a body.

This command also introduces the --source-name argument which gives the source a readable name. Before executing this command it’s recommended to remove the previous source (called “Upload”) from the project.

Data load command

squirro_data_load ^
    -v ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv ^
    --map-id InteractionID ^
    --map-title InteractionSubject ^
    --map-created-at Date ^
    --map-body Notes ^
    --source-name "Interactions (CSV)"

Step 6: Configure Labels#

Note

Labels were previously referred to as facets in the Squirro UI. You will still see references to facets in the code, and in some places within the Squirro UI. All facets can be treated as labels.

Squirro items can have keywords. These keywords are used for label navigation, to easily group data in dashboards or navigate on semi-structured data. The example data contains a column called “InteractionType” which gives each interaction a type. That type should be made available in the Squirro user interface for easy searching and navigation.

For that, create a new file in the tutorial folder called “facets.json”. That file provides a mapping of input data to the labels in Squirro.

facets.json

{
    "InteractionType": {
        "name": "Type"
    },
}

This example uses the “InteractionType” column from the input data and creates a keyword called “Type” in Squirro.

To make use of this, the import command is extended with the --facets-file argument:

Data load command

squirro_data_load ^
    -v ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv ^
    --map-id InteractionID ^
    --map-title InteractionSubject ^
    --map-created-at Date ^
    --map-body Notes ^
    --source-name "Interactions (CSV)" ^
    --facets-file facets.json

When executing this, the Type label is now available in the user interface. For example, the following search query will now only return meetings: “Type:Meeting” (enter without the quotes).

Step 7: Label data types#

By default labels are plain text. Squirro also supports numeric and date types for labels. Also there is a simple configuration option to accept multi-value labels. The following example adds three new labels in the labels.json file:

facets.json

{
    "InteractionType": {
        "name": "Type"
    },
    "Attendees": {
        "name": "Attendees",
        "delimiter": ";",
    },
    "DurationMinutes": {
        "name": "Duration",
        "data_type": "int",
    },
    "NoAttendees": {
        "name": "Attendee Count",
        "data_type": "int",
    },
}

The import command remains unchanged.

Data load command

squirro_data_load ^
    -v ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv ^
    --map-id InteractionID ^
    --map-title InteractionSubject ^
    --map-created-at Date ^
    --map-body Notes ^
    --source-name "Interactions (CSV)" ^
    --facets-file facets.json

When reloading the data in Squirro, note that Attendees has been imported with multiple values for each item. Also the duration and number of attendees has been imported as a numeric label. Numeric labels can be used in the dashboards with calculations, such as outputting average or sum of values over time.

Step 8: Templating#

So far the Squirro items contained a simple body that was taken directly from the CSV file. Often there is requirements to show more complex content, for example by combining multiple columns or adding conditions.

Add a new file called template-body.html. This is a Jinja2 template for formatting the body of the Squirro items.

template-body.html

<table>
    <tr>
        <th>Type</th>
        <td>{{ row.InteractionType }}</td>
    </tr>
    <tr>
        <th>Attendees</th>
        <td>{{ item.keywords.Attendees    | join(', ')    | e}}</td>
    </tr>
    <tr>
        <th>Duration</th>
        <td>{{ row.DurationMinutes }} minutes</td>
    </tr>
</table>

<p>{{ row.Notes    | e}}</p>

This template adds a summary table before the interaction notes which shows some of the metadata of the interaction.

The labels file remains unmodified.

facets.json

{
    "InteractionType": {
        "name": "Type"
    },
    "Attendees": {
        "name": "Attendees",
        "delimiter": ";",
    },
    "DurationMinutes": {
        "name": "Duration",
        "data_type": "int",
    },
    "NoAttendees": {
        "name": "Attendee Count",
        "data_type": "int",
    },
}

The command is changed slightly. First, the --map-body is replaced with --map-abstract. This ensures that the notes are still used for the overview in the search result screen. Then the --body-template-file parameter is used to specify the template that has been created above.

Data load command

squirro_data_load ^
    -v ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv ^
    --map-id InteractionID ^
    --map-title InteractionSubject ^
    --map-created-at Date ^
    --map-abstract Notes ^
    --source-name "Interactions (CSV)" ^
    --facets-file facets.json ^
    --body-template-file template-body.html

After loading the data with this command, the individual interactions look a lot more readable in Squirro and give the user an easy glance at the essential information.

Step 9: Pipelets#

In the Squirro pipeline, pipelets are used for enrichments that happen during data import. To re-use the power of this approach locally, the data loader also allows the specification of pipelets. The interface is identical, so pipelets can be used locally with the data loader and/or remotely with the Squirro pipeline.

The goal of this example is to convert the interaction duration from minutes to hours.

As a first step, the label configuration is extended. This now contains a new label “Duration (Hours)” which uses the float data type.

facets.json

{
    "InteractionType": {
        "name": "Type"
    },
    "Attendees": {
        "name": "Attendees",
        "delimiter": ";",
    },
    "DurationMinutes": {
        "name": "Duration",
        "data_type": "int",
    },
    "DurationHours": {
        "name": "Duration (Hours)",
        "data_type": "float",
    },
    "NoAttendees": {
        "name": "Attendee Count",
        "data_type": "int",
    },
}

Next a pipelet configuration file is created which declares all the pipelets that are to be run.

pipelets.json

{
    "DurationConversionPipelet": {
        "file_location": "duration_conversion.py",
        "stage": "before templating",
    },
}

This configuration references a DurationConversionPipelet which is declared in the file duration_conversion.py. This is a code file written in the Python programming language. The contents is as follows and quite easy to follow with the inline comments.

duration_conversion.py

"""Pipelet to convert the duration from minutes to hours.

Takes the keyword `Duration` and moves the values into a new `Duration (Hours)`
keyword.
"""
from squirro.sdk import PipeletV1

class DurationConversionPipelet(PipeletV1):
    def consume(self, item):
        """This method is called once for each item to be processed."""

        # Names of the keywords (before and after)
        orig_kw_name = 'Duration'
        new_kw_name = 'Duration (Hours)'

        keywords = item.get('keywords', {})
        if orig_kw_name in keywords:
            # Convert every existing value to hours
            new_values = []
            for value in item.get('keywords', {}).get(orig_kw_name, []):
                new_values.append(round(float(value) / 60, 2))

            # Replace orig with new keyword
            del item['keywords'][orig_kw_name]
            item['keywords'][new_kw_name] = new_values

        # Return the modified item
        return item

The workings of this pipelet is quite easy. The main logic is on line 21. The rest of the code is tasked with correctly handling multiple values in the keyword (all keywords can have multiple values attached in Squirro) and switching the keywords.

Everything comes together again with the data load command. The only change needed is the --pipelets-file argument which points to the pipelets configuration file created above and the --transform-items argument.

Data load command

squirro_data_load ^
    -v ^
    --cluster %CLUSTER% ^
    --project-id %PROJECT_ID% ^
    --token %TOKEN% ^
    --source-type csv ^
    --source-file interaction.csv ^
    --map-id InteractionID ^
    --map-title InteractionSubject ^
    --map-created-at Date ^
    --map-body Notes ^
    --source-name "Interactions (CSV)" ^
    --facets-file facets.json ^
    --pipelets-file pipelets.json
    --transform-items

When executing this command, the items are loaded into Squirro. But now the duration has been changed to a float value representing the duration in hours instead of minutes.

Custom Data Source#

Writing a plugin for a custom data source is covered in How To Write a Custom Data Loader Plugin.

Data Loader Command Line Interface Tool Tutorial

Contents

Data Loader Command Line Interface Tool Tutorial#

Installation#

Folder Setup#

Download Example Data#

Set up Connection#

CSV Import#

Step 1: Empty Invocation#

Step 2: Provide Configuration#

Step 3: Select Source Type and Source File#

Step 4: Data Mapping#

Step 5: Map additional data#

Step 6: Configure Labels#

Step 7: Label data types#

Step 8: Templating#

Step 9: Pipelets#

Custom Data Source#