This is an automated email from the ASF dual-hosted git repository.
fjy pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-druid.git
The following commit(s) were added to refs/heads/master by this push:
new 4c211ab update sys table docs (#6955)
4c211ab is described below
commit 4c211ab2b4c29795f5b61fb1404055d3706d0d35
Author: Surekha <surekha.saha...@imply.io>
AuthorDate: Thu Jan 31 08:51:39 2019 -0800
update sys table docs (#6955)
* update sys table docs
* Capitalize SQL
---
docs/content/querying/sql.md | 30 +++++++++++++++++++++++++-----
1 file changed, 25 insertions(+), 5 deletions(-)
diff --git a/docs/content/querying/sql.md b/docs/content/querying/sql.md
index 257963d..6c9674c 100644
--- a/docs/content/querying/sql.md
+++ b/docs/content/querying/sql.md
@@ -562,10 +562,6 @@ SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_SCHEMA = 'druid' AND TABLE_
## SYSTEM SCHEMA
The "sys" schema provides visibility into Druid segments, servers and tasks.
-For example to retrieve all segments for datasource "wikipedia", use the query:
-```sql
-SELECT * FROM sys.segments WHERE datasource = 'wikipedia'
-```
### SEGMENTS table
Segments table provides details on all Druid segments, whether they are
published yet or not.
@@ -587,6 +583,26 @@ Segments table provides details on all Druid segments,
whether they are publishe
|is_realtime|Boolean is represented as long type where 1 = true, 0 = false. 1
if this segment is being served on any type of realtime tasks|
|payload|JSON-serialized data segment payload|
+For example to retrieve all segments for datasource "wikipedia", use the query:
+
+```sql
+SELECT * FROM sys.segments WHERE datasource = 'wikipedia'
+```
+
+Another example to retrieve segments total_size, avg_size, avg_num_rows and
num_segments per datasource:
+
+```sql
+SELECT
+ datasource,
+ SUM("size") AS total_size,
+ CASE WHEN SUM("size") = 0 THEN 0 ELSE SUM("size") / (COUNT(*) FILTER(WHERE
"size" > 0)) END AS avg_size,
+ CASE WHEN SUM(num_rows) = 0 THEN 0 ELSE SUM("num_rows") / (COUNT(*)
FILTER(WHERE num_rows > 0)) END AS avg_num_rows,
+ COUNT(*) AS num_segments
+FROM sys.segments
+GROUP BY 1
+ORDER BY 2 DESC
+```
+
### SERVERS table
Servers table lists all data servers(any server that hosts a segment). It
includes both Historicals and Peons.
@@ -602,6 +618,7 @@ Servers table lists all data servers(any server that hosts
a segment). It includ
|max_size|Max size in bytes this server recommends to assign to segments see
[druid.server.maxSize](#../configuration/index.html#Historical-General-Configuration)|
To retrieve information about all servers, use the query:
+
```sql
SELECT * FROM sys.servers;
```
@@ -617,6 +634,7 @@ SERVER_SEGMENTS is used to join servers with segments table
JOIN between "servers" and "segments" can be used to query the number of
segments for a specific datasource,
grouped by server, example query:
+
```sql
SELECT count(segments.segment_id) as num_segments from sys.segments as
segments
INNER JOIN sys.server_segments as server_segments
@@ -649,10 +667,12 @@ check out [ingestion tasks](#../ingestion/tasks.html)
|error_msg|Detailed error message in case of FAILED tasks|
For example, to retrieve tasks information filtered by status, use the query
+
```sql
-SELECT * FROM sys.tasks where status='FAILED';
+SELECT * FROM sys.tasks WHERE status='FAILED';
```
+Note that sys tables may not support all the Druid SQL Functions.
## Server configuration
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@druid.apache.org
For additional commands, e-mail: commits-h...@druid.apache.org
