import os
import shutil
import pandas as pd
from seeq import spy

# Set the compatibility option so that you maximize the chance that SPy will remain compatible with your notebook/script
spy.options.compatibility = 193

# Log into Seeq Server if you're not using Seeq Data Lab:
spy.login(url='http://localhost:34216', credentials_file='../credentials.key', force=False)

spy.workbooks

The spy.workbooks module provides functions for importing and exporting workbooks. A workbook is either a Workbench Analysis (colored green in the Seeq user interface) or an Organizer Topic (colored blue).

This functionality is helpful to:

  • Move content between two Seeq servers

  • Manage content by exporting and committing to a version control system like Git

The process typically starts by searching for some content that you have created in Seeq and exporting it. However, since this documentation needs some pre-built content to illustrate how it works, there is a pre-built-and-exported set of workbooks alongside this documentation notebook. So we’ll go in a non-typical order of operations in this example.

The Export Format

When content is exported from Seeq, each workbook is encapsulated in its own folder, including all its worksheets, calculated item definitions and all dependencies, journal/document images and anything else that is necessary to import that data into another server. Content is written to disk as either JSON, HTML or image files as appropriate. References to datasource items are also catalogued during export and default datasource maps are created that facilitate identification of equivalent signals/conditions/scalars on the destination system so that the imported content gets “hooked up” to the right data.

Main Actions

There are five main operations you can perform on workbooks:

  • search for workbooks whose content you want to pull

  • pull those workbooks into Workbook in-memory Python objects

  • save the Workbook Python objects to disk in the export format described above

  • load Workbook Python objects from disk into memory

  • push in-memory Workbook Python objects into a Seeq Server

As mentioned, we’re going to go out-of-order for illustration purposes: load, push, search, pull, save.

Importing

This set of documentation comes with an Example Export folder that contains an Analysis and a Topic for illustration purposes. First we load it into memory:

workbooks = spy.workbooks.load('Support Files/Example Export.zip')
workbooks

Now that the workbook definitions are in memory, we can push them into Seeq.

spy.workbooks.push(workbooks, path='SPy Documentation Examples >> My Import', errors='raise')

The workbooks have been imported into Seeq in a My Import folder with you as the owner. Refresh Seeq Workbench in your browser and take a look.

Exporting

In Seeq Workbench, try changing the name of the Example Analysis workbook to something like My First Analysis Export so that you can tell that your changes get exported.

Now we will search for the workbooks we want to export. The syntax for a workbook search query is very similar to an item metadata search via spy.search():

workbooks_df = spy.workbooks.search({
    'Path': 'SPy Documentation Examples >> My Import'
})

workbooks_df

As you can see, the spy.workbooks.search() command returns a metadata DataFrame with the properties of the workbooks. We can now use that to pull:

workbooks = spy.workbooks.pull(workbooks_df)
workbooks

These are the same type of in-memory Python objects that we had when we executed spy.workbooks.load(). Now we can save them to disk:

if os.path.exists('../My First Export'):
    shutil.rmtree('../My First Export')

spy.workbooks.save(workbooks, '../My First Export')

In the parent folder of this documentation notebook, you’ll find a new My First Export folder that contains similar files to the Example Export folder that’s part of the documentation.

Inspecting Worksheets

With the in-memory Python objects that result from spy.workbooks.pull() or spy.workbooks.load(), you can inspect the worksheets to see what is displayed on them. For example, let’s look at what’s in the Details Pane of the second worksheet of Example Analysis:

worksheet_items = workbooks['Example Analysis'].worksheets['Calculated Items'].display_items
worksheet_items

Now you can call spy.pull() to pull data for the items in the worksheet.

spy.pull(worksheet_items, start='2019-01-01T00:00:00', end='2019-01-02T00:00:00')

Note that if you just wanted the full metadata for the items, you could execute spy.search(worksheet_items[['ID']]).

Manipulating Worksheets

In the example above, you saw how we can inspect the items that are displayed on the worksheet by accessing the display_items attribute on the worksheet object, which is a Pandas DataFrame.

