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

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'.
6. Optional - If security is enabled within Views, a username/password can be provided through the 'Security' tab of the Linked Server. (*Note, this user account will overwrite the user account configured in the ODBC Data Source Administrator. See Installing the ODBC Client.)
   
7. Click 'OK'.

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''')