LinDB on GitHub
  1. Lin Ql
  2. Lin query language

In order to make it easier for user to get started quickly, LinDB provides SQL-like syntax (LinQL) to query data in the cluster, and can also perform related operations on the cluster or database through LinQL.

Database

Create Database

Create Database

Syntax

create database json_config

Example

/*configuration in JSON format*/
create database {\"option\":{\"intervals\":[{\"interval\":\"10s\",\"retention\":\"30d\"},{\"interval\":\"5m\",\"retention\":\"3M\"},{\"interval\":\"1h\",\"retention\":\"2y\"}],\"autoCreateNS\":true,\"behead\":\"1h\",\"ahead\":\"1h\"},\"name\":\"system_monitor\",\"storage\":\"/lindb-storage\",\"numOfShard\":3,\"replicaFactor\":2}

Example: create a database named system_monitor, allocate 3 shards and 2 replicas to store in /lindb-storage storage cluster.

{
   "name":"system_monitor",
   "storage":"/lindb-storage",
   "numOfShard":3,
   "replicaFactor":2,
   "option":{
      "intervals":[
         {
            "interval":"10s",
            "retention":"30d"
         },
         {
            "interval":"5m",
            "retention":"3M"
         },
         {
            "interval":"1h",
            "retention":"2y"
         }
      ],
      "autoCreateNS":true,
      "behead":"1h",
      "ahead":"1h"
   }
}

Configuration field description.

FieldDescription
nameRequired, database name
storageRequired, Storage cluster(Storage Namespace)
numOfShardRequired, number of shards
replicaFactorRequired, number of replicas for each shard
option.intervalsRequired, data storage calculation period and storage ttl, max length is 3(Day/Month/Year)
option.beheadHow long in the past data can be written
option.aheadHow long in the future data can be written

Show Database

Get all database names of current cluster

Syntax

show databases

Show Schema

Get all database schemas of current cluster

Syntax

show schemas

Delete Database

Delete database configuration and data in Storage cluster

Syntax

drop database database_name

Broker

Create Broker Cluster

After deployment of Broker cluster, it should be registered to the Root cluster for providing multiple IDCs/Regions services.

Syntax

create broker json_config

Example

/*Broker Configuration in JSON format*/
create broker {\"config\":{\"namespace\":\"/lindb-broker\",\"timeout\":10,\"dialTimeout\":10,\"leaseTTL\":10,\"endpoints\":[\"http://192.168.1.10:2379\"]}}

Show Brker Cluster

Get configuration of current alive Broker cluster

Syntax

show brokers

Storage

Create Storage Cluster

After deployment of storage cluster, it should be registered to the broker cluster for providing storage services.

The storage node will self-register its cluster configuration to the broker cluster when it starts. If self-registration fails, it's still able to be registered manually.

Syntax

create storage json_config

Example

/*Storage Configuration in JSON format*/
create storage {\"config\":{\"namespace\":\"/lindb-storage\",\"timeout\":10,\"dialTimeout\":10,\"leaseTTL\":10,\"endpoints\":[\"http://192.168.1.10:2379\"]}}

Show Storage Cluster

Get configuration of current alive Storage cluster

Syntax

show storages

Query Data

The premise is that the database to be queried has been selected (database name is specified when executing using USE or calling API).

TIP

If namespace is not specified, the default namespace: default-ns will be used

Metric Query

Use Select Query data from the specified database

Syntax

explain? 
select 
    fields|expr 
from metric (on namespace)?
(where where_condition)? 
(group by {tag_key | time_expr})?

Example

/* select from all ip nodes */
select idle,iowait from monitor.system.cpu_stat group by ip;

/* select from a single node whose ip is 192.168.1.10 */
select idle,iowait from monitor.system.cpu_stat where ip="192.168.1.10";

/* select from multi nodes whose ip startwith 192.168 */
select idle from monitor.system.cpu_stat where ip like '192.168.*' group by ip;

/* select from a single node whose ip is 192.168.1.10, then multiply the value with 100 */
select usage * 100.0 as usage_percent from monitor.system.cpu_stat where ip="192.168.1.10";

/* select orders of the last one hour with interval(1 minute)*/
select sum(order) from order.system.monitoring where time>now()-1h group by time(1m);

/* select total orders of the last one hour with interval(1 hour, based on query time range), return one data point*/
select sum(order) from order.system.monitoring where time>now()-1h group by time();

/* show statistics of processing select query, such as how many Series were found、time-cost of each steps、network traffic */
explain select sum(order) from order.system.monitoring where time>now()-1h group by time(1m);

Metric Meta query

use Show to query meta of metrics

Namespace

get namespaces of this cluster

Syntax

show namespaces (where namespace = prefix)? (limit n)?

Example

/* get 100 Namespace with ascii asecending */
show namespaces;

/* get 10 namespaces with order prefix*/
show namespaces where namespace = order limit 10;

Metric

get metric-names of current cluster

Syntax

show metrics (on namespace)? (where metric = prefix)? (limit n)?

Example

/* get 100 metrics of default-ns with ascii asecending */
show metrics;

/* get 10 metrics of system namespace with cpu prefix*/
show metrics on system where metric = cpu limit 10;

Field

get fields of current metric

Syntax

show fields from metric (on namespace)?

Example

/* get all fields of system.cpu_stat */
show fields from system.cpu_stat;

Tag Key

get all tag-keys of metric

Syntax

show tag keys from metric (on namespace)?

Example

/* get all tag keys from system.cpu_stat */
show tag keys from system.cpu_stat on system;

Tag Value

get all tag-values of the tag-key

Syntax

show tag values from metric (on namespace)? with key = key_value (where tag_key=value)? (limit n)?

Example

/* get 100 tag values from system.cpu_stat with ip tag-key */
show tag values from system.cpu_stat with key=ip;

/* get 20 tag values from system.cpu_stat with ip startswith 192.168 */
show tag values from system.cpu_stat with key=ip where ip=192.168 limit 20;

Status Query

use Show to query status of the cluster

Master

query status of current master node

Syntax

show master

Root

query status of alive roots of current cluster

Syntax

show root alive

Broker

query status of alive brokers of current cluster

Syntax

show broker alive

Storage

query status of current storage cluster and status of each storage node

Syntax

show storage alive

Replication

query replication status of current cluster

Syntax

/*get WAL status of each replica*/
show replication where storage='/lindb-cluster' and database='_internal';

/*get write status of each replica*/
show memory database where storage='/lindb-cluster' and database='_internal';

Request

get running LQL of current cluster

Syntax

show requests

Metadata

Query metadata information of cluster coordination, including information stored in ETCD and information in current of each state machine in memory.

Type Query

get all Metadata type and storing keys in ETCD

Syntax

show metadata types

The metadata currently provided is as follows, for specific metadata usage and metadata-based cluster scheduling, please refer to Cluster Coordination

RoleTypePathDescription
RootDatabaseConfig/database/configLogic Database Configuration synced by each Root node
LiveNode/live/nodesCurrent Alive Root Node
BrokerStateBrokerStateStatus of Current Broker Cluster
BrokerDatabaseConfig/database/configDatabase Configuration synced by each Broker node
LiveNode/live/nodesCurrent Alive Broker Node
StorageState/storage/stateStatus of Current Storage Cluster
MasterDatabaseConfig/database/configThe master allocates shards and replicas based on the database configuration
Master/master/nodeCurrent Master Node
ShardAssigment/database/assignDatabase Shard and Replicas
StorageConfig/storage/configStorage cluster configuration
StorageState/storage/stateStorage Status of current cluster
StorageLiveNode/live/nodesAlive Nodes of Storage Cluster
ShardAssigment/database/assignShard status and Replica status of current Storage Cluster

The metadata storage media are as follows:

  • state_repo: Persisted storing in ETCD;
  • state_machine: Coordinates to the state machine memory of the corresponding node according to the information in ETCD;

Data Query

Query the metadata value in the corresponding storage medium.

Syntax

show Broker|Master|Storage metadata from state_repo|state_machine where where_condition

Example

/*1: query alive storage nodes in `/lindb-storage`*/
show Storage metadata from state_repo where type='LiveNode' and storage='/lindb-storage';

/*2: query cluster configurtion in Master's State Machine*/
show Master metadata from state_machine where type='DatabaseConfig';

/*3: get Metadata of Storage Cluster in ETCD */
show Broker metadata from state_repo where type='StorageState';

/*4: GET Metdata of Storage Cluster in Broker's State Machine,
  comparsion with 3 to check coordination */
show Broker metadata from state_machine where type='StorageState';

/*5: GET State of Storage Cluster in Root's State Machine */
show Root metadata from state_machine where type='BrokerState' and broker='/lindb-broker';

Self Monitoring

get statistics metrics of nodes in current Clusters

Syntax

show Root|Broker|Storage metric where where_condition

Example

/*get Root's CPU/Memory Stats*/
show root metric where metric in ('lindb.monitor.system.cpu_stat','lindb.monitor.system.mem_stat');

/*get Broker's CPU/Memory Stats*/
show broker metric where metric in ('lindb.monitor.system.cpu_stat','lindb.monitor.system.mem_stat');

/*get disk stats from storage cluster `/lindb-storage` */
show storage metric where storage='/lindb-storage' and metric in ('lindb.monitor.system.disk_usage_stats';

KeyWords

TIP

If keywords are used as metric names/labels/fields, double quote them.

ALIVE          AND            AS             ASC            AVG            BETWEEN        
BROKER         BROKERS        BY             COUNT          CREATE         DATASBAE       
DATASBAES      DAY            DESC           DROP           EXPLAIN        FIELD          
FIELDS         FILL           FIRST          FOR            FROM           FUTURE_TTL     
GROUP          HAVING         HOUR           ID             IN             INFO           
INTERVAL       INTERVAL_NAME  IS             KEY            KEYS           KILL           
LAST           LIKE           LIMIT          LOG            MASTER         MAX            
MEMORY         METADATA       META_TTL       METRIC         METRICS        MIN            
MINUTE         MONTH          NAMESPACE      NAMESPACES     NODE           NOT            
NOW            NULL           ON             OR             ORDER          PASTTL         
PREVIOUS       PROFILE        QUANTILE       QUERIES        QUERY          RATE           
REPLICATION    REQUEST        REQUESTS       ROOT           SCHEMAS        SECOND         
SELECT         SET            SHARD          SHOW           STATE_MACHINE  STATE_REPO     
STATS          STDDEV         STORAGE        STORAGES       SUM            TAG            
TIME           TTL            TYPE           TYPES          UPDATE         USE            
VALUE          VALUES         WEEK           WHERE          WITH           WITH_VALUE     
YEAR           
Previous
Java