《MY大数据开发笔记》----hive知识点总结

桃扇骨 2022-03-25 10:07 638阅读 0赞

hive的作用：hive最初的设计目的是为了给哪些精通sql但是不熟悉Hadoop的分析师，用来分析大规模的结构化的数据文件而设计的。

首先最重要的一点：

**hive的使用和关系型数据库的使用方式类似（类sql语句），那我们为什么不使用关系型数据库而使用hive？**

1.hive能分析超大数据规模的数据。

2.hive用于分析存储在文件中的结构化的数据。而关系型数据库不能。

所以hive最初的设计目的是：给哪些精通sql但是编码不熟练。而要分析的结构化的数据存储于大规模的文件中。在hive出现之前。是用MR来进行统计和分析。但是对这些存储文件中的结构化的数据。MR都类似。分析的统计最终数据也类似于关系型数据库中的手法。所以索性就将这些MR统一封装。成一个HQL语句。在调用HQL语句分析这些数据时，转换成MR。

# 一：hive提供的服务 #

输入hive --service help。弹出hive提供的服务列表。在这里主要介绍常用的几个服务。 cli ,hiveserver,hwi,jar

[s201 ~]$ hive --service help
 Usage ./hive <parameters> --service serviceName <service parameters>
 Service List: 
 beeline cleardanglingscratchdir cli hbaseimport hbaseschematool
 help hiveburninclient hiveserver2 hplsql jar lineage llapdump 
 llap llapstatus metastore metatool orcfiledump rcfilecat schemaTool version

1.1 hive cli 服务

默认的服务。输入hive直接启动这个服务，进入cli服务的交互式模式。

