Hive入门&基本操作

Hive

什么是 Hive

• Hive 是由 FaceBook 开源的，用于解决海量结构化日志数据的统计

• Hive 是基于 Hadoop 的一个数据仓库，可以将结构化日志数据映射为一张表，提供类 SQL 的查询功能：HQL (Hive Query Language)

• Hive 的本质是将 HQL 转化为 MapReduce 程序(将 HQL 对应的功能，转化为使用 MapReduce 来实现)

• 

• Hive 处理的数据存储在 HDFS 上

• Hive 分析数据的底层的默认实现是 MapReduce

• 执行程序运行在 yarn 上

Hive 优缺点

优点

• 采用类 SQL 的写法，提供快速开发能力
• 避免去写 MapReduce 减少开发学习成本
• Hive 执行延迟比较高，适用于数据分析，因此 Hive 优势在于处理大数据，小数据的处理没有优势
• Hive 支持自定义函数

缺点

• 迭代式(将前一个的计算结果作为后一个计算的输入)算法无法表达
• 数据挖掘方面不擅长
• Hive 自动生成的 MapReduce ，通常情况下不够智能化

Hive 架构

• 

• 

Hive 与数据库比较

• Hive 除了采用类 SQL 的查询语言外，与数据库再无类似之处
• Hive 是针对数据仓库设计的，数据仓库的内容通常是读多写少，不建议使用 Hive 对数据进行改写，所有的数据都是在加载时就确定好的，若一定要使用 Hive 对数据进行改写，可以先将数据下载下来，再进行修改，然后上传到 Hive 的数据仓库，但是效率极低，而数据库的改写效率则高得多
• Hive 中没有索引，查询数据时，需要遍历整张表，延迟较高，同时，Hive 默认使用的是 MapReduce 的计算框架，此框架本身具有高延迟，相反，数据库的执行延迟很低
• Hive 建立在集群上，支持的数据规模很大，对应的，数据库的数据规模相对较小

Hive 的安装 .(环境搭建重点 )

注: Hive 的安装最好使用完全安装的 Linux ，最小安装的 Linux 在 安装 MySQL 时会有各种问题

资源下载

• Hive
  • http://archive.apache.org/dist/hive/
• MySQL
  • https://downloads.mysql.com/archives/community/
• MySQL连接驱动
  • https://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.37

安装流程

• 解压 Hive 安装包

• 配置 Hive 环境变量

  • #HIVE_HOME
    export HIVE_HOME={Hive 安装路径}
    export PATH=$PATH:$HIVE_HOME/bin

• 初始化元数据库(注:此步骤在配置 hive-site.xml 后再执行)

  • schematool -dbType derby -initSchema

• 启动 Hive

  • hive

Hive 使用.(数据分析重点)

简单使用

• show databases; 显示数据库
• show tables; 显示表
• create table 表名 (字段名 字段类型); 创建表
• insert into 表名 (字段名) values ( 字段值); 插入数据
• select * from 表名; 查询表信息
• 补充
  • Hive 是基于 hdfs 的，存储的表数据，默认在 hdfs 的 user / hive 目录下可以找到

MySQL 安装

• 查看是否安装过 MySQL
  • rpm -qa | grep mariadb
• 若安装过，卸载
  • rpm -e --nodeps mariadb-libs
• 解压 MySQL
  • tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C 【指定一个目录】
• 安装 MySQL
  • 来到 MySQL 解压目录
  • 按照顺序执行
    • rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
  • 删除 /etc/my.cnf 文件中 datadir 指向的目录下的所有内容(如果有的情况下)
  • 初始化数据库
    • mysqld --initialize --user=mysql
  • 查看临时生成的 MySQL 密码
    • cat /var/log/mysqld.log
  • 启动 MySQL 服务
    • systemctl start mysqld
  • 登录 MySQL 数据库，使用之前临时的 MySQL 密码
    • mysql -u root -p
  • 修改 root 用户的初始密码
    • set password = password(“新密码”);
  • 允许任意 ip 连接
    • update mysql.user set host=’%’ where user=‘root’;
    • flush privileges;

Hive 配置元数据到 MySQL.( 环境搭建重点 )

