hive 匹配纯数字

目录

• 1 Hive概述

• 1.1 Hive产生背景
• 1.2 Hive和RDBMS(关系数据库管理系统)对比
• 1.3 Hive的优缺点
• 1.4 Hive架构

• 2 Hive安装与配置

• 2.1 Hive安装配置

• 2.1.1 MySQL安装
• 2.1.2 Hive安装
• 2.1.3 Hive 属性配置
• 2.1.4 参数配置方式

• 2.2 Hive命令

• 3 数据类型与文件格式

• 3.1 基本数据类型及转换
• 3.2 集合数据类型
• 3.3 文本文件数据编码
• 3.4 读时模式

• 4 HQL操作之 -- DDL命令

• 4.1 数据库操作
• 4.2 建表语法
• 4.3 内部表 & 外部表
• 4.4 分区表
• 4.5 分桶表
• 4.6 修改表 & 删除表

• 5 HQL操作之--数据操作

• 5.1 数据导入
• 5.2 数据导出

1 Hive概述

1.1 Hive产生背景

直接使用MapReduce处理大数据，将面临以下问题：

• MapReduce 开发难度大，学习成本高(wordCount => Hello World)
• Hdfs文件没有字段名、没有数据类型，不方便进行数据的有效管理
• 使用MapReduce框架开发，项目周期长，成本高

Hive是基于Hadoop的一个数据仓库工具，可以将 结构化的数据文件映射为一张表(类似于RDBMS中的表)，并提供类SQL查询功能；Hive是由Facebook开源，用于解决海量结构化日志的数据统计。

• Hive本质是：将 SQL 转换为 MapReduce 的任务进行运算
• 底层由HDFS来提供数据存储
• 可以将Hive理解为一个：将 SQL 转换为 MapReduce 任务的工具

数据仓库(Data Warehouse)是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，主要用于管理决策。(数据仓库之父比尔·恩门，1991年提出)。

• 数据仓库的目的：构建面向分析的、集成的数据集合；为企业提供决策支持
• 数据仓库本身不产生数据，数据来源与外部
• 存储了大量数据，对这些数据的分析和处理不可避免的用到Hive

1.2 Hive和RDBMS(关系数据库管理系统)对比

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将Hive 理解为数据库。其实从结构上来看，Hive 和传统的关系数据库除了拥有类似的查询语言，再无类似之处。

• 查询语言相似。HQL <=> SQL 高度相似
  由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。
• 数据规模。Hive存储海量数据；RDBMS只能处理有限的数据集；
  由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；而RDBMS可以支持的数据规模较小。
• 执行引擎。Hive的引擎是MR/Tez/Spark/Flink；RDBMS使用自己的执行引擎Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而RDBMS通常有自己的执行引擎。
• 数据存储。Hive保存在HDFS上；RDBMS保存在本地文件系统 或 裸设备Hive 的数据都是存储在 HDFS 中的。而RDBMS是将数据保存在本地文件系统或裸设备中。
• 执行速度。Hive相对慢（MR/数据量）；RDBMS相对快；
  Hive存储的数据量大，在查询数据的时候，通常没有索引，需要扫描整个表；加之Hive使用MapReduce作为执行引擎，这些因素都会导致较高的延迟。而RDBMS对数据的访问通常是基于索引的，执行延迟较低。当然这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出并行的优势。
• 可扩展性。Hive支持水平扩展；通常RDBMS支持垂直扩展，对水平扩展不友好。Hive建立在Hadoop之上，其可扩展性与Hadoop的可扩展性是一致的（Hadoop集群规模可以轻松超过1000个节点）。而RDBMS由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。
• 数据更新。Hive对数据更新不友好；RDBMS支持频繁、快速数据更新
  Hive是针对数据仓库应用设计的，数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而RDBMS中的数据需要频繁、快速的进行更新。

1.3 Hive的优缺点

Hive的优点：

• 学习成本低。Hive提供了类似SQL的查询语言，开发人员能快速上手；
• 处理海量数据。底层执行的是MapReduce 任务；
• 系统可以水平扩展。底层基于Hadoop；
• 功能可以扩展。Hive允许用户自定义函数；
• 良好的容错性。某个节点发生故障，HQL仍然可以正常完成；
• 统一的元数据管理。元数据包括：有哪些表、表有什么字段、字段是什么类型

Hive的缺点：

