剑指数据仓库-Hive02

一、上次课回顾

二、Hive02

• 2.1、Hive中table的DDL语句
• 2.2、创建表的几种方式
• 2.3、Hive中基础的sql命令&&修改表信息&&删除表
• 2.4、Hive的外部表和内部表（******）
• 2.5、外部表的典型应用
• 2.6、Hive中的分区表&&使用msck刷新元数据信息(慎用)

三、Hive中的多级分区&&静态分区&&动态分区

• 3.1、Hive中的数据加载
• 3.2、Hive中的数据导出
• 3.3、SQL进行查询
• 3.4、Hive中的聚合函数
• 3.5、SQL跑Mapreduce任务取决于什么？

四、面试题

一、上次课回顾

• https://blog.csdn.net/SparkOnYarn/article/details/105140753

• 使用MapReduce编程难度较大，无法满足基于sql查询的需求；Hive主要是用来做离线数仓；Hive对比与普通MySQL的优缺点；Hive的执行流程；MetaStore元数据信息。PK离线数据并发3 5万；

• Hive必须要两份数据，hdfs上的数据和mysql上的数据，两者缺一不可；部署的时候hive-site.xml四个信息配全还有一个mysql的jar包拷贝到$HIVE_HOME/lib下；

• Hive中的表对应hdfs就是文件夹，Hive中的几种分隔符，行与行分隔符\n，列与列分隔符\t

• 创建数据库，创建数据库的时候指定路径，修改数据库的属性，要以文件夹的方式组织；删除数据库需要先删除数据库中的表，如果数据库不需要了直接使用cascade级联删除。

二、Hive02

2.1、Table的DDL语句

• https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableCreate/Drop/TruncateTable

创建表的语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

