Hive实现离线计算(HQL)

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: Hive实现离线计算(HQL)

 image.gif编辑

👨🏻‍🎓博主介绍:大家好,我是芝士味的椒盐,一名在校大学生,热爱分享知识,很高兴在这里认识大家🌟

🌈擅长领域:Java、大数据、运维、电子

🙏🏻如果本文章各位小伙伴们有帮助的话,🍭关注+👍🏻点赞+🗣评论+📦收藏,相应的有空了我也会回访,互助!!!

🤝另本人水平有限,旨在创作简单易懂的文章,在文章描述时如有错,恳请各位大佬指正,在此感谢!!!


大数据业务分为两大:实时计算,离线计算

实时计算:实时对来到的数据进行及时计算

离线计算:对堆积已久的数据进行计算(MapReduce(效率低)、Hive)

Hive提供了 一种类SQL的操作进行查询统计

Hive的底层:是基于MapReduce的引擎,会将查询语句转换为Map阶段和Reduce阶段

MR、Hive:离线计算处理模块的技术

Hive特点:

    • 可扩展性———>Hive自由扩展集群的规模,一般无需重启服务
    • 延展性————>Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数
    • 容错性————>节点宕机,SQL任然可以完成执行

    数据仓库(DataWarehouse):可以包含多个数据库

    数据库:包含多个表

    Hive与传统数据库的对比:image.gif编辑

    安装Hive

    Hive常用交互命令:

      1. "-e" 不进入hive的交互窗口执行sql
      hive -e "show databases;"
      1. image.gif
      2. “-f” 执行脚本中的sql语句
        • 编写selectdata.sql脚本
        use testdatas;
        select * from stu;
        • image.gif
        • 执行sql脚本
        hive -f  selectdata.sql
        • image.gif
        • 将sql执行的结果写入文件中
        hive -f  selectdata.sql >> ./restult1.txt
        • image.gif

          Hive其他命令操作:

            1. 退出hive窗口(老版本有区别,新版没有区别):
            exit;#先隐形提交数据,再退出
            quit;不提交数据,退出
            1. image.gif
            2. 在hive cli命令窗口中查看hdfs文件系统
            3. 在hive cli命令窗口中查看本地文件系统
            4. 查看在hive中输入的所有的历史命令
            cat /home/当前用户/.hivehistory
            1. image.gif

            Hive数据仓库位置配置:

              1. 配置Default数据仓库的位置,默认是在hdfs上的:/user/hive/warehouse/(预先存在的路径)下,可以将下面的标签片段编写到hive-site.xml中就可以改变Default数据创库的位置
              <property>
              <name>hive.metastore.warehouse.dir</name>
              <value>/user/hive/warehouse</value>
              <description>location of default database for the warehouse</description>
              </property>
              1. image.gif
                • 切记配置完Default数据创库的位置,需要设置同组可读的权限
                hadoop fs -chmod g+w /user/hive/warehouse
                • image.gif
                  1. 后台信息查询配置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>
                  1. image.gif

                  Hive运行日志信息配置:

                    1. 在/tmp/hadoop目录下可以看到hive的日志文件,进行排错,hive.log、hive.log.2020-09-27....
                    2. 拷贝并修改hive-log4j.properties. template——>hive-log4j.properties
                    #可以修改日志存储的位置
                    hive.log.dir=/usr/local/src/hive/logs
                    #可以修改日志打印日志时间的格式
                    log4j.appender.DRFA.DatePattern=.yyyy-MM-dd
                    1. image.gif

                    参数配置方式:

                      1. 查看当前所有的配置信息
                      hive>set ;
                      1. image.gif
                      2. 参数配置的三种方式
                        1. 配置文件方式
                          • 默认配置文件:hive-default.xml
                          • 用户自定义配置文件:hive-site.xml(会覆盖默认配置)
                            1. 命令行参数方式
                              • 启动hive时,在命令行添加-hiveconf param=value来进行参数设定(本次有效
                              • 查看参数设置:
                              hive(default)>set mapred.reduce.tasks;
                              • image.gif
                                1. 参数声明方式
                                  可以在HQL中使用SET关键字设定参数(本次有效)
                                hive(Default)>set mapred.reduce.tasks=100
                                1. image.gif
                                    • 三种配置的优先级:配置文件<命令行参数<参数声明

                                      Hive的基本概念:

                                        • 用户可以在Hive中执行Hadoop的fs命令,如“dfs -ls /;”查询hdfs上存储的数据
                                        • 在hive中使用hadoop命令比直接在bash shell中执行hadoop dfs更加高效,在hive中是会在同一个进程中执行这些命令,而在bash shell执行则会每次启动一个JVM的新实例。

                                        Hive中的基本数据类型:

                                          • Hive v0.8.0版本中增加了时间戳数据类型和二进制数组数据类型image.gif编辑

                                          image.gif编辑

                                            • 所有的数据类型都是对Java中的数据类型接口的实现
                                            • 新增数据类型TIMESTAMP的值可以是整数,也就是距离Unix新纪元时 间( 1 970年1月1日, 午夜12点)的秒数;也可以是浮点数,即距离 Unix新纪元时间的秒数,精确到纳秒(小数点后保留9位数) ; 还可以 是字符串,即JDBC所约定的时间字符串格式,格式为YYYY-MM-DD hh:mm:s.ffffff。
                                            • TIMESTAMPS表示的是UTC时间。Hive本身提供了不同时区间互相转 换的内置函数,也就是to_ utctimestamp函数和from utc_ timestamp函数
                                            • BINARY数据类型和很多关系型数据库中的VARBINARY数据类型是 类似的,但其和BLOB数据类型并不相同。因为BINARY的列是存储在 记录中的,而BLOB则不同。BINARY可以在记录中包含任意字节,这 样可以防止Hive尝试将其作为数字,字待串等进行解析。
                                            • String相当于varchar,可以单个存储2G的字符数

                                            操作

                                              • 创建stu表
                                              #**row format delimited----》表示对行格式化
                                              #fields terminated by '\\t'----》表示参数按什么分割这里,这为一个制表符**
                                              **create table stu(id bigint,name string ,score double,age int) row format delimited fields terminated by '\\t';**
                                              • image.gif
                                              • 在/home/hadoop/databasesdata/下创建stu.txt,并加入以下内容
                                              20192023  Marry 99.9  18
                                              20192022  Lucrh 78  22
                                              20192021  Marry 89.0  99
                                              • image.gif
                                                • tips:数据和数据之间要严格按照创建表时指定的数据分割格式,不然之后加载进表中会出现NULL
                                                  • 将数据加载进stu表
                                                  load data local inpath '/home/hadoop/databasesdata/stu.txt' into table stu;
                                                  • image.gif

                                                  集合数据类型

                                                    • Hive中的列支持使用struct、map和array集合数据类型image.gif编辑

                                                    操作

                                                      • 创建star表
                                                      create table star(name string, friends array<string >, childern map<string, int>, address struct<street:string, city:string>) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' line terminated by '\\n';
                                                      • image.gif
                                                        • row format delimited fields terminated by ',':每行每条语句的分隔符
                                                        • collection items terminated by '_' :array的数据分隔符
                                                        • line terminated by '\n':行分隔符

                                                          类型转化

                                                            • Hive的原子数据类型可以进行隐式转化,可以正向转化,不会进行反向转化,如果传入的值不是低于属性的数据类型,而是高于其,则会直接报错,可以使用CAST进行操作,将不会进行报错。

                                                            隐式类型转换

                                                              1. 任何类型都可以准换成一个更广的类型
                                                                TINYINT—>INT—>BIGINT
                                                              2. 所有整数类型、FLOAT和STRING都可以隐式转换成DOUBLE
                                                              3. TINYINT、SMALLINT、INT都可以转换成FLOAT
                                                              4. BOOLEAN不允许转换成其他类型

                                                              强制转换

                                                              例子:

                                                                1. CAST('1' AS INT)是将字符串型‘1’转换成整数1,
                                                                2. 如果强制类型转换失败,执行了CAST('X' AS INT) 表达式将返回空值NULL

                                                                DDL数据定义

                                                                创建数据库

                                                                  1. 创建的数据库默认是在/user/hive/warehouse/下
                                                                  hive (default)> create database star;
                                                                  1. image.gif
                                                                  2. 使用not exitis判断是否预先已有数据库
                                                                  hive (default)> create database if not exitis star;
                                                                  1. image.gif
                                                                  2. 创建数据库,并制定数据库在HDFS上存储的位置
                                                                  create database if not exists kos location '/koss.db';

                                                                  image.gif

                                                                  查询数据库

                                                                  显示数据库

                                                                    1. 显示数据库
                                                                    show databases;
                                                                    1. image.gif
                                                                    2. 过滤显示查询的数据库,*
                                                                    show databases like 'ko*';
                                                                    1. image.gif

                                                                    查询数据库详情

                                                                      1. 显示数据库信息
                                                                      desc database star;
                                                                      1. image.gif
                                                                      2. 显示数据库详细信息,extended
                                                                      desc database extended star;
                                                                      1. image.gif

                                                                      切换当前数据库

                                                                      use kos;

                                                                      image.gif

                                                                      修改数据库

                                                                      alter database kos set dbproperties('createtime'='20180818');

                                                                      image.gif

                                                                        • tips:通过设置键值对的形式修改数据库的属性信息,数据库其他的元数据都是不可以更改的,包括数据库名和数据库所在的目录位置 ,可以通过extend查看修改之后的信息

                                                                        删除数据库

                                                                        drop database kos;

                                                                        image.gif

                                                                          • tips:可以采取if exists进行判断数据库是否存在,数据库不为空的情况下采取cascade命令将其删除
                                                                          drop database kos cascade;
                                                                          • image.gif

                                                                          创建表

                                                                          语法:

                                                                          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]

                                                                          image.gif

                                                                          管理表

                                                                            • 默认创建的表都是所谓的管理表,有时也被称为内部表。因为这种表,Hive会(或多或少地)控制着数据的生命周期,当我们删除一个管理表时,Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
                                                                              1. 普通表创建
                                                                              create table if not exists student2(
                                                                              id int, name string
                                                                              )
                                                                              row format delimited fields terminated by '\\t'
                                                                              stored as textfile
                                                                              location '/user/hive/warehouse/student2';
                                                                              1. image.gif
                                                                              2. 根据查询结果创表
                                                                              create table if not exists restl as select * from stu;
                                                                              1. image.gif
                                                                              2. 根据已经存在的表结构创建表
                                                                              create table if not exists re2 like stu;
                                                                              1. image.gif
                                                                              2. 查询表的类型
                                                                              desc formatted stu;
                                                                              1. image.gif

                                                                              外部表

                                                                                • 因为表是外部表,所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉

                                                                                案例:

                                                                                  1. dept.txt
                                                                                  10  ACCOUNTING  1700
                                                                                  20  RESEARCH  1800
                                                                                  30  SALES 1900
                                                                                  40  OPERATIONS  1700
                                                                                  1. image.gif
                                                                                  2. 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
                                                                                  7839  KING  PRESIDENT   1981-11-17  5000.00   10
                                                                                  7844  TURNER  SALESMAN  7698  1981-9-8  1500.00 0.00  30
                                                                                  7876  ADAMS CLERK 7788  1987-5-23 1100.00   20
                                                                                  7900  JAMES CLERK 7698  1981-12-3 950.00    30
                                                                                  7902  FORD  ANALYST 7566  1981-12-3 3000.00   20
                                                                                  7934  MILLER  CLERK 7782  1982-1-23 1300.00   10
                                                                                  1. image.gif
                                                                                  2. 建表语句(dept、emp)
                                                                                  create external table if not exists testdatas.dept(deptno int, deptname string, money int) row format delimited fields terminated by '\\t';
                                                                                  create external table if not exists testdatas.emp(empno int, empname string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) row format delimited fields terminated by '\\t';
                                                                                  1. image.gif
                                                                                  2. 加载数据进表中
                                                                                  load data local inpath '/home/hadoop/databasesdata/dept.txt' into table dept;
                                                                                  load data local inpath '/home/hadoop/databasesdata/emp.txt' into table emp;
                                                                                  1. image.gif

                                                                                  管理表和外部表的互相转换

                                                                                    1. 首先查询表的类型使用
                                                                                    desc formatted stu;
                                                                                    1. image.gif
                                                                                    2. 修改内部表stu为外部表
                                                                                    alter table stu set tblproperties('EXTERNAL'='TRUE');
                                                                                    1. image.gif
                                                                                    2. 修改外部表stu为内部表
                                                                                    alter table stu set tblproperties('EXTERNAL'='FLASE');
                                                                                    1. image.gif

                                                                                    分区表

                                                                                      • 分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。谓词下推(先where筛选)
                                                                                        1. 创建分区表
                                                                                        create table depts_partition (dept int, dname string, loc string) partitioned by (month string) row format delimited fields terminated by '\\t';
                                                                                        1. image.gif
                                                                                        2. 加载数据到分区表中
                                                                                        load data local inpath '/home/hadoop/databasesdata/dept.txt' into table depts_partition partition(month='2020-9-29');
                                                                                        1. image.gif
                                                                                        2. 查询分区表中数据
                                                                                        select * from depts_partition where month='2020-9-23';
                                                                                        1. image.gif
                                                                                        2. 增加分区
                                                                                        #添加单个分区
                                                                                        alter table depts_partition add partition(month='2020-9-30');
                                                                                        #添加多个分区
                                                                                        alter table depts_partition add partition(month='2020-10-2') partition(month='2020-10-1');
                                                                                        1. image.gif
                                                                                        2. 删除分区
                                                                                        #删除单个分区
                                                                                        alter table depts_partition drop partition(month='2020-10-2');
                                                                                        #删除多个分区
                                                                                        alter table depts_partition drop partition(month='2020-10-2') partition(month='2020-10-1');
                                                                                        1. image.gif
                                                                                        2. 查看分区表中有分区
                                                                                        show partitions depts_partition;
                                                                                        1. image.gif
                                                                                        2. 查看分区结构
                                                                                        desc formatted depts_partition;
                                                                                        1. image.gif

                                                                                        二级分区:

                                                                                          1. 创建二级分区
                                                                                          create table kos_deptss_partition(dept int, dname string, loc string) partitioned by (month string, day string) row format delimited fields terminated by  '\\t';
                                                                                          1. image.gif
                                                                                          2. 正常加载数据到分区
                                                                                          load data local inpath '/home/hadoop/databasesdata/dept.txt' into table kos_deptss_partition partition(month='1999-9',day='9');
                                                                                          1. image.gif
                                                                                          2. 把数据直接上传到分区目录,让分区和数据表产生关联的三种方式
                                                                                            1. 方式一:上传数据后修复上传数据
                                                                                            hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
                                                                                            hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
                                                                                            1. image.gif
                                                                                              • 此时查询分区是查询不到的,需要修复分区
                                                                                              hive (default)> select * from dept_partition2 where month='201709' and day='12';
                                                                                              • image.gif
                                                                                                1. 执行修复分区的命令
                                                                                                hive> msck repair table dept_partition2;
                                                                                                1. image.gif
                                                                                                2. 方式二:上传数据后添加分区
                                                                                                  上传数据
                                                                                                ive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11;
                                                                                                hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;
                                                                                                1. image.gif添加分区
                                                                                                hive (default)> alter table dept_partition2 add partition(month='201709',
                                                                                                 day='11');
                                                                                                1. image.gif
                                                                                                2. 方式三:创建文件夹后load数据到分区
                                                                                                  创建目录
                                                                                                hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
                                                                                                1. image.gif上传数据
                                                                                                hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
                                                                                                1. image.gif
                                                                                                  1. 修改表
                                                                                                    1. 修改表名
                                                                                                    alter table table_name rename to new_table_name
                                                                                                    1. image.gif
                                                                                                    2. 增加/修改/替换列信息
                                                                                                      1. 语法更新列
                                                                                                      ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
                                                                                                      1. image.gif增加和替换列
                                                                                                      ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
                                                                                                      1. image.gif
                                                                                                        • 注:ADD是代表新增一字段,字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段。
                                                                                                          1. 例子:
                                                                                                            查询结构
                                                                                                          hive> desc dept_partition;
                                                                                                          1. image.gif添加列
                                                                                                          hive (default)> alter table dept_partition add columns(deptdesc string);
                                                                                                          1. image.gif更新列
                                                                                                          hive (default)> alter table dept_partition change column deptdesc desc int;
                                                                                                          1. image.gif替换列
                                                                                                          hive (default)> alter table dept_partition change column deptdesc desc int;
                                                                                                          1. image.gif
                                                                                                              相关实践学习
                                                                                                              日志服务之使用Nginx模式采集日志
                                                                                                              本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
                                                                                                              相关文章
                                                                                                              |
                                                                                                              SQL 分布式计算 监控
                                                                                                              Hive性能优化之计算Job执行优化 2
                                                                                                              Hive性能优化之计算Job执行优化
                                                                                                              225 1
                                                                                                              |
                                                                                                              SQL 分布式计算 大数据
                                                                                                              黑马程序员-大数据入门到实战-分布式SQL计算 Hive 入门
                                                                                                              黑马程序员-大数据入门到实战-分布式SQL计算 Hive 入门
                                                                                                              150 0
                                                                                                              |
                                                                                                              SQL 存储 大数据
                                                                                                              黑马程序员-大数据入门到实战-分布式SQL计算 Hive 语法与概念
                                                                                                              黑马程序员-大数据入门到实战-分布式SQL计算 Hive 语法与概念
                                                                                                              141 0
                                                                                                              |
                                                                                                              SQL 存储 分布式数据库
                                                                                                              【通过Hive清洗、处理和计算原始数据,Hive清洗处理后的结果,将存入Hbase,海量数据随机查询场景从HBase查询数据 】
                                                                                                              【通过Hive清洗、处理和计算原始数据,Hive清洗处理后的结果,将存入Hbase,海量数据随机查询场景从HBase查询数据 】
                                                                                                              240 0
                                                                                                              |
                                                                                                              30天前
                                                                                                              |
                                                                                                              SQL 分布式计算 关系型数据库
                                                                                                              Hadoop-13-Hive 启动Hive 修改启动参数命令行启动测试 几句简单的HQL了解Hive
                                                                                                              Hadoop-13-Hive 启动Hive 修改启动参数命令行启动测试 几句简单的HQL了解Hive
                                                                                                              50 2
                                                                                                              |
                                                                                                              30天前
                                                                                                              |
                                                                                                              SQL 分布式计算 Hadoop
                                                                                                              Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(一)
                                                                                                              Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(一)
                                                                                                              31 4
                                                                                                              |
                                                                                                              30天前
                                                                                                              |
                                                                                                              SQL 分布式计算 Hadoop
                                                                                                              Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
                                                                                                              Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
                                                                                                              56 3
                                                                                                              |
                                                                                                              30天前
                                                                                                              |
                                                                                                              SQL
                                                                                                              Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(二)
                                                                                                              Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(二)
                                                                                                              32 2
                                                                                                              |
                                                                                                              4月前
                                                                                                              |
                                                                                                              SQL 分布式计算 关系型数据库
                                                                                                              Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
                                                                                                              Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
                                                                                                              62 2
                                                                                                              |
                                                                                                              6月前
                                                                                                              |
                                                                                                              SQL 关系型数据库 HIVE
                                                                                                              Hive中的HQL是什么?请解释其语法和常用操作。
                                                                                                              Hive中的HQL是什么?请解释其语法和常用操作。
                                                                                                              53 0