• HQL表达能力有限；
• 迭代计算无法表达；
• Hive的执行效率不高(基于MR的执行引擎)；
• Hive自动生成的MapReduce作业，某些情况下不够智能；
• Hive的调优困难；

1.4 Hive架构

1. 用户接口 CLI(Common Line Interface)：Hive的命令行，用于接收HQL，并返回结果； JDBC/ODBC：是指Hive的java实现，与传统数据库JDBC类似；WebUI：是指可通过浏览器访问Hive；
2. Thrift Server
   Hive可选组件，是一个软件框架服务，允许客户端使用包括Java、C++、Ruby和其他很多种语言，通过 编程的方式远程访问Hive；
3. 元数据管理(MetaStore) Hive将元数据存储在关系数据库中(如mysql、derby)。Hive的元数据包括：数据库名、表名及类型、字段名称及数据类型、数据所在位置等；
4. 驱动程序(Driver)
   - 解析器 (SQLParser) ：使用第三方工具（antlr）将HQL字符串转换成抽象语法树（AST）；对AST进行语法分析，比如字段是否存在、SQL语义是否有误、表是否存在；
   - 编译器 (Compiler) ：将抽象语法树编译生成逻辑执行计划；
   - 优化器 (Optimizer) ：对逻辑执行计划进行优化，减少不必要的列、使用分区等；
   - 执行器 (Executr) ：把逻辑执行计划转换成可以运行的物理计划；

2 Hive安装与配置

2.1 Hive安装配置

Hive官网：http://hive.apache.org 下载网址：http://archive.apache.org/dist/hive/

文档网址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual

备注：Hive的元数据默认存储在自带的 derby 数据库中，生产中多采用MySQL

derby：java语言开发占用资源少，单进程，单用户。仅仅适用于个人的测试。

2.1.1 MySQL安装

1. 卸载旧版本mysql

# 1.查看mysql安装
	rpm -qa|grep -i mysql
# 2.卸载前关闭mysql服务 
	rpm -ev --nodeps mysql-community-release-el7-5.noarch
	rpm -ev --nodeps mysql-community-common-5.6.38-2.el7.x86_64
	rpm -ev --nodeps mysql-community-client-5.6.38-2.el7.x86_64
	rpm -ev --nodeps mysql-community-libs-5.6.38-2.el7.x86_64
	rpm -ev --nodeps community-server-5.6.38-2.el7.x86_64
# 3.再次执行 检查
	rpm -qa|grep -i mysql 
# 4.删除关联文件夹
	find / -name mysql
	rm -rm 上面查出的文件夹

2. 删除MariaDB
   centos7.6自带的 MariaDB(MariaDB是MySQL的一个分支)，与要安装的MySQL有冲突，需要删除。

# 查询是否安装了mariadb
	rpm -aq | grep mariadb
# 删除mariadb。-e 删除指定的套件；--nodeps 不验证套件的相互关联性
	rpm -e --nodeps mariadb-libs

3. 安装依赖

yum install perl -y
	yum install net-tools -y

4. 安装MySQL

# 解压缩
	tar xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
# 依次运行以下命令
	rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
	rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
	rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
	rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
# 启动数据库
	systemctl start mysqld
# 查找root密码
	grep password /var/log/mysqld.log
# 修改 root 口令
# 进入MySQL，使用前面查询到的口令
	mysql -u root -p
# 设置口令强度；将root口令设置为12345678；刷新
	set global validate_password_policy=0;
	set password for 'root'@'localhost' =password('12345678');
	flush privileges;

validate_password_policy 密码策略(默认是1)，可配置的值有以下：
0 or LOW 仅需需符合密码长度（由参数validate_password_length【默认为8】指定）
1 or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符
2 or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionaryfile）中
备注：个人开发环境，出于方便的目的设比较简单的密码；生产环境一定要设复杂密码！

5. 创建 hive 用户

# 创建用户设置口令、授权、刷新
	CREATE USER 'hive'@'%' IDENTIFIED BY '12345678';
	GRANT ALL ON *.* TO 'hive'@'%';
	FLUSH PRIVILEGES;

2.1.2 Hive安装

1. 下载Hive软件，并解压缩

cd /opt/lagou/software
	tar zxvf apache-hive-2.3.7-bin.tar.gz -C ../servers/
	cd ../servers
	mv apache-hive-2.3.7-bin hive-2.3.7

