tencent cloud

All product documents
TencentDB for CTSDB
DocumentationTencentDB for CTSDBHTTP API ReferencesCTSDB 2.0SQL-Like Query Support
SQL-Like Query Support
Download PDF
Last updated: 2022-06-09 10:57:11
SQL-Like Query Support
Last updated: 2022-06-09 10:57:11
Download PDF
CTSDB 2.0 supports using SQL-like statements for querying, which is only for the convenience of beginners. If you are concerned about the query time, we recommend you use CTSDB's native query statements.

Request address

The address is the instance IP and port, such as 10.13.20.15:9200, which can be obtained in the console.

Request path and method

Path: _nlpcn/sql. Method: GET

Request parameters

You can add the pretty parameter value when querying to get the response in a sorted format. For details, see the sample.

Request content

Queries mainly include regular queries and aggregate queries. For the specific request content, see the samples.

Response content

You need to judge whether a request is successful based on the error field. If the response content contains the error field, the request failed. For the error details, see the error field description. If the parameter or metric name is incorrect, you need to check and correct it on your own.

Sample code for curl

For specific queries, see the following samples:
The metric structure used by all samples is as follows:
   {
       "ctsdb_test" : {
         "tags" : {
           "region" : "string"
         },
         "time" : {
           "name" : "timestamp",
           "format" : "epoch_millis"
         },
         "fields" : {
           "cpuUsage" : "float",
           "diskUsage" : "string",
           "dcpuUsage" : "integer"
         },
         "options" : {
           "expire_day" : 7,
           "refresh_interval" : "10s",
           "number_of_shards" : 5
         }
       }
   }
Sample code of regular query for curl:
curl -u root:le201909 -H 'Content-Type: application/json' -XGET 172.16.345.14:9201/_nlpcn/sql?pretty -d 'select docvalue(cpuUsage) from ctsdb_test limit 1'
Sample response of regular query:
{
  "took" : 22,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "ctsdb_test@1646064000000_1",
        "_type" : "_doc",
        "_id" : "XMzBRX8BfqojiIOn8jFa",
        "_score" : 1.0,
        "fields" : {
          "cpuUsage" : [
            10.0
          ]
        }
      }
    ]
  }
}
Note: During regular query, docvalue should be added to the field to be queried, so that the instance can get the data from its columnar storage.
Sample code of regular query with search criteria for curl:
curl -u root:le201909 -H 'Content-Type: application/json' -XGET 172.16.345.14:9201/_nlpcn/sql?pretty -d 'select docvalue(cpuUsage) from ctsdb_test where region="shanghai" limit 1'
Sample response of regular query with search criteria:
{
  "took" : 13,
  "timed_out" : false,
  "_shards" : {
    "total" : 10,
    "successful" : 10,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "ctsdb_test@1646064000000_1",
        "_type" : "_doc",
        "_id" : "XszBRX8BfqojiIOn8jFb",
        "_score" : 0.0,
        "fields" : {
          "cpuUsage" : [
            30.0
          ]
        }
      }
    ]
  }
}
Note: For regular queries with search criteria, there is no need to add docvalue to the fields used as criteria.
Sample code of aggregate query grouped by tag for curl:
curl -u root:le201909 -H 'Content-Type: application/json' -XGET 172.16.345.14:9201/_nlpcn/sql?pretty -d 'select max(cpuUsage) from ctsdb_test group by region'
Sample response of aggregate query grouped by tag:
{
  "took" : 33,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "region" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "beijing",
          "doc_count" : 1,
          "MAX(cpuUsage)" : {
            "value" : 20.0
          }
        },
        {
          "key" : "chengdu",
          "doc_count" : 1,
          "MAX(cpuUsage)" : {
            "value" : 10.0
          }
        },
        {
          "key" : "shanghai",
          "doc_count" : 1,
          "MAX(cpuUsage)" : {
            "value" : 30.0
          }
        }
      ]
    }
  }
}
Note: For aggregate queries grouped by tag, there is no need to add docvalue to the field used for grouping.
Sample code of aggregate query grouped by time for curl:
curl -u root:le201909 -H 'Content-Type: application/json' -XGET 172.16.345.14:9201/_nlpcn/sql?pretty -d 'select max(cpuUsage) from ctsdb_test GROUP BY date_histogram(field="timestamp","interval"="1d")'
Sample response of aggregate query grouped by time:
{
  "took" : 30,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "date_histogram(field=timestamp,interval=1d)" : {
      "buckets" : [
        {
          "key_as_string" : "2022-03-01 00:00:00",
          "key" : 1646092800000,
          "doc_count" : 3,
          "MAX(cpuUsage)" : {
            "value" : 30.0
          }
        }
      ]
    }
  }
}
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

Contact Us

Contact our sales team or business advisors to help your business.

Contact Us
Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

Submit a Ticket
7x24 Phone Support
中国香港
+852 800 906 020 (無料)
アメリカ
+1 844 606 0804 (無料)
イギリス
+44 808 196 4551 (無料)
カナダ
+1 888 605 7930 (無料)
オーストラリア
+61 1300 986 386 (無料)
EdgeOne ホットライン
+852 300 80699
ローカルサービスホットラインが続々追加中です