The VtrinLib ODBC driver enables access to classes provided by a VtrinLib connection by using the SQL language via the ODBC API. The driver conforms to the ODBC 3.80 specification and covers the Minimum SQL grammar and very much of the Core and Extended grammars.

In this article we cover:

• installing the ODBC driver
• configuring the ODBC data source
• connecting to the database using ODBC
• We cover how to use the GraphFetch data access using ODBC and provide examples on how to manipulate the database contents.
• Section Data analysis provides examples on how to query data for analysis using Python and R.

Installation

The VtrinLib ODBC Driver can be installed to a Windows computer that has .NET Framework 4.7.2 or later installed. There are separate installation kits for 64-bit and 32-bit programs. For a 64-bit computer and operating system, both can be installed to support both 64-bit and 32-bit programs. The ABB Ability™ History product, from version 5.0 and up, also installs the VtrinLib ODBC Driver. The installation package resides in the VtrinLibODBCDriver directory in the ABB Ability™ History installation media. The installation kit can be used for installing the driver to separate computers.

The driver can be installed by double-clicking the installation .msi file (either VtrinLibODBCDriverSetupx86.msi or VtrinLibODBCDriverSetupx64.msi). To install the driver silently in a command script, execute the command:

msiexec /i "%msifile%" /qn

Where %msifile% is the full path to the installation kit .msi file (either VtrinLibODBCDriverSetupx86.msi or VtrinLibODBCDriverSetupx64.msi). Remember that msiexec is a Windows GUI program, so the command returns immediately and does not wait for completion if executed from a command line. If you want to wait for completion, prefix the command with start /wait. (This is not needed if you use bat scripts).

Upgrading

Upgrading an existing installation can be done by either first uninstalling the software from the Windows Programs and Features control panel, or by executing the installation with the command:

msiexec /i "%msifile%" REINSTALL=ALL REINSTALLMODE=vomus

A bat script that either silently installs or upgrades the driver looks as follows:

msiexec /i "%msifile%" /qn /l*xv "%logfile%"
if %errorlevel% EQU 1638 (
   echo UPDATING
   msiexec /i "%msifile%" REINSTALL=ALL REINSTALLMODE=vomus /qn /l*xv "%logfile%"
)

The above example also puts a diagnostic log of the installation to a log file (%logfile% is the full path to the file).

Configuration of the ODBC data source name

Use the Control Panel ODBC application for defining the ODBC data source names (or other appropriate generic ODBC tools, such as odbcconf.exe). The name of the driver is "VtrinLib ODBC Driver" (the 32-bit and 64-bit versions have the same name). The configuration settings are:

• Data Source Name: this is the DSN to be defined. There are some restrictions on the length of the name and the characters that can be used in it.
• Description: this is a descriptive text to be stored with the definition. The ODBC driver does not use it.
• Location: this is the Vtrin DSN string. For example wss://server/history when connecting to RTDB 5.0 by using the wss protocol; or tcp://server/server-rtdb for connecting to RTDB 4.6 by using the tcp protocol; or D:\RTDBData for connecting to the local RTDB database by using VtrinLib. (Note: the "VtrinLib dir" must be set as RTDB in this case, and the RTDB version must be compatible with the VtrinLib ODBC Driver version.)
• VtrinLib dir: this should usually be left empty. It means that the ODBC driver uses the VtrinLib.dll version that was included in the ODBC Driver installation (to the subfolder named A under the installation directory, for example: C:\Program Files\ABB\VtrinLibODBCDriver\A). To use another copy of VtrinLib.dll, specify a full directory name, or a subdirectory of the installation directory. Environment variables can be used in the string by using the syntax %name%. The special setting RTDB means the VtrinLib.dll version that was installed with the RTDB installation to the local machine. Note: It is not possible to use different VtrinLib instances concurrently in the same process. Note: If you have an RTDB development environment in your process (the driver checks this with the existence of the RTDBWork environment variable that the environment always uses), the driver will use the development version of VtrinLib instead, unless you have explicitly defined the VtrinLib dir in the data source name setting.

32/64-bit considerations: The ODBC technology means that a DLL (ODBC Driver) must be loaded to the address space of the process. This means that correct bitness of the DLL must be used. If you have 32-bit programs, you need to install (also) the 32-bit version of the ODBC driver, and you need to use the "ODBC Data Sources (32-bit)" control panel application for defining the data source names. For the 64-bit applications, you use the "ODBC Data Sources (64-bit)" control panel application. However, when you define a user data source name, the same definition works for both 32-bit and 64-bit processes. But if you define a system data source name, you need to create the same DSN with both 32-bit and 64-bit versions.

Connecting to database

After you have defined the ODBC data source name, you can connect to the database using that name. The username and password settings are also in use (which is typical for ODBC drivers). If you omit the username and password, the connection tries to use the current credentials of the process (this is true for the VtrinLib netclient connections, but it may vary if you use some other Vtrin Driver). The Vtrin NetClient also supports getting the username and password from the Windows stored credentials (Windows Vault), in which case you pass the username in the format "VAULTNAME" and password as "". The connection can also be created without the creation of a DSN. In this case, the application must be able to use the "SQLDriverConnect" API which takes a connection string instead. The connection string keywords are:

• Driver = Provide the value "VtrinLib ODBC Driver" for this.
• DSN = the ODBC standard data source name keyword. This can be omitted if the Driver keyword was used.
• UID = the user name. This can also be in the format "_name", where name is the name in Windows Saved Credentials (The "Windows Vault"). The string "GUEST" means anonymous logon with ws or wss protocols, and an empty string means the saved credentials (if found) or current credentials. The saved credentials mean the credentials that were stored with Vtrin user interface when connecting to the same location and using its "Save Credentials" function.
  • If the connection flag mask 16 has been provided (CF=16), the behavior changes to such that the ODBC driver passes the provided username and password directly to the VtrinLib connect function. However, in this case the null username and password cannot be passed, and they are passed as empty strings instead. In this mode, an empty or missing UID string is the anonymous login and "KERBEROS" (with exactly these case-sensitive letters) means the current credentials.
  • If the connection flag mask 8 has been provided (CF=8), the passed username and password are ignored, and only the saved credentials are used. If there are no saved credentials, the connection will fail to "invalid username or password" (unless the Vtrin database driver does not need any username authentication).
