LinQL

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.

This document describes the Syntaxs of LinQL and common scenarios for using 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}

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

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 storage

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).

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);

/* 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

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
BrokerDatabaseConfig/database/configDatabase Configuration synced by each Broker node
LiveNode/live/nodesCurrent Alive Broker Node
StorageState/storage/stateStatus of Current Storage Nodes
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';

Self Monitoring

get statistics metrics of nodes in current Clusters

Syntax

show Broker|Storage metric where where_condition

Example

/*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

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

ALIVE          AND            AS             ASC            AVG            BETWEEN
BROKER         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       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