devbox
我家自动化
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Apache Hive（二）

作者：我家自动化 | 2024-07-21 07:13:54

赞

踩

Apache Hive（二）

目录

一、Apache Hive-Hive SQL

1、Hive SQL语言：DDL建库、建表

1.1、建库

1、数据库database

2、create database

3、切换数据库

4、删除数据库

1.2、建表

1、表Table

2、建表语句

3、数据类型

4、分隔符指定语法

实战操作

Hive默认分隔符

1.3、show语法

1.4、注释comment中文乱码解决

2、Hive SQL DML语法之加载数据

1、Load语法功能

语法规则之LOCAL

Load加载数据实战

bin/beeline客户端操作

2、Insert插入数据

insert+select

3、Hive SQL DML语法之查询数据

4、Hive SQL Join关联查询

5、Hive SQL 中的函数使用

一、Apache Hive-Hive SQL

1、Hive SQL语言：DDL建库、建表

    Hive的数据模型：Hive---->数据库(...)--->表(...)--->记录(...)

    数据定义语言（Data Definition Language，DDL），是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database、table等。

    DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。

    Hive SQL（HQL）与标准SQL的语法大同小异，基本相通。

    基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握Hive DDL语法的重中之重。建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。通俗点说，没有表，表没有数据，你用Hive分析什么呢？

    选择正确的方向，往往比盲目努力重要。

1.1、建库

1、数据库database

    在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的/user/hive/warehouse下

    用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下