2. 修改环境变量

# 在 /etc/profile 文件中增加环境变量
export HIVE_HOME=/opt/lagou/servers/hive-2.3.7
export PATH=$PATH:$HIVE_HOME/bin
# 执行并生效
source /etc/profile

3. 修改 Hive 配置
   cd $HIVE_HOME/conf vi hive-site.xml 增加以下内容：

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<!-- hive元数据的存储位置 -->
	<property>
		<name>javax.jdo.option.ConnectionURL</name>
		<value>jdbc:mysql://linux123:3306/hivemetadata?
		createDatabaseIfNotExist=true&useSSL=false</value>
		<description>JDBC connect string for a JDBC
		metastore</description>
	</property>
	<!-- 指定驱动程序 -->
	<property>
		<name>javax.jdo.option.ConnectionDriverName</name>
		<value>com.mysql.jdbc.Driver</value>
		<description>Driver class name for a JDBC
		metastore</description>
	</property>
	<!-- 连接数据库的用户名 -->
	<property>
		<name>javax.jdo.option.ConnectionUserName</name>
		<value>hive</value>
		<description>username to use against metastore
		database</description>
	</property>
	<!-- 连接数据库的口令 -->
	<property>
		<name>javax.jdo.option.ConnectionPassword</name>
		<value>12345678</value>
		<description>password to use against metastore
		database</description>
	</property>
	</configuration>

4. 拷贝 MySQL JDBC 驱动程序
   将 mysql-connector-java-5.1.46.jar 拷贝到 $HIVE_HOME/lib
5. 初始化元数据库

[root@linux123 ~]$ schematool -dbType mysql -initSchema

6. 启动Hive，执行命令

# 启动hive服务之前，请先启动hdfs、yarn的服务
[root@linux123 ~]$ hive
hive> show functions;

2.1.3 Hive 属性配置

可在 hive-site.xml 中增加以下常用配置，方便使用。

数据存储位置
	<property>
		<!-- 数据默认的存储位置(HDFS) -->
		<name>hive.metastore.warehouse.dir</name>
		<value>/user/hive/warehouse</value>
		<description>location of default database for the
		warehouse</description>
	</property>
显示当前库
	<property>
		<!-- 在命令行中，显示当前操作的数据库 -->
		<name>hive.cli.print.current.db</name>
		<value>true</value>
		<description>Whether to include the current database in the
		Hive prompt.</description>
	</property>
	显示表头属性
	<property>
		<!-- 在命令行中，显示数据的表头 -->
		<name>hive.cli.print.header</name>
		<value>true</value>
	</property>
	本地模式
	<property>
		<!-- 操作小规模数据时，使用本地模式，提高效率 -->
		<name>hive.exec.mode.local.auto</name>
		<value>true</value>
		<description>Let Hive determine whether to run in local mode
		automatically</description>
	</property>

备注：当 Hive 的输入数据量非常小时，Hive 通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间会明显被缩短。当一个job满足如下条件才能真正使用本地模式：

• job的输入数据量必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
• job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max (默认4)
• job的reduce数必须为0或者1

Hive的日志文件
Hive的log默认存放在 /tmp/root 目录下（root为当前用户名）；这个位置可以修改。

vi $HIVE_HOME/conf/hive-log4j2.properties
# 添加以下内容：
property.hive.log.dir = /opt/lagou/servers/hive-2.3.7/logs

2.1.4 参数配置方式

查看参数配置信息：

-- 查看全部参数
hive> set;
-- 查看某个参数
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=false

参数配置的三种方式：
1、用户自定义配置文件(hive-site.xml)
2、启动hive时指定参数(-hiveconf)
3、hive命令行指定参数(set)
配置信息的优先级：
set > -hiveconf > hive-site.xml > hive-default.xml

2.2 Hive命令

1. Hive
   -e：不进入hive交互窗口，执行sql语句

hive -e "select * from users"

-f：执行脚本中sql语句

# 创建文件hqlfile1.sql，内容：select * from users
# 执行文件中的SQL语句
hive -f hqlfile1.sql
# 执行文件中的SQL语句，将结果写入文件
hive -f hqlfile1.sql >> result1.log

2. 退出Hive命令行

exit; quit;

3. 在命令行执行 shell 命令 / dfs 命令

hive> ! ls;
hive> ! clear;
hive> dfs -ls / ;

