Query ASAM ODS Server

Query ASAM ODS Server#

In this example Notebook, we show you how to query (instance) data from your ASAM ODS Server.

The first sections are on initializing and connecting. The fun starts with “Query Instances”.

Dependencies for this notebook#

The ASAM ODSBox contains some functionality that wraps the ODS HTTP API making using Python easier ;-)

It contains:

  • http wrapper implemented using protobuf and requests

  • JAQueL implementation that converts MongoDB style JSON queries into a native ODS SelectStatement

  • utility that converts ODS DataMatrices into pandas.DataFrame

All examples are using the JAQueL query style to help you getting started. Use jaquel_to_select_statement to convert JAQueL to a native ODS SelectStatement.

try:  
  import odsbox
except:
  !pip install odsbox 

from odsbox.con_i import ConI

ASAM ODS Queries#

Establish session#

The ODS HTTP API is a session based API. The session ID is called conI in the ODS documentation. The ASAM ODSBox uses con_i as API object representing the session. Close this session to release the connection license. Otherwise the session will be auto closed after 30 minutes of inactivity.

con_i = ConI(url='http://79.140.180.128:10032/api', auth=('Demo','mdm'))

Query Instances#

Query Units#

Engineering units are contained in every ASAM ODS server, so we can use them to get started: Lets search for all units in this server

# There is always one Entity derived from AoUnit
con_i.query_data({
        "AoUnit":{}
    })
Unit.Id Unit.Name Unit.MimeType Unit.Description Unit.DateCreated Unit.Factor Unit.Offset Unit.dB Unit.dB_reference_factor Unit.PhysDimension
0 1 m application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 3
1 2 kg application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 2
2 3 s application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 1
3 4 A application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 4
4 5 K application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 5
... ... ... ... ... ... ... ... ... ... ...
96 97 Sv/s application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 63
97 98 A*s/kg application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 64
98 99 °C application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 73
99 100 rpm application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 73
100 101 Nm application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 73

101 rows × 10 columns

In our first query we’ve used the base name ‘AoUnit’ - so this query works on every ODS server. In our query result above we see that the entity derived from ‘AoUnit’ is called ‘Unit’. So we can use this directly.

con_i.query_data({
        "Unit":{}
    }).tail()
Unit.Id Unit.Name Unit.MimeType Unit.Description Unit.DateCreated Unit.Factor Unit.Offset Unit.dB Unit.dB_reference_factor Unit.PhysDimension
96 97 Sv/s application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 63
97 98 A*s/kg application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 64
98 99 °C application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 73
99 100 rpm application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 73
100 101 Nm application/x-asam.aounit 19700101000000 1.0 0.0 False 0.0 73

Now lets narrow down the list to the interesting attributes like ‘name’, ‘factor’ and ‘offset’. And instead of its id we want to know the ‘name’ of the referenced physical dimension.

con_i.query_data({
        "Unit":{},
        "$attributes":{
            "name":1,
            "factor":1,
            "offset":1,
            "PhysDimension.name":1
        }
    }).tail()
Unit.Name Unit.Factor Unit.Offset PhysDimension.Name
96 Sv/s 1.0 0.0 equivalent_dose_rate
97 A*s/kg 1.0 0.0 exposure_as_a_quantity
98 °C 1.0 0.0 unknown
99 rpm 1.0 0.0 unknown
100 Nm 1.0 0.0 unknown

In addition to the ‘name’ of the physical dimension, we are also interested in the lenght exponent.

con_i.query_data({
        "Unit":{},
        "$attributes":{
            "name":1,
            "factor":1,
            "offset":1,
            "PhysDimension.name":1,
            "PhysDimension.length_exp":1
        }
    }).tail()
Unit.Name Unit.Factor Unit.Offset PhysDimension.Name PhysDimension.Length
96 Sv/s 1.0 0.0 equivalent_dose_rate 2
97 A*s/kg 1.0 0.0 exposure_as_a_quantity 0
98 °C 1.0 0.0 unknown 0
99 rpm 1.0 0.0 unknown 0
100 Nm 1.0 0.0 unknown 0

Ok, and if I’m only interested in Units with a physical dimension with a length exponent of ‘1’?