2、create database

    create database用于创建新的数据库

    COMMENT：数据库的注释说明语句

    LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db

    WITH DBPROPERTIES：用于指定一些数据库的属性配置。

  1. # 连接bin/beeline客户端
  2. [root@node1 ~]# /export/server/hive-3.1.3/bin/beeline
  3. SLF4J: Class path contains multiple SLF4J bindings.
  4. SLF4J: Found binding in [jar:file:/export/server/hive-3.1.3/lib/log4j-slf4j-impl                                                                                                                                                             -2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  5. SLF4J: Found binding in [jar:file:/export/server/hadoop-3.3.6/share/hadoop/commo                                                                                                                                                             n/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  6. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  7. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  8. SLF4J: Class path contains multiple SLF4J bindings.
  9. SLF4J: Found binding in [jar:file:/export/server/hive-3.1.3/lib/log4j-slf4j-impl                                                                                                                                                             -2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  10. SLF4J: Found binding in [jar:file:/export/server/hadoop-3.3.6/share/hadoop/commo                                                                                                                                                             n/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  11. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  12. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  13. Beeline version 3.1.3 by Apache Hive
  14. beeline> ! connect jdbc:hive2://node1:10000
  15. Connecting to jdbc:hive2://node1:10000
  16. Enter username for jdbc:hive2://node1:10000: root
  17. Enter password for jdbc:hive2://node1:10000:
  18. Connected to: Apache Hive (version 3.1.3)
  19. Driver: Hive JDBC (version 3.1.3)
  20. Transaction isolation: TRANSACTION_REPEATABLE_READ
  21. #创建database
  22. 0: jdbc:hive2://node1:10000> create database lwztest;
  23. INFO  : Compiling command(queryId=root_20240227234021_e74ac3ed-eaf0-4ee1-a4a7-73                                                                                                                                                             22a1e81285): create database lwztest
  24. INFO  : Concurrency mode is disabled, not creating a lock manager
  25. INFO  : Semantic Analysis Completed (retrial = false)
  26. INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
  27. INFO  : Completed compiling command(queryId=root_20240227234021_e74ac3ed-eaf0-4e                                                                                                                                                             e1-a4a7-7322a1e81285); Time taken: 0.023 seconds
  28. INFO  : Concurrency mode is disabled, not creating a lock manager
  29. INFO  : Executing command(queryId=root_20240227234021_e74ac3ed-eaf0-4ee1-a4a7-73                                                                                                                                                             22a1e81285): create database lwztest
  30. INFO  : Starting task [Stage-0:DDL] in serial mode
  31. INFO  : Completed executing command(queryId=root_20240227234021_e74ac3ed-eaf0-4e                                                                                                                                                             e1-a4a7-7322a1e81285); Time taken: 1.008 seconds
  32. INFO  : OK
  33. INFO  : Concurrency mode is disabled, not creating a lock manager
  34. No rows affected (1.093 seconds)
  35. 0: jdbc:hive2://node1:10000>

HDFS下/user/hive路径存储database

3、切换数据库
  1. # 切换数据库
  2. use database

    选择特定的数据库，切换当前会话使用哪一个数据库进行操作

  1. # 切换到lwztest数据库
  2. 0: jdbc:hive2://node1:10000> use lwztest;
  3. INFO  : Compiling command(queryId=root_20240227234852_0877050e-8aea-4d1b-a8d5-de                                                                                                                                                             023a4d15ae): use lwztest
  4. INFO  : Concurrency mode is disabled, not creating a lock manager
  5. INFO  : Semantic Analysis Completed (retrial = false)
  6. INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
  7. INFO  : Completed compiling command(queryId=root_20240227234852_0877050e-8aea-4d                                                                                                                                                             1b-a8d5-de023a4d15ae); Time taken: 0.046 seconds
  8. INFO  : Concurrency mode is disabled, not creating a lock manager
  9. INFO  : Executing command(queryId=root_20240227234852_0877050e-8aea-4d1b-a8d5-de                                                                                                                                                             023a4d15ae): use lwztest
  10. INFO  : Starting task [Stage-0:DDL] in serial mode
  11. INFO  : Completed executing command(queryId=root_20240227234852_0877050e-8aea-4d                                                                                                                                                             1b-a8d5-de023a4d15ae); Time taken: 0.016 seconds
  12. INFO  : OK
  13. INFO  : Concurrency mode is disabled, not creating a lock manager
  14. No rows affected (0.08 seconds)
  15. 0: jdbc:hive2://node1:10000>
4、删除数据库

    drop database：删除数据库，默认行为是RESTRICT(限制)，这意味着仅在数据库为空时才删除它。

    要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE。

DROP [IF EXISTS] database_name [RESTRICT|CASCADE];

1.2、建表

1、表Table

    一个数据库通常包括一个或多个表。每个表由一个名字标识

    表包含带有数据的记录（行）。

2、建表语句
  1. CREATE TABLE [IF NOT EXISTS][db_name.]table_name
  2. (col_name data_type [COMMENT col_comment],...)
  3. [COMMENT table_comment]
  4. [ROW FORMAT DELIMITED ...];
  5.  
  6.     []中括号的语法表示可选。
  7.     建表语句中的语法顺序要和语法树中顺序保持一致。
3、数据类型

    Hive数据类型指的是表中列的字段类型；

    整体分为两类：原生数据类型（primitive data type）和复杂数据类型（complex data type）

    最常用的数据类型是字符串String和数字类型Int

4、分隔符指定语法

    ROW FORMAT DELIMITED语法用于指定字段之间相关的分隔符，这样Hive才能正确的读取解析数据。

    或者说只有分隔符指定正确，解析数据成功，我们才能在表中看到数据。

    LazySimpleSerDe是Hive默认的，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射kv之间、换行的分隔符号。

    在建表的时候可以根据数据的特点灵活搭配使用。

  1. ROW FORMAT DELIMITED
  2.     [FIELDS TERMINATED BY char] -------------->字段之间分隔符
  3.     [COLLECTION ITEMS TERMINATED BY char]----->集合元素之间分隔符
  4.     [MAP KEYS TERMINATED BY char]------------->Map映射kv之间分隔符
  5.     [LINES TERMINATED BY char]---------------->行数据之间分隔符
实战操作

创建测试数据archer.txt

  1. 1	后羿	5986	1784	396	336	remotely	arche
  2. 2	马可波罗	5584	200	392	344	remotely	arche
  3. 3	鲁班七号	5989	1756	396	400	remotely	arche
  4. 4	李元芳	5725	1770	411	336	remotely	arche
  5. 5	孙尚香	6014	1756	376	346	remotely	arche
  6. 6	黄忠	5998	1784	396	336	remotely	arche
  7. 7	狄仁杰	5710	1770	407	338	remotely	arche
  8. 8	虞姬	5669	1770	396	336	remotely	arche
  9. 9	成吉思汗	5799	1742	394	329	remotely	arche
  10. 10	百里守约	5611	1784	410	329	remotely	arche	assassin

建表语句

  1. create table t_archer(
  2.     id int comment 'ID',
  3.     name string comment '英雄名称',
  4.     hp_max int comment '最大生命',
  5.     mp_max int comment '最大法力',
  6.     attack_max int comment '最高物攻',
  7.     defense_max int comment '最大物防',
  8.     attack_range string comment '攻击范围',
  9.     role_main string comment '主要定位',
  10.     role_assist string comment '次要定位'
  11. )
  12. row format delimited
  13. fields terminated by '\t'; --字段之间的分隔符是tab键 制表符

创建完表，上传文件

  1. #上传文件到HDFS上/user/hive/warehouse/lwztest.db/t_archer路径下
  2. [root@node1 ~]# hadoop fs -put file:///export/archer.txt hdfs://node1:8020/user/hive/warehouse/lwztest.db/t_archer
  3. [root@node1 ~]#

DataGrip查看表数据

select * from t_archer;

Hive默认分隔符

    Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；

    默认分隔符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。

    在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入'\001'，显示^A

    在一些文本编辑器中将以SOH的形式显示。

测试数据student.txt

  1. 1张三10
  2. 2李四20
  3. 3王五30

上传文件到HDFS

  1. [root@node1 export]# hadoop fs -put file:///export/student.txt hdfs://node1:8020/user/hive/warehouse/lwztest.db/t_student
  2. [root@node1 export]#

建表语句

  1. -- 使用默认分隔符建表 \001 非打印字符
  2. create table t_student(
  3.     id int comment 'ID',
  4.     name string comment '学生名称',
  5.     age int comment '年龄'
  6. );

查询语句

select * from t_student;

查询结果如下

如果分隔符和建表语句不符的话，使用SQL语句查询，查询不到数据都是null，映射不成功。

1.3、show语法

    show相关的语句可以帮助用户查询相关信息。

    比如我们最常使用的查询当前数据库下有哪些表 show tables.

  1. -- 1、显示所有数据库SCHEMAS和DATABASES的用法功能一样
  2. show databases;
  3. show schemas;
  4.  
  5. -- 2、显示当前数据库所有表
  6. show tables;
  7. show tables [in database_name];--指定某个数据库
  8.  
  9. -- 3、查询显示一张表的元数据信息
  10. desc formatted t_student;

1.4、注释comment中文乱码解决

现象如下图乱码

Hive注释信息中文乱码解决

  1. --注意  下面sql语句需要在MYSQL中执行  修改Hive存储的元数据信息(metadata)
  2. use hive;
  3. show tables;
  4.  
  5. alter table hive.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
  6. alter table hive.TABLE_PARAMS modify column PARAM_VALUE varchar(256) character set utf8;
  7. alter table hive.PARTITION_PARAMS modify column PARAM_VALUE varchar(256) character set utf8;
  8. alter table hive.PARTITION_KEYS modify column PKEY_COMMENT varchar(256) character set utf8;
  9. alter table hive.INDEX_PARAMS modify column PARAM_VALUE varchar(256) character set utf8;

修改完再执行还是乱码，此时需要删除表，重新创建。即解决乱码问题。

  1. --删除表
  2. drop table t_student;

2、Hive SQL DML语法之加载数据

    在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；

    文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/use/hive/warehouse;

    不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功；

    最原始暴力的方式就是使用hadoop fs -put|-mv等方式直接将数据移动到表文件夹下；

    但是，Hive官方推荐使用Load命令将数据加载到表中。

1、Load语法功能

    Load英文单词的含义为：加载、装载；

    所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。

    纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作。

  1. LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
  2. LOCAL本地是哪里？
  3. 本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。
语法规则之LOCAL

  指定LOCAL，将在本地文件系统中查找文件路径。

    若指定相对路径，将相对于用户的当前工作目录进行解释；

    用户也可以为本地文件指定完整的URI，例：file:///user/hive/project/data1

  没有指定LOCAL关键字

    如果filepath指向的是一个完整的URI，会直接使用这个URI；

    如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）

Load加载数据实战
  1. use lwztest;
  2. ------Hive SQL DML Load 加载数据------
  3. --step1:建表
  4. --建表student_local 用于演示从本地加载数据
  5. create table student_local(
  6.     num int,
  7.     name string,
  8.     sex string,
  9.     age int,
  10.     dept string
  11. )
  12. row format delimited
  13. fields terminated by ',';
  14.  
  15. --建表student_HDFS 用于演示从HDFS加载数据
  16. create table student_HDFS(
  17.     num int,
  18.     name string,
  19.     sex string,
  20.     age int,
  21.     dept string
  22. )
  23. row format delimited
  24. fields terminated by ',';
  25.  
  26. --建议使用beeline客户端，可以显示出加载过程日志信息
  27. --step2：加载数据
  28. --从本地加载数据 数据位于HS2(node1)本地文件系统 本质是hadoop fs -put上传操作
  29. LOAD DATA LOCAL INPATH '/export/students.txt' INTO TABLE lwztest.student_local;
  30. --从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv移动操作
  31. --先把数据上传到HDFS上 hadoop fs -put /export/students.txt /
  32. LOAD DATA INPATH '/students.txt' INTO TABLE lwztest.student_HDFS;
  33.  
  34. students.txt 数据内容
  35. 1,李勇,男,20,CS
  36. 2,王敏,女,22,IS
  37. 3,王一,女,19,CS
  38. 4,郑明,男,20,MA
bin/beeline客户端操作

1、local本地加载-纯复制

  1. #连接beeline客户端
  2. [root@node1 ~]# /export/server/hive-3.1.3/bin/beeline
  3. SLF4J: Class path contains multiple SLF4J bindings.
  4. SLF4J: Found binding in [jar:file:/export/server/hive-3.1.3/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  5. SLF4J: Found binding in [jar:file:/export/server/hadoop-3.3.6/share/hadoop/common/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.cla                                                                                       ss]
  6. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  7. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  8. SLF4J: Class path contains multiple SLF4J bindings.
  9. SLF4J: Found binding in [jar:file:/export/server/hive-3.1.3/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  10. SLF4J: Found binding in [jar:file:/export/server/hadoop-3.3.6/share/hadoop/common/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.cla                                                                                       ss]
  11. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  12. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  13. Beeline version 3.1.3 by Apache Hive
  14. beeline> ! connect jdbc:hive2://node1:10000
  15. Connecting to jdbc:hive2://node1:10000
  16. Enter username for jdbc:hive2://node1:10000: root
  17. Enter password for jdbc:hive2://node1:10000:
  18. Connected to: Apache Hive (version 3.1.3)
  19. Driver: Hive JDBC (version 3.1.3)
  20. Transaction isolation: TRANSACTION_REPEATABLE_READ
  21. # 加载数据从本地
  22. 0: jdbc:hive2://node1:10000> LOAD DATA LOCAL INPATH '/export/students.txt' INTO TABLE lwztest.student_local;
  23. INFO  : Compiling command(queryId=root_20240303230810_d5bd2c94-3e72-490a-a8ea-728ac379d8ed): LOAD DATA LOCAL INPATH '/export/students.txt' INTO TABLE                                                                                        lwztest.student_local
  24. INFO  : Concurrency mode is disabled, not creating a lock manager
  25. INFO  : Semantic Analysis Completed (retrial = false)
  26. INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
  27. INFO  : Completed compiling command(queryId=root_20240303230810_d5bd2c94-3e72-490a-a8ea-728ac379d8ed); Time taken: 0.079 seconds
  28. INFO  : Concurrency mode is disabled, not creating a lock manager
  29. INFO  : Executing command(queryId=root_20240303230810_d5bd2c94-3e72-490a-a8ea-728ac379d8ed): LOAD DATA LOCAL INPATH '/export/students.txt' INTO TABLE                                                                                        lwztest.student_local
  30. INFO  : Starting task [Stage-0:MOVE] in serial mode
  31. INFO  : Loading data to table lwztest.student_local from file:/export/students.txt
  32. INFO  : Starting task [Stage-1:STATS] in serial mode
  33. INFO  : Completed executing command(queryId=root_20240303230810_d5bd2c94-3e72-490a-a8ea-728ac379d8ed); Time taken: 0.35 seconds
  34. INFO  : OK
  35. INFO  : Concurrency mode is disabled, not creating a lock manager
  36. No rows affected (0.439 seconds)
  37. 0: jdbc:hive2://node1:10000> select * from student_local;
  38. INFO  : Compiling command(queryId=root_20240303231007_8a3b8082-6984-4634-9698-a35a3fdf2fff): select * from student_local
  39. INFO  : Concurrency mode is disabled, not creating a lock manager
  40. INFO  : Semantic Analysis Completed (retrial = false)
  41. INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:student_local.num, type:int, comment:null), FieldSchema(name:student_local.name,                                                                                        type:string, comment:null), FieldSchema(name:student_local.sex, type:string, comment:null), FieldSchema(name:student_local.age, type:int, comment:null                                                                                       ), FieldSchema(name:student_local.dept, type:string, comment:null)], properties:null)
  42. INFO  : Completed compiling command(queryId=root_20240303231007_8a3b8082-6984-4634-9698-a35a3fdf2fff); Time taken: 0.273 seconds
  43. INFO  : Concurrency mode is disabled, not creating a lock manager
  44. INFO  : Executing command(queryId=root_20240303231007_8a3b8082-6984-4634-9698-a35a3fdf2fff): select * from student_local
  45. INFO  : Completed executing command(queryId=root_20240303231007_8a3b8082-6984-4634-9698-a35a3fdf2fff); Time taken: 0.0 seconds
  46. INFO  : OK
  47. INFO  : Concurrency mode is disabled, not creating a lock manager
  48. +--------------------+---------------------+--------------------+--------------------+---------------------+
  49. | student_local.num  | student_local.name  | student_local.sex  | student_local.age  | student_local.dept  |
  50. +--------------------+---------------------+--------------------+--------------------+---------------------+
  51. | 1                  | 李勇                  | 男                  | 20                 | CS                  |
  52. | 2                  | 王敏                  | 女                  | 22                 | IS                  |
  53. | 3                  | 王一                  | 女                  | 19                 | CS                  |
  54. | 4                  | 郑明                  | 男                  | 20                 | MA                  |
  55. +--------------------+---------------------+--------------------+--------------------+---------------------+
  56. 4 rows selected (0.415 seconds)
  57. 0: jdbc:hive2://node1:10000>

2、hdfs加载数据--直接移动数据到sql目录下

  1. # 先查询student_hdfs数据为空
  2. 0: jdbc:hive2://node1:10000> select * from student_hdfs;
  3. INFO  : Compiling command(queryId=root_20240303232201_33548c54-8e0a-404f-a2e8-c2f0e7255ce4): select * from student_hdfs
  4. INFO  : Concurrency mode is disabled, not creating a lock manager
  5. INFO  : Semantic Analysis Completed (retrial = false)
  6. INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:student_hdfs.num, type:int, comment:null), FieldSchema(name:student_hdfs.name, type:string, comment:null), FieldSchema(name:student_hdfs.sex, type:string, comment:null), FieldSchema(name:student_hdfs.age, type:int, comment:null), FieldSchema(name:student_hdfs.dept, type:string, comment:null)], properties:null)
  7. INFO  : Completed compiling command(queryId=root_20240303232201_33548c54-8e0a-404f-a2e8-c2f0e7255ce4); Time taken: 0.183 seconds
  8. INFO  : Concurrency mode is disabled, not creating a lock manager
  9. INFO  : Executing command(queryId=root_20240303232201_33548c54-8e0a-404f-a2e8-c2f0e7255ce4): select * from student_hdfs
  10. INFO  : Completed executing command(queryId=root_20240303232201_33548c54-8e0a-404f-a2e8-c2f0e7255ce4); Time taken: 0.0 seconds
  11. INFO  : OK
  12. INFO  : Concurrency mode is disabled, not creating a lock manager
  13. +-------------------+--------------------+-------------------+-------------------+--------------------+
  14. | student_hdfs.num  | student_hdfs.name  | student_hdfs.sex  | student_hdfs.age  | student_hdfs.dept  |
  15. +-------------------+--------------------+-------------------+-------------------+--------------------+
  16. +-------------------+--------------------+-------------------+-------------------+--------------------+
  17. No rows selected (0.196 seconds)
  18. # 加载数据从HDFS上进行加载students.txt数据
  19. 0: jdbc:hive2://node1:10000> LOAD DATA INPATH '/students.txt' INTO TABLE lwztest.student_HDFS;
  20. INFO  : Compiling command(queryId=root_20240303232209_47aaa363-d26c-422f-93db-09b3515e4098): LOAD DATA INPATH '/students.txt' INTO TABLE lwztest.student_HDFS
  21. INFO  : Concurrency mode is disabled, not creating a lock manager
  22. INFO  : Semantic Analysis Completed (retrial = false)
  23. INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
  24. INFO  : Completed compiling command(queryId=root_20240303232209_47aaa363-d26c-422f-93db-09b3515e4098); Time taken: 0.052 seconds
  25. INFO  : Concurrency mode is disabled, not creating a lock manager
  26. INFO  : Executing command(queryId=root_20240303232209_47aaa363-d26c-422f-93db-09b3515e4098): LOAD DATA INPATH '/students.txt' INTO TABLE lwztest.student_HDFS
  27. INFO  : Starting task [Stage-0:MOVE] in serial mode
  28. INFO  : Loading data to table lwztest.student_hdfs from hdfs://node1:8020/students.txt
  29. INFO  : Starting task [Stage-1:STATS] in serial mode
  30. INFO  : Completed executing command(queryId=root_20240303232209_47aaa363-d26c-422f-93db-09b3515e4098); Time taken: 0.284 seconds
  31. INFO  : OK
  32. INFO  : Concurrency mode is disabled, not creating a lock manager
  33. No rows affected (0.346 seconds)
  34. #再次查询有数据
  35. 0: jdbc:hive2://node1:10000> select * from student_hdfs;
  36. INFO  : Compiling command(queryId=root_20240303232218_a3c37424-20fd-4c65-a4c1-e2f3e2f47e6f): select * from student_hdfs
  37. INFO  : Concurrency mode is disabled, not creating a lock manager
  38. INFO  : Semantic Analysis Completed (retrial = false)
  39. INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:student_hdfs.num, type:int, comment:null), FieldSchema(name:student_hdfs.name, type:string, comment:null), FieldSchema(name:student_hdfs.sex, type:string, comment:null), FieldSchema(name:student_hdfs.age, type:int, comment:null), FieldSchema(name:student_hdfs.dept, type:string, comment:null)], properties:null)
  40. INFO  : Completed compiling command(queryId=root_20240303232218_a3c37424-20fd-4c65-a4c1-e2f3e2f47e6f); Time taken: 0.225 seconds
  41. INFO  : Concurrency mode is disabled, not creating a lock manager
  42. INFO  : Executing command(queryId=root_20240303232218_a3c37424-20fd-4c65-a4c1-e2f3e2f47e6f): select * from student_hdfs
  43. INFO  : Completed executing command(queryId=root_20240303232218_a3c37424-20fd-4c65-a4c1-e2f3e2f47e6f); Time taken: 0.0 seconds
  44. INFO  : OK
  45. INFO  : Concurrency mode is disabled, not creating a lock manager
  46. +-------------------+--------------------+-------------------+-------------------+--------------------+
  47. | student_hdfs.num  | student_hdfs.name  | student_hdfs.sex  | student_hdfs.age  | student_hdfs.dept  |
  48. +-------------------+--------------------+-------------------+-------------------+--------------------+
  49. | 1                 | 李勇                 | 男                 | 20                | CS                 |
  50. | 2                 | 王敏                 | 女                 | 22                | IS                 |
  51. | 3                 | 王一                 | 女                 | 19                | CS                 |
  52. | 4                 | 郑明                 | 男                 | 20                | MA                 |
  53. +-------------------+--------------------+-------------------+-------------------+--------------------+
  54. 4 rows selected (0.255 seconds)
  55. 0: jdbc:hive2://node1:10000>