• PWD = the plain text password. If you do not want to write the password to the connection string, you can either use the vault format, or if the process is interactive, you can let the Vtrin library ask the password by providing the connection string setting CF=4
• Location = the Vtrin DSN string, such as wss://127.0.0.1/history
• DSF = data store flags. An integer bitmask for diagnostic purposes. The value 1 means that the driver will return callstack information in ODBC error messages that originate from the VtrinLib level. The mask value 2 means to use AutoFlush for the TraceListenerFile (see below). Value 64 is for troubleshooting purposes. It disables the passdown optimization of where predicates to VtrinLib level completely. (Version info: 5.3_25.12 for mask 2 support).
• LOGLEVEL = Logging level for debugging purposes. Possible values are:
  • 0 or Off
  • 1 or Fatal
  • 2 or Error
  • 3 or Warning
  • 4 or Info
  • 5 or Debug
  • 6 or Trace
• When the LOGLEVEL is defined, the driver writes a text file VD_connection_*.log to the current directory. It contains debugging information that may be useful when troubleshooting errors with the product support.
• TRACE = trace setting for debugging purposes. When LOGLEVEL=Trace has been defined, the TRACE=1 setting enables additional events for which tracing is displayed. This enables, for example, displaying the Vtrin query predicate that was used for reading the instance set. See also the DSF setting above.
• TRACELISTENERFILE = Defines a trace listener file for storing the diagnostic messages from the VtrinLib level. Existing file will be overwritten during connection time.
• VTRINLIBDIR = The directory that contains the VtrinLib.dll file to be used (see the description in section Configuration of the ODBC data source name above).
• CF = Connection flags. Integer bitmask setting that defines the connection flags to be used when connecting to the Vtrin data source:
  • 1 = enables the "AcceptServerKeyChanges" (this is useful only with the tcp protocol or with wss when self signed certificates are used and when the target server has been reinstalled and a new encryption key has been generated).
  • 2 = do not use the "AcceptNewServerKeys" setting in the connection which is otherwise used by default.
  • 4 = Interactive Vtrin logon (the Vtrin logon dialog is displayed if the connection is not possible without it).
  • 8 = Use the credentials that have been saved by Vtrin for this data source.
  • 16 = Empty username means anonymous user.
• LONGTEXTSIZE = This can be used to change the maximum size of long text columns. The default value is 20000. A smaller value may need to be used with some applications if they refuse to use this large maximum size.
• BLOBSIZE = The size to be returned for blob columns. By default, the max integer is returned as the size. However, some applications may not recognize this and may try to allocate the maxint value of memory, in which case this setting can be used for forcing a smaller value.
• CONFIG = A string for configuring the behavior of the driver. This is either a json text directly, or reference of json text file given with "@filepath", where the filepath can contain environment variables with syntax "%name%". See below for the description of the configuration settings. (Version info 5.3_24.04).

See section praox usage below for examples on how to use connection strings and saved credentials.

Config string

The description of the "Config" connection string setting is given in here.

Notice that if the config JSON text is given directly in the connection string (and not as reference to file), the whole value must be placed to extra braces, and the right braces in the must be duplicated. For example:

"config={{TableSettings : [{ColumnNameStyle:1}}]}}}"

Example json text file:

{
  "IgnoreUnknownConfigSettings" : true,
   "TableSettings" : 
   [
      {
         "Tables": "OPCEvent,OtherEvent*",
         "Tables_Regex": "MyStuff|^OPCEvent$",
         "ColumnNameStyle": "1",
         "MaxCols" : 255,
         "Tgt": false,
         "Raw": false
      }
   ]
}

Note: All property names in the configuration are case sensitive. The property names can be written also without quoted strings (it is recommended to use quotes in the json files, but the quotes are difficult if given directly in the connection string).

Detailed description of the settings:

Notice that the connection string settings can also be given in ODBC data source setting in Windows Registry setting of the DSN, such as "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MyDSN". However, the Config string still must be given as single line in the registry (REG_MULTI_SZ -type is not supported either).

Accessing data

The VtrinLib ODBC Driver presents the Vtrin classes as SQL tables. To enumerate the available tables, use the SQLTables API or execute the statement "SELECT Id from Class". In addition to the contained classes, the driver presents the virtual table GraphFetch that wraps access to the VtrinLib FetchGraphData API. This makes the Vtrin filters available for the SQL level.

The "TGT:" columns

The driver presents the RAW property values as the column values. Some columns also use the formatted variant of the value that, for example, contains the enumerated string for the Enum data types. The driver presents this value, too, by using another column with the name prefix "TGT:". Warning: avoid using "TGT:" columns in general-purpose scripts or programs, because the values may depend on the language setting of the user etc.

DateTime columns (UTC and Local)

For clarity (and for compatibility with the RTDB ODBC Driver), the driver uses the suffix UTC for the_DateTime columns to make it clear that it uses the UTC time zone. The local time is available in a separate column that has the prefix "TGT:" in it.

TimeSpan columns

The driver presents the TimeSpan type as the ODBC 3.0 data type SQL_INTERVAL_DAY_TO_SECOND with 7 digits in the fraction of second and up to 8 digits in the day value (the actual TimeSpan max day value is 10675199). The ODBC driver presents the value in character binding in the ODBC standard format, for example "0 00:00:10.0000000" for 10 seconds, and "2 00:00:00.0000000" for two days, and "-0 00:00:10.0000000" for -10 seconds. (Version info: TimeSpan support was added to version 1.1/2017-06-09)

Presenting varying type data

If a property of the Vtrin class may contain different data types, the VtrinLib ODBC driver presents the column as WVARCHAR data type by using a type tag prefix. For example, the string "(Int32)10" means a 32-bit signed integer type with the value 10. The possible type tags, and their corresponding .NET types (all in the System namespace) are:

The type tag is case-insensitive. Array data values are enclosed in curly braces and separated with a comma. Period is always used as the decimal separator. The ODBC standard timestamp format is used and the presented time is in the UTC time zone.

GetInstanceSet result truncation

The ODBC driver uses the VtrinLIb GetIntanceSet API to fetch the requested rows. The driver tries to provide the mask value for the function from the specified WHERE predicate as closely as possible. It also provides the given TOP or LIMIT counts to count parameter if possible. The RTDBDriver limits the amount of returned rows or amount of skipped rows according to the configuration settings in SimpleConfig (see "RTDB Driver for VtrinLib" in cpmPlus wiki for details how to adjust the MaximumMaskSkipCount and MaximumPostProcessSet parameters). If the limit is exceeded, the ODBC driver will return an error level result from the SQLFetch call after the last returned row. The error message contains string "GetInstanceSet result was truncated". (Version info: Before 5.3_25.08 the truncation error was silently ignored).

Using GraphFetch

The driver presents the virtual table GraphFetch that wraps access to the VtrinLib FetchGraphData API. This makes the Vtrin filters available for the SQL level. The GraphFetch query supports providing one element in the parameter array to the Vtrin FetchGraphData API (i.e. one instance of cGraphFetchParameters). The parameters of the query are provided with virtual columns in the WHERE predicate of the query, by using column names "gf_*". The input columns and their data types are:

• gf_type WVARCHAR This corresponds to the "type" parameter for FetchGraphData. This column is for legacy driver support only. It should NOT be needed with current drivers. Instead, all features that it provided (such as Plot filter and Rep filter) are now defined with the gf_Filter setting.
• gf_cls WVARCHAR The class name to query. The default is 'ProcessHistory'
• gf_masking WVARCHAR The masking string that selects what data to query. For example: History=''CurrentHistory'' AND Variable=''SYS_CPU0_TIME_1'' The time range is often omitted from the query but instead it is passed with the "gf_startxvalue" and "gf_endxvalue" parameters and providing gf_appendxlimitstomask = 1. However, if the times are desired to be provided, the syntax is: ... AND Time>>''2015-06-17 06:00:00'' AND Time<<''2015-06-30 10:10:00'' (The >> and << operators were used above to also select one out of range).
• gf_xvaluepropertyname WVARCHAR The property name for the xvalue (for ProcessHistory, the Time of the data is used by default)
• gf_yvaluepropertyname WVARCHAR The property name for the yvalue (for ProcessHistory, the Value of the data is used by default)
• gf_startxvalue gf_endxvalue WVARCHAR The starting and ending x values for query. WARNING: These must be defined when a filter such as AVG1min has been defined (if there is no data, the filter does not find its starting time and may use the minimum time instead). The filters treat this range as the graph range in the x-dimension (and it corresponds to the gf_plotareaWidth in case of the PLOT filter). If gf_appendxlimitstomask is 1, this is also used to define the selected (time) range of the data. If gf_Filter has been defined, the limit is treated "one out of range". The value uses the type tagged data representation (for example: '(DateTime)2015-06-25 03:00:00')
• gf_startyvalue gf_endyvalue WVARCHAR The starting and ending y values for query. The value uses the type tagged data representation. The range corresponds to the gf_plotareaHeight in case of the PLOT filter.
• gf_filter WVARCHAR The filter string for the query. For example 'AVG10min' calculates 10 minute averages from the data. NOTE: when you provide the gf_filter setting, you must also provide gf_startxvalue and gf_endxvalue settings so that the query knows the time range to be returned (and then it is handy to provide also gf_appendxlimitstomask = 1 so that the same limits go to the mask of the queried source data also). See Filters for a description on how the filters are used. In addition, there are filter settings that are not documented in the user documentation, because they are only used internally by the user interface application. The internal filter definitions are PLOT = the plot filter, and PLOTM = the plot filter with markers. Some internal filter types can be specified, but they may not be useful. For example, the MINMAXFIND filter enables the filling of the SmallestYValueInResultSet and LargestYValueInResultSet return values, but the ODBC driver does not provide them as its result set.
• gf_rawmax INTEGER Limits the amount of raw data that is queried for the data. By default, the setting is NULL, which means that the limit is Vtrin driver specific (for example, the RTDBDriver uses a limit of 300000)
• gf_questionablelimit DOUBLE When Vtrin filter is used for calculating averages, this defines the proportion of how much of the data must be valid in order for the result not to be regarded as questionable.
• gf_validitylimit DOUBLE When Vtrin filter is used for calculating averages, this defines the proportion of how much of the data must be valid in order to have the result regarded as valid.
• gf_appendxlimitstomask BIT If this is used, the query appends the passed gf_startxvalue gf_endxvalue range to the masking value
• gf_appendylimitstomask BIT If this is used, the query appends the passed gf_startyvalue gf_endyvalue range to the masking value
• gf_limittype INTEGER Changes the query to fetch limit values instead (such as "High Warning Limit"), as supported by some Vtrin drivers. For example, when using gf_limittype=4 with RTDB driver, the query returns the high warning limit value for the variable from the EventEngineering table. If the EventEngineering table uses a reference variable to define the limit, the returned data is not completely constant. For a list of limit types, see the 'Enums' section and the 'Chart Limit Types' key in the UIString class. For example, when executing the query: select subkey,text from uistring where langid=65537 and section='Enums' and "Key"='Chart Limit Types' order by convert(subkey,sql_integer), the default is -1 which means that the limittype fetch is not in use.

The result columns of the GraphFetch query are the columns with the name prefix "gd_" (for_GraphData_) or "gi_" (for_GraphInfo_). Only the most often used columns are described below. The columns are:

• gd_AccessDenied BIT
• gd_AlarmLevel INTEGER
• gd_Bad BIT
• gd_ChangeDirection INTEGER
• gd_Disabled BIT
• gd_EndOfSlice BIT
• gd_Extrapolated BIT
• gd_Fake BIT
• gd_FormattedLongTime WVARCHAR
• gd_FormattedStatus WVARCHAR
• gd_FormattedTime WVARCHAR
• gd_FormattedToolTip WVARCHAR
• gd_FormattedValue WVARCHAR
• gd_FormattedValueWithUnit WVARCHAR
• gd_Frozen BIT
• gd_HistorySubstituted BIT
• gd_Interpolated BIT
• gd_Invalid BIT True if the status indicates that the value is invalid.
• gd_LastInSequence BIT
• gd_ManuallySet BIT
• gd_NonAckAlarm BIT
• gd_Producer INTEGER
• gd_Questionable BIT True if the status indicates that the value is questionable.
• gd_Representativeness REAL The proportion (0.0 to 1.0) of how large a portion of the period the value was valid for.
• gd_SourceDescription WVARCHAR
• gd_StartOfSlice BIT
• gd_Status BIGINT
• gd_StatusReasonResolved INTEGER
• gd_StatusResolved INTEGER
• gd_Substituted BIT
• gd_SuppressedAlarm BIT
• gd_TimeUTC TIMESTAMP The gd_XValue if as a TIMESTAMP type. This can be used if you know that the x value type is timestamp.
• gd_Uninitialized BIT
• gd_UserStatus INTEGER The user status value. For example the RTDB OPC Client stores the OPC quality to the user status by using some coding.
• gd_Value WVARCHAR The y-value for the result. The value uses the type tagged data representation (for example: '(DateTime)2015-06-25 03:00:00')
• gd_Value_float DOUBLE The gd_Value converted to double precision floating point type. Use this if you know that the result is double type or convertible to double.
• gd_XValue WVARCHAR The x-value for the result. The value uses the type tagged data representation (for example: '(DateTime)2015-06-25 03:00:00')
• gd_YValue WVARCHAR This is the same as gd_Value in all current Vtrin drivers.
• gi_PeriodBase TIMESTAMP
• gi_PeriodBaseO WVARCHAR
• gi_QuestionableLimit DOUBLE
• gi_ValidityLimit DOUBLE
• gi_PeriodLengthO WVARCHAR
• gi_XValueUnit WVARCHAR
• gi_YValueUnit WVARCHAR

