# 数据仓库架构设计
# 1. 分层架构：原始数据层、数据清洗层、数据转换层、数据集市层
# 2. 存储策略：使用HDFS存储原始数据，使用Hive存储处理后的数据
# 3. 计算引擎：使用MapReduce、Spark等处理数据
# 4. 查询引擎：使用Hive、Presto、Impala等提供查询服务

# ETL流程设计
# 1. 数据抽取：从源系统抽取数据到HDFS
# 2. 数据清洗：清洗和转换数据
# 3. 数据加载：将处理后的数据加载到数据仓库
# 4. 数据质量：检查数据质量，确保数据的准确性和完整性

# 安装Hive
[root@fgedu.net.cn ~]# wget https://downloads.apache.org/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz
[root@fgedu.net.cn ~]# tar -zxvf apache-hive-3.1.3-bin.tar.gz
[root@fgedu.net.cn ~]# mv apache-hive-3.1.3-bin /bigdata/app/hive

# 配置Hive
[root@fgedu.net.cn ~]# vi /bigdata/app/hive/conf/hive-site.xml

javax.jdo.option.ConnectionURL
jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName
com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName
hive javax.jdo.option.ConnectionPassword
hive hive.metastore.warehouse.dir
/user/hive/warehouse hive.exec.scratchdir
/tmp/hive hive.querylog.location
/var/log/hive hive.server2.logging.operation.log.location
/var/log/hive/operation

# 初始化Hive元数据库
[root@fgedu.net.cn ~]# schematool -initSchema -dbType mysql

# 创建维度表
hive> CREATE TABLE dim_date (
> date_id STRING,
> year INT,
> month INT,
> day INT,
> quarter INT,
> week INT,
> is_weekend INT,
> is_holiday INT
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;

# 创建事实表
hive> CREATE TABLE fact_sales (
> sales_id STRING,
> date_id STRING,
> product_id STRING,
> customer_id STRING,
> store_id STRING,
> sales_amount DOUBLE,
> sales_quantity INT,
> payment_method STRING
> )
> PARTITIONED BY (dt STRING)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;

# 创建数据集市表
hive> CREATE TABLE dm_sales (
> date_id STRING,
> product_id STRING,
> product_name STRING,
> category STRING,
> sales_amount DOUBLE,
> sales_quantity INT,
> order_count INT
> )
> PARTITIONED BY (dt STRING)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;

# ETL脚本
#!/bin/bash
# etl_process.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

# 日期参数
DATE=$(date +%Y%m%d)

# 1. 数据抽取
echo “开始数据抽取…”
sqoop import –connect jdbc:mysql://localhost:3306/source_db –username root –password password –table sales –target-dir /user/hive/warehouse/raw/sales/$DATE

# 2. 数据清洗
echo “开始数据清洗…”
hive -e “INSERT OVERWRITE TABLE fact_sales PARTITION (dt=’$DATE’)
SELECT
sales_id,
date_id,
product_id,
customer_id,
store_id,
sales_amount,
sales_quantity,
payment_method
FROM raw_sales
WHERE dt=’$DATE’
AND sales_amount > 0;
”

# 3. 数据转换
echo “开始数据转换…”
hive -e “INSERT OVERWRITE TABLE dm_sales PARTITION (dt=’$DATE’)
SELECT
f.date_id,
f.product_id,
p.product_name,
p.category,
SUM(f.sales_amount) AS sales_amount,
SUM(f.sales_quantity) AS sales_quantity,
COUNT(*) AS order_count
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
WHERE f.dt=’$DATE’
GROUP BY f.date_id, f.product_id, p.product_name, p.category;
”

# 4. 数据质量检查
echo “开始数据质量检查…”
hive -e “SELECT
COUNT(*) AS total_records,
COUNT(CASE WHEN sales_amount IS NULL THEN 1 END) AS null_amount,
COUNT(CASE WHEN sales_quantity IS NULL THEN 1 END) AS null_quantity
FROM fact_sales
WHERE dt=’$DATE’;
”