1 hive简介
1.1. 什么是Hive
Hive：由Facebook开源用于解决海量结构化日志的数据统计(海量的结构化数据的运算分析工具)。
Hive是基于Hadoop的一个数据仓库工具(服务性的软件)，可以将结构化的数据文件映射为一张表，并提供类似于SQL查询。
本质是：将Hive SQL转化成MapReduce程序或者spark程序处理数据的一种工具
1）Hive处理的数据存储在HDFS
2）Hive分析数据底层的实现是MapReduce / spark(分布式运算框架)
3）执行程序运行在Yarn上
1.2 hive的优缺点
1.2.1 优点
-
操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。HQL
-
避免了去写MapReduce，减少开发人员的学习成本。
-
Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。(历史数据)
-
Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
-
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
-
底层就是java ,提供了反射调用java类的方法
-
运行模式为MR , 比较稳定
1.2.1 缺点
1．Hive的HQL表达能力有限
（1）迭代式算法无法表达
（2）数据挖掘方面不擅长(数据挖掘和算法,机器学习)
2．Hive的效率比较低
（1）Hive自动生成的MapReduce作业，通常情况下不够智能化
（2）Hive调优比较困难，粒度较粗(快)
1.3 架构原理
1.3.1.用户接口：Client
CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）
1.3.2.元数据：Metastore
Metadata即元数据。元数据包含用Hive创建的database、table、表的字段等元信息。元数据存储在关系型数据库中。如hive内置的Derby、第三方如MySQL等。
Metastore即元数据服务，作用是：客户端连接metastore服务，metastore再去连接MySQL数据库来存取元数据。有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。
远程模式下，需要单独起metastore服务，然后每个客户端都在配置文件里配置连接到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。
在生产环境中，建议用远程模式来配置Hive Metastore。
在这种情况下，其他依赖hive的软件都可以通过Metastore访问hive。
远程模式下，需要配置hive.metastore.uris 参数来指定metastore服务运行的机器ip和端口，并且需要单独手动启动metastore服务。
1.3.3.Hadoop
使用HDFS进行存储，yarn资源调度 ,使用MapReduce进行计算。
1.3.4.驱动器：Driver
（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。
（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。
Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
1.4. Hive和数据库比较
DBMS数据库管理系统 [数据进行CRUD , 组织数据结构]
hive不是数据库, 数据处理工具
由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。
1.4.1. 查询语言
由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。
1.4.2.数据存储位置
Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。
1.4.3.数据更新
由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET修改数据。
1.4.4.索引
Hive在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些Key建立索引。Hive要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。
1.4.5.执行
Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。
1.4.6.执行延迟
Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。
1.4.7. 可扩展性
由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的（世界上最大的Hadoop 集群在 Yahoo!，2009年的规模在4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。
1.4.8.数据规模
由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。
hive是一个数仓工具(服务性质软件),并不是一个数据库
处理分析大量的静态的结构化数据 , 报表
2 hive安装部署
2.1 Hive安装地址
1．Hive官网地址
http://hive.apache.org/
2．文档查看地址
https://cwiki.apache.org/confluence/display/Hive/GettingStarted
3．下载地址
http://archive.apache.org/dist/hive/
4．github地址
https://github.com/apache/hive
2.2 hive安装部署
2.2.1 准备HDFS
2.2.2 mysql数据库准备
-
有一台mysql服务器 doit01
-
mysql要开启远程连接权限
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BXYZabxK-1631782764343)([多易教育]-hive笔记/image-20210914093956857.png)]
1) [root@doit01 ~]# service mysqld start
Starting mysqld: [ OK ]
2) 登录mysql -uroot -proot
3) set global validate_password_policy=0;
4) set global validate_password_length=1; 这个两个设置以后 密码很简单不会报错
5) 开放远程连接权限
mysql > grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
mysql > flush privileges;
6) 授权完成后，测试一下是否成功：在windows上用Navicat连接一下看是否能成功！
补充: 数据库修改密码命令
SET PASSWORD FOR 'root'@'doit001' = PASSWORD('root');
2.2.3 上传解压
tar -zxf apache-hive-3.1.2.tar.gz
2.2.4 配置
vi hive-env.sh
export HADOOP_HOME=/opt/apps/hadoop-3.1.1
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
vi hive-site.xml
<configuration>
<!-- 记录hive中的元数据信息 记录在mysql中 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://doit01:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!-- mysql的用户名和密码 -->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
</property>
<!-- hive在HDFS上的工作目录 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>hive.exec.scratchdir</name>
<value>/user/hive/tmp</value>
</property>
<property>
<name>hive.querylog.location</name>
<value>/user/hive/log</value>
</property>
<!-- 客户端远程连接的端口 -->
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
<property>
<name>hive.server2.thrift.bind.host</name>
<value>0.0.0.0</value>
</property>
<property>
<name>hive.server2.webui.host</name>
<value>0.0.0.0</value>
</property>
<!-- hive服务的页面的端口 -->
<property>
<name>hive.server2.webui.port</name>
<value>10002</value>
</property>
<property>
<name>hive.server2.long.polling.timeout</name>
<value>5000</value>
</property>
<property>
<name>hive.server2.enable.doAs</name>
<value>true</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
<property>
<name>hive.execution.engine</name>
<value>mr</value>
</property>
<!-- 添加元数据服务配置 -->
<property>
<name>hive.metastore.local</name>
<value>false</value>
<description>controls whether to connect to remove metastore server or open a new metastore server in Hive Client JVM</description>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://doit01:9083</value>
</property>
</configuration>
vi /opt/apps/hadoop-3.1.1/etc/hadoop/core-site.xml 开启hive操作hadoop的权限
<property>
<name>dfs.permissions.enabled</name>
<value>false</value>
</property>
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
2.2.5 复制mysql驱动包
在hive的lib包中默认没有mysql的驱动包 (Derby); 拷贝一个mysql的jdbc驱动jar包到hive的lib目录中
2.2.6 配置hive系统环境变量
vi /etc/profile
export HIVE_HOME=/opt/apps/hive-3.1.2
export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/sbin:$HADOOP_HOME/bin:$HIVE_HOME/bin
2.2.7 hive初始化
将hive的原数据信息初始化到mysql中
${HIVE_HOME}/bin/schematool -initSchema -dbType mysql
Initialization script completed
schemaTool completed
在mysql数据库中发现生成一个新的hive数据库 , 说明hive初始化成功
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zii0DMSM-1631782764345)([多易教育]-hive笔记/image-20210914101218194.png)]
2.2.8 启动元数据服务
hive --service metastore & 后台启动元数据服务
检查启动额端口
netstat -nltp | grep 9083 端口存在说明元数据服务启动成功
- hive 进入到操作hive的客户端
2.3 客户端
2.3.1 本地模式客户端
直接执行bin下的hive就可以在shell窗口中启动hive的客户端
bin/hive
show databases ;
create database doit_db ;
这种本地模式下的客户端,显示数据的格式不是很友好!
退出客户端命令
hive(default)>exit;
hive(default)>quit;
2.3.2 远程客户端连接
前提 开启元数据服务 9083端口
- 开启hive2的服务 hiveserver2 前台启动
- 9083 10000 10002
hiveserver2 & -- 后台启动hiveserver2服务
netstat -nltp | grep 10000 启动成功以后 检查服务端口10000
tcp6 0 0 :::10000 :::* LISTEN 21663/java
查看WEBUI页面http://linux01:10002/
- 可以使用shell客户端户JDBC远程连接
在hive的bin目录下提供了一个网络连接工具 beeline
[root@linux01 bin]# beeline
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/apps/hive-3.1.2/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/apps/hadoop-3.1.1/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]
Beeline version 3.1.2 by Apache Hive
beeline>
然后输入相关的连接URI
beeline> !connect jdbc:hive2://linux01:10000
Connecting to jdbc:hive2://linux01:10000
Enter username for jdbc:hive2://linux01:10000: root
Enter password for jdbc:hive2://linux01:10000: 回车
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://linux01:10000>
远程连接窗口显示的数据比较友好
2.3.3 非交互式操作
bin/hive -help
usage: hive
-d,--define <key=value> Variable substitution to apply to Hive
commands. e.g. -d A=B or --define A=B
--database <databasename> Specify the database to use
-e <quoted-query-string> SQL from command line
-f <filename> SQL from files
-H,--help Print help information
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable substitution to apply to Hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)
1．“-e”不进入hive的交互窗口执行sql语句
[root@doit01hive]$ bin/hive -e “select id from student;”
以后可以将hive -e指令编写到shell脚本中 ,调度SQL语句
2．“-f”执行脚本中sql语句
（1）在/opt/module/datas目录下创建hivef.sql文件
[root@doit01datas]$ touch hivef.sql
文件中写入正确的sql语句
select *from student;
（2）执行文件中的sql语句
[root@doit01hive]$ bin/hive -f /opt/module/datas/hivef.sql
（3）执行文件中的sql语句并将结果写入文件中
[root@doit01hive]$ bin/hive -f /opt/module/datas/hivef.sql > /opt/module/datas/hive_result.txt
3 hive-SQL快速入门
HQL 支持标准的SQL语法, 具备自己的特殊语法!
3.1 hive处理HDFS数据演示
{ 基本步骤 }
- 在HDFS上准备一个结构化的文件
- 根据文件中数据结构创建一张hive表
- 导入数据 ***
- 查询表中的数据
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
建表
create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t';
导入数据
load data inpath '' into table emp ;
3.2 基本语法回顾
-- 显示库
show databases ;
-- 创建库
create database db_doit25 ;
-- 切换库
use db_doit25 ;
-- 查看当前正在使用的数据库 current_database()
select current_database() ;
-- 修改库
alter database db_name set dbproperties('K'='V')
-- 查看数据库属性
desc database db_doit25 ;
desc database extended db_doit25 ; -- 详细查看
-- 删除库
drop database dn_doit25 ;
-- 创建表
方式1
create table tb_name(
col1 datatype ,
col2 datatype
...
)
row format delimited fileds terminated by '分割符'
location 'HDFS路径';
方式2
create table new_tbl like old_tbl ; -- 复制表结构创建表
方式3 保存处理结果到新的表中 新表有结构和数据
create table tb_emp3 as select eno , name , job from tb_emp ;
-- 查看表
show tables ;
show tables like 'tb.*'
show tables in db_doit25 ; 或者 show tables like 'db_doit25.*' ;
-- 查看表结构
desc tb_emp ;
-- 退出客户端
exit ;
quit ;
远程连接 ctrl+c
-- 查看系统中支持的函数
show functions ;
select 函数名(参数) ;
4 HQL语法详解
4.1 基本数据类型
| Hive数据类型 | Java数据类型 | 长度 | 例子 |
|---|---|---|---|
| TINYINT | byte | 1byte有符号整数 | 20 |
| SMALLINT | short | 2byte有符号整数 | 20 |
| INT | int | 4byte有符号整数 | 20 |
| BIGINT | long | 8byte有符号整数 | 20 |
| BOOLEAN | boolean | 布尔类型，true或者false | TRUE FALSE |
| FLOAT | float | 单精度浮点数 | 3.14159 |
| DOUBLE | double | 双精度浮点数 | 3.14159 |
| STRING | string | 字符系列。可以指定字符集。可以使用单引号或者双引号。 | ‘now is the time’ “for all good men” |
| TIMESTAMP | 时间类型 | ||
| BINARY | 字节数组 |
对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。
强制数据类型转换
hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。
1．隐式类型转换规则如下
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。
（4）BOOLEAN类型不可以转换为任何其它的类型。
2．可以使用CAST操作显示进行数据类型转换
例如**CAST(‘1’ AS INT)**将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。
4.2 基本建表语法
建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 分区
[CLUSTERED BY (col_name, col_name, ...) 分桶
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format] row format delimited fields terminated by “分隔符”
[STORED AS file_format]
[LOCATION hdfs_path]
字段说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY 创建分区表
（5）CLUSTERED BY 创建分桶表
（6）SORTED BY 不常用
（7）ROW FORMAT
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, …)]
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9） LOCATION ：指定表在HDFS上的存储位置。
（10）LIKE允许用户复制现有的表结构，但是不复制数据。
4.2.1 内部表,管理表/外部表
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
-
在hive中建表 , 默认使用的是ManagerTable , 管理表/内部表
删除表的时候 ,默认删除表对应的文件夹 , 所以数据也会被删除
假如遇到公共的数据(多张表使用一个文件夹下的数据, 删除表数据就删除了!!)
-
在建表的时候可以使用关键字 ,external. 创建外部表
删除表的时候 ,默认表对应的文件夹 不会被删除,
- 原始数据特别重要, 公共数据要使用外部表
create external table tb_user3(
id int ,
name string ,
age int
)
row format delimited fields terminated by ","
location '/data/user/' ;
create external table tb_user4(
id int ,
name string ,
age int
)
row format delimited fields terminated by ","
location '/data/user/' ;
- 管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
管理表的创建和使用,管理表直接管理数据, 管理表的目录和表一致
create table tb_manage_user(
id int ,
name string ,
age int
)
row format delimited fields terminated by "," ;
直接将数据put到表目录下就可以导入数据
注意导入的数据格式和表结构对应
1,zss,23
2,lss,33
3,ww,21
查看表的详细信息
desc formatted tb_manage_user ;
4.2.3 管理表和外部表转换
（1）查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
（2）修改内部表student2为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
（3）查询表的类型
hive (default)> desc formatted student2;
Table Type: EXTERNAL_TABLE
（4）修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE'); -- 要求KV的大小写
（5）查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！
4.3 数据导入
4.3.0 直接将数据put到表目录下
hdfs dfs -put user.txt /user/hive/warehouse/db_doit25.db/tb_manage_user/b.txt
-- 将HDFS的文件移动到表目录下 cp mv
hdfs dfs -mv /user.txt /user/hive/warehouse/db_doit25.db/tb_manage_user/b.txt
在hive的客户端也提供了操作文件系统的命令
0: jdbc:hive2://linux01:10000> dfs -ls /user/hive/warehouse ;
+----------------------------------------------------+
| DFS Output |
+----------------------------------------------------+
| Found 4 items |
| drwxrwxrwx - root supergroup 0 2021-08-05 22:14 /user/hive/warehouse/db_doit25.db |
| drwxrwxrwx - root supergroup 0 2021-08-05 03:18 /user/hive/warehouse/tb_demo1 |
| drwxrwxrwx - root supergroup 0 2021-08-05 06:01 /user/hive/warehouse/tb_emp2 |
| drwxrwxrwx - root supergroup 0 2021-08-05 06:03 /user/hive/warehouse/tb_emp3 |
+----------------------------------------------------+
4.3.1向表中装载数据（Load）
本地数据导入分区表
LOAD DATA LOCAL INPATH '/目录' OVERWRITE INTO TABLE 表名 PARTITION (ds='2008-08-15');
LOAD DATA LOCAL INPATH '/目录' OVERWRITE INTO TABLE 表名 PARTITION (ds='2008-08-08');
数据
oid001,uid001,120
oid002,uid001,130
oid003,uid002,110
oid004,uid003,88
本地数据导入
create table tb_orders(
oid string ,
uid string ,
money double
)
row format delimited fields terminated by ',' ;
-- 本地数据导入
load data local inpath '/data/orders.csv' into table tb_orders ;
select * from tb_orders ;
+----------------+----------------+------------------+
| tb_orders.oid | tb_orders.uid | tb_orders.money |
+----------------+----------------+------------------+
| oid001 | uid001 | 120.0 |
| oid002 | uid001 | 130.0 |
| oid003 | uid002 | 110.0 |
| oid004 | uid003 | 88.0 |
+----------------+----------------+------------------+
导入 HDFS上的数据
create table tb_orders_load_hdfs(
oid string ,
uid string ,
money double
)
row format delimited fields terminated by ',' ;
-- HDFS上的数据导入
load data inpath '/data/orders.csv' into table tb_orders_load_hdfs ;
覆盖导入 overwrite into table
load data local inpath '/data/orders.csv' overwrite into table 表 ;
load data inpath '/data/orders.csv' overwrite into table 表 ;
4.3.2 插入数据（Insert）
1) 使用insert into values
insert into tb_orders values('oid010','uid003',98),(),() ;
每次insert都会在HDFS中生成小文件, 影响性能 ! 不建议使用
2) insert into select
将查询结果保存在已经存在的一张表中 增量保存处理结果
insert into tb_orders_back select * from tb_orders where oid > 'oid003' ;
+---------------------+---------------------+-----------------------+
| tb_orders_back.oid | tb_orders_back.uid | tb_orders_back.money |
+---------------------+---------------------+-----------------------+
| oid008 | uid003 | 99.0 |
| oid009 | uid003 | 98.0 |
| oid010 | uid003 | 98.0 |
| oid004 | uid003 | 88.0 |
| oid005 | uid003 | 99.0 |
| oid006 | uid003 | 11.0 |
| oid007 | uid004 | 90.0 |
+---------------------+---------------------+-----------------------+
insert into tb_orders_back select * from tb_orders where oid <= 'oid003' ;
+---------------------+---------------------+-----------------------+
| tb_orders_back.oid | tb_orders_back.uid | tb_orders_back.money |
+---------------------+---------------------+-----------------------+
| oid008 | uid003 | 99.0 |
| oid009 | uid003 | 98.0 |
| oid010 | uid003 | 98.0 |
| oid004 | uid003 | 88.0 |
| oid005 | uid003 | 99.0 |
| oid006 | uid003 | 11.0 |
| oid007 | uid004 | 90.0 |
| oid001 | uid001 | 120.0 |
| oid002 | uid001 | 130.0 |
| oid003 | uid002 | 110.0 |
+---------------------+---------------------+-----------------------+
-- 覆盖的方式插入数据
insert overwrite table tb_orders_back select * from tb_orders where oid <= 'oid003' ;
+---------------------+---------------------+-----------------------+
| tb_orders_back.oid | tb_orders_back.uid | tb_orders_back.money |
+---------------------+---------------------+-----------------------+
| oid001 | uid001 | 120.0 |
| oid002 | uid001 | 130.0 |
| oid003 | uid002 | 110.0 |
+---------------------+---------------------+-----------------------+
4.3.3 查询语句中创建表并加载数据（As Select）
create table tb_emp3 as select eno , name , job from tb_emp ;
4.3.4 创建表时通过Location指定加载数据路径
一般适用于外部表
create table tb_user(
id int ,
name string ,
age int
)
row format delimited fields terminated by ","
location '/data/user/' ;
4.3.5 Import数据到指定Hive表中
用于数据的备份和迁移 , 导入的数据必须是export导出的数据
-- 1导出数据
export table tb_orders to
'/user/hive/warehouse/output/orders';
-- 2 建表
create table tb_orders_import like tb_orders ;
-- 3 导入
import table tb_orders_import from '/user/hive/warehouse/output/orders';
-- 4 查询
select * from tb_orders_import ;
+-----------------------+-----------------------+-------------------------+
| tb_orders_import.oid | tb_orders_import.uid | tb_orders_import.money |
+-----------------------+-----------------------+-------------------------+
| oid008 | uid003 | 99.0 |
| oid009 | uid003 | 98.0 |
| oid010 | uid003 | 98.0 |
| oid001 | uid001 | 120.0 |
| oid002 | uid001 | 130.0 |
| oid003 | uid002 | 110.0 |
| oid004 | uid003 | 88.0 |
| oid005 | uid003 | 99.0 |
| oid006 | uid003 | 11.0 |
| oid007 | uid004 | 90.0 |
+-----------------------+-----------------------+-------------------------+
导入数据总结
- hdfs dfs -put
- hdfs dfs mv / cp
- load data [local] inpath ‘’ into table 表
- insert into 表 values()
- insert into tb_name select from…
- create table tb_name as select from…
- import table tb_name from
4.4 数据导出
4.4.1 Insert导出
将a表的数据导出到b表中
insert into b select * from a;
将表数据导出到文件夹中
-- 将查询的结果保存在文件夹中
insert overwrite local directory '/data/output'
select * from tb_orders;
导出的数据默认使用隐藏分隔符 , 可以自己指定数据属性的分隔符
insert overwrite local directory '/data/output2'
row format delimited fields terminated by ','
select * from tb_orders;
insert overwrite local directory '/data/output3'
row format delimited fields terminated by '-'
select * from tb_orders;
可以将数据导出到HDFS上
insert overwrite directory '/data/output3'
row format delimited fields terminated by '-'
select * from tb_orders;
4.4.2 Hadoop命令导出到本地
hdfs dfs -get
4.4.3 hive Shell 命令导出***
hive -e "SQL1;SQL2;" -- 直接执行SQL语句
hive -e " select * from 库.表 ;" >> a.txt 保存sql结果
hive -f my_sql.sh > a.txt
4.4.4 Export导出到HDFS上
export table tb_orders to
'/user/hive/warehouse/output/orders';
数据的迁移 , 导出的数据 ,保留了原始的数据存储目录(表,分区,分桶文件)
4.4.5 导出工具 sqoop/dataX
数据迁移工具
补充:清除表中数据
注意：Truncate只能删除管理表，不能删除外部表中数据
truncate table student;
4.5 分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
将表中的数据以某种维度进行划分文件夹管理 ,当要查询数据的时候,根据维度直接加载对应文件夹下的数据! 不用加载整张表所有的数据再进行过滤, 从而提升处理数据的效率!
select * from tb_a where city = ' bj ' ;
在表目录中 ,有以地域为数据的文件
tb_a的文件夹下
bj
bj.txt
nj
nj.txt
sh
sh.txt
1 一级静态分区表演示
-- 建表
create table 表1(
id int ,
name string ,
city string
)
-- 指定分区
partitioned by (city_name string)
row format delimited fields terminated by ',' ;
+--------------------------+------------+----------+
| col_name | data_type | comment |
+--------------------------+------------+----------+
| id | int | |
| name | string | |
| city | string | |
| city_name | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| city_name | string | |
+--------------------------+------------+----------+
-- 插入数据时设置分区
load data local inpath '/data/bj.txt' into table 表1 partition(city_name='bj') ;
load data local inpath '/data/nj.txt' into table 表1 partition(city_name='nj') ;
load data local inpath '/data/sh.txt' into table 表1 partition(city_name='sh') ;
可以查询SQL的执行计划explain
- 已经存在了静态的数据在不同的文件中
- 加载数据到不同的分区下(文件夹)
- 在查询的时候指定分区字段作为查询字段维度 [可以直接定位数据的分区文件夹,加载数据返回,不需要加载所有数据再进行过滤]
2 二级静态分区表演示
日期进行分区 / 8月的数据
2021-07-01.log
a,2021-07-01
b,2021-07-01
c,2021-07-01
d,2021-07-01
e,2021-07-01
2021-07-02.log
a,2021-07-02
b,2021-07-02
c,2021-07-02
d,2021-07-02
e,2021-07-02
2021-08-01.log
a,2021-08-01
b,2021-08-01
c,2021-08-01
d,2021-08-01
e,2021-08-01
2021-08-02.log
a,2021-08-02
b,2021-08-02
c,2021-08-02
d,2021-08-02
e,2021-08-02
2021-08-03.log
a,2021-08-03
b,2021-08-03
c,2021-08-03
d,2021-08-03
e,2021-08-03
-- 创建表
create table tb_static_partition_demo2(
name string ,
`date` string
)
-- 指定分区字段
partitioned by (`month` string , `day` string)
row format delimited fields terminated by ',' ;
+--------------------------+------------+----------+
| col_name | data_type | comment |
+--------------------------+------------+----------+
| name | string | |
| date | string | |
| month | string | |
| day | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| month | string | |
| day | string | |
+--------------------------+------------+----------+
-- 插入数据时设置分区
load data local inpath '/data/2021-07-01.log' into table tb_static_partition_demo2 partition(`month`='2021-07' , `day`='01') ;
load data local inpath '/data/2021-07-02.log' into table tb_static_partition_demo2 partition(`month`='2021-07' , `day`='02') ;
load data local inpath '/data/2021-08-01.log' into table tb_static_partition_demo2 partition(`month`='2021-08' , `day`='01') ;
load data local inpath '/data/2021-08-02.log' into table tb_static_partition_demo2 partition(`month`='2021-08' , `day`='02') ;
load data local inpath '/data/2021-08-03.log' into table tb_static_partition_demo2 partition(`month`='2021-08' , `day`='03') ;
3 分区表修改操作
注意: 静态分区,直接load文件到指定的分区中 ,不会对文件中的数据内容进行校验 , 有可能出现数据错误!
4.5.2 动态分区
按照某个字段自动的将数据加载到指定的分区中
数据没有按照我们的规则存储在不同的文件中/而是在同一个文件中
期望根据一个字段的值进行分区
操作步骤
- 创建普通表 导入数据
- 开启动态分区
- 开启非严格模式
- 创建分区表
- 通过insert into partition select from 语法导入数据
1,zss,bj
2,lss,bj
3,tg,sh
4,xg,bj
5,ln,sd
6,yg,sh
- 创建普通表 导入数据
drop table tb_dynamic_partition_source ;
create table tb_dynamic_partition_source(
id string ,
name string ,
city string
)
row format delimited fields terminated by ',' ;
load data local inpath '/data/city.txt' into table tb_dynamic_partition_source ;
+----------------+------------------+------------------+
| tb_teacher.id | tb_teacher.name | tb_teacher.city |
+----------------+------------------+------------------+
| 1 | zss | bj |
| 2 | lss | bj |
| 3 | tg | sh |
| 4 | xg | bj |
| 5 | ln | sd |
| 6 | yg | sh |
+----------------+------------------+------------------+
- 设置相关参数
set hive.exec.dynamic.partition=true; -- 开启动态分区
set hive.exec.dynamic.partition.mode=nonstrick; -- 开启非严格模式
- 创建分区表
create table tb_name(
id string ,
name string ,
city string
)
partitioned by (ct string) ;
-- row format delimited fields terminated by ',' 从HDFS上读取数据 切割 将数据映射到表
4 导入数据到分区表中
insert into table tb_name partition(ct)
select id , name ,city , city as ct from tb_name ;
show partitions tb_name ;
+------------+
| partition |
+------------+
| ct=bj |
| ct=sd |
| ct=sh |
+------------+
4.5.3 修改分区表结构
-- 查看表的所有分区
show partitions tb_name ;
-- 添加分区 load数据的时候自动的创建没有的分区
alter table dept_partition add partition(month='201705') partition(month='201704');
-- 删除分区
alter table tb_static_partition_demo1 drop partition(city_name='bj');
-- 分区表不会对分区文件夹中的数据内容校验 , 删除一个分区重新导入数据,对分区内的错误数据进行校正
alter table tb_static_partition_demo1 drop partition(city_name='bj');
load data local inpath '/data/bj.txt' into table tb_static_partition_demo1 partition(city_name='bj') ;
4.6 分桶表
对Hive(Inceptor)表分桶可以将表中记录按分桶键(字段)的哈希值分散进多个文件中，这些小文件称为桶。桶以文件为单位管理数据!分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若*分的另一个技术。
- 如果查询的维度是分桶字段 ,查询的时候先确定数据所在的桶 , 提升查询效率
- 进行join的时候 ,关联字段就是分桶字段 ,只有相同的桶之间的数据进行关联 ,从而提升关联效率
操作步骤
1创建普通表 导入数据
2 创建分桶表
3 设置相关参数
4 导入数据到分桶表中
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16
-- 1 创建普通表
create table tb_student(
sid int ,
name string
)
row format delimited fields terminated by '\t' ;
load data local inpath '/data/student.txt' into table tb_student ;
+-----------------+------------------+
| tb_student.sid | tb_student.name |
+-----------------+------------------+
| 1001 | ss1 |
| 1002 | ss2 |
| 1003 | ss3 |
| 1004 | ss4 |
| 1005 | ss5 |
| 1006 | ss6 |
| 1007 | ss7 |
| 1008 | ss8 |
| 1009 | ss9 |
| 1010 | ss10 |
| 1011 | ss11 |
| 1012 | ss12 |
| 1013 | ss13 |
| 1014 | ss14 |
| 1015 | ss15 |
| 1016 | ss16 |
+-----------------+------------------+
-- 2 创建分桶表
create table tb_buck_student(
sid int ,
name string
)
clustered by (name)
into 3 buckets ;
-- 3 设置参数
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
-- 4 导入数据到分桶表中
insert into table tb_buck_student
select sid, name from tb_student;
总结:
分桶表实则就是将表中的数据分文件管理 , 某个字段的hashcode% 桶数clustered by (name)
- join的时候 相同规则的桶数据进行关联
- 查询数据的时候可以先确定数据的桶 , 加载数据
- 在实际使用时,可以分区和分桶同时使用
补充:抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
select * from buck_demo tablesample(bucket 1 out of 4 on id);
4.7 集合数据类型
| 数据类型 | 描述 | 语法示例 |
|---|---|---|
| STRUCT(结构体)对象 | 和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 | struct() |
| MAP 映射 | MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素 | map() |
| ARRAY 数组 | 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用 | Array() |
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。
数据结构
{
"name": "songsong",
"friends": ["bingbing" , "lili"] , //列表Array,
"children": { //键值Map,
"xiao song": 18 ,
"xiaoxiao song": 14
}
"address": { //结构Struct,
"street": "hui long guan" ,
"city": "beijing"
}
}
本地数据
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
操作
-- 创建表
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的分隔符
+-------------+----------------------+--------------------------------------+----------------------------------------------+
| tb_dt.name | tb_dt.fs | tb_dt.chs | tb_dt.addr |
+-------------+----------------------+--------------------------------------+----------------------------------------------+
| songsong | ["bingbing","lili"] | {"xiao song":18,"xiaoxiao song":19} | {"street":"hui long guan","city":"beijing"} |
| yangyang | ["caicai","susu"] | {"xiao yang":18,"xiaoxiao yang":19} | {"street":"chao yang","city":"beijing"} |
+-------------+----------------------+--------------------------------------+----------------------------------------------+
-- 操作数组
- arr[index] 取值
- size(arr) 长度
- 数组角标越界返回NULL
- 数组的定义
- array(e,e2,e3...)
- split(str , regex) 切割字符串
- collect_set collect_list 收集组内的数据到数组
- explode(arr) 炸开
--操作map
map
map_keys
map_value
select name , map_keys(chs) keys , map_values(chs) vs from tb_dt ;
+-----------+--------------------------------+----------+
| name | keys | vs |
+-----------+--------------------------------+----------+
| songsong | ["xiao song","xiaoxiao song"] | [18,19] |
| yangyang | ["xiao yang","xiaoxiao yang"] | [18,19] |
+-----------+--------------------------------+----------+
---根据key获取value值 , 如果不存在 返回NULL
select
name ,
chs['xiao song']
from
tb_dt ;
+-----------+-------+
| name | _c1 |
+-----------+-------+
| songsong | 18 |
| yangyang | NULL |
+-----------+-------+
-- map长度
select size(chs) from tb_dt ;
-- 将字符串转换成 map集合 参数一字符串 参数二 分隔符 参数三 k v 分隔符
select str_to_map('zss:23,lss:22',',' ,':') ;
+--------------------------+
| _c0 |
+--------------------------+
| {"zss":"23","lss":"22"} |
-----------------------------
-- 炸开explode(chs)
select
explode(chs) as (k,v) -- 别名
from
tb_dt ;
+----------------+--------+
| k | v |
+----------------+--------+
| xiao song | 18 |
| xiaoxiao song | 19 |
| xiao yang | 18 |
| xiaoxiao yang | 19 |
+----------------+--------+
--操作struct
addr.street
addr.city
select name , addr.city , addr.street from tb_dt ;
+-----------+----------+----------------+
| name | city | street |
+-----------+----------+----------------+
| songsong | beijing | hui long guan |
| yangyang | beijing | chao yang |
+-----------+----------+----------------+
4.8 查询回顾
4.8.1 基本查询语法
1 全表和特定列查询
1．全表查询 在开发中不要出现 *
hive (default)> select * from emp; XXXX
2．选择特定列查询
hive (default)> select empno, ename from emp;
注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。
2 列别名
1．重命名一个列
2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’
4．案例实操
查询名称和部门
hive (default)> select ename AS name, deptno dn from emp;
select
id ,
name ,
age ,
age+1 as new_age ,
upper(name) as upper_name
from
tb_user ;
-- 在底层 HIVE优化
select
id ,
name
from
(select
id ,
name
from
tb_user where id >1
) t ;
+-----+-------+
| id | name |
+-----+-------+
| 2 | lss |
| 3 | ww |
+-----+-------+
3 算术运算符
| 运算符 | 描述 |
|---|---|
| A+B | A和B 相加 |
| A-B | A减去B |
| A*B | A和B 相乘 |
| A/B | A除以B |
| A%B | A对B取余 |
| A&B | A和B按位取与 |
| A|B | A和B按位取或 |
| A^B | A和B按位取异或 |
4 常用聚合函数(聚合函数 返回一条结果)
自动的忽略null值
1．求总行数（count）
hive (default)> select count(*) cnt from emp;
2．求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3．求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4．求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp;
5．求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;
-- collect_list --->array
-- collect_set --->array
5 Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from emp limit 5;
参数一 起始的行数 0开始计数 参数2 每页显示的条数
hive (default)> select * from emp limit 2, 5;
6 Where语句
- SQL 是一种结构化查询语言 , 面向集合的编程语言
select
id , -- 变量
name ,
age + 1 ,
"hello" ,
current_date()
--- 4
from -- 1
tb_name -- 表数据
where id < 10000 -- 2
group by -- 3
having -- 5
order by -- 6
limit 5 -- 7
-- 统计emp表中每个部门的最高工资
select
max(sal) max_sal
from
tb_emp
where dptno = 10
;
select
dptno,
max(sal) max_sal
from
tb_emp
where dptno > 10
group by dptno
having max_sal >=3000
;
-- 每个人的年薪
select
name ,
-- NUll和任何数运算返回null
(sal+ if(comm is null ,0 , comm )) * 12 as y_ammount
from
tb_emp ;
where 对原始数据进行筛选过滤
1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句
3．案例实操
查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
7 比较运算符（Between/In/ Is Null）
下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE |
| A<=>B | 基本数据类型 | 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL |
| A<>B, A!=B | 基本数据类型 | A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE |
| A<B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE |
| A<=B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE |
| A>B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE |
| A>=B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE |
| A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 |
| A IS NULL | 所有数据类型 | 如果A等于NULL，则返回TRUE，反之返回FALSE |
| A IS NOT NULL | 所有数据类型 | 如果A不等于NULL，则返回TRUE，反之返回FALSE |
| IN(数值1, 数值2) | 所有数据类型 | 使用 IN运算显示列表中的值 |
| A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。 |
| A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
（1）查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
（2）查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
（3）查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
（4）查询工资是1500或5000的员工信息 or in (1500 , 5000) sal=1500 or sal =5000
hive (default)> select * from emp where sal IN (1500, 5000);
8 Like和RLike
1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
（1）查找以2开头薪水的员工信息
hive (default)> select * from emp where sal LIKE '2%';
（2）查找第二个数值为2的薪水的员工信息
hive (default)> select * from emp where sal LIKE '_2%';
（3）查找薪水中含有2的员工信息
hive (default)> select * from emp where sal RLIKE '[2]';
9 逻辑运算符（And/Or/Not）
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
（1）查询薪水大于1000，部门是30
hive (default)> select * from emp where sal>1000 and deptno=30;
（2）查询薪水大于1000，或者部门是30
hive (default)> select * from emp where sal>1000 or deptno=30;
（3）查询除了20部门和30部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);
10 group by
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
11 having
having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
4.8.3 关联查询
1 多表连接
2 笛卡尔积
3 内连接 inner join on , join
4 左外连接 left join
5 右外连接 right join
6 全外连接 full join
-- tb_user数据
1,zss,23
2,lss,33
3,lny,49
4,tgg,43
drop table tb_user ;
-- 创建表
create table tb_user(
id int ,
name string ,
age int
)
row format delimited fields terminated by ',' ;
-- 插入数据
load data local inpath '/data/user.txt' into table tb_user ;
-- tb_orders数据
o001,1
o002,3
o003,2
o005,5
drop table tb_orders ;
-- 创建表
create table tb_orders(
id string ,
uid int
)
row format delimited fields terminated by ',' ;
-- 插入数据
load data local inpath '/data/orders.txt' into table tb_orders ;
-- 查询
select * from tb_user ;
+-------------+---------------+--------------+
| tb_user.id | tb_user.name | tb_user.age |
+-------------+---------------+--------------+
| 1 | zss | 23 |
| 2 | lss | 33 |
| 3 | lny | 49 |
| 4 | tgg | 43 |
+-------------+---------------+--------------+
--查询
select * from tb_orders ;
+---------------+----------------+
| tb_orders.id | tb_orders.uid |
+---------------+----------------+
| o001 | 1 |
| o002 | 3 |
| o003 | 2 |
| o005 | 5 |
+---------------+----------------+
-- 左外连接
select
*
from
tb_user
left join
tb_orders
on tb_user.id = tb_orders.uid ;
+-------------+---------------+--------------+---------------+----------------+
| tb_user.id | tb_user.name | tb_user.age | tb_orders.id | tb_orders.uid |
+-------------+---------------+--------------+---------------+----------------+
| 1 | zss | 23 | o001 | 1 |
| 2 | lss | 33 | o003 | 2 |
| 3 | lny | 49 | o002 | 3 |
| 4 | tgg | 43 | NULL | NULL |
+-------------+---------------+--------------+---------------+----------------
-- 右外连接
select
*
from
tb_user
right join
tb_orders
on tb_user.id = tb_orders.uid ;
+-------------+---------------+--------------+---------------+----------------+
| tb_user.id | tb_user.name | tb_user.age | tb_orders.id | tb_orders.uid |
+-------------+---------------+--------------+---------------+----------------+
| 1 | zss | 23 | o001 | 1 |
| 3 | lny | 49 | o002 | 3 |
| 2 | lss | 33 | o003 | 2 |
| NULL | NULL | NULL | o005 | 5 |
+-------------+---------------+--------------+---------------+----------------
-- 全外连接
select
*
from
tb_user
full join
tb_orders
on tb_user.id = tb_orders.uid ;
+-------------+---------------+--------------+---------------+----------------+
| tb_user.id | tb_user.name | tb_user.age | tb_orders.id | tb_orders.uid |
+-------------+---------------+--------------+---------------+----------------+
| 1 | zss | 23 | o001 | 1 |
| 2 | lss | 33 | o003 | 2 |
| 3 | lny | 49 | o002 | 3 |
| 4 | tgg | 43 | NULL | NULL |
| NULL | NULL | NULL | o005 | 5 |
+-------------+---------------+--------------+---------------+----------------+
7 连接显示union去重 / union all不去重
-- union all 不去重显示
select * from tb_user where id >=2
union all
select * from tb_user where id <= 3 ;
+---------+-----------+----------+
| _u1.id | _u1.name | _u1.age |
+---------+-----------+----------+
| 1 | zss | 23 |
| 2 | lss | 33 |
| 2 | lss | 33 |
| 3 | lny | 49 |
| 3 | lny | 49 |
| 4 | tgg | 43 |
+---------+-----------+----------+
-- union 去重显示
select * from tb_user where id >=2
union
select * from tb_user where id <= 3 ;
+---------+-----------+----------+
| _u1.id | _u1.name | _u1.age |
+---------+-----------+----------+
| 1 | zss | 23 |
| 2 | lss | 33 |
| 3 | lny | 49 |
| 4 | tgg | 43 |
+---------+-----------+----------+
8 left semi join
in(1,2,3,4,5)的优化
select * from tb_user ;
+-------------+---------------+--------------+
| tb_user.id | tb_user.name | tb_user.age |
+-------------+---------------+--------------+
| 1 | zss | 23 |
| 1 | zss | 23 |
| 2 | lss | 33 |
| 3 | lny | 49 |
| 4 | tgg | 43 |
+-------------+---------------+--------------+
select * from tb_user2 ;
+--------------+----------------+---------------+
| tb_user2.id | tb_user2.name | tb_user2.age |
+--------------+----------------+---------------+
| 1 | zss | 23 |
| 2 | lss | 33 |
+--------------+----------------+---------------+
select
tb_user.* ,
tb_user2.id as id2
from
tb_user
join
tb_user2
on
tb_user.id = tb_user2.id
+-------------+---------------+--------------+------+
| tb_user.id | tb_user.name | tb_user.age | id2 |
+-------------+---------------+--------------+------+
| 1 | zss | 23 | 1 |
| 1 | zss | 23 | 1 |
| 2 | lss | 33 | 2 |
+-------------+---------------+--------------+------+
select
*
from
tb_user
left semi join
tb_user2
on
tb_user.id = tb_user2.id ;
4.8.4 case when语法
case 字段
when 值 then
when 值 then
when 值 then
else
end as 别名
case
when expr1 and/or expr2 then
when expr1 and/or expr2 then
when expr1 and/or expr2 then
else
end as 别名
4.9 排序
4.9.1全局排序
使用 ORDER BY 语句全局排序
- ASC（ascend）: 升序（默认）
- DESC（descend）: 降序
可以指定多个排序字段
select * from emp order by deptno , job , sal desc
可以按照查询别名进行排序
select ename, sal*2 twosal from emp order by twosal;
4.9.2 区内排序
Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。
1．设置reduce个数
hive (default)> set mapreduce.job.reduces=3;
2．查看设置reduce个数
hive (default)> set mapreduce.job.reduces;
3．根据部门编号降序查看员工信息 指定分区字段
hive (default)> select * from emp distribute by dptno sort by empno desc;
4．将查询结果导入到文件中（按照部门编号降序排序)
hive (default)> insert overwrite local directory ‘/hive/sb’
select * from emp sort by deptno desc;
上面使用sortby对数据进行区内排序 , 但是没有指定分区字段 , 我们可以指定分区字段
distribute By 指定分区字段 , 按照字段的值的hashcode进行分区 , 然后使用sort by分区内排序
先按照部门编号分区，再按照员工编号降序排序。
set mapreduce.job.reduces=3;
insert overwrite local directory '/root/data/distribute-result' select * from emp distribute by deptno sort by empno desc;
当分区字段和排序字段一致的时候,可以使用cluster by 来简化 distribute by 和 sort by . 但是注意的是cluster by 只能按照字段的升序排列 ,不能指定排序规则为ASC或者DESC。
-- 这两种书写方式是一样的
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
总结
order by 对结果进行全局排序 limit 3
sort by 分区内排序 asc desc
distribute by 指定分区字段
cluster by 当分区字段和排序字段是同一个字段 ,并且是升序 可以使用 cluster by替代
4.10 行转列
函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。 将某列数据转换成数组
COLLECT_LIST(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行汇总，产生array类型字段。 将某列数据转换成数组
代码演示
-- 数据
name gender job
涛哥 M T
娜娜 F S
老赵 M T
小刘 F T
老娜 F S
-- 获取如下结果
S,F 娜娜|老娜
T,F 小刘
T,M 涛哥|老赵
drop table tb_teacher ;
create table tb_teacher(
name string,
gender string,
job string)
row format delimited fields terminated by "\t";
load data local inpath "/data/teacher.tsv" into table tb_teacher;
+------------------+--------------------+-----------------+
| tb_teacher.name | tb_teacher.gender | tb_teacher.job |
+------------------+--------------------+-----------------+
| 涛哥 | M | T |
| 娜娜 | F | S |
| 老赵 | M | T |
| 小刘 | F | T |
| 老娜 | F | S |
+------------------+--------------------+-----------------+
-- concat(字符串,字符串) 字符串拼接
-- concat_ws(分隔符,数组) 数组拼接
-- collect_list 收集组内数据到数组中
-- collect_set 收集组内数据到数组中 去重
select
concat(job,',',gender),
concat_ws('|',collect_list(name))
from tb_teacher
group by job , gender;
"S,F",娜娜|老娜
"T,F",小刘
"T,M",涛哥|老赵
+------+--------+
| _c0 | _c1 |
+------+--------+
| S,F | 娜娜|老娜 |
| T,F | 小刘 |
| T,M | 涛哥|老赵 |
+------+--------+
4.11 列传行
函数说明
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。 [爆炸, 炸裂]
LATERAL VIEW 一入多出
用法：LATERAL VIEW udtf(expression) table Alias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
代码实现
《八佰》 战争,动作,爱国,剧情
《当幸福来敲门》 剧情,励志,怀旧,心理,故事
《悬崖之上》 战争,爱国,抗日,谍战
drop table tb_movie;
create table tb_movie(
name string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath '/a/movie.txt' into table tb_movie ;
select * from tb_movie ;
+----------------+-----------------------------+
| tb_movie.name | tb_movie.category |
+----------------+-----------------------------+
| 《八佰》 | ["战争","动作","爱国","剧情"] |
| 《当幸福来敲门》 | ["剧情","励志","怀旧","心理","故事"] |
| 《悬崖之上》 | ["战争","爱国","抗日","谍战"] |
+----------------+-----------------------------+
-- lateral view 侧窗口函数
侧窗口函数 维护了炸裂前后的数据关系 lateral view
-- explode(集合/数组) 炸裂
select
name,
`类型`
from
tb_movie
lateral view
explode(category) t as `类型` ;
+-----------+-----+
| name | 类型 |
+-----------+-----+
| 《八佰》 | 战争 |
| 《八佰》 | 动作 |
| 《八佰》 | 爱国 |
| 《八佰》 | 剧情 |
| 《当幸福来敲门》 | 剧情 |
| 《当幸福来敲门》 | 励志 |
| 《当幸福来敲门》 | 怀旧 |
| 《当幸福来敲门》 | 心理 |
| 《当幸福来敲门》 | 故事 |
| 《悬崖之上》 | 战争 |
| 《悬崖之上》 | 爱国 |
| 《悬崖之上》 | 抗日 |
| 《悬崖之上》 | 谍战 |
+-----------+-----+
4.11 窗口函数
窗口: 聚合函数执行的范围 , 组内执行聚合 , 窗口大小就是组
在进行分组聚合以后 ， 我们还想操作集合以前的数据 使用到窗口函数
函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行 current row
n PRECEDING：往前n行数据 n preceding
n FOLLOWING：往后n行数据 n following
UNBOUNDED：
起点，UNBOUNDED PRECEDING 表示从前面的起点，
终点, UNBOUNDED FOLLOWING表示到后面的终点 unbound preceding unbound following
LAG(col,n,[default])：往前第n行数据 lag 参数一 字段 n lag
LEAD(col,n)：往后第n行数据 lead
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。 ntile(5)
代码演示
-- 数据
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
drop table tb_orders;
create table tb_orders(
name string ,
cdate string ,
money double
)
row format delimited fields terminated by ',';
load data local inpath '/a/orders.txt' into table tb_orders;
-- 求每个月的订单明细和订单总数
select *,
sum(money) over(partition by substr(cdate,0,7))
from tb_orders
order by cdate;
-- 统计每个人的消费总金额
select name,
sum(money)
from tb_orders
group by name;
-- 统计每个人的消费总金额 以及订单明细
select
*,
sum(money) over(partition by name)
from tb_orders;
窗口函数控制窗口大小
-- 1) 组内 排序 起始行到当前行计算聚合
select
* ,
sum(money) over(partition by name order by cdate
rows between unbounded preceding and current row)
from
tb_orders ;
-- 2) 组内 排序 起始行到结束行计算聚合
select
* ,
sum(money) over(partition by name order by cdate
rows between unbounded preceding and unbounded following)
from
tb_orders ;
-- 3) 组内 当前行和上一行累加
select
* ,
sum(money) over(partition by name order by cdate rows between 1 preceding and current row )
from
tb_orders ;
-- 4) 组内 上一行,当前行 ,下一行累加
select
* ,
sum(money) over(partition by name order by cdate rows between 1 preceding and 1 following )
from
tb_orders ;
-- 5) 当前行到结束行
select
* ,
sum(money) over(partition by name order by cdate rows between current row and unbounded following )
from
tb_orders ;
总结
聚合函数 over()
- max min sum count avg lag lead collect_set collect_list
- ntile ()
lag 向前n行 /每个人上次购买
over(partiiton by col , col2…)
over(order by col ,col2) 排序
over(partition by colx ,coly order by col ,col2 desc) 组内排序
rows between … and …
unbounded preceding
unbounded following
N preceding
N following
current row
4.12 编号函数
函数说明
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
代码演示
name subject score
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
娜娜 语文 94
娜娜 数学 56
娜娜 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
create table tb_score(
name string ,
subject string ,
score double
)
row format delimited fields terminated by '\t' ;
load data local inpath '/data/score.txt' into table tb_score ;
+----------------+-------------------+-----------------+
| tb_score.name | tb_score.subject | tb_score.score |
+----------------+-------------------+-----------------+
| 孙悟空 | 语文 | 87.0 |
| 孙悟空 | 数学 | 95.0 |
| 孙悟空 | 英语 | 68.0 |
| 娜娜 | 语文 | 94.0 |
| 娜娜 | 数学 | 56.0 |
| 娜娜 | 英语 | 84.0 |
| 宋宋 | 语文 | 64.0 |
| 宋宋 | 数学 | 86.0 |
| 宋宋 | 英语 | 84.0 |
| 婷婷 | 语文 | 65.0 |
| 婷婷 | 数学 | 85.0 |
| 婷婷 | 英语 | 78.0 |
+----------------+-------------------+-----------------+
select
* ,
-- 编号不考虑排序
row_number() over(partition by subject order by score desc) as rn
from
tb_score ;
select
* ,
-- 排序字段相同的行 编号一致 , 总数不变
rank() over(partition by subject order by score desc) as rn
from
tb_score ;
+----------------+-------------------+-----------------+-----+
| tb_score.name | tb_score.subject | tb_score.score | rn |
+----------------+-------------------+-----------------+-----+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 1 |
| 婷婷 | 英语 | 78.0 | 3 |
| 孙悟空 | 英语 | 68.0 | 4 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 4 |
+----------------+-------------------+-----------------+-----+
select
* ,
-- 排序字段相同的行 编号一致 ,编号是连续的 , 总数变少
dense_rank() over(partition by subject order by score desc) as rn
from
tb_score ;
+----------------+-------------------+-----------------+-----+
| tb_score.name | tb_score.subject | tb_score.score | rn |
+----------------+-------------------+-----------------+-----+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 1 |
| 婷婷 | 英语 | 78.0 | 2 |
| 孙悟空 | 英语 | 68.0 | 3 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 4 |
+----------------+-------------------+-----------------+-----+
5 其他
5.1 系统函数
-- 查看系统中支持的函数
show functions ;
+------------------------------+
| tab_name |
+------------------------------+
| ! |
| != |
| $sum0 |
| % |
| & |
| * |
| + |
| - |
| / |
| < |
| <= |
| <=> |
| <> |
| = |
| == |
| > |
| >= |
| ^ |
| abs |
| acos |
| add_months |
| aes_decrypt |
| aes_encrypt |
| and |逻辑&
| array |数组定义
| array_contains |数组中是否包含某元素
| ascii |
| asin |
| assert_true |
| assert_true_oom |
| atan |
| avg |聚合函数 - 平均
| base64 |
| between |
| bin |
| bloom_filter |
| bround |
| cardinality_violation |
| case |
| cbrt |
| ceil |
| ceiling |
| char_length |字符串长度
| character_length |字符串长度
| chr |
| coalesce |返回第一个部位null的元素
| collect_list |聚合-将组内的元素收集成数组 不会去重
| collect_set |聚合-将组内的元素收集成数组 会去重
| compute_stats |
| concat |逐行运算中的拼接
| concat_ws |拼接函数的升级版
| context_ngrams |
| conv |
| corr |
| cos |
| count |聚合函数-统计
| covar_pop |
| covar_samp |
| crc32 |
| create_union |
| cume_dist |
| current_authorizer |当前作者
| current_database |当前使用的数据库
| current_date |当前日期
| current_groups |
| current_timestamp |当前时间戳
| current_user |当前用户
| date_add |日期+
| date_format |格式化时间
| date_sub |日期-
| datediff |两个日期的差值
| day |提取天
| dayofmonth |
| dayofweek |
| decode |
| degrees |
| dense_rank |编号函数
| div |
| e |
| elt |
| encode |
| enforce_constraint |
| exp |
| explode |炸裂函数
| extract_union |
| factorial |
| field |
| find_in_set |
| first_value |
| floor |
| floor_day |截取到时间戳的位置
| floor_hour |
| floor_minute |
| floor_month |
| floor_quarter |
| floor_second |
| floor_week |
| floor_year |
| format_number |
| from_unixtime |将长整型的时间戳转换成指定的字符串格式
+------------------------------+
| tab_name |
+------------------------------+
| from_utc_timestamp |
| get_json_object |
| get_splits |
| greatest |逐行中的最大值
| grouping |
| hash |
| hex |
| histogram_numeric |
| hour |
| if |判断
| in |在...内部...
| in_bloom_filter |
| in_file |
| index |
| initcap |
| inline |
| instr |
| internal_interval |
| isfalse |
| isnotfalse |
| isnotnull |
| isnottrue |
| isnull |判断是否为null
| istrue |
| java_method |调用java中的方法 用于功能扩展
| json_tuple |json解析函数***
| lag |向前n行
| last_day |
| last_value |最后一个值
| lcase |
| lead |
| least |
| length |字符串长度
| levenshtein |
| like |
| likeall |
| likeany |
| ln |
| locate |
| log |
| log10 |
| log2 |
| logged_in_user |
| lower |
| lpad |
| ltrim |
| map |定义map集合
| map_keys |获取map的keys
| map_values |获取map的values
| mask |
| mask_first_n |
| mask_hash |
| mask_last_n |
| mask_show_first_n |
| mask_show_last_n |
| matchpath |
| max |聚合函数 最大值
| md5 |
| min |聚合函数 最小值
| minute |
| mod |
| month |获取时间的月份
| months_between |
| murmur_hash |
| named_struct |
| negative |
| next_day |
| ngrams |
| noop |
| noopstreaming |
| noopwithmap |
| noopwithmapstreaming |
| not |
| ntile |数据按比例划分
| nullif |
| nvl |空值处理 ***
| octet_length |
| or |
| parse_url |
| parse_url_tuple |
| percent_rank |
| percentile |
| percentile_approx |
| pi |
| pmod |
| posexplode |
| positive |
| pow |
| power |
| printf |
| quarter |
| radians |
| rand |
| rank |
| reflect |
| reflect2 |
| regexp |
| regexp_extract |
| regexp_replace |
| regr_avgx |
+------------------------------+
| tab_name |
+------------------------------+
| regr_avgy |
| regr_count |
| regr_intercept |
| regr_r2 |
| regr_slope |
| regr_sxx |
| regr_sxy |
| regr_syy |
| repeat |
| replace |
| replicate_rows |
| restrict_information_schema |
| reverse |
| rlike |
| round |
| row_number |
| rpad |拼接
| rtrim |
| second |
| sentences |
| sha |
| sha1 |
| sha2 |
| shiftleft |
| shiftright |
| shiftrightunsigned |
| sign |
| sin |
| size |集合长度
| sort_array |数组排序
| sort_array_by |
| soundex |
| space |
| split |字符串切割
| sq_count_check |
| sqrt |
| stack |
| std |
| stddev |
| stddev_pop |
| stddev_samp |
| str_to_map |字符串转map
| struct |
| substr |子串
| substring |子串
| substring_index |子串的位置
| sum |聚合函数 求和
| tan |
| to_date |转换成日期
| to_epoch_milli |
| to_unix_timestamp |转换成本地时间戳
| to_utc_timestamp |
| translate |
| trim |
| trunc |
| ucase |
| udftoboolean |
| udftobyte |
| udftodouble |
| udftofloat |
| udftointeger |
| udftolong |
| udftoshort |
| udftostring |
| unbase64 |
| unhex |
| unix_timestamp |
| upper |字符串转换成大写
| uuid |随机字符串ID
| var_pop |
| var_samp |
| variance |
| version |
| weekofyear |
| when |
| width_bucket |
| windowingtablefunction |
| xpath |
| xpath_boolean |
| xpath_double |
| xpath_float |
| xpath_int |
| xpath_long |
| xpath_number |
| xpath_short |
| xpath_string |
| year |
| | |
| ~ |
+------------------------------+
desc function 函数名 ; 查询函数的使用文档
select from_unixtime(to_unix_timestamp('2021-08-01 11:11:12'),'yyyy/MM/dd') ;
json解析案例
{"movie":"1240","rate":"5","timeStamp":"978294260","uid":"4"}
{"movie":"2987","rate":"4","timeStamp":"978243170","uid":"5"}
{"movie":"2333","rate":"4","timeStamp":"978242607","uid":"5"}
{"movie":"1175","rate":"5","timeStamp":"978244759","uid":"5"}
{"movie":"39","rate":"3","timeStamp":"978245037","uid":"5"}
{"movie":"288","rate":"2","timeStamp":"978246585","uid":"5"}
{"movie":"2337","rate":"5","timeStamp":"978243121","uid":"5"}
{"movie":"1535","rate":"4","timeStamp":"978245513","uid":"5"}
{"movie":"1392","rate":"4","timeStamp":"978245645","uid":"5"}
create table test_json(
str string
) ;
load data local inpath '/data/movie.txt' into table test_json ;
+----------------------------------------------------+
| test_json.str |
+----------------------------------------------------+
| {"movie":"1240","rate":"5","timeStamp":"978294260","uid":"4"} |
| {"movie":"2987","rate":"4","timeStamp":"978243170","uid":"5"} |
| {"movie":"2333","rate":"4","timeStamp":"978242607","uid":"5"} |
| {"movie":"1175","rate":"5","timeStamp":"978244759","uid":"5"} |
| {"movie":"39","rate":"3","timeStamp":"978245037","uid":"5"} |
| {"movie":"288","rate":"2","timeStamp":"978246585","uid":"5"} |
| {"movie":"2337","rate":"5","timeStamp":"978243121","uid":"5"} |
| {"movie":"1535","rate":"4","timeStamp":"978245513","uid":"5"} |
| {"movie":"1392","rate":"4","timeStamp":"978245645","uid":"5"} |
+----------------------------------------------------+
select json_tuple(str , 'movie' , 'rate' , 'timeStamp' , 'uid') as (mid , rate , ct , uid) from test_json ;
+-------+-------+------------+------+
| mid | rate | ct | uid |
+-------+-------+------------+------+
| 1240 | 5 | 978294260 | 4 |
| 2987 | 4 | 978243170 | 5 |
| 2333 | 4 | 978242607 | 5 |
| 1175 | 5 | 978244759 | 5 |
| 39 | 3 | 978245037 | 5 |
| 288 | 2 | 978246585 | 5 |
| 2337 | 5 | 978243121 | 5 |
| 1535 | 4 | 978245513 | 5 |
| 1392 | 4 | 978245645 | 5 |
+-------+-------+------------+------+
5.2 自定义函数
1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。除了自定义函数以外 , 使用reflect反射调用java的方法完成功能
3）根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore() split()
5.2.1 添加依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
5.2.2 编写代码 打包
public class Demo01 extends UDF {
public String evaluate(String se,String...str){
StringBuilder stringBuilder = new StringBuilder();
for (String s : str) {
stringBuilder.append(s).append(se) ;
}
String res = stringBuilder.toString();
String k = res.substring(0, res.length() - 1);
return k;
}
/* public String evaluate(String se,String str){
StringBuilder stringBuilder = new StringBuilder();
for (String s : str) {
stringBuilder.append(s).append(se) ;
}
String res = stringBuilder.toString();
String k = res.substring(0, res.length() - 1);
return k;*/
}
}
- 打包 上传到linux机器
- hive> add jar xx.jar ;
- create temporary function my_concat as “com._51doit.test.Demo01” ;
- select my_concat(’-’,‘java’,‘jim’,‘sql’) ;
5.2.3 定义临时函数
drop function xxoo_lower; 可以删除自定义函数
5.3 JDBC
jdbc是jdk开发的操作数据库的一套标准API(接口方法)
hives使用JDBC连接需要开启hiveserver2远程连接服务
添加依赖
<dependencies>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>3.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>3.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc
数据库的驱动 连接的是何种数据库
-->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-metastore</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
代码实现
public class HiveJdbc {
private static String driverName = "org.apache.hive.jdbc.HiveDriver";
public static void main(String[] args) throws SQLException {
try {
// 注册驱动
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
// 获取连接
Connection con = DriverManager.getConnection("jdbc:hive2://linux01:10000/default", "root", "");
// 获取指定SQL的对象
Statement stmt = con.createStatement();
String sql = "select * from tb_movie";
// 执行sql
ResultSet res = stmt.executeQuery(sql);
// 处理结果
if (res.next()) {
System.out.println(res.getString(1) + "-" + res.getString(2));
}
}
}
5.4 文件格式
Hive支持的存储数的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。
5.4.1 列式存储和行式存储
如图6-10所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。
1．行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
2．列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；
ORC和PARQUET是基于列式存储的。
5.4.2 TextFile格式
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。
5.4.3 Orc格式
Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存储格式。
如图6-11所示可以看到每个Orc文件由1个或多个stripe组成，每个stripe250MB大小，这个Stripe实际相当于RowGroup概念，不过大小由4MB->250MB，这样应该能提升顺序读的吞吐率。每个Stripe里有三部分组成，分别是Index Data，Row Data，Stripe Footer：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VYozQOD1-1631782764360)(img/wps3149.tmp.jpg)]
1）Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
3）Stripe Footer：存的是各个Stream的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。
5.4.4 Parquet格式
Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发，2015年5月从Apache的孵化器里毕业成为Apache*项目。
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。
上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。
在hive中默认使用的数据格式是文本 , hive中处理方式以SQL的语法处理 , 结构化查询 , 使用列式存储文件格式高效
5.4.5 主流文件格式对比
log.data 文本文件实际大小18.1M
1 text 18.1M
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile ;
load data local inpath '/data/log.data' into table log_text ;
desc formatted tb_name ; 查看表的详细信息 表类型 分区字段 表位置 输入输出的处理类(文件格式)
2ORC 列式存储 2.8M
create table log_orc (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
stored as ORC ;
insert into table log_orc select * from log_text ;
3parquet 列式存储 13.1 M
create table log_parquet (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
stored as parquet ;
insert into table log_parquet select * from log_orc ;
- 文本文件是默认格式 行式存储 不压缩 效率最低 18.1M
- ORC 列式存储 压缩比高 效率高 2.7M
- parquet 列式存储 压缩比比ORC低 效率和ORC相当 , 兼容性比ORC好 13.1M
执行效率ORC和parquet格式类似 , 但是ORC的压缩比更更好 ,parquet 兼容性好 ,
5.4.6 with立方体
- with cube
- with rollup
- with total X
1,a,山东,济南市,天桥区
2,b,山东,济南市,历城区
3,c,山东,济南市,历下区
4,a,山东,青岛市,崂山区
5,b,山东,青岛市,东城区
6,a,山西,太原市,东城区
7,b,山西,太原市,西城区
p c a
0 0 0
0 0 1
0 1 0
select
p , c , a ,
count(1)
from
tb_city
group by p , c , a
with cube ;
+-------+-------+-------+------+
| p | c | a | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 7 |
| NULL | NULL | 东城区 | 2 |
| NULL | NULL | 历下区 | 1 |
| NULL | NULL | 历城区 | 1 |
| NULL | NULL | 天桥区 | 1 |
| NULL | NULL | 崂山区 | 1 |
| NULL | NULL | 西城区 | 1 |
| NULL | 太原市 | NULL | 2 |
| NULL | 太原市 | 东城区 | 1 |
| NULL | 太原市 | 西城区 | 1 |
| NULL | 济南市 | NULL | 3 |
| NULL | 济南市 | 历下区 | 1 |
| NULL | 济南市 | 历城区 | 1 |
| NULL | 济南市 | 天桥区 | 1 |
| NULL | 青岛市 | NULL | 2 |
| NULL | 青岛市 | 东城区 | 1 |
| NULL | 青岛市 | 崂山区 | 1 |
| 山东 | NULL | NULL | 5 |
| 山东 | NULL | 东城区 | 1 |
| 山东 | NULL | 历下区 | 1 |
| 山东 | NULL | 历城区 | 1 |
| 山东 | NULL | 天桥区 | 1 |
| 山东 | NULL | 崂山区 | 1 |
| 山东 | 济南市 | NULL | 3 |
| 山东 | 济南市 | 历下区 | 1 |
| 山东 | 济南市 | 历城区 | 1 |
| 山东 | 济南市 | 天桥区 | 1 |
| 山东 | 青岛市 | NULL | 2 |
| 山东 | 青岛市 | 东城区 | 1 |
| 山东 | 青岛市 | 崂山区 | 1 |
| 山西 | NULL | NULL | 2 |
| 山西 | NULL | 东城区 | 1 |
| 山西 | NULL | 西城区 | 1 |
| 山西 | 太原市 | NULL | 2 |
| 山西 | 太原市 | 东城区 | 1 |
| 山西 | 太原市 | 西城区 | 1 |
+-------+-------+-------+------+
select
p , c , a ,
count(1)
from
tb_city
group by p , c , a
with rollup ;
+-------+-------+-------+------+
| p | c | a | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 7 |
| 山东 | NULL | NULL | 5 |
| 山东 | 济南市 | NULL | 3 |
| 山东 | 济南市 | 历下区 | 1 |
| 山东 | 济南市 | 历城区 | 1 |
| 山东 | 济南市 | 天桥区 | 1 |
| 山东 | 青岛市 | NULL | 2 |
| 山东 | 青岛市 | 东城区 | 1 |
| 山东 | 青岛市 | 崂山区 | 1 |
| 山西 | NULL | NULL | 2 |
| 山西 | 太原市 | NULL | 2 |
| 山西 | 太原市 | 东城区 | 1 |
| 山西 | 太原市 | 西城区 | 1 |
+-------+-------+-------+------+
x，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
3）Stripe Footer：存的是各个Stream的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。
5.4.4 Parquet格式
Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发，2015年5月从Apache的孵化器里毕业成为Apache*项目。
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式如图6-12所示。
[外链图片转存中…(img-BhwZ2OLe-1631782764361)]
上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。
在hive中默认使用的数据格式是文本 , hive中处理方式以SQL的语法处理 , 结构化查询 , 使用列式存储文件格式高效
5.4.5 主流文件格式对比
log.data 文本文件实际大小18.1M
1 text 18.1M
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile ;
load data local inpath '/data/log.data' into table log_text ;
desc formatted tb_name ; 查看表的详细信息 表类型 分区字段 表位置 输入输出的处理类(文件格式)
2ORC 列式存储 2.8M
create table log_orc (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
stored as ORC ;
insert into table log_orc select * from log_text ;
3parquet 列式存储 13.1 M
create table log_parquet (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
stored as parquet ;
insert into table log_parquet select * from log_orc ;
- 文本文件是默认格式 行式存储 不压缩 效率最低 18.1M
- ORC 列式存储 压缩比高 效率高 2.7M
- parquet 列式存储 压缩比比ORC低 效率和ORC相当 , 兼容性比ORC好 13.1M
执行效率ORC和parquet格式类似 , 但是ORC的压缩比更更好 ,parquet 兼容性好 ,
5.4.6 with立方体
- with cube
- with rollup
- with total X
1,a,山东,济南市,天桥区
2,b,山东,济南市,历城区
3,c,山东,济南市,历下区
4,a,山东,青岛市,崂山区
5,b,山东,青岛市,东城区
6,a,山西,太原市,东城区
7,b,山西,太原市,西城区
p c a
0 0 0
0 0 1
0 1 0
select
p , c , a ,
count(1)
from
tb_city
group by p , c , a
with cube ;
+-------+-------+-------+------+
| p | c | a | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 7 |
| NULL | NULL | 东城区 | 2 |
| NULL | NULL | 历下区 | 1 |
| NULL | NULL | 历城区 | 1 |
| NULL | NULL | 天桥区 | 1 |
| NULL | NULL | 崂山区 | 1 |
| NULL | NULL | 西城区 | 1 |
| NULL | 太原市 | NULL | 2 |
| NULL | 太原市 | 东城区 | 1 |
| NULL | 太原市 | 西城区 | 1 |
| NULL | 济南市 | NULL | 3 |
| NULL | 济南市 | 历下区 | 1 |
| NULL | 济南市 | 历城区 | 1 |
| NULL | 济南市 | 天桥区 | 1 |
| NULL | 青岛市 | NULL | 2 |
| NULL | 青岛市 | 东城区 | 1 |
| NULL | 青岛市 | 崂山区 | 1 |
| 山东 | NULL | NULL | 5 |
| 山东 | NULL | 东城区 | 1 |
| 山东 | NULL | 历下区 | 1 |
| 山东 | NULL | 历城区 | 1 |
| 山东 | NULL | 天桥区 | 1 |
| 山东 | NULL | 崂山区 | 1 |
| 山东 | 济南市 | NULL | 3 |
| 山东 | 济南市 | 历下区 | 1 |
| 山东 | 济南市 | 历城区 | 1 |
| 山东 | 济南市 | 天桥区 | 1 |
| 山东 | 青岛市 | NULL | 2 |
| 山东 | 青岛市 | 东城区 | 1 |
| 山东 | 青岛市 | 崂山区 | 1 |
| 山西 | NULL | NULL | 2 |
| 山西 | NULL | 东城区 | 1 |
| 山西 | NULL | 西城区 | 1 |
| 山西 | 太原市 | NULL | 2 |
| 山西 | 太原市 | 东城区 | 1 |
| 山西 | 太原市 | 西城区 | 1 |
+-------+-------+-------+------+
select
p , c , a ,
count(1)
from
tb_city
group by p , c , a
with rollup ;
+-------+-------+-------+------+
| p | c | a | _c3 |
+-------+-------+-------+------+
| NULL | NULL | NULL | 7 |
| 山东 | NULL | NULL | 5 |
| 山东 | 济南市 | NULL | 3 |
| 山东 | 济南市 | 历下区 | 1 |
| 山东 | 济南市 | 历城区 | 1 |
| 山东 | 济南市 | 天桥区 | 1 |
| 山东 | 青岛市 | NULL | 2 |
| 山东 | 青岛市 | 东城区 | 1 |
| 山东 | 青岛市 | 崂山区 | 1 |
| 山西 | NULL | NULL | 2 |
| 山西 | 太原市 | NULL | 2 |
| 山西 | 太原市 | 东城区 | 1 |
| 山西 | 太原市 | 西城区 | 1 |
+-------+-------+-------+------+