背景

本示例的假定背景是企业雇员分析，主要用于管理企业雇员信息。示例主要构建三张表：

1、雇员基本信息表（employees_info），主要存储员工基本信息，详情见建表语句；

2、雇员通信表（employees_contact），主要存储员工通信信息，详情见建表语句；

3、公司部门表（deparments），主要存储公司部门信息，详情见建表语句。

数据分析需求如下：

1、查看各个部门雇员薪资的平均值；

2、统计雇员额外信息中包含哪些内容；

3、查看雇员中各个号段的数量。

操作步骤

1、前置操作：检查集群是否安装、部署相关大数据组件（例如 Hadoop、Hive 等），检查相关环境变量是否被正确设置（前置操作的具体细节见下文）；

2、如果当前集群已启用 Kerberos 认证，执行 kinit 命令认证当前用户，当前用户需要具有创建 Hive 表的权限。如果当前集群未启用 Kerberos 认证，则无需执行此命令；

3、启动 Beeline客户端：命令行输入$HIVE_HOME/bin/beeline -u 'jdbc:hive2://<hiveserver2_url>:<port>' -n <user> 运行 beeline 客户端；

4、进行数据分析；

5、完成分析后输入 !q 退出 beeline 客户端。

前置检查操作具体如下：

# 检查是否配置了 HIVE_HOME 环境变量，如果正确设置了环境变量，则打印信息不为空。
echo "$HIVE_HOME"

# 检查必要 Jar 包是否存在，如果存在，下面三条语句都应该有正确的输出。
ll | grep 'hive-exec-*.jar'
ll | grep hive-metastore-*.jar
ll | grep hive-cli-*.jar

# 输入如下语句检查 HiveServer2 服务是否正常，如果能显示 default 库中的表则代表服务正常。
$HIVE_HOME/bin/beeline -u "url" -e 'show tables;'

# 输入如下语句检查元数据服务是否正常工作，如果能显示 default 库中的表则代表服务正常。
$HIVE_HOME/bin/hive -e "show tables;"

样例代码

员工信息基础表employees_info 建表语句如下：

DROP TABLE IF EXISTS employees_info;
CREATE TABLE IF NOT EXISTS employees_info
(
    id INT COMMENT '雇员ID',
    name STRING COMMENT '雇员名称',
    gender STRING COMMENT '性别',
    dept_id INT COMMENT '部门ID',
    grade STRING COMMENT '雇员工资等级',
    salary DOUBLE COMMENT '雇员薪水',
    deduction_rate DOUBLE COMMENT '雇员税率',
    entry_date STRING COMMENT '入职日期',
    other_info STRING COMMENT '额外信息'
) COMMENT '员工信息基础表'
STORED AS PARQUET TBLPROPERTIES (
'creator' = 'author',
'created_at' = '2023-12-07'
);

雇员通信表 employees_contact 建表语句如下：

DROP TABLE IF EXISTS employees_contact;
CREATE TABLE IF NOT EXISTS employees_contact
(
    id INT COMMENT '雇员ID',
    phone STRING COMMENT '雇员电话号',
    email STRING COMMENT '雇员email'
) COMMENT '雇员通信表'
STORED AS PARQUET TBLPROPERTIES (
'creator' = 'author',
'created_at' = '2023-12-07'
);

公司部门表 deparments 建表语句如下：

DROP TABLE IF EXISTS department;
CREATE TABLE IF NOT EXISTS department
(
    dept_id INT COMMENT '部门ID',
    dept_name STRING COMMENT '部门名称'
);

插入数据。本小节介绍了如何使用 HQL 向已有的表中添加数据。为了更好的展示本小节插入的数据，因此使用 INSERT INTO 插入数据而不是 LOAD DATA：

-- 插入employees_contact表
INSERT INTO employees_contact
VALUES (53275, '145939987', 'oqjunox@yahoo.com'),
(21430, '183727019', 'tmmprglpsy@gmail.com'),
(79254, '143213819', 'nxtay@outlook.com'),
(99623, '199096465', 'pejnmcr@hotmail.com'),
(84898, '184258622', 'kvxpzo@yahoo.com'),
(68430, '140604947', 'ipghrm@yahoo.com'),
(85156, '181130863', 'eaqmiow@yahoo.com'),
(81262, '197596386', 'mcmuboan@outlook.com'),
(36140, '138427016', 'guutq@hotmail.com'),
(97550, '157310548', 'ilqbjanwy@yahoo.com');
-- 插入employees_info表
INSERT INTO employees_info
VALUES (7821, 'Meryl Wilson', 'm', 1, 'M1', 120129, 0.23, '2007-4-8',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(9130, 'Jacob Tracie', 'f', 2, 'M2', 15305, 0.53, '2002-2-11',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(41255, 'Arlene Dmitri', 'm', 3, 'M1', 39911, 0.31, '2006-1-10',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(19775, 'Ravi Dani', 'f', 1, 'M3', 31773, 0.6, '2009-7-17',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(89953, 'Emiliano Cheryl', 'f', 2, 'M3', 28919, 0.94, '2014-7-2',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(3747, 'Iain Christabel', 'f', 2, 'I2', 5779, 0.91, '2004-8-10',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(22790, 'Ellis Naja', 'f', 3, 'M3', 28409, 0.54, '2007-11-1',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(85810, 'Cormac Eddy', 'm', 3, 'I3', 7442, 0.34, '2005-12-12',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(30028, 'Melissa Imran', 'm', 2, 'M3', 28243, 0.08, '2017-3-10',
'{"country": "china", "city": "beijing", "address": "", "single": false}'),
(55980, 'Olivia Siobhan', 'f', 1, 'I3', 8920, 0.04, '2002-10-4',
'{"country": "china", "city": "beijing", "address": "", "single": false}');
-- 插入department表
INSERT INTO department
VALUES (1, '测试'),
(2, '开发'),
(3, '产品');

数据分析。本小节介绍如何使用 HQL 对数据进行查询分析。查看各个部门雇员薪水的平均值：

SELECT dept.dept_name, ROUND(AVG(emp.salary), 2)
FROM employees_info emp
JOIN department dept ON emp.dept_id = dept.dept_id
GROUP BY dept.dept_name;

查询结果如下：

查看雇员中各个号段的数量：

SELECT SUBSTRING(phone, 1, 3) AS phone_field, COUNT(phone) AS cnt
FROM employees_contact
GROUP BY SUBSTRING(phone, 1, 3);

查询结果如下：

统计雇员额外信息中包含哪些内容：

SELECT name, get_json_key(other_info) AS info
FROM employees_info;

查询结果如下：