大数据数仓工具之Hive

Hive

hive基本概念

1. hive是一个数据仓库工具，可以将结构化的数据映射为一张表，并提供类SQL查询功能
2. hive本质是将HQL转换为MapReduce程序
3. hive分析数据的底层的实现是MapReduce，执行程序运行在Yarn上
4. hive元数据存储在Mysql，数据本身存储在HDFS上

hive的优缺点

优点：

• 基于SQL类似的语法
• 避免了MapReduce编程
• 处理海量数据比较有优势
• hive支持根据需求来自定义函数

缺点：

• hive的HQL表达能力有限
• 数据挖掘方面不擅长
• hive执行延迟较高，效率比较低，适合实时性要求不高的场合
• hive调优比较困难

hive常用交互命令（可以用hive -help查看详情）

1. “-e”不进入hive的交互窗口执行sql语句

   hive -e "select id from student;"
   

2. “-f”执行脚本中sql语句

   hive -f hive.sql
   

hive其他命令操作

1. exit或者quit：退出

2. dfs：在hive命令行中查看HDFS文件系统

   hive(default)>dfs -ls /;
   

3. ！：在hive命令行查看本地文件系统

   hive(default)>! ls /opt/module/datas;
   

hive常见属性配置

1. 修改default数据仓库原始位置（将hive-default.xml.template如下配置信息拷贝到hive-site.xml文件中）

   <property>
   <name>hive.metastore.warehouse.dir</name>
   <value>/user/hive/warehouse</value>
   <description>location of default database for the warehouse</description>
   </property>
   

2. 在hive-site.xml文件中添加如下配置信息，就可以实现显示当前数据库，以及查询表的头信息配置

   <property>
   	<name>hive.cli.print.header</name>
   	<value>true</value>
   </property>
   <property>
   	<name>hive.cli.print.current.db</name>
   	<value>true</value>
   </property>
   

3. Hive的log默认存放在/tmp/kgg/hive.log目录下（当前用户名下），修改hive的log存放日志到/opt/module/hive/logs，具体是修改/opt/module/hive/conf/hive-log4j.properties.template文件名称为hive-log4j.properties ，在hive-log4j.properties文件中修改log存放位置

   hive.log.dir=/opt/module/hive/logs
   

4. 参数配置方式

   • 查看当前所有的配置信息

     hive>set;
     

   • 参数的配置三种方式

     • 配置文件方式，用户自定义配置会覆盖默认配置

     • 命令行参数方式

       [kgg@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;  注意：仅对本次hive启动有效
       

     • 参数声明方式

       hive (default)> set mapred.reduce.tasks=100;  注意：仅对本次hive启动有效
       

       注意：三种设定方式的优先级依次递增，即配置文件<命令行参数<参数声明。

DDL数据定义

1. 创建数据库

   create database db_hive;
   

2. 查询数据库

   show databases; //显示数据库
   show databases like 'db_hive*'; //过滤显示查询的数据库
   desc database db_hive;  //显示数据库信息
   desc database extended db_hive;  //显示数据库详细信息
   

3. 切换当前数据库

   use db_hive;
   

4. 修改数据库

   alter database db_hive set dbproperties('createtime'='20170830');
   

5. 删除数据库

   drop database db_hive2;
   drop database db_hive cascade;  //如果数据库不为空，可以采用cascade命令，强制删除
   

6. 创建表

   create table：创建表
   external：该关键字创建外部表
   comment：为表和字段添加注释
   partitioned by：创建分区表
   clustered by：创建分桶表
   stored as：以什么文件存储（textfile、sequencefile、orc、parquet），前两个是行式存储，后两个是列式存储
   row format delimited 加上如下：
   	fields terminated by：各字段之间的分隔符
   	map keys terminated by：map中key-value之间的分隔符
   	collection items terminated by：集合内部之间的分隔符
   	lines terminated by：行与行之间的分隔符
   location：指定表在HDFS上的存储位置
   like：允许用户复制现有的表结构，但是不复制数据
   WITH SERDEPROPERTIES ：设置属性对应关系
   

7. 管理表（内部表）

   create table if not exists student3 as select id, name from student;  //根据查询结果创建表
   create table if not exists student4 like student; //根据已经存在的表结构创建表
   desc formatted student2;  //查询表的类型
   

8. 管理表与外部表的互相转换

   alter table student2 set tblproperties('EXTERNAL'='TRUE'); //修改内部表student2为外部表
   

9. 分区表

   alter table dept_partition add partition(month='201705') partition(month='201704'); //增加分区
   alter table dept_partition drop partition (month='201705'), partition (month='201706'); //删除分区
   show partitions dept_partition; //查看分区
   

   把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

   • 方式一：上传数据后修复

     hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
     hive (default)> msck repair table dept_partition2;
     

   • 方式二：上传数据后添加分区

     hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;
     hive (default)> alter table dept_partition2 add partition(month='201709',day='11');
     

   • 方式三：创建文件夹后load数据到分区

     hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
     hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
     

10. 修改表

    alter table dept_partition2 rename to dept_partition3; //重命名表
    alter table dept_partition add columns(deptdesc string); //添加列
    alter table dept_partition change column deptdesc desc int; //更新列
    alter table dept_partition replace columns(deptno string, dname string, loc string); //替换列
    

11. 删除表

    drop table dept_partition;
    

DML数据操作

导入数据

1. 向表中装载数据

   （1）load data:表示加载数据
   （2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
   （3）inpath:表示加载数据的路径
   （4）overwrite:表示覆盖表中已有数据，否则表示追加
   （5）into table:表示加载到哪张表
   （6）student:表示具体的表
   （7）partition:表示上传到指定分区
   

2. 通过查询语句向表中插入数据（Insert）

   insert overwrite table student partition(month='201708') 
   select id, name from student where month='201709';
   

3. 查询语句中创建表并加载数据（As Select）

   create table if not exists student3
   as select id, name from student;