2、Insert插入数据

Insert语法功能

    Hive官方推荐加载数据的方式：清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。

    也可以使用insert语法把数据插入到指定的表中，最常用的配合是把查询返回结果插入到另一张表中。

实例：

  1. #创建t_1表
  2. 0: jdbc:hive2://node1:10000> create table t_1(id int,name string);
  3. INFO  : Compiling command(queryId=root_20240304000029_a2c6ace7-4aca-463c-83a2-b09ef288cd2f): create table t_1(id int,name string)
  4. INFO  : Concurrency mode is disabled, not creating a lock manager
  5. INFO  : Semantic Analysis Completed (retrial = false)
  6. INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
  7. INFO  : Completed compiling command(queryId=root_20240304000029_a2c6ace7-4aca-463c-83a2-b09ef288cd2f); Time taken: 0.026 seconds
  8. INFO  : Concurrency mode is disabled, not creating a lock manager
  9. INFO  : Executing command(queryId=root_20240304000029_a2c6ace7-4aca-463c-83a2-b09ef288cd2f): create table t_1(id int,name string)
  10. INFO  : Starting task [Stage-0:DDL] in serial mode
  11. INFO  : Completed executing command(queryId=root_20240304000029_a2c6ace7-4aca-463c-83a2-b09ef288cd2f); Time taken: 0.128 seconds
  12. INFO  : OK
  13. INFO  : Concurrency mode is disabled, not creating a lock manager
  14. No rows affected (0.171 seconds)
  15. #insert插入一条数据
  16. 0: jdbc:hive2://node1:10000> insert into table t_1 values(1,"zhangsan");
  17. INFO  : Compiling command(queryId=root_20240304000119_97a0e4ac-45cf-414c-b2d8-78e98532ca72): insert into table t_1 values(1,"zhangsan")
  18. INFO  : Concurrency mode is disabled, not creating a lock manager
  19. INFO  : Semantic Analysis Completed (retrial = false)
  20. INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:col1, type:int, comment:null), FieldSchema(name:col2, type:string, comment:null)], properties:null)
  21. INFO  : Completed compiling command(queryId=root_20240304000119_97a0e4ac-45cf-414c-b2d8-78e98532ca72); Time taken: 0.571 seconds
  22. INFO  : Concurrency mode is disabled, not creating a lock manager
  23. INFO  : Executing command(queryId=root_20240304000119_97a0e4ac-45cf-414c-b2d8-78e98532ca72): insert into table t_1 values(1,"zhangsan")
  24. WARN  : Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
  25. INFO  : Query ID = root_20240304000119_97a0e4ac-45cf-414c-b2d8-78e98532ca72
  26. INFO  : Total jobs = 3
  27. INFO  : Launching Job 1 out of 3
  28. INFO  : Starting task [Stage-1:MAPRED] in serial mode
  29. INFO  : Number of reduce tasks determined at compile time: 1
  30. INFO  : In order to change the average load for a reducer (in bytes):
  31. INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
  32. INFO  : In order to limit the maximum number of reducers:
  33. INFO  :   set hive.exec.reducers.max=<number>
  34. INFO  : In order to set a constant number of reducers:
  35. INFO  :   set mapreduce.job.reduces=<number>
  36. INFO  : number of splits:1
  37. INFO  : Submitting tokens for job: job_1709476925218_0001
  38. INFO  : Executing with tokens: []
  39. INFO  : The url to track the job: http://node1:8088/proxy/application_1709476925218_0001/
  40. INFO  : Starting Job = job_1709476925218_0001, Tracking URL = http://node1:8088/proxy/application_1709476925218_0001/
  41. INFO  : Kill Command = /export/server/hadoop-3.3.6/bin/mapred job  -kill job_1709476925218_0001
  42. INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
  43. INFO  : 2024-03-04 00:01:50,003 Stage-1 map = 0%,  reduce = 0%
  44. INFO  : 2024-03-04 00:02:02,509 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.45 sec
  45. INFO  : 2024-03-04 00:02:08,744 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6.49 sec
  46. INFO  : MapReduce Total cumulative CPU time: 6 seconds 490 msec
  47. INFO  : Ended Job = job_1709476925218_0001
  48. INFO  : Starting task [Stage-7:CONDITIONAL] in serial mode
  49. INFO  : Stage-4 is selected by condition resolver.
  50. INFO  : Stage-3 is filtered out by condition resolver.
  51. INFO  : Stage-5 is filtered out by condition resolver.
  52. INFO  : Starting task [Stage-4:MOVE] in serial mode
  53. INFO  : Moving data to directory hdfs://node1:8020/user/hive/warehouse/lwztest.db/t_1/.hive-staging_hive_2024-03-04_00-01-19_178_7643664026654055193-4/-ext-10000 from hdfs://node1:8020/user/hive/warehouse/lwztest.db/t_1/.hive-staging_hive_2024-03-04_00-01-19_178_7643664026654055193-4/-ext-10002
  54. INFO  : Starting task [Stage-0:MOVE] in serial mode
  55. INFO  : Loading data to table lwztest.t_1 from hdfs://node1:8020/user/hive/warehouse/lwztest.db/t_1/.hive-staging_hive_2024-03-04_00-01-19_178_7643664026654055193-4/-ext-10000
  56. INFO  : Starting task [Stage-2:STATS] in serial mode
  57. INFO  : MapReduce Jobs Launched:
  58. INFO  : Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 6.49 sec   HDFS Read: 15250 HDFS Write: 241 SUCCESS
  59. INFO  : Total MapReduce CPU Time Spent: 6 seconds 490 msec
  60. INFO  : Completed executing command(queryId=root_20240304000119_97a0e4ac-45cf-414c-b2d8-78e98532ca72); Time taken: 50.958 seconds
  61. INFO  : OK
  62. INFO  : Concurrency mode is disabled, not creating a lock manager
  63. No rows affected (51.537 seconds)     #51.537 seconds   51秒才执行完
  64. 0: jdbc:hive2://node1:10000> select * from t_1;
  65. INFO  : Compiling command(queryId=root_20240304000349_9b1c1d8d-3008-406a-ae56-86f5c06b37f4): select * from t_1
  66. INFO  : Concurrency mode is disabled, not creating a lock manager
  67. INFO  : Semantic Analysis Completed (retrial = false)
  68. INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:t_1.id, type:int, comment:null), FieldSchema(name:t_1.name, type:string, comment:null)], properties:null)
  69. INFO  : Completed compiling command(queryId=root_20240304000349_9b1c1d8d-3008-406a-ae56-86f5c06b37f4); Time taken: 0.179 seconds
  70. INFO  : Concurrency mode is disabled, not creating a lock manager
  71. INFO  : Executing command(queryId=root_20240304000349_9b1c1d8d-3008-406a-ae56-86f5c06b37f4): select * from t_1
  72. INFO  : Completed executing command(queryId=root_20240304000349_9b1c1d8d-3008-406a-ae56-86f5c06b37f4); Time taken: 0.001 seconds
  73. INFO  : OK
  74. INFO  : Concurrency mode is disabled, not creating a lock manager
  75. +---------+-----------+
  76. | t_1.id  | t_1.name  |
  77. +---------+-----------+
  78. | 1       | zhangsan  |
  79. +---------+-----------+
  80. 1 row selected (0.2 seconds)

    insert语法把数据插入到指定的表中效率慢，一般不推荐或不会这么操作。