• 在 $HIVE_HOME/conf 目录下新建 hive-site.xml

  • vim $HIVE_HOME/conf/hive-site.xml

• 添加如下内容

  • <?xml version="1.0"?>
    <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
    <configuration>
     <!-- jdbc 连接的 URL -->
     <property>
         <name>javax.jdo.option.ConnectionURL</name>
         <value>jdbc:mysql://主机名:3306/metastore?useSSL=false</value>
     </property>
     <!-- jdbc 连接的 Driver-->
     <property>
         <name>javax.jdo.option.ConnectionDriverName</name>
         <value>com.mysql.jdbc.Driver</value>
     </property>
     <!-- jdbc 连接的 username-->
     <property>
         <name>javax.jdo.option.ConnectionUserName</name>
         <value>root</value>
     </property>
     <!-- jdbc 连接的 password -->
     <property>
         <name>javax.jdo.option.ConnectionPassword</name>
         <value>mysql密码</value>
     </property>
     <!-- Hive 元数据存储版本的验证 -->
     <property>
         <name>hive.metastore.schema.verification</name>
         <value>false</value>
     </property>
     <!--元数据存储授权-->
     <property>
         <name>hive.metastore.event.db.notification.api.auth</name>
         <value>false</value>
     </property>
     <!-- Hive 默认在 HDFS 的工作目录 -->
     <property>
         <name>hive.metastore.warehouse.dir</name>
         <value>/user/hive/warehouse</value>
     </property>
     <!-- 指定存储元数据要连接的地址 -->
    <property>
    	<name>hive.metastore.uris</name>
    	<value>thrift://主机名:9083</value>
    </property>
    </configuration>
    

• 登录 MySQL ，创建元数据库

  • create database metastore;

• 将 mysql 驱动添加到 Hive 的 lib 目录下

• 初始化元数据库

  • schematool -initSchema -dbType mysql -verbose

• 启动 Hive

  • hive

• 使用 Hive 测试一下元数据库

  • show tables;

总结补充

• 关于元数据库 & Hive & HDFS 的解析
  • Hive 中的创建的表，插入到数据等，实际上并不是直接放入元数据库 MySQL 中的，而是存储在 HDFS 文件系统上的，MySQL 元数据库 metastore 中存储的，实际上是创建的表和插入的数据在 HDFS 存储时的文件路径，在 MySQL 元数据库 metastore 中，有 DBS 和 TBLS 两张表，DBS 中存储的是 Hive 创建的数据库在 HDFS 中保存的路径信息，TBLS 中保存的是 Hive 创建的表在 HDFS 中保存路径的信息，当在 Hive 中执行查询语句时，实际上就是在元数据库的 DBS 和 TBLS 中找到对应的 HDFS 文件路径，将数据库和表对应的文件路径拼接起来，就是 HDFS 文件系统上保存数据的位置
    • 例如，数据库的元数据为: hdfs://master-3.x-wfb:8020/user/hive/warehouse 表的有元数据为: test，那么 test 表的数据在 HDFS 上保存的路径就为: hdfs://master-3.x-wfb:8020/user/hive/warehouse/test
  • 通过上面的说明，可以看出，在 Hive 上，插入数据，创建表等操作的顺序，可以随意，只要保证元数据库中有对应的元信息，HDFS 上有对应的路径，路径下有对应的数据即可，哪怕不创建表，只要在 HDFS 上有对应的数据，直接查询也是可以的

使用元数据服务的方式访问 Hive.( 了解即可 )

• 在 Hive-site.xml 中添加如下配置信息

  • <!-- 指定存储元数据要连接的地址 -->
    <property>
     <name>hive.metastore.uris</name>
     <value>thrift://主机名:9083</value>
    </property>
    

• 启动 metastore

  • hive --service metastore
  • 注: 启动后，该窗口不能再操作，需要新起一个窗口

• 启动 Hive

  • hive

• 注: 该操作只是为外部提供一个调用的端口，使得外部可以通过该端口连接到 Hive

使用 JDBC 方式访问 Hive.( 了解即可 )

• 

注: 上面的元数据和JDBC连接方式都只做演示，为了后续方便操作，将 hive-site.xml 中涉及到的配置文件注释掉即可