praox usage

ABB Ability™ History installs an ODBC executor program called praox.exe to the path %RTDBRoot%\bin or %RTDBRoot64%\bin. You can copy that executable to computers where the vtrinLib ODBC Driver is installed, if you do not have other ODBC applications that you want to use instead. The same program actually comes in different variants:

• praox.exe 32 and 64-bit versions of the program that reside in different folders. However, the ABB Ability™ History installation only installs the 64-bit version if the operating system version is 64-bit.
• praotstxw.exe 32-bit version of the program (same as the 32-bit praox.exe but with a different name). This program is also available in older RTDB versions.
• praotstxw64.exe 64-bit version of the program (same as the 64-bit praox.exe but with a different name).

Here is a simple example that connects to the database and executes one SQL command: praox mydsn -user u -pass p -sql "select name from variable" You can also put the SQL statements to a script file and terminate the statements with a semicolon that must be at the end of the line (even no spaces are allowed after that). For example, put the following to a file named a.sql:

select name from variable;
select "TGT:DisplayName",Value from currentvalue;

And then execute it with command: praox mydsn -script a.sql

The "mydsn" is the data source name that was defined with the ODBC control panel. You can connect to the Vtrin data source also without a data source name by using the SQLDriverConnect method, for example:

praox -constr "Driver=VtrinLib ODBC Driver;Location=tcp://myserver/myserver-rtdb;uid=u;pwd=p"

The username and password do not need to be provided if the current credentials are valid for the connection. If the target server is not in the same domain, you can enter the domain credentials of the remote computer by using the Credential Manager of Windows, and adding an entry to the "Windows Credentials" section by using the name of the remote server (for example "myserver" in the case of the above example). If the saved domain password cannot be used for some reason, another possibility is to use saved generic credentials, either saved by the Vtrin user interface or by using Windows Credential Manager. When you use Windows Credential Manager, you can save the entry with any name, and then pass the name as the username with the syntax "name". If you used Vtrin to store the credential, it saved it with the name VTRIN/datasourcestring. In this case, you can connect to the database with the VtrinLib ODBC driver convenience connection flag cf=8, which passes the Vtrin data source string prefixed with "" to the username in the connection. Example: First connect to the data source with cf=4 so that the Vtrin logon dialog appears, because the username and password was not valid:

praox -constr "Driver=VtrinLib ODBC Driver;Location=tcp://myserver/myserver-rtdb;uid=xxx;cf=4"

Then enter the username and password and check the "Save Credentials" in the dialog box and press ok. Next time, you can connect to the data source by using the connection string:

praox -constr "Driver=VtrinLib ODBC Driver;Location=tcp://myserver/myserver-rtdb;cf=8"

If you want to use a named ODBC DSN instead, it is not possible to set the CF setting by using the ODBC control panel. However, you can enter the setting to the registry directly by using registry editor. For example, if you defined a user DSN with the name mydsn, enter the setting to registry key: HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\mydsn as a string value name "cf" and value "8"

Examples

GraphFetch examples

From equipment history

Selecting history data from EquipmentHistory for the selected time range:

select gd_TimeUTC,gd_Value_float,gd_Invalid from GraphFetch WHERE 
gf_cls='EquipmentHistory' and 
gf_startxvalue='(DateTime)2020-02-26 11:01:00' and 
gf_endxvalue='(DateTime)2020-02-26 11:02:00' and 
gf_appendxlimitstomask=1 and
gf_masking='Path=''Example site.Water transfer system.Tank area.Target tank'' AND Property=''Level''';

From raw history

Selecting raw history data from CurrentHistory for the selected time range:

select gd_TimeUTC,gd_Value_float,gd_Invalid from GraphFetch WHERE 
gf_startxvalue='(DateTime)2015-09-07 08:00:00' and 
gf_endxvalue='(DateTime)2015-09-07 09:00:00' and 
gf_appendxlimitstomask=1 and
gf_masking='History=''CurrentHistory'' AND Variable=''SYS_CPU0_TIME''';

Result:

                      gd_TimeUTC           gd_Value_float gd_Invalid
     --------------------------- ------------------------ ----------
  1: 2015-09-07 08:00:00.0093442        0.470023983693013          0
  2: 2015-09-07 08:00:20.0096002           5.938703984589          0
  3: 2015-09-07 08:00:30.0097282        0.313775983667408          0
  ...   

The same but now also one sample before and after the time range are returned.

select gd_TimeUTC,gd_Value_float,gd_Invalid from GraphFetch WHERE 
gf_masking='History=''CurrentHistory'' AND Variable=''SYS_CPU0_TIME'' and 
Time>>''2015-09-07 08:00:00'' AND Time<<''2015-09-07 09:00:00''';

Result:

                      gd_TimeUTC           gd_Value_float gd_Invalid
     --------------------------- ------------------------ ----------
  1: 2015-09-07 07:59:50.0092162        0.470023983693013          0
  2: 2015-09-07 08:00:00.0093442        0.470023983693013          0
  3: 2015-09-07 08:00:20.0096002           5.938703984589          0
...
338: 2015-09-07 09:00:00.0209278         1.71732666096445          0
Total 338 rows

Reducing the amount of returned rows to conform with the desired resolution in the X and Y directions using PLOT Filter.

Using gf_appendxlimitstomask to automatically use the time range from the gf_startxvalue and gf_endxvalue settings, (in which case the one out of range setting is also automatically in effect).

select gd_TimeUTC,gd_Value_float from GraphFetch WHERE 
gf_filter='PLOT(100,100)' and
gf_startxvalue='(DateTime)2015-09-07 08:00:00' and 
gf_endxvalue='(DateTime)2015-09-07 09:00:00' and 
gf_appendxlimitstomask=1 and
gf_masking='History=''CurrentHistory'' AND Variable=''SYS_CPU0_TIME'''

Result:

                      gd_TimeUTC           gd_Value_float
     --------------------------- ------------------------
  1: 2015-09-07 07:59:50.0092162        0.470023983693013
  2: 2015-09-07 08:00:00.0093442        0.470023983693013
  3: 2015-09-07 08:00:20.0096002           5.938703984589
...
278: 2015-09-07 09:00:00.0209278         1.71732666096445
Total 278 rows

Calculate 10 minute averages on the fly.