con_i.query_data({
        "Unit":{"PhysDimension.length_exp":1},
        "$attributes":{
            "name":1,
            "factor":1,
            "offset":1,
            "PhysDimension.name":1,
            "PhysDimension.length_exp":1
        }
    }).tail()
Unit.Name Unit.Factor Unit.Offset PhysDimension.Name PhysDimension.Length
4 N 1.0 0.0 force 1
5 N/s 1.0 0.0 momentum 1
6 W/m*K 1.0 0.0 thermal_conductivity 1
7 V/m 1.0 0.0 electric_field_strength 1
8 H/m 1.0 0.0 permeability 1

…. or in those, where the ‘name’ of the physical dimension is ‘length’ …

con_i.query_data({
        "Unit":{"PhysDimension.name":"length"},
        "$attributes":{
            "name":1,
            "factor":1,
            "offset":1,
            "PhysDimension.name":1,
            "PhysDimension.length_exp":1
        }
    }).tail()
Unit.Name Unit.Factor Unit.Offset PhysDimension.Name PhysDimension.Length
0 m 1.0 0.0 length 1

… we can also make it easier getting a group of attributes of the same type in return…

con_i.query_data({
        "Unit":{"PhysDimension.name":"length"},
        "$attributes":{
            "name":1,
            "factor":1,
            "offset":1,
            "PhysDimension": {
                "name":1,
                "length_exp":1
            }
        }
    }).tail()
Unit.Name Unit.Factor Unit.Offset PhysDimension.Name PhysDimension.Length
0 m 1.0 0.0 length 1

All queries have used ‘equal’ as implicit operator before. Now lets use ‘like’ instead.

con_i.query_data({
        "Unit":{
            "name":{"$like":"k*"}
        },
        "$attributes":{
            "name":1,
            "factor":1,
            "offset":1,
            "PhysDimension": {
                "name":1,
                "length_exp":1
            }
        }
    }).tail()
Unit.Name Unit.Factor Unit.Offset PhysDimension.Name PhysDimension.Length
3 kg/m^3 1.0 0.0 density -3
4 km/h 3.6 0.0 velocity 1
5 kW*h 3600000.0 0.0 energy 2
6 kat 1.0 0.0 catalytic_activity 0
7 kat/m^3 1.0 0.0 catalytic_concentration -3

Query Hierarchy elements#

When you think about using ASAM ODS as a tool to manage and harmonize measurement data files like CSV, XLSX, MDF4, … you can think of the following data model:

  • file -> ‘TestStep’

  • group, sheet -> ‘MeaResult’

  • channel, column -> ‘MeaQuantity’

So lets query for them and reduce the maximal amount returned to a fix number - here: 5.

con_i.query_data({
    "TestStep": {},
    "$options": { "$rowlimit": 5 }
})
TestStep.Id TestStep.Name TestStep.MimeType TestStep.Description TestStep.DateCreated TestStep.Optional TestStep.Sortindex TestStep.MDMLinks TestStep.Test TestStep.TplTestStep TestStep.TestEquipment TestStep.UnitUnderTest TestStep.TestSequence TestStep.Classification
0 230 Profile_76 application/x-asam.aosubtest.teststep 20240730000000 False 21 [] 23 0 0 0 0 0
1 231 Profile_61 application/x-asam.aosubtest.teststep 20240728000000 False 22 [] 24 0 0 0 0 0
2 232 Profile_69 application/x-asam.aosubtest.teststep 20240725000000 False 23 [] 23 0 0 0 0 0
3 233 Profile_45 application/x-asam.aosubtest.teststep 20240722000000 False 24 [] 26 0 0 0 0 0
4 234 Profile_55 application/x-asam.aosubtest.teststep 20240720000000 False 25 [] 24 0 0 0 0 0 
con_i.query_data({
    "MeaResult": {},
    "$options": { "$rowlimit": 5 }
})
MeaResult.Id MeaResult.Name MeaResult.MimeType MeaResult.Description MeaResult.DateCreated MeaResult.MeasurementBegin MeaResult.MeasurementEnd MeaResult.MDMLinks MeaResult.TestStep MeaResult.TestEquipment MeaResult.UnitUnderTest MeaResult.TestSequence MeaResult.Classification MeaResult.TplMeaResult
0 297 Profile_56 application/x-asam.aomeasurement Data based on the Kaggle 'Electric Motor Tempe... 19700101000000 20240802000000 20240802000000 [] 229 0 0 0 0 0
1 298 Profile_76 application/x-asam.aomeasurement Data based on the Kaggle 'Electric Motor Tempe... 19700101000000 20240730000000 20240730000000 [] 230 0 0 0 0 0
2 299 Profile_61 application/x-asam.aomeasurement Data based on the Kaggle 'Electric Motor Tempe... 19700101000000 20240728000000 20240728000000 [] 231 0 0 0 0 0
3 300 Profile_69 application/x-asam.aomeasurement Data based on the Kaggle 'Electric Motor Tempe... 19700101000000 20240725000000 20240725000000 [] 232 0 0 0 0 0
4 301 Profile_45 application/x-asam.aomeasurement Data based on the Kaggle 'Electric Motor Tempe... 19700101000000 20240722000000 20240722000000 [] 233 0 0 0 0 0 
con_i.query_data({
    "MeaQuantity": {},
    "$options": { "$rowlimit": 5 }
})
MeaQuantity.Id MeaQuantity.Name MeaQuantity.MimeType MeaQuantity.Description MeaQuantity.Interpolation MeaQuantity.DataType MeaQuantity.Rank MeaQuantity.Dimension MeaQuantity.TypeSize MeaQuantity.Minimum MeaQuantity.Maximum MeaQuantity.Average MeaQuantity.Deviation MeaQuantity.non_reference_channel_name MeaQuantity.reference_channel_name MeaQuantity.MeaResult MeaQuantity.Quantity MeaQuantity.Unit MeaQuantity.Classification
0 3849 Time application/x-asam.aomeasurementquantity Time measured in 2 Hz 0 7 0 [] 0 0.000000 7982.000000 3991.000000 4781.037581 297 0 3 0
1 3850 U_q application/x-asam.aomeasurementquantity Voltage q-component measurement in dq-coordinates 0 7 0 [] 0 -9.305150 133.009676 67.712335 37.097723 297 0 76 0
2 3851 Coolant application/x-asam.aomeasurementquantity Coolant temperature 0 7 0 [] 0 17.354847 91.180365 58.899453 20.461698 297 0 99 0
3 3852 Stator_winding application/x-asam.aomeasurementquantity Stator winding temperature measured with therm... 0 7 0 [] 0 45.986175 131.675086 92.553726 22.161028 297 0 99 0
4 3853 U_d application/x-asam.aomeasurementquantity Voltage d-component measurement in dq-coordinates 0 7 0 [] 0 -131.234010 123.049144 -20.714359 72.732526 297 0 76 0

Get parent properties#

Consider you’re looking for columns (MeaQuantity), but also being interested to which sheet (MeaResult) and file (TestStep) they belong, the following query will give you an idea how this works…

con_i.query_data({
    "MeaQuantity": {},
    "$attributes": {
        "Id": 1,
        "Name": 1,
        "MeaResult": {
            "Id": 1,
            "Name": 1,
            "TestStep": {
                "Id": 1,
                "Name": 1
            }
        },
        "Unit.name": 1
    },
    "$options": {"$rowlimit": 10}
})
MeaQuantity.Id MeaQuantity.Name MeaResult.Id MeaResult.Name TestStep.Id TestStep.Name Unit.Name
0 3849 Time 297 Profile_56 229 Profile_56 s
1 3850 U_q 297 Profile_56 229 Profile_56 V
2 3851 Coolant 297 Profile_56 229 Profile_56 °C
3 3852 Stator_winding 297 Profile_56 229 Profile_56 °C
4 3853 U_d 297 Profile_56 229 Profile_56 V
5 3854 Stator_tooth 297 Profile_56 229 Profile_56 °C
6 3855 Motor_speed 297 Profile_56 229 Profile_56 rpm
7 3856 I_d 297 Profile_56 229 Profile_56 A
8 3857 I_q 297 Profile_56 229 Profile_56 A
9 3858 Pm 297 Profile_56 229 Profile_56 °C

