Support Questions
Find answers, ask questions, and share your expertise

How to check if table is skewed or not?

Explorer

How can i check if Hive table is a simple managed table or Skewed table ??

1 REPLY 1

@Bal P

You could verify the skew table from 'desc formatted <table_name>'.

hive> desc formatted T;
OK
# col_name            	data_type           	comment             	 
c1                  	string              	                    
c2                  	string              	                    	 	 
# Detailed Table Information	 	 
Database:           	default             	 
Owner:              	hive                	 
CreateTime:         	Fri Oct 05 09:16:47 UTC 2018
LastAccessTime:     	UNKNOWN             	 
Protect Mode:       	None                	 
Retention:          	0                   	 
Location:           	hdfs://xxx:8020/apps/hive/warehouse/t	 
Table Type:         	MANAGED_TABLE       	 
Table Parameters:	 	 
	COLUMN_STATS_ACCURATE	{\"BASIC_STATS\":\"true\"}
	numFiles            	0                   
	numRows             	0                   
	rawDataSize         	0                   
	totalSize           	0                   
	transient_lastDdlTime	1538731007           	 
# Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Skewed Columns:     	[c1]                	 
Skewed Values:      	[[x1]]              	 
Storage Desc Params:	 	 
	serialization.format	1                   
Time taken: 0.899 seconds, Fetched: 34 row(s)

Or, from backend Hive Metastore DB to get list of the tables and their skew columns.

mysql> select S1.SKEWED_COL_NAME,T1.TBL_NAME from SKEWED_COL_NAMES S1, TBLS T1 where S1.SD_ID=T1.SD_ID;
+-----------------+----------+
| SKEWED_COL_NAME | TBL_NAME |
+-----------------+----------+
| c1              | t        |
+-----------------+----------+
1 row in set (0.00 sec)
; ;