Hive典型应用示例(1) 输入如下语句检查 HiveServer2 服务是否正常,如果能显示 default 库中的表则代表服务正常。 $HIVEHOME/bin/beeline u "url" e 'show tables;' 输入如下语句检查元数据服务是否正常工作,如果能显示 default 库中的表则代表服务正常。 $HIVEHOME/bin/hive e "show tables;" 样例代码 员工信息基础表employeesinfo 建表语句如下: plaintext DROP TABLE IF EXISTS employeesinfo; CREATE TABLE IF NOT EXISTS employeesinfo ( id INT COMMENT '雇员ID', name STRING COMMENT '雇员名称', gender STRING COMMENT '性别', deptid INT COMMENT '部门ID', grade STRING COMMENT '雇员工资等级', salary DOUBLE COMMENT '雇员薪水', deductionrate DOUBLE COMMENT '雇员税率', entrydate STRING COMMENT '入职日期', otherinfo STRING COMMENT '额外信息' ) COMMENT '员工信息基础表' STORED AS PARQUET TBLPROPERTIES ( 'creator' 'author', 'createdat' '20231207' ); 雇员通信表 employeescontact 建表语句如下: plaintext DROP TABLE IF EXISTS employeescontact; CREATE TABLE IF NOT EXISTS employeescontact ( id INT COMMENT '雇员ID', phone STRING COMMENT '雇员电话号', email STRING COMMENT '雇员email' ) COMMENT '雇员通信表' STORED AS PARQUET TBLPROPERTIES ( 'creator' 'author', 'createdat' '20231207' ); 公司部门表 deparments 建表语句如下: plaintext DROP TABLE IF EXISTS department; CREATE TABLE IF NOT EXISTS department ( deptid INT COMMENT '部门ID', deptname STRING COMMENT '部门名称' ); 插入数据。本小节介绍了如何使用 HQL 向已有的表中添加数据。为了更好的展示本小节插入的数据,因此使用 INSERT INTO 插入数据而不是 LOAD DATA: plaintext 插入employeescontact表 INSERT INTO employeescontact 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'); 插入employeesinfo表 INSERT INTO employeesinfo VALUES (7821, 'Meryl Wilson', 'm', 1, 'M1', 120129, 0.23, '200748', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (9130, 'Jacob Tracie', 'f', 2, 'M2', 15305, 0.53, '2002211', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (41255, 'Arlene Dmitri', 'm', 3, 'M1', 39911, 0.31, '2006110', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (19775, 'Ravi Dani', 'f', 1, 'M3', 31773, 0.6, '2009717', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (89953, 'Emiliano Cheryl', 'f', 2, 'M3', 28919, 0.94, '201472', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (3747, 'Iain Christabel', 'f', 2, 'I2', 5779, 0.91, '2004810', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (22790, 'Ellis Naja', 'f', 3, 'M3', 28409, 0.54, '2007111', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (85810, 'Cormac Eddy', 'm', 3, 'I3', 7442, 0.34, '20051212', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (30028, 'Melissa Imran', 'm', 2, 'M3', 28243, 0.08, '2017310', '{"country": "china", "city": "beijing", "address": "", "single": false}'), (55980, 'Olivia Siobhan', 'f', 1, 'I3', 8920, 0.04, '2002104', '{"country": "china", "city": "beijing", "address": "", "single": false}'); 插入department表 INSERT INTO department VALUES (1, '测试'), (2, '开发'), (3, '产品'); 数据分析。本小节介绍如何使用 HQL 对数据进行查询分析。查看各个部门雇员薪水的平均值: plaintext SELECT dept.deptname, ROUND(AVG(emp.salary), 2) FROM employeesinfo emp JOIN department dept ON emp.deptid dept.deptid GROUP BY dept.deptname; 查询结果如下: deptname avgsalary 产品 25254 开发 19561.5 测试 53607.33 查看雇员中各个号段的数量: plaintext SELECT SUBSTRING(phone, 1, 3) AS phonefield, COUNT(phone) AS cnt FROM employeescontact GROUP BY SUBSTRING(phone, 1, 3); 查询结果如下: phonefield cnt 138 1 140 1 140 1 143 1 145 1 157 1 181 1 183 1 184 1 197 1 199 1 统计雇员额外信息中包含哪些内容: plaintext SELECT name, getjsonkey(otherinfo) AS info FROM employeesinfo; 查询结果如下: name info Meryl Wilson singlecountryaddresscity Jacob Tracie singlecountryaddresscity Arlene Dmitri singlecountryaddresscity Ravi Dani singlecountryaddresscity Emiliano Cheryl singlecountryaddresscity Iain Christabel singlecountryaddresscity Ellis Naja singlecountryaddresscity Cormac Eddy singlecountryaddresscity Melissa Imran singlecountryaddresscity Olivia Siobhan singlecountryaddresscity
来自: