Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to optimize count (distinct) in Hive", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let me lead you to study and learn how to optimize count (distinct) in Hive.

Problem description

COUNT (DISTINCT xxx) can easily cause data skew in hive. In view of this situation, there are many optimization methods on the Internet, and I will not repeat them here.

But sometimes, "data tilt" is almost inevitable. Let's give an example:

Suppose that the client session information visiting a website M is recorded in the table detail_sdk_session, that is, if user An opens the app client, a session information will be recorded in the table. The granularity of the table is "once" session, in which each session records the user's unique identification uuid,uuid is a very long string, assuming its length is 64 bits. The demand now is to count the number of active users every day-"monthly active users" (those who visited app in that month are active users). Let's take January 2016 as an example, and now represents the current date.

The easiest way

The question is logically simple and SQL is easy to write, such as:

SELECT COUNT (DISTINCT uuid) FROM detail_sdk_session tWHERE t.date > = '2016-01-01' AND t.date = '2016-01-01' AND partition_date = '2016-01-01' AND partition_date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.