补充: Hive 其他命令操作

• exit; (退出 hive)
• dfs -ls 指定路径 (查看 hdfs 文件目录)
• set; (查看当前所有的配置信息)

Hive 常用交互命令

• hive -e “sql 语句;” (不进入 hive ，直接执行 sql 语句)
• hive -f 【sql 文件路径】(执行 sql 文件)
• hive -f 【sql 文件路径】> 【文件路径】(将执行的结果存入指定文件中)
• cat .hivehistory (查看当前用户在 hive 中执行的所有命令，需要进入当前用户家目录)

补充

• select count(*) from 表名; 没有运行 MR 任务的原因
  • 在 MySQL 的元数据中，保存了表的行数，因此，不会运行 MR 任务
• select count(*) from 表名; 查询结果与实际不符的原因
  • 若是直接使用 hadoop fs -put 命令向表对应的 HDFS 文件系统中添加的数据，那么，MySQL 的表中元数据没有更新，查询的结果也就不相符

Hive 数据类型

• 基本数据类型

  • 

  • 补充

    • 对于时间类型 TIMESTAMP ，只要将字符串写成 yyyy-MM-dd，Hive 就能自动转换为时间类型

• 集合数据类型

  • 

  • 补充

    • 集合数据类型之间支持嵌套

• 案例实操

  • 源数据

    • songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
      yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

  • 建表

    • create table test2(
      name string,
      friends array,
      children map<string, int>,
      address struct<street:string, city:string>
      )
      row format delimited fields terminated by ‘,’ // 字段之间使用 ，分割
      collection items terminated by ‘_’ // 集合(包括 array 和 struce 以及 map )元素之间使用 _ 分割
      map keys terminated by ‘:’ // map 的 key 和 value 之间使用 : 分割
      lines terminated by ‘\n’; // 每一行数据使用 \n 分割，默认就为 \n

  • 插入数据

    • 直接将文件上传到 test2 表 在 hdfs 对应的路径即可

  • 补充

    • 三种集合的访问方式
      • select friends[数组下标],children[‘key 值’],address.元素名 from test2
    • 查看完整的创建表
      • show create table 表名
    • 查看创建数据库的完整语句
      • show create database 数据库名

DDL 数据定义

DDL:数据定义语言，用来创建 & 修改 & 删除数据库，表，视图，索引等

创建数据库

• 直接创建一个数据库
  • create database 数据库名
• 创建数据库时，判断是否存在
  • create database if not exists 数据库名 location ‘数据库在 hdfs 上的路径’
• 创建数据库到指定的 hdfs 文件路径
  • create database if not exists 数据库名 location ‘数据库在 hdfs 上的路径’

查询数据库

• 显示所有数据库
  • show databases;
• 过滤显示数据库
  • show databases like ‘数据库名(支持正则)’;
• 显示数据库信息
  • desc database ‘数据库名’;
• 显示数据库详细信息
  • desc database extended ‘数据库名’;
• 切换当前数据库
  • use ‘数据库名’;

修改数据库

• 修改数据库属性
  • alter database 数据库名 set dbproperties(“属性名”=“属性值”);
  • 例如: alter database stu set dbproperties(“createTime”=“2020-10-23”);

删除数据库

• 删除空数据库
  • drop database 空数据库名
• 强制删除数据库
  • drop database 数据库名 cascaed;

创建表

• 建表语句解析

  • CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
    [(col_name data_type [COMMENT col_comment], ...)]
    [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]
    [ROW FORMAT row_format]
    [STORED AS file_format]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (property_name=property_value, ...)]
    [AS select_statement]
    /*
    EXTERNAL: 外部的，Hive 的表有内外之分
    COMMENT 注释:表的注释
    PARTITIONED BY: 分区表
    CLUSTERED BY: 分桶表
    SORTED BY (col_name [ASC|DESC], ...) INTO num_buckets BUCKETS: 分桶表
    ROW FORMAT 行格式: 定义行的格式
    STORED AS 文件格式: 定义文件格式
    LOCATION hdfs路径: 指定表在 hdfs 上的存储路径
    TBLPROPERTIES (属性名=属性值, ...): 表的额外属性
    AS select_statement: 从查询出来的结果创建表，创建的表自带查询出的数据
    */
    