[s201 ~]$ hive
    SLF4J: Class path contains multiple SLF4J bindings.
    SLF4J: Found binding in [jar:file:/soft/apache-hive-2.3.4-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
    SLF4J: Found binding in [jar:file:/soft/hadoop-2.9.2/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
    SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
    SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
    
    Logging initialized using configuration in jar:file:/soft/apache-hive-2.3.4-bin/lib/hive-common-2.3.4.jar!/hive-log4j2.properties Async: true
    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.
    hive>

进入cli的交互式模式。里面的命令如下

* quit,exit:  退出交互式shell
 * reset: 重置配置为默认值
 * set <key>=<value> : 修改特定变量的值(如果变量名拼写错误，不会报错)
 * set :  输出用户覆盖的hive配置变量
 * set -v : 输出所有Hadoop和Hive的配置变量
 * add FILE\[S\] \*, add JAR\[S\] \*, add ARCHIVE\[S\] \* : 添加 一个或多个 file, jar, archives到分布式缓存
 * list FILE\[S\], list JAR\[S\], list ARCHIVE\[S\] : 输出已经添加到分布式缓存的资源。
 * list FILE\[S\] \*, list JAR\[S\] \*,list ARCHIVE\[S\] \* : 检查给定的资源是否添加到分布式缓存
 * delete FILE\[S\] \*,delete JAR\[S\] \*,delete ARCHIVE\[S\] \* : 从分布式缓存删除指定的资源
 * ! <command> :  从Hive shell执行一个shell命令
 * dfs <dfs command> :  从Hive shell执行一个dfs命令
 * <query string> : 执行一个Hive 查询，然后输出结果到标准输出
 * source FILE <filepath>:  在CLI里执行一个hive脚本文件
 * ！+ shell 命令，可以执行宿主机的命令如   !ls /；
 * dfs 命令可访问Hadoop文件系统

可以在非交互模式下使用cli服务。

script.q是按行分隔的hive命令。这是一个脚本文件。
    下面的命令不进入交互式模式而可以直接执行hive脚本。
    
    [s201 ~]$ hive -f script.q
    
    
    执行单条的hive命令
    [s201 ~]$ hive -e 'select * from testTable'
    
    hive会将所有输出打印到标准输出。如果只想看到结果不想看到其他输出信息。
    [s201 ~]$ hive -e -S 'select * from testTable'

1.2 hiveserver 服务

hive 以提供Thrift服务的服务器形式运行。允许Hive Thrift client，hive jdbc client， hive odbc连接。设置hive\_port来设置该服务器的监听端口号。

启动该服务器的方式：

$>hive --service hiveserver2 &

使用hive jdbc client方式连接到hiveserver。

方式一：使用beeline服务，在命令行连接。

$>beeline						//进入beeline命令行(于hive --service beeline)
    $beeline>!help										//查看帮助
    $beeline>!quit										//退出
    $beeline>!connect jdbc:hive://localhost:10000/mydb2//连接到hibve数据
    注，这里连接不上。用
    !connect jdbc:hive2://localhost:10000 root
    再输入mysql的密码就可以连上了。
    具体参考hive的安装配置。
    metastore数据存在数据库中，
    实际数据存在HDFS上。
    所以要连上hive，就需要输入mysql的用户和密码。
    
    
    $beeline>show databases ;
    $beeline>use mydb2 ;
    $beeline>show tables;								//显式表

方式二：java编码中使用hive jdbc client方式连接到hiveserver

使用Hive-jdbc驱动程序采用jdbc方式访问远程数据仓库
 ----------------------------------------------------
 	1.创建java模块
 	2.引入maven
 	3.添加hive-jdbc依赖
 		<?xml version="1.0" encoding="UTF-8"?>
 		<project xmlns="http://maven.apache.org/POM/4.0.0"
 				 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 				 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 			<modelVersion>4.0.0</modelVersion>
 
 			<groupId>com.it18zhang</groupId>
 			<artifactId>HiveDemo</artifactId>
 			<version>1.0-SNAPSHOT</version>
 
 			<dependencies>
 				<dependency>
 					<groupId>org.apache.hive</groupId>
 					<artifactId>hive-jdbc</artifactId>
 					<version>2.1.0</version>
 				</dependency>
 			</dependencies>
 		</project>
 
 	4.App
 		package com.it18zhang.hivedemo;
 
 		import java.sql.Connection;
 		import java.sql.DriverManager;
 		import java.sql.ResultSet;
 		import java.sql.Statement;
 
 		/**
 		 * 使用jdbc方式连接到hive数据仓库，数据仓库需要开启hiveserver2服务。
 		 */
 		public class App {
 			public static void main(String[] args) throws Exception {
 				Class.forName("org.apache.hive.jdbc.HiveDriver");
 				//Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.231.201:10000/mydb2");
 				Connection conn = DriverManager.getConnection("jdbc:hive://192.168.231.201:10000/mydb2");//应该是这个
 				Statement st = conn.createStatement();
 				ResultSet rs = st.executeQuery("select id , name ,age from t");
 				while(rs.next()){
 					System.out.println(rs.getInt(1) + "," + rs.getString(2)) ;
 				}
 				rs.close();
 				st.close();
 				conn.close();
 			}
 		}

1.3 hwi 服务

HWI是Hive Web Interface的简称，是hive cli的一个web替换方案。熟悉hive cli就够了，操作hwi更是简单。  
所以这里就略过。

1.4 hive jar 服务

hive jar 和Hadoop jar一样，是运行类路径中同时包含Hadoop和hive类java应用程序的方式

1.5 hive metastore服务

要介绍metastore服务，必须先介绍metastore。

metastore包含metastore服务和metastore后台数据的存储。

metastore服务默认是和hive服务程序运行在一个jvm中。我们可以配置metastore服务和hive服务分离，运行在不同的进程中。这样做可以提供更好的管理性和安全性。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTMxOTcxMzkyNQ_size_16_color_FFFFFF_t_70][]

如上图，metastare的三种不熟方式。

一：内嵌的metastare。数据库采用内嵌的Derby，一次只能为一个metastare打开一个hive会话。如果尝试打开第二个会话则会报错。

二：本地的metastare。数据库采用mysql，mysql运行在本机或者远程机器。可以打开多个会话。但是metastare服务和hive服务仍然在一个进程中。

三：远程的metastare。数据库采用mysql，mysql运行在本机或者远程机器。可以打开多个会话。metastare服务和hive服务仍然在不同的进程中。

具体的配置，略。入门使用内嵌的，一般情况为了方便都使用本地的，为了考虑安全，商用性可用性等一般使用远程的。

# 二：hive和传统关系型数据库的区别 #

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:137.3pt;"> Hive </td> 
 <td style="vertical-align:top;width:137.35pt;"> 传统数据库 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 语言 </td> 
 <td style="vertical-align:top;width:137.3pt;"> Hql </td> 
 <td style="vertical-align:top;width:137.35pt;"> sql </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 数据存储 </td> 
 <td style="vertical-align:top;width:137.3pt;"> Hdfs </td> 
 <td style="vertical-align:top;width:137.35pt;"> 磁盘 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 执行 </td> 
 <td style="vertical-align:top;width:137.3pt;"> MR </td> 
 <td style="vertical-align:top;width:137.35pt;"> Excutor </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 延迟 </td> 
 <td style="vertical-align:top;width:137.3pt;"> 高 </td> 
 <td style="vertical-align:top;width:137.35pt;"> 低（小规模查询） </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 处理规模 </td> 
 <td style="vertical-align:top;width:137.3pt;"> 大 </td> 
 <td style="vertical-align:top;width:137.35pt;"> 小 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 索引 </td> 
 <td style="vertical-align:top;width:137.3pt;"> 0.7.0之后开始支持紧凑索引和位图索引 </td> 
 <td style="vertical-align:top;width:137.35pt;"> 索引，聚集索引和非聚集索引 更新，事务，是传统数据库最重要的特征，当然全部支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 更新 </td> 
 <td style="vertical-align:top;width:137.3pt;"> 不支持Update的delete，支持insert。 后面都支持了 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 事物 </td> 
 <td style="vertical-align:top;width:137.3pt;"> 0.13.0之后开始支持事务（表级和分区级），默认不打开，需要配置hive-site参数。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;"> 模式 </td> 
 <td style="vertical-align:top;width:137.3pt;"> 读时模式 </td> 
 <td style="vertical-align:top;width:137.35pt;"> 写时模式 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;">数据类型</td> 
 <td style="vertical-align:top;width:137.3pt;">除了基础数据类型还支持，数组，映射，结构</td> 
 <td style="vertical-align:top;width:137.35pt;">基础数据类型</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;">多表插入</td> 
 <td style="vertical-align:top;width:137.3pt;">支持</td> 
 <td style="vertical-align:top;width:137.35pt;">不支持</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;">连接</td> 
 <td style="vertical-align:top;width:137.3pt;">内连接，外链接，半连接，映射连接</td> 
 <td style="vertical-align:top;width:137.35pt;">支持，因为hql都是基于此来做的</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:137.3pt;">子查询</td> 
 <td style="vertical-align:top;width:137.3pt;">只能在from字句中，不支持相关的子查询</td> 
 <td style="vertical-align:top;width:137.35pt;">任何子句中支持相关的或者不相关的</td> 
 </tr> 
 </tbody> 
</table>

**关键字解释：**

**聚集索引：**物理上逻辑上都是连续的索引，一个表中只能有一个

非聚集索引：逻辑上连续，物理上不连续的索引，一个表中可以有多个

**紧凑索引**：存储每个值得hdfs块号。不是存储文件的内偏移量，因此不会占用太多的空间

**位图索引**：使用压缩的位集合来高效存储具有某个特殊值的行。这种缩影一般适用于较少曲子可能的列，如性别和国籍。

**读时模式**：对数据的验证不在加载数据的时候验证，而在查询的时候才验证。所以叫读时模式。

**写时模式**：表的模式在数据加载入数据库的时候就确定了，如果加载时发现数据不符合，就拒绝加载。数据在写入数据库时，对照模式进行检查。所以叫写实模式。

# 三：hive的数据类型详解 #

## 3.1基本数据类型 ##

<table> 
 <thead> 
 <tr> 
 <td> 类型名称 </td> 
 <td> 大小 </td> 
 <td> 示例 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> TINYINT </td> 
 <td> 1字节整数 </td> 
 <td> 45Y </td> 
 </tr> 
 <tr> 
 <td> SMALLINT </td> 
 <td> 2字节整数 </td> 
 <td> 12S </td> 
 </tr> 
 <tr> 
 <td> INT </td> 
 <td> 4字节整数 </td> 
 <td> 10 </td> 
 </tr> 
 <tr> 
 <td> BIGINT </td> 
 <td> 8字节整数 </td> 
 <td> 244L </td> 
 </tr> 
 <tr> 
 <td> FLOAT </td> 
 <td> 4字节单精度浮点数 </td> 
 <td> 1.0 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> 8字节双精度浮点数 </td> 
 <td> 1.0 </td> 
 </tr> 
 <tr> 
 <td> DECIMAL </td> 
 <td> 任意精度带符号小数 </td> 
 <td> DECIMAL(4, 2)范围：-99.99到99.99 </td> 
 </tr> 
 <tr> 
 <td> BOOLEAN </td> 
 <td> true/false </td> 
 <td> TRUE </td> 
 </tr> 
 <tr> 
 <td> STRING </td> 
 <td> 字符串，长度不定 </td> 
 <td> “a”, ‘b’ </td> 
 </tr> 
 <tr> 
 <td> VARCHAR </td> 
 <td> 字符串，长度不定，有上限 </td> 
 <td> “a”, ‘b’ </td> 
 </tr> 
 <tr> 
 <td> CHAR </td> 
 <td> 字符串，固定长度 </td> 
 <td> “a”, ‘b’ </td> 
 </tr> 
 <tr> 
 <td> BINARY </td> 
 <td> 存储变长的二进制数据 </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> TIMESTAMP </td> 
 <td> 时间戳，纳秒精度 </td> 
 <td> 122327493795 </td> 
 </tr> 
 <tr> 
 <td> DATE </td> 
 <td> 日期 </td> 
 <td> ‘2016-07-03’ </td> 
 </tr> 
 </tbody> 
</table>

**说明： **  
1，关于整数和小数类型，整数默认情况下为INT，如果要声明为其他类型，通过后缀来标识（Y、S、L）。小数默认为DOUBLE类型，DECIMAL小数点左边允许的最大位数为38位，直接写DECIMAL默认等同于DECIMAL(10,0)。同时任意数值类型都可以隐式转换成更宽的数值类型（不丢精度）或者文本类型。   
2，关于文本类型，STRING存储变长的文本，对长度没有限制。理论上将STRING可以存储的大小为2GB，但是存储特别大的对象时效率可能受到影响。VARCHAR与STRING类似，但是长度上只允许在1-65355之间，超过最大长度会被截断。CHAR则用固定长度来存储数据，最大255。   
3，关于时间和日期类型，Hive提供了一些内置函数用于在TIMESTAMP与Unix时间戳（秒）和字符串之间做转换：

<table> 
 <thead> 
 <tr> 
 <th>类型转换</th> 
 <th>结果</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>cast(date as date)</td> 
 <td>相同的日期。</td> 
 </tr> 
 <tr> 
 <td>cast(timestamp as date)</td> 
 <td>基于本地时区确定timestamp的年月日作为值返回。</td> 
 </tr> 
 <tr> 
 <td>cast(string as date)</td> 
 <td>如果字符串的格式为’YYYY-MM-DD’, 则对应的年月日返回。如果字符串不是该格式，则返回NULL。</td> 
 </tr> 
 <tr> 
 <td>cast(date as timestamp)</td> 
 <td>基于本地时区，返回日期对应午夜时间。</td> 
 </tr> 
 <tr> 
 <td>cast(date as string)</td> 
 <td>日期被转换为’YYYY-MM-DD’格式的字符串。</td> 
 </tr> 
 </tbody> 
</table>

## 3.2复杂数据类型 ##

<table> 
 <thead> 
 <tr> 
 <th>类型名称</th> 
 <th>大小</th> 
 <th>示例</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>ARRAY</td> 
 <td>存储同类型数据</td> 
 <td>ARRAY&lt; data_type&gt;</td> 
 </tr> 
 <tr> 
 <td>MAP</td> 
 <td>key-value,key必须为原始类型，value可以是任意类型</td> 
 <td>MAP&lt; primitive_type, data_type&gt;</td> 
 </tr> 
 <tr> 
 <td>STRUCT</td> 
 <td>类型可以不同</td> 
 <td>STRUCT&lt; col_name : data_type [COMMENT col_comment], …&gt;</td> 
 </tr> 
 <tr> 
 <td>UNION</td> 
 <td>在有限取值范围内的一个值</td> 
 <td>UNIONTYPE&lt; data_type, data_type, …&gt;说明：&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

关于元素的访问，ARRAY\[1\]访问第二个元素；  
map访问元素格式为map\[‘key’\]；  
STRUCT元素的访问使用点号；  
UNION类型声明语法为UNIONTYPE< data\_type,data\_type,…>，每个UNION类型的值都通过一个整数来表示其类型，这个整数就是索引，从0开始。例如：

CREATE TABLE union_test(u UNIONTYPE<int, double, array<string>,struct<a:int,b:string>>); 
 SELECT u FROM union_test; 
 {0:1} 
 {1:2.0} 
 {2:["three","four"]} 
 {3:{"a":5,"b":"five"}} 
 {2:["six","seven"]} 
 {3:{"a":8,"b":"eight"}} 
 {0:9} 
 {1:10.0}

## 3.3 案例实操 ##

### （1）假设有如下json表示的数据结构 ###

{
        "name": "songsong",
        "friends": ["bingbing" , "lili"] ,           //列表Array,
        "children": {                               //键值Map,
            "xiao song": 18 ,
            "xiaoxiao song": 19
        }
        "address": {                               //结构Struct,
            "street": "hui long guan" ,
            "city": "beijing"
        }
    }

**注意：这里猛的一看，键值对MAP和地址结构Struce。在结构上似乎没太大不同。**

**在自己设计的时候。怎么样区分这种结构，什么时候用MAP。什么时候用Struct.**

**且找共性：键值Map出。child，每个孩子是不一样的。对应的年龄是不一样的。所以用map结构。**

**而地址。有两个字段。一个street。一个city。每一个地址都是要有这两个属性的。**

**如果说child那里也是这样。可以使用性别。年龄。但是有多个孩子。就成了数组。而结构体如果构成数组将变复杂了。**

**似乎map和结构体可以互相转换。但是寻找一个最优的存储格式。还是有点难。最好就是用单个的数据类型解决。而最好不要用多个数据类型解决。**

**比如。child如果要用结构体来存。也可以。这会构成一个结构体数组，数组成员两个结构体成员。结构体成员有性别和年龄。**

**这就用到了两个数据结构。而用map一个数据结构就可以解决。**

**而地址，如果要用map来解决，也可以。会构成一个map数组。数组成员两个map成员。map成员是地区到具体地区，city到具体city的映射、这就太复杂了。所以选择合适的数据结构来存储数据也是一门学问和经验。**

**注意，这里存的只是一行的数据，地址这用map其实也是可以的，因为每一行是独立的。只不过结构体更符合逻辑**

## （2）准备数据文件 ##

/opt/module/datas/test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
    yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

## （3）hive上创建表test ##

create table test(
 name string,
 friends array<string>,
 children map<string, int>,
 address struct<street:string, city:string>
 )
 row format delimited fields terminated by ','
 collection items terminated by '_'
 map keys terminated by ':'
 lines terminated by '\n';

**字段解释：**  
row format delimited fields terminated by ','  -- 列分隔符  
collection items terminated by '\_'      --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)  
map keys terminated by ':'                -- MAP中的key与value的分隔符  
lines terminated by '\\n';                    -- 行分隔符

