Page Comparison

...

This

...

Setup

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

For this tutorial 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

Please 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 these information can be found in Connecting to Squirro.

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

Setting variables

Code Block

language	powershell

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

Replace all the "..." preferences with their actual values.

CSV Import

The first project 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

Code Block

language	powershell

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 in the Data Loader Reference.

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. Previously in the section Set up Connection we created variables for these settings. The way to use this variables is as follows:

Data load command

Code Block

language	powershell

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

Code Block

language	powershell

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

Code Block

language	powershell

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

Code Block

language	powershell

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

Code Block

language	powershell

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 facets

Squirro items can have keywords. These keywords are used for facet 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 facets in Squirro.

facets.json

Code Block

language	js

{
    "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

Code Block

language	powershell

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 facet 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: Facet data types

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

facets.json

Code Block

language	js

{
    "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

Code Block

language	powershell

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 facet. Numeric facets 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

Code Block

language	xml

<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 facets file remains unmodified.

facets.json

Code Block

language	js

{
    "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

Code Block

language	powershell

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 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 facet configuration is extended. This now contains a new facet "Duration (Hours)" which uses the float data type.

facets.json

Code Block

language	js

{
    "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

Code Block

language	js

{
    "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

Code Block

language	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

Code Block

language	powershell

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 custom data source is covered in Example data loader plugin.

Conclusion

This concludes the tutorial - a whirlwind tour through the main features of the Squirro data loader.

For detailed documentation, please refer to the following documentation pages:

Data Loader Reference: a full reference of the command line arguments.
Data Loader Facet Config Reference: a reference of both the facets.json and pipelets.json files.
Data Loader Examples: additional examples on how to use the data loader.

This tutorial goes step by step through using the Data Loader. This is first done by uploading a CSV file to Squirro. In the last step a custom source script is then implemented to implement a proprietary data format.can now be found at Data Loader Command Line Interface Tool Tutorial on the Squirro Docs site.

Versions Compared

Old Version 9

New Version Current

Key

Table of Contents

Setup

Installation

Folder Setup

Download Example Data

Set up Connection

Setting variables

CSV Import

Step 1: Empty Invocation

Data load command

Step 2: Provide Configuration

Data load command

Data load command

Step 3: Select source type and source file

Data load command

Step 4: Data mapping

Data load command

Step 5: Map additional data

Data load command

Step 6: Configure facets

facets.json

Data load command

Step 7: Facet data types

facets.json

Data load command

Step 8: Templating

template-body.html

facets.json

Data load command

Step 9: Pipelets

facets.json

pipelets.json

duration_conversion.py

Data load command

Custom Data Source

Conclusion