• 内 | 外部创建表 & 区别

  • Hive 默认创建的表都是管理表，也叫内部表，Hive 会或多或少的控制这些数据的生命周期，默认情况下，表中的数据被存放在由配置项所定义的 hdfs 路径的子路径下，删除表时，对应路径下的数据也被删除，管理表不适合与其他工具共享数据
  • 外部表，顾名思义，表是外部的，Hive 并非完全拥有这份数据，删除表时，只会删除该表在元数据库中对应的元数据，而 hdfs 上保存的数据并为被删除

• 内 | 外部表转换 & 查看

  • 内 | 外部表转化
    • alter table 表名 set tblproperties(‘EXTERNAL’=‘FALSE’); 修改为内部表
    • alter table 表名 set tblproperties(‘EXTERNAL’=‘TRUE’); 修改为外部表
  • 查看表的类型
    • desc formatted 表名
  • 内部表转外部表
    • alter table 表名 set tblproperties (“EXTERNAL”=“TRUE”);
  • 外部表转内部表
    • alter table 表名 set tblproperties(“EXTERNAL”=“FALSE”);

以指定分隔符分割字段创建表

• 创建表
  • create table test4(id int,name string) row format delimited fields terminated by ‘,’; 创建字段之间以逗号分割的表
• 插入数据
  • insert into test4 values (1001,‘zhangsan’);
  • 对应的，在 hdfs 中，数据保存的格式，字段之间也是以逗号分割，同样的，直接创建一个同样分割符格式的文件存放到表对应的 hdfs 路径上，也能被以同样格式创建的表所读取并且能够查询到，相反的，不同格式的数据，若和表存放在同样路径下，分割后的字段数据，不能转换和不存在的值，都用 NULL 表示，就算存在能够转换的值，转换出的结果，因为分隔符不符，数据也可能不相符
    • 

修改删除表

• 修改表名字
  • alter table 表名 rename to 新表名;
• 增加 | 修改 | 替换表的列信息
  • 修改列
    • alter table 表名 change 旧列名 新列名 新列名数据类型;
  • 增加和替换
    • alter table 表名 add | replace clumns (列名，数据类型);
    • 注: add 代表在所有字段后增加一列字段，replace 代表替换表中所有字段(replace 的字段，会覆盖表中的所有字段，也就是说，可以通过 replace 删除增加修改表中的某一个字段)
• 删除表
  • drop table 表名;

DML 数据操作

DML: 数据操作语言，对数据的增 & 删 & 改操作

向表中装载数据

• load data [local] inpath ‘导入的数据的路径’ [overwrite] into table 表名 [partition (partcol1=val1,…)];
  • local 代表在本地加载文件，不加则在 hdfs 上查找，将 hdfs 上对应的文件移动到表对应的路径下
  • overwrite 代表是否重新覆盖表中的内容，不加则不覆盖其中的内容
  • partition (partcol1=val1,…) 上传到分区

基本插入数据

• insert into table 表名 values (值1，值2 …),(值1，值2);

基本模式插入

• 以查询的方式插入数据
  • insert into table 表名 查询语句
  • insert overwrite table 表名 查询语句
  • 补充
    • into 向表中添加数据
    • overwrite 覆盖表中的数据

查询语句创建表并导入数据

• create table if not exists 表名 as select * from 表名

建表时通过 Location 指定表在 hdfs 上的路径

• create table 表名 (id int,name string) row format delimited fields terminated by ‘/t’ location ‘hdfs 上的路径’;

Import 导入数据

• 略

Insert 导出数据

• 导出到本地
  • insert overwrite local directory ‘本地路径’ 查询语句;
• 格式化导出到本地
  • insert overwrite local directory ‘本地路径’ row format delimited fields terminated by ‘分割符’ 查询语句;
• 将查询结果导出到 hdfs 上
  • insert overwrite directory ‘本地路径’ row format delimited fields terminated by ‘分割符’ 查询语句;

清除表中的数据

• truncate table student;
  • truncate 只能清除管理表(内部表)中的数据，外部表不能清除

查询

