Querying the Historian using a Linked Server in Microsoft SQL Server (version 24)

Configuring the Linked Server

1. Ensure the ODBC Service is installed/licensed on the Canary Historian and the ODBC client is installed on the SQL server.
2. Open Microsoft SQL Server Management Studio and right-click Linked Servers to create a New Linked Server.                                         
3. Name the linked server. In this example, we are using CANARY.
4. Select Microsoft OLE DB Provider for ODBC Drivers from the Provider drop-down list.
5. Set the Data source to CanaryODBCClient.

If the Anonymous provider is disabled within the Identity service, an API token must be used to connect to the Views service. This can be the same token that was used to configure the ODBC client as a System DSN in Step 1.

1. Open the Properties window of the Canary Linked Server.
2. Select the Security page.
3. Select the last connection option: Be made using this security context:
4. Set the Remote login to "apitoken" and paste the token into the With password field.

   

Querying the Historian

Canary offers 9 tables to query against: aggregates, assetinstances, assettypes, assettypetags, currentdata, data, tagproperties, tags, and views.

For a simple test of connectivity, use the following query to return a list of all available aggregates and their descriptions:

select * from openquery(CANARY, 'select * from aggregates')

The ODBC connector was built so that the machine pulling the data from the Historian would not be overloaded with data. In most instances you may need to make a query using the openquery function. This function will help define the parameters and keep the system from bogging down. 

Here is an example requesting an hourly average over a 1-day period using openquery where CanaryHistorian is the machine name, Data Generation is the DataSet, and Random is the beginning of the tag name:

select * from openquery(CANARY,
          'select * from data
          where tag_name like ''HistorianMachineName.Data Generation.Random%''
          and aggregate_interval=''01:00:00''
          and aggregate_id=''TimeAverage2''
          and time_stamp>=''9/26/2021''
          and time_stamp<=''9/26/2021''')