You can make changes by manipulating this DataFrame as shown below. Documentation on the various attributes of a worksheet is available in the spy.workbooks.AnalysisWorksheet online documentation.

workbooks = spy.workbooks.load('Support Files/Example Export.zip')

worksheet = workbooks['Example Analysis'].worksheets['Details Pane']

# Copy this into a variable where each row is indexed by Name for easy manipulation
display_items = worksheet.display_items.set_index('Name')

# Change Area A_Optimizer color to red
display_items.at['Area A_Optimizer', 'Color'] = '#FF0000'

# Change Area A_Temperature line style
display_items.at['Area A_Temperature', 'Line Style'] = 'Short Dash'

# Remove Area A_Compressor Stage
display_items = display_items[~(display_items.index == 'Area A_Compressor Stage')]

# Add Area E_Temperature
area_e_temperature = spy.search({'Datasource Name': 'Example Data', 'Name': 'Area E_Temperature'}).set_index('Name')
area_e_temperature.at['Area E_Temperature', 'Color'] = '#00FF00'
area_e_temperature.at['Area E_Temperature', 'Lane'] = 2
display_items = pd.concat([display_items, area_e_temperature])

# Reset the index since the worksheet expects a Name column, then assign the manipulated DataFrame to the worksheet
worksheet.display_items = display_items.reset_index()
worksheet.display_items

Now push to the server and click the link to observe:

spy.workbooks.push(workbooks, path='SPy Documentation Examples >> My Import', errors='raise')

Re-importing and Labels

If you push a set of workbooks more than once, then by default you will simply overwrite the existing workbooks with the saved content. This can be useful when you are “backing up” content to disk, perhaps for the purposes of version control.

You can choose to push and supply a label, which will create a separate copy of all of the imported items instead of modifying the existing ones. This is useful when you want to import something that you are iterating on prior to affecting the “published” version. For example, let’s push our workbooks with the label of In Development:

workbooks = spy.workbooks.load('Support Files/Example Export.zip')

spy.workbooks.push(workbooks, path='SPy Documentation Examples >> My Development Folder', label='In Development')

If you refresh Seeq Workbench, you’ll notice that there is now a My Development Folder and a separate copy of the Topic and Analysis that is independent of the original – including all calculated items.

Pushing with the same value for the label argument will overwrite the content for that label. Change the label again if you want yet another separate copy.

Mapping Items

Sometimes you will have workbooks that are built upon a set of items (signals, conditions, scalars etc) and you would like to programmatically replace that set of items with a different set. For example, perhaps some of your workbooks are built using tags from an on-premise plant historian and you’d like to to switch to using the enterprise historian in the cloud.

In order to achieve this, you must create a Datasource Map Override folder, populate it with modified Datasource Map files, and then reference it via the spy.workbooks.push(datasource_map_folder=<folder>) argument.

When you pull a set of workbooks and save them to disk, there will be a series of files in the save location that start with Datasource_Map_, one for each datasource that (any of) the workbooks touch. If you want to “re-route” any of the items in those datasources, copy the appropriate datasource map files to a new folder location and then edit them with a text editor. Note that Seeq Data Lab includes an editor:

  • In Advanced Mode, right-click on a file and select Open With > Editor.

  • In Non-Advanced Mode, click the checkbox to the left of the file and select Edit from the top set of action buttons.

Tweaking the Datasource Map

Each Datasource Map file is in the JSON format. Here’s what the Datasource_Map_Time Series CSV Files_Example Data_Example Data looks like:

{
    "Datasource Class": "Time Series CSV Files",
    "Datasource ID": "Example Data",
    "Datasource Name": "Example Data",
    "Item-Level Map Files": [],
    "RegEx-Based Maps": [
        {
            "Old": {
                "Type": "(?<type>.*)",
                "Datasource Class": "Time Series CSV Files",
                "Datasource Name": "Example Data",
                "Data ID": "(?<data_id>.*)"
            },
            "New": {
                "Type": "${type}",
                "Datasource Class": "Time Series CSV Files",
                "Datasource Name": "Example Data",
                "Data ID": "${data_id}"
            }
        }
    ]
}