SELECT gd_TimeUTC,gd_Value_float,gd_Representativeness FROM GraphFetch WHERE
gf_filter='avg10min' and
gf_cls='ProcessHistory' and
gf_appendxlimitstomask=1 and
gf_startxvalue='(DateTime)2015-09-11 04:00:00' and
gf_endxvalue='(DateTime)2015-09-11 05:00:00' and
gf_masking='History=''CurrentHistory'' AND Variable=10236'
              gd_TimeUTC    gd_Value_float gd_Representativeness
    -------------------- ----------------- ---------------------
  1: 2015-09-11 04:00:00                 0                     0
  2: 2015-09-11 04:10:00                 0                     0
  3: 2015-09-11 04:20:00                 0                     0
  4: 2015-09-11 04:30:00  6.70547147577584             0.8166056
  5: 2015-09-11 04:40:00  21.6950035269888                     1
  6: 2015-09-11 04:50:00  25.1461419947438                     1
  Total 6 rows

Fetching multiple variables with same query: Currently this is not possible. You need to execute the query for each variable separately.

From OpcEvents

Fetching the events from OPCEventlog table for the given time range.

Now the OPC Event from vtrinlib ODBC appends all the Vendor specific attributes as a column to the table in addition to the standard attributes, so its recommended to give the exact column name instead of a '*' .

select  "TGT:EventTime", SourceServer, Message from OpcEvent where "TGT:EventTime" >= {ts '2023-09-14 01:00:00.000'} AND "TGT:EventTime" <= {ts '2023-09-14 01:01:00.000'} ORDER BY "TGT:EventTime"

Result:

                 TGT:EventTime 				SourceServer                     		Message
      --------------------------- 		-------------------------------- 		--------------------------------
   1: 2023-09-14 01:00:01.7369083 		opc.tcp://in-v-cpml409:48031     		Expected Voltage should be <= 240 Recorded Voltage is :: 248
   2: 2023-09-14 01:00:04.4103761 		opc.tcp://in-v-cpml409:48031     		Expected Voltage should be <= 240 Recorded Voltage is :: 240
   3: 2023-09-14 01:00:04.4105363 		opc.tcp://in-v-cpml409:48031     		Expected Voltage should be <= 235 Recorded Voltage is :: 240
   4: 2023-09-14 01:00:05.4719115 		opc.tcp://in-v-cpml409:48031     		Expected Voltage should be <= 240 Recorded Voltage is :: 261
   5: 2023-09-14 01:00:07.7844438 		opc.tcp://in-v-cpml409:48031     		Expected Voltage should be <= 240 Recorded Voltage is :: 253
   6: 2023-09-14 01:00:08.3171903 		opc.tcp://in-v-cpml409:48031    		Expected Voltage should be <= 235 Recorded Voltage is :: 240
   7: 2023-09-14 01:00:09.5343366 		opc.tcp://in-v-cpml409:48031     		Expected Voltage should be <= 240 Recorded Voltage is :: 264

Creation of a new RTDB Variable

insert into Variable(Name,DiscreteValue,Description) values('MY_NEW_VARIABLE',1,'VtrinLib ODBC Documentation example variable');

Creation of a new RTDB Tag

insert into Tag(ProposedName, DaPath, DaFrequency, Activity, ValueMax, ConsistencyControlled, "TGT:CollectorNode") 
values ('MY_NEW_TAG', 'opcda:///ABB.RtdbOpcDaServer.1/Variable.RTDB_SECOND_OF_HOUR.Value', 5000, 1, '(double)60', 1, 'FI-DB-REL-R');

Warning: The Tag class does not have a unique index for the proposed name and it generates a new tag for each INSERT, unless you also specify the Id column as a GUID, in which case the INSERT fails if the same Id already exists.

In a special use case, when you want to create a tag for an already existing Variable, you need to insert it to the table TagRTDBInternal instead.

Updating multiple variables

update variable set processarea=1, "tgt:path"='Path1' where variablename like 'MY_NEW_%';

Note: The current (1.0) version of the driver commits each row separately, and does not utilize the multi-commit feature of VtrinLib.

Deleting multiple tags

delete from tag where proposedname like 'MY_NEW_%';

Command classes

To execute commands that are provided by the VtrinLib command classes, you can use SQL INSERT statements. For example:

INSERT INTO CopyFromDCN("TGT:DCN", StartTime_UTC, EndTime_UTC) values('FI-DB-MAIN-DCN2', { ts '2015-09-23 12:00:00'}, { ts '2015-09-23 13:00:00'})"

Interpreting the Status property of CurrentValue

The VtrinLib CurrentValue class presents the status information only with a unsigned 64 integer bitmask number. C# programs that have access to VtrinLib can interprete this with the following enum definition in namespace ABB.Vtrin:

 [System.Flags]
   public enum cValueStatus : ulong
   {
      OK                   =0x0000000000000000UL,
      UserStatus           =0x00000000000000FFUL,
      ManuallySet          =0x0000000000000100UL,
      Increasing           =0x0000000000000200UL,
      Decreasing           =0x0000000000000400UL,
      StartOfSlice         =0x0000000000000800UL,
      EndOfSlice           =0x0000000000001000UL,
      Interpolated         =0x0000000000002000UL,
      Extrapolated         =0x0000000000004000UL,
      Fake                 =0x0000000000006000UL,
      NoStatus             =0x0000000000008000UL,
      Representativeness   =0x000000000FFF0000UL,
      UserStatusExtended   =0x000000000FFF0000UL,
      Producer             =0x00000003F0000000UL,
      LastInSequence       =0x0000004000000000UL,
      HistorySubstituted   =0x0000008000000000UL,
      Reason               =0x00000F0000000000UL, //4bits reason, 0=No reason, 1=SubstitutedBySource, 2=Uninitialized, 3=BadGroup, 4=UnknownSource, 5=BadBySource, 6=SetByEvent, 7=QuestionableBySource, 8=Bad, 9=Disabled, 10=AccessDenied
      Uninitialized        =0x0000020000000000UL,
      AccessDenied         =0x00000A0000000000UL,
      Frozen               =0x0000100000000000UL,
      Incomplete           =0x0000200000000000UL,
      Substituted          =0x0000800000000000UL,
      SuppressedAlarm      =0x0001000000000000UL,
      Questionable         =0x0002000000000000UL,
      AlarmLevel           =0x0FFC000000000000UL,
      Bad                  =0x1000000000000000UL,
      Disabled             =0x2000000000000000UL,
      Invalid              =0x4000000000000000UL,
      NonAckAlarm          =0x8000000000000000UL
   }

This is difficult to interprete directly in our SQL because it does not have any bitwise support functions. The tools that we can use are the MOD and TRUNCATE functions and division. The good thing is that the MOD function is accurate because it can operate with the UINT64 values and TRUNCATE function is able to use the exact decimal data type. The division operator is more dangerous because it usually means that it calculates the result as double precision value, unless the operand is larger than can fit precisely to a double variable, in which case it uses SQL decimal type instead. Also subtraction and addition operators will switch to work with floating point values when the other operand is UINT64. But we can force it to use exact numeric by introducing enough leading or trailing zeros to the numbers. So this is how the bits can be extracted, naturally in quite inefficient way that burns much CPU time, and also, it depends on the implementation details of the bitfields:

