Hive is not only a SQL query tool, but also a reflection of big data system design capability. Whether it's dealing with massive log data or building a data center for an upper-level analytics platform, Hive can't be used without efficient query logic design, code writing, and system architecture awareness. This article organizes some common technical interviews in the Hive Direction of high-frequency questions (Hive interview questions), to help you from the big factory interview!
Counting UVs and Active Users with Hive SQL (with window function)
Topic description:
There is a table of user access logs user_log, fields are as follows:
| field name | typology | descriptive |
|---|---|---|
| User_id | STRING | User ID |
| event_time | TIMESTAMP | Duration of behavior |
| event_type | STRING | Behavior types such as 'login', 'click', 'logout' |
Please use Hive SQL query:
- Unique visitors (UV) per day
- Number of active users per day (refers to the number of users who logged in and engaged in arbitrary behavior)
Refer to SQL:
-- Unique visitors per day
SELECT
TO_DATE(event_time) AS dt,
COUNT(DISTINCT user_id) AS daily_uv
FROM user_log
GROUP BY TO_DATE(event_time).
-- daily_active_users (find users who have behaved after logging in first)
WITH logged_in AS (
SELECT user_id, MIN(event_time) AS login_time
FROM user_log
WHERE event_type = 'login'
GROUP BY user_id
), active_users AS (
active_users AS (
SELECT l.user_id
FROM user_log u
JOIN logged_in l
ON u.user_id = l.user_id
AND u.event_time >= l.login_time
)
SELECT
TO_DATE(event_time) AS dt, COUNT(DISTINCT user_id) AS dt, COUNT(DISTINCT user_id)
COUNT(DISTINCT user_id) AS active_user_count
FROM user_log
WHERE user_id IN (SELECT user_id FROM active_users)
GROUP BY TO_DATE(event_time).How to design a Hive data model to support "user retention analysis"?
Question background:
The requirement is to design an intermediate table that supports retention analysis to analyze whether a new user still has access records on day 1, day 3, and day 7 after signing up.
Inspection points:
time window processing
User Lifecycle Calculator
System design capabilities: data modeling, scheduling ideas, performance optimization
Modeling Recommendations:
Original table:user_log(user_id, event_time, event_type)
Registration form:user_register(user_id, register_time)
Creating a wide table user_retention(user_id, register_date, d1, d3, d7)
d1/d3/d7 fields indicate whether the corresponding day is active or inactive (1/0)
How to build an "Exception User Identification System" with Hive?
Demand:
The log data are user behavior logs, each record contains user_id, event_time, action, and IP address.
You will need to identify "high frequency operation in a short period of time" accounts (suspected swiping/abnormal bot) on a daily basis.
Requires overall solution (table structure, ETL ideas, core SQL logic)
Idea:
Analyzes the frequency of operation per unit of time (e.g. 1 minute) and sets the threshold value
Using Hive's window function Or collect_list and other properties
Output visual dashboards (extension points) in conjunction with Redis/ES
Sample SQL:
WITH action_counts AS (
SELECT
user_id,
FROM_UNIXTIME(UNIX_TIMESTAMP(event_time), 'yyyy-MM-dd HH:mm') AS minute_slot, COUNT(*) AS action_counts AS
COUNT(*) AS action_count
FROM user_log
GROUP BY user_id, FROM_UNIXTIME(UNIX_TIMESTAMP(event_time), 'yyyy-MM-dd HH:mm')
)
SELECT *
FROM action_counts
WHERE action_count >= 20.Conclusion
Interviews for Hive-related positions are not just about writing SQL, but also about your ability to model data systems, optimize queries, understand user behavior, and other multidimensional skills. Familiarizing yourself with these Hive interview questions and practicing them in real life will greatly improve your chances of getting a data position in big companies like Byte, Ali, Shopee, and so on.
Want to systematically improve your Hive coding skills and brush up without getting sidetracked?
Welcome to contact ProgramhelpWe provide OA programming ghostwriting, interview system design accompaniment, VO technical assistance (voice forwarding/real-time prompts) and other services to help you efficiently get to the bank!
