Skip to content

Pulsar Reporting API User Guide

Jump to bottom Edit New page
mingmwang edited this page Nov 4, 2015 · 6 revisions

Welcome to the Pulsar Query API User Guide! You will learn all about how to use Pulsar Query API to power your data visualization. The API layers between the metric stores and visualization and provides interactive analytic queries with Rest/SQL manner. The current version of the API is v2.

Pulsar Query API is designed to provide a standard way to connect to different metric stores. The current supported metric store is Druid. We will add support for other metric stores like Apache Kylin later. If you are new to Druid, please go to druid.io to get more info.

PreConfig

under pulsarquery-service\src\main\resources\

Please copy the pulsar-config.properties file to your ${user.home} directory and configure the parameters like "pulsarquery.db.url" and "pulsarquery.memcached.hosts" correctly.

DataSource Setup

Pulsar Query supports both static datasources and dynamic datasources. You can preconfigure the API to connect to metric stores with properties files or add new data sources dynamically with DataSource management API.

Druid DataSource setup

under pulsarquery-service\src\main\resources\datasources

Add druid.properties:

datasourcetype=druid
datasourcename=druiddatasource1
endpoints=http://xxxxx/druid/v2

The endpoints are the Druid brokers, you can specify a comma separated list of broker nodes. You can configure and connect to as many druid clusters as you want, just put them to different properties files with different data source names.

Dynamic DataSource setup

Check Administrator API Datasource Part

Pulsar Query API

Pulsar Query API supports both SQL and structured JSON syntax.

SQL API

Endpoint: http://<API_Server>/prapi/v2/sql
Field Type Description Required
sql String An standard SQL query of "select ... from ... where expression". Yes
intervals String Pattern: "yyyy-MM-dd HH:mm:ss/yyyy-MM-dd HH:mm:ss" (startTime/endTime) The time pattern can also be: "yyyy-MM-dd HH:mm. ...", or "yyyy-MM-dd/ ..." These are similar to the startTime and endTime in above metrics Yes
granularity String The time range you want to roll up the data by. Simple type Supported values are: all, second, minute, five_minute, fifteen_minute, thirty_minute, hour, day, week, month. "all" means the data is rolled up by the time range marked by startTime and endTime. ISO8601 Duration Refer to http://en.wikipedia.org/wiki/ISO_8601#Durations for the possible values. The format is P[n]Y[n]M[n]DT[n]H[n]M[n]S. Examples: P1D = 1 day, P3M = 3 months, PT8H = 8 hours, P3DT2H5M = 3 days 2 hours 5 minutes Yes

Currently, there are some restrictions in term of the SELECT statements can do. There must be an Aggregation column for SELECT. The FROM table can only be pulsar_session or pulsar_event now.

|Payload|

{ "sql" : "SELECT (count(session) - sum(retvisitor)) * 1.0 / count(session) newSessionRate, sum(sessionDuration) * 1000 totalSessionDurations, count(session) sessions, sum(sessionDuration) totalSessions, sum(totalpagect) totalPages, country, trafficSource FROM pulsar_session WHERE site=0 and country='usa' GROUP BY country, trafficSource ORDER BY sum(totalpagect) ASC limit 20",
"intervals": "2015-10-11 03:00:32/2015-10-18 01:00:32",
"granularity": "day"}

Supported SQL Syntax:

  • SQL Select
  • SQL Where
  • SQL Group By
  • SQL Order By (ASC|DESC)
  • SQL LIMIT
  • SQL Having
  • SQL AND & OR
  • SQL Like
  • SQL In
  • SQL NOT In
  • SQL is Null
  • SQL Not Null
  • SQL count distinct
  • SQL count()
  • SQL max()
  • SQL min()
  • SQL sum()
  • SQL Aliases

You can query different data sources like below:

Endpoint: http://<API_Server>/prapi/v2/sql/{datasourceName}

Alternatively, you can query as below:

Endpoint: http://<API_Server>/prapi/v2/sql

