apim_4xx_support_multiple_analytics_publishers APIM manage workflow with multiple roles APIM 3.0.0 per API based subscription workflow Logging internal HTTP requests Log APIM analytics events to a file Monetization and sample with WSO2 API Manager 2.6.0 Share application and subscription among a set of specific groups or roles WSO2 APIM Correlating analytics event with correlationID APIM analytics distinguish production and sandbox traffic APIM 2.x.x analytics internal and analytics tuneup Configure APIM(Next release) Key Manager User stores APIM(Next release) working with key manager DAS 3.x Parse system variables to Spark Context Revoke OAuth application In APIM 2.1.0 Next WSO2 APIM powered by WSO2 Ballerina Configure WSO2 APIM Analytics on Cluster environment Configure WSO2 DAS 3.1.0 for WSO2 APIM 2.0.0 Analytics WSO2 APIM publishing custom statistics WSO2 APIM Error codes Working with WSO2 message tracer Use DAS admin service to query using Spark SQL Configure WSO2 APIM Analytics using XML WSO2 APIM Generating and Retrieving Custom Statistics Understanding WSO2 APIM Statistics Model Publishing WSO2 APIM 1.10.x Runtime Statistics to DAS with RDBMS Publishing_APIM_1100_Runtime_Statistics_to_DAS Aggregate functions with WSO2 DAS REST API Create a cApp for WSO2 DAS Debugging WSO2 Products using OSGI console. Publishing APIM Runtime Statistics to DAS Deploy cApp on WSO2 DAS How to configure and start the Accumulo minicluster How to setup DNS server on Ubuntu and Ubuntu server How to use Java Reflection how to install apache web server on ubuntu and ubuntu server How to install Mail server on Ubuntu and Ubuntu server How to install squirrelmail webmail client on Ubuntu and Ubuntu Server Pass and return String value to JNI method Pass and return numeric value to JNI method Calling a C Function from the Java Programming Language using JNI AXIS 2 Sample web service Client with maven and eclipse How to setup AXIS 2 with Apache Tomcat AXIS 2 Sample web service with maven and eclipse Robot framework Sample with Selenium Robot framework Custom Library Sample Behaviour-Driven Development with JBehave and Eclipse Play Audio with Netbeans and linking with LibVLC Implement LibVLC based player with QT-part2 Simple Audio playing sample with LibVLC How to install LibVLC on Ubuntu Implement LibVLC based player with QT-part1
Aggregate functions with WSO2 DAS REST API

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)

Add Comment

* Required information
1000
Powered by Commentics

Comments (0)

No comments yet. Be the first!