Q:

Skew vs Partition in Hive

After going through Skewed tables in Hive, I got confused with the way the data is stored for Skewed tables and the way it is treated for partitioned tables. Can someone clearly state the differences with marked examples as to where these two concepts
Skewed Tables and Partitioned Tables
coincide and where they differ?
Please do provide example.

A:

Purpose of both Skewed and Partitioned tables are same, to optimize query. However, way they do and when they are applicable is bit different.
Let's assume we are building fitness tracker like Strava and users data is sent to us constantly. 

Partitioning: It's quite normal to partition this kind of data by date and time like /year=2017/month=10/day=12 etc. This way any date and time based filters will be very fast eg.

SELECT col FROM table WHERE year=2017 AND month=10

Skewed table: It can happen that some of the users are not only sending gym workout but also walking steps count, geo-location, bike ride, calorie consumption, sleep and what not. These are very few users but amount of data they send is very large compared to average users. So if you want to query by UserId, it would be slow:

SELECT col FROM table WHERE year=2017 AND month=10 AND userid=20
However, skewed table can help here. Let's say those active users were 20, 23, 25. Now you can create storage data like:
/year=2017/month=10/day=12/userid=20
/year=2017/month=10/day=12/userid=23
/year=2017/month=10/day=12/userid=27
/year=2017/month=10/day=12/userid=others
As you can see, these fitness freaks got their own directory. This will result in fast query when you run same query above (filter by user id).
There is bit more to it, refer to this documentation.