SELECT count(session) sessions,country, trafficSource FROM datasourceName.pulsar_session WHERE site=0 and country='usa' GROUP BY country, trafficSource ORDER BY count(session) ASC limit 20

If you do not specify any data source name in the SQL or path, it's default to query the data source "trackingdruid" which is preconfigured in Pulsar HOLAP:

pulsarholp-config.xml

You can also debug the query to take a closer look at the raw queries generated to query the metric store, the query time spent, whether it's from cache or not. To debug the query, you just need to add param "?debug" to the query path:

Endpoint: http://<API_Server>/prapi/v2/sql?debug

Structured JSON API

Pulsar Reporting JSON API supports querying predefined metrics in a restful way. Those predefined metrics are some common analytic metrics as Google Analytics defined, like activeusers, pageviews, sessions etc. The definition of those metrics and aggregation methods are pre-configured in XML file: restmetric-config.xml

  • The metric "activeusers" is used to measure "Number of Visitors".
  • The metric "pageviews" is used to measure "Number of Clicks".
  • The metric "sessions" is used to measure "Session related metrics, like session count, duration, bounce etc".

RealTime API

Query metrics occurring now. (Default to last 5 minutes).

Endpoint: http://<API_Server>/prapi/v2/realtime
Field Type Description Required
metrics String array Array of the metrics you need to get. We only support 1 metric for the current release. Yes
dimensions String array Array of dimensions. The result will be grouped by the dimensions. No
filter String An standard SQL where expression of filters. It supports "and", "or" and "=","!=". E.g., country='usa' and (deviceFamily='mobile' or deviceFamily='tablet') No
maxResults Int Maximum number of rows for the results. Default 10. No

|Payload|

{
  "metrics": [
    "activeusers"
  ],
  "dimensions": [
    "devicefamily"
  ],
  "filter": "country='usa'",
  "maxResults": 50
}

Core API

Query historical metrics to produce customized reports. Besides the fields used by Realtime API, there are 3 more fields, startTime, endTime and granularity.

Endpoint: http://<API_Server>/prapi/v2/core

|Payload|

{
  "metrics": [ "activeusers" ],
  "dimensions": [ "devicefamily" ],
  "filter": "country='usa'",
  "startTime": "2015-06-01 08:00:00",
  "endTime": "2015-06-02 09:00:00",
  "granularity": "day"
}

{
  "metrics": [ "pageviews" ],
  "dimensions": [ "trafficsource" ],
  "filter": "country='usa'",
  "startTime": "2015-06-01 08:00:00",
  "endTime": "2015-06-02 09:00:00",
  "granularity": "day"
}

Today API

This is just a wrapper on top of core API which will give you the data of today break down by 15 min granularity. Today means from starting of today to now.

Endpoint: http://<API_Server>/prapi/v2/today

|Payload|

{   "metrics" : [ "activeusers" ],   "dimensions" : [ "trafficsource" ],   "filter" :  "site=0"   }

Payload is the same as Realtime API. "startTime", "endTime" and "granularity" are defined by API internally with values of start of today, now and 15 minutes respectively.

Yesterday API

This is just a wrapper on top of core API which will give you the 24 hour data of yesterday break down by 15 min granularity.

Endpoint: http://<API_Server>/prapi/v2/yesterday

|Payload|

{   "metrics" : [ "activeusers" ],   "dimensions" : [ "trafficsource" ],   "filter" :  "site=0"   }

Payload is the same as Realtime API. "startTime", "endTime" and "granularity" are defined by API internally with values of start of yesterday, end of yesterday and 15 minutes respectively.

Pulsar Query MetaData API

Retrieve the list of dimensions and metrics of the data source.

SQL

Endpoint: http://<API_Server>/prapi/v2/sql

|Show data source types|

{"sql":"show datasourcetypes"}

|Show all the data sources|

{"sql":"show datasources"}

|Show all the tables from a data source|

{"sql":"show tables from <datasourcename>"}

|Show the metadata from a table|

{"sql":"desc <datasourcename.tablename>"}

|Show the dimensions from a table|

{"sql":"desc <datasourcename.tablename>.dimensions"}

|Show the metrics from a table|

{"sql":"desc <datasourcename.tablename>.metrics"}

Rest

Endpoint: http://<API_Server>/prapi/v2/datasource

|Show data source types|

Endpoint: http://<API_Server>/prapi/v2/datasource

|Show all the data sources from a datasource type|

Endpoint: http://<API_Server>/prapi/v2/datasource/druid

|Show all the tables from a data source|

Endpoint: http://<API_Server>/prapi/v2/datasource/druid/{datasourceName}

|Show the metadata from a table|

Endpoint: http://<API_Server>/prapi/v2/datasource/druid/{datasourceName}/{tableName}

|Show the dimensions from a table|

Endpoint: http://<API_Server>/prapi/v2/datasource/druid/{datasourceName}/{tableName}?metrics=false

|Show the metrics from a table|

Endpoint: http://<API_Server>/prapi/v2/datasource/druid/{datasourceName}/{tableName}?metrics=true

PulsarQuery API Error Code

Datasources:

Error Type Error Code Error Message
DATASOURCE_ERROR 11001 "DataSource Error."
INVALID_DATASOURCE 11002 "Invalid query datasource:" + datasource name
INVALID_ENDPOINTS 11003 "Invalid Endpoints in XML config for metric: " + metric

Query Parameters:

Error Type Error Code Error Message
INVALID_GRANULARITY 12001 "Invalid granularity: " + granularity
INVALID_GRANULARITY_INTERVAL 12002 "Invalid granularity minute with Intervals > 1 day. "
INVALID_QUERYTIME 12003 "Invalid time range (start/end):" + time range
INVALID_CUSTOM_TIME 12004 "Invalid Custom Time: " + time range

SQL:

Error Type Error Code Error Message
MISSING_SQL 13001 "Missing sql."
SQL_PARSING_ERROR 13002 "SQL parsing error: " + detailed message
INVALID_AGGREGATE 13011 "Invalid aggregate: " + detailed message
INVALID_SORT_PARAM 13021 "Invalid sort parameter (must be metric/aggregate): " + detailed message)
INVALID_FILTER 13031 "Invalid filter: " + detailed message
MISSING_METRIC 13041 "Missing metrics. "
INVALID_METRIC 13042 "Invalid metrics: " + detailed message
MULTI_METRICS_ERROR 13043 "Multi-metrics not supported. "
INVALID_HAVING_CLAUSE 13051 "Invalid having clause: " + detailed message
INVALID_DIMENSION 13061 "Invalid dimension: " + detailed message
INVALID_MAXRESULT 13071 "Invalid maxResults parameter: " + detailed message
INVALID_DURATION 13081 "Invalid duration (must be between 1 to 1800 seconds): "+ detailed message

Security and Permission Control

Spring security is integrated to provide the authentication and authorization. The http basic authentication with username/password stored in database is used as default.

Configured file: pulsarquery-service\src\main\resources\simple\authentication- security.xml

The authorization is implemented for the data security sensitive. The user without correct rights can not view the dashboards and do query with the datasource.

For one datasource(/dashboard/group), the owner who created it has the manage right.

To grant a right "RightA" to a group "GroupB", two conditions should be satisfied:

  • The user has the "RightA".
  • The user has the manage right of "GroupB".

Users can do the authorization management by the Admin Page in our Pulsar Reporting UI framework. Restful API is also provided to do the authorization management.

Right types are defined as:

Type Description Is Public
ADD_DATASOURCE The right to create a new datasource. Yes
ADD_DASHBOARD The right to create a new dashboard. Yes
ADD_GROUP The right to create a new group. No
{datasourceName}_MANAGE The right to do delete/update/do query actions for this datasource. No
{datasourceName}_VIEW The right only to do query for this datasource. No
{dashboardName}_MANAGE The right to do delete/update/view actions for this dashboard. No
{dashboardName}_VIEW The right only can view this dashboard. No
{groupName}_MANAGE The right to do delete/update actions for this group and can update users and rights in the group. No
{groupName}_VIEW The right only can view this group and view the users and rights in the group. No
SYS_MANAGE_DATASOURCE The right can do delete/update actions for all datasources. No
SYS_VIEW_DATASOURCE The right can do query for all datasources. No
SYS_MANAGE_DASHBOARD The right can do delete/update actions for all dashboards. No
SYS_VIEW_DASHBOARD The right can view all dashboards. No
SYS_MANAGE_GROUP The right can do delete/update actions for all groups and can update users and rights in all groups. No
SYS_VIEW_GROUP The right can can view all the groups and view the users and rights in all groups. No

Administrator API

The Administrator API is used to manage the user created datasources, user created dashboards, permissions, etc currently all the data are store in in-memeroy db derby, If you want to change to mySQL or other RDBMS, Please first initialized db in advance. refer to pulsarquery-admin/src/main/resources/mysql.sql. and add dependencies in pom.xml. Defult User and password: admin/test

headers:

Accept:  application/json
Content-Type:  application/json
Authorization:  Base 64 encoding for username:password

1. DataSource Part

a) Add a datasource

Endpoint: http://<API_Server>/prapi/v2/datasources

Method: POST

Field Type Description Required
displayName String The diplayName for the datasource. Yes
type String The datasource type, like druid. Yes
endpoint String The valid URI for datasource connection Yes
comment String The description for the datasource. No
properties String Required parameters for datasource connection. No

|Payload|

{"displayName":"datasource1", "type":"druid/pulsar","endpoint":"valid uri","comment":"testComment","properties":"test"}

b) Get datasources

Endpoint: http://<API_Server>/prapi/v2/datasources  (get all the datasources users can manage by default)
          http://<API_Server>/prapi/v2/datasources?right=view  (get all the datasources users can view)

Method: GET

|NO Payload|

c) Update a datasource

Endpoint: http://<API_Server>/prapi/v2/datasources

Method: PUT

Field Type Description Required
name String The unique name generated by system for the datasource. Yes
displayName String The diplayName for the datasource. No
endpoint String The valid URI for datasource connection No
comment String The description for the datasource. No
properties String Required parameters for datasource connection. No

|Payload|

{"name":"datasource1", "displayName":"DS1","endpoint":"valid uri"}

d) Delete a datasource

Endpoint: http://<API_Server>/prapi/v2/datasources/{datasourceName}

Method: DELETE

|NO Payload|

e) Batch delete datasources

Endpoint: http://<API_Server>/prapi/v2/datasources?batch=datasource1,datasource2

Method: DELETE

|NO Payload|

2. Dashboards Part

a) Add a dashboard

Endpoint: http://<API_Server>/prapi/v2/dashboards

Method: POST

Field Type Description Required
displayName String The diplayName for the dashboard. Yes
config Json The configuration file for a dashboard. Yes

|Payload|

{"displayName":"dashboard1", "config":{xxx}}

b) Get dashboards

Endpoint: http://<API_Server>/prapi/v2/dashboards   (get all the dashboards users can manage by default)
          http://<API_Server>/prapi/v2/dashboards?right=view  (get all the dashboards users can view)

Method: GET

|NO Payload|

c) Update a dashboard

Endpoint: http://<API_Server>/prapi/v2/dashboards

Method: PUT

Field Type Description Required
name String The unique name generated by system for the dashboard. Yes
config Json The configuration file for a dashboard. Yes
displayName String The diplayName for the dashboard. No

|Payload|

{"name":" dashboard1", "config":{xxx}, "displayname":"DS1"}

d) Delete a dashboard

Endpoint: http://<API_Server>/prapi/v2/dashboards/{dashboardName}

Method: DELETE

|NO Payload|

e) Batch delete dashboards

