大数据教程FG109-Hadoop多维分析与宽表建设实战

# 检查集群硬件配置
[root@fgedu.net.cn ~]# hadoop dfsadmin -report
Configured Capacity: 1099511627776 (1024.0 GB)
Present Capacity: 879609302016 (819.2 GB)
DFS
Remaining: 703687441664 (655.4 GB)
DFS Used: 175921860352 (163.8 GB)
DFS Used%: 20.0%
Under
replicated blocks: 0
Blocks with corrupt replicas: 0
Missing blocks: 0

2.2 存储方案设计

# 创建HDFS目录结构
[root@fgedu.net.cn ~]# hdfs dfs -mkdir -p /user/fgedu/analysis/raw
[root@fgedu.net.cn ~]# hdfs dfs -mkdir -p /user/fgedu/analysis/wide_table
[root@fgedu.net.cn ~]# hdfs dfs -mkdir -p /user/fgedu/analysis/result
[root@fgedu.net.cn ~]# hdfs dfs -mkdir -p /user/fgedu/analysis/archive

# 设置目录权限
[root@fgedu.net.cn ~]# hdfs dfs -chmod -R 755 /user/fgedu/analysis

2.3 多维分析架构设计

推荐使用分层架构：数据采集层、数据处理层、宽表构建层、分析服务层。风哥提示：合理的架构设计可以提高多维分析的效率和可扩展性。

Part03-生产环境项目实施方案

3.1 宽表设计与实现

# 创建宽表
hive> CREATE TABLE wide_sales_table (
sale_id STRING,
order_id STRING,
customer_id STRING,
customer_name STRING,
customer_age INT,
customer_gender STRING,
customer_location STRING,
product_id STRING,
product_name STRING,
product_category STRING,
product_price DOUBLE,
store_id STRING,
store_name STRING,
store_location STRING,
sale_date STRING,
sale_amount DOUBLE,
sale_quantity INT,
payment_method STRING,
dt STRING
)
PARTITIONED BY (dt STRING);

# 填充宽表数据
hive> INSERT OVERWRITE TABLE wide_sales_table PARTITION (dt=’2023-04-08′)
SELECT
s.sale_id,
s.order_id,
c.customer_id,
c.customer_name,
c.age,
c.gender,
c.location,
p.product_id,
p.product_name,
p.category,
p.price,
st.store_id,
st.store_name,
st.location,
s.sale_date,
s.amount,
s.quantity,
s.payment_method,
‘2023-04-08’ AS dt
FROM sales s
JOIN customer c ON s.customer_id = c.customer_id
JOIN product p ON s.product_id = p.product_id
JOIN store st ON s.store_id = st.store_id
WHERE s.sale_date = ‘2023-04-08’;

3.2 多维分析实现

# 使用Hive进行多维分析
hive> — 按产品类别分析销售情况
hive> SELECT
product_category,
SUM(sale_amount) AS total_amount,
SUM(sale_quantity) AS total_quantity,
COUNT(*) AS order_count
FROM wide_sales_table
WHERE dt = ‘2023-04-08’
GROUP BY product_category
ORDER BY total_amount DESC;

— 按地区分析销售情况
hive> SELECT
store_location,
SUM(sale_amount) AS total_amount,
AVG(sale_amount) AS avg_amount,
COUNT(*) AS order_count
FROM wide_sales_table
WHERE dt = ‘2023-04-08’
GROUP BY store_location
ORDER BY total_amount DESC;

— 按时间分析销售趋势
hive> SELECT
substr(sale_date, 1, 10) AS date,
SUM(sale_amount) AS total_amount,
COUNT(*) AS order_count
FROM wide_sales_table
WHERE sale_date BETWEEN ‘2023-04-01’ AND ‘2023-04-08’
GROUP BY substr(sale_date, 1, 10)
ORDER BY date;

3.3 性能优化

# 优化宽表查询性能
hive> set hive.exec.parallel=true;
hive> set hive.exec.parallel.thread.number=8;
hive> set hive.vectorized.execution.enabled=true;
hive> set hive.vectorized.execution.reduce.enabled=true;
hive> set hive.auto.convert.join=true;
hive> set hive.optimize.skewjoin=true;