单表查询 & 常用函数 & 运算符

• 查询全表 & 指定列 & 注意事项

  • 查询全表
    • select * from 表名
  • 查询指定列
    • select 列名 form 表名
  • 注意事项
    • 在 linux 命令行，不能直接使用 tab 进行 sql 格式的调整

• 别名 & 运算符 & 常用函数

  • 别名
    • select 列名 列别名 from 表名
  • 运算符
    • +，-，*，/，%
  • 常用函数
    • count(); 计数
    • max(); 最大值
    • min(); 最小值
    • sum(); 求和
    • avg(); 平均值

• 聚合函数 Limit & Where

  • Limit 限制返回的行数
    • select * from 表名 limit 5; 只返回前五行
    • select * from 表名 limit 0,5; (偏移量从 0 开始，代表第一行，返回前 5 条记录)
  • Where 限制查询的条件
    • select * from 表名 where 查询条件

• 比较运算和逻辑运算符

  • 

• group by & having 分组

  • 按照某字段分组

    • select 字段1，字段2 from 表名 group by 字段1;
    • 若只按照一个字段进行分组，那么其余字段的值就只能有一种情况，否则，分组条件就必须加上其余字段
    • 分组的实质就是将字段值相同的作为一个组，将不同字段之间不同的值的组合都呈现出来，例如，若使用了 count() 函数，则相同组的 count() 函数的值就会累加，类似于 MR 程序中 wordCount 的执行原理
      • 

  • 对分组后的字段进行条件限制

    • select 字段1，字段2 from 表名 group by 字段1 having 字段1 > 1000;

  • 补充

    • group by 子句必须出现在 where 子句之后，‎order by 子句之前. having 语句必须在 order by 子句之后。(where 先执行，再 group by 分组；group by 先分组，having 再执行；order by 子句在 group by 之后，having 之前)
    • 通过 where 筛选结果，group by 进行分组，order by 进行排序，再使用 having 进行数据的过滤
    • having 弥补了 where 不能和聚合函数(对一组值进行计算，返回单一结果)使用的缺陷

多表查询

join 语句

• 内连接 join
  • 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
    • select e.empno,e.ename,d.deptno from emp e join dept d on e.deptno = d.deptno
• 左外连接 left join
  • 以 left join 左边的表为准，根据 on 条件返回结果，当右边表存在不满足左边表的字段时，该字段以 NULL 代替
    • select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
  • 当使用了 where 子句时，left join 左边表满足 where 子句的数据将会被返回
• 右外连接
  • 以 right join 右边的表为准，根据 on 条件返回结果，当左边表存在不满足右边表的字段时，该字段以 NULL 代替
    • select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
  • 当使用了 where 子句时，right jion 右边表满足 where 子句的数据将会被返回
• 全外连接
  • 以 full join 两边的表为准，根据 on 条件返回结果，任意表中存在不满足的字段时，该字段以 NULL 代替
    • select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
  • 当使用 where 子句时，若子句条件以左边表为准，那么查询的是左表独有的数据，若子句条件以右表为准，那么查询出的数据为右表独有数据，若子句条件包含两张表且用 and 连接，查询的是两张表共有的数据，若以 or 连接，则查询出的是两张表所有的数据
• 笛卡尔积(表A的每一行与表B之间的每一行数据都连接上了，总共的连接数是 A 表行数 * B 表行数)
  • 笛卡尔积会在下面的条件下产生
    • 省略了连接条件
    • 连接条件无效
    • 所有表的所有行本来就是相互对应的

排序

常用排序

• order by 全局排序，只使用一个 reduce 来执行排序任务
  • order by asc 升序(默认)
  • order by desc 降序
  • order by 子句在 select 语句结尾使用
  • order by 字段1，字段2 按照多个列排序
• sort by 对于大规模的数据，order by 的全局排序并不适用，使用 sort by 可以对每一个 reduce 产生一个排序文件
  • 使用方法
    • 设置 reduce 个数
      • set mapreduce.job.reduces = reduce 个数;
    • 查看设置的 reduce 个数
      • set mapreduce.job.reduces;
    • 使用 order by 子句进行分区排序
      • select e.deptno,e.empno,e.ename from emp order by e.deptno
      • 按照 deptno 字段进行升序排序，将排序的文件分为 (reduce 个数) 个分区，Hive 中，在不指定分区规则的情况下，使用的分区方式是随机的，也就是说，被排序的数据被放到那个分区中也是随机的
