实验室基础Hive学习

一、Hive的概念介绍（相当于Hadoop的客户端）

        1> Hive处理的数据存储在HDFS中

        2>Hive分析数据的底层是MR（在安装完Hive的时候它底层已经完成了对应SQL语句和MR编程的对应关系的模板的写入，将所有MR模板封装在Hive中），而当客户端输入的SQL语句时，就会根据这些模板来进行对应地翻译成MR程序，并打成jar包，在YARN上运行

        3>执行在YARN上

        本质：将HQL转化成MR程序

二、Hive数据查询

        在Hive中的数据查询是基于HDFS的，而默认的处理HDFS的目录我是在/user/hive/warehous e里面的。因此，在hive中查询数据需要两个关键组成部分：元数据，HDFS是否有数据；

        元数据，就是描述数据的数据，存储包括表的各种属性等数据；

        HDFS中的数据，就是Hive根据对应的表的信息去HDFS的默认处理路径对指定目录（即表名）进行对应的SQL操作。

        总而言之，在Hive中查询数据，查询的表就对应HDFS默认路径下的目录，SQL语句处理的数据就是对应目录下的所有文件（相当于将SQL语句翻译成MR程序对HDFS中的文件进行一定的操作）。并且当db表迁移到mysql数据库中，就可以允许多个客户端同时访问hive

三、Hive的其他交互方式（不进入hive交互页面执行sql，适用于脚本封装命令）

        ①hive -e "sql语句"-------执行完后输出结果退回命令行

         ②hive -f 文件路径---------执行文件内的sql语句输出结果后退回命令行

         ③在hive交互页面中，使用dfs -ls 目录 可以直接查看HDFS中的目录信息

linux小知识：cd -  可以回到上一次的目录

四、Hive的参数配置

        1、在hive交互页面中直接使用set;可以直接查看所有配置信息（包括hadoop的）

         2、参数配置的三种方式

                Ⅰ配置文件方式

                    默认配置文件：hive-default.xml

                    用户自定义配置文件：hive-site.xml

                     用户自定义配置会直接覆盖默认配置，并且hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端来启动的，Hive的配置会覆盖掉Hadoop的配置

                Ⅱ 命令行参数方式（仅对本次启动的hive有效）---用于脚本封装（与之前的交互方式交替使用）

                        hive -hiveconf key=value(key是要修改的参数名，value是目标参数值）

                Ⅲ 参数声明形式

                        在HQL中直接set key=value

⭐关于在hive交互界面使用count(*)显示出来的数据数量与实际用select语句显示出来的不同：

        之前的实验是将数据put进HDFS，再用HQL语句进行筛选，但是count(*)是走元数据库，并不是通过HDFS；因此，使用count(*)显示出来的只能是修改了元数据库的语句（insert），而不能针对HDFS的修改进行改变数值

五、HIve的数据类型

        与java基础数据类型的对比：

         hive中独有的复杂数据类型：

        STRUCT: 类似于Java中的Bean

        MAP：键值对

        ARRAY:类似于Java中的列表 

        而不同复杂数据类型之间允许任意层次的嵌套

        实例：

在hive中抽象的表如下图所示：

{

"name": "songsong",

"friends": ["bingbing" , "lili"] , // 列表 Array,

"children": { // 键值 Map,

"xiao song": 18 ,

"xiaoxiao song": 19

}

"address": { // 结构 Struct,

"street": "hui long guan",

"city": "beijing"

}

}

 创建本地文件test.txt，用于导入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

在hive中创建表test2：

create table test(

name string,

friends array<string>,

children map<string, int>,

address struct<street:string, city:string>

)

row format delimited fields terminated by ','

collection items terminated by '_'

map keys terminated by ':'

lines terminated by '\n';

对应i字段的解释：

row format delimited fields terminated by ','       -- 列分隔符

collection items terminated by '_'       --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)

即表示所有集合中元素之间的分隔符（详情参考之前的文件test.txt中的字段之间的分隔符）

map keys terminated by ':'         -- MAP 中的 key 与 value 的分隔符

lines terminated by '\n';           -- 行分隔符

通过hive查看表test2中的各个字段：

 通过hive查看friends字段的对应位置的值：（ARRAY类型数据）

 通过hive查看children字段对应key的值：（MAP类型数据）

 通过hive查看adress字段对应字段的值：（STRUCT类型数据）

