Migrating From SQL V1 to SQL V2 Connection

[Kristopher Wiggins]

Prior to Seeq major version R20, the only way to bring in data coming from a SQL datasource into Seeq was through the SQL Connector V1. From major version R20 and beyond, Seeq created the SQL Connector V2 that features many improvements. For more information, please take a look at SQL Connectors. In order to gain the improvements from the new connector, admins may want to migrate their old SQL V1 connections to V2 and all of the Seeq calculations built from them. The steps below can be used as a rough guide for the steps needed. Please contact Seeq support if attempting to do this as different SQL configurations will require changes to the steps listed below.

 

Step 1: Creating your SQL V2 Connections

One significant difference between the SQL V1 and SQL V2 Connectors is that SQL V1 only connects to the SQL datasource. To actually query information from the SQL datasource, individual Seeq formulas have to be written for each signal or condition being retrieved. The SQL V2 Connector, however, can connect to the SQL datasource and perform a single query to bring in numerous signals and conditions. In order to replicate the queries made from the SQL V1 Connections to SQL V2, the best approach is to create a new query that generalizes the SQL V1 queries rather than copying each individual one. Below is an simple example of a V1 query and its V2 equivalent. Note in the V1 version, 4 different formulas had to be made to retrieve volume, thickness, temperature and cost.  

V1 Query: 

V2 Query: 

 

The SQL V1 and V2 Connectors are separate such that each item can have its own name, description, etc. but still point to the same data. For the purpose of migrating from SQL V1 to V2, it is best to have the signals/conditions brought in from SQL V1 and V2 have either the same names or a small variation that is consistent throughout all items. For example, if the signal made from the SQL V1 connection was titled "Volume From Lab Signals", I should try to make the query made with the SQL V2 connector either replicate that name or have a slight variation such as "Volume From Lab Signals_V2".

 

Step 2: Migrating SQL V1 Items to SQL V2

In this step, we will be leveraging Seeq's command line interface to swap all of the SQL V1 items with their V2 counterpart. Please refer to  CLI Datasource Swapping for more information about datasource swapping. We'll assume here that it is intended to perform this mapping on the same server. 

By default, datasource swapping looks at all of the items available in a datasource and then determines which meet the criteria for swapping. Since we are working with Seeq's internal database that stores calculations, we will have to modify a file on the Seeq server that controls datasource swapping. For a common Seeq install, this file will be located at "C:\Program Files\Seeq Server\pilot\datasource.py". Save a copy of the file outside of the Seeq folder in order to revert the file back to the original once the migration is complete. Depending on your version number, replace the datasource file with the python file shown at the bottom of the post. After download, change the name of the file to be "datasource.py". 

The next step is to perform the map. For this walk-through, we will assume the Seeq database that houses these SQL V1 items is the cassandra database. To verify, look at the item properties on one of the signals/conditions made in Formula that uses the SQL V1 Connection. The cassandra database has a Datasource ID = default and a Datasource Class = cassandraV2. We have to specify a class for the cassandra datasource since its datasource ID is shared by other internal Seeq databases. For the SQL V2 Connector, we will assume it has a Datasource ID=11BB11B1-B1B1-1B11-1BB1-BB11B111BB1B, but this can be found in the connector file. 

If your SQL V1 and V2 items are named the same, you will run a version of the command below to create the map file. If they're not exactly the same but only have a minor variation, you will include a regex parameter. For example, in the previous scenario of adding "_V2" after my SQL V2 items, I would include the  --name-regex "(?<V1Name>.*)" --new-name-regex "${V1Name}_V2" after the command. With the below command, we will map all of the items from the SQL V2 Connector to their cassandra counterpart. Its more likely that there will be more item in the cassandra database than in the SQL V2 Connector so performing the mapping in this manner will save time. If that is not the case, switch the two datasources in the command. The map command will then produce a csv file we can use for the swapping.

seeq datasource map same-server --datasource-id "11BB11B1-B1B1-1B11-1BB1-BB11B111BB1B" --new-datasource-id "00AA00A0-A0A0-0A00-0AA0-AA00A000AA0A" --new-datasource-class "cassandraV2"

If a switch was performed, proceed to the next portion of this step. If not, we'll have to perform the switch in the csv file. Replace the word "Stored" with "Calculated" in the Type column. Cut and paste all of the old columns (Column C through G) from the map csv into the columns following the new columns. Change the column headings such that old becomes new and new becomes old and save the file. 

With this map file, you can then perform the swap across all workbooks or a particular workbook as described in CLI Datasource Swapping.

 

Step 3: Archiving SQL V1 Items

This step is not necessary since the migration is now complete. Sometimes, admins would like to clean up their Seeq server by removing the V1 items that aren't used anymore. You can either archive the items in bulk or individually delete the items.

The first step is to find all of the items that are dependent on the SQL V1 connection. Use the "GET /datasources" endpoint with SQL V1 Connection filters to find the ID of your SQL V1 Connection. You can then use the "GET /items/{id}/dependents" to retrieve the ids of all the items that depend on the SQL V1 Connection.

If you'd like to archive these items, you can use the API reference "POST /signals/batch" or the Seeq SDK equivalent to archive the signals in bulk.

If you'd like to irreversibly delete the items, you can use the API reference "DELETE /items/{id}" endpoint or the Seeq SDK equivalent. This endpoint will need to be ran twice since deletion requires items to have been archived. The archiving is done by the first execution and the second does the deletion.

Lastly the SQL V1 Connection itself will need to be archived. Please refer to Removing a Datasource for more information.

 

Version48andAfter_datasource.py BeforeVersion48_datasource.py

Edited August 24, 2020 by Kristopher Wiggins