• distribute by 按照某一字段进行分区
  • select * from emp distribute by ename sort by deptno;
    • 按照 ename 字段进行分区，每个分区内，按照 deptno 进行排序
• cluster by 当 sort by 和 distribute by 依照的字段相同时使用 cluster by
  • 一下两种写法等价
    • select * from emp distribute by empno sort by empno;
    • select * from emp cluster by empno;

分区

分区简介

• 分区实际上就是将一张表的内容分为多个部分，对于小数据量的表，该操作对执行效率的影响不大，但是当数据量大时，分区后的表，可以指定在那个分区进行查询，避免了查询时，直接查询全表，效率极低
• 每个分区对于的实际上就是 hdfs 上的一个文件夹，该文件夹中保存的是该分区中的表数据

创建分区表

• 数据准备

  • 分区1

    10 ACCOUNTING 1700
    20 RESEARCH 1800

    分区2

    30 SALES 1900
    40 OPERATIONS 1700

    分区3

    50 TEST 2000
    60 DEV 1900

• 创建分区表

  • hive (default)> create table dept_partition( deptno int, dname string, loc string) partitioned by (day string) row format delimited fields terminated by ‘\t’;
    • partitioned by (day string)
      • 该字段是一个分区字段，意为该表属于那个分区

• 加载数据到分区表

  • load data local inpath ‘/opt/software/dept3.txt’ overwrite into table dept_par partition (day = ‘2020-10-25’);
    • 加载数据时，需要使用 partition (分区字段 = ‘字段值’); 来指定该数据加载到表的那个分区中

• 单分区查询

  • select * from dept_par where day = ‘2020-10-23’;

• 多分区联合查询

  • select * from dept_par where day = ‘2020-10-23’ or day = ‘2020-10-24’;

• 全部分区查询

  • select * form dept_par;

分区的增 & 删 & 改

• 增加单个分区
  • alter table dept_partition add partition(day=‘20200404’);
• 增加多个分区
  • alter table dept_partition add partition(day=‘2020-10-26’) partition(day=‘2020-10-27’);
    • 多个分区使用空格隔开
• 删除单个分区
  • alter table dept_partition drop partition (day=‘2020-10-23’); 删除分区后，分区中的表数据也被删除，hdfs 上也是一样
• 删除多个分区
  • alter table dept_partition drop partition (day=‘2020-10-28’),partition (day=‘2020-10-27’); 删除分区后，分区中的表数据也被删除，hdfs 上也是一样
    • 多个分区使用逗号隔开
• 查看表的分区
  • show partition dept_par;

二级分区

• 创建二级分区表
  • create table dept_par2 (deptno int,dname string,loc string) partitioned by (day string,hour string) row format delimited fields terminated by ‘\t’;
    • 二级分区表中，分区字段有两个，第二个字段就代表二级分区
• 查询二级分区
  • 全表查询
    • select * from dept_par2;
  • 分区查询(既包括二级分区，也可以单查一级分区)
    • select * from dept_par2 where day = ‘2020-10-28’ and hour = ‘10’;

使得 HDFS 文件系统与分区表产生关联

• 若根据分区在 HDFS 上的目录结构，直接新建一个目录并传入文件，预期能够通过查询语句查询出分区中的数据，实际上，通过查询语句查询出的数据中，并没有该数据，原因在于，MySQL 的元数据库中，并没有保存该分区的元数据
• 解决办法
  • msck repair table dept_partition2; 使用分区修复命令，修复指定表的分区
  • alter table dept_partition2 add partition(day=‘201709’,hour=‘14’); 直接添加对应的分区
  • load data local inpath ‘/opt/module/hive/datas/dept_20200401.log’ into table dept_partition2 partition(day=‘20200401’,hour=‘15’); 创建文件夹后，通过 load 将数据加载到分区中

动态分区

• 略，可查看尚硅谷原笔记

分桶表

• 略，可查看尚硅谷原笔记