## （4）导入文本数据到测试表 ##

hive (default)> load data local inpath '/opt/module/datas/test.txt’ into table test

## （5）访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式 ##

hive (default)> select friends[1],children['xiao song'],address.city from test where name="songsong";
    OK
    _c0    _c1    city
    lili    18    beijing
    Time taken: 1.5 seconds, Fetched: 1 row(s)
    
    hive (default)>hive (default)> select friends,children,address.city from test where name="songsong";
    OK
    friends                children                                city
    ["bingbing","lili"]    {"xiao song":18,"xiaoxiao song":19}    beijing
    Time taken: 0.07 seconds, Fetched: 1 row(s)

**注，hive的文件读取和存储格式不只是text，还有二进制的文件格式，顺序文件，Avro数据文件，以及RCfile文件。**

# 四：hive 表数据的导入 #

## 4.1 从本地文件系统导入 ##

#创建表结构
    hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    
    
    hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_hive ;
    Copying data from file:/home/cos/demo/t_hive.txt
    
    
    #当然，导入了数据后，就可以通过select * from t_hive来查看数据
    hive> select * from t_hive;

## 4.2 从hdfs文件系统导入 ##

创建表t_hive2
    hive> CREATE TABLE t_hive2 (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    
    #从HDFS加载数据
    hive> LOAD DATA INPATH '/user/hive/warehouse/t_hive/t_hive.txt' OVERWRITE INTO TABLE t_hive2;
    
    
    #查看数据
    hive> select * from t_hive2;
    OK

## 4.3 从其他hive表导入 ##

hive> INSERT OVERWRITE TABLE t_hive2 SELECT * FROM t_hive ;
    
    overwrite关键字
    overwrite关键字的意思是这个表之前的所有数据将会被替换掉。
    如果不使用overwrite，
    那么该表之前的数据就仍然存在，仅仅是往其中添加新的记录。
    
    上面的语句等同于
    FROM t_hive
    INSERT OVERWRITE TABLE t_hive2
    SELECT *;

## 4.3.5 多表插入 ##

FORM table2
    INSERT OVERWRITE TABLE table_y_c
        SELECT year,CORNT(DISTINCT station)
        GROUP BY year
    INSERT OVERWIRTE TABLE records_by_year
        SELECT year COUNT(1)
        GROUP BY year;
    
    多表查询，扫描一个表的数据，插入多个表。
    相比多个单独的INSERT语句，效率更高，
    因为单独的INSERT语句，会每次都扫描一次表。

# #

## 4.4 创建表的时候从其他表导入 ##

#删除表
    hive> DROP TABLE t_hive;
    
    #创建表并从其他表导入数据
    hive> CREATE TABLE t_hive AS SELECT * FROM t_hive2 ;
    
    就是被简称称为CTAS SELECT语句。

## 4.5 复制表 ##

hive> CREATE TABLE t_hive3 LIKE t_hive;
    
    
    #仅仅拷贝了表的结构
    hive> select * from t_hive3;
    OK
    Time taken: 0.077 seconds

## 4.6 从mysql数据库导入 ##

这里用到了另一个Hadoop组件sqoop.
    在后面的文章中再介绍

# 五：hive 表数据的导出 #

## 5.1将表数据导出到本地文件系统 ##

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/t_hive' SELECT * FROM t_hive;
    
    ...省略中间的打印信息
    
    
    #查看本地操作系统
    hive> ! cat /tmp/t_hive/000000_0;
    hive> 1623
    611213
    41231
    17213
    71231
    11234
    11234

## 5.2 导出到关系型数据库 ##

通过sqoop。

导出到关系型数据库。是因为关系型数据库的查询比较快。

## 六：hive 表结构的增（创建）删改查 ##

## 6.1 hive 表结构的增 ##

//准备数据
    >vi /home/cos/demo/t_hive.txt
    16      2       3
    61      12      13
    41      2       31
    17      21      3
    71      2       31
    1       12      34
    11      2       34
    
    //进入hive
    >hive shell
    hive>
    
    //创建新表
    hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
    
    //导入数据
    hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_hive ;

## 6.2 hive 表结构的查 ##

#查看表 
    hive> show tables;
    t_hive
    
    
    #正则匹配表名
    hive>show tables '*t*';
    t_hive
    
    #查看表数据
    hive> select * from t_hive;
    
    
    #查看表结构
    hive> desc t_hive;
    OK
    a       int
    b       int
    c       int

## 6.3 hive 表结构的改 ##

#增加一个字段
    hive> ALTER TABLE t_hive ADD COLUMNS (new_col String);
    
    hive> desc t_hive;
    OK
    a       int
    b       int
    c       int
    new_col string
    
    
    #重命令表名
    ~ ALTER TABLE t_hive RENAME TO t_hadoop;
    
    hive> show tables;
    OK
    t_hadoop

## 6.4 hive 表结构的删 ##

hive> DROP TABLE t_hadoop;
    
    //查看有哪些表的时候已经没有这个表的存在了
    hive> show tables;

# 七：排序 #

### order by ###

order by 会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）  
只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。  
set hive.mapred.mode=nonstrict; (default value / 默认值)  
set hive.mapred.mode=strict;  
 order by 和数据库中的Order by 功能一致，按照某一项 & 几项 排序输出。 与数据库中 order by 的区别在于在hive.mapred.mode = strict 模式下 必须指定 limit 否则执行会报错。  
hive> select \* from test order by id;       
FAILED: Error in semantic analysis: 1:28 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'id'  
原因： 在order by 状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无法输出结果的情况，如果进行 limit n ，那只有  n \* map number 条记录而已。只有一个reduce也可以处理过来。

### sort by ###

sort by不是全局排序，其在数据进入reducer前完成排序. 
因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序。 
sort by 不受 hive.mapred.mode 是否为strict ,nostrict 的影响sort by 的数据只能保证在同一reduce中的数据可以按指定字段排序。使用sort by 你可以指定执行的reduce 个数 （set mapred.reduce.tasks=<number>）,对输出的数据再执行归并排序，即可以得到全部结果。

**注意**：可以用limit子句大大减少数据量。使用limit n后，传输到reduce端（单机）的数据记录数就减少到n\* （map个数）。否则由于数据过大可能出不了结果。

### distribute by ###

按照指定的字段对数据进行划分到不同的输出reduce  / 文件中。  
 insert overwrite local directory '/home/hadoop/out' select \* from test order by name distribute by length(name);    
 此方法会根据name的长度划分到不同的reduce中，最终输出到不同的文件中。   
 length 是内建函数，也可以指定其他的函数或这使用自定义函数。

### Cluster By ###

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。   
但是排序只能是倒序排序，不能指定排序规则为asc 或者desc。

# 八：hive的连接查询，聚合查询，子查询，视图 #

## 8.1 连接查询 ##

//内连接查询，据说hive不支持这种语法的内连接
    hive> select a.*,b.* from customers a , orders b where a.id = b.cid ;
    //内连接查询
    hive>select a.*,b.* from customers a      outer join orders b on a.id = b.cid ;
    //左外连接
    hive>select a.*,b.* from customers a left outer join orders b on a.id = b.cid ;
    //右外连接
    hive>select a.*,b.* from customers a right outer join orders b on a.id = b.cid ;
    //全外连接
    hive>select a.*,b.* from customers a full outer join orders b on a.id = b.cid ;
    
    //半连接 据说hive不支持这种语法
    select * from things_table 
    where things_table.id IN (select id from sales);
    //半连接，所以引入这个语法代替
    SELECT * 
    FROM things_table LEFT SEMI JOIN sales ON (sales.id = things_table.id);
    
    
    map连接
    如果要使用map连接，需要再其中加入C语言风格的注释，表示这个是一个MAP连接。
    SELECT /* + MAPJOIN(things) */ sales.* things.*
    FROM sales JOIN things ON (sales.id = things.id);

## 8.2 聚合查询 ##

# count, avg
    hive> SELECT count(*), avg(a) FROM t_hive;
    7       31.142857142857142
    
    
    # count，distinct
    hive> SELECT count(DISTINCT b) FROM t_hive;
    3
    
    
    # GROUP BY
    hive> SELECT avg(a),b,sum(c) FROM t_hive GROUP BY b,c
    16.0    2       3
    56.0    2       62
    11.0    2       34
    61.0    12      13
    1.0     12      34
    17.0    21      3
    
    # HAVING
    hive> SELECT avg(a),b,sum(c) FROM t_hive GROUP BY b,c HAVING sum(c)>30
    56.0    2       62
    11.0    2       34
    1.0     12      34

## 8.3 子查询 ##

子查询指的是内嵌在另一个SQL语句中的SELECT语句。

hive只允许子查询出现在SELECT语句中的FROM子句。

SELECT station,year
    FROM (
        SELECT station,year
        FROM record2
        ) mt
    GROUP BY station;

##  8.4视图 ##

//创建视图
    $hive>create view v1 as select a.id aid,a.name ,b.id bid , b.order \
    from customers a left outer join default.tt b on a.id = b.cid ;
    
    //查看视图
    $hive>show tables ;
    $hive>select * from v1 ;

# 九：hive的分区和桶 #

## 9.1 静态分区 ##

Hive分区的概念与传统关系型数据库分区不同。  
传统数据库的分区方式：就oracle而言，分区独立存在于段里，里面存储真实的数据，在数据进行插入的时候自动分配分区。Hive的分区方式：由于Hive实际是存储在HDFS上的抽象，Hive的一个分区名对应一个目录名，子分区名就是子目录名，并不是一个实际字段。

（1）单个分区

创建分区

create table par_tab (name string,nation string) \
    partitioned by (sex string) \
    row format delimited fields terminated by ',';

该分区表的表结构：

hive> desc par_tab;
    OK
    name                    string                                      
    nation                  string                                      
    sex                     string                                      
              
    # Partition Information          
    # col_name                data_type               comment             
              
    sex                     string                                      
    Time taken: 0.038 seconds, Fetched: 8 row(s)

准备数据

jan,china
    mary,america
    lilei,china
    heyong,china
    yiku,japan
    emoji,japan

数据插入分区表

load data local inpath '/home/hadoop/files/par_tab.txt' into table par_tab partition (sex='man');

查看表数据

hive> select * from par_tab;
    OK
    jan    china    man
    mary    america    man
    lilei    china    man
    heyong    china    man
    yiku    japan    man
    emoji    japan    man
    Time taken: 0.076 seconds, Fetched: 6 row(s)

查看表存储在hdfs上的目录结构

再准备数据

lily,china
    nancy,china
    hanmeimei,america

再插入数据

load data local inpath '/home/hadoop/files/par_tab_wm.txt' into table par_tab partition (sex='woman');

再查看表数据

hive> select * from par_tab;
    OK
    jan    china    man
    mary    america    man
    lilei    china    man
    heyong    china    man
    yiku    japan    man
    emoji    japan    man
    lily    china    woman
    nancy    china    woman
    hanmeimei    america    woman
    Time taken: 0.136 seconds, Fetched: 9 row(s)

再查看表在hdfs上的存储结构

[hadoop@hadoop001 files]$ hadoop dfs -lsr /user/hive/warehouse/par_tab
    drwxr-xr-x   - hadoop supergroup          0 2017-03-29 08:25 /user/hive/warehouse/par_tab/sex=man
    -rwxr-xr-x   1 hadoop supergroup         71 2017-03-29 08:25 /user/hive/warehouse/par_tab/sex=man/par_tab.txt
    drwxr-xr-x   - hadoop supergroup          0 2017-03-29 08:35 /user/hive/warehouse/par_tab/sex=woman
    -rwxr-xr-x   1 hadoop supergroup         41 2017-03-29 08:35 /user/hive/warehouse/par_tab/sex=woman/par_tab_wm.txt

查询指定分区的表数据

hive> select * from par_tab where sex='woman';
    OK
    lily    china    woman
    nancy    china    woman
    hanmeimei    america    woman
    Time taken: 0.515 seconds, Fetched: 3 row(s)

（2）多分区

创建多分区的表

hive> create table par_tab_muilt (name string, nation string) \
    partitioned by (sex string,dt string) \
    row format delimited fields terminated by ',' ;
    
    
    hive> load data local inpath '/home/hadoop/files/par_tab.txt' \
    into table par_tab_muilt partition (sex='man',dt='2017-03-29');

查看存储在hdfs上的目录结构。与单个分区表的不同的

[hadoop@hadoop001 files]$ hadoop dfs -lsr /user/hive/warehouse/par_tab_muilt
    drwxr-xr-x   - hadoop supergroup          0 2017-03-29 08:45 /user/hive/warehouse/par_tab_muilt/sex=man
    drwxr-xr-x   - hadoop supergroup          0 2017-03-29 08:45 /user/hive/warehouse/par_tab_muilt/sex=man/dt=2017-03-29
    -rwxr-xr-x   1 hadoop supergroup         71 2017-03-29 08:45 /user/hive/warehouse/par_tab_muilt/sex=man/dt=2017-03-29/par_tab.txt

## 9.2 动态分区 ##

启用动态分区

hive> set hive.exec.dynamic.partition=true;

插入数据(dt是冬天分区)

hive> insert overwrite table par_dnm partition(sex='man',dt)
        > select name, nation, dt from par_tab;

**注意，动态分区不允许[主分区][Link 1]采用动态列而副分区采用静态列，这样将导致所有的主分区都要创建副分区静态列所定义的分区。**

动态分区可以允许所有的分区[列都][Link 2]是动态分区列，但是要首先设置一个参数

hive> set hive.exec.dynamic.partition.mode=nostrick;

##  9.3 桶 ##

# 十：hive 自定义函数 #

1.创建类，继承UDF
    		package com.it18zhang.hivedemo.udf;
    
    		import org.apache.hadoop.hive.ql.exec.Description;
    		import org.apache.hadoop.hive.ql.exec.UDF;
    
    		/**
    		 * 自定义hive函数
    		 */
    		@Description(name = "myadd",
    				value = "myadd(int a , int b) ==> return a + b ",
    				extended = "Example:\n"
    						+ " myadd(1,1) ==> 2 \n"
    						+ " myadd(1,2,3) ==> 6;")
    		public class AddUDF extends UDF {
    
    			public int evaluate(int a ,int b) {
    				return a + b ;
    			}
    
    			public int evaluate(int a ,int b , int c) {
    				return a + b + c;
    			}
    		}
    	2.打成jar包。
    		cmd>cd {classes所在目录}
    		cmd>jar cvf HiveDemo.jar -C x/x/x/x/classes/ .
    	3.添加jar包到hive的类路径
    		//添加jar到类路径
    		$>cp /mnt/hgfs/downloads/bigdata/data/HiveDemo.jar /soft/hive/lib
    	
    	3.重进入hive
    		$>....
    
    	4.创建临时函数
    		//
    		CREATE TEMPORARY FUNCTION myadd AS 'com.it18zhang.hivedemo.udf.AddUDF';
    
    	5.在查询中使用自定义函数
    		$hive>select myadd(1,2)  ;
    	
    	6.定义日期函数
    		1)定义类
    		public class ToCharUDF extends UDF {
    			/**
    			 * 取出服务器的当前系统时间 2017/3/21 16:53:55
    			 */
    			public String evaluate() {
    				Date date = new Date();
    				SimpleDateFormat sdf = new SimpleDateFormat();
    				sdf.applyPattern("yyyy/MM/dd hh:mm:ss");
    				return sdf.format(date) ;
    			}
    			public String evaluate(Date date) {
    				SimpleDateFormat sdf = new SimpleDateFormat();
    				sdf.applyPattern("yyyy/MM/dd hh:mm:ss");
    				return sdf.format(date) ;
    			}
    
    			public String evaluate(Date date,String frt) {
    				SimpleDateFormat sdf = new SimpleDateFormat();
    				sdf.applyPattern(frt);
    				return sdf.format(date) ;
    			}
    		}
    
    		2)导出jar包，通过命令添加到hive的类路径(不需要重进hive)。
    			$hive>add jar /mnt/hgfs/downloads/bigdata/data/HiveDemo-1.0-SNAPSHOT.jar
    		
    		3)注册函数
    			$hive>CREATE TEMPORARY FUNCTION to_char AS 'com.it18zhang.hivedemo.udf.ToCharUDF';
    			$hive>CREATE TEMPORARY FUNCTION to_date AS 'com.it18zhang.hivedemo.udf.ToDateUDF';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTMxOTcxMzkyNQ_size_16_color_FFFFFF_t_70]: /images/20220325/80bbd81c2f744f2ab4252cf031b3c6db.png
[Link 1]: https://www.baidu.com/s?wd=%E4%B8%BB%E5%88%86%E5%8C%BA&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E5%88%97%E9%83%BD&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd