26、Hive数据管理与分析实战指南

最新推荐文章于 2025-11-02 17:10:17 发布

transformer2023

最新推荐文章于 2025-11-02 17:10:17 发布

阅读量56

点赞数

CC 4.0 BY-SA版权

分类专栏：解锁Hive：大数据查询的SQL之道文章标签： Hive Hadoop 多集群数据管理

本文链接：https://blog.youkuaiyun.com/transformer2023/article/details/150507014

解锁Hive：大数据查询的SQL之道专栏收录该内容

30 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

Hive数据管理与分析实战指南

1. M6D多集群Hive数据管理

在数据处理场景中，有时需要运行多个文件系统或Job - Tracker。M6D采用运行两个不同的Hadoop部署方案，即生产（Production）和研究（Research）部署。

部署架构
- 生产和研究部署各自拥有专用的数据节点（Data Nodes）和任务跟踪器（Task Trackers）。
- 每个NameNode和JobTracker都采用DRBD和Linux - HA进行故障转移设置。
- 两个部署位于同一交换网络中，具体信息如下表：
  | 部署类型 | NameNode | JobTracker |
  | ---- | ---- | ---- |
  | 生产（Production） | hdfs.hadoop.pvt:54310 | jt.hadoop.pvt:54311 |
  | 研究（Research） | rs01.hadoop.pvt:34310 | rjt.hadoop.pvt:34311 |
跨集群查询
- 确定表信息 ：在生产集群中，使用 describe extended 命令确定表 zz_mid_set 的列和位置。

hive> set fs.default.name;
fs.default.name=hdfs://hdfs.hadoop.pvt:54310
hive> set mapred.job.tracker;
mapred.job.tracker=jt.hadoop.pvt:54311
hive> describe extended zz_mid_set;
OK
adv_spend_id    int
transaction_id  bigint
time    string
client_id       bigint
visit_info      string
event_type      tinyint
level   int
location:hdfs://hdfs.hadoop.pvt:54310/user/hive/warehouse/zz_mid_set
Time taken: 0.063 seconds
hive> select count(1) from zz_mid_set;
1795928

- **创建外部表**：在研究集群中，创建一个具有相同列的外部表`table_in_another_cluster`。

hive> set fs.default.name;
fs.default.name=hdfs://rs01.hadoop.pvt:34310
hive> set mapred.job.tracker;
mapred.job.tracker=rjt.hadoop.pvt:34311
hive> CREATE TABLE EXTERNAL table_in_another_cluster
( adv_spend_id int, transaction_id bigint, time string, client_id bigint,
visit_info string, event_type tinyint, level int)
LOCATION 'hdfs://hdfs.hadoop.pvt:54310/user/hive/warehouse/zz_mid_set';
hive> select count(*) FROM table_in_another_cluster;
1795928

- **性能影响**：跨集群访问会导致性能下降和网络使用增加，因为任务跟踪器需要连接到另一个集群的数据节点。

数据复制
- 优势：Hadoop和Hive数据复制比传统数据库更容易，因为数据通常是“一次写入”的，添加新分区不会改变现有分区。
- 复制过程 ：早期复制系统使用 distcp 并生成Hive语句按间隔添加分区，后来实现了更自动化的系统。复制守护进程不断扫描复制层次结构，找到文件后查找Hive元数据中的表和分区，然后复制分区，成功后删除文件。以下是主要程序循环：

