hive乱码数据处理函数 hive设置编码格式

目录

数据仓库工具 -- Hive

第一部分 Hive概述

第 1 节 Hive产生背景

第 2 节 Hive和RDBMS对比

第 3 节 Hive的优缺点

第 4 节 Hive架构

第二部分 Hive安装与配置

第 1 节 Hive安装配置

1.1、MySQL安装

1.2、Hive 安装

1.3、Hive 属性配置

1.4、参数配置方式

第 2 节 Hive命令

第三部分 数据类型与文件格式

第 1 节 基本数据类型及转换

数据类型的隐式转换

数据类型的显示转换

第 2 节 集合数据类型

第 3 节 文本文件数据编码

第 4 节 读时模式

第四部分 HQL操作之 -- DDL命令

第 1 节 数据库操作

第 2 节 建表语法

第 3 节 内部表 & 外部表external

第 4 节 分区表 partitioned by (参数名 类型)

第 5 节 分桶表clustered by (...) into ... buckets

第 6 节 修改表 & 删除表

第五部分 HQL操作之--数据操作

第 1 节 数据导入Load

第 2 节 数据导出

第六部分 HQL操作之--DQL命令【重点】

第 1 节 基本查询

第 2 节 where子句

第 3 节 group by子句

第 4 节 表连接

第 5 节 排序子句【重点】

全局排序(order by)

每个MR内部排序(sort by)

分区排序(distribute by)

Cluster By

---

 

数据仓库工具 -- Hive

第一部分 Hive概述

HDFS => 海量数据的存储
MapReduce => 海量数据的分析和处理
YARN => 集群资源的管理和作业调度

第 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

 

第 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中的数据需要频繁、快速的进行更新。

 

第 3 节 Hive的优缺点

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

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

 

第 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) ：把逻辑执行计划转换成可以运行的物理计划；

 

第二部分 Hive安装与配置

第 1 节 Hive安装配置

Hive官网：http://hive.apache.org
下载网址：http://archive.apache.org/dist/hive/
文档网址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual
安装前提：3台虚拟机，安装了Hadoop
安装软件：Hive(2.3.7) + MySQL (5.7.26)
备注：Hive的元数据默认存储在自带的 derby 数据库中，生产中多采用MySQL
derby：java语言开发占用资源少，单进程，单用户。仅仅适用于个人的测试。

# hive安装包
apache-hive-2.3.7-bin.tar.gz 

# MySQL安装包
mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

# MySQL的JDBC驱动程序
mysql-connector-java-5.1.46.jar

# 整体的安装步骤：
1、安装MySQL
2、安装配置Hive
3、Hive添加常用配置

 

1.1、MySQL安装

Hive中使用MySQL存储元数据，MySQL的版本 5.7.26。

安装步骤：

1、环境准备(删除有冲突的依赖包、安装必须的依赖包)
2、安装MySQL
3、修改root口令(找到系统给定的随机口令、修改口令)
4、在数据库中创建hive用户

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

# 查询是否安装了mariadb
rpm -aq | grep mariadb

# 删除mariadb。-e 删除指定的套件；--nodeps 不验证套件的相互关联性
rpm -e --nodeps mariadb-libs

2、安装依赖

yum install perl -y

yum install net-tools -y

3、安装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

4、启动数据库

systemctl start mysqld

5、查找root密码

grep password /var/log/mysqld.log

6、修改 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）中

备注：个人开发环境，出于方便的目的设比较简单的密码；生产环境一定要设复杂密码！

7、创建 hive 用户

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

 

1.2、Hive 安装

安装步骤：
1、下载、上传、解压缩
2、修改环境变量
3、修改hive配置
4、拷贝JDBC的驱动程序
5、初始化元数据库

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>

备注：

• 注意jdbc的连接串，如果没有 useSSL=false 会有大量警告
• 在xml文件中  &amp; 表示  &

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;

 

1.3、Hive 属性配置

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

数据存储位置

<!-- 数据默认的存储位置(HDFS) -->
    <property>
          <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为当前用户名）；这个位置可以修改。

 

可以不修改，但是要知道位置。

Hadoop 2.x 中 NameNode RPC缺省的端口号：8020
对端口号要敏感
附录：添加第三方用户（Hadoop）

 