中括号可以不选择使用，小括号需要选择使用：
1、最基础的创建表，必要的数据：
create table emp(
	colname datatype,
	colname1 datatype,
	colname2 datatype
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

//意味着字段与字段的分割符使用tab制表符分割

2.2、创建表的几种方式

1、DDL语句创建表

1、数据准备emp.txt，列名解析：

员工编号	员工姓名	员工岗位	员工上级领导编号	入职时间	工资		津贴		员工所处部门编号
7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20

2、照着数据创建表：

create table emp(
	empno int,
	ename string,
	job string,
	mgr int,
	date string,
	sal double,
	comm double,
	deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

hive (ruozedata_hive)> create table emp(emono int,ename string,job string,mgr int,date string,sal double,comm double,deptno int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.077 seconds

3、加载数据：

	LOAD DATA LOCAL INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

	在本次案例如下写：
	load data local inpath '/home/hadoop/data/emp.txt' into table emp;

4、验证是否已经加载成功：

hive (ruozedata_hive)> select * from emp;
OK
emp.emono       emp.ename       emp.job emp.mgr emp.date        emp.sal emp.comm emp.deptno
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10
Time taken: 0.052 seconds, Fetched: 15 row(s)

注意：如果你加载进Hive的数据显示都为null，很有可能是创建表时的分隔符和文件中的分割符不一致，就是schema信息和文件内容对不上。

2、根据已有的表来创建表（只包含表结构）

hive (ruozedata_hive)> create table emp2 like emp;
OK
Time taken: 0.096 seconds
hive (ruozedata_hive)> select * from emp2;
OK
emp2.emono      emp2.ename      emp2.job        emp2.mgr        emp2.date       emp2.sal  emp2.comm       emp2.deptno
Time taken: 0.043 seconds

3、既拷贝表数据也拷贝表结构：

hive (ruozedata_hive)> create table emp3 as select * from emp;
Query ID = hadoop_20200328172424_0442dd5a-507d-4b4f-b00b-85b3799af226
Total jobs = 3
Launching Job 1 out of 3

• load加载进来的文件名就是emp.txt、跑mapreduce出来的文件名是00000_0
  
  

2.3、Hive中基础的sql命令&&修改表信息&&删除表

Hive中基础的sql命令：

1、在default数据库下去查询ruozedata_hive中的表名：

hive (default)> show tables in ruozedata_hive;
OK
tab_name
emp
emp2
stu2
Time taken: 0.024 seconds, Fetched: 3 row(s)

2、查询建表语句，仅限查看：

hive (default)> show create table ruozedata_hive.emp;

3、根据模糊匹配查询当前数据库下的表：

hive (ruozedata_hive)> show tables 'emp*';
OK
tab_name
emp
emp2
Time taken: 0.015 seconds, Fetched: 2 row(s)

修改表信息：

1、修改表名，在hdfs表名也会随之修改：
hive (ruozedata_hive)> alter table emp3 rename to emp3bak;
OK
Time taken: 0.089 seconds

drop和truncate的区别：

1、drop table emp2; 表结构和表数据全都删除
2、truncate table emp3; 只删除表数据，保留表结构

2.4、Hive中的外部表和内部表（很重要，面试最常被问到）

MANAGED_TABLE：内部表，删除的时候，hdfs数据和mysql元数据统统删除；

EXTERNAL_TABLE：外部表，删除的时候，mysql元数据删除，hdfs上的文件是会保留的；

1、测试创建内部表和删除内部表：

	1、在hive中操作如下：
	create table emp_managed as select * from emp;

	2、select * from emp_managed;

	3、drop table emp_managed;

	4、进入到mysql中，查看ruozedata_hive.tbls数据中的内容：
	*************************** 4. row ***************************
        TBL_ID: 13
   CREATE_TIME: 1585388797
         DB_ID: 6
  LAST_ACCESS_TIME: 0
             OWNER: hadoop
        OWNER_TYPE: USER
         RETENTION: 0
             SD_ID: 13
          TBL_NAME: emp_managed
          TBL_TYPE: MANAGED_TABLE
	VIEW_EXPANDED_TEXT: NULL
	VIEW_ORIGINAL_TEXT: NULL
	4 rows in set (0.00 sec)
	
	ERROR: 
	No query specified

• 删除内部表的时候hdfs上的数据和mysql中的元数据信息都会被删除；

2、测试创建外部表和删除外部表：

1、创建外部表：
create external table emp_external(emono int,ename string,job string,mgr int,date string,sal double,comm double,deptno int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

2、还是加载数据和检验有无内容：
load data local inapth '/home/hadoop/data/emp.txt' into table emp_external;
select * from emp_external;

3、drop table emp_externa;

4、继续去mysql中查询tbls表：
已经查询不到相关信息

• 删除外部表的时候，其在mysql中的元数据信息删除，hdfs上的数据会保留。

Hive官网对于external table和managed table的描述：

• https://cwiki.apache.org/confluence/display/Hive/Managed+vs.+External+Tables

如何操作直接在外部表和内部表之间转换：

语法如下：

1、修改内部表属性为外部表：
hive (ruozedata_hive)> alter table emp_managed set tblproperties('EXTERNAL'='TRUE');
OK
Time taken: 0.131 seconds
hive (ruozedata_hive)> desc formatted emp_managed;

2、删除外部表：
- 既然已经将它修改为外部表了，那它就具有了外部表的属性，删除它之后，hdfs数据保留，删除mysql中的元数据信息

2.5、外部表的典型应用

• 外部表的典型应用：
  使用flume把日志采集到hdfs，比如在某个路径下/ruozedata/access/day=2020-03-28/…下，要做ETL数据清洗，清洗完后还是一张表数据存放在hdfs上，清洗完后多少列，分割符是什么都知道，此时就可以创建外部表再通过location指定过去即可；这种情况很保险，即使表删除，hdfs上的源数据也是保留着的。

• 任何一个人做了什么操作都要进行记录，这个动作需要记录的信息是非常多的，在mysql中的话就需要进行分表，log_20190327、log_20190328；表中字段需要创建索引。

–> 对应到Hive中，如果你不分区，log 0327 0328
select … from log where day = ‘…’
全路径扫描涉及到的IO开销是非常大的：读取数据–>磁盘IO，分布式计算–>网络IO

–> 分区表：分区其实对应的就是HDFS上的一个文件夹/目录：
log
day=20190327
day=20190328

比较：全目录进行搜索 VS 指定目录进行搜索？

2.6、Hive中的分区表&&刷新元数据信息msck

• 在10086电信业务中，我们会输入1进入话费查询，再下一层输入2进入话费充值栏，这就是Ticket：服务请求

    1、创建订单分区表：order_partition
    create table order_partition(
    	order_no string,
    	event_time string
    )
    PARTITIONED BY (event_month string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
  
    hive (ruozedata_hive)> create table order_partition(order_no string,event_time string) partitioned by (event_month string) row format delimited fields terminated by '\t';
    OK
    Time taken: 0.057 seconds
  
    2、分区列并不是一个真正的表字段，它对应在hdfs就是一个文件夹
    # col_name              data_type               comment             
             
    order_no                string                                      
    event_time              string                                      
                     
    # Partition Information          
    # col_name              data_type               comment             
                     
    event_month             string      
  
    3、加载数据进分区表：
    load data local inpath '/home/hadoop/data/order_created.txt' into table order_partition PARTITION (event_month='2020-01');
  
    4、查询分区表中的条件：
    hive (ruozedata_hive)> select * from order_partition where event_month='2020-01';
    OK
    order_partition.order_no        order_partition.event_time      order_partition.event_month
    10703007267488  2014-05-01 06:01:12.334+01      2020-01
    10101043505096  2014-05-01 07:28:12.342+01      2020-01
    10103043509747  2014-05-01 07:50:12.33+01       2020-01
    10103043501575  2014-05-01 09:27:12.33+01       2020-01
    10104043514061  2014-05-01 09:03:12.324+01      2020-01
    Time taken: 0.204 seconds, Fetched: 5 row(s)
  
    //注意：分区表查询的时候要根据分区条件进行查找
  

• 假设我们已经清洗了一部分的数据出来，直接在hdfs上创建2020-02的文件夹，再把数据put上去；此时我们选择去Hive中进行查询，是查询不到的；

• 对于分区表操作，如果你的数据是写入到HDFS，默认sql查询是查不到的，为什么呢？因为在元数据中是没有这个event_month=202002的记录的。

    1、在hdfs上级联创建目录，与2020-01分区文件夹并行：
  [hadoop@hadoop001 data]$ hdfs dfs -mkdir -p /user/hive/warehouse/ruozedata_hive.db/order_partition/event_month=2020-02
    20/03/28 23:01:53 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
    
    2、把本地Linux文件上传到2020-02目录下：
    [hadoop@hadoop001 data]$ hdfs dfs -put /home/hadoop/data/order_created.txt /user/hive/warehouse/ruozedata_hive.db/order_partition/event_month=2020-02/
    
    3、直接在Hive中测试能否查询到这个数据：
    hive (ruozedata_hive)> select * from order_partition where event_time='2020-02';
    OK
    order_partition.order_no        order_partition.event_time      order_partition.event_month
    Time taken: 0.106 seconds
  
    4、经验证：在Hive中查询不到2020-02的分区数据：
    原因是mysql中的元数据信息没有它，去到MySQL中验证：
  
    //查看到创建的订单分区表id是18；
    mysql> select * from tbls \G;
    *************************** 4. row ***************************
        TBL_ID: 18
   CREATE_TIME: 1585406888
         DB_ID: 6
  LAST_ACCESS_TIME: 0
             OWNER: hadoop
        OWNER_TYPE: USER
         RETENTION: 0
             SD_ID: 18
          TBL_NAME: order_partition
          TBL_TYPE: MANAGED_TABLE
    VIEW_EXPANDED_TEXT: NULL
    VIEW_ORIGINAL_TEXT: NULL
    4 rows in set (0.00 sec)
  
    //分区表信息存储在partitions中，根据表id18查询分区的信息，mysql中没有关于event_month=2020-02的相关信息：
    mysql> select * from partitions where TBL_ID=18;
    +---------+-------------+------------------+---------------------+-------+--------+
    | PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME           | SD_ID | TBL_ID |
    +---------+-------------+------------------+---------------------+-------+--------+
    |       1 |  1585407336 |                0 | event_month=2020-01 |    19 |     18 |
    +---------+-------------+------------------+---------------------+-------+--------+
    1 row in set (0.00 sec)
    
    5、如何解决？
    刷新分区信息：msck repair table order_partition;
    hive (ruozedata_hive)> msck repair table order_partition;
    OK
    Partitions not in metastore:    order_partition:event_month=2020-02
    Repair: Added partition to metastore order_partition:event_month=2020-02
    Time taken: 0.249 seconds, Fetched: 2 row(s)
  
    	6、再去到mysql中查询到元数据信息：
    	mysql> select * from partitions where TBL_ID=18;
    +---------+-------------+------------------+---------------------+-------+--------+
    | PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME           | SD_ID | TBL_ID |
    +---------+-------------+------------------+---------------------+-------+--------+
    |       1 |  1585407336 |                0 | event_month=2020-01 |    19 |     18 |
    |       2 |  1585409080 |                0 | event_month=2020-02 |    20 |     18 |
    +---------+-------------+------------------+---------------------+-------+--------+
    2 rows in set (0.00 sec)
  

注意：外部表进来，不刷新元数据，是查不到数据的；生产上msck操作是一定不能用的，这是一个重量级的操作

取而代之msck的是另一个操作：

• alter table order_partition ADD IF NOT EXISTS PARTITION(event_month=‘2020-03’);

• 如何展示一个表下有多少分区信息？

• hive (ruozedata_hive)> show partitions order_partition;
  OK
  partition
  event_month=2020-01
  event_month=2020-02
  event_month=2020-03
  Time taken: 0.049 seconds, Fetched: 3 row(s)

三、Hive中的多级分区&&静态分区&&动态分区

• 与单级分区的区别，创建的时候多指定一个分区列，加载数据的时候也多指定了一个分区列信息：

    1、创建两层分区表：
    create table order_multi_partition(
    		order_no string,
    		event_time string
    	)
    	PARTITIONED BY (event_month string,step string)
    	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
  
    2、两层分区也就是两个目录：
    # Partition Information          
    # col_name              data_type               comment             
                     
    event_month             string                                      
    step                    string   
    
    3、加载数据也是一样的：
    hive (ruozedata_hive)> load data local inpath '/home/hadoop/data/order_created.txt' into table order_multi_partition partition(event_month='2020-01',step='1');
    Loading data to table ruozedata_hive.order_multi_partition partition (event_month=2020-01, step=1)
    Partition ruozedata_hive.order_multi_partition{event_month=2020-01, step=1} stats: [numFiles=1, numRows=0, totalSize=213, rawDataSize=0]
    OK
    Time taken: 0.27 seconds
  

注意：使用分区表时，加载数据，一定要指定所有的分区字段，不指定是不行的。

创建部门员工分区表：

hive (ruozedata_hive)> create table emp_partition(emono int,ename string,job string,mgr int,date string,sal double,comm double,deptno int) PARTITIONED BY (deptno int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

• 使用sql从emp表中把数据插入到emp_partition表中：

    Standard syntax:
    INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
    INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
  
    2、如下：
    insert overwrite table emp_partition PARTITION (deptno=10) select * from emp where deptno=10;
    
    放到hive中执行出现如下错误：
    FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different '10': Table insclause-0 has 7 columns, but query has 8 columns.
  
    //错误原因：我们创建的hive分区表只有7个字段，而select * 的表中有8个字段。
    
    3、最笨的解决办法：一个一个字段的去写：
    insert overwrite table emp_partition PARTITION (deptno=10) select empno,ename,job,mgr,date,sal,comm from emp where deptno=10;
    //这种方式是非常麻烦的
  

–> 动态分区（和静态分区差别在参数上，而不是在语法上）：
找到一种有效的方式快速的把数据插进去呢？

创建动态分区：

• 为了能够使得deptno自动对号入座：

  1、语法：
  create table emp_dynamic_partition(empno int,ename string,job string,mgr int,date string,sal double,comm double) PARTITIONED BY (deptno int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;

  2、如下：
  hive (ruozedata_hive)> insert overwrite table emp_dynamic_partition PARTITION (deptno) select emono,ename,job,mgr,date,sal,comm,deptno from emp;
  FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
  hive (ruozedata_hive)> set hive.exec.dynamic.partition.mode=nonstrict;

• 动态分区的前提是设置为非严格模式

3.1、Hive中的数据加载

• 1、加载数据的语法：

    LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
  
    LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)
  

load data：加载数据；
local：有local就是从Linux本地；没有local就是hdfs上的路径；
inpath：指定路径
overwrite：有就会覆盖，没有就会进行追加（数据重复）

1、从hdfs加载数据进emp表，会发现/ruozedata/emp.txt这个目录下的文件，会被移动到/user/hive/warehouse/ruozedata_hive.db/emp这个目录下：
hive (ruozedata_hive)> load data inpath '/ruozedata/emp.txt' into table emp;

2、为什么数据没有，因为emp表是内部表，它的所有生命周期都是交给/user/hive/warehouse来进行管理的：

比如emp3是我们需要的表，如何从emp把数据写进去：

• create table emp3 as select empno,ename from emp;

• create table … as select … 表不能事先存在

    1、可以从emp表中选取字段再进行写入emp3：
    hive (ruozedata_hive)> create table emp3 as select empno,ename from emp;
    Query ID = hadoop_20200329142929_60ac5c1f-2dbd-4688-9316-aedcde01c186
    Total jobs = 3
    Launching Job 1 out of 3
    Number of reduce tasks is set to 0 since there's no reduce operator
  

• insert overwrite table emp4 select * from emp; 表必须事先存在，先拷贝表结构：create table emp4 like emp;

• 也支持的语法：from emp insert into table emp4 select *;

注意：可能会出现的2类问题：

1、目标表中有8个字段，你sql中只插入两个字段，所以会出现问题：
hive (ruozedata_hive)> from emp insert into table emp4 select ename,mgr;
FAILED: SemanticException [Error 10044]: Line 1:27 Cannot insert into target table because column number/types are different 'emp4': Table insclause-0 has 8 columns, but query has 2 columns.

2、假设插入数据的时候，两个字段类型一致，但是你把它们的顺序搞反了，就会出现问题：
hive (ruozedata_hive)> insert overwrite table emp4 select emono,job,ename,mgr,date,sal,comm,deptno from emp;

3、因为字段顺序反了，所以第二种方式运行出来的结果就会废咯：去到emp4表种查询数据就会废咯
hive (ruozedata_hive)> select * from emp4 where ename = 'SMITH';

3.2、Hive中的数据导出

1、写到Linux本地目录上：/home/hadoop/tmp/hivetmp目录没有创建就会自动创建：

• hive (ruozedata_hive)> insert overwrite local directory ‘/home/hadoop/tmp/hivetmp’ select empno,ename,mgr from emp;

• 我们什么分割符都没有设置：

    [hadoop@hadoop001 hivetmp]$ cat 000000_0 
    7369SMITH7902
    7499ALLEN7698
    7521WARD7698
    7566JONES7839
  

• 指定分割符：insert overwrite local directory ‘/home/hadoop/tmp/hivetmp’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select empno,ename,mgr from emp;

    [hadoop@hadoop001 hivetmp]$ cat 000000_0 
    7369    SMITH   7902
    7782    CLARK   7839
    7788    SCOTT   7566
    7839    KING    \N
  

2、写到hdfs的目录上，把local删掉，再指定一个目录即可；

• hive (ruozedata_hive)> insert overwrite directory ‘/ruozedata/’ select empno,ename,mgr from emp;

• 同样指定分割符：insert overwrite directory ‘/ruozedata/’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select empno,ename,mgr from emp;
  
  3、直接从hdfs上get到本地linux系统目录：

• hdfs dfs -get /ruozedata/000000_0 /home/hadoop/data/

4、使用交互式命令：hive -e

1、hive还能够grep过滤字段
[hadoop@hadoop001 data]$ hive -e 'select * from ruozedata_hive.emp' |grep SALESMAN > file
[hadoop@hadoop001 data]$ cat file
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30
7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30
7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30
7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30

5、Sqoop可以做数据导入导出操作

3.3、使用SQL进行查询

1、进行select的时候对字段进行重命名：
hive (ruozedata_hive)> select empno as id,ename as name from emp;
OK
id      name
7369    SMITH
7499    ALLEN
7521    WARD

2、根据where条件来进行判断：‘
hive (ruozedata_hive)> select * from emp where ename = 'SMITH';
OK
emp.empno       emp.ename       emp.job emp.mgr emp.date        emp.sal emp.comm      emp.deptno
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

hive (ruozedata_hive)> select * from emp where sal between 800 and 1500;
OK
emp.empno       emp.ename       emp.job emp.mgr emp.date        emp.sal emp.comm      emp.deptno
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

hive (ruozedata_hive)> select * from emp where ename in ('SMITH','john');
OK
emp.empno       emp.ename       emp.job emp.mgr emp.date        emp.sal emp.comm      emp.deptno
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

hive (ruozedata_hive)> select * from emp where sal like '1%';

//like需要使用_ %这些
hive (ruozedata_hive)> select * from emp where sal like '_2%'

//rlike包含的是正则表达式
hive (ruozedata_hive)> select * from emp where sal rlike '[2]'

对于生产上的数据空值：null、 NULL、 “” 、" "

3.4、Hive中的聚合函数

1、求deptno=10的总次数 --> 为deptno=10计次：
hive (ruozedata_hive)> select count(1) from emp where deptno=10;
Query ID = hadoop_20200329155151_17c78e33-141a-4f58-9109-fca8b3a22f5a
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1

2、求最大、最小、工资和、平均工资
hive (ruozedata_hive)> select max(sal),min(sal),sum(sal),avg(sal) from emp;
Query ID = hadoop_20200329155252_1f9c3d47-a567-4884-afe7-5d1c062bfba4

3、求每个部门的平均工资（按照部门进行分组）：
hive (ruozedata_hive)> select deptno,avg(sal) from emp group by deptno;

//select中出现的字段，如果没有出现在group by中，必须出现在聚合函数中；
错误的演示：
hive (ruozedata_hive)> select ename,deptno,avg(sal) from emp group by deptno;
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'ename'

4、求每个部门的平均工资大于2000的
select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000;
deptno  avg_sal
NULL    10300.0
10      2916.6666666666665
20      2175.0

3.5、为什么有的sql会跑mapreduce，有的不跑mapreduce

• 在hive参数配置页面：https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties，找到这个参数：hive.fetch.task.conversion

查看到当前Hive中此参数设置的是more；回到官网进行解读：
hive (ruozedata_hive)> set hive.fetch.task.conversion;
hive.fetch.task.conversion=more

这个参数可以设置为none、minimal、more;
1、设置为none后，任何操作都是跑mapreduce
2、设置为more的时候：出现select/filter/limit等的都不会跑mapreduce

记住一点：select *、指定字段、常用过滤条件是分区或者某一个字段内容都不会跑mapredcue；涉及统计的话会跑mapreduce

四、面试题

1、Hive中where和having的区别？

• where是单条记录的过滤，having是分组过后的过滤

2、count(1)和count(*)的区别？