Endpoint: http://<API_Server>/prapi/v2/dashboards?batch=dashboard1,dashboard2

Method: DELETE

|NO Payload|

3. Group Part

a) Add a group

Endpoint: http://<API_Server>/prapi/v2/groups

Method: POST

Field Type Description Required
displayName String The diplayName for the group. Yes

|Payload|

{"displayName":"group1"}

b) Get groups

Endpoint: http://<API_Server>/prapi/v2/groups   (get all the groups users can manage by default)
          http://<API_Server>/prapi/v2/groups?right=view  (get all the groups users can view)

Method: GET

|NO Payload|

c) Update a group

Endpoint: http://<API_Server>/prapi/v2/groups

Method: PUT

Field Type Description Required
name String The unique name generated by system for the group. Yes
displayName String The diplayName for the group. No

|Payload|

{"name":" group1", "displayName":"Group1"}

d) Delete a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}

Method: DELETE

|NO Payload|

e) Batch delete groups

Endpoint: http://<API_Server>/prapi/v2/groups?batch=group1,group2

Method: DELETE

|NO Payload|

4. Group and User Part

a) Add users to a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/users

Method: POST

|Payload|

["user1","user2",…]

b) Delete a user from a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/users/{userName}

Method: DELETE

|NO Payload|

c) Delete users from a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/users?batch=username1,username2

Method: DELETE

|NO Payload|

d) Replace all the users in a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/users

Method: PUT

|Payload|

["user1","user2",…]

e) Get users in a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/users

Method: GET

|NO Payload|

5. Group and Right part

a) Add rights to a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/rights

Method: POST

Field Type Description Required
rightName String The name of one right. Yes
rightType String "0" represents the system rights. "1" represents the datasource rights. "2" represents the dashboard rights. "3" represents the group rights. Yes

|Payload|

[ 
{"rightName":"datasource1_MANAGE", "rightType": "1"},
{"rightName":"dashboard1_VIEW", "rightType":"2"}
]

b) Delete a right from a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/rights/{rightName}

Method: DELETE

|NO Payload|

c) Batch delete rights from a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/rights?batch=rightName1,rightName2

Method: DELETE

|NO Payload|

d) Replace all rights in a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/rights

Method: PUT

Field Type Description Required
rightName String The name of one right. Yes
rightType String "0" represents the system rights. "1" represents the datasource rights. "2" represents the dashboard rights. "3" represents the group rights. Yes

|Payload|

[ 
{"rightName":"datasource1_MANAGE", "rightType": "1"},
{"rightName":"dashboard1_VIEW", "rightType":"2"}
…
]

e) Get rights in a group

Endpoint: http://<API_Server>/prapi/v2/groups/{groupName}/rights

Method: GET

|NO Payload|

f) Get all the groups that can manage or view datasource

Endpoint: http://<API_Server>/prapi/v2/datasources/{datasourceName}/groups (get all the groups can manage the datasource)
http://xxxxxx:8080/prapi/v2/datasources/{datasourceName}/groups?right=view

Method: GET

|NO Payload|

6. User Part

a) Add users

Endpoint: http://<API_Server>/prapi/v2/user

Method: POST

Field Type Description Required
name String The name of the user. Yes
password String The password will be encoded by MD5 and stored to database. No

|Payload|

{"name":"user1","password":"test" }

b) Delete users

Endpoint: http://<API_Server>/prapi/v2/users?batch=user1,user2

Method: DELETE

|NO Payload|

c) Get users

Endpoint: http://<API_Server>/prapi/v2/users

Method: GET

|NO Payload|

d) Get all rights

Endpoint: http://<API_Server>/prapi/v2/rights

Method: GET

|NO Payload|

e) Get all sys rights

Endpoint: http://<API_Server>/prapi/v2/sysrights

Method: GET

|NO Payload|

Timezone settings

All the start time, end time, daily break down are by MST timezone(GMT-7).

Resources:

Clone this wiki locally