建议：现阶段使用root用户

小结：
1、添加了配置，使用Hive更方便；
2、删除了有冲突的软件包(hive)
3、Hive的日志在哪里(/tmp/root)
4、第三方用户使用Hive。建议使用root用户
5、NameNode 缺省的RPC(远程过程调用)端口号8020，经常使用的端口号9000

 

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

1、配置文件方式
默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml
配置优先级：hive-site.xml > hive-default.xml
配置文件的设定对本机启动的所有Hive进程有效；
配置文件的设定对本机所有启动的Hive进程有效；

2、启动时指定参数值
启动Hive时，可以在命令行添加 -hiveconf param=value 来设定参数，这些设定仅对本次启动有效。

# 启动时指定参数
hive -hiveconf hive.exec.mode.local.auto=true

# 在命令行检查参数是否生效
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=true

3、命令行修改参数
可在 Hive 命令行中使用SET关键字设定参数，同样仅对本次启动有效

hive> set hive.exec.mode.local.auto=false;
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=false

set > -hiveconf > hive-site.xml > hive-default.xml

 

第 2 节 Hive命令

1、Hive

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 SQLto the console)

-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 / ;

 

第三部分 数据类型与文件格式

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

第 1 节 基本数据类型及转换

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

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

 

数据类型的隐式转换

Hive的数据类型是可以进行隐式转换的，类似于Java的类型转换。如用户在查询中将一种浮点类型和另一种浮点类型的值做对比，Hive会将类型转换成两个浮点类型中值较大的那个类型，即：将FLOAT类型转换成DOUBLE类型；当然如果需要的话，任意整型会转化成DOUBLE类型。

Hive 中基本数据类型遵循以下层次结构，按照这个层次结构，子类型到祖先类型允许隐式转换。

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

• 任何整数类型都可以隐式转换为一个范围更广的类型, tinyInt→Int; int→bigint
• 所有整数类型, float, 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

 

第 2 节 集合数据类型

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

和基本数据类型一样，这些类型的名称同样是保留字；
ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似；
STRUCT 与 C 语言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套；

hive> select array(1,2,3);
OK
[1,2,3]

-- 使用 [] 访问数组元素
hive> select arr[0] from (select array(1,2,3) arr) tmp;

 

hive> select map('a', 1, 'b', 2, 'c', 3);
OK
{"a":1,"b":2,"c":3}

-- 使用 [] 访问map元素
hive> select mymap["a"] from (select map('a', 1, 'b', 2, 'c', 3) as mymap) tmp;

-- 使用 [] 访问map元素。 key 不存在返回 NULL
hive> select mymap["x"] from (select map('a', 1, 'b', 2, 'c', 3) as mymap) tmp;
NULL

 

hive> select struct('username1', 7, 1288.68);
OK
{"col1":"username1","col2":7,"col3":1288.68}

-- 给 struct 中的字段命名

hive> select named_struct("name", "username1", "id", 7, "salary",12880.68);
OK
{"name":"username1","id":7,"salary":12880.68}

-- 使用 列名.字段名 访问具体信息
hive> select userinfo.id  from (select named_struct("name", "username1", "id", 7,"salary", 12880.68) userinfo) tmp;
 
-- union 数据类型
hive> select create_union(0, "zhansan", 19, 8000.88) uinfo;

 

第 3 节 文本文件数据编码

Hive表中的数据在存储在文件系统上，Hive定义了默认的存储格式，也支持用户自定义文件存储格式。
Hive默认使用几个很少出现在字段值中的控制字符，来表示替换默认分隔符的字符。

Hive默认分隔符

id name age hobby(array) score(map)
字段之间：^A
元素之间: ^B
key-value之间：^C
666^Alisi^A18^Aread^Bgame^Ajava^C97^Bhadoop^C87

create table s1(
id int,
name string,
age int,
hobby array<string>,
score map<string, int>
);

load data local inpath '/home/hadoop/data/s1.dat' into table s1;

select * from s1;

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

^A / ^B / ^C 都是特殊的控制字符，使用 more 、 cat 命令是看不见的；可以使用cat -A file.dat

 

第 4 节 读时模式

在传统数据库中，在加载时发现数据不符合表的定义，则拒绝加载数据。数据在写入数据库时对照表模式进行检查，这种模式称为"写时模式"（schema on write）。