函数

系统内置函数

• show funcations; 查询系统内置的函数
• desc function 函数名; 显示函数的用法
• desc function extended 函数名; 显示函数的详细用法(包括函数的使用例子)

空字段赋值

• NVL 函数
  • nvl(value,defaultValue);
    • 如果 value 的值为 null，则填充默认值 defaultValue
  • select 字段1，字段2，nvl(字段1，-1) from emp; (默认值也可以使用另外一个字段来代替)

CASE WHEN THEN ELSE END

• case 字段
  when 条件
  then 符合条件执行
  else 不符合条件执行
  end 必须以 end 结尾

• 使用演示(查询男女人数，以 dept_id 划分)

  • select
    dept_id,
    sum(case sex when ‘男’ then 1 else 0 end) male_count,
    sum(case sex when ‘女’ then 1 else 0 end) female_count
    from
    emp_sex
    group by dept_id;

拼接字符串

• concat(‘hello-’,’-world’ …); 返回字符串连接后的结果: hello–world

• select concat_ws(’-’,‘hello’,‘world’); 以第一个参数为分隔符，返回拼接的字符串

  • 若该函数的分隔符为 null ，则返回值也为 null，分隔符可以和参数相同
  • 若后续参数中有值为 null，该函数会直接跳过
  • 参数字符串，可以直接使用字符串的数组代替

• collect_set(字段); 将字段的值进行去重，汇总，产生 Array 类型的字段

  • 该函数只接受基本数据类型

• 案例实操

  • 数据源

    • name conste blood_type

      孙悟空 白羊座 A
      大海 射手座 A
      宋宋 白羊座 B
      猪八戒 白羊座 A
      凤姐 射手座 A
      苍老师 白羊座 B

  • 建表

    • create table person_info( name string, constellation string, blood_type string) row format delimited fields terminated by “\t”; load data local inpath “/opt/module/hive/data/person_info.txt” into table person_info;

  • 需求

    • 按照血型和星座，将相同的人集合起来

  • 查询语句

    • select con_blo,concat_ws(’|’,collect_set(t1.name)) name_arr from (select concat_ws(’,’,conste,blood_type) con_blo,name from persion)t1 group by con_blo;
    • 语句分析
      • 通过多表查询，先从表中查出每一行数据的血型和星座(使用 concat_ws() 函数用指定分隔符连接后整体作为一个字段 con_blo)以及姓名作为 t1 表，然后将 t1 表作为嵌套，查询其中的 con_blo 和 name 字段，其中，name 字段使用 concat_ws() 函数，同时使用 collect_set() 函数将 t1 表中查询出的 name 聚合起来并用指定分隔符分隔，最后，以 con_blo 作为分组，就查询出了相同星座血型的人并将他们集合起来

数值处理函数

• 对给定的数取到指定精度(四舍五入)
  • select round(10/3,6); 将 10/3 的值取到小数点后六位
• 向上取整
  • select ceiling(10.123); 值为 11，出现小数时，直接1向上取整
• 向下取整
  • select floor(10.123); 值为 10，出现小数时，直接向下取整

Explode 函数

• 将 hive 中的 Array 和 Map 拆分成多行数据，例如 [1，2，3，4，5] 拆分为 1,
  2,
  3,
  4,
  5

其他常用函数

常用字符串操作函数
upper： 转大写
select upper(‘low’);

lower： 转小写
select lower(‘low’);

length： 长度
select length(“atguigu”);

trim： 前后去空格
select trim(" atguigu ");

lpad： 向左补齐，到指定长度
select lpad(‘atguigu’,9,‘g’);

rpad： 向右补齐，到指定长度
select rpad(‘atguigu’,9,‘g’);

regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace(‘2020/10/25’, ‘/’, ‘-’);

集合操作
size： 集合中元素的个数
select size(friends) from test3;

map_keys： 返回map中的key
select map_keys(children) from test3;

map_values: 返回map中的value
select map_values(children) from test3;

array_contains: 判断array中是否包含某个元素
select array_contains(friends,‘bingbing’) from test3;

sort_array： 将array中的元素排序
select sort_array(friends) from test3;

grouping_set:多维分析