• Integration
  • Output
  • Query API

SQL API

This document was translated by GPT-4

# 1. Introduction

This provides a unified SQL interface for querying all types of observation data, which can be used as a DataSource for Grafana or to implement your own GUI based on this.

# 2. SQL Service Endpoint

To get the service endpoint port number:

port=$(kubectl get --namespace deepflow -o jsonpath="{.spec.ports[0].nodePort}" services deepflow-server)
1

# 3. SQL Query Statements

# 3.1 Get All Databases

SQL statement:

show databases
1

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "sql=show databases"
1
2

# 3.2 Get All Tables from a Specific Database

SQL statement:

show tables
1

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "db=${db_name}" \
    --data-urlencode "sql=show tables"
1
2
3

# 3.3 Get Tags from a Specified Data Table

SQL statement:

show tags from ${table_name}
1

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "db=${db_name}" \
    --data-urlencode "sql=show tags from ${table_name}"
1
2
3

Output example:

{
  "OPT_STATUS": "SUCCESS",
  "DESCRIPTION": "",
  "result": {
    "columns": [
      "name",
      "client_name",
      "server_name",
      "display_name",
      "type" // int, int_enum, string, string_enum, resource_name, resource_id, ip
    ],
    "values": [
      ["chost", "chost_0", "chost_1", "Cloud Server", "resource_id"],
      [
        "chost_name",
        "chost_name_0",
        "chost_name_1",
        "Cloud Server Name",
        "resource_name"
      ]
    ]
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 3.4 Get Tag Values from a Specific Tag

# 3.4.1 Get All Values of a Tag

SQL statement:

show tag ${tag_name} values from ${table_name}
1

You can also use the limit and offset keywords in the above statement to reduce the number of returned values:

show tag ${tag_name} values from ${table_name} limit 100 offset 100
1

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "db=${db_name}" \
    --data-urlencode "sql=show tag ${tag_name} values from ${table_name}"
1
2
3

Output example:

{
  "OPT_STATUS": "SUCCESS",
  "DESCRIPTION": "",
  "result": {
    "columns": ["value", "display_name", "uid"],
    "values": [[348, "deepflow", "i-2ze3bpa0o5cy8edplozi"]]
  }
}
1
2
3
4
5
6
7
8

# 3.4.2 Filter by the Tag Name

Notice that the tag values returned above will yield three columns: value, display_name, uid. We can use this information for filtering, for example:

show tag ${tag_name} values from ${table_name} where display_name like '*abc*'
1

The API call method and output examples are the same as above.

# 3.4.3 Filter by Associating with Other Tags

Sometimes, we want to use tags for associated filtering to reduce the scope of potential value options. In this case, we can choose to query a data table, filter on Tag1 while aggregating on Tag2. For example, if we want to query the name of all pods in pod_cluster="cluster1":

SELECT pod FROM `network.1m` WHERE pod_cluster = 'cluster1' GROUP BY pod
1

The above statement will utilize the pod_cluster field in the network.1m table in the flow_metrics database to filter and group pod candidate items. Of course, we can also meet this requirement by querying any table in DeepFlow, but we should try to avoid using tables with a large amount of data. Additionally, we can speed up the search by adding other dimensions such as time to the SQL:

SELECT pod FROM `network.1m` WHERE pod_cluster = 'cluster1' AND time > 1234567890 GROUP BY pod
1

Note: data can only be found in flow_metrics if there has ever been flow in the pod (and HostNetwork is not used). After integrating Prometheus or Telegraf data, we can also use the consistently present indicators therein to help obtain tag values. For example, we can use the Prometheus indicators in ext_metrics to meet the above requirement:

SELECT pod FROM `prometheus.kube_pod_start_time` WHERE pod_cluster = 'cluster1' GROUP BY pod
1

In Grafana, we can also leverage the above capability to implement linkage filtering of Variable candidate items. For example, using a custom Variable $cluster and the built-in Variable $**from, $**to (opens new window) we can filter another Variable pod:

  • when the value of cluster is id, use $cluster:

    cluster = [1, 2]
result: 1, 2
    1
    2
    // Add 5 minutes before and after the time range to avoid frequent changes of candidates
SELECT pod_id as `value`, pod as `display_name` FROM `network.1m` WHERE pod_cluster IN ($cluster) AND time >= ${__from:date:seconds}-500 AND time <= ${__to:date:seconds}+500 GROUP BY `value`
    1
    2

  • when the value of cluster is name, use ${cluster:singlequote}:

    cluster = [deepflow-a, deepflow-b]
result: 'deepflow-a', 'deepflow-b'
    1
    2
    SELECT pod as `value`, pod as `display_name` FROM `network.1m` WHERE pod_cluster IN (${cluster:singlequote}) AND  time >= ${__from:date:seconds}-500 AND time <= ${__to:date:seconds}+500 GROUP BY `value`
    1

# 3.5 Get Metrics from a Specified Data Table

SQL statement:

show metrics from ${table_name}
1

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "db=${db_name}" \
    --data-urlencode "sql=show metrics from ${table_name}"
1
2
3

# 3.6 Query Observation Data

SQL statement:

SELECT col_1, col_2, col_3 \
FROM   tbl_1 \
WHERE  col_4 = y \
GROUP BY col_1, col_2 \
HAVING   col_5 > 100 \
ORDER BY col_3 \
LIMIT 100
1
2
3
4
5
6
7

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "db=${db_name}" \
    --data-urlencode "sql=${sql}"
1
2
3

When db=flow_metric, you need to specify the data precision by --data-urlencode "data_precision=${data_precision}", the options for data_precision are 1m and 1s.

# 4. SQL Query Functions

# 4.1 Functions Supported by Tag

  • enum
    • Description: enum(tap_side) transforms the enumeration field into a value
    • Example: SELECT enum(tap_side) ..., ... WHERE enum(tap_side) = 'xxx' ...
    • Note: Only tag types string_enum, int_enum are supported

# 4.2 Functions Supported by Metrics

To get all functions, execute the following SQL statement:

show metric function
1

API call method:

curl -XPOST "http://${deepflow_server_node_ip}:${port}/v1/query/" \
    --data-urlencode "sql=show metric function"
1
2

# 5. SQL Syntax

  • Left values do not support spaces, single quotes, back quotes
  • Single quotes in right values need to be escaped with a backslash \