OData Access to Data Sets#
Estimated time to read: 7 minutes
OData (Open Data Protocol) is an open standard protocol that enables simple and standardized access to data. Data Platform allows accessing the data in all data sets using this protocol.
Overview#
In this tutorial we describe how OData clients can easily access data in CM MES data sets stored in Data Platform.
Endpoints#
Through Data Platform, all data sets can be accessed using the OData V4 protocol.
If you navigate to the Data Sets page in the Data Platform section, you can see that the data sets are organized in 6 root folders:
Each folder contains different types of data sets:
- Apps - folder for data sets created and used by MES Apps.
- CDM - folder for system data sets containing the Canonical Data Model (CDM) data.
- DWH - folder for data warehouse data sets containing aggregated data.
- IotEvents - folder for data sets associated with iot events.
- ODS - folder for data sets containing MES ODS data.
- UserDefined - folder for data sets manually created by CM MES users.
Each of these folders can be accessed independently via OData. For example, to list all available data sets in the CDM root folder, the OData client should use one of these endpoints:
- Service Document -
https://<hostname>/datamanager/odata/CDM - Metadata -
https://<hostname>/datamanager/odata/CDM/$metadata
To access the data of data set MaterialMovement in the CDM root folder:
https://<hostname>/datamanager/odata/CDM/MaterialMovement
To access the data of data set ResourceLayout in the Resource folder (which is inside the CDM root folder):
https://<hostname>/datamanager/odata/CDM/Resource.ResourceLayout
Thus, to access data sets that are not in root folders, the path to the data set should be built using the dot separator.
Query Options#
The standard OData query options are supported, plus a few extensions:
| Query Option | Description | Example |
|---|---|---|
| $select | Pick the columns to get. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$select=Enterprise_Name,Facility_Name,Area_Name,Step_Name,Material_Name,InProcessPrimaryQty |
| $filter | Filter the data to get. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$filter=contains(Material_Name,'Cookie') and Step_Name eq 'Mixing' |
| $orderby | Columns to order the data. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$orderby=Material_Name asc,Step_Name desc |
| $top | Number of rows to get. If $top is not set, Data Manager will return up to the default maximum number of rows defined by the DATAMANAGER_DEFAULT_MAX_ROWS environment variable (set to 1000 by default). | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$top=50 |
| $skip | Number of rows to skip before returning the result. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$skip=50 |
| $count | Add the total number of rows in the unfiltered data set to the reply. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$count=true |
| $apply | Group and/or aggregate data. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$select=Area_Name,Material_Name,TotalTrackIns,TotalTrackOuts&$apply=groupby((Area_Name,Material_Name),aggregate(TrackInCount with sum as TotalTrackIns,TrackOutCount with sum as TotalTrackOuts)) |
| $query | If the query needs to be sent via POST instead of a GET request (for example, the query is too long to be sent with a GET request), the $query option should be used, and the actual query sent as plain text in the body of the POST request. | https://<hostname>/datamanager/odata/CDM/MaterialMovement?$query, POST request body in plain text: $select=Step_Name,Material_Name&$filter=Material_Name eq 'Cookie01' |
| parameters | If the data set is of type Query and the user-defined query string requires database parameters, they can be sent using the parameters option. For more information, see Data Set Parameters. | https://<hostname>/datamanager/odata/UserDefined/MyDataset?parameters=var1=123,var2='example' |
Note
All the options run server-side, so all OData queries should be as specific as possible to obtain the best performance.
Data Set Parameters#
A data set of type Query can declare database parameters in its SQL SELECT statement, using the syntax {<name>: <datatype>}. For more information, see Create Data Set.
To assign values to those parameters, add the parameters query option to the OData request and separate each parameter with a comma:
https://<hostname>/datamanager/odata/UserDefined/MyDataset?parameters=var1=123,var2='example'
A parameter holds either a flat value or an array of values. Enclose array values in square brackets and separate them with commas:
https://<hostname>/datamanager/odata/UserDefined/MyDataset?parameters=var1=[1,2,3],var2=['example','sample']
Parameter Conversion#
Data Platform converts the value of each parameter into the value that ClickHouse receives when it runs the query. The table below lists every valid pattern and the value it is converted to.
| Parameter Type | Value Sent | Value in ClickHouse |
|---|---|---|
| Valued flat variable | var='xpto' | 'xpto' |
| Non-valued flat variable | var= | null |
| Empty flat variable | var='' | '' |
| Null flat variable | var=null | null |
| Valued array variable | var=[null] | [null] |
| Valued array variable | var=[1,'xpto'] | [1,'xpto'] |
| Valued array variable | var=[1,'xpto',null] | [1,'xpto',null] |
| Non-valued array variable | var=[,] | [null,null] |
| Empty array variable | var=[] | [] |
Table 1: Conversion of OData parameter values.
An empty value and a null value are distinct. Sending var='' gives ClickHouse an empty string, while sending var= gives ClickHouse a null. The same applies to arrays, where var=[] gives an empty array and var=[,] gives an array of two nulls.
Breaking change in 12.0.0
An empty array variable is converted to an empty array. In earlier versions, var=[] was converted to [null], an array with a single null element.
A query that expects an array with at least one element no longer gets one, and arrays built from different parameters can end up with different lengths, which makes ClickHouse fail the query. Review every report, dashboard, and OData client that sends an empty array. To keep the previous behavior, send var=[null].
Accessing Data Sets With an OData Client#
Any OData client can connect to Data Platform. However, the client will need an Access Token to retrieve the data.
To create an Access Token, go to your User page, select the Access Tokens sub-section, and create a new Access Token:
Save the token, so that it can be used in the OData Client to access the data:
Data Sets in Excel#
To retrieve data sets in Excel go to Data > Get Data > From Other Sources > From OData Feed:
Type the URL for the root folder you want to access, or an OData query to access a specific data set with query options, and select OK:
An authentication window will pop-up. Select Basic, paste your Access Token in the Password field and select Connect (you can leave the User name field empty):
You should now have access to the data in the data set, or to the list of data sets if you gave it an URL to a root folder (in which case you can pick the data set from the list). The data is now available to be processed and refreshed in Excel:
Excel caches the access tokens. Thus, if you are having trouble accessing the data (for example, your token expired) you should go to Get Data > Data Source Settings and select Clear Permissions, select the permissions you want to clear and Delete them:
Next time you try to connect to the data set, Excel will ask for the token again.
Data Sets in Jupyter Notebook#
There are several ways to access OData data sets in Jupyter Notebook. For example, with a regular HTTP request with an OData query:
Just make sure to use Basic Authentication in the request, and setting your Access Token as the password.