写时模式 →写数据检查 → RDBMS；

Hive中数据加载过程采用"读时模式" (schema on read)，加载数据时不进行数据格式的校验，读取数据时如果不合法则显示NULL。这种模式的优点是加载数据迅速。

读时模式 → 读时检查数据 → Hive；好处：加载数据快；问题：数据显示NULL

 

第四部分 HQL操作之 -- DDL命令

参考：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

DDL（data definition language）： 主要的命令有CREATE、ALTER、DROP等。

DDL主要是用在定义、修改数据库对象的结构 或 数据类型。

第 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;

查看, show fatabases

显示, desc mydb

使用, use mydb

删除, drop datebase mydb cascade

 

第 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 not exists 规避。

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 表名，允许用户复制现有的表结构，但是不复制数据

 

第 3 节 内部表 & 外部表external

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

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

内部表

t1.dat文件内容

2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

创建表 SQL

-- 创建内部表
create table t1(
    id int,
    name string,
    hobby array<string>,
    addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 显示表的定义，显示的信息较少
desc t1;

-- 显示表的定义，显示的信息多，格式友好
desc formatted t1;

-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t1;

-- 查询数据
select * from t1;

-- 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;

-- 删除表。表和数据同时被删除
drop table t1;

-- 再次查询数据文件，已经被删除

外部表

-- 创建外部表
create external table t2(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 显示表的定义
desc formatted t2;

-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t2;

-- 查询数据
select * from t2;

-- 删除表。表删除了，目录仍然存在
drop table t2;

-- 再次查询数据文件，仍然存在

内部表与外部表的转换

-- 创建内部表，加载数据，并检查数据文件和表的定义
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, 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 t1;

dfs -ls /user/hive/warehouse/mydb.db/t1;

desc formatted t1;

-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');

-- 查询表信息，是否转换成功
desc formatted t1;

-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');

-- 查询表信息，是否转换成功
desc formatted t1;

 

小结

建表时：
    如果不指定external关键字，创建的是内部表；
    指定external关键字，创建的是外部表；

删表时
    删除外部表时，仅删除表的定义，表的数据不受影响
    删除内部表时，表的数据和定义同时被删除

外部表的使用场景
    想保留数据时使用。生产多用外部表

 

第 4 节 分区表 partitioned by (参数名 类型)

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

在实际中，通常根据时间、地区等信息进行分区。

分区表创建与数据加载

-- 创建分区表, 关键字partitioned 
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 ... add partition() ...

-- 增加一个分区，不加载数据
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');

 

第 5 节 分桶表clustered by (...) into ... buckets

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

分桶的原理：
MR中：key.hashCode % reductTask
Hive中：分桶字段.hashCode % 分桶个数

-- 测试数据
1 java 90
1 c 78
1 python 91
1 hadoop 80
2 java 75
2 c 76
2 python 80
2 hadoop 93
3 java 98
3 c 74
3 python 89
3 hadoop 91
5 java 93
6 c 76
7 python 87
8 hadoop 88

-- 创建分桶表
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，那是Hive 1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶；

 

第 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

 

第五部分 HQL操作之--数据操作

第 1 节 数据导入Load

装载数据(Load)
基本语法：

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：将数据加载到指定的分区

准备工作：

-- 创建表
CREATE TABLE tabA ( 
id int
,name string
,area string 
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

数据文件（~/data/sourceA.txt）：
1,fish1,SZ
2,fish2,SH
3,fish3,HZ
4,fish4,QD
5,fish5,SR

-- 拷贝文件到 HDFS
hdfs dfs -put sourceA.txt data/

装载数据：

-- 加载本地文件到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';

-- 多表（多分区）插入模式, 下面overwrite覆盖的是分区表中的数据
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='202001'or month='202002';

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

-- 根据查询结果创建表
create table if not exists tabD
as select * from tabC;

使用import导入数据

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

 

第 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 命令导出数据到本地。执行查询将查询结果重定向到文件
hive -e "select * from tabC" > a.log                                            "

-- export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息
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;

alter table tabC set tblproperties("EXTERNAL"="TRUE");
-- 以下语句报错，外部表不能执行 truncate 操作
truncate table tabC;

小结：
数据导入：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等；

 

 

第六部分 HQL操作之--DQL命令【重点】

DQL -- Data Query Language 数据查询语言

select语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY
col_list]]
[LIMIT [offset,] rows]

SQL语句书写注意事项：

• SQL语句对大小写不敏感
• SQL语句可以写一行（简单SQL）也可以写多行(复杂SQL)
• 关键字不能缩写，也不能分行
• 各子句一般要分行
• 使用缩进格式，提高SQL语句的可读性(重要)

 

创建表，加载数据

-- 测试数据 /home/hadoop/data/emp.dat
7369,SMITH,CLERK,7902,2010-12-17,800,,20
7499,ALLEN,SALESMAN,7698,2011-02-20,1600,300,30
7521,WARD,SALESMAN,7698,2011-02-22,1250,500,30
7566,JONES,MANAGER,7839,2011-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,2011-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,2011-05-01,2850,,30
7782,CLARK,MANAGER,7839,2011-06-09,2450,,10
7788,SCOTT,ANALYST,7566,2017-07-13,3000,,20
7839,KING,PRESIDENT,,2011-11-07,5000,,10
7844,TURNER,SALESMAN,7698,2011-09-08,1500,0,30
7876,ADAMS,CLERK,7788,2017-07-13,1100,,20
7900,JAMES,CLERK,7698,2011-12-03,950,,30
7902,FORD,ANALYST,7566,2011-12-03,3000,,20
7934,MILLER,CLERK,7782,2012-01-23,1300,,10

-- 建表并加载数据
CREATE TABLE emp (
empno int,
ename string, 
job string, 
mgr int, 
hiredate DATE, 
sal int, 
comm int, 
deptno int
)row format delimited fields terminated by ",";

-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.dat'
INTO TABLE emp;

 

第 1 节 基本查询

-- 省略from子句的查询
select 8*888 ;
select current_date ;

-- 使用列别名
select 8*888 product;
select current_date as currdate;

-- 全表查询
select * from emp;

-- 选择特定列查询
select ename, sal, comm from emp;

-- 使用函数
select count(*) from emp;

-- count(colname) 按字段进行count，不统计NULL
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;

-- 使用limit子句限制返回的行数
select * from emp limit 3;

 

第 2 节 where子句

WHERE子句紧随FROM子句，使用WHERE子句，过滤不满足条件的数据；
where 子句中不能使用列的别名；

select * from emp
where sal > 2000;

where子句中会涉及到较多的比较运算 和 逻辑运算；

 

比较运算符

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

备注：通常情况下NULL参与运算，返回值为NULL；NULL<=>NULL的结果为true

逻辑运算符
就是我们所熟悉的：and、or、not

-- 比较运算符，null参与运算
select null=null;
select null==null;
select null<=>null;

-- 使用 is null 判空
select * from emp where comm is null;

-- 使用 in
select * from emp where deptno in (20, 30);

-- 使用 between ... and ...
select * from emp where sal between 1000 and 2000;

-- 使用 like
select ename, sal from emp where ename like '%L%';

-- 使用 rlike。正则表达式，名字以A或S开头
select ename, sal from emp where ename rlike '^(A|S).*';

 

第 3 节 group by子句

GROUP BY语句通常与聚组函数一起使用，按照一个或多个列对数据进行分组，对每个组进行聚合操作。

-- 计算emp表每个部门的平均工资
select deptno, avg(sal)
from emp
group by deptno; 

-- 计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal)
from emp
group by deptno, job;

