OpenTSDB JSON Protocol
Introduction
A JSON string is used in OpenTSDB JSON to represent one or more rows of data, for example: For example:
[
{
"metric": "sys.cpu.nice",
"timestamp": 1346846400,
"value": 18,
"tags": {
"host": "web01",
"dc": "lga"
}
},
{
"metric": "sys.cpu.nice",
"timestamp": 1346846400,
"value": 9,
"tags": {
"host": "web02",
"dc": "lga"
}
}
]
Similar to OpenTSDB line protocol, metric
will be used as the STable name, timestamp
is the timestamp to be used, value
represents the metric collected, tags
are the tag sets.
Please refer to OpenTSDB HTTP API for more details.
- In JSON protocol, strings will be converted to NCHAR type and numeric values will be converted to double type.
- The rule of table name
- The child table name is created automatically in a rule to guarantee its uniqueness.
- You can configure
smlAutoChildTableNameDelimiter
in taos.cfg to specify a delimiter between tag values as the table names. For example, you setsmlAutoChildTableNameDelimiter=-
in taos.cfg, when you insertst,t0=cpu1,t1=4 c1=3 1626006833639000000
, the child table will becpu1-4
- You can configure
smlChildTableName
in taos.cfg to specify a tag value as the table names if the tag value is unique globally. For example, if a tag is calledtname
and you setsmlChildTableName=tname
in taos.cfg, when you insertst,tname=cpu1,t1=4 c1=3 1626006833639000000
, the child tablecpu1
will be created automatically. Note that if multiple rows have the same tname but different tag_set values, the tag_set of the first row is used to create the table and the others are ignored
Examples
- Java
- Python
- Go
- Node.js
- C#
- C
package com.taos.example;
import com.taosdata.jdbc.SchemalessWriter;
import com.taosdata.jdbc.enums.SchemalessProtocolType;
import com.taosdata.jdbc.enums.SchemalessTimestampType;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JSONProtocolExample {
private static Connection getConnection() throws SQLException {
String jdbcUrl = "jdbc:TAOS://localhost:6030?user=root&password=taosdata";
return DriverManager.getConnection(jdbcUrl);
}
private static void createDatabase(Connection conn) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.execute("CREATE DATABASE IF NOT EXISTS test");
stmt.execute("USE test");
}
}
private static String getJSONData() {
return "[{\"metric\": \"meters.current\", \"timestamp\": 1648432611249, \"value\": 10.3, \"tags\": {\"location\": \"California.SanFrancisco\", \"groupid\": 2}}," +
" {\"metric\": \"meters.voltage\", \"timestamp\": 1648432611249, \"value\": 219, \"tags\": {\"location\": \"California.LosAngeles\", \"groupid\": 1}}, " +
"{\"metric\": \"meters.current\", \"timestamp\": 1648432611250, \"value\": 12.6, \"tags\": {\"location\": \"California.SanFrancisco\", \"groupid\": 2}}," +
" {\"metric\": \"meters.voltage\", \"timestamp\": 1648432611250, \"value\": 221, \"tags\": {\"location\": \"California.LosAngeles\", \"groupid\": 1}}]";
}
public static void main(String[] args) throws SQLException {
try (Connection conn = getConnection()) {
createDatabase(conn);
SchemalessWriter writer = new SchemalessWriter(conn);
String jsonData = getJSONData();
writer.write(jsonData, SchemalessProtocolType.JSON, SchemalessTimestampType.NOT_CONFIGURED);
}
}
}
import json
import taos
from taos import SmlProtocol, SmlPrecision
lines = [{"metric": "meters.current", "timestamp": 1648432611249, "value": 10.3, "tags": {"location": "California.SanFrancisco", "groupid": 2}},
{"metric": "meters.voltage", "timestamp": 1648432611249, "value": 219,
"tags": {"location": "California.LosAngeles", "groupid": 1}},
{"metric": "meters.current", "timestamp": 1648432611250, "value": 12.6,
"tags": {"location": "California.SanFrancisco", "groupid": 2}},
{"metric": "meters.voltage", "timestamp": 1648432611250, "value": 221, "tags": {"location": "California.LosAngeles", "groupid": 1}}]
def get_connection():
return taos.connect()
def create_database(conn):
conn.execute("CREATE DATABASE test keep 36500")
conn.execute("USE test")
def insert_lines(conn):
global lines
lines = json.dumps(lines)
# note: the first parameter must be a list with only one element.
affected_rows = conn.schemaless_insert(
[lines], SmlProtocol.JSON_PROTOCOL, SmlPrecision.NOT_CONFIGURED)
print(affected_rows) # 4
if __name__ == '__main__':
connection = get_connection()
try:
create_database(connection)
insert_lines(connection)
finally:
connection.close()
package main
import (
"log"
"github.com/taosdata/driver-go/v3/af"
)
func prepareDatabase(conn *af.Connector) {
_, err := conn.Exec("CREATE DATABASE test")
if err != nil {
panic(err)
}
_, err = conn.Exec("USE test")
if err != nil {
panic(err)
}
}
func main() {
conn, err := af.Open("localhost", "root", "taosdata", "", 6030)
if err != nil {
log.Fatalln("fail to connect, err:", err)
}
defer conn.Close()
prepareDatabase(conn)
payload := `[{"metric": "meters.current", "timestamp": 1648432611249, "value": 10.3, "tags": {"location": "California.SanFrancisco", "groupid": 2}},
{"metric": "meters.voltage", "timestamp": 1648432611249, "value": 219, "tags": {"location": "California.LosAngeles", "groupid": 1}},
{"metric": "meters.current", "timestamp": 1648432611250, "value": 12.6, "tags": {"location": "California.SanFrancisco", "groupid": 2}},
{"metric": "meters.voltage", "timestamp": 1648432611250, "value": 221, "tags": {"location": "California.LosAngeles", "groupid": 1}}]`
err = conn.OpenTSDBInsertJsonPayload(payload)
if err != nil {
log.Fatalln("insert error:", err)
}
}
const taos = require("@tdengine/client");
const conn = taos.connect({
host: "localhost",
});
const cursor = conn.cursor();
function createDatabase() {
cursor.execute("CREATE DATABASE test");
cursor.execute("USE test");
}
function insertData() {
const lines = [
{
metric: "meters.current",
timestamp: 1648432611249,
value: 10.3,
tags: { location: "California.SanFrancisco", groupid: 2 },
},
{
metric: "meters.voltage",
timestamp: 1648432611249,
value: 219,
tags: { location: "California.LosAngeles", groupid: 1 },
},
{
metric: "meters.current",
timestamp: 1648432611250,
value: 12.6,
tags: { location: "California.SanFrancisco", groupid: 2 },
},
{
metric: "meters.voltage",
timestamp: 1648432611250,
value: 221,
tags: { location: "California.LosAngeles", groupid: 1 },
},
];
cursor.schemalessInsert(
[JSON.stringify(lines)],
taos.SCHEMALESS_PROTOCOL.TSDB_SML_JSON_PROTOCOL,
taos.SCHEMALESS_PRECISION.TSDB_SML_TIMESTAMP_NOT_CONFIGURED
);
}
try {
createDatabase();
insertData();
} finally {
cursor.close();
conn.close();
}
using TDengine.Driver;
using TDengine.Driver.Client;
namespace TDengineExample
{
internal class OptsJsonExample
{
public static void Main(string[] args)
{
var builder =
new ConnectionStringBuilder("host=localhost;port=6030;username=root;password=taosdata");
using (var client = DbDriver.Open(builder))
{
client.Exec("CREATE DATABASE test WAL_RETENTION_PERIOD 3600");
client.Exec("use test");
string[] lines =
{
"[{\"metric\": \"meters.current\", \"timestamp\": 1648432611249, \"value\": 10.3, \"tags\": {\"location\": \"California.SanFrancisco\", \"groupid\": 2}}," +
" {\"metric\": \"meters.voltage\", \"timestamp\": 1648432611249, \"value\": 219, \"tags\": {\"location\": \"California.LosAngeles\", \"groupid\": 1}}, " +
"{\"metric\": \"meters.current\", \"timestamp\": 1648432611250, \"value\": 12.6, \"tags\": {\"location\": \"California.SanFrancisco\", \"groupid\": 2}}," +
" {\"metric\": \"meters.voltage\", \"timestamp\": 1648432611250, \"value\": 221, \"tags\": {\"location\": \"California.LosAngeles\", \"groupid\": 1}}]"
};
client.SchemalessInsert(lines, TDengineSchemalessProtocol.TSDB_SML_JSON_PROTOCOL,
TDengineSchemalessPrecision.TSDB_SML_TIMESTAMP_MILLI_SECONDS, 0, ReqId.GetReqId());
}
}
}
}
int main() {
TAOS *taos = taos_connect("localhost", "root", "taosdata", "", 6030);
if (taos == NULL) {
printf("failed to connect to server\n");
exit(EXIT_FAILURE);
}
executeSQL(taos, "DROP DATABASE IF EXISTS test");
executeSQL(taos, "CREATE DATABASE test");
executeSQL(taos, "USE test");
char *line =
"[{\"metric\": \"meters.current\", \"timestamp\": 1648432611249, \"value\": 10.3, \"tags\": {\"location\": "
"\"California.SanFrancisco\", \"groupid\": 2}},{\"metric\": \"meters.voltage\", \"timestamp\": 1648432611249, "
"\"value\": 219, \"tags\": {\"location\": \"California.LosAngeles\", \"groupid\": 1}},{\"metric\": \"meters.current\", "
"\"timestamp\": 1648432611250, \"value\": 12.6, \"tags\": {\"location\": \"California.SanFrancisco\", \"groupid\": "
"2}},{\"metric\": \"meters.voltage\", \"timestamp\": 1648432611250, \"value\": 221, \"tags\": {\"location\": "
"\"California.LosAngeles\", \"groupid\": 1}}]";
char *lines[] = {line};
TAOS_RES *res = taos_schemaless_insert(taos, lines, 1, TSDB_SML_JSON_PROTOCOL, TSDB_SML_TIMESTAMP_NOT_CONFIGURED);
if (taos_errno(res) != 0) {
printf("failed to insert schema-less data, reason: %s\n", taos_errstr(res));
} else {
int affectedRow = taos_affected_rows(res);
printf("successfully inserted %d rows\n", affectedRow);
}
taos_free_result(res);
taos_close(taos);
taos_cleanup();
}
// output:
// successfully inserted 4 rows
2 STables will be created automatically and each STable has 2 rows of data in the above sample code.
taos> use test;
Database changed.
taos> show stables;
name |
=================================
meters_current |
meters_voltage |
Query OK, 2 row(s) in set (0.001954s)
taos> select * from `meters_current`;
_ts | _value | groupid | location |
===================================================================================================================
2022-03-28 09:56:51.249 | 10.300000000 | 2.000000000 | California.SanFrancisco |
2022-03-28 09:56:51.250 | 12.600000000 | 2.000000000 | California.SanFrancisco |
Query OK, 2 row(s) in set (0.004076s)
Query Examples
If you want query the data of "tags": {"location": "California.LosAngeles", "groupid": 1}, here is the query SQL:
SELECT * FROM `meters_current` WHERE location = "California.LosAngeles" AND groupid = 3;