Parameter and ParameterSets#

Parameters are a flexible way to carry additional information, not being strictly pre-defined by the data model. Those parameters are name value pairs with an additional data type information, being grouped in parameter sets. The following query returns the parameters belonging to certain MeaResult - including their parameter set information…

con_i.query_data({
    "ResultParameter": {"parameter_set.MeaResult.Name": {"$like": "Pend*"}},
    "$attributes": {
        "Name": 1,
        "Value": 1,
        "DataType": 1,
        "parameter_set.Name": 1,
        "parameter_set.MeaResult.Id": 1
    },
    "$options": {"$rowlimit": 20}
})
ResultParameter.Name ResultParameter.Value ResultParameter.DataType ResultParameterSet.Name MeaResult.Id

Given their flexible character, only minimal query capabilities can be offered for Parameters. Be aware that the conditions are combined by “AND” and lead to an empty result if you try to use multiple properties.

con_i.query_data({
    "ResultParameter": {
        "parameter_set.MeaResult.Name": {"$like": "Pend*"},
        "Name":"start_time",
        "DataType":1,
        "Value": {"$like": "2023*"}
    },
    "$attributes": {
        "Name": 1,
        "Value": 1,
        "DataType": 1,
        "parameter_set.Name": 1,
        "parameter_set.MeaResult.Id": 1
    },
    "$options": {"$rowlimit": 20}
})
ResultParameter.Name ResultParameter.Value ResultParameter.DataType ResultParameterSet.Name MeaResult.Id 
con_i.query_data({
    "ResultParameter": {
        "parameter_set.MeaResult.Name": {"$like": "Pend*"},
        "$and": [
            {
                "Name": "start_time",
                "DataType": 1,
                "Value": {"$like": "2023*"}
             }
        ]
    },
    "$attributes": {
        "Name": 1,
        "Value": 1,
        "DataType": 1,
        "parameter_set.Name": 1,
        "parameter_set.MeaResult.Id": 1
    },
    "$options": {"$rowlimit": 20}
})
ResultParameter.Name ResultParameter.Value ResultParameter.DataType ResultParameterSet.Name MeaResult.Id

Aggregates#

Distinct#

con_i.query_data({
    "Test": {},
    "$attributes": {
        "name": {"$distinct": 1}
    }
})
Test.Name.AG_DISTINCT
0 Campaign_05
1 Campaign_06
2 Campaign_03
3 Campaign_01
4 Campaign_02
5 Campaign_00
6 Campaign_04

Lets do it adaptive with query condition

con_i.query_data({
    "Test": {"name": {"$like": "*"}},
    "$attributes": {
        "name": {"$distinct": 1}
    }
})
Test.Name.AG_DISTINCT
0 Campaign_05
1 Campaign_06
2 Campaign_03
3 Campaign_01
4 Campaign_02
5 Campaign_00
6 Campaign_04

Min & Max#

con_i.query_data({
    "TestStep": {},
    "$attributes": {
        "DateCreated": {"$max": 1}
    }
})
TestStep.DateCreated.AG_MAX
0 20240919000000 
con_i.query_data({
    "TestStep": {},
    "$attributes": {
        "DateCreated": {"$min": 1}
    }
})
TestStep.DateCreated.AG_MIN
0 20240405000000 
con_i.query_data({
    "TestStep": {},
    "$attributes": {
        "DateCreated": {"$max": 1, "$min": 1}
    }
})
TestStep.DateCreated.AG_MAX TestStep.DateCreated.AG_MIN
0 20240919000000 20240405000000

Lets do it adaptive with query condition

con_i.query_data({
    "TestStep": {"Name": {"$like": "EngineNoise*"}},
    "$attributes": {
        "DateCreated": {"$max": 1, "$min":1}
    }
})
TestStep.DateCreated.AG_MAX TestStep.DateCreated.AG_MIN
0 19700101000000 19700101000000

Close session#

Don’t forget to close the session to release the connection license. Otherwise the session will be auto closed after 30 minutes of inactivity.

con_i.logout()

License#

Copyright © 2024 Peak Solution GmbH

The training material in this repository is licensed under a Creative Commons BY-NC-SA 4.0 license. See LICENSE file for more information.