select 
   MOD(Status,256) as UserStatus,
   truncate(MOD(Status,512)/00000000000000000256, 0) as ManuallySet,
   truncate(MOD(Status,1024)/00000000000000000512, 0) as Increasing,
   truncate(MOD(Status,2048)/00000000000000001024, 0) as Decreasing,
   truncate(MOD(Status,4096)/00000000000000002048, 0) as StartOfSlice,
   truncate(MOD(Status,8192)/00000000000000004096, 0) as EndOfSlice,
   truncate(MOD(Status,16384)/00000000000000008192, 0) as Interpolated,
   truncate(MOD(Status,32768)/00000000000000016384, 0) as Extrapolated,
   truncate(MOD(Status,32768)/00000000000000008192, 0) as Fake,
   truncate(MOD(Status,65536)/00000000000000032768, 0) as NoStatus,
   truncate(MOD(Status,268435456)/00000000000000065536, 0) as UserStatusExtended,
   truncate(MOD(Status,268435456)/00000000000000065536, 0) as Representativeness,
   truncate(MOD(Status,17179869184)/00000000000268435456, 0) as Producer,
   truncate(MOD(Status,549755813888)/00000000274877906944, 0) as LastInSequence,
   truncate(MOD(Status,1099511627776)/00000000549755813888, 0) as HistorySubstituted,
   truncate(MOD(Status,17592186044416)/00000001099511627776, 0) as Reason,
   truncate(MOD(Status,35184372088832)/00000017592186044416, 0) as Frozen,
   truncate(MOD(Status,70368744177664)/00000035184372088832, 0) as Incomplete,
   truncate(MOD(Status,281474976710656)/00000140737488355328, 0) as Substituted,
   truncate(MOD(Status,562949953421312)/00000281474976710656, 0) as SuppressedAlarm,
   truncate(MOD(Status,1125899906842624)/00000562949953421312, 0) as Questionable,
   truncate(MOD(Status,1152921504606846976)/00001125899906842624, 0) as AlarmLevel,
   truncate(MOD(Status,2305843009213693952)/01152921504606846976, 0) as Bad,
   truncate(MOD(Status,4611686018427387904)/02305843009213693952, 0) as Disabled,
   truncate(MOD(Status,9223372036854775808)/04611686018427387904, 0) as Invalid,
   truncate(Status/09223372036854775808, 0) as NonAckAlarm
 from CurrentValue;

NOTE: the above includes each and every status bit in the enum, while many of them are not relevant with CurrentValue status.

NOTE: The enum also contains the items Uninitialized and AccessDenied but they are not bitfields but actually mask values for the 4-bit Reason field. The reason values are: 0=No reason, 1=SubstitutedBySource, 2=Uninitialized, 3=BadGroup, 4=UnknownSource, 5=BadBySource, 6=SetByEvent, 7=QuestionableBySource, 8=Bad, 9=Disabled, 10=AccessDenied

The above example was generated with this program.

Data analysis

This section shows typical equipment model related use cases, where equipment data is retrieved to popular data analysis tools. As a prerequisite, the VtrinLib ODBC Driver needs to be installed on the client machine. Read the installation instructions and especially the 32-bit vs. 64-bit part from the Installation section.

📘

The code scripts included in this section are tested with VtrinLib ODBC Driver version 1.0, more precisely with the version named 2017-06-28_cpmPlus_VtrinLibODBCDriver_1.0_SR4_Candidate.

R

"R is a programming language and free software environment for statistical computing and graphics supported by the R Foundation for Statistical Computing. The R language is widely used among statisticians and data miners for developing statistical software and data analysis." (Source: Wikipedia, cited 11.12.2019)

Fetching time series data from the equipment model

The following code sample fetches time series data from a remote or local ABB Ability™ Edge database. To test and run it, replace the appropriate DSN, username and password values into the ODBC connection call.

# author [email protected]

# RODBC is the ODBC library used in this example

if (!require(RODBC))
  install.packages('RODBC')

library(RODBC)

# The preferred way to establish the connection is to not to provide the password in the source code
# To achieve that, specify your connection string and use a selected source for credentials
# Options:
# CF=8 -> select credentials previously stored by Vtrin client (used below)
# CF=4 -> will prompt for credentials in interactive environments like RStudio

# More options are documented in "Connecting to database" section

# Note that the driver name is given exactly as below
# {VtrinLib ODBC Driver} including the curly braces
connectionstring = "Driver={VtrinLib ODBC Driver};Location=wss://[Your Server Name or IP]/[Your DB Name];CF=8"

rtdbODBC <- odbcDriverConnect(connection = connectionstring, "nochange", believeNRows = TRUE,
                  rows_at_time = 100, readOnlyOptimize = TRUE)

# we should now have a working connection…

# There is an alternative way to connect via preconfigured data sources
# A DSN based connection example is provided (and commented out) below
# DSN (Data Source Name) is a string that has an associated data structure
# used to describe a connection to a data source
# This requires configuring a DSN with ODBC administration tools first

# Query for system level ODBC data sources 
# odbcDataSources(type = "system")
# A pre-configured VtrinLib ODBCDriver should be included in the listing if DSN parameter is used

# specify your DSN (in place of ZZZ), user name (in place of XXX) and password  (in place of YYY) below
# Note the escape character in RStudio: ”Domain\Username” is written ”Domain\\Username”  

# rtdbODBC <- odbcConnect("ZZZ", uid="XXX", pwd="YYY")



# Fetch a whole table into a data frame like this
dfEquipment <- sqlFetch(rtdbODBC, "Equipment") # fetch a table from the database in its entirety

# Fetch the results of a query into a data frame like this
EqTypes <- sqlQuery(rtdbODBC, "SELECT * FROM Equipment WHERE (Name like '%Tank%')")

# Let's select the first row only
selectedEqType = EqTypes[1,]

#conversion of equipment types name (column #2)to text
as.character(selectedEqType[,2])

#formulate a query to select the instances of selected type
#Note: equipment names may contain dots, so you must put the "Path_EqType" in identifier literal quotes (the double quotation mark (")).
#Otherwise SQL treats the dot as catalog delimiter and you get an error "Catalogs are not supported for table identifiers."
query_str = paste('SELECT * FROM "Path_',as.character(selectedEqType[,2]), '"', sep="")

#all instances of selected type
dfEqInstances <- sqlQuery(rtdbODBC, query_str)

# One could query all the visible classes / tables from the DB
#dfClasses <- sqlQuery(rtdbODBC, "SELECT Id from Class")

#The names of the instances
View(dfEqInstances[,"DisplayName"])


