grafana关联mongodb数据库 - Thu, Mar 28, 2019

使用grafana展示mongodb数据库时间序列内容

1. 概述

默认情况下，grafana是不支持显示mongodb数据库的内容。因为mongodb默认是没有时间字段，如果要想让grafana支持时间序列的展示，需要在插入数据时设置时间字段。

2. 设计架构

3. simplejson插件

grafana默认支持的datasource有Graphite, InfluxDB, OpenTSDB, Prometheus, Elasticsearch, CloudWatch。但是也有很多DataSource以插件的形式存在，由于mongodb是bson形式进行存储，所以我们使用simplejson作为DataSource插件，进行数据转换。

https://grafana.com/plugins/grafana-simple-json-datasource

安装方法：

grafana-cli plugins install grafana-simple-json-datasource

4. mongodb代理

mongodb-proxy需要实现simplejson插件必要的api。

/ 必须返回200 OK,创建datasource时测试。

/search 搜索能够进行查询的label。

/query 查询时间序列。

/annotations 返回注释。

/tag-keys 返回标签键。

/tag-values 返回标签值。

使用swagger生成服务端代码，直接查询数据库。

swagger文件

---
swagger: "2.0"
info:
  description: This is a sample mongodb proxy server.
  version: 1.0.0
  title: Swagger of MongoDB for Grafana plugin
  contact:
    email: wind.kaisa@gmail.com
  license:
    name: MIT License
    url: https://mit-license.org/
host: localhost:8080
basePath: /
consumes:
  - application/json
produces:
  - application/json
tags:
- name: SimpleJSON
  description: Operations about SimpleJSON
schemes:
- http
paths:
  /:
    get:
      tags:
      - SimpleJSON
      security: []
      summary: test connection
      description: should return 200 ok. Used for "Test connection" on the datasource config page.
      operationId: TestDatasource
      responses:
        200:
          description: connection successfully.
  /search:
    post:
      tags:
      - SimpleJSON
      security: []
      summary: find metric options
      description: used by the find metric options on the query tab in panels.
      operationId: MetricFindQuery
      parameters:
      - in: body
        name: options
        description: The options to query.
        schema:
          $ref: '#/definitions/Target'
      responses:
        200:
          description: find metric options successfully.
          schema:
            type: object
  /annotations:
    post:
      tags:
      - SimpleJSON
      security: []
      summary: get annotations
      description: used by dashboards to get annotations.
      operationId: AnnotationQuery
      parameters:
      - in: body
        name: options
        description: The options to query.
        schema:
          $ref: '#/definitions/Target'
      responses:
        200:
          description: get annotations successfully.
          schema:
            $ref: '#/definitions/Annotations'
  /query:
    post:
      tags:
      - SimpleJSON
      security: []
      summary: query data
      description: used by panels to get data
      operationId: Query
      parameters:
      - in: body
        name: options
        description: The options to query.
        schema:
          $ref: '#/definitions/Query'
      responses:
        200:
          description: query data successfully.
          schema:
            $ref: '#/definitions/Timeseries'
definitions:
  Query:
    description: query data requests.
    type: object
    properties:
      range:
        $ref: '#/definitions/Range'
      interval:
        type: string
      intervalMs:
        type: integer
      targets:
        type: array
        items:
          $ref: '#/definitions/Target'
      format:
        type: string
        enum:
          - json
        default: json
      maxDataPoints:
        type: integer
  Target:
    description: data target.
    type: object
    properties:
      target:
        type: string
      refId:
        type: string
      type:
        type: string
        enum:
          - timeserie
          - table
        default: timeserie
  Annotations:
    description: encodes the information provided by Grafana in its requests.
    type: object
    properties:
      range:
        $ref: '#/definitions/Range'
      annotation:
        $ref: '#/definitions/Annotation'
  Range:
    description: Range specifies the time range the request is valid for. 
    type: object
    properties:
      from:
        type: string
        format: date-time
      to:
        type: string
        format: date-time
  Annotation:
    description: Annotation is the object passed by Grafana when it fetches annotations.
    type: object
    properties:
      name:
        type: string
      datasource:
        type: string
      iconColor:
        type: string
      enable:
        type: boolean
      showLine:
        type: boolean
      query:
        type: string
  Timeseries:
    description: Request object passed to datasource.query function
    type: array
    items:
      $ref: '#/definitions/Timeserie'
  Timeserie:
    type: object
    properties:
      target:
        type: string
      datapoints:
        type: array
        items:
          type: array
          items:
            type: object

5. mongodb聚合查询

查询数据库生成时间序列需要用到聚合查询

$match匹配要查询的时间段

$group聚合时间段的数据

$substract时间段相减，算出group的时间差

$mod取模

$project字段是否显示，不显示为0，显示为1

$sort排序

db.getCollection('products').aggregate([
{
    '$match':{
        'created_at': {'$gte': "2019-03-13T10:30:56Z", '$lte': "2019-03-14T09:25:50Z"}
    }
},
{
    '$group': {
        '_id': {
            '$subtract': [
                {'$toDate': '$created_at'},
                {
                    '$mod': [
                        {'$toLong': {'$toDate': '$created_at'}},
                        60 * 60 * 24 * 1000
                    ]
                }
            ]
        },
        'total': {'$sum': 1}
    }
},
{
    '$project': { '_id': 0, 'timestamp': '$_id', 'total': 1}
},
{
    '$sort': { 'timestamp': 1 }
}
])