# 使用分区和分桶
hive> CREATE TABLE wide_sales_table_bucketed (
sale_id STRING,
order_id STRING,
customer_id STRING,
customer_name STRING,
customer_age INT,
customer_gender STRING,
customer_location STRING,
product_id STRING,
product_name STRING,
product_category STRING,
product_price DOUBLE,
store_id STRING,
store_name STRING,
store_location STRING,
sale_date STRING,
sale_amount DOUBLE,
sale_quantity INT,
payment_method STRING
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (product_category) INTO 10 BUCKETS;

Part04-生产案例与实战讲解

4.1 电商销售多维分析

案例背景

某电商平台需要对销售数据进行多维分析，了解不同产品类别、不同地区、不同时间段的销售情况。

实施步骤

构建销售宽表，包含产品、用户、地区等维度
使用Hive进行多维分析
使用Presto进行交互式查询
使用Grafana进行数据可视化

实施效果

多维分析系统上线后，分析人员可以快速获取各种维度的销售数据，分析效率提升了90%。from bigdata视频:www.itpux.com

4.2 用户行为多维分析

# 用户行为多维分析脚本
#!/bin/bash
# user_behavior_analysis.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 导出环境变量
export HADOOP_HOME=/bigdata/app/hadoop
export HIVE_HOME=/bigdata/app/hive
export PATH=$HADOOP_HOME/bin:$HIVE_HOME/bin:$PATH

# 执行用户行为多维分析
hive -f /bigdata/app/scripts/user_behavior_multidimensional.sql

# 检查执行结果
if [ $? -eq 0 ]; then
echo “用户行为多维分析成功”
else
echo “用户行为多维分析失败”
exit 1
fi

4.3 运营指标多维分析

# 运营指标多维分析实现
hive> — 分析用户活跃度
hive> SELECT
dt,
COUNT(DISTINCT user_id) AS active_users,
COUNT(*) AS total_behavior,
SUM(CASE WHEN behavior = ‘purchase’ THEN 1 ELSE 0 END) AS purchase_count,
SUM(CASE WHEN behavior = ‘purchase’ THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id) AS purchase_rate
FROM wide_user_behavior
GROUP BY dt
ORDER BY dt;

— 分析用户留存率
hive> SELECT
first_date,
COUNT(DISTINCT user_id) AS new_users,
COUNT(DISTINCT CASE WHEN next_date = date_add(first_date, 1) THEN user_id END) AS day1_retention,
COUNT(DISTINCT CASE WHEN next_date = date_add(first_date, 7) THEN user_id END) AS day7_retention,
COUNT(DISTINCT CASE WHEN next_date = date_add(first_date, 1) THEN user_id END) / COUNT(DISTINCT user_id) AS
day1_retention_rate,
COUNT(DISTINCT CASE WHEN next_date = date_add(first_date, 7) THEN user_id END) / COUNT(DISTINCT user_id) AS
day7_retention_rate
FROM (
SELECT
user_id,
MIN(dt) AS first_date,
MAX(dt) AS next_date
FROM wide_user_behavior
GROUP BY user_id
) t
GROUP BY first_date
ORDER BY first_date;

Part05-风哥经验总结与分享

5.1 多维分析最佳实践

合理设计宽表：根据业务需求设计合适的宽表结构
分区策略：根据数据特点选择合适的分区策略
索引优化：为常用查询字段建立索引
缓存机制：使用缓存提高查询效率
数据压缩：使用压缩减少存储成本

5.2 宽表设计技巧

# 宽表设计示例
hive> — 设计用户行为宽表
CREATE TABLE wide_user_behavior (
user_id STRING,
user_age INT,
user_gender STRING,
user_location STRING,
device_type STRING,
behavior STRING,
item_id STRING,
item_category STRING,
item_price DOUBLE,
timestamp BIGINT,
dt STRING,
hour INT
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 20 BUCKETS;

5.3 常见问题与解决方案

问题	原因	解决方案
宽表过大	包含过多字段或数据量过大	合理设计宽表，只包含必要字段
查询性能慢	数据量过大或索引设计不合理	优化查询语句，合理分区和分桶
数据更新困难	宽表数据冗余，更新成本高	采用增量更新策略，定期重建宽表
存储成本高	数据量过大，存储策略不合理	使用数据压缩，设置合理的存储策略

通过Hadoop生态系统构建的多维分析和宽表，可以高效处理海量数据，为企业提供全面的业务分析能力，支持管理决策和业务发展。学习交流加群风哥QQ113257174

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

大数据教程FG109-Hadoop多维分析与宽表建设实战

目录大纲

Part01-基础概念与理论知识

1.1 多维分析概述

1.2 宽表概念与特点

1.3 Hadoop在多维分析中的应用

Part02-生产环境规划与建议

2.1 硬件资源规划