【Hive】Apache Hive系列之Hive高级查询案例

柔情只为你懂 2023-10-05 11:55 31阅读 0赞

#### 指路牌 ####

*   *  准备
     *  SQL查询
     *   *  单表查询
         *  WHERE条件查询
         *  DISTINCT去重查询
         *  LIMIT分页查询
         *  GROUP BY分组查询
         *  ORDER AND SORT排序
         *   *  sort by
             *  order by
         *  HAVING条件过滤
         *  DISTRIBUTE BY 分发
         *  CLUSTER BY查询
         *  Join表连接查询
         *   *  内连接
             *  外连接
         *  LEFT SEMI JOIN 查询
         *  JOIN优化
         *   *  STREAMTABLE
             *  MAPJOIN
         *  开窗函数
         *  Cube分析
         *  行转列

### 准备 ###

1、筹备一些测试数据

# t_employee表数据
    7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,\N,20
    7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
    7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
    7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,\N,20
    7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
    7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,\N,30
    7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,\N,10
    7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,1500,\N,20
    7839,KING,PRESIDENT,\N,1981-11-17 00:00:00,5000,\N,10
    7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
    7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,\N,20
    7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,\N,30
    7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,\N,20
    7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,\N,10
    
    # t_dept表数据
    10,ACCOUNTING,NEW YORK
    20,RESEARCH,DALLAS
    30,SALES,CHICAGO
    40,OPERATIONS,BOSTON

2、由于随后的sql命令较多，为节省时间我们将所需的sql命令直接写入SQL脚本一次性执行`/root/hivescriot.sql`

-- 删除test数据库
    drop database if exists test cascade;
    
    -- 创建test数据库
    create database if not exists test;
    
    -- 使用test数据库
    use test;
    
    -- 创建t_employee表
    CREATE TABLE if not exists t_employee(
        empno INT,
        ename STRING,
        job STRING,
        mgr INT,
        hiredate TIMESTAMP,
        sal DECIMAL(7,2),
        comm DECIMAL(7,2),
        deptno INT)
    row format delimited
    fields terminated by ','
    collection items terminated by '|'
    map keys terminated by '>'
    lines terminated by '\n'
    stored as textfile;
    
    -- 创建t_dept表
    CREATE TABLE  if not exists  t_dept(
        DEPTNO INT,
        DNAME STRING,
        LOC STRING)
    row format delimited
    fields terminated by ','
    collection items terminated by '|'
    map keys terminated by '>'
    lines terminated by '\n'
    stored as textfile;
    
    -- 将数据加载至两张表中
    load data local inpath '/root/t_employee' overwrite  into table  t_employee;
    load data local inpath '/root/t_dept' overwrite  into table  t_dept;

3、执行脚本

[root@CentOS ~]# hive -f /root/hivescript.sql

### SQL查询 ###

[root@CentOS ~]# hive -e 'sql语句'

#### 单表查询 ####

0: jdbc:hive2://CentOS:10000> select empno,ename,job,mgr,hiredate,sal,comm,deptno  from t_employee;
    +--------+---------+------------+-------+------------------------+-------+-------+---------+--+
    | empno  |  ename  |    job     |  mgr  |        hiredate        |  sal  | comm  | deptno  |
    +--------+---------+------------+-------+------------------------+-------+-------+---------+--+
    | 7369   | SMITH   | CLERK      | 7902  | 1980-12-17 00:00:00.0  | 800   | NULL  | 20      |
    | 7499   | ALLEN   | SALESMAN   | 7698  | 1981-02-20 00:00:00.0  | 1600  | 300   | 30      |
    | 7521   | WARD    | SALESMAN   | 7698  | 1981-02-22 00:00:00.0  | 1250  | 500   | 30      |
    | 7566   | JONES   | MANAGER    | 7839  | 1981-04-02 00:00:00.0  | 2975  | NULL  | 20      |
    | 7654   | MARTIN  | SALESMAN   | 7698  | 1981-09-28 00:00:00.0  | 1250  | 1400  | 30      |
    | 7698   | BLAKE   | MANAGER    | 7839  | 1981-05-01 00:00:00.0  | 2850  | NULL  | 30      |
    | 7782   | CLARK   | MANAGER    | 7839  | 1981-06-09 00:00:00.0  | 2450  | NULL  | 10      |
    | 7788   | SCOTT   | ANALYST    | 7566  | 1987-04-19 00:00:00.0  | 1500  | NULL  | 20      |
    | 7839   | KING    | PRESIDENT  | NULL  | 1981-11-17 00:00:00.0  | 5000  | NULL  | 10      |
    | 7844   | TURNER  | SALESMAN   | 7698  | 1981-09-08 00:00:00.0  | 1500  | 0     | 30      |
    | 7876   | ADAMS   | CLERK      | 7788  | 1987-05-23 00:00:00.0  | 1100  | NULL  | 20      |
    | 7900   | JAMES   | CLERK      | 7698  | 1981-12-03 00:00:00.0  | 950   | NULL  | 30      |
    | 7902   | FORD    | ANALYST    | 7566  | 1981-12-03 00:00:00.0  | 3000  | NULL  | 20      |
    | 7934   | MILLER  | CLERK      | 7782  |