# Here we select the wanted equipment instances - one for this example
selEqInstances <- dfEqInstances[dfEqInstances$DisplayName == "Tank1",]

# Here we select the desired property
sPropertyName = "Level"

#Let's see the current value
View(selEqInstances[,sPropertyName])


# some time handling follows:

# we can use fixed start time...
fixed_start_time <- as.POSIXct("8/07/2017 00:00:00 AM", format="%m/%d/%Y %H:%M:%S")

# ... or current time minus something
start_time <- Sys.time() - 3600

# select fixed versus previous time range here by (un)commenting
# start_time  = fixed_start_time

#change to UTC based time
attr(start_time, "tzone") <- "UTC" 

#check the start time
start_time

#let's fetch the values until now
endtime = Sys.time()
# in UTC also
attr(endtime, "tzone") <- "UTC" 

# we can now loop over the selected equipment instances and fetch the historized values

#initialize (allows to run this code segment sequentially without manual clearing)
row<-0
df <- data.frame()
newrow <- data.frame()

# the loop for querying time series of selected instances and appending to a result dataframe 
for(row in seq_len(nrow(selEqInstances))){
  
  query <- paste0(
    "SELECT gd_SourceDescription,gd_TimeUTC as Time,gd_Value_float as Value,gd_Invalid FROM GraphFetch 
    WHERE gf_cls='EquipmentHistory' AND gf_appendxlimitstomask=1 
    AND gf_startxvalue='(DateTime)",start_time,"' AND gf_endxvalue='(DateTime)",endtime,"' 
    AND gf_masking='Path=''",selEqInstances$Name[row],"'' AND Property=''",sPropertyName,"'''"
    
  )
  
  newrow <- sqlQuery(rtdbODBC, query) 
  
  if(nrow(newrow)>0)
  {
  
    newrow$Name <- selEqInstances$DisplayName[row]
  
    df <- rbind(df, newrow)
  }
  
}

# select only the Time and Value columns...
myvars <- c("Time", "Value")

#... from the DataFrame object df and assign to another DataFrame called “ts” (for time series)
ts <- df[myvars]

# Plot the time series
plot(ts)

#we can now close the ODBC connection
close(rtdbODBC)

# you are free to play with the data contained in the data frame "ts" ...

Python

The following codes conform to Python 3.x code style and have been tested with python version 3.8.

Fetch all equipment property infos

import pyodbc

# Create the ODBC connection to the selected DB.
# Input your credentials and password instead of XXX and YYY
# and give the proper DSN name instead of ZZZ.
cnxn = pyodbc.connect('DSN=ZZZ;UID=XXX;PWD=YYY',autocommit='TRUE')

# Or use the saved credentials if available on the client machine:
# cnxn = pyodbc.connect('DSN=ZZZ;CF=8',autocommit='TRUE')

# Initialize the cursor.
cursor = cnxn.cursor()

cursor.execute("SELECT DisplayName FROM EquipmentPropertyInfo")

data = cursor.fetchall()
for row in data:
    print(row[0])

Fetch All Equipment Property Info from a remote Data Source using the Python ODBC client.

import pyodbc

# Create the ODBC connection to the selected DB.
# Input your credentials and password instead of XXX and YYY
# and give the proper DSN name instead of ZZZ.
conn_str = 'DSN=DSN_120;' # This is the remote machine DSN source, make sure that DSN is created in the remote machine
conn_str += 'UID=username;PWD=password;' # Remote machine credentails, replace username and password with actual credentails of the remote machine
cnxn = pyodbc.connect(conn_str,autocommit='TRUE')
# Or use the saved credentials if available on the client machine:
# cnxn = pyodbc.connect('DSN=ZZZ;CF=8',autocommit='TRUE')
# Initialize the cursor.
cursor = cnxn.cursor()
cursor.execute("SELECT DisplayName FROM EquipmentPropertyInfo")
eqtypes = cursor.fetchall()
for row in eqtypes:
    print(row[0])

Fetch all equipments

import pyodbc

# Create the ODBC connection to the selected DB.
# Input your credentials and password instead of XXX and YYY
# and give the proper DSN name instead of ZZZ.
cnxn = pyodbc.connect('DSN=ZZZ;UID=XXX;PWD=YYY',autocommit='TRUE')

# Or use the saved credentials if available on the client machine:
# cnxn = pyodbc.connect('DSN=ZZZ;CF=8',autocommit='TRUE')

# Initialize the cursor.
cursor = cnxn.cursor()

cursor.execute("SELECT Name FROM Equipment")

eqtypes = cursor.fetchall()
for row in eqtypes:
    print(row[0])

Fetch all tank instances

import pyodbc

# Create the ODBC connection to the selected DB.
# Input your credentials and password instead of XXX and YYY
# and give the proper DSN name instead of ZZZ.
cnxn = pyodbc.connect('DSN=ZZZ;UID=XXX;PWD=YYY',autocommit='TRUE')

# Or use the saved credentials if available on the client machine:
# cnxn = pyodbc.connect('DSN=ZZZ;CF=8',autocommit='TRUE')

# Initialize the cursor.
cursor = cnxn.cursor()

cursor.execute('SELECT DisplayName, Name FROM "Path_Tank"')

tanks = cursor.fetchall()
for row in tanks:
    print("DisplayName: '" + row[0] + "', Name: '" + row[1] + "'")

Fetching current value from a property of an equipment instance

This Python script reads the current value of an equipment property.

import pyodbc

# Create the ODBC connection to the selected DB.
# Input your credentials and password instead of XXX and YYY
# and give the proper DSN name instead of ZZZ.
cnxn = pyodbc.connect('DSN=ZZZ;UID=XXX;PWD=YYY',autocommit='TRUE')

# Or use the saved credentials if available on the client machine:
# cnxn = pyodbc.connect('DSN=ZZZ;CF=8',autocommit='TRUE')

# Initialize the cursor.
cursor = cnxn.cursor()

cursor.execute("SELECT * FROM Path_Tank WHERE Name='Example site.Water transfer system.Tank area.Target tank'")

columns = [column[0] for column in cursor.description]

sPropertyName = "Level"

# Get the index of the selected property.
propertyindex = columns.index(sPropertyName)

idata = cursor.fetchall()

# Get the current value.
value = idata[0][propertyindex]

print("Current value of property " + sPropertyName + " is %.3f" %(value))

Fetching time series data from a historized property of an equipment instance

In this example, we will read the historized property values, i.e. the time series data, of the same property as in the code sample above. We query the data from a virtual GraphFetch table as seen below. See the GraphFetch examples section for the syntax and more examples.

import pyodbc

