2 Replies Latest reply on Sep 11, 2016 5:14 PM by roberttay RSS
    roberttay Apprentice

    Query API and Aggregates

    We have a requirement to provide aggregate information for logged properties across multiple assets inheriting from the same ThingTemplate. The scenario would be something like,

     

    Out of 100,000 assets (each with 50 logged properties with several months of history):

     

    - Show me the count of assets which were disconnected in the last 30 days

    - Show me the count of assets whose temperature has been  above 90 degrees in the last 90 days

    - etc...

     

    I understand this can be done using the InfoTableFunctions, however, this means all of the data would have to be sucked into memory. I'm not sure this is practical with large data sets and a large number of users executing this type of query on a frequent basis or simultaneously.

     

    I was hoping the Query API could handle this on the server and not pull things into memory.

     

    Also, assume we are using a PostGres persistence store (not Neo4j).

     

    If the InfoTableFunction is indeed the only way to accomplish this, what are the limits for performing these types of aggregates.


    Asked on IoT Overflow, but no response:

    http://converge-overflow.ptcnet.ptc.com/index.php/4169/query-api-and-aggregates


      • Re: Query API and Aggregates
        carlesc Ninja

        I don't think that you can query this kind of huge TW model with current TW features ( without putting an infinite server on it ). Some solutions:

        • If questions are known in advance, easily you can aggregate data while you are storing it, or in batch each day --> Actually we are doing this.
        • If questions aren't known in advance, you may need to push all your model data to a Business Intelligence/Big Data tool where you can query easily huge amounts of data.

        Hope it helps,

        Best Regards,

        Carles.

          • Re: Query API and Aggregates
            roberttay Apprentice

            Hmmmm. Was speaking to a colleague and he mentioned that Things (assets) are already in memory (as they have to be able to respond to events etc...).Therefore, my assumption about additional memory seems to be wrong. However, there is still a concern regarding performance. We haven't yet performance tested the aggregation functions across a large number of assets.