• where子句针对表中的数据发挥作用；having针对查询结果（聚组以后的结果）发挥作用
• where子句不能有分组函数；having子句可以有分组函数
• having只用于group by分组统计之后

-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;

 

第 4 节 表连接

Hive支持通常的SQL JOIN语句。默认情况下，仅支持等值连接，不支持非等值连接。

JOIN 语句中经常会使用表的别名。使用别名可以简化SQL语句的编写，使用表名前缀可以提高SQL的解析效率。

连接查询操作分为两大类：内连接和外连接，而外连接可进一步细分为三种类型：

1. 内连接: [inner] join
2. 外连接 (outer join)
 - 左外连接。 left [outer] join，左表的数据全部显示
 - 右外连接。 right [outer] join，右表的数据全部显示
 - 全外连接。 full [outer] join，两张表的数据都显示

案例演示：

-- 准备数据
u1.txt数据：
1,a
2,b
3,c
4,d
5,e
6,f

u2.txt数据：
4,d
5,e
6,f
7,g
8,h
9,i

create table if not exists u1(
id int,
name string)
row format delimited fields terminated by ',';

create table if not exists u2(
id int,
name string)
row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/data/u1.txt' into table u1;

load data local inpath '/home/hadoop/data/u2.txt' into table u2;

-- 内连接
select * from u1 join u2 on u1.id = u2.id;