3 数据类型与文件格式

Hive支持关系型数据库的绝大多数基本数据类型，同时也支持4种集合数据类型

3.1 基本数据类型及转换

Hive类似和java语言中一样，会支持多种不同长度的整型和浮点类型数据，同时也支持布尔类型、字符串类型，时间戳数据类型以及二进制数组数据类型等。详细信息见下表：

大类	类型
Integers(整型)	TINYINT – 1字节的有符号整数 SAMLINT – 2字节的有符号整数 INT – 4字节的有符号整数 BIGINT – 8字节的有符号整数
Floating point numbers(浮点数)	FLOAT – 单精度浮点数 DOUBLE – 双精度浮点数
Fixed point numbers(定点数)	DECIMAL–用户自定义精度定点数 如DECIMAL(10,3)(长度，小数位数)
String(字符串)	STRING – 可指定字符集的不定长字符串 VARCHAR – 1-65535长度的不定长字符串 CHAR – 1-25定长字符串(空余字符串会使用空格填充)
Datetime(时间日期类型)	STRTIMESTAMP – 时间戳 DATE – 日期类型
Boolean(布尔类型)	BOOLEAN – TRUE / FALSE
Binary types(二进制类型)	BINARY – 字节序列

这些类型名称都是 Hive 中保留字。这些基本的数据类型都是 java 中的接口进行实现的，因此与 java 中数据类型是基本一致的：

数据类型的隐式转换:

Hive的数据类型是可以进行隐式转换的，类似于Java的类型转换。如用户在查询中将一种浮点类型和另一种浮点类型的值做对比，Hive会将类型转换成两个浮点类型中值较大的那个类型，即：将FLOAT类型转换成DOUBLE类型；当然如果需要的话，任意整型会转化成DOUBLE类型。 Hive 中基本数据类型遵循以下层次结构，按照这个层次结构，子类型到祖先类型允许隐式转换。

总的来说数据转换遵循以下规律

• 任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT， INT 可以转换成 BIGINT；
• 所有整数类型、 FLOAT 和 STRING 类型（仅当STRING都是数字时）可以隐式转换为 DOUBLE；
• TINYINT、 SMALLINT、 INT 都可以转换为 FLOAT；
• BOOLEAN 类型不可以转换为任何其它的类型；

hive> select '1.0'+2;
OK
3.0
hive> select '1111' > 10;
hive> select 1 > 0.8;

数据类型的显示转换
使用cast函数进行强制类型转换；如果强制类型转换失败，返回NULL

hive> select cast('1111s' as int);
OK
NULL
hive> select cast('1111' as int);
OK
1111

3.2 集合数据类型

Hive支持集合数据类型，包括array、map、struct、union

和基本数据类型一样，这些类型的名称同样是保留字；

ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似；

STRUCT 与 C 语言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套；

3.3 文本文件数据编码

Hive表中的数据在存储在文件系统上，Hive定义了默认的存储格式，也支持用户自定义文件存储格式。

Hive默认使用几个很少出现在字段值中的控制字符，来表示替换默认分隔符的字符。

Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、"\t"、"\x001"）、行分隔符（"\n"）以及读取文件数据的方法。

在加载数据的过程中，Hive 不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。

将 Hive 数据导出到本地时，系统默认的分隔符是^A、^B、^C 这些特殊字符，使用cat 或者 vim 是看不到的；

在 vi 中输入特殊字符：

• (Ctrl + v) + (Ctrl + a) => ^A
• (Ctrl + v) + (Ctrl + b) => ^B
• (Ctrl + v) + (Ctrl + c) => ^C

可以使用cat -A file.dat进行查看。

3.4 读时模式

在传统数据库中，在加载时发现数据不符合表的定义，则拒绝加载数据。数据在写入数据库时对照表模式进行检查，这种模式称为"写时模式"（schema on write）。
写时模式 -> 写数据检查 -> RDBMS；
Hive中数据加载过程采用"读时模式" (schema on read)，加载数据时不进行数据格式的校验，读取数据时如果不合法则显示NULL。这种模式的优点是加载数据迅速。
读时模式 -> 读时检查数据 -> Hive；好处：加载数据快；问题：数据显示NULL

4 HQL操作之 – DDL命令

DDL（data definition language）： 主要的命令有CREATE、ALTER、DROP等。DDL主要是用在定义、修改数据库对象的结构或数据类型。

