Segment Cache View

Use snap$cachesegments view to get an idea of the segment files on cluster hosts. Output structure of the view is:

indexName : StringType
segmentName : StringType
partitionValues : StringType
creationTime : StringType
lastAccessTime : StringType
size : LongType
host : StringType

Example

select * from `snap$cachesegments`

List of hosts and size of indexes/segment distribution

select host, count(segmentName) as numSegments, sum(size)/(1000*1000*1000) as totalSizeGB. 
avg(size/(1000*1000)) as avgSegmentSizeMB
from `snap$cachesegments`
group by host 
order by 1, 3

Distribution of segments by host for a specific index

select host, count(segmentName) as numSegments, sum(size)/(1000*1000*1000) as totalSizeGB. 
avg(size/(1000*1000)) as avgSegmentSizeMB
from `snap$cachesegments`
where indexName='sales.sales_snap'
group by host 
order by 1, 3