SQL metadata tables

Druid Brokers infer table and column metadata for each datasource from segments loaded in the cluster, and use this to plan SQL queries. This metadata is cached on Broker startup and also updated periodically in the background through . Background metadata refreshing is triggered by segments entering and exiting the cluster, and can also be throttled through configuration.

Druid exposes system information through special system tables. There are two such schemas available: Information Schema and Sys Schema. Information schema provides details about table and column types. The “sys” schema provides information about Druid internals like segments/tasks/servers.

You can access table and column metadata through JDBC using , or through the INFORMATION_SCHEMA tables described below. For example, to retrieve metadata for the Druid datasource “foo”, use the query:

INFORMATION_SCHEMA.SCHEMATA provides a list of all known schemas, which include druid for standard Druid Table datasources, lookup for , sys for the virtual System metadata tables, and INFORMATION_SCHEMA for these virtual tables. Tables are allowed to have the same name across different schemas, so the schema may be included in an SQL statement to distinguish them, e.g. lookup.table vs druid.table.

TABLES table

INFORMATION_SCHEMA.TABLES provides a list of all known tables and schemas.

INFORMATION_SCHEMA.COLUMNS provides a list of all known columns across all tables and schema.

FROM INFORMATION_SCHEMA.COLUMNS
WHERE "TABLE_NAME" = 'foo'

SYSTEM SCHEMA

The “sys” schema provides visibility into Druid segments, servers and tasks.

SEGMENTS table

Segments table provides details on all Druid segments, whether they are published yet or not.

For example, to retrieve all currently active segments for datasource “wikipedia”, use the query:

WHERE datasource = 'wikipedia'
AND is_active = 1

Another example to retrieve segments total_size, avg_size, avg_num_rows and num_segments per datasource:

This query goes a step further and shows the overall profile of available, non-realtime segments across buckets of 1 million rows each for the foo datasource:

SELECT ABS("num_rows" /  1000000) as "bucket",
  COUNT(*) as segments,
  SUM("size") / 1048576 as totalSizeMiB,
  MIN("size") / 1048576 as minSizeMiB,
  AVG("size") / 1048576 as averageSizeMiB,
  MAX("size") / 1048576 as maxSizeMiB,
  SUM("num_rows") as totalRows,
  MIN("num_rows") as minRows,
  AVG("num_rows") as averageRows,
  (AVG("size") / AVG("num_rows"))  as avgRowSizeB
WHERE is_available = 1 AND is_realtime = 0 AND "datasource" = `foo`
GROUP BY 1
ORDER BY 1

If you want to retrieve segment that was compacted (ANY compaction):

SELECT * FROM sys.segments WHERE is_active = 1 AND last_compaction_state IS NOT NULL

Servers table lists all discovered servers in the cluster.

To retrieve information about all servers, use the query:

SELECT * FROM sys.servers;

SERVER_SEGMENTS table

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:

SELECT count(segments.segment_id) as num_segments from sys.segments as segments
INNER JOIN sys.server_segments as server_segments
ON segments.segment_id  = server_segments.segment_id
INNER JOIN sys.servers as servers
ON servers.server = server_segments.server
WHERE segments.datasource = 'wikipedia'
GROUP BY servers.server;

The tasks table provides information about active and recently-completed indexing tasks. For more information check out the documentation for ingestion tasks.

For example, to retrieve tasks information filtered by status, use the query

SUPERVISORS table

The supervisors table provides information about supervisors.