Hive基础知识大全

Hive基础知识大全


  
1、Hive根本概念

1.1、Hive简介

Hive本质是将SQL转化为MapReduce的任务进行计算，底层由HDFS来提供数据存储。说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具，乃至更进一步说hive就是MapReduce的一个客户端。
1.2、什么是Hive(口试题)

• Hive是数据库建模工具之一
  
• 可以向Hive传入一条交互式SQL,在海量数据中查询分析得到结果的平台
  

1.3、为什么要使用Hive

• 假如使用Hadoop的话，人员学习成本高，MapReduce的实现复杂查询逻辑的开发难度较大。
  
• 假如使用Hive的话，可以直接操纵接口接纳类SQL语法，免去了写MapReduce,大大进步了开发服从，而且功能扩展很方便（比如：开窗函数）
  

1.4、Hive的特点

• 可扩展性：Hive可以自由的扩展集群的规模、一样平常环境下不需要重启服务
  
• 延伸性：Hive支持自定函数，用户可以根据自己的需求来实现自己的函数
  
• 容错：纵然节点出现错误，SQL仍然可以完成实行
  

1.5、Hive的优缺点

   长处
  

• 操纵接口接纳类SQL语法，提供快速开发的能力（简单，易上手）
  
• 避免了去写MapReduce，减少了开发人员的学习成本
  
• 由于Hive的延迟性比力高，因此Hive常用于数据分析，实用于对实时性要求不高的场合
  
• 由于Hive的实行延迟性比力高（不断的开关JVM虚拟机），Hive的优势在于处置惩罚大数据，对于小数据没有优势
  
• Hive支持自定义函数，用户可以根据自己的需求来实现满足自己需求的函数
  
• 集群可以自由扩展而且有良好的容错性，节点出现问题SQL仍然可以完成实行
  

   缺点
  

• Hive的HiveSql表达能力有限
  
  
  
  • 迭代是算法无法表达（反复调用，mr之间独立，只有一个map一个reduce，反复开关。名词表明：
    反复调用：迭代式算法需要多次调用同一组计算，每次调用的结果会影响下一次调用的输入。
    MR之间独立：传统的MapReduce任务是独立的，每个任务之间没有状态共享。这意味着每次MapReduce任务运行时，不知道之前任务的结果。
    只有一个map和一个reduce：在描述迭代式算法时，这句话可能意味着每次迭代只有一个Map阶段和一个Reduce阶段，但每次都需要重新启动MapReduce作业。
    反复开关：每次迭代需要启动和关闭MapReduce作业，这带来了大量的开销，因为每次启动和关闭作业都需要花费时间和资源。）
    
  
  
• Hive的服从比力低
  
  
  
  • Hive自动天生的MapReduce作业，通常环境下不敷智能化
    
  • Hive调优比力困难难，粒度较粗（HiveSql根据模本转成MapReduce,不能像自己编写的MapReduce一样精致，无法控制在map处置惩罚数据还是reduce处置惩罚数据）
    
  
  

   迭代算法：迭代式算法是指那些需要反复实行同一组计算步骤的算法，每次迭代的结果依赖于前一次迭代的结果。
  1.6、hive和mysql的区别

1.7、hive的应用场景

• 日志分析：大部门互联网公司使用hive进行日志分析，如百度、淘宝等。
  
• 统计一个网站一个时间段内的pv,uv，SKU,SPU,SKC
  
• 多维度数据分析（数据堆栈）
  
• 海量布局化(关系型)数据离线分析
  
• 构建数据堆栈
  

   名词表明

1. PV（Page View）访问量, 即页面浏览量或点击量，衡量网站用户访问的网页数量；在一定统计周期内用户每打开或刷新一个页面就记录1次，多次打开或刷新同一页面则浏览量累计。
3. UV（Unique Visitor）独立访客，统计1天内访问某站点的用户数(以cookie为依据);访问网站的一台电脑客户端为一个访客。可以理解成访问某网站的电脑的数量。网站判断来访电脑的身份是通过来访电脑的cookies实现的。如果更换了IP后但不清除cookies，再访问相同网站，该网站的统计中UV数是不变的。如果用户不保存cookies访问、清除了cookies或者更换设备访问，计数会加1。00:00-24:00内相同的客户端多次访问只计为1个访客。

复制代码

1.8、Hive架构

1.8.1、Hive Client

   Hive Client 是与Hive交互的工具，用户可以通过它提交HiveQL查询、实行数据操纵并获取结果。
  

• Client(hive shell)
  
  
  
  • 启动：可以通过运行hive命令启动Hive CLI。
    
  • 功能：支持实行HiveQL查询、检察表布局、插入数据、创建表等操纵。
    
  • 局限性：由于CLI是基于命令行的，操纵可能不如图形界面直观，且在某些环境下（如长时间运行的查询）可能不太方便。
    
  
  
• JDBC/ODBC(java访问hive)
  
  
  
  • JDBC客户端：例如，Java步伐可以通过Hive的JDBC驱动与HiveServer2进行连接和操纵。
    
  • ODBC客户端：例如，使用ODBC接口，可以在Windows环境中使用Excel等工具连接Hive。
    
  • 优势：提供与多种应用和工具的集成能力，适合构建复杂的数据处置惩罚和分析应用。
    
  
  
• Beeline(Beeline是Hive的JDBC客户端，提供了一种与HiveServer2交互的方式)
  
  
  
  • 启动：通过运行beeline命令启动，可以连接到本地或远程的HiveServer2实例。
    
  • 优势：相比于Hive CLI，Beeline更加轻量级，而且支持更好的多用户并发访问。
    
  • 使用方式：范例的连接命令是!connect jdbc:hive2://hostname:port/default，然后输入用户名和密码进行连接。
    
  • 特性：支持更丰富的SQL语法、参数化查询、脚本实行等。
    
  
  

1.8.2、Hive Metastore(Hive的元数据存储)

   在Apache Hive中，Metastore（元数据存储）饰演着非常重要的脚色，它负责管理和存储Hive表的元数据信息。
  元数据存储在数据库中，默认存在自带的derby数据库（单用户局限性）中，保举使用Mysql进行存储。
  

• 作用：对Hive表的源数据进行存储，元数据包括：表名、表所属的数据库（默认default数据库）、表的拥有者、列名、表的分区字段、表的类型（内部表还是外部表）、表的存储格式、表的存储位置。
  

1.8.3、sql语句是如何转换成MapReduce任务的（重点！！！！！！！！！！！！）

• 剖析器（SQL Parser）：将SQL字符串转换成抽象语法树（从3.x版本后，转换成一些stage）,在此阶段，Hive会检查语法错误：比如白哦是否在、字段是否存在。
  
• 编译器（Physical Plan）：将抽象语法树（从3.x版本后，转换成一些stage）生存逻辑实行计划。
  
• 优化器（Query Optimizer）：对逻辑实行计划进行优化。
  
• 实行器（Execution）：将逻辑实行计划转换成可以运行的物理实行计划，也就是MapReduce任务
  
• 结果处置惩罚：Hive将天生的MapReduce作业提交给Hadoop集群中的YARN进行实行，在任务实行完成后，将结果返回给用户。
  

2、Hive的三种交互方式

(1) 第一种交互方式

   shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互。

1. 服务端启动metastore服务（后台启动）：nohup hive --service metastore &
2. 进入命令:hive
3. 退出命令行：quit;

复制代码

(2) 第二种交互方式

   Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器，来完成访问操纵，这是生产环境用法最多的

1. 服务端启动hiveserver2服务：
2. nohup hive --service metastore &
3. nohup hiveserver2 &
5. 需要稍等一下，启动服务需要时间：
6. beeline -u jdbc:hive2://master:10000 -n root
7. 退出命令行：!exit

复制代码

(3) 第三种交互方式

   使用 –e 参数来直接实行hql的语句

1. bin/hive -e "show databases;
2. "

复制代码

  使用 –f 参数通过指定文本文件来实行hql的语句
  特点：实行完sql后，回到linux命令行。

1. vim hive.sql
3. create database bigdata30_test;
4. use bigdata30_test;
6. create table test1
7. (
8.     id bigint,
9.     name string
10. )
11. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
13. show tables;

复制代码

1. hive -f hive.sql

复制代码

(4) hive cli和beeline cli的区别

3、Hive元数据

Hive元数据库中一些重要的表布局及用途**，方便Impala、SparkSQL、Hive等组件访问元数据库的理解。
1、存储Hive版本的元数据表(VERSION)，该表比力简单，但很重要,假如这个表出现问题，根本进不来Hive-Cli。比如该表不存在，当启动Hive-Cli的时间，就会报错“Table ‘hive.version’ doesn’t exist”
2、Hive数据库相干的元数据表(DBS、DATABASE_PARAMS)
​ DBS：该表存储Hive中所有数据库的根本信息。
​ DATABASE_PARAMS：该表存储数据库的相干参数。
3、Hive表和视图相干的元数据表
​ 主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联。
​ TBLS:该表中存储Hive表，视图，索引表的根本信息。
​ TABLE_PARAMS:该表存储表/视图的属性信息。
​ TBL_PRIVS：该表存储表/视图的授权信息。
4、Hive文件存储信息相干的元数据表
​ 主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive表时间也可以指定各种文件格式，Hive在将HQL剖析成MapReduce时间，需要知道去哪里，使用哪种格式去读写HDFS文件，而这些信息就保存在这几张表中。
​ SDS：该表保存文件存储的根本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的SD_ID与该表关联，可以获取Hive表的存储信息。
​ SD_PARAMS: 该表存储Hive存储的属性信息。
​ SERDES:该表存储序列化使用的类信息。
​ SERDE_PARAMS:该表存储序列化的一些属性、格式信息，比如:行、列分隔符。
5、Hive表字段相干的元数据表
​ 主要涉及COLUMNS_V2：该表存储表对应的字段信息。
4、Hive的根本操纵

4.1 Hive库操纵

4.1.1 创建数据库

   1）创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。

1. create database testdb;

复制代码

  2）避免要创建的数据库已经存在错误，增加if not exists判断。（尺度写法）

1. create database [if not exists] testdb;
3. create database if not exists bigdata30_test2;

