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.

Field	Description
name	Required, database name
storage	Required, Storage cluster(Storage Namespace)
numOfShard	Required, number of shards
replicaFactor	Required, number of replicas for each shard
option.intervals	Required, data storage calculation period and storage ttl, max length is 3(Day/Month/Year)
option.behead	How long in the past data can be written
option.ahead	How 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).

​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。

Role	Type	Path	Description
Root	DatabaseConfig	/database/config	Logic Database Configuration synced by each Root node
	LiveNode	/live/nodes	Current Alive Root Node
	BrokerState	BrokerState	Status of Current Broker Cluster
Broker	DatabaseConfig	/database/config	Database Configuration synced by each Broker node
	LiveNode	/live/nodes	Current Alive Broker Node
	StorageState	/storage/state	Status of Current Storage Cluster
Master	DatabaseConfig	/database/config	The master allocates shards and replicas based on the database configuration
	Master	/master/node	Current Master Node
	ShardAssigment	/database/assign	Database Shard and Replicas
	StorageConfig	/storage/config	Storage cluster configuration
	StorageState	/storage/state	Storage Status of current cluster
Storage	LiveNode	/live/nodes	Alive Nodes of Storage Cluster
	ShardAssigment	/database/assign	Shard 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

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