-- 左外连接
select * from u1 left join u2 on u1.id = u2.id;

-- 右外连接
select * from u1 right join u2 on u1.id = u2.id;

-- 全外连接
select * from u1 full join u2 on u1.id = u2.id;

多表连接
连接 n张表，至少需要 n-1 个连接条件。例如：连接四张表，至少需要三个连接条件。

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生：

select *
from techer t 
left join course c on t.t_id = c.t_id
left join score s on s.c_id = c.c_id
left join student stu on s.s_id = stu.s_id;

Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个MapReduce 任务。

上面的例子中会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作；然后再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作；

然后再继续直到全部操作；

笛卡尔积

满足以下条件将会产生笛卡尔集：

• 没有连接条件
• 连接条件无效
• 所有表中的所有行互相连接

如果表A、B分别有M、N条数据，其笛卡尔积的结果将有 M*N 条数据；缺省条件下hive不支持笛卡尔积运算；

set hive.strict.checks.cartesian.product=false;

select * from u1, u2;

第 5 节 排序子句【重点】

全局排序(order by)

order by 子句出现在select语句的结尾；
order by子句对最终的结果进行排序；
默认使用升序(ASC)；可以使用DESC，跟在字段名之后表示降序；
ORDER BY执行全局排序，只有一个reduce；

-- 普通排序
select * from emp order by deptno;

-- 按别名排序, 下面的nvl是个函数, 将null转换为0
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno
from emp
order by salcomm desc;

-- 多列排序
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno
from emp
order by deptno, salcomm desc;

-- 排序字段要出现在select子句中。以下语句无法执行（因为select子句中缺少deptno）：
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm
from emp
order by deptno, salcomm desc;

每个MR内部排序(sort by)

对于大规模数据而言order by效率低；
在很多业务场景，我们并不需要全局有序的数据，此时可以使用sort by；
sort by为每个reduce产生一个排序文件，在reduce内部进行排序，得到局部有序的结果；

-- 设置reduce个数
set mapreduce.job.reduces=2;

-- 按照工资降序查看员工信息
select * from emp sort by sal desc;

-- 将查询结果导入到文件中（按照工资降序）。生成两个输出文件，每个文件内部数据按工资降序排列
insert overwrite local directory '/home/hadoop/output/sortsal'
row format delimited fields terminated by ' '
select * from emp sort by sal desc;

分区排序(distribute by)

distribute by 将特定的行发送到特定的reducer中，便于后继的聚合 与 排序操作；

distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；
distribute by 要写在sort by之前；

分区要注意分区条件

启动2个reducer task；先按 deptno 分区，在分区内按 sal+comm 排序
    set mapreduce.job.reduces=2;

将结果输出到文件，观察输出结果
    insert overwrite local directory '/home/hadoop/output/distBy'
    select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
    from emp
    distribute by deptno
    sort by salcomm desc;

    上例中，所有数据都被分到了统一区，看不出分区的结果
    是由于hash分区的时候,  10,20,30 % 20 都为0,  即分区条件写错

 将数据分到3个区中，每个分区都有数据
    set mapreduce.job.reduces=3;
    insert overwrite local directory '/home/hadoop/output/distBy1'
    select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
    from emp
    distribute by deptno
    sort by salcomm desc;

Cluster By

当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法；
cluster by 只能是升序，不能指定排序规则；

-- 升序的情况下, 语法上是等价的
select * from emp distribute by deptno sort by deptno;
select * from emp cluster by deptno;

排序小结：

• order by。执行全局排序，效率低。生产环境中慎用
• sort by。使数据局部有序(在reduce内部有序)
• distribute by。按照指定的条件将数据分组，常与sort by联用，使数据局部有序
• cluster by。当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法