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