六、HiveDDL

        1.数据库操作

        Ⅰ创建数据库：CREATE DATABASE IF NOT EXISTS BASE_NAME(创建数据库的默认路径就是/user/hive/warehouse），如果要指定数据库创建的位置的话，在后面加一个location 'HDFS_path'即可

 

         Ⅱ查看数据库：SHOW DATABASES;查看包含指定名称的数据库：SHOW DATABASES LIKE '指定名称*'----这个是显示以指定名称开头的数据库 ，其他依次类推

        Ⅲ 修改数据库（仅仅能修改拓展的数据）：ALTER DATABASE 数据库名 SET PROPERTIES("KEY"="VALUE")

        Ⅳ 删除数据库：（空数据库）DROP DATABASE IF EXISTS 数据库名;（非空数据库---强制删除）DROP DATABASE 数据库名 CASCADE；

        2.表的操作

        Ⅰ建表语句

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的表有内外之分）

---------------------------------------------------------------------------------------------------------------------------------

               ⭐ 外部表和内部表的区别：

                内部表-----管理表；之前创建的默认都是管理表

                 具体区别：删除内部表会直接删除掉所有数据，包括表的元数据信息和表中所存储的数据都没了（相当于直接将mysql中hive库里面的表和HDFS中的对应目录全部删除）

                 而删除外部表只会删除掉表的元数据信息，就相当于只是在mysql中的hive库里面将描述表的数据给删除了，在navicat中是查不到这张表的；但是并不会删除掉具体信息，即HDFS中的对应目录下的文件和对应目录会保留。

                内外部表的转换：（这里以将内部表改为外部表为例）

                        ALTER TABLE 表名 SET TBLPROPERTIES('EXTERNAL'='TRUE');（参数必须是大写）

                        即将表中的EXTERNAL的参数值改为TRUE即可转换为外部表

                查看内外部表的语句：DESC FORMATTED 表名；

                总而言之，就安全性和实用性而言，外部表总是优于内部表。因为即使误删了表，也不会删除掉HDFS中的数据，这样再建一个相同的表，再用HQL就能重新找回数据了

       实例：

        创建了的外部表（test2，之前那个内部表删了）及其内部文件：

         删除这个外部表：

        mysql中hive库里面的表：

         HDFS中的对应目录（test2目录）------文件和目录还是存在

---------------------------------------------------------------------------------------------------------------------------------

        [PARTITIONED BY]---建立分区表

        [CLUSTERED BY]----分桶表

        [SORTED BY]---分桶表

        [ROW FORMAT row_format]---定义行格式的限制（参考之前建的表）

                row format delimited by '指定字符'---------各个列之间的分隔符

                lines terminated by '指定字符'---------------各个行之间的分隔符

        [STORED AS file_format]：指定存储文件的格式（Hive对应MR）

        [LOCATION hdfs_path]：存储路径

        [TBLPROPERTIES (property_name=property_value, ...)]：额外属性

        [AS select_statement]：以查询的方式建表

        Ⅱ 修改表

          ①重命名表：ALTER TABLE table_name RENAME TO new_table_name    

                重命名表之后，HDFS中对应的目录也会发生相应的改动；但是不能改成已存在的表名

          ②更新列(修改列名和其数据类型）

                ALTER TABLE table_name CHANGE  col_old_name col_new_name col_new_type

          ③增加和替换列

                ALTER TABLE table_name ADD|REPLACE COLUMNS (COL_NAME,DATA_TYPE)

                replace操作就直接对整张表的列进行替换,相应的,add也可以添加多个列;

                replace操作也可以用于删除列（直接替换），这些操作都很灵活，可以动态地扩展字段

        ④删除表：DROP TABLE table_name

七、Hive的DML操作（重点记忆）

        ①数据的导入

        1、向表中装载数据（load）

                load data [local] inpath '数据的path'  [overwrite] into table 表名 [partition]

                        local:表示从本地加载数据到hive表，否则从HDFS加载数据；如果从HDFS中加载数据到表中，文件就是移动操作（只需要修改namenode中的元数据即可，所以速度很快）；从本地加载数据到表中，文件就是复制操作

                        inpath:表示加载数据的路径

                        overwrite:表示覆盖表中的已有数据，否则就追加

                        partition:表示上传到指定分区

        2、向表中加载数据（insert）

                多用于将另一个表的指定数据复制到指定表当中（与select语句搭配使用）

                insert into 表名 select * from stu1;----指定具体字段时属性不需要加''

                也可以同时从多张表中复制数据过来进行插入（使用join操作,只有一个分号）

⭐关于向HDFS中上传数据的三种方法对于元数据的修改（count(*)的操作)：

                        ⅠHDFS中的put操作：不会修改元数据，能够从hive中查询到是因为地址指向的就是HDFS对应的目录，使用count(*)时不会走MR程序

                        Ⅱ Hive中的insert into 操作：走的时hive客户端的形式，元数据中的numfiles和numrows都会进行改动，使用count(*)时会走MR程序

                        Ⅲ Hive中的load操作：往HDFS中上传文件，但是是通过hive客户端的形式，元数据中的numfiles会进行改动，但是numrows不会进行改动，使用count(*)时会走MR程序

        3、在建表时导入数据（as select)

        create table 表名 as select * from table_name---可以将对应表的对应字段直接复制到创建的表当中，并且直接给新表建立一模一样的列（前提是建表语句）

        4、在建表时导入指定路径数据（location）-----一般用于外部表

        在正常的建表语句的后方（即行分隔符后方）加上location 'HDFS_FILE_PATH'即可在创建表的时候就导入目标数据

        ②数据的导出

         1、在hive客户端的情况下导出HDFS中的数据到本地指定路径(HDFS上就去掉local就行)

        insert overwrite local directory '本地路径'

        row format delimited fields terminated by '字符间指定分隔符' ----传出来的文件能够直接使用

        select * from 指定表

        2、在hive客户端的情况下将数据导出到HDFS指定目录中，与insert语句不同的是，使用export语句导出表会同时导出表的元数据信息，所以使用export语句导出的数据可以使用import语句导入数据到hive中的表，前提是目标表不存在，或者是个空表（里面不能有数据）-------这样导入数据确实太麻烦，这种主要用于两个hadoop平台集群之间hive表的迁移

                export语法：export table 表名 to 'hdfs路径'

                import语法：import table 要导入数据的目标表 from 'hdfs文件路径‘

        ③表中数据的清除

        清空表：（仅限于内部表，只会删除掉HDFS中的数据，表的字段不会修改-----元数据不会修改），外部表本身drop掉表都删除不了HDFS数据

        truncate table 表名

        ④对表中数据的具体处理：

        1、列的别名 as-------直接在列名后+as+别名。之后的HQL就可以使用这个别名来对列进行操作（常用于嵌套的sql语句操作）

        2、数据限制 limit----- 在查询语句后加个limit num即可规定控制台输出的数据行数

        3、指定条件 where----可以指定查询的字段的具体筛选条件（后面的列名不能用别名）

        ⑤逻辑运算符和比较运算符

        1、where 字段 between num1 and num2------查询字段在num1和num2之间的数据（前后都闭）

        2、where 字段 in (num1,num2)---查询字段等于num1或num2的数据（相当于or）

        3、like和rlike----选择出包含指定字段的数据：

               like：%表示任意字段；_表示一个字段

                        select * from test where name like 'A%'--------表示筛选出name以A开头的数据

         4、or和and----或和且，not-----否

        ⑥分组语句和having语句

        1、group by 字段        以字段为key进行分组

        2、having语句-----紧跟group by语句之后，用法等价于where，用于筛选，但是其区别于where的地方就是having后面可以跟别名

八、join语句

        前置知识：使用表的别名可以提高join查询的效率

        1、join内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来

        ex：         

        使用e来代表emp表，使用d来代表dept表；

        join的key是两个表的deptno字段，相等即join起来；

        select后指定表的名称 有利于提高join查询的效率

select
    e.empno
    e.ename
    d.dname
from 
    emp e
join
    dept d
on e.detno = d.deptno;

2、join左连接（left join）

        以左边的表为基准，对应右边的字段，即显示左边表的所有数据，再根据select语句显示出对应项目；如果说根据join标准，右边的表中没有对应的符合要求的数据，则右边表相关数据（select显示的）为null;

        以下面的为例子：

                如果说emp中存在一行数据deptno=50，但是dept中的deptno并没有50的数据，那么对应emp中deptno=50的那行数据的dname值就为null；

        由此可知：left join是以左边的表为基准

select
    e.empno
    e.ename
    d.dname
from 
    emp e
left join
    dept d
on e.detno = d.deptno;