复制代码

4.1.2 创建数据库和位置

1. create database if not exists bigdata30_test3 location '/bigdata30/liliangdb';

复制代码

4.1.3 修改数据库

   数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。（重点关注哪些不能改，以及为什么！！）

1. alter database dept set dbproperties('createtime'='20220531');

复制代码

4.1.4 数据库详细信息

   1）表现数据库（show）

1. show databases;

复制代码

  2）可以通过like进行过滤

1. show databases like 't*';

复制代码

  3）检察详情（desc）

1. desc database testdb;

复制代码

  4）切换数据库（use）

1. use testdb;

复制代码

4.1.5 删除数据库（将删除的目录移动到回收站中）

   1）最简写法

1. drop database testdb;

复制代码

  2）假如删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

1. drop database if exists testdb;

复制代码

  3)假如数据库不为空，使用cascade命令进行逼迫删除。报错信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(messageatabase db_hive is not empty. One or more tables exist.)

1. drop database if exists testdb cascade;

复制代码

4.2、Hive数据类型

4.2.1、基础数据类型

类型Java数据类型描述TINYINTbyte8位有符号整型。取值范围：-128~127。SMALLINTshort16位有符号整型。取值范围：-32768~32767。INTint32位有符号整型。取值范围：-2 31 ~2 31 -1。BIGINTlong64位有符号整型。取值范围：-2 63 +1~2 63 -1。BINARY二进制数据类型，目前长度限定为8MB。FLOATfloat32位二进制浮点型。DOUBLEdouble64位二进制浮点型。DECIMAL(precision,scale)10进制精确数字类型。precision：表现最多可以表现多少位的数字。取值范围：1 <= precision <= 38。scale：表现小数部门的位数。取值范围： 0 <= scale <= 38。假如不指定以上两个参数，则默认为decimal(10,0)。VARCHAR(n)变长字符类型，n为长度。取值范围：1~65535。CHAR(n)固定长度字符类型，n为长度。最大取值255。长度不足则会填充空格，但空格不参与比力。STRINGstring字符串类型，目前长度限定为8MB。DATE日期类型，格式为yyyy-mm-dd。取值范围：0000-01-01~9999-12-31。DATETIME日期时间类型。取值范围：0000-01-01 00:00:00.000~9999-12-31 23.59:59.999，精确到毫秒。TIMESTAMP与时区无关的时间戳类型。取值范围：0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999，精确到纳秒。说明 对于部门时区相干的函数，例如cast( as string)，要求TIMESTAMP按照与当前时区符合的方式来展现。BOOLEANbooleanBOOLEAN类型。取值：True、False。 4.2.2、复杂数据类型

类型定义方法构造方法ARRAYarray<int>``array<struct<a:int, b:string>>array(1, 2, 3)``array(array(1, 2), array(3, 4))MAPmap<string, string>``map<smallint, array<string>>map(“k1”, “v1”, “k2”, “v2”)``map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’))STRUCTstruct<x:int, y:int>struct<field1:bigint, field2:array<int>, field3:map<int, int>> named_struct(‘x’, 1, ‘y’, 2)named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200))   Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map雷同，而STRUCT与C语言中的Struct雷同，它封装了一个定名字段集合，复杂数据类型允许任意条理的嵌套。还有一个uniontype< 所有类型，所有类型… > 。
  ​ 数组：array< 所有类型 >；
​ Map < 根本数据类型，所有数据类型 >；
​ struct < 名：所有类型[注释] >;
​ uniontype< 所有类型，所有类型… >
  4.3、Hive表操纵

   Hive的存储格式:
  Hive没有专门的数据文件格式,常见的有以下几种:
  ​ TEXTFILE
​ SEQUENCEFILE
​ AVRO
​ RCFILE
​ ORCFILE
​ PARQUET

1. TextFile:
2.        TEXTFILE 即正常的文本格式，是Hive默认文件存储格式，因为大多数情况下源数据文件都是以text文件格式保存（便于查看验数和防止乱码）。此种格式的表文件在HDFS上是明文，可用hadoop fs -cat命令查看，从HDFS上get下来后也可以直接读取。
3.         TEXTFILE 存储文件默认每一行就是一条记录，可以指定任意的分隔符进行字段间的分割。但这个格式无压缩，需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用，使用这种方式，Hive不会对数据进行切分，从而无法对数据进行并行操作。
4. 一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式，其他事实表和维度表都不建议使用。
6. RCFile:
7. Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能。通常写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。 RCFile是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据`列式存储`，有利于数据压缩和快速的列存取。
9. ORCFile:
10. Hive从0.11版本开始提供了ORC的文件格式，ORC文件不仅仅是一种列式文件存储格式，最重要的是有着很高的压缩比，并且对于MapReduce来说是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，不仅可以很大程度的节省HDFS存储资源，而且对数据的查询和处理性能有着非常大的提升，因为ORC较其他文件格式压缩比高，查询任务的输入数据量减少，使用的Task也就减少了。ORC能很大程度的节省存储和计算资源，但它在读写时候需要消耗额外的CPU资源来压缩和解压缩，当然这部分的CPU消耗是非常少的。
12. Parquet:
13. 通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如数组、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，很可能需要把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，它是语言、平台无关的，并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势：支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.
15. SEQUENCEFILE:
16. SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，实际生产中不会使用。
18. AVRO:
19. Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog。

复制代码

  Hive的四大常用存储格式存储服从及实行速度对比
  

   结论：ORCFILE存储文件读操纵服从最高
  耗时比力：ORC<arquet<RC<Text
  

   结论：ORCFILE存储文件占用空间少，压缩服从高
  占用空间：ORC<arquet<RC<Text
  4.3.1、创建表