4.1 数据库操作

Hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认数据库；
Hive的数据库名、表名均不区分大小写；
名字不能使用数字开头；
不能使用关键字，尽量不使用特殊符号；
创建数据库语法

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[MANAGEDLOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
-- 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db
hive (default)> create database mydb;
hive (default)> dfs -ls /user/hive/warehouse;
-- 避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】
hive (default)> create database if not exists mydb;
-- 创建数据库。添加备注，指定数据库在存放位置
hive (default)> create database if not exists mydb2
comment 'this is mydb2'
location '/user/hive/mydb2.db';

查看数据库

-- 查看所有数据库
show database;
-- 查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

使用数据库

use mydb;

删除数据库

-- 删除一个空数据库
drop database databasename;
-- 如果数据库不为空，使用 cascade 强制删除
drop database databasename cascade;

4.2 建表语法

create [external] table [IF NOT EXISTS] table_name
[(colName colType [comment 'comment'], ...)]
[comment table_comment]
[partition by (colName colType [comment col_comment], ...)]
[clustered BY (colName, colName, ...)
[sorted by (col_name [ASC|DESC], ...)] into num_buckets buckets]
[row format row_format]
[stored as file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

1. CREATE TABLE。按给定名称创建表，如果表已经存在则抛出异常。可使用if notexists 规避。
2. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。删除内部表时，数据和表的定义同时被删除；删除外部表时，仅仅删除了表的定义，数据保留；在生产环境中，多使用外部表；
3. comment。表的注释
4. partition 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, ...)]

建表时可指定 SerDe 。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用默认的 SerDe。建表时还需要为表指定列，在指定列的同时也会指定自定义的 SerDe。Hive通过 SerDe 确定表的具体的列的数据。SerDe是 Serialize/Deserilize 的简称， hive使用Serde进行行对象的序列与反序列化。

8. stored as SEQUENCEFILE|TEXTFILE|RCFILE。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE（缺省）；如果数据需要压缩，使用 STORED AS SEQUENCEFILE（二进制序列文件）。
9. LOCATION。表在HDFS上的存放位置
10. TBLPROPERTIES。定义表的属性
11. AS。后面可以接查询语句，表示根据后面的查询结果创建表
12. LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

4.3 内部表 & 外部表

在创建表的时候，可指定表的类型。表有两种类型，分别是内部表(管理表)、外部表。

• 默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
• 在删除内部表时，表的定义(元数据) 和 数据 同时被删除
• 在删除外部表时，仅删除表的定义，数据被保留
• 在生产环境中，多使用外部表

小结

• 建表时：
  如果不指定external关键字，创建的是内部表；
  指定external关键字，创建的是外部表；
• 删表时
  删除外部表时，仅删除表的定义，表的数据不受影响
  删除内部表时，表的数据和定义同时被删除
• 外部表的使用场景
  想保留数据时使用。生产多用外部表

4.4 分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。
而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。
在实际中，通常根据时间、地区等信息进行分区。

-- 创建表
create table if not exists t3(
id int
,name string
,hobby array<string>
,addr map<String,string>
) 
partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';
-- 加载数据。
load data local inpath "/home/hadoop/data/t1.dat" into table t3
partition(dt="2020-06-01");
load data local inpath "/home/hadoop/data/t1.dat" into table t3
partition(dt="2020-06-02");

备注：分区字段不是表中已经存在的数据，可以将分区字段看成伪列

查看分区

show partitions t3;

新增分区并设置数据

-- 增加一个分区，不加载数据
alter table t3 add partition(dt='2020-06-03');
-- 增加多个分区，不加载数据
alter table t3
add partition(dt='2020-06-05') partition(dt='2020-06-06');
-- 增加多个分区。准备数据
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
/user/hive/warehouse/mydb.db/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
/user/hive/warehouse/mydb.db/t3/dt=2020-06-08
-- 增加多个分区。加载数据
alter table t3 add
partition(dt='2020-06-07') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
partition(dt='2020-06-08') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';
-- 查询数据
select * from t3;

-- 修改分区的hdfs路径
alter table t3 partition(dt='2020-06-01') set location
'/user/hive/warehouse/t3/dt=2020-06-03';

-- 删除分区
-- 可以删除一个或多个分区，用逗号隔开
alter table t3 drop partition(dt='2020-06-03'),
partition(dt='2020-06-04');