You will generally start by focusing on the RegEx-Based Maps section. This is a list of mapping directives, each with an Old and a New dictionary. Each key in the dictionary is an item property. In the Old dictionary, the value for each key is a Regular Expression that is used to match on the item property’s value. When spy.workbooks.push() is operating, it runs every item for this datasource through each of the Old sections to determine if they match.

Let’s take the value of Type for the map above: (?<type>.*). Using regex101.com as an explanatory tool, we can copy and paste (?<type>.*) into the Regular Expression text box at the top of its user interface and it will explain what is happening on the far right:

  • A Named Capture Group called type is being defined.

  • The .* matches on any character, any number of times.

This effectively means that the entire value of the Type property is being assigned to the type Named Capture Group (for use later, in the New section). The same thing is being done for Data ID. However, Datasource Class and Datasource Name are being explicitly matched to Time Series CSV Files and Example Data, respectively, and they are not being assigned to a capture group.

Now let’s focus on the New dictionary. The values in this dictionary are used to “look up” an item and use it in place of the item identified by the Old criteria. The default mapping is simple: It just finds items that are a 100% match of the Type and Data ID properties. (You can look at Item Properties for any item in Seeq Workbench by clicking the (i) button to the left of the item in the Details pane.) If you are pulling and pushing within the same server without altering the map, this conveniently means that the old and new items are the same, and nothing changes.

But what if you need to map to a different datasource and you want to use the Name property in a somewhat complex way? Let’s look at a possible example:

{
    "Datasource Class": "OSIsoft PI",
    "Datasource ID": "442A2678-A76F-49C4-A702-4CC494701D6C",
    "Datasource Name": "PIPRDSRV012",
    "Item-Level Map Files": [],
    "RegEx-Based Maps": [
        {
            "Old": {
                "Type": "(?<type>.*)",
                "Datasource Class": "OSIsoft PI",
                "Datasource Name": "PIPRDSRV012",
                "Name": "(?<name>.*)"
            },
            "New": {
                "Type": "${type}",
                "Datasource Class": "ADX",
                "Datasource Name": "PRDDATALAKE",
                "Name": "PIPRDSRV012_${name}"
            }
        }
    ]
}

Here we are mapping from an OSIsoft PI datasource (PIPRDSRV012) to a Microsoft ADX data lake (PRDDATALAKE), and we are using the Name property for mapping. When we are looking up the equivalent tag in the data lake, we are prepending PIPRDSRV012_ to the name, because that is the (fictional) naming convention in this hypothetical data lake.

In this way, you can specify a relatively complex set of rules for mapping. You can have as many Old/New dictionaries in the RegEx-Based Maps list as you want, and SPy will match against the Old dictionary in the order you specify them. You can only use the following properties in the New dictionary: Datasource Class, Datasource ID, Datasource Name, Data ID, Type, Name, Description, Username, Path, Asset.

Importing to a Different Seeq Server

You may wish to copy content to a new/different Seeq Server by exporting and then importing. For example, you might have a development server where you iterate on content and a production server that you publish to when finished.

In order to accomplish this, you’ll do one of two actions:

  • If you’re using the SPy module within Seeq Data Lab, you’ll copy the exported folder to the other version of Seeq Data Lab and then push it from there.

  • If you’re using the SPy module with your own Python set up, you’ll log in to the other server and push it.

The default Datasource Map files that are part of the export may need to be tweaked if the destination server has differently-named datasources. See Custom Datasource Mapping above.

Detailed Help

All SPy functions have detailed documentation to help you use them. Just execute help(spy.<func>) like you see below.

help(spy.workbooks.search)

help(spy.workbooks.pull)

help(spy.workbooks.save)

help(spy.workbooks.load)

help(spy.workbooks.push)