DAS is used to do analytics based on the published event data. After the analytics done these summarised data may need other external components like APIM dashboard etc. In such a case we face how to retrieve these data from the DAS to outside. One option we have is saving summarized data to external RDBMS as APIM 1.9.x used. But the next interesting option we have is using DAS REST API. REST API can be used to retrieve data from DAS internal tables.

But sometime developer may prefer extracting more specific data from the summarised data. In that case, we have to do some search. DAS rest API provide Apache Lucene based searching capabilities to the DAS tables.

As developers, we may be interesting on more specific and complex search queries on the RDBMS. But Lucene search queries are very limited and you cannot achieve SQL like data retrieval. But using Lucene aggregates API we can make data retrieval more specifically.

This blog is to explain how to use the das REST API for aggregate functions.

1. Current REST API Support Following Aggregate functions on the numeric values
   • MAX
   • MIN
   • AVG
   • SUM
2. REST API Aggregate Lucene search request object format

   {
       "query": "max_request_time: [1442860200000 TO 1445538600000]",
       "aggregateLevel": 3,
       "tableName": "API_FAULT_SUMMARY",
       "groupByField": "api_version_apiPublisher_context_facet",
       "aggregateFields": [
           {
               "fieldName": "total_fault_count",
               "aggregate": "SUM",
               "alias": "totalFaultCount"
           }
       ]
   }
   

   • Query: Lucene search query
   • tablename: name of the table
   • groupByField: Field of the table that used for grouping. This field is a composition of the one or multiple fields. which can be created using facet attribute and which is JSON array.
   • aggregateFields: aggregate functions configs
   • aggregateFields.fieldName : column name of the table which applies the function
   • aggregateFields.aggregate: aggregate function
   • aggregateFields.alias : name was given to the result
   • aggregateLevel: integer value indicating depth of the number of columns used to group. As mentioned earlier groupByField is a composition of a several columns value. if the groupByField is composed of the 4 values and if the aggregateLevel is set to 0 it means using the first value of the groupByField used to group. likewise, if it set to 1 it means using a first and second value of the groupByField to grouping etc. So it always matters the order of the values in groupByField.
3. How to create a table which supports for aggregate function

   Its column should be indexed : that is for Lucene support SHould have facet attribute for aggregate functions Create facet attribute composing multiple attributes.

   ex: say we need an API table having a name,request_count, version,user_count, publisher attributes. also, we need to find out the request_count and user_count grouping by the name, version, and publisher. So that we need to create extra facet attribute for a name, version, and publisher as a combined column.

   ex:

   CREATE TEMPORARY TABLE API_SUMMARY USING CarbonAnalytics OPTIONS (tableName "API_SUMMARY",
   schema "name string -i,
   version string -i,
   publisher string -i,
   request_count int -i,
   user_count int -i,
   name_version_publisher facet -i",
   primaryKeys "name,version,publisher"
   );
   

   note that, we have to always keep the name column as a primitive type column along with facet composition attribute for support lucent query. Because for query attribute it is supported only for primitive type columns only.

4. How to insert data

   insert into table API_SUMMARY select “sampleAPI”,”v1.0.0”,”admin”,5,2,”sampleAPI,v1.0.0,admin”;
   
   

5. How to construct the Lucene search request,

   {
       "query": "",
       "aggregateLevel": 2,
       "tableName": "API_SUMMARY",
       "groupByField": "name_version_publisher",
       "aggregateFields": [
           {
               "fieldName": "request_count",
               "aggregate": "SUM",
               "alias": "count"
           }
       ]
   }
   

   the simplicity we keep empty query. And we are group by name_version_publisher field. Here we group by all the name, version and publisher. Thus aggregateLevel should be 2. If you want to group by the only name it is 0 and if group by name, version it is 1.

6. Lucene search request with queries.

   in a situation where you want to get all the request count of the particular API, we can use the lucene query.

   ex: query: “name:myapi”

   {
       "query": “name”:”sampleAPI”,
       "aggregateLevel": 2,
       "tableName": "API_SUMMARY",
       "groupByField": "name_version_publisher",
       "aggregateFields": [
           {
               "fieldName": "request_count",
               "aggregate": "SUM",
               "alias": "count"
           }
       ]
   }
   

   sql equivalent query for above: select name, version, publisher, sum(request_count) from API_SUMMARY where api=”myapi” group by name, version, publisher.

7. Using more than one aggregate functions

   You can use more than one aggregate function by adding more to aggregateFields.

   ex:

   {
       "query": “name:sampleAPI”,
       "aggregateLevel": 2,
       "tableName": "API_SUMMARY",
       "groupByField": "name_version_publisher",
       "aggregateFields": [
           {
               "fieldName": "request_count",
               "aggregate": "SUM",
               "alias": "count"
           },
            {
               "fieldName": "user_count",
               "aggregate": "AVG",
               "alias": "user_avg"
           }
       ]
   }
   

8. Format of the json response

   [
   {
      "tableName": "API_SUMMARY",
      "timestamp": 1446093162267,
      "values":    {
         "name_version_publisher":       [
            "sampleAPI",
            "v1.0.0",
            "admin"
         ],
         "count": 5,
         "user_avg": 2
      }
   }
   ]
   

   response is in json format and it is an arrays of objects. each object contain the different result for the different groups. here we have one group and its associate result. mainly it contain the attribute for the table name, timestamp and the value. Value contain the actual results for the aggregate functions. It contain the values for the aggregate functions and the attribute value of the group.

   value for the aggregate function is return with the alias name you provided with the search request like count and user_avg. And it return a record with the groupByField you used alone with a value of that group. It is a array and it’s length will equal to the aggregateLevel. And it is value preserve the same order as it’s actual data formed.

   Aggregate function of a different combination of attributes. if you have many columns and you need a different type of grouping you have to define extra facet attribute with a combination of the other attributes.

   ex: if you need group by name and publisher you have to define facet with a combination of name and publisher like name_publisher. Because with name_version_punlisher you can’t group by name and publisher.

9. Limitation

   Current version of DAS have following limitation in REST API and its aggregate API

   • impossible to get distinct records
   • cannot do aggregate function on non-numeric fields
   • cannot do aggregate function without grouping
   • cannot mention the order(ASC or DESC)