# Create the ODBC connection to the selected DB.
# Input your credentials and password instead of XXX and YYY
# and give the proper DSN name instead of ZZZ.
cnxn = pyodbc.connect('DSN=ZZZ;UID=XXX;PWD=YYY',autocommit='TRUE')

# Or use the saved credentials if available on the client machine:
# cnxn = pyodbc.connect('DSN=ZZZ;CF=8',autocommit='TRUE')

# Initialize the cursor.
cursor = cnxn.cursor()


# Next we initialize the changeable parameters for the time series query
# these would be the arguments of a fetch function


# The start time of the fetch range in UTC
start_time = "2017-08-10 00:00:00"
# The end time of the fetch range in UTC
end_time = "2020-08-10 00:10:00"

# The equipment instance (full path name)
eq_instance = "Example site.Water transfer system.Tank area.Target tank"

# The name of the historized property
property_name = "Level"

# Construct the WHERE-part of the queries for getting time series items
# of a selected property for a selected equipment instance.
where_query = "WHERE gf_cls='EquipmentHistory' AND gf_appendxlimitstomask=1 "
where_query += "AND gf_startxvalue='(DateTime)" + start_time + "' AND gf_endxvalue='(DateTime)" + end_time +"' AND "
where_query += "gf_masking='Path=''" + eq_instance + "'' AND Property=''" + property_name + "'''" 

# First get the count of time series items.
queryc =  "SELECT count(*) FROM GraphFetch " + where_query

cursor.execute(queryc)

valuecount = list(cursor.execute(queryc))[0][0]
print('Total number of data points: ' + str(valuecount) + '\n')


# Now get the time series data.
query =  "SELECT gd_TimeUTC, gd_Value_float FROM GraphFetch " + where_query

# See the section "GraphFetch examples" for more usage examples of graph fetch.

cursor.execute(query)

# Fetch the data in batches.
batch_size = 10
batches = 2

for i in range(batches):
	print('Batch #' + str(i + 1) + ':')

	# Fetch the current batch of data points.
	rows = cursor.fetchmany(batch_size)

	for row in rows:
		print(str(row[0]) + "  " + str(row[1]))

	print()

# To fetch all data points you can use cursor.fetchall()
# and to fetch a single data point you can use cursor.fetchone().

What next...

Using the code samples above, you can fetch time series data to your development environment or notebook. The following lists some useful libraries and packages that will help with typical data analysis tasks.

R packages for Data Science

Collecting Data

Feather (beta stage)

• Fast, lightweight, easy-to-use binary format for filetypes
• Makes pushing data frames in and out of memory as simple as possible
• Language-agnostic (works across Python and R)
• High read and write performance (600 MB/s vs 70 MB/s of CSVs)
• For passing data from one language to another in your pipeline

Readr

• Creates a contract for what the data features should be, making it more robust to use in production
• Much faster than read.csv

JsonLite

• Handle JSON data
• Intelligently turns JSON into matrices or data frames

HDF5

• HDF5 is a binary data format designed for flexible and efficient I/O, high–volume and complex data
• Language-agnostic (works across Python and R)

Data Visualization

ggplot2

• You can do faceting and zoom into facets

htmlwidgets

• Reusable components
• Brings the best of JavaScript visualization to R
• Extensive gallery

Leaflet

• Interactive maps for the web
• Nice Javascript maps that you can embed in web applications

Shiny

• Build useful web applications with only a few lines of code—no JavaScript required.
• Shiny applications are automatically "live" in the same way that spreadsheets are live. Outputs change instantly as users modify inputs, without requiring a reload of the browser.
• Shiny user interfaces can be built entirely using R, or can be written directly in HTML, CSS, and JavaScript for more flexibility.

Cleaning & Transforming Data

Dplyr

• Has a collection of very handy joins
• Makes data wrangling much easier

Modeling

MXNet

• Simple deep learning
• Intuitive interface for building deep neural networks in R

TensorFlow

• Now has an interface in R

Python libraries for Data Science

Collecting Data

Feather

• Fast, lightweight, easy-to-use binary format for filetypes
• Makes pushing data frames in and out of memory as simple as possible
• Language-agnostic (works across Python and R)
• High read and write performance (600 MB/s vs 70 MB/s of CSVs)
• For passing data from one language to another in your pipeline

Ibis

• Bridges the gap between local Python environments and remote storages, like Hadoop or SQL
• Integrates with the rest of the Python ecosystem

bcolz (Helps you deal with data that is larger than your RAM)

• Compressed columnar storage
• You have the ability to define a Pandas-like data structure, compress it, and store it in memory
• Helps get around the performance bottleneck of querying from slower memory

HDF5

• HDF5 is a binary data format designed for flexible and efficient I/O, high–volume and complex data
• Language-agnostic (works across Python and R)

Data Visualization

Altair

• Create beautiful and effective visualizations with a minimal amount of code
• Takes a tidy DataFrame as the data source
• Data is mapped to visual properties using the group-by operation of Pandas and SQL
• Primarily for creating static plots

Bokeh

• Interactive visualization library that targets modern web browsers for presentation
• Able to embed interactive visualizations
• Reusable components for the web

Geoplotlib (Interactive maps)

• Clean and simple way to create maps
• Can take a simple list of names, latitudes, and longitudes as input

Seaborn

• Functions for visualizing univariate and bivariate distributions or for comparing them between subsets of data
• Tools that fit and visualize linear regression models for different kinds of independent and dependent variables
• A function to plot statistical timeseries data with flexible estimation and representation of uncertainty around the estimate

Cleaning & Transforming Data

Blaze

• Translates a NumPy / Pandas-like syntax to data computing systems
• The same Python code can query data across a variety of data storage systems
• Good way to future-proof your data transformations and manipulations

xarray

• N-dimensional arrays of core pandas data structures (e.g. if the data has a time component as well)
• Multi-dimensional Pandas data frames

Dask

• Dynamic task scheduling system
• "Big Data" collections like parallel arrays, dataframes, and lists that extend common interfaces, such as NumPy, Pandas, or Python iterators, to larger-than-memory or distributed environments

Tsfresh

• Automatically extracts 100s of features from time series. Those features describe basic characteristics of the time series such as the number of peaks, the average or maximal value or more complex features such as the time reversal symmetry statistic.
• Built-in filtering procedure. This filtering procedure evaluates the explaining power and importance of each characteristic for the regression or classification tasks at hand.

Modeling

Keras

• Higher-level interface for Theano and Tensorflow

PyMC3

• Probabilistic programming
• Contains the most high-end research from labs in academia
• Powerful Bayesian statistical modeling

Scikit Learn

• Simple and efficient tools for data mining and data analysis
• Built on NumPy, SciPy, and matplotlib
• Open source, commercially usable - BSD license

Troubleshooting

Explanation of some error messages