| <code>APPROX_QUANTILE_DS</code>. Only standard SQL functions can be used.</p></blockquote><h3 class="anchor anchorWithStickyNavbar_LWe7" id="segments-table">SEGMENTS table<a href="#segments-table" class="hash-link" aria-label="Direct link to SEGMENTS table" title="Direct link to SEGMENTS table"></a></h3><p>Segments table provides details on all Druid segments, whether they are published yet or not.</p><table><thead><tr><th>Column</th><th>Type</th><th>Notes</th></tr></thead><tbody><tr><td>segment_id</td><td>VARCHAR</td><td>Unique segment identifier</td></tr><tr><td>datasource</td><td>VARCHAR</td><td>Name of datasource</td></tr><tr><td>start</td><td>VARCHAR</td><td>Interval start time (in ISO 8601 format)</td></tr><tr><td>end</td><td>VARCHAR</td><td>Interval end time (in ISO 8601 format)</td></tr><tr><td>size</td><td>BIGINT</td><td>Size of segment in bytes</td></tr><tr><td>version</td><td>VARCHAR</td><td>Version string (generally an ISO8601 timestamp corresponding to when the segment set was first started). Higher version means the more recently created segment. Version comparing is based on string comparison.</td></tr><tr><td>partition_num</td><td>BIGINT</td><td>Partition number (an integer, unique within a datasource+interval+version; may not necessarily be contiguous)</td></tr><tr><td>num_replicas</td><td>BIGINT</td><td>Number of replicas of this segment currently being served</td></tr><tr><td>num_rows</td><td>BIGINT</td><td>Number of rows in this segment, or zero if the number of rows is not known.<br><br>This row count is gathered by the Broker in the background. It will be zero if the Broker has not gathered a row count for this segment yet. For segments ingested from streams, the reported row count may lag behind the result of a <code>count(*)</code> query because the cached <code>num_rows</code> on the Broker may be out of date. This will settle shortly after new rows stop being written to that particular segment.</td></tr><tr><td>is_active</td><td>BIGINT</td><td>True for segments that represent the latest state of a datasource.<br><br>Equivalent to <code>(is_published = 1 AND is_overshadowed = 0) OR is_realtime = 1</code>. In steady state, when no ingestion or data management operations are happening, <code>is_active</code> will be equivalent to <code>is_available</code>. However, they may differ from each other when ingestion or data management operations have executed recently. In these cases, Druid will load and unload segments appropriately to bring actual availability in line with the expected state given by <code>is_active</code>.</td></tr><tr><td>is_published</td><td>BIGINT</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 if this segment has been published to the metadata store and is marked as used. See the <a href="/docs/26.0.0/design/architecture#segment-lifecycle">segment lifecycle documentation</a> for more details.</td></tr><tr><td>is_available</td><td>BIGINT</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 if this segment is currently being served by any data serving process, like a Historical or a realtime ingestion task. See the <a href="/docs/26.0.0/design/architecture#segment-lifecycle">segment lifecycle documentation</a> for more details.</td></tr><tr><td>is_realtime</td><td>BIGINT</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 if this segment is <em>only</em> served by realtime tasks, and 0 if any Historical process is serving this segment.</td></tr><tr><td>is_overshadowed</td><td>BIGINT</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 if this segment is published and is <em>fully</em> overshadowed by some other published segments. Currently, <code>is_overshadowed</code> is always 0 for unpublished segments, although this may change in the future. You can filter for segments that "should be published" by filtering for <code>is_published = 1 AND is_overshadowed = 0</code>. Segments can briefly be both published and overshadowed if they were recently replaced, but have not been unpublished yet. See the <a href="/docs/26.0.0/design/architecture#segment-lifecycle">segment lifecycle documentation</a> for more details.</td></tr><tr><td>shard_spec</td><td>VARCHAR</td><td>JSON-serialized form of the segment <code>ShardSpec</code></td></tr><tr><td>dimensions</td><td>VARCHAR</td><td>JSON-serialized form of the segment dimensions</td></tr><tr><td>metrics</td><td>VARCHAR</td><td>JSON-serialized form of the segment metrics</td></tr><tr><td>last_compaction_state</td><td>VARCHAR</td><td>JSON-serialized form of the compaction task's config (compaction task which created this segment). May be null if segment was not created by compaction task.</td></tr></tbody></table><p>For example, to retrieve all currently active segments for datasource "wikipedia", use the query:</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">segments</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> datasource </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'wikipedia'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> is_active </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>Another example to retrieve segments total_size, avg_size, avg_num_rows and num_segments per datasource:</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> datasource</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> total_size</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">CASE</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">WHEN</span><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">THEN</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ELSE</span><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">COUNT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">></span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">END</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> avg_size</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">CASE</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">WHEN</span><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">num_rows</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">THEN</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">ELSE</span><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">COUNT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> num_rows </span><span class="token operator" style="color:rgb(137, 221, 255)">></span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">END</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> avg_num_rows</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">COUNT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> num_segments</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">segments</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> is_active </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">2</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DESC</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>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 <code>foo</code> datasource:</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> ABS</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1000000</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"bucket"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">COUNT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> segments</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1048576</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> totalSizeMiB</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">MIN</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1048576</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> minSizeMiB</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">AVG</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1048576</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> averageSizeMiB</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">MAX</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1048576</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> maxSizeMiB</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> totalRows</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">MIN</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> minRows</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">AVG</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> averageRows</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">MAX</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> maxRows</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token function" style="color:rgb(130, 170, 255)">AVG</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"size"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">/</span><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">AVG</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"num_rows"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">as</span><span class="token plain"> avgRowSizeB</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">segments</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> is_available </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> is_realtime </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"datasource"</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token identifier punctuation" style="color:rgb(199, 146, 234)">`</span><span class="token identifier">foo</span><span class="token identifier punctuation" style="color:rgb(199, 146, 234)">`</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>If you want to retrieve segment that was compacted (ANY compaction):</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">segments </span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> is_active </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> last_compaction_state </span><span class="token operator" style="color:rgb(137, 221, 255)">IS</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">NOT</span><span class="token plain"> </span><span class="token boolean" style="color:rgb(255, 88, 116)">NULL</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>or if you want to retrieve segment that was compacted only by a particular compaction spec (such as that of the auto compaction):</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">segments </span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> is_active </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">1</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> last_compaction_state </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'CompactionState{partitionsSpec=DynamicPartitionsSpec{maxRowsPerSegment=5000000, maxTotalRows=9223372036854775807}, indexSpec={bitmap={type=roaring}, dimensionCompression=lz4, metricCompression=lz4, longEncoding=longs, segmentLoader=null}}'</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="servers-table">SERVERS table<a href="#servers-table" class="hash-link" aria-label="Direct link to SERVERS table" title="Direct link to SERVERS table"></a></h3><p>Servers table lists all discovered servers in the cluster.</p><table><thead><tr><th>Column</th><th>Type</th><th>Notes</th></tr></thead><tbody><tr><td>server</td><td>VARCHAR</td><td>Server name in the form host:port</td></tr><tr><td>host</td><td>VARCHAR</td><td>Hostname of the server</td></tr><tr><td>plaintext_port</td><td>BIGINT</td><td>Unsecured port of the server, or -1 if plaintext traffic is disabled</td></tr><tr><td>tls_port</td><td>BIGINT</td><td>TLS port of the server, or -1 if TLS is disabled</td></tr><tr><td>server_type</td><td>VARCHAR</td><td>Type of Druid service. Possible values include: COORDINATOR, OVERLORD, BROKER, ROUTER, HISTORICAL, MIDDLE_MANAGER or PEON.</td></tr><tr><td>tier</td><td>VARCHAR</td><td>Distribution tier see <a href="/docs/26.0.0/configuration/#historical-general-configuration">druid.server.tier</a>. Only valid for HISTORICAL type, for other types it's null</td></tr><tr><td>current_size</td><td>BIGINT</td><td>Current size of segments in bytes on this server. Only valid for HISTORICAL type, for other types it's 0</td></tr><tr><td>max_size</td><td>BIGINT</td><td>Max size in bytes this server recommends to assign to segments see <a href="/docs/26.0.0/configuration/#historical-general-configuration">druid.server.maxSize</a>. Only valid for HISTORICAL type, for other types it's 0</td></tr><tr><td>is_leader</td><td>BIGINT</td><td>1 if the server is currently the 'leader' (for services which have the concept of leadership), otherwise 0 if the server is not the leader, or the default long value (0 or null depending on <code>druid.generic.useDefaultValueForNull</code>) if the server type does not have the concept of leadership</td></tr></tbody></table><p>To retrieve information about all servers, use the query:</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">servers</span><span class="token punctuation" style="color:rgb(199, 146, 234)">;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="server_segments-table">SERVER_SEGMENTS table<a href="#server_segments-table" class="hash-link" aria-label="Direct link to SERVER_SEGMENTS table" title="Direct link to SERVER_SEGMENTS table"></a></h3><p>SERVER_SEGMENTS is used to join servers with segments table</p><table><thead><tr><th>Column</th><th>Type</th><th>Notes</th></tr></thead><tbody><tr><td>server</td><td>VARCHAR</td><td>Server name in format host:port (Primary key of <a href="#servers-table">servers table</a>)</td></tr><tr><td>segment_id</td><td>VARCHAR</td><td>Segment identifier (Primary key of <a href="#segments-table">segments table</a>)</td></tr></tbody></table><p>JOIN between "servers" and "segments" can be used to query the number of segments for a specific datasource, |
| check out the documentation for <a href="/docs/26.0.0/ingestion/tasks">ingestion tasks</a>.</p><table><thead><tr><th>Column</th><th>Type</th><th>Notes</th></tr></thead><tbody><tr><td>task_id</td><td>VARCHAR</td><td>Unique task identifier</td></tr><tr><td>group_id</td><td>VARCHAR</td><td>Task group ID for this task, the value depends on the task <code>type</code>. For example, for native index tasks, it's same as <code>task_id</code>, for sub tasks, this value is the parent task's ID</td></tr><tr><td>type</td><td>VARCHAR</td><td>Task type, for example this value is "index" for indexing tasks. See <a href="/docs/26.0.0/ingestion/tasks">tasks-overview</a></td></tr><tr><td>datasource</td><td>VARCHAR</td><td>Datasource name being indexed</td></tr><tr><td>created_time</td><td>VARCHAR</td><td>Timestamp in ISO8601 format corresponding to when the ingestion task was created. Note that this value is populated for completed and waiting tasks. For running and pending tasks this value is set to 1970-01-01T00:00:00Z</td></tr><tr><td>queue_insertion_time</td><td>VARCHAR</td><td>Timestamp in ISO8601 format corresponding to when this task was added to the queue on the Overlord</td></tr><tr><td>status</td><td>VARCHAR</td><td>Status of a task can be RUNNING, FAILED, SUCCESS</td></tr><tr><td>runner_status</td><td>VARCHAR</td><td>Runner status of a completed task would be NONE, for in-progress tasks this can be RUNNING, WAITING, PENDING</td></tr><tr><td>duration</td><td>BIGINT</td><td>Time it took to finish the task in milliseconds, this value is present only for completed tasks</td></tr><tr><td>location</td><td>VARCHAR</td><td>Server name where this task is running in the format host:port, this information is present only for RUNNING tasks</td></tr><tr><td>host</td><td>VARCHAR</td><td>Hostname of the server where task is running</td></tr><tr><td>plaintext_port</td><td>BIGINT</td><td>Unsecured port of the server, or -1 if plaintext traffic is disabled</td></tr><tr><td>tls_port</td><td>BIGINT</td><td>TLS port of the server, or -1 if TLS is disabled</td></tr><tr><td>error_msg</td><td>VARCHAR</td><td>Detailed error message in case of FAILED tasks</td></tr></tbody></table><p>For example, to retrieve tasks information filtered by status, use the query</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">tasks </span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">status</span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token string" style="color:rgb(195, 232, 141)">'FAILED'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="supervisors-table">SUPERVISORS table<a href="#supervisors-table" class="hash-link" aria-label="Direct link to SUPERVISORS table" title="Direct link to SUPERVISORS table"></a></h3><p>The supervisors table provides information about supervisors.</p><table><thead><tr><th>Column</th><th>Type</th><th>Notes</th></tr></thead><tbody><tr><td>supervisor_id</td><td>VARCHAR</td><td>Supervisor task identifier</td></tr><tr><td>state</td><td>VARCHAR</td><td>Basic state of the supervisor. Available states: <code>UNHEALTHY_SUPERVISOR</code>, <code>UNHEALTHY_TASKS</code>, <code>PENDING</code>, <code>RUNNING</code>, <code>SUSPENDED</code>, <code>STOPPING</code>. Check <a href="/docs/26.0.0/development/extensions-core/kafka-supervisor-operations">Kafka Docs</a> for details.</td></tr><tr><td>detailed_state</td><td>VARCHAR</td><td>Supervisor specific state. (See documentation of the specific supervisor for details, e.g. <a href="/docs/26.0.0/development/extensions-core/kafka-ingestion">Kafka</a> or <a href="/docs/26.0.0/development/extensions-core/kinesis-ingestion">Kinesis</a>)</td></tr><tr><td>healthy</td><td>BIGINT</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 indicates a healthy supervisor</td></tr><tr><td>type</td><td>VARCHAR</td><td>Type of supervisor, e.g. <code>kafka</code>, <code>kinesis</code> or <code>materialized_view</code></td></tr><tr><td>source</td><td>VARCHAR</td><td>Source of the supervisor, e.g. Kafka topic or Kinesis stream</td></tr><tr><td>suspended</td><td>BIGINT</td><td>Boolean represented as long type where 1 = true, 0 = false. 1 indicates supervisor is in suspended state</td></tr><tr><td>spec</td><td>VARCHAR</td><td>JSON-serialized supervisor spec</td></tr></tbody></table><p>For example, to retrieve supervisor tasks information filtered by health status, use the query</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sys</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">supervisors </span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> healthy</span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token number" style="color:rgb(247, 140, 108)">0</span><span class="token punctuation" style="color:rgb(199, 146, 234)">;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div></div></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/26.0.0/querying/sql-query-context"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">SQL query context</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/26.0.0/querying/sql-translation"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">SQL query translation</div></a></nav></div></div><div class="col col--3"><div class="tableOfContents_bqdL thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#information-schema" class="table-of-contents__link toc-highlight">INFORMATION SCHEMA</a><ul><li><a href="#schemata-table" class="table-of-contents__link toc-highlight">SCHEMATA table</a></li><li><a href="#tables-table" class="table-of-contents__link toc-highlight">TABLES table</a></li><li><a href="#columns-table" class="table-of-contents__link toc-highlight">COLUMNS table</a></li></ul></li><li><a href="#system-schema" class="table-of-contents__link toc-highlight">SYSTEM SCHEMA</a><ul><li><a href="#segments-table" class="table-of-contents__link toc-highlight">SEGMENTS table</a></li><li><a href="#servers-table" class="table-of-contents__link toc-highlight">SERVERS table</a></li><li><a href="#server_segments-table" class="table-of-contents__link toc-highlight">SERVER_SEGMENTS table</a></li><li><a href="#tasks-table" class="table-of-contents__link toc-highlight">TASKS table</a></li><li><a href="#supervisors-table" class="table-of-contents__link toc-highlight">SUPERVISORS table</a></li></ul></li></ul></div></div></div></div></main></div></div><footer class="footer"><div class="container container-fluid"><div class="footer__bottom text--center"><div class="margin-bottom--sm"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--light_HNdA footer__logo"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--dark_i4oU footer__logo"></div><div class="footer__copyright">Copyright © 2023 Apache Software Foundation. Except where otherwise noted, licensed under CC BY-SA 4.0. Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></div></footer></div> |