Row to Column Conversion

Problem Analysis

Given the following employee data:

1,PK,RD,1
2,XIAOAI,RD,1
3,XIAOHONG,RD,2
4,XIAOZHANG,QA,1
5,XIAOLI,QA,2
6,XIAOFANG,QA,2

Group by department and gender to get:

QA,1    XIAOZHANG
QA,2    XIAOLI|XIAOFANG
RD,1    PK|XIAOAI
RD,2    XIAOHONG

Required Functions

• concat: String concatenation
• concat_ws: String concatenation with separator
• collect_list: Collects values into a list
• collect_set: Collects distinct values into a set

Implementation

CREATE TABLE employee_data(
    emp_id STRING,
    emp_name STRING,
    department STRING,
    gender STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INPATH '/data/employee_data.txt' INTO TABLE employee_data;

SELECT 
    dept_gender,
    CONCAT_WS("|", COLLECT_SET(emp_name)) AS employees
FROM (
    SELECT 
        emp_name,
        CONCAT_WS(",", department, gender) AS dept_gender 
    FROM employee_data
) temp
GROUP BY dept_gender;

Column to Row Conversion

Problem Analysis

Given data with comma-separated courses:

Saddam  MapReduce,Hive,Spark,Flink
XIAOAI  Hadoop,Hbase,Kafka

Convert to individual rows:

Saddam  MapReduce
Saddam  Hive
Saddam  Spark
Saddam  Flink
XIAOAI  Hadoop
XIAOAI  Hbase
XIAOAI  Kafka

Required Functions

• split: Splits string into array
• explode: Converst array elements to rows

Implementation

CREATE TABLE employee_courses(
    emp_name STRING,
    course_list STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

LOAD DATA LOCAL INPATH '/data/employee_courses.txt' INTO TABLE employee_courses;

SELECT 
    emp_name,
    individual_course
FROM employee_courses
LATERAL VIEW EXPLODE(SPLIT(course_list, ",")) course_table AS individual_course;

Window Functions for Cumulative Calculations

Problem Analysis

Given web site access data:

domain,date,visits
imooc.com,2024-01-02,5
imooc.com,2024-01-03,15
google.com,2024-01-03,5
imooc.com,2024-01-04,8
google.com,2024-01-02,25
imooc.com,2024-01-05,5
imooc.com,2024-02-01,4
imooc.com,2024-02-02,6
google.com,2024-02-01,10
google.com,2024-02-01,10
imooc.com,2024-03-05,9
imooc.com,2024-03-06,5
google.com,2024-03-01,11
google.com,2024-03-02,10

Calculate for each domain: monthly visits, maximum monthly visits, and cumulative visits up to each month.

Required Functions

• date_format: Formats date values
• Window functions for cumulative calculations

Implementation

CREATE TABLE website_access(
    domain STRING,
    access_date STRING,
    visit_count INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INPATH '/data/website_access.txt' INTO TABLE website_access;

-- Extract month from date
SELECT 
    domain,
    DATE_FORMAT(access_date, 'yyyy-MM') AS month_year
FROM website_access;

-- Calculate monthly visits
SELECT 
    domain,
    DATE_FORMAT(access_date, 'yyyy-MM') AS month_year,
    SUM(visit_count) AS monthly_visits
FROM website_access
GROUP BY domain, DATE_FORMAT(access_date, 'yyyy-MM');

-- Create temporary table for monthly aggregates
CREATE TABLE monthly_aggregates AS 
SELECT 
    domain,
    DATE_FORMAT(access_date, 'yyyy-MM') AS month_year,
    SUM(visit_count) AS monthly_visits
FROM website_access
GROUP BY domain, DATE_FORMAT(access_date, 'yyyy-MM');

-- Self-join approach for cumulative calculations
CREATE TABLE cumulative_calc AS 
SELECT
    a.domain AS domain_a,
    a.month_year AS month_a,
    a.monthly_visits AS visits_a,
    b.domain AS domain_b,
    b.month_year AS month_b,
    b.monthly_visits AS visits_b
FROM monthly_aggregates a 
JOIN monthly_aggregates b ON a.domain = b.domain;