Help Center> Data Lake Insight> SQL Syntax Reference> SQL Syntax of Batch Jobs> Creating a Datasource Connection with an OpenTSDB Table> Creating a DLI Table and Associating It with OpenTSDB
View PDF

Creating a DLI Table and Associating It with OpenTSDB

Function

Run the CREATE TABLE statement to create the DLI table and associate it with the existing metric in OpenTSDB. This syntax supports the OpenTSDB of CloudTable and MRS.

Prerequisites

Before creating a DLI table and associating it with OpenTSDB, you need to create a datasource connection. For details about operations on the management console, see Basic Datasource Connections and Enhanced Datasource Connections.

Syntax

1
2
3
4
5
 
CREATE TABLE [IF NOT EXISTS] UQUERY_OPENTSDB_TABLE_NAME
  USING OPENTSDB OPTIONS (
  'host' = 'xx;xx',
  'metric' = 'METRIC_NAME',
  'tags' = 'TAG1,TAG2');

Keyword

Table 1 CREATE TABLE parameter description

Parameter

Description

host

OpenTSDB IP address.

Before obtaining the OpenTSDB IP address, you need to create a datasource connection. For details about how to create a datasource connection on the management console, see Basic Datasource Connections and Enhanced Datasource Connections.

  • After successfully created a connection, you can access the CloudTable OpenTSDB by entering the IP address of the OpenTSDB.
  • You can also access the MRS OpenTSDB. If you have created an enhanced datasource connection, enter the IP address and port number of the node where the OpenTSDB is located. The format is IP:PORT. If the OpenTSDB has multiple nodes, enter one of the node IP addresses. If you have created a basic datasource connection, enter the returned IP address. For details about operations on the management console, see Basic Datasource Connections.

metric

Name of the metric in OpenTSDB corresponding to the DLI table to be created.

tags

Tags corresponding to the metric. The tags are used for classification, filtering, and quick retrieval. You can set 1 to 8 tags, which are separated by commas (,). The parameter value includes values of all tagKs in the corresponding metric.

Precautions

When creating a DLI table, you do not need to specify the timestamp and value fields. The system automatically builds the following fields based on the specified tags. The fields TAG1 and TAG2 are specified by tags.

  • TAG1 String
  • TAG2 String
  • timestamp Timestamp
  • value double

Example

1
2
3
4
5
 
CREATE table opentsdb_table
  USING OPENTSDB OPTIONS (
  'host' = 'opentsdb-3xcl8dir15m58z3.cloudtable.com:4242',
  'metric' = 'city.temp',
  'tags' = 'city,location');