insert+select

    insert+select表示：将后面查询返回的结果作为内容插入到指定表中。

    1、需要保证查询结果列的数目和需要插入数据表格的列数目一致。

    2、如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

INSERT INTO TABLE tablename select_statement1 FROM from_statement;

  1. ------Hive SQL DML Insert 插入数据------
  2. --step1:创建一张源表student
  3. drop table if exists student;
  4. create table student(
  5.     num int,
  6.     name string,
  7.     sex string,
  8.     age int,
  9.     dept string
  10. )
  11. row format delimited
  12. fields terminated by ',';
  13.  
  14. --step2：加载数据
  15. --从本地加载数据 数据位于HS2(node1)本地文件系统 本质是hadoop fs -put上传操作
  16. LOAD DATA LOCAL INPATH '/export/students.txt' INTO TABLE lwztest.student;
  17.  
  18. --step3:创建一张目标表  只有两个字段
  19. create table student_from_insert(
  20.     sno int,
  21.     sname string
  22. );
  23.  
  24. --使用insert+select插入数据到新表
  25. insert into table student_from_insert select num,name from student;
  26.  
  27. select * from student_from_insert;

3、Hive SQL DML语法之查询数据

Select语法树

    从哪里查询取决于FROM关键字后面的table_reference。表名和列名不区分大小写。

    select_expr表示检索查询返回的列，必须至少有一个select_expr。

    默认情况下是有ALL的，代表全部查询。DISTINCT：去重。GROUP BY：分组。ORDER BY排序。默认升序(ASC)、倒序(DESC)、LIMIT 用于限制SELECT语句返回的行数。

不详细介绍了，和SQL语法一样。

  1. SELECT [ALL|DISTINCT] select_expr,select_expr,... 
  2.   FROM table_reference
  3.   [WHERE where_condition]
  4.   [GROUP BY col_list]
  5.   [ORDER BY col_list]
  6.   [LIMIT [offset,]rows];

聚合操作

    SQL中拥有很多可用于计数和计算的内建函数，其使用的语法是：SELECT function(列) FROM 表。

    聚合操作函数如：Count、Sum、Max、Min、Avg等函数。

    聚合函数的最大特点是不管原始数据有多少行记录，经过聚合操作只返回一条数据，这条数据就是聚合的结果。

AVG(column)返回某列的平均值
COUNT(column)返回某列的行数（不包括NULL值）
COUNT(*)返回被选行数
MAX(column)返回某列的最高值
MIN(column)返回某列某列的最低值
SUM(column)返回某列的总和

HAVING

    在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用。

    HAVING子句可以让我们筛选分组后的各组数据，并且可以在Having中使用聚合函数，因为此时where，group by 已经执行结束，结果集已经确定。

HAVING与WHERE区别

    having是在分组后对数据进行过滤

    where是在分组前对数据进行过滤

    having后面可以使用聚合函数

    where后面不可以使用聚合函数

执行顺序

    在查询过程中执行顺序：from>where>group(含聚合)>having>order>select;

    1.聚合语句要比having子句优先执行

    2.where子句在查询过程中执行优先级别优先于聚合语句。

  1. select state,sum(age) as cnts from t_1
  2. where c_date='2021-01-28'
  3. group by state
  4. having cnts>1000
  5. limit 2;

4、Hive SQL Join关联查询

    join语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据。

    在Hive中，使用最多，最重要的两种join分别是inner join(内连接)、left join(左连接)

  1. join_table:
  2.   table_reference [INNER] JOIN table_factor [join_condition]
  3.  | table_reference {LEFT}[OUTER] JOIN table_factor [join_condition]
  4.  
  5. join_condition:
  6.   ON expression

    table_reference：是join查询中使用的表名

    table_factor：与table_reference相同，是联接查询中使用的表名。

    join_condition：join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关键字。

    INNER可以省略：INNER JOIN ==JOIN

INNER JOIN关系如下图

left join 左连接

    left join中文叫做是左外连接(Left Outer Join)或者左连接，其中outer可以省略，left outer join是早期的写法。

    left join的核心就在于left左。左指的是join关键字左边的表，简称左表。

    通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

5、Hive SQL 中的函数使用

    Hive内建了不少函数，用户满足用户不同使用需求，提高SQL编写效率：

        1、使用show functions查看当下可用的所有函数；

        2、通过describe function extended funcname来查看函数的使用方式；

  1. --查看当下可用的所有函数
  2. show functions;
  3.  
  4. --查看函数的使用方式
  5. describe function extended count;

    Hive的函数分为两大类：内置函数（Build-in Functions）、用户定义函数UDF（User-Defined Functions）：

      1、内置函数可以分为：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；

      2、用户定义函数根据输入输出的行数可分为3类：UDF、UDAF、UDTF。

用户定义函数UDF分类标准

根据函数输入输出的行数：

    UDF（User-Defined Functions）普通函数，一进一出

    UDAF（User-Defined Aggregation Functions）聚合函数，多进一出

    UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出

内置函数

    内置函数（Build-in）指的是Hive开发实现好，直接可以使用的函数，也叫做内建函数。

官方文档地址

    内置函数根据应用归类整体可以分为8大种类型。数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；

  1. ------String Functions 字符串函数------
  2. select length("lwztest");
  3. select reverse("lwztest");
  4. --拼接
  5. select concat("lwz","test");
  6. --带分隔符字符串连接函数:concat_ws(separator,[string | array(string)]+)
  7. select concat_ws('.','www',array('com','lwz','cn'));
  8. --字符串截取函数:substr(str,pos[,len]) 或者substring(str,pos[,len])
  9. select substr("lwztest",-2); --pos是从1开始的索引，如果为负数则倒着数
  10. select substr("lwztest",2);
  11. --分割字符串函数:split(str,regex)
  12. --split针对字符串数据进行分割，返回是数组array,可以通过数组的下标取内部的元素,注意下标从0开始
  13. select split('apache hive',' ');
  14. select split('apache hive',' ')[0];
  15. select split('apache hive',' ')[1];
  16.  
  17. ------Date Functions 日期函数------
  18. --获取当前日期:current_date 
  19. select current_date();
  20. --获取当前UNIX时间戳函数:unix_timestamp 
  21. select unix_timestamp();
  22. --日期转UNIX时间戳函数:unix_timestamp
  23. select unix_timestamp("2011-12-07 13:01:03"); 
  24. --指定格式日期转UNIX时间戳函数:unix_timestamp
  25. select unix_timestamp('2011120713:01:03','yyyyMMdd HH:mm:ss');
  26. --UNIX时间戳转日期函数:from_unixtime 
  27. select from_unixtime(1_618_238_391);
  28. select from_unixtime(0,'yyyy-MM-dd HH:mm:ss');
  29. --日期比较函数:datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' 
  30. select datediff('2012-12-08','2012-05-09'); 
  31. --日期增加函数:date_add
  32. select date_add('2012-02-28',10); 
  33. --日期减少函数:date_sub
  34. select date_sub('2012-01-1',10);	
  35.  
  36. ------Mathematical Functions 数学函数------
  37. --取整函数: round 返回double类型的整数值部分(遵循四舍五入) 
  38. select round(3.1415926);
  39. --指定精度取整函数:round(double a, intd)返回指定精度d的double类型 
  40. select round(3.1415926,4);
  41. --取随机数函数:rand 每次执行都不一样返回一个0到1范围内的随机数 
  42. select rand();
  43. --指定种子取随机数函数:rand(int seed)得到一个稳定的随机数序列 
  44. select rand(3);
  45.  
  46. ------Conditional Functions条件函数------
  47. --使用之前课程创建好的student表数据
  48. select * from student limit 3;
  49. --if条件判断:if(boolean testCondition, T valueTrue,T valueFalseOrNull)
  50. select if(1=2,100,200);
  51. select if(sex ='男','M','W') from student limit 3;
  52. --空值转换函数:nvl(T value,T default_value) 
  53. select nvl("allen","lwztest"); 
  54. select nvl(null,"lwztest");
  55. --条件转换函数:CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
  56. select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
  57. select case sex when '男' then 'male' else 'female' end from student limit 3;

数据仓库 & Apache Hive

请记住，你当下的结果，由过去决定；你现在的努力，在未来见效；
不断学习才能不断提高！磨炼，不断磨炼自己的技能！学习伴随我们终生！
生如蝼蚁，当立鸿鹄之志，命比纸薄，应有不屈之心。
乾坤未定，你我皆是黑马，若乾坤已定，谁敢说我不能逆转乾坤？
努力吧，机会永远是留给那些有准备的人，否则，机会来了，没有实力，只能眼睁睁地看着机会溜走。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/859990
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号