1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
2. [(col_name data_type [COMMENT col_comment], ...)]
3. [COMMENT table_comment]
4. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
5. [CLUSTERED BY (col_name, col_name, ...)
6. [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
7. [ROW FORMAT row_format]
8. [STORED AS file_format]
9. [LOCATION hdfs_path]
12. 字段解释说明:
13. - CREATE TABLE
14.         创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
16. - EXTERNAL
17.         关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
18.         创建内部表时，会将数据移动到数据仓库指向的路径（默认位置）；
19.         创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。
20.         在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
22. - COMMENT：
23.         为表和列添加注释。
25. - PARTITIONED BY
26.         创建分区表
28. - CLUSTERED BY
29.         创建分桶表
31. - SORTED BY
32.         不常用
34. - ROW FORMAT
35.   DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
36.         用户在建表的时候可以自定义SerDe或者使用自带的SerDe。
37.         如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。
38.         在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
39.         SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
41. - STORED AS指定存储文件类型
42.         常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
43.         如果文件数据是纯文本，可以使用STORED AS TEXTFILE。
44.         如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
46. - LOCATION ：
47.         指定表在HDFS上的存储位置。
49. - LIKE
50.         允许用户复制现有的表结构，但是不复制数据。

复制代码

建表1：创建内部表(全部使用默认建表方式)

   当创建好表的时间，HDFS会在当前表所属的库中创建一个文件夹
  当设置表路径的时间，假如直接指向一个已有的路径,可以直接去使用文件夹中的数据
  当load数据的时间，就会将数据文件移动到到表对应的文件夹中
  而且数据一旦被load，就不能被修改
  我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS
  当我们删除表的时间，表对应的文件夹会被删除，同时数据也会被删除
  默认建表的类型就是内部表

1. #默认情况下使用的是default数据库
2. #可以在切换到其它数据库：use bigdata30 该数据库在hdfs的具体位置/user/hive/warehouse/bigdata30.db
3. #通过下面的语句创建students表后会在hdfs上生成一个students文件夹，文件夹里面是用来存储将来插入该表的students.txt数据（也可以是其他类型的文件）
4. create table students
5. (
6.     id bigint,
7.     name string,
8.     age int,
9.     gender string,
10.     clazz string
11. )
12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '_'; // 必选，指定列分隔符
14. //使用load加载数据：
15. load data inpath '/data/students.txt' into table students;
16. 加载数据的作用就是将hdfs上的/data/students.txt的数据移动到/user/hive/warehouse/bigdata30.db/students文件夹下
18. //使用上传文件的方式加载数据
19. hadoop fs -put students.txt /user/hive/warehouse/bigdata30.db/students
21. #在Hive中创建表时指定列分隔符是为了解析数据文件中的列。当Hive读取数据文件时，它会使用指定的列分隔符来识别和分隔每一列的数据。
23. 表的路径为： /user/hive/warehouse/bigdata30.db/students
25. 删除文件后数据和文件夹同时删除

复制代码

建表2：创建外部表

   ​ 外部表因为是指定其他的hdfs路径的数据加载到表中来，以是hive会认为自己不完全独占这份数据
  ​ 删除hive表的时间，数据仍然保存在hdfs中，不会删除。

1. // 外部表(一般情况下，建一个与表名相同的文件夹，然后再将数据上传到该文件夹下，在创建表的时候指定location的路径为数据的存储路径即可)
2. create external table students
3. (
4.     id bigint,
5.     name string,
6.     age int,
7.     gender string,
8.     clazz string
9. )
10. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
11. location '/data';
13. //当指定的路径下有数据时，直接将数据加载到表中
14. 表的存储路径hdfs://master:9000/data
16. //当指定的路径下没有数据时，通过hadoop命令将数据上传到location指定的文件夹，数据自动加载到表中
17. hadoop fs -put students.txt /data
19. //也可以使用hive命令行中通过命令将本地文件导入到hdfs上
20. hive> dfs -put /usr/local/soft/data/students.txt /data;

复制代码

建表3：指定存储格式

1. create table IF NOT EXISTS students
2. (
3.     id bigint,
4.     name string,
5.     age int,
6.     gender string,
7.     clazz string
8. )
9. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
10. STORED AS ORC
11. // 指定储存格式为orcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，
12. 注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表插入数据的方式。
13. load data inpath '/data/students.txt' into table students;(不可用)
14. insert into table students_test1 select * from students limit 10;(可用)

复制代码

建表4：使用查询语句建表 (这种方式比力常用)

1. create table students1 as select * from students;

复制代码

建表5：只想建表，不需要加载数据

1. create table students5 like students;

复制代码

  简单用户信息表创建：

1. create table students6(
2. id int,
3. uname string,
4. pwd string,
5. gender string,
6. age int
7. )
8. row format delimited fields terminated by ','
9. lines terminated by '\n';
11. 在Hive中，建表语句中的lines terminated by '\n'是可选的，并不是必须的。这个语句用于指定在加载数据时每行数据的结束符号，默认情况下，Hive会将每行数据以换行符 \n 作为结束标志。如果你的数据文件每行以换行符结束，你可以不用显式地指定这个参数，Hive会自动识别。但如果你的数据文件行结尾使用了其他分隔符，你就需要使用这个参数来告诉Hive如何正确地解析每行数据。

复制代码

1. 1,admin,123456,nan,18
2. 2,zhangsan,abc123,nan,23
3. 3,lisi,654321,nv,16

复制代码

复杂人员信息表创建：

1. create table IF NOT EXISTS t_person(
2. name string,
3. friends array<string>,
4. children map<string,int>,
5. address struct<street:string ,city:string>
6. )
7. row format delimited fields terminated by ',' -- 列与列之间的分隔符
8. collection items terminated by '_' -- 集合数据类型元素与元素之间分隔符
9. map keys terminated by ':' -- Map数据类型键与值之间的分隔符
10. lines terminated by '\n';  -- 行与行之间的换行符

复制代码

1. songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,beng bu_anhui
2. yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,he fei_anhui

复制代码

4.3.2、加载数据

1、使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'

1. hadoop fs -put /uer/local/soft/bigdata30/students.txt /data/students

复制代码

2、使用 load data

   注意：默认环境下加载的新数据会被追加到已有表的末尾，而不会覆盖或者清空已存在的数据

1. load data inpath '/data/students.txt' into table students;
3. // 清空表
4. truncate table students;
5. // 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
6. load data local inpath '/usr/local/soft/data/students.txt' into table students;
7. // overwrite 覆盖加载
8. load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

复制代码

3、create table xxx as SQL语句

4、insert into table xxxx SQL语句 （没有as）

1. // 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失
2. insert into table students1 select * from students;
4. // 覆盖插入 把into 换成 overwrite
5. insert overwrite table students1 select * from students;

复制代码

4.3.3、对表进行修改

   表现表

1. //显示当前数据库下的所有表
2. show tables;
3. //显示当前数据库下以u开头的所有表
4. show tables like 'u*';
5. //查看表的结构（表的字段和字段类型）
6. desc t_person;
7. //查看表的详细结构，包括表的存储位置，表的存储类型，和表的一些其他的属性
8. desc formatted students;

复制代码

  添加列

1. alter table students2 add columns (education string);

复制代码

  更新列

1. alter table stduents2 change education educationnew string;
3. 这条语句将会更改 students2 表中名为 education 的列的数据类型为 STRING，同时将其列名更改为 educationnew。

复制代码

4.3.4、工作案例

   一样平常在公司中，使用外部表多一点，因为数据可以需要被多个步伐使用，避免误删，通常外部表会联合location一起使用
  外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch…
  计划外部表的初志就是 让 表的元数据 与 数据 解耦
  第一步：在hdfs上创建表数据存储的文件夹

1. hdfs dfs -mkdir -p /bigdata30/dept
2. hdfs dfs -mkdir -p /bigdata30/emp
3. hdfs dfs -mkdir -p /bigdata30/salgrade

复制代码

第二步：将数据上传到hdfs上

1. hadoop fs -put /uer/local/soft/bigdata30/dept.txt /bigadta30/dept
2. hadoop fs -put /uer/local/soft/bigdata30/dept.txt /bigadta30/emp
3. hadoop fs -put /uer/local/soft/bigdata30/dept.txt /bigadta30/salgrade

复制代码

第三步：创建表

   创建dept表

1. CREATE EXTERNAL TABLE IF NOT EXISTS dept (
2.   DEPTNO int,
3.   DNAME varchar(255),
4.   LOC varchar(255)
5. ) row format delimited fields terminated by ','
6. location '/bigdata30/dept';
8. 10,ACCOUNTING,NEW YORK
9. 20,RESEARCH,DALLAS
10. 30,SALES,CHICAGO
11. 40,OPERATIONS,BOSTON

复制代码

  创建emp表

1. CREATE EXTERNAL TABLE IF NOT EXISTS emp (
2.    EMPNO int,
3.    ENAME varchar(255),
4.    JOB varchar(255),
5.    MGR int,
6.    HIREDATE date,
7.    SAL decimal(10,0),
8.    COMM decimal(10,0),
9.    DEPTNO int
10. ) row format delimited fields terminated by ','
11. location '/bigdata30/emp';
13. 7369,SMITH,CLERK,7902,1980-12-17,800,null,20
14. 7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
15. 7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
16. 7566,JONES,MANAGER,7839,1981-04-02,2975,null,20
17. 7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
18. 7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30
19. 7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10
20. 7788,SCOTT,ANALYST,7566,1987-07-13,3000,null,20
21. 7839,KING,PRESIDENT,null,1981-11-17,5000,null,10
22. 7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
23. 7876,ADAMS,CLERK,7788,1987-07-13,1100,null,20
24. 7900,JAMES,CLERK,7698,1981-12-03,950,null,30
25. 7902,FORD,ANALYST,7566,1981-12-03,3000,null,20
26. 7934,MILLER,CLERK,7782,1982-01-23,1300,null,10

复制代码

  创建salgrade表

1. CREATE EXTERNAL TABLE IF NOT EXISTS salgrade (
2.   GRADE int,
3.   LOSAL int,
4.   HISAL int
5. ) row format delimited fields terminated by ','
6. location '/bigdata30/salgrade';
8. 1,700,1200
9. 2,1201,1400
10. 3,1401,2000
11. 4,2001,3000
12. 5,3001,9999

复制代码

4.3.5、Hive导出数据

   将表中的数据备份
  

• 将查询结果存放到本地
  

1. //创建存放数据的目录
2. mkdir -p /usr/local/soft/shujia
4. //导出查询结果的数据(导出到Node01上)
5. insert overwrite local directory '/usr/local/soft/shujia' select * from t_person;

复制代码

• 按照指定的方式将数据输出到本地
  

1. -- 创建存放数据的目录
2. mkdir -p /usr/local/soft/shujia
4. -- 导出查询结果的数据
5. insert overwrite local directory '/usr/local/soft/shujia'
6. ROW FORMAT DELIMITED fields terminated by ','
7. collection items terminated by '-'
8. map keys terminated by ':'
9. lines terminated by '\n'
10. select * from t_person;

复制代码

• 将查询结果输出到HDFS
  

1. -- 导出查询结果的数据
2. insert overwrite local directory '/usr/local/soft/shujia'
3. ROW FORMAT DELIMITED fields terminated by ','
4. collection items terminated by '-'
5. map keys terminated by ':'
6. lines terminated by '\n'
7. select * from t_person;-- 创建存放数据的目录
8. hdfs dfs -mkdir -p /shujia/bigdata30/copy
10. -- 导出查询结果的数据
11. insert overwrite directory '/data/' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select clazz,count(1) from students group by clazz

复制代码

• 直接使用HDFS命令保存表对应的文件夹
  

1. // 创建存放数据的目录
2. hdfs dfs -mkdir -p /bigdata30/data
4. // 使用HDFS命令拷贝文件到其他目录
5. hdfs dfs -cp /hive/warehouse/t_person/*  /bigdata30/data

复制代码

• 将表布局和数据同时备份
  ​ 将数据导出到HDFS
  
  1. //创建存放数据的目录
  2. hdfs dfs -mkdir -p /bigdata30/copy
  4. //导出查询结果的数据
  5. export table t_person to '/bigdata30/copy';

  复制代码
  ​ 删除表布局
  
  1. drop table t_person;

  复制代码
  ​ 恢复表布局和数据
  
  1. // 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
  2. load data local inpath '/usr/local/soft/data/students.txt' into table students;
  3. // overwrite 覆盖加载
  4. load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

  复制代码
        注意：时间不同步，会导致导入导出失败
  

Hive分区与排序（内置函数）

1、Hive分区(十分重要！！)

   分区的目标：避免全表扫描，加快查询速度！
    在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割分别成一个个的小的文件，如许每次操纵一个个小的文件就会很容易了，同样的原理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照天天或者每小时切分成一个个小的文件，如许去操纵小的文件就会容易很多了。
  假如现在我们公司一天产生3亿的数据量，那么为了方便管理和查询，就做以下的事变。
  ​ 1）建立分区（可按照日期，部门等等具体业务分区）
  ​ 2）分门别类的管理
  1.1 静态分区（SP）

   静态分区（SP）static partition–partition by (字段 类型)
  ​ 借助于物理的文件夹分区，实现快速检索的目标。
  ​ 一样平常对于查询比力频繁的列设置为分区列。
  ​ 加载数据的时间直接把对应分区中所有数据放到对应的文件夹中。
    创建单分区表语法：

1. CREATE TABLE IF NOT EXISTS t_student (
2. sno int,
3. sname string
4. ) partitioned by(grade int)
5. row format delimited fields terminated by ',';
6. --  分区的字段不要和表的字段相同。相同会报错error10035
9. 1,xiaohu01,1
10. 2,xiaohu02,1
11. 3,xiaohu03,1
12. 4,xiaohu04,1
13. 5,xiaohu05,1
15. 6,xiaohu06,2
16. 7,xiaohu07,2
17. 8,xiaohu08,2
19. 9,xiaohu09,3
20. 10,xiaohu10,3
21. 11,xiaohu11,3
22. 12,xiaohu12,3
23. 13,xiaohu13,3
24. 14,xiaohu14,3
25. 15,xiaohu15,3
27. 16,xiaohu16,4
28. 17,xiaohu17,4
29. 18,xiaohu18,4
30. 19,xiaohu19,4
31. 20,xiaohu20,4
32. 21,xiaohu21,4
34. 22,xiaohu16,5
35. 23,xiaohu17,4
36. 24,xiaohu18,5
37. 25,xiaohu19,5
38. 26,xiaohu20,5
39. 27,xiaohu21,5
41. -- 载入数据
42. -- 将相应年级的数据导入对应分区中(对应的是分区文件夹)
43. --导入一年级的数据
44. load data local inpath '/usr/local/soft/bigdata30/grade1.txt' into table t_student partition(grade=1);
45. --导入二年级的数据
46. load data local inpath '/usr/local/soft/bigdata30/grade2.txt' into table t_student partition(grade=2);
48. -- 演示多拷贝一行上传，分区的列的值是分区的值，不是原来的值

复制代码

  静态多分区表语法：

1. CREATE TABLE IF NOT EXISTS t_teacher (
2. tno int,
3. tname string
4. ) partitioned by(grade int,clazz int)
5. row format delimited fields terminated by ',';
7. --注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。
8. 1,xiaoge01,1,1
9. 2,xiaoge02,1,1
11. 3,xiaoge03,1,2
12. 4,xiaoge04,1,2
14. 5,xiaoge05,1,3
15. 6,xiaoge06,1,3
17. 7,xiaoge07,2,1
18. 8,xiaoge08,2,1
20. 9,xiaoge09,2,2
22. --载入数据
23. load data local inpath '/usr/local/soft/bigdata30/teacher_1.txt' into table t_teacher partition(grade=1,clazz=1);
24. load data local inpath '/usr/local/soft/bigdata30/teacher_2.txt' into table t_teacher partition(grade=1,clazz=2);

复制代码

  分区表查询

1. // 全表扫描，不推荐，效率低
2. select count(*) from students_pt1;
3. //查询分区表的数据
4. select * from t_student where grade = 1;
5. // 使用where条件进行分区裁剪，避免了全表扫描，效率高
6. select count(*) from students_pt1 where grade = 1;
7. // 也可以在where条件中使用非等值判断
8. select count(*) from students_pt1 where grade<3 and grade>=1;

复制代码

  检察分区

1. show partitions t_teacher;

复制代码

  添加分区

1. alter table t_student add partition (grade=6);
3. alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/bigdata29.db/t_teacher/grade=3/clazz=1';
5. location:表示hdfs上的分区路径（新添加在该表下的分区路径grade=3/clazz=1必须提前创建好）

复制代码

  删除分区

1. alter table t_student drop partition (grade=5);

复制代码

1.3 动态分区（DP）

• 动态分区（DP）dynamic partition
  
• 静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
  
• 详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL实行时才华决定。
  

   开启动态分区首先要在hive会话中设置如下的参数

1. # 表示开启动态分区
2. hive> set hive.exec.dynamic.partition=true;
4. # 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
5. # strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
6. hive> set hive.exec.dynamic.partition.mode=nonstrict;
8. ===================以下是可选参数======================
10. # 表示支持的最大的分区数量为1000，可以根据业务自己调整
11. hive> set hive.exec.max.dynamic.partitions.pernode=1000;

复制代码

  其余的参数详细配置如下

1. 设置为true表示开启动态分区的功能（默认为false）
2. --hive.exec.dynamic.partition=true;
4. 设置为nonstrict，表示允许所有分区都是动态的（默认为strict）
5. -- hive.exec.dynamic.partition.mode=nonstrict;
6. -- hive.exec.dynamic.partition.mode=strict;
8. 每个mapper或reducer可以创建的最大动态分区个数(默认为100)
9. 比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错
10. --hive.exec.max.dynamic.partition.pernode=100;
12. 一个动态分区创建可以创建的最大动态分区个数（默认值1000）
13. --hive.exec.max.dynamic.partitions=1000;
15. 全局可以创建的最大文件个数（默认值100000）
16. --hive.exec.max.created.files=100000;
18. 当有空分区产生时，是否抛出异常（默认false）
19. -- hive.error.on.empty.partition=false;  

复制代码

• 案例1： 动态插入学生年级班级信息
  

1. --创建外部表
2. CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
3. sno int,
4. sname string,
5. grade int,
6. clazz int
7. )
8. row format delimited fields terminated by ','
9. location "/bigdata30/teachers";
11. --创建分区表
12. CREATE TABLE IF NOT EXISTS t_student_d (
13. sno int,
14. sname string
15. ) partitioned by (grade int,clazz int)
16. row format delimited fields terminated by ',';

复制代码

1. 数据：
3. 1,xiaohu01,1,1
4. 2,xiaohu02,1,1
5. 3,xiaohu03,1,1
6. 4,xiaohu04,1,2
7. 5,xiaohu05,1,2
8. 6,xiaohu06,2,3
9. 7,xiaohu07,2,3
10. 8,xiaohu08,2,3
11. 9,xiaohu09,3,3
12. 10,xiaohu10,3,3
13. 11,xiaohu11,3,3
14. 12,xiaohu12,3,4
15. 13,xiaohu13,3,4
16. 14,xiaohu14,3,4
17. 15,xiaohu15,3,4
18. 16,xiaohu16,4,4
19. 17,xiaohu17,4,4
20. 18,xiaohu18,4,5
21. 19,xiaohu19,4,5
22. 20,xiaohu20,4,5
23. 21,xiaohu21,4,5

复制代码

  假如静态分区的话，我们插入数据必须指定分区的值。
  假如想要插入多个班级的数据，我要写很多SQL而且实行24次很麻烦。
  而且静态分区有可能会产生数据错误问题

1. -- 会报错
2. insert overwrite table t_student_d partition (grade=1,clazz=1) select * from t_student_e;

复制代码

  假如使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。

1. insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;

复制代码

  长处：不用手动指定了，自动会对数据进行分区
  缺点：可能会出现数据倾斜
  2、Hive分桶

2.1 业务场景

   数据分桶的实用场景：

  ​ 分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定符合巨细的分区分别方式
​ 不合理的数据分区分别方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬环境
​ 分桶是将数据集分解为更容易管理的多少部门的另一种技术。
​ 分桶就是将数据按照字段进行分别，可以将数据按照字段分别到多个文件当中去。(都各不相同)
  2.2 数据分桶原理

• Hive接纳对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
  
  
  
  • bucket num = hash_function(bucketing_column) mod num_buckets ( hash(name)%n == x )
    
  • 列的值做哈希取余 决定命据应该存储到哪个桶
    
  
  

2.3 数据分桶优势

   方便抽样
  ​ 使取样（sampling）更高效。在处置惩罚大规模数据集时，在开发和修改查询的阶段，假如能在数据集的一小部门数据上试运行查询，会带来很多方便
  进步join查询服从
  ​ 得到更高的查询处置惩罚服从。桶为表加上了额外的布局，Hive 在处置惩罚有些查询时能利用这个布局。具体而言，连接两个在（包罗连接列的）相同列上分别了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操纵。对于JOIN操纵两个表有一个相同的列，假如对这两个表都进行了桶操纵。那么将保存相同列值的桶进行JOIN操纵就可以，可以大大较少JOIN的数据量。
  2.4 分桶实战

   ​ 首先，分区和分桶是两个不同的概念，很多资料上说需要先分区在分桶，实在不然，分区是对数据进行分别，而分桶是对文件进行分别。
  ​ 当我们的分区之后，末了的文件还是很大怎么办，就引入了分桶的概念。
  将这个比力大的文件再分成多少个小文件进行存储，我们再去查询的时间，在这个小范围的文件中查询就会快很多。
  ​ 对于hive中的每一张表、分区都可以进一步的进行分桶。
  ​ 当然，分桶不是说将文件随机进行切分存储，而是有规律的进行存储。在看完下面的例子后进行表明，现在干巴巴的表明也不太好理解。它是由列的哈希值除以桶的个数来决定每条数据分别在哪个桶中。
  创建顺序和分区一样，创建的方式不一样。
    首先我们需要开启分桶的支持

1. （依然十分重要，不然无法进行分桶操作！！！！）
2. set hive.enforce.bucketing=true;

复制代码

  数据预备（id,name,age）

1. 1,tom,11
2. 2,cat,22
3. 3,dog,33
4. 4,hive,44
5. 5,hbase,55
6. 6,mr,66
7. 7,alice,77
8. 8,scala,88

复制代码

  创建一个普通的表

1. create table person
2. (
3. id int,
4. name string,
5. age int
6. )
7. row format delimited
8. fields terminated by ',';

复制代码

  将数据load到这张表中

1. load data local inpath '文件在Linux上的绝对路径' into table person ;

复制代码

  创建分桶表

1. create table psn_bucket
2. (
3. id int,
4. name string,
5. age int
6. )
7. clustered by(age) into 4 buckets
8. row format delimited fields terminated by ',';

复制代码

  将数据insert到表psn_bucket中
  (注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要)

1. insert into psn_bucket select * from person;

复制代码

  在HDFS上检察数据
  

   查询数据
  我们在linux中使用Hadoop的命令检察一下（与我们料想的顺序一致）

1. hadoop fs -cat /user/hive/warehouse/bigdata30.db/psn_bucket/*

复制代码

  这里设置的桶的个数是4 数据按照 年龄%4 进行放桶(文件)
11%4 == 3 -----> 000003_0
22%4 == 2 -----> 000002_0
33%4 == 1 -----> 000001_0
44%4 == 0 -----> 000000_0
…以此类推
  3、Hive JDBC

启动hiveserver2

1. nohup hiveserver2 &
2. 或者
3. hiveserver2 &

复制代码

新建maven项目并添加两个依赖

1.     <dependency>
2.         <groupId>org.apache.hadoop</groupId>
3.         <artifactId>hadoop-common</artifactId>
4.         <version>2.7.6</version>
5.     </dependency>
6.     <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
7.     <dependency>
8.         <groupId>org.apache.hive</groupId>
9.         <artifactId>hive-jdbc</artifactId>
10.         <version>1.2.1</version>
11.     </dependency>

复制代码

编写JDBC代码

1. import java.sql.*;
3. public class HiveJDBC {
4.     public static void main(String[] args) throws ClassNotFoundException, SQLException {
5.         Class.forName("org.apache.hive.jdbc.HiveDriver");
6.         Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata29");
7.         Statement stat = conn.createStatement();
8.         ResultSet rs = stat.executeQuery("select * from students limit 10");
9.         while (rs.next()) {
10.             int id = rs.getInt(1);
11.             String name = rs.getString(2);
12.             int age = rs.getInt(3);
13.             String gender = rs.getString(4);
14.             String clazz = rs.getString(5);
15.             System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
16.         }
17.         rs.close();
18.         stat.close();
19.         conn.close();
20.     }
21. }

复制代码

4、Hive的4种排序

4.1 全局排序

• order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间
  
• 使用 order by子句排序 :ASC（ascend）升序（默认）| DESC（descend）降序
  
• order by放在select语句的末端
  

1. select * from 表名 order by 字段名1[，别名2...];

复制代码

4.2 局部排序(对reduce内部做排序)

• sort by 不是全局排序,其在数据进入reducer前完成排序。
  
• 假如用sort by进行排序，而且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。asc,desc
  
• 设置reduce个数
  

1. set mapreduce.job.reduces=3;

复制代码

• 检察reduce个数
  

1. set mapreduce.job.reduces;

复制代码

• 排序
  

1. select * from 表名 sort by 字段名[,字段名...];

复制代码

4.3 分区排序(本身没有排序)

   distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
  雷同MR中partition,进行分区，联合sort by使用。（注意：distribute by 要在sort by之前）
  对于distrbute by 进行测试，一定要多分配reduce进行处置惩罚，否则无法看到distribute by的结果。
  设置reduce个数

1. set mapreduce.job.reduce=7;

复制代码

• 排序
  

1. select * from 表名 distribute by 字段名[,字段名...] sort by 字段;

复制代码

4.3 分区并排序

• cluster by（字段）除了具有Distribute by的功能外，还会对该字段进行排序 asc desc
  
• cluster by = distribute by + sort by 只能默认升序，不能使用倒序
  

1. select * from 表名 cluster by 字段名[,字段名...];
2. select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];

复制代码

5、Hive内置函数

1. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

复制代码

1. -- 1.查看系统自带函数
2. show functions;
3. -- 2.显示自带的函数的用法
4. desc function xxxx;
5. -- 3.详细显示自带的函数的用法
6. desc function extended upper;

复制代码

5.1 内置函数分类

1. 关系操作符：包括 = 、 <> 、 <= 、>=等
3. 算数操作符：包括 + 、 - 、 *、／等
5. 逻辑操作符：包括AND 、 && 、 OR 、 || 等
7. 复杂类型构造函数：包括map、struct、create_union等
9. 复杂类型操作符：包括A[n]、Map[key]、S.x
11. 数学操作符：包括ln(double a)、sqrt(double a)等
13. 集合操作符：包括size(Array)、sort_array(Array)等
15. 类型转换函数： binary(string|binary)、cast(expr as )
17. 日期函数：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等
19. 条件函数：包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等
21. 字符串函数：包括acat(string|binary A, string|binary B…)等
23. 其他：xpath、get_json_objectscii(string str)、con

复制代码

5.2 UDTF hive中特殊的一个功能（进一出多）

1. -- UDF 进一出一
4. -- UDAF 进多出一
5. -- collect_set()和collect_list()将多行数据转成一行，区别就是list里面可重复而set里面是去重的
6. -- concat_ws(':',collect_set(type))   ':' 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
7. select 字段名,concat_ws(':',collect_set(列名)) as 别名 from 表名 group by id;
9. -- UDTF 进一出多
10. -- explode  可以将一行数据变成多行数据
11. select  explode(split(列名,"数据的分隔符")) from 表名;
12. -- lateral view 表生成函数，可以将explode的数据生成一个列表
13. select id,name,列名 from 表1,lateral view explode(split(表1.列名,"数据的分隔符"))新列名 as 别列名;
15. --注意：collect_set() 和 collect_list() 是 Hive 中用于聚合操作的集合函数，它们分别用于将列值聚合成集合或列表。

复制代码

1. -- 创建数据库表
2. create table t_movie1(
3. id int,
4. name string,
5. types string
6. )
7. row format delimited fields terminated by ','
8. lines terminated by '\n';
10. -- 电影数据  movie1.txt
11. -- 加载数据到数据库 load data inpath '/shujia/movie1.txt' into table t_movie1;
12. 1,这个杀手不太冷,剧情-动作-犯罪
13. 2,七武士,动作-冒险-剧情
14. 3,勇敢的心,动作-传记-剧情-历史-战争
15. 4,东邪西毒,剧情-动作-爱情-武侠-古装
16. 5,霍比特人,动作-奇幻-冒险
18. -- explode  可以将一组数组的数据变成一列表
19. select  explode(split(types,"-")) from t_movie1;
21. -- lateral view 表生成函数，可以将explode的数据生成一个列表
22. select id,name,type from t_movie1 lateral view explode(split(types,"-")) typetable as type;

复制代码

1. -- 创建数据库表
2. create table t_movie2(
3. id int,
4. name string,
5. type string
6. )
7. row format delimited fields terminated by ','
8. lines terminated by '\n';
10. -- 电影数据 movie2.txt
11. -- 加载数据到数据库 load data inpath '/shujia/movie2.txt' into table t_movie2;                                    1,这个杀手不太冷,剧情
12. 1,这个杀手不太冷,动作
13. 1,这个杀手不太冷,犯罪
14. 2,七武士,动作
15. 2,七武士,冒险
16. 2,七武士,剧情
17. 3,勇敢的心,动作
18. 3,勇敢的心,传记
19. 3,勇敢的心,剧情
20. 3,勇敢的心,历史
21. 3,勇敢的心,战争
22. 4,东邪西毒,剧情
23. 4,东邪西毒,动作
24. 4,东邪西毒,爱情
25. 4,东邪西毒,武侠
26. 4,东邪西毒,古装
27. 5,霍比特人,动作
28. 5,霍比特人,奇幻
29. 5,霍比特人,冒险
31. -- collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去重的
32. -- concat_ws(':',collect_set(type))   ':' 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
33. select id,concat_ws(':',collect_set(type)) as types from t_movie2 group by id;

复制代码

5.3 WordCount案例

   数据预备

1. hello,world
2. hello,bigdata
3. like,life
4. bigdata,good

复制代码

  建表

1. create table wc2
2. (
3. line string
4. )
5. row format delimited fields terminated by '\n'

复制代码

  导入数据

1. load data local inpath '/usr/local/soft/data/wc1.txt' into table wc;

复制代码

  步骤1：先对一行数据进行切分

1. select split(line,',') from wc;

复制代码

  步骤2：将行转列

1. select explode(split(line,',')) from wc;

复制代码

  步骤3：将相同的进行分组统计

1. select w.word,count(*) from (select explode(split(line,',')) as word from wc) w group by w.word;

复制代码

Hive函数学习

1、count(*)、count(1) 、count(‘字段名’) 区别

   从实行结果来看
  

• count(*)包括了所有的列，相称于行数，在统计结果的时间，不会忽略列值为NULL 最慢的
  
• count(1)忽略所有列，只计算行的数量，在统计结果的时间，不会忽略列值为NULL 最快的
  
• count(列名)只包括列名那一列，在统计结果的时间，会忽略列值为空（这里的空不是只空字符串或者0，而是表现null）的计数，即某个字段值为NULL时，不统计 仅次于count(1)
  

  从实行服从来看
  

• 假如列为主键，count(列名)服从优于count(1)
  
• 假如列不为主键，count(1)服从优于count(列名)
  
• 假如表中存在主键，count(主键列名)服从最优
  
• 假如表中只有一列，则count(*)服从最优
  
• 假如表有多列，且不存在主键，则count(1)服从优于count(*)
  

  在工作中假如没有特殊的要求，就使用count(1)来进行计数。
  2、hive语句的实行顺序

1.from
2.join on 或 lateral view explode(需炸裂的列) tbl as 炸裂后的列名
3.where
4.group by
5.聚合函数 如Sum() avg() count(1)等
6.having 在此开始可以使用select中的别名
7.select 若包罗over（）开窗函数，此时select中的内容作为窗口函数的输入，窗口中所选的数据范围也是在group by，having之后，并不是针对where后的数据进行开窗，这点要注意。需要注意开窗函数的实行顺序及时间点。
8.distinct
9.order by
10.limit（发起：以后在大数据环境中，一张表的数据量肯定十分庞大的，养成加limit的风俗）
   where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists( hive3.x版本是支持的 )

1. # 查询薪资大于SCOTT的薪资员工信息
3. -- 列出与“SCOTT”从事相同工作的所有员工。
4. select  t1.EMPNO
5.         ,t1.ENAME
6.         ,t1.JOB
7. from emp t1
8. where t1.ENAME != "SCOTT" and t1.job in(
9.     select  job
10.     from emp
11.     where ENAME = "SCOTT");
12.    
13. 7900,JAMES,CLERK,7698,1981-12-03,950,null,30
14. 7902,FORD,ANALYST,7566,1981-12-03,3000,null,20
16. select  t1.EMPNO
17.         ,t1.ENAME
18.         ,t1.JOB
19. from emp t1
20. where t1.ENAME != "SCOTT" and exists(
21.     select  job
22.     from emp t2
23.     where ENAME = "SCOTT"
24.     and t1.job = t2.job
25. );

复制代码

4、hive中巨细写不敏感（列名无所谓巨细写）
5、在hive中，数据中假如有null字符串，加载到表中的时间会变成 null （不是字符串）
   假如需要判断 null，使用 某个字段名 is null 如许的方式来判断
  或者使用 nvl() 函数，不能 直接 某个字段名 == null
  6、使用explain检察SQL实行计划
   口试题：hive中一条sql语句如何剖析成MapReduce作业实行的？（hive的版本）

1. explain select  t1.EMPNO
2.         ,t1.ENAME
3.         ,t1.JOB
4. from emp t1
5. where t1.ENAME != "SCOTT" and t1.job in(
6.     select  job
7.     from emp
8.     where ENAME = "SCOTT");
9.    
10. # 查看更加详细的执行计划，加上extended
11. explain extended select  t1.EMPNO
12.         ,t1.ENAME
13.         ,t1.JOB
14. from emp t1
15. where t1.ENAME != "SCOTT" and t1.job in(
16.     select  job
17.     from emp
18.     where ENAME = "SCOTT");

复制代码

3、Hive 常用函数

3.1、关系运算

1. // 等值比较 = == < = >
2. // 不等值比较 != <>
3. // 区间比较： select * from default.students where id between 1500100001 and 1500100010;
4. // 空值/非空值判断：isnull、isnotnull、nvl()、isnull()
5. // like、rlike、regexp用法

复制代码

3.2、数值计算

1. 取整函数(四舍五入)：round
2. 向上取整：ceil
3. 向下取整：floor

复制代码

3.3、条件函数(主要使用场景是数据洗濯的过程中使用，有些构建表的过程也是需要的)

• if： if(表达式,假如表达式建立的返回值,假如表达式不建立的返回值) （重点）
  
• 条件表达式?表达式1:表达式2;
  

1. create table sc(
2. sno string,
3. cno string,
4. score bigint
5. )row format delimited fields terminated by '\n';
6. select sal,if(sal<2000,'低薪',if(sal>=2000 and sal<3000,'中等','高薪')) as level from emp;
8. select if(1>0,1,0);
9. select if(1>0,if(-1>0,-1,1),0);
10. select score,if(score>120,'优秀',if(score>100,'良好',if(score>90,'及格','不及格'))) as pingfen from sc;

复制代码

• COALESCE
  

1. select COALESCE(null,'1','2'); // 1 从左往右 依次匹配 直到非空为止
2. select COALESCE('1',null,'2'); // 1

复制代码

• case when（重点）
  

1. select sal,case when sal<2000 then '低薪'
2.                 when sal>=2000 and sal<3000 then '中等薪资'
3.                 else '高薪' end as level
4. from emp;
6. select  score
7.         ,case when score>90 then '优秀'
8.               when score>80 then '良好'
9.               when score>=60 then '及格'
10.         else '不及格'
11.         end as pingfen
12. from sc;
14. select  name
15.         ,case name when "施笑槐" then "槐ge"
16.                   when "吕金鹏" then "鹏ge"
17.                   when "单乐蕊" then "蕊jie"
18.         else "算了不叫了"
19.         end as nickname
20. from students limit 10;

复制代码

  注意条件的顺序
  3.4、日期函数重点！！！

1. select from_unixtime(1717666208,'YYYY年MM月dd日 hh时mm分ss秒');
3. select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');
6. // '2021年01月14日' -> '2021-01-14'
7. select from_unixtime(unix_timestamp('2024年06月06日','yyyy年MM月dd日'),'yyyy-MM-dd');
8. // "04牛2021数加16强" -> "2021/04/16"
9. select from_unixtime(unix_timestamp("04牛2024数加11强","MM牛yyyy数加dd强"),"yyyy年MM月dd日");

复制代码

3.5、字符串函数

1. concat('123','456'); // 123456
2. concat('123','456',null); // NULL
4. select concat_ws('#','a','b','c'); // a#b#c
5. select concat_ws('#','a','b','c',NULL); // a#b#c 可以指定分隔符，并且会自动忽略NULL
6. select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;
8. select substring("abcdefg",1); // abcdefg HQL中涉及到位置的时候 是从1开始计数
9. // '2021/01/14' -> '2021-01-14'
10. select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));
11. // 建议使用日期函数去做日期
12. select from_unixtime(unix_timestamp('2021/01/14','yyyy/MM/dd'),'yyyy-MM-dd');
14. select split("abcde,fgh",","); // ["abcde","fgh"]
15. select split("a,b,c,d,e,f",",")[2]; // c 数组的下标依旧是从0开始
17. select explode(split("abcde,fgh",",")); // abcde
18.                                                                                 //  fgh
20. // 解析json格式的数据
21. select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); // 60
23. {
24.         "name": "zhangsan",
25.         "age": 18,
26.         "score": [{
27.                 "course_name": "math",
28.                 "score": 100
29.         }, {
30.                 "course_name": "english",
31.                 "score": 60
32.         }]
33. }
34. $.score[0].score

复制代码

3.6、例题：Hive 中的wordCount

1. create table words(
2.     words string
3. )row format delimited fields terminated by '\n';
5. // 数据
6. hello,java,hello,java,scala,python
7. hbase,hadoop,hadoop,hdfs,hive,hive
8. hbase,hadoop,hadoop,hdfs,hive,hive
10. select word,count(*) from (select explode(split(words,',')) word from words) a group by a.word;
12. // 结果
13. hadoop        4
14. hbase        2
15. hdfs        2
16. hello        2
17. hive        4
18. java        2
19. python        1
20. scala        1

复制代码

3.7、Hive窗口函数

   普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。
简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
开窗函数一样平常就是说的是over()函数，其窗口是由一个 OVER 子句 定义的多行记录
开窗函数一样平常分为两类,聚合开窗函数和排序开窗函数。

1. -- 聚合格式
2. select sum(字段名) over([partition by 字段名] [ order by 字段名]) as 别名,
3.         max(字段名) over() as 别名
4. from 表名;
6. -- 排序窗口格式
7. select rank() over([partition by 字段名] [ order by 字段名]) as 别名 from 表名;

复制代码

注意点：

• over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求联合使用
  
• over()函数中假如不指定分区，窗口巨细是针对查询产生的所有数据，假如指定了分区，窗口巨细是针对每个分区的数据
  

   测试数据

1. -- 创建表
2. create table t_fraction(
3. name string,
4. subject string,
5. score int)
6. row format delimited fields terminated by ","
7. lines terminated by '\n';
9. -- 测试数据 fraction.txt
10. 孙悟空,语文,10
11. 孙悟空,数学,73
12. 孙悟空,英语,15
13. 猪八戒,语文,10
14. 猪八戒,数学,73
15. 猪八戒,英语,11
16. 沙悟净,语文,22
17. 沙悟净,数学,70
18. 沙悟净,英语,31
19. 唐玄奘,语文,21
20. 唐玄奘,数学,81
21. 唐玄奘,英语,23
23. -- 上传数据
24. load data local inpath '/usr/local/soft/bigdata17/xiaohu/data/fraction.txt' into table t_fraction;

复制代码

3.7.1、 聚合开窗函数

   sum(求和)
  min(最小)
  max(最大)
  avg(平均值)
  count(计数)
  lag(获取当前行上一行的数据）
  lead(获取当前行下一行的数据）

1. --
2. select name,subject,score,sum(score) over() as sumover from t_fraction;
3. +-------+----------+--------+----------+
4. | name  | subject  | score  | sumover  |
5. +-------+----------+--------+----------+
6. | 唐玄奘   | 英语       | 23     | 321      |
7. | 唐玄奘   | 数学       | 81     | 321      |
8. | 唐玄奘   | 语文       | 21     | 321      |
9. | 沙悟净   | 英语       | 31     | 321      |
10. | 沙悟净   | 数学       | 12     | 321      |
11. | 沙悟净   | 语文       | 22     | 321      |
12. | 猪八戒   | 英语       | 11     | 321      |
13. | 猪八戒   | 数学       | 73     | 321      |
14. | 猪八戒   | 语文       | 10     | 321      |
15. | 孙悟空   | 英语       | 15     | 321      |
16. | 孙悟空   | 数学       | 12     | 321      |
17. | 孙悟空   | 语文       | 10     | 321      |
18. +-------+----------+--------+----------+
20. select name,subject,score,
21. sum(score) over() as sum1,
22. sum(score) over(partition by subject) as sum2,
23. sum(score) over(partition by subject order by score) as sum3,
25. -- 由起点到当前行的窗口聚合，和sum3一样
26. sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,
28. -- 当前行和前面一行的窗口聚合
29. sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
31. -- 当前行的前面一行到后面一行的窗口聚合  前一行+当前行+后一行
32. sum(score) over(partition by subject order by score rows between 1 preceding and 1 following) as sum6,
34. -- 当前行与后一行之和
35. sum(score) over(partition by subject order by score rows between current row and 1 following) as sum6,
37. -- 当前和后面所有的行
38. sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
39. from t_fraction;
41. rows：行
42. unbounded preceding：起点
43. unbounded following：终点
44. n preceding：前 n 行
45. n following：后 n 行
46. current row：当前行
49. +-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
50. | name  | subject  | score  | sum1  | sum2  | sum3  | sum4  | sum5  | sum6  | sum7  |
51. +-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
52. | 孙悟空   | 数学       | 12     | 359   | 185   | 12    | 12    | 12    | 31    | 185   |
53. | 沙悟净   | 数学       | 19     | 359   | 185   | 31    | 31    | 31    | 104   | 173   |
54. | 猪八戒   | 数学       | 73     | 359   | 185   | 104   | 104   | 92    | 173   | 154   |
55. | 唐玄奘   | 数学       | 81     | 359   | 185   | 185   | 185   | 154   | 154   | 81    |
56. | 猪八戒   | 英语       | 11     | 359   | 80    | 11    | 11    | 11    | 26    | 80    |
57. | 孙悟空   | 英语       | 15     | 359   | 80    | 26    | 26    | 26    | 49    | 69    |
58. | 唐玄奘   | 英语       | 23     | 359   | 80    | 49    | 49    | 38    | 69    | 54    |
59. | 沙悟净   | 英语       | 31     | 359   | 80    | 80    | 80    | 54    | 54    | 31    |
60. | 孙悟空   | 语文       | 10     | 359   | 94    | 10    | 10    | 10    | 31    | 94    |
61. | 唐玄奘   | 语文       | 21     | 359   | 94    | 31    | 31    | 31    | 53    | 84    |
62. | 沙悟净   | 语文       | 22     | 359   | 94    | 53    | 53    | 43    | 84    | 63    |
63. | 猪八戒   | 语文       | 41     | 359   | 94    | 94    | 94    | 63    | 63    | 41    |
64. +-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+

复制代码

rows必须跟在Order by 子句之后，对排序的结果进行限定，使用固定的行数来限定分区中的数据行数量。
   **OVER()：**指定分析函数工作的数据窗口巨细，这个数据窗口巨细可能会随着行的变而变化。
  **CURRENT ROW：**当前行
  **n PRECEDING：**往前n行数据
  **n FOLLOWING：**往后n行数据
  **UNBOUNDED：**起点，UNBOUNDED PRECEDING 表现从前面的起点， UNBOUNDED FOLLOWING表现到后面的终点
  **LAG(col,n,default_val)：**往前第n行数据，col是列名，n是往上的行数，当第n行为null的时间取default_val
  **LEAD(col,n, default_val)：**往后第n行数据，col是列名，n是往下的行数，当第n行为null的时间取default_val
  **NTILE(n)：**把有序分区中的行分发到指定命据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
  cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
  ​ 小于即是当前值x的行数 / 窗口或partition分区内的总行数。其中，x 即是 order by 子句中指定的列的当前行中的值。
  聚合开窗函数实战：

实战1：Hive用户购买明细数据分析

   创建表和加载数据

1. name，orderdate，cost
2. jack,2017-01-01,10
3. tony,2017-01-02,15
4. jack,2017-02-03,23
5. tony,2017-01-04,29
6. jack,2017-01-05,46
7. jack,2017-04-06,42
8. tony,2017-01-07,50
9. jack,2017-01-08,55
10. mart,2017-04-08,62
11. mart,2017-04-09,68
12. neil,2017-05-10,12
13. mart,2017-04-11,75
14. neil,2017-06-12,80
15. mart,2017-04-13,94
18. 建表加载数据
19. vim business.txt
21. create table business
22. (
23. name string,
24. orderdate string,
25. cost int
26. )ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
28. load data local inpath "/shujia/bigdata17/xiaohu/data/business.txt" into table business;

复制代码

实战1需求：

   需求1：查询在2017年4月份购买过的顾客及总人数

1. # 分析：按照日期过滤、分组count求总人数
2. select t1.name,t1.orderdate,count(1) over() as counts_04 from (select name,orderdate from business where month(orderdate)='04') t1;

复制代码

  需求2：查询顾客的购买明细及月购买总额

1. # 分析：按照顾客分组、sum购买金额
2. select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate))  from business;

复制代码

  需求3：上述的场景,要将cost按照日期进行累加

1. # 分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加
2. select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row)  from business;

复制代码

  需求4：查询顾客上次的购买时间

1. ·# 分析：查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)
2. select name,orderdate,cost,lag(orderdate,1) over(partition by name order by orderdate) as last_time from business;

复制代码

  需求5：查询前20%时间的订单信息

1. 分析：按照日期升序排序、取前20%的数据
2. select t1.name,t1.orderdate,t1.cost from (select name,orderdate,cost,ntile(5) over(order by orderdate) as n from business) t1 where t1.n=1;

复制代码

3.7.2、 排序开窗函数(重点)

• RANK() 排序相同时会重复，总数不会变
  
• DENSE_RANK() 排序相同时会重复，总数会减少
  
• ROW_NUMBER() 会根据顺序计算
  
• PERCENT_RANK()计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)
  

1. select name,subject,
2. score,
3. rank() over(partition by subject order by score desc) rp,
4. dense_rank() over(partition by subject order by score desc) drp,
5. row_number() over(partition by subject order by score desc) rnp,
6. percent_rank() over(partition by subject order by score) as percent_rank
7. from t_fraction;

复制代码

1. select name,subject,score,
2. rank() over(order by score) as row_number,
3. percent_rank() over(partition by subject order by score) as percent_rank
4. from t_fraction;

复制代码

实战2：Hive分析学天生绩信息

   创建表语加载数据

1. name        subject        score
2. 李毅        语文        87
3. 李毅        数学        95
4. 李毅        英语        68
5. 黄仙        语文        94
6. 黄仙        数学        56
7. 黄仙        英语        84
8. 小虎        语文        64
9. 小虎        数学        86
10. 小虎        英语        84
11. 许文客        语文        65
12. 许文客        数学        85
13. 许文客        英语        78
15. 建表加载数据
16. vim score.txt
18. create table score2
19. (
20. name string,
21. subject string,
22. score int
23. ) row format delimited fields terminated by "\t";
25. load data local inpath '/shujia/bigdata17/xiaohu/data/score.txt' into table score;

复制代码

  需求1：每门学科学天生绩排名(是否并列排名、空位排名三种实现)

1. 分析：学科分组、成绩降序排序、按照成绩排名
3. select name,subject,score,
4. rank() over(partition by subject order by score desc) rp,
5. dense_rank() over(partition by subject order by score desc) drp,
6. row_number() over(partition by subject order by score desc) rmp
7. from
8. score;

复制代码

  需求2：每门学科成绩排名top 2的学生

1. select t1.name,t1.subject,t1.score from (select name,subject,score,row_number() over(partition by subject order by score desc) as rn from score2) t1 where t1.rn<3;

复制代码

4、Hive 行转列

lateral view explode

1. create table testArray2(
2.     name string,
3.     weight array<string>
4. )row format delimited
5. fields terminated by '\t'
6. COLLECTION ITEMS terminated by ',';
8. 小虎        "150","170","180"
9. 火火        "150","180","190"
13. select name,col1  from testarray2 lateral view explode(weight) t1 as col1;
15. 小虎        150
16. 小虎        170
17. 小虎        180
18. 火火        150
19. 火火        180
20. 火火        190
22. select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;
24. key1
25. key2
26. key3
28. select name,col1,col2  from testarray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;
29. 小虎        key1        1
30. 小虎        key2        2
31. 小虎        key3        3
32. 火火        key1        1
33. 火火        key2        2
34. 火火        key3        3
37. select name,pos,col1  from testarray2 lateral view posexplode(weight) t1 as pos,col1;
39. 小虎        0        150
40. 小虎        1        170
41. 小虎        2        180
42. 火火        0        150
43. 火火        1        180
44. 火火        2        190

复制代码

5、Hive 列转行

1. // testLieToLine
2. name col1
3. 小虎        150
4. 小虎        170
5. 小虎        180
6. 火火        150
7. 火火        180
8. 火火        190
10. create table testLieToLine(
11.     name string,
12.     col1 int
13. )row format delimited
14. fields terminated by '\t';
17. select name,collect_list(col1) from testLieToLine group by name;
19. // 结果
20. 小虎        ["150","180","190"]
21. 火火        ["150","170","180"]
23. select  t1.name
24.         ,collect_list(t1.col1)
25. from (
26.     select  name
27.             ,col1
28.     from testarray2
29.     lateral view explode(weight) t1 as col1
30. ) t1 group by t1.name;

复制代码

6、Hive自定义函数UserDefineFunction

6.1、UDF：一进一出

   定义UDF函数要注意下面几点:
  

• 继承org.apache.hadoop.hive.ql.exec.UDF
  
• 重写evaluate()，这个方法不是由接口定义的,因为它可继承的参数的个数,数据类型都是不确定的。Hive会检查UDF,看可否找到和函数调用相匹配的evaluate()方法
  

  

• 创建maven项目，并加入依赖
  

1.         <dependency>
2.             <groupId>org.apache.hive</groupId>
3.             <artifactId>hive-exec</artifactId>
4.             <version>1.2.1</version>
5.         </dependency>

复制代码

  打包的时间可能会出现错误
  Could not transfer artifact org.pentaho:pentaho-aggdesigner-algorithm:pom:5.1.5-jhyde

    办理方案：
在pom文件中修改hive-exec的配置

1.         <dependency>
2.             <groupId>org.apache.hive</groupId>
3.             <artifactId>hive-exec</artifactId>
4.             <exclusions>
5.                 <!--排除pentaho-aggdesigner-algorithm依赖，不将它引入-->
6.                 <exclusion>
7.                     <groupId>org.pentaho</groupId>
8.                     <artifactId>pentaho-aggdesigner-algorithm</artifactId>
9.                 </exclusion>
10.             </exclusions>
11.         </dependency>

复制代码

• 编写代码，继承org.apache.hadoop.hive.ql.exec.UDF，实现evaluate方法，在evaluate方法中实现自己的逻辑
  

1. [/code]
2. [list]
3. [*]打成jar包并上传至Linux虚拟机
4. [*]在hive shell中，使用 add jar 路径将jar包作为资源添加到hive环境中
5. [/list] [code]add jar /usr/local/soft/bigdata19/hive-bigdata19-1.0-SNAPSHOT.jar;

复制代码

• 使用jar包资源注册一个临时函数，fxxx1是你的函数名，'MyUDF’是主类名
  

1. create temporary function fxxx1 as 'MyUDF';

复制代码

• 使用函数名处置惩罚数据
  

1. select fxx1(name) as fxx_name from students limit 10;
3. #施笑槐$
4. #吕金鹏$
5. #单乐蕊$
6. #葛德曜$
7. #宣谷芹$
8. #边昂雄$
9. #尚孤风$
10. #符半双$
11. #沈德昌$
12. #羿彦昌$

复制代码

案例2：转大写

1. [/code] [size=1]函数加载方式[/size]
3.    命令加载
4.   这种加载只对本session有效
5.   [code]# 1、将项目打包上传服务器：将打好的jar包传到linux系统中。(不要打依赖)
6. # 进入到hive客户端,执行下面命令
7. hive> add jar /usr/local/soft/bigdata17/data/xiaohu/hadoop-mapreduce-1.0-SNAPSHOT.jar
8. # 2、创建一个临时函数名,要跟上面hive在同一个session里面：
9. hive> create temporary function toUP as 'com.shujia.testHiveFun.udf.FirstUDF';
11. 3、检查函数是否创建成功
12. show functions;
14. 4. 测试功能
15. select toUp('abcdef');
17. 5. 删除函数
18. drop temporary function if exists toUp;

复制代码

  创建永久函数
  将jar上传HDFS：

1. hadoop fs -put hadoop-mapreduce-1.0-SNAPSHOT.jar /jar/

复制代码

  在hive命令行中创建永久函数：

1. create function myUp as 'com.shujia.testHiveFun.udf.FirstUDF' using jar 'hdfs:/jar/hadoop-mapreduce-1.0-SNAPSHOT.jar';
3. create function bfy_fun as 'com.shujia.udfdemo.HiveTest' using jar 'hdfs:/shujia/bigdata19/jar/hive-udf.jar';

复制代码

  退出hive，再进入，实行测试：
  

删除永久函数，并检查：

6.2、UDTF：一进多出

   UDTF是一对多的输入输出,实现UDTF需要完成下面步骤
  M1001#xiaohu#S324231212,lkd#M1002#S2543412432,S21312312412#M1003#bfy
  1001 xiaohu 324231212
  1002 lkd 2543412432
  1003 bfy 21312312412
  继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF，
重写initlizer（）、process（）、close()。
实行流程如下:
  UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）。
  初始化完成后，会调用process方法,真正的处置惩罚过程在process函数中，在process中，每一次forward()调用产生一行；假如产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。
  末了close()方法调用，对需要清理的方法进行清理。
    “key1:value1,key2:value2,key3:value3”
  key1 value1
  key2 value2
  key3 value3
  方法一：使用 explode+split

1. [/code] [size=1]方法二：自定UDTF[/size]
4. [list]
5. [*]代码
6. [/list] [code]

复制代码

• SQL
  

1. create temporary function my_udtf as 'com.shujia.testHiveFun.udtf.HiveUDTF';
3. select my_udtf("key1:value1,key2:value2,key3:value3");

复制代码

  字段：id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12 共13列
  数据：
  a,1,2,3,4,5,6,7,8,9,10,11,12
  b,11,12,13,14,15,16,17,18,19,20,21,22
  c,21,22,23,24,25,26,27,28,29,30,31,32
  转成3列：id,hours,value
  例如：
  a,1,2,3,4,5,6,7,8,9,10,11,12
  a,0时,1
  a,2时,2
  a,4时,3
  a,6时,4
  …

1. create table udtfData(
2.     id string
3.     ,col1 string
4.     ,col2 string
5.     ,col3 string
6.     ,col4 string
7.     ,col5 string
8.     ,col6 string
9.     ,col7 string
10.     ,col8 string
11.     ,col9 string
12.     ,col10 string
13.     ,col11 string
14.     ,col12 string
15. )row format delimited fields terminated by ',';

复制代码

代码：

1. [/code] 添加jar资源:
2. [code]add jar /usr/local/soft/HiveUDF2-1.0.jar;

复制代码

注册udtf函数：

1. create temporary function my_udtf as 'MyUDTF';

复制代码

SQL:

1. select id,hours,value from udtfData lateral view my_udtf(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12) t as hours,value ;

复制代码

6.3、UDAF：多进一出

Hive Shell

第一种：

1. hive -e "select * from test1.students limit 10"

复制代码

第二种：

1. hive -f hql文件路径

复制代码

  将HQL写在一个文件里，再使用 -f 参数指定该文件
  连续登岸问题

   在电商、物流和银行可能常常会遇到如许的需求：统计用户连续交易的总额、连续登岸天数、连续登岸开始和竣事时间、间隔天数等
  数据：

   注意：每个用户天天可能会有多条记录

1. id        datestr          amount
2. 1,2019-02-08,6214.23
3. 1,2019-02-08,6247.32
4. 1,2019-02-09,85.63
5. 1,2019-02-09,967.36
6. 1,2019-02-10,85.69
7. 1,2019-02-12,769.85
8. 1,2019-02-13,943.86
9. 1,2019-02-14,538.42
10. 1,2019-02-15,369.76
11. 1,2019-02-16,369.76
12. 1,2019-02-18,795.15
13. 1,2019-02-19,715.65
14. 1,2019-02-21,537.71
15. 2,2019-02-08,6214.23
16. 2,2019-02-08,6247.32
17. 2,2019-02-09,85.63
18. 2,2019-02-09,967.36
19. 2,2019-02-10,85.69
20. 2,2019-02-12,769.85
21. 2,2019-02-13,943.86
22. 2,2019-02-14,943.18
23. 2,2019-02-15,369.76
24. 2,2019-02-18,795.15
25. 2,2019-02-19,715.65
26. 2,2019-02-21,537.71
27. 3,2019-02-08,6214.23
28. 3,2019-02-08,6247.32
29. 3,2019-02-09,85.63
30. 3,2019-02-09,967.36
31. 3,2019-02-10,85.69
32. 3,2019-02-12,769.85
33. 3,2019-02-13,943.86
34. 3,2019-02-14,276.81
35. 3,2019-02-15,369.76
36. 3,2019-02-16,369.76
37. 3,2019-02-18,795.15
38. 3,2019-02-19,715.65
39. 3,2019-02-21,537.71

复制代码

建表语句

1. create table deal_tb(
2.     id string
3.     ,datestr string
4.     ,amount string
5. )row format delimited fields terminated by ',';

复制代码

计算逻辑

• 先按用户和日期分组求和，使每个用户天天只有一条数据
  

1. [/code]
2. [list]
3. [*] 根据用户ID分组按日期排序，将日期和分组序号相减得到连续登岸的开始日期，假如开始日期相同说明连续登岸
4. [*] [i]datediff(string end_date,string start_date);[/i] 即是0说明连续登录
5. [*] 统计用户连续交易的总额、连续登岸天数、连续登岸开始和竣事时间、间隔天数
6. [/list] [code]

复制代码

• 结果
  

1. 1        2019-02-07        13600.23        3        2019-02-08        2019-02-10 NULL
2. 1        2019-02-08        2991.650        5        2019-02-12        2019-02-16        1
3. 1        2019-02-09        1510.8                2        2019-02-18        2019-02-19        1
4. 1        2019-02-10        537.71                1        2019-02-21        2019-02-21        1
5. 2        2019-02-07        13600.23        3        2019-02-08        2019-02-10 NULL
6. 2        2019-02-08        3026.649        4        2019-02-12        2019-02-15        1
7. 2        2019-02-10        1510.8                2        2019-02-18        2019-02-19        2
8. 2        2019-02-11        537.71                1        2019-02-21        2019-02-21        1
9. 3        2019-02-07        13600.23        3        2019-02-08        2019-02-10 NULL
10. 3        2019-02-08        2730.04                5        2019-02-12        2019-02-16        1
11. 3        2019-02-09        1510.8                2        2019-02-18        2019-02-19        1
12. 3        2019-02-10        537.71                1        2019-02-21        2019-02-21        1

复制代码

inated by ‘,’;

1. 代码：```java```添加jar资源:```add jar /usr/local/soft/HiveUDF2-1.0.jar;
2. ```注册udtf函数：```create temporary function my_udtf as 'MyUDTF';
3. ```SQL:```select id,hours,value from udtfData lateral view my_udtf(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12) t as hours,value ;
4. ```## 6.3、UDAF：多进一出#### Hive Shell##### 第一种：```hive -e "select * from test1.students limit 10"
5. ```##### 第二种：```hive -f hql文件路径
6. ```> 将HQL写在一个文件里，再使用 -f 参数指定该文件#### 连续登岸问题> 在电商、物流和银行可能常常会遇到如许的需求：统计用户连续交易的总额、连续登岸天数、连续登岸开始和竣事时间、间隔天数等##### 数据：> 注意：每个用户天天可能会有多条记录```id        datestr          amount
7. 1,2019-02-08,6214.23
8. 1,2019-02-08,6247.32
9. 1,2019-02-09,85.63
10. 1,2019-02-09,967.36
11. 1,2019-02-10,85.69
12. 1,2019-02-12,769.85
13. 1,2019-02-13,943.86
14. 1,2019-02-14,538.42
15. 1,2019-02-15,369.76
16. 1,2019-02-16,369.76
17. 1,2019-02-18,795.15
18. 1,2019-02-19,715.65
19. 1,2019-02-21,537.71
20. 2,2019-02-08,6214.23
21. 2,2019-02-08,6247.32
22. 2,2019-02-09,85.63
23. 2,2019-02-09,967.36
24. 2,2019-02-10,85.69
25. 2,2019-02-12,769.85
26. 2,2019-02-13,943.86
27. 2,2019-02-14,943.18
28. 2,2019-02-15,369.76
29. 2,2019-02-18,795.15
30. 2,2019-02-19,715.65
31. 2,2019-02-21,537.71
32. 3,2019-02-08,6214.23
33. 3,2019-02-08,6247.32
34. 3,2019-02-09,85.63
35. 3,2019-02-09,967.36
36. 3,2019-02-10,85.69
37. 3,2019-02-12,769.85
38. 3,2019-02-13,943.86
39. 3,2019-02-14,276.81
40. 3,2019-02-15,369.76
41. 3,2019-02-16,369.76
42. 3,2019-02-18,795.15
43. 3,2019-02-19,715.65
44. 3,2019-02-21,537.71
45. ```##### 建表语句```sqlcreate table deal_tb(
46.     id string
47.     ,datestr string
48.     ,amount string
49. )row format delimited fields terminated by ',';
50. ```##### 计算逻辑* 先按用户和日期分组求和，使每个用户天天只有一条数据```sql```* 根据用户ID分组按日期排序，将日期和分组序号相减得到连续登岸的开始日期，假如开始日期相同说明连续登岸* *datediff(string end_date,string start_date);* 即是0说明连续登录* 统计用户连续交易的总额、连续登岸天数、连续登岸开始和竣事时间、间隔天数``````* 结果```1        2019-02-07        13600.23        3        2019-02-08        2019-02-10 NULL
51. 1        2019-02-08        2991.650        5        2019-02-12        2019-02-16        1
52. 1        2019-02-09        1510.8                2        2019-02-18        2019-02-19        1
53. 1        2019-02-10        537.71                1        2019-02-21        2019-02-21        1
54. 2        2019-02-07        13600.23        3        2019-02-08        2019-02-10 NULL
55. 2        2019-02-08        3026.649        4        2019-02-12        2019-02-15        1
56. 2        2019-02-10        1510.8                2        2019-02-18        2019-02-19        2
57. 2        2019-02-11        537.71                1        2019-02-21        2019-02-21        1
58. 3        2019-02-07        13600.23        3        2019-02-08        2019-02-10 NULL
59. 3        2019-02-08        2730.04                5        2019-02-12        2019-02-16        1
60. 3        2019-02-09        1510.8                2        2019-02-18        2019-02-19        1
61. 3        2019-02-10        537.71                1        2019-02-21        2019-02-21        1
62. ```

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。