public void run(){
    while (goOn){
        Path base = new Path(pathToConsume);
        FileStatus [] children = srcFs.listStatus(base);
        for (FileStatus child: children){
            try {
                openHiveService();
                String db = child.getPath().getName().split("\\.")[0];
                String hiveTable = child.getPath().getName().split("\\.")[1];
                Table table = srcHive.client.get_table(db, hiveTable);
                if (table == null){
                    throw new RuntimeException(db+" "+hiveTable+
                      " not found in source metastore");
                }
                Table tableR = destHive.client.get_table(db,hiveTable);
                if (tableR == null){
                    throw new RuntimeException(db+" "+hiveTable+
                      " not found in dest metastore");
                }
                URI localTable = new URI(tableR.getSd().getLocation());
                FileStatus [] partitions = srcFs.listStatus(child.getPath());
                for (FileStatus partition : partitions){
                    try {
                        String replaced = partition.getPath().getName()
                        .replace(",", "/").replace("'","");
                        Partition p = srcHive.client.get_partition_by_name(
                        db, hiveTable, replaced);
                        URI partUri = new URI(p.getSd().getLocation());
                        String path = partUri.getPath();
                        DistCp distCp = new DistCp(destConf.conf);
                        String thdfile = "/tmp/replicator_distcp";
                        Path tmpPath = new Path(thdfile);
                        destFs.delete(tmpPath,true);
                        if (destFs.exists( new Path(localTable.getScheme()+
                          "://"+localTable.getHost()+":"+localTable.getPort()+path) ) ){
                            throw new RuntimeException("Target path already exists "
                              +localTable.getScheme()+"://"+localTable.getHost()+
                              ":"+localTable.getPort()+path );
                        }
                        String [] dargs = new String [4];
                        dargs[0]="-log";
                        dargs[1]=localTable.getScheme()+"://"+localTable.getHost()+":"+
                          localTable.getPort()+thdfile;
                        dargs[2]=p.getSd().getLocation();
                        dargs[3]=localTable.getScheme()+"://"+localTable.getHost()+":"+
                          localTable.getPort()+path;
                        int result =ToolRunner.run(distCp,dargs);
                        if (result != 0){
                            throw new RuntimeException("DistCP failed "+ dargs[2] +" "+dargs[3]);
                        }
                        String HQL = "ALTER TABLE "+hiveTable+
                            " ADD PARTITION ("+partition.getPath().getName()
                            +") LOCATION '"+path+"'";
                        destHive.client.execute("SET hive.support.concurrency=false");
                        destHive.client.execute("USE "+db);
                        destHive.client.execute(HQL);
                        String [] results=destHive.client.fetchAll();
                        srcFs.delete(partition.getPath(),true);
                    } catch (Exception ex){
                        ex.printStackTrace();
                    }
                } 
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        } 
        closeHiveService();
        Thread.sleep(60L*1000L);
    } 
}

2. Outbrain内容发现平台数据处理

Outbrain作为领先的内容发现平台，在数据处理方面有一些独特的需求。

站内引荐识别
- URL清理 ：将引荐URL分为站内（In - site）、直接（Direct）和其他（Other）三类。若引荐URL为空或为null，则标记为直接；若属于指定的站点，则标记为站内；否则标记为其他。可以将URL解析为主机或域名，Hive只有 host 函数，若要获取域名可使用UDF。
- 确定引荐类型
  - 少量站点情况 ：假设有三个站点 mysite1.com 、 mysite2.com 和 mysite3.com ，使用以下查询为表 referrer_identification 添加引荐类型：

SELECT ri_page_url, ri_referrer_url,
  CASE
    WHEN ri_referrer_url is NULL or ri_referrer_url = '' THEN 'DIRECT'
    WHEN ri_referrer_url is in ('mysite1.com','mysite2.com','mysite3.com') THEN 'INSITE'
    ELSE ri_referrer_url
  END as ri_referrer_url_classed
FROM
  referrer_identification;

    - **大量站点情况**：如果有大量站点，使用`site_url`表记录每个URL所属的站点，为`referrer_identification`表添加`ri_site_id`字段，通过以下查询确定引荐类型：

SELECT
  c.c_page_url as ri_page_url,
  c.c_site_id as ri_site_id,
  CASE
    WHEN c.c_referrer_url is NULL or c.c_referrer_url = '' THEN 'DIRECT'
    WHEN c.c_insite_referrer_flags > 0 THEN 'INSITE'
    ELSE c.c_referrer_url
  END as ri_referrer_url_classed
FROM
(SELECT
  a.a_page_url as c_page_url,
  a.a_referrer_url as c_referrer_url,
  a.a_site_id as c_site_id,
  SUM(IF(b.b_url <> '', 1, 0)) as c_insite_referrer_flags
FROM
(SELECT
  ri_page_url as a_page_url,
  ri_referrer_url as a_referrer_url,
  ri_site_id as a_site_id
FROM
  referrer_identification
) a
LEFT OUTER JOIN
(SELECT
  su_site_id as b_site_id,
  su_url as b_url
FROM
  site_url
) b
ON
  a.a_site_id = b.b_site_id and
  a.a_referrer_url = b.b_url
) c

唯一用户计数
- 问题分析 ：统计 daily_users 表中一个月内的唯一用户数时，当用户数量过多而集群机器不足，在归约阶段会出现问题。因为唯一计数复杂度为O(n)且常数因子高，可通过减少n来优化。
- 解决方案
  - 创建临时表 ：创建分区临时表 daily_users_deduped 。

CREATE TABLE daily_users_deduped (dud_user_id STRING)
PARTITIONED BY (dud_date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

    - **写入临时表**：编写模板查询`mj_01.sql`，并通过脚本在指定日期范围内运行。

INSERT OVERWRITE TABLE daily_users_deduped
PARTITION (dud_date = ':date:')
SELECT DISTINCT
  du_user_id
FROM
  daily_users
WHERE
  du_date = ':date:'

start_date = '2012-03-01'
end_date = '2012-03-31'
for date in date_range(start_date, end_date):
    femp_file = modify_temp_file('mj_01.sql',{':date:':my_date})
    fire_query(temp_file)
    delete(temp_file)

    - **查询临时表**：查询临时表获取唯一用户数。

SELECT
  COUNT(DISTINCT (dud_uuid)
FROM
  daily_users_deduped

会话化分析
- 会话定义 ：会话是指一系列活动，间隔不超过30分钟。若间隔超过30分钟，则开启新会话。
- 分析步骤
  1. 识别会话起始页 ：通过条件求和，找出没有在30分钟内前置活动的页面作为会话起始页。

CREATE TABLE sessionization_step_one_origins AS
SELECT
  c.c_user_id as ssoo_user_id,
  c.c_pageview_id as ssoo_pageview_id,
  c.c_timestamp as ssoo_timestamp
FROM
        (SELECT
    a.a_user_id as c_user_id,
    a.a_pageview_id as c_pageview_id,
    a.a_timestamp as c.c_timestamp,
    SUM(IF(a.a_timestamp + 1800 >= b.b_timestamp AND
        a.a_timestamp < b.b_timestamp,1,0)) AS c_nonorigin_flags
  FROM
    (SELECT
      st_user_id as a_user_id,
      st_pageview_id as a_pageview_id,
      st_timestamp as a_timestamp
    FROM
      session_test
    ) a
    JOIN
    (SELECT
      st_user_id as b_user_id,
      st_timestamp as b_timestamp
    FROM
      session_test
    ) b
    ON
      a.a_user_id = b.b_user_id
) c
WHERE c_nonorigin_flags = 0;

    2. **将页面视图归类到正确的起始页**：根据时间和用户ID等信息，将每个页面视图与对应的会话起始页关联。
    3. **聚合每个起始页的页面视图**：对每个会话起始页关联的所有页面视图进行聚合操作。
    4. **标记每个起始页并计算会话参与度**：为每个会话起始页标记，然后根据会话长度等指标计算会话参与度。

通过以上方法，可以对Hive数据进行有效的管理和分析，解决多集群数据处理、唯一用户计数和会话化分析等问题。

Hive数据管理与分析实战指南

3. 操作步骤总结

为了更清晰地展示上述各项操作的流程，下面将各项操作的步骤进行详细总结。

跨集群查询操作步骤
1. 在生产集群中，使用 set 命令设置文件系统和Job - Tracker信息，使用 describe extended 命令确定表的列和位置，示例如下：

hive> set fs.default.name;
fs.default.name=hdfs://hdfs.hadoop.pvt:54310
hive> set mapred.job.tracker;
mapred.job.tracker=jt.hadoop.pvt:54311
hive> describe extended zz_mid_set;

2. 在研究集群中，同样使用`set`命令设置文件系统和Job - Tracker信息，创建外部表并指定表的位置，示例如下：

hive> set fs.default.name;
fs.default.name=hdfs://rs01.hadoop.pvt:34310
hive> set mapred.job.tracker;
mapred.job.tracker=rjt.hadoop.pvt:34311
hive> CREATE TABLE EXTERNAL table_in_another_cluster
( adv_spend_id int, transaction_id bigint, time string, client_id bigint,
visit_info string, event_type tinyint, level int)
LOCATION 'hdfs://hdfs.hadoop.pvt:54310/user/hive/warehouse/zz_mid_set';

3. 在研究集群中查询外部表，验证数据是否可访问：

hive> select count(*) FROM table_in_another_cluster;

数据复制操作步骤
1. 确保表在源和目标元存储中都已定义，在程序中通过以下代码检查：

Table table = srcHive.client.get_table(db, hiveTable);
if (table == null){
    throw new RuntimeException(db+" "+hiveTable+
      " not found in source metastore");
}
Table tableR = destHive.client.get_table(db,hiveTable);
if (tableR == null){
    throw new RuntimeException(db+" "+hiveTable+
      " not found in dest metastore");
}

2. 查找分区信息，使用`DistCp`进行数据复制，并确保目标路径不存在，示例代码如下：

URI localTable = new URI(tableR.getSd().getLocation());
FileStatus [] partitions = srcFs.listStatus(child.getPath());
for (FileStatus partition : partitions){
    String replaced = partition.getPath().getName()
    .replace(",", "/").replace("'","");
    Partition p = srcHive.client.get_partition_by_name(
    db, hiveTable, replaced);
    URI partUri = new URI(p.getSd().getLocation());
    String path = partUri.getPath();
    DistCp distCp = new DistCp(destConf.conf);
    String thdfile = "/tmp/replicator_distcp";
    Path tmpPath = new Path(thdfile);
    destFs.delete(tmpPath,true);
    if (destFs.exists( new Path(localTable.getScheme()+
      "://"+localTable.getHost()+":"+localTable.getPort()+path) ) ){
        throw new RuntimeException("Target path already exists "
          +localTable.getScheme()+"://"+localTable.getHost()+
          ":"+localTable.getPort()+path );
    }
    String [] dargs = new String [4];
    dargs[0]="-log";
    dargs[1]=localTable.getScheme()+"://"+localTable.getHost()+":"+
      localTable.getPort()+thdfile;
    dargs[2]=p.getSd().getLocation();
    dargs[3]=localTable.getScheme()+"://"+localTable.getHost()+":"+
      localTable.getPort()+path;
    int result =ToolRunner.run(distCp,dargs);
    if (result != 0){
        throw new RuntimeException("DistCP failed "+ dargs[2] +" "+dargs[3]);
    }
}

3. 在目标Hive中执行`ALTER TABLE`语句添加分区，并删除源文件：

String HQL = "ALTER TABLE "+hiveTable+
    " ADD PARTITION ("+partition.getPath().getName()
    +") LOCATION '"+path+"'";
destHive.client.execute("SET hive.support.concurrency=false");
destHive.client.execute("USE "+db);
destHive.client.execute(HQL);
String [] results=destHive.client.fetchAll();
srcFs.delete(partition.getPath(),true);

站内引荐识别操作步骤
- 少量站点情况
  1. 准备 referrer_identification 表，包含 ri_page_url 和 ri_referrer_url 字段。
  2. 执行以下查询确定引荐类型：

SELECT ri_page_url, ri_referrer_url,
  CASE
    WHEN ri_referrer_url is NULL or ri_referrer_url = '' THEN 'DIRECT'
    WHEN ri_referrer_url is in ('mysite1.com','mysite2.com','mysite3.com') THEN 'INSITE'
    ELSE ri_referrer_url
  END as ri_referrer_url_classed
FROM
  referrer_identification;

- **大量站点情况**
    1. 准备`referrer_identification`表，添加`ri_site_id`字段，准备`site_url`表，包含`su_site_id`和`su_url`字段。
    2. 执行以下查询确定引荐类型：

SELECT
  c.c_page_url as ri_page_url,
  c.c_site_id as ri_site_id,
  CASE
    WHEN c.c_referrer_url is NULL or c.c_referrer_url = '' THEN 'DIRECT'
    WHEN c.c_insite_referrer_flags > 0 THEN 'INSITE'
    ELSE c.c_referrer_url
  END as ri_referrer_url_classed
FROM
(SELECT
  a.a_page_url as c_page_url,
  a.a_referrer_url as c_referrer_url,
  a.a_site_id as c_site_id,
  SUM(IF(b.b_url <> '', 1, 0)) as c_insite_referrer_flags
FROM
(SELECT
  ri_page_url as a_page_url,
  ri_referrer_url as a_referrer_url,
  ri_site_id as a_site_id
FROM
  referrer_identification
) a
LEFT OUTER JOIN
(SELECT
  su_site_id as b_site_id,
  su_url as b_url
FROM
  site_url
) b
ON
  a.a_site_id = b.b_site_id and
  a.a_referrer_url = b.b_url
) c

唯一用户计数操作步骤
1. 创建分区临时表 daily_users_deduped ：

CREATE TABLE daily_users_deduped (dud_user_id STRING)
PARTITIONED BY (dud_date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

2. 编写模板查询`mj_01.sql`：

INSERT OVERWRITE TABLE daily_users_deduped
PARTITION (dud_date = ':date:')
SELECT DISTINCT
  du_user_id
FROM
  daily_users
WHERE
  du_date = ':date:'

3. 使用脚本在指定日期范围内运行查询：

start_date = '2012-03-01'
end_date = '2012-03-31'
for date in date_range(start_date, end_date):
    femp_file = modify_temp_file('mj_01.sql',{':date:':my_date})
    fire_query(temp_file)
    delete(temp_file)

4. 查询临时表获取唯一用户数：

SELECT
  COUNT(DISTINCT (dud_uuid)
FROM
  daily_users_deduped

会话化分析操作步骤
1. 准备 session_test 表，包含 st_user_id 、 st_pageview_id 、 st_page_url 、 st_referrer_url 和 st_timestamp 字段。
2. 识别会话起始页：

CREATE TABLE sessionization_step_one_origins AS
SELECT
  c.c_user_id as ssoo_user_id,
  c.c_pageview_id as ssoo_pageview_id,
  c.c_timestamp as ssoo_timestamp
FROM
        (SELECT
    a.a_user_id as c_user_id,
    a.a_pageview_id as c_pageview_id,
    a.a_timestamp as c.c_timestamp,
    SUM(IF(a.a_timestamp + 1800 >= b.b_timestamp AND
        a.a_timestamp < b.b_timestamp,1,0)) AS c_nonorigin_flags
  FROM
    (SELECT
      st_user_id as a_user_id,
      st_pageview_id as a_pageview_id,
      st_timestamp as a_timestamp
    FROM
      session_test
    ) a
    JOIN
    (SELECT
      st_user_id as b_user_id,
      st_timestamp as b_timestamp
    FROM
      session_test
    ) b
    ON
      a.a_user_id = b.b_user_id
) c
WHERE c_nonorigin_flags = 0;

3. 将页面视图归类到正确的起始页，可根据时间和用户ID等信息实现。
4. 聚合每个起始页的页面视图，对关联的页面视图进行聚合操作。
5. 标记每个起始页并计算会话参与度，根据会话长度等指标进行计算。

4. 总结与展望

本文介绍了在多集群环境下Hive数据的管理和分析方法，包括M6D的多集群部署、跨集群查询、数据复制，以及Outbrain平台的站内引荐识别、唯一用户计数和会话化分析等内容。通过这些方法，可以有效地处理和分析大规模数据。

在实际应用中，还可以进一步优化这些方法。例如，对于跨集群查询，可以考虑优化网络配置以减少性能损失；对于数据复制，可以进一步提高自动化程度和容错能力；对于唯一用户计数，可以探索更高效的去重算法。

总之，随着数据量的不断增长和业务需求的不断变化，Hive数据管理和分析技术也需要不断发展和完善，以满足不同场景下的需求。

下面是跨集群查询和数据复制操作的mermaid流程图：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px

    A([开始]):::startend --> B(设置生产集群信息):::process
    B --> C(确定表信息):::process
    C --> D(设置研究集群信息):::process
    D --> E(创建外部表):::process
    E --> F(查询外部表):::process
    F --> G{查询成功?}:::decision
    G -->|是| H([结束]):::startend
    G -->|否| I(检查网络和配置):::process
    I --> D

    J([开始数据复制]):::startend --> K(检查源和目标元存储):::process
    K --> L{表存在?}:::decision
    L -->|是| M(查找分区信息):::process
    L -->|否| N(抛出异常):::process
    N --> K
    M --> O(使用DistCp复制数据):::process
    O --> P{复制成功?}:::decision
    P -->|是| Q(添加分区到目标Hive):::process
    P -->|否| R(检查目标路径和DistCp参数):::process
    R --> O
    Q --> S(删除源文件):::process
    S --> T([结束数据复制]):::startend

通过以上的操作步骤和流程图，希望能帮助读者更好地理解和应用Hive数据管理和分析技术。