4.5 分桶表

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。分桶的原理：

• MR中：key.hashCode % reductTask
• Hive中：分桶字段.hashCode % 分桶个数

-- 创建分桶表
create table course(
id int,
name string,
score int
) 
clustered by (id) into 3 buckets
row format delimited fields terminated by "\t";
-- 创建普通表
create table course_common(
id int,
name string,
score int
) 
row format delimited fields terminated by "\t";
-- 普通表加载数据
load data local inpath '/home/hadoop/data/course.dat' into table
course_common;
-- 通过 insert ... select ... 给桶表加载数据
insert into table course select * from course_common;
-- 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区

备注：

• 分桶规则：分桶字段.hashCode % 分桶数
• 分桶表加载数据时，使用 insert… select … 方式进行
• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶；

4.6 修改表 & 删除表

-- 修改表名。rename
alter table course_common
rename to course_common1;

-- 修改列名。change column
alter table course_common1
change column id cid int;

-- 修改字段类型。change column
alter table course_common1
change column cid cid string;

-- The following columns have types incompatible with the
existing columns in their respective positions
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是
string不能转为int
-- 增加字段。add columns
alter table course_common1
add columns (common string);

-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1
replace columns(
id string, cname string, score int);

-- 删除表
drop table course_common1;

HQL DDL命令小结：

• 主要对象：数据库、表
• 表的分类：
  内部表。删除表时，同时删除元数据和表数据
  外部表。删除表时，仅删除元数据，保留表中数据；生产环境多使用外部表
  分区表。按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的性能
  分桶表。按照分桶字段，将表中数据分开。 分桶字段.hashCode % 分桶数据
• 主要命令：create、alter 、drop

5 HQL操作之–数据操作

5.1 数据导入

基本语法：

LOAD DATA [LOCAL] INPATH 'filepath'
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,
partcol2=val2 ...)]

• LOCAL：
  LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷贝到Hive表指定的位置
  LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定的位置
• INPATH：加载数据的路径
• OVERWRITE：覆盖表中已有数据；不指定则表示追加数据
• PARTITION：将数据加载到指定的分区
  装载数据：

-- 加载本地文件到hive(tabA)
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt'
INTO TABLE tabA;
-- 检查本地文件还在
-- 加载hdfs文件到hive(tabA)
LOAD DATA INPATH 'data/sourceA.txt'
INTO TABLE tabA;
-- 检查HDFS文件，已经被转移
-- 加载数据覆盖表中已有数据
LOAD DATA INPATH 'data/sourceA.txt'
OVERWRITE INTO TABLE tabA;
-- 创建表时加载数据
hdfs dfs -mkdir /user/hive/tabB
hdfs dfs -put sourceA.txt /user/hive/tabB
CREATE TABLE tabB (
id INT
,name string
,area string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
Location '/user/hive/tabB';

插入数据(Insert)：

-- 创建分区表
CREATE TABLE tabC (
id INT
,name string
,area string
) 
partitioned by (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 插入数据
insert into table tabC
partition(month='202001')
values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan',
'TJ');
-- 插入查询的结果数据
insert into table tabC partition(month='202002')
select id, name, area from tabC where month='202001';
-- 多表（多分区）插入模式
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';

创建表并插入数据(as select)：

-- 根据查询结果创建表 此时tableD没有partitioned信息
create table if not exists tabD
as select * from tabC;

使用import导入数据：

import table student2 partition(month='201709')
from '/user/hive/warehouse/export/student';

5.2 数据导出

-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;

-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;

-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;

-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001
/home/hadoop/data/tabC4

-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件，此时默认使用default数据库来查找。
hive -e "select * from mysb.tabC" > a.log

-- export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息(metadata)
export table tabC to '/user/hadoop/data/tabC4';

-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构可能不一致
create table tabE like tabc;
import table tabE from '/user/hadoop/data/tabC4';

-- 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;
-- 以下语句报错，外部表不能执行 truncate 操作
alter table tabC set tblproperties("EXTERNAL"="TRUE");
truncate table tabC; //会报错 不能truncate

小结：
数据导入：

• load data /
• insert /
• create table … as select … /
• import table

数据导出：

• insert overwrite … diretory … /
• hdfs dfs -get /
• hive -e “select …” >a.log /
• export table …

Hive的数据导入与导出还可以使用其他工具：Sqoop、DataX等；