hive笔记

hive

了解

Hive是基于Hadoop的一个数据仓库工具，可以将 结构化的数据文件 映射为一张表.

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

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

Hive的优点
学习成本低。Hive提供了类似SQL的查询语言，开发人员能快速上手；
处理海量数据。底层执行的是MapReduce 任务；
系统可以水平扩展。底层基于Hadoop；
功能可以扩展。Hive允许用户自定义函数；
良好的容错性。某个节点发生故障，HQL仍然可以正常完成；
统一的元数据管理。元数据包括：有哪些表、表有什么字段、字段是什么类型
Hive的缺点
HQL表达能力有限；
迭代计算无法表达；
Hive的执行效率不高(基于MR的执行引擎)；
Hive自动生成的MapReduce作业，某些情况下不够智能；
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) ：把逻辑执行计划转换成可以运行的物理计划；

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

	hadoop201	hadoop203	hadoop203
Hadoop	✔	v	✔
MySQL			✔
Hive			✔

# 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、MySQL安装

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

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

1、删除冲突依赖*

# 查询是否安装了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、创建用户
-- 创建用户设置口令、授权、刷新 
CREATE USER 'hive'@'%' IDENTIFIED BY '12345678';
GRANT ALL ON *.* TO 'hive'@'%'; 
FLUSH PRIVILEGES;

2、hive安装

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

1、安装
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.d/my_env.sh 文件中增加环境变量 
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://hadoop201:3306/hivemetadata?createDatabaseIfNotExist=true&amp;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、初始化元数据库
schematool -dbType mysql -initSchema

6、启动Hive，执行命令
# 启动hive服务之前，请先启动hdfs、yarn的服务 
[root@hadoop201 ~]$ hive 
hive> show functions;

3、Hive属性配置

可在 hive-site.xml 中增加以下常用配置，方便使用。
1、存儲位置
<property> 
    <!-- 数据默认的存储位置(HDFS) --> 
    <name>hive.metastore.warehouse.dir</name> 
    <value>/user/hive/warehouse</value> 
    <description>location of default database for the warehouse</description>
</property>
2、显示当前库
<property> <!-- 在命令行中，显示当前操作的数据库 --> 
    <name>hive.cli.print.current.db</name> 
    <value>true</value> 
    <description>Whether to include the current database in the Hive prompt</description> 
</property>
3、显示表头属性
<property> <!-- 在命令行中，显示数据的表头 --> 
    <name>hive.cli.print.header</name> 
    <value>true</value> 
</property>
4、本地模式
<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

5、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

Hadoop 2.x 中 NameNode RPC缺省的端口号：8020


6、添加第三方用户（Hadoop） 建议不要用hadoop 这种和命令一致的单词  要区分使用的命令和用户

groupadd hadoop 
# -m：自动建立用户的登入目录 
# -g：指定用户所属的起始群组 
# -G<群组>：指定用户所属的附加群组 
    # －s：指定用户登入后所使用的shell 
    
    useradd -m hadoop -g hadoop -s /bin/bash 
    passwd hadoop 
    visudo
    # 在100行后添加。允许用户执行sudo，免密 
    hadoop ALL=(ALL) NOPASSWD:ALL

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

配置文件的设定对本机启动的所有Hive进程有效；
配置文件的设定对本机所有启动的Hive进程有效；

启动时指定参数值

启动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

命令行修改参数

可在 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、常用命令

-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
    	
退出Hive命令行
	exit;
    quit;
    
在命令行执行 shell 命令 / dfs 命令
hive> ! ls; 
hive> ! clear; 

hive> dfs -ls /

3、数据类型与格式

1、数据类型 + 隐式转换

基本数据类型

TINYINT、SMALLINT、INT、BIGINT

BOOLEAN、

FLOAT、DOUBLE

STRING、

TIMESTAMP

BINARY

集合数据类型：array**、map、struct、union

ARRAY	有序的相同数据类型的集合	array(1,2)
MAP	key-value对。key必须是基 本数据类型，value不限	map(‘a’, 1, ‘b’,2)
STRUCT	不同类型字段的集合。	struct(‘1’,1,1.0), named_struct(‘col1’, ‘1’, ‘col2’, 1,‘clo3’, 1.0)
UNION	不同类型的元素存储在同一 字段的不同行中	create_union(1, ‘a’, 63)

数据类型的显示转换

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

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

集合数据类型

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;

2、文本文件数据编码

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;

\n  换行符   用于分隔行。每一行是一条记录，使用换行符分割数据 

^A  < Ctrl >+A   用于分隔字段。在CREATE TABLE语句中使用八进制编码 \001表示 

^B  < Ctrl >+B   用于分隔 ARRAY、MAP、STRUCT 中的元素。在CREATE TABLE语句中使用八进制编码\002表示 

^C  < Ctrl +C>  Map中 key、value之间的分隔符。在CREATE TABLE语句  中使用八进制编码\003表示

在 vi 中输入特殊字符：
(Ctrl + v) + (Ctrl + a) => ^A
(Ctrl + v) + (Ctrl + b) => ^B
(Ctrl + v) + (Ctrl + c) => ^C

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

cat -A 可以看到

4、HQ操作之-- DDL命令

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

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

database
	--table
		--分区
			--分桶

1、常用操作

1、创建数据库语法
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';

2、查看数据库
-- 查看所有数据库 
	show database;
-- 查看数据库信息 
	desc database mydb2; 
	desc database extended mydb2; 
	describe database extended mydb2;
	
3、使用数据库
	use mydb;
4、删除数据库
	-- 删除一个空数据库 
		drop database databasename;
     -- 如果数据库不为空，使用 cascade 强制删除 
     	drop database databasename cascade;

建表语法

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 ASSEQUENCEFILE（二进制序列文件）。
9. LOCATION。表在HDFS上的存放位置
10. TBLPROPERTIES。定义表的属性
11. AS。后面可以接查询语句，表示根据后面的查询结果创建表
12. LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

2、内部表 & 外部表

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

创建内部表

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 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关键字，创建的是外部表；
删表时
    删除外部表时，仅删除表的定义，表的数据不受影响
    删除内部表时，表的数据和定义同时被删除
外部表的使用场景
    想保留数据时使用。生产多用外部表

3、分区表

-- 创建表 

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、分桶表

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶 技术将数据划分成更细的粒度。

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 % 分桶数
分桶表加载数据时，使用 insert... select ... 方式进行
网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive
1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶；

5、修改表 & 删除表

-- 修改表名。
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;

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

5、 HQL操作之数据操作

1、导入数据

LOCAL：

LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷 贝到Hive表指定的位置

LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定 的位置

INPATH：加载数据的路径

OVERWRITE：覆盖表中已有数据；否则表示追加数据

PARTITION：将数据加载到指定的分区

-- 加载本地文件到
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt' INTO TABLE tabA;
-- 加载hdfs文件到
LOAD DATA INPATH 'data/sourceA.txt' INTO TABLE tabA;
-- 加载数据覆盖表中已有数据 
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';

2、插入数据(Insert)

-- 插入数据 
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';

-- 根据查询结果创建表 
create table if not exists tabD as select * from tabC; 123
--使用import导入数据
import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

3、数据导出

-- 将查询结果导出到本地 
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;
-- 以下语句报错，外部表不能执行 truncate 操作 
alter table tabC set tblproperties("EXTERNAL"="TRUE"); truncate table tabC;

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

6、HQL操作之–DQL命令

SQL****语句书写注意事项：

SQL语句对大小写不敏感

SQL语句可以写一行（简单SQL）也可以写多行(复杂SQL)

关键字不能缩写，也不能分行

各子句一般要分行

使用缩进格式，提高SQL语句的可读性(重要)

1、sql常识

where

select * from emp where sal > 2000; 

比较运算符

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

逻辑运算符

-- 比较运算符，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).*';

group by子句

-- 计算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;

表连接

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

-- 内连接 
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; 

多表连接

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 进行连接操作；

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

生笛卡尔集

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

没有连接条件

连接条件无效

所有表中的所有行互相连接

2、排序子句【重点】

1全局排序(order by)

order by 子句出现在select语句的结尾；

order by子句对最终的结果进行排序；

ORDER BY执行全局排序，只有一个reduce

-- 多列排序 
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;

2、每个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' 
select * from emp sort by sal desc;

3、分区排序(distribute by)

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

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

distribute by 要写在sort by之前；

-- 启动2个reducer task； 分区数量可能有很大影响和
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;

注意： 分区数量可能有很大影响和set mapreduce.job.reduces=3; 可能冲突

7、函数（重点）

-- 查看系统自带函数 
show functions;
-- 显示自带函数的用法 
desc function upper; 
desc function extended upper;

1、日期函数【重要】

-- 当前前日期 

select current_date; 
select unix_timestamp(); 
-- 建议使用current_timestamp，有没有括号都可以 
select current_timestamp(); 

-- 时间戳转日期 
select from_unixtime(1505456567); 
select from_unixtime(1505456567, 'yyyyMMdd'); 
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss'); 

-- 日期转时间戳 
select unix_timestamp('2019-09-15 14:23:00'); 

-- 计算时间差 
select datediff('2020-04-18','2019-11-21'); 
select datediff('2019-11-21', '2020-04-18'); 

-- 查询当月第几天 
select dayofmonth(current_date); 

-- 计算月末: 
select last_day(current_date); 

-- 当月第1天: 
select date_sub(current_date, dayofmonth(current_date)-1) 

-- 下个月第1天: 
select add_months(date_sub(current_date, 
dayofmonth(current_date)-1), 1) 

-- 字符串转时间（字符串必须为：yyyy-MM-dd格式） 
select to_date('2020-01-01'); 
select to_date('2020-01-01 12:12:12'); 

-- 日期、时间戳、字符串类型格式化输出标准时间格式 
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss'); 
select date_format(current_date(), 'yyyyMMdd'); 
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss'); 

-- 计算emp表中，每个人的工龄 
select *, round(datediff(current_date, hiredate)/365,1) workingyears from emp; 

2、字符串函数

-- 转小写
lower select lower("HELLO WORLD"); 
-- 转大写。
upper select lower(ename), ename from emp;
-- 求字符串长度。
length select length(ename), ename from emp; 
-- 字符串拼接。 concat / || 
select empno || " " ||ename idname from emp;
select concat(empno, " " ,ename) idname from emp; 
-- 指定分隔符。concat_ws(separator, [string | array(string)]+) 
SELECT concat_ws('.', 'www', array('lagou', 'com'));
select concat_ws(" ", ename, job) from emp; 
-- 求子串。substr SELECT substr('www.lagou.com', 5);
SELECT substr('www.lagou.com', -5); 
SELECT substr('www.lagou.com', 5, 5); 
-- 字符串切分。split，注意 '.' 要转义 
select split("www.lagou.com", "\\.");

3、条件函数【重要】

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull) 

select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp; 
-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END 
-- 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上 

select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp; 

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END 
-- 复杂条件用 case when 更直观 
select sal, case when sal<=1500 then 1 when sal<=3000 then 2 
else 3 end sallevel 
from emp; 

-- 以下语句等价 
select ename, deptno, 
case deptno when 10 then 'accounting' 
when 20 then 'research' 
when 30 then 'sales' 
else 'unknown' end deptname 
from emp; 

select ename, deptno, 
case when deptno=10 then 'accounting' 
when deptno=20 then 'research' 
when deptno=30 then 'sales' 
else 'unknown' end deptname 
from emp; 

-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为 
NULL，那么返回NULL 
select sal, coalesce(comm, 0) from emp; 

-- isnull(a) isnotnull(a) 
select * from emp where isnull(comm); 
select * from emp where isnotnull(comm); 

-- nvl(T value, T default_value) 
select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal 
from emp; 

-- nullif(x, y) 相等为空，否则为a 
SELECT nullif("b", "b"), nullif("b", "a");

4、UDTF函数【重要】

用户定义表生成函数，一行输 入，多行输出

-- explode，炸裂函数 
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行 
select explode(array('A','B','C')) as col; 
select explode(map('a', 8, 'b', 88, 'c', 888)); 

-- UDTF's are not supported outside the SELECT clause, nor nested in expressions 
-- SELECT pageid, explode(adid_list) AS myCol... is not supported 
-- SELECT explode(explode(adid_list)) AS myCol... is not supported 
-- lateral view 常与 表生成函数explode结合使用 
-- lateral view 语法： 

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)* 
fromClause: FROM baseTable (lateralView)* 

-- lateral view 的基本使用 
with t1 as ( 
select 'OK' cola, split('www.lagou.com', '\\.') colb
)

select cola, colc 
from t1
lateral view explode(colb) t2 as colc;

举例

select id, split(tags, ',') from tab1;

select id, tag from tab1 lateral view explode(split(tags, ",")) t1 as tag;

-- 需求：找到每个学员的最好成绩 
-- 第一步，使用 explode 函数将map结构拆分为多行 
select explode(score) as (subject, socre) from studscore; 

--但是这里缺少了学员姓名，加上学员姓名后出错。下面的语句有是错的 
select name, explode(score) as (subject, socre) from studscore; 

-- 第二步：explode常与 lateral view 函数联用，这两个函数结合在一起能关 联其他字段 

select name, subject, score1 as score from studscore lateral view explode(score) t1 as subject, score1; 

-- 第三步：找到每个学员的最好成绩 
select name, max(mark) maxscore 
from (select name, subject, mark 
from studscore lateral view explode(score) t1 as 
subject, mark) t1 
group by name; 

with tmp as ( 
select name, subject, mark 
from studscore lateral view explode(score) t1 as subject, 
mark 
)
select name, max(mark) maxscore 
from tmp 
group by name; 

将一行数据转换成多行数据，可以用于array和map类型的数据；

lateral view 与 explode 联用，解决 UDTF 不能添加额外列的问题

5、窗口函数【重要】

1、over partition by order by Window关键字

-- 查询emp表工资总和
select sum(sal) from emp; 
-- 不使用窗口函数，有语法错误 
select ename, sal, sum(sal) salsum from emp;
-- 使用窗口函数，查询员工姓名、薪水、薪水总和 
select ename, sal, sum(sal) over() salsum, concat(round(sal / sum(sal) over()*100, 1) || '%') ratiosal from emp;


-- 增加了 partition by  order by子句；sum：从分组的第一行到当前行求和
select ename, sal, deptno, sum(sal) over(partition by deptno order by sal) salsum from emp;

--Window
unbounded preceding。组内第一行数据
n preceding。组内当前行的前n行数据
current row。当前行数据
n following。组内当前行的后n行数据
unbounded following。组内最后一行数据

-- 等价。组内，第一行到当前行的和 
select ename, sal, deptno,
sum(sal) over(partition by deptno order by ename) from emp; 

select ename, sal, deptno, 
sum(sal) over(partition by deptno order by ename rows between unbounded preceding and current row ) from emp;

-- 组内，前一行、当前行、后一行的和 
select ename, sal, deptno, 
sum(sal) over(partition by deptno order by ename rows between 1 preceding and 1 following ) from emp;

注意：窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果 集；

2、排名函数

row_number()。排名顺序增加不会重复；如1、2、3、4、… …

RANK()。 排名相等会在名次中留下空位；如1、2、2、4、5、… …

DENSE_RANK()。 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、

-- 按照班级，使用3种方式对成绩进行排名
select cname, sname, score, 
row_number() over (partition by cname order by score desc) rank1, 
rank() over (partition by cname order by score desc) rank2, 
dense_rank() over (partition by cname order by score desc) rank3 
from t2;

3、序列函数

lag。返回当前数据行的上一行数据

lead。返回当前数据行的下一行数据

first_value。取分组内排序后，截止到当前行，第一个值

last_value。分组内排序后，截止到当前行，最后一个值

ntile。将分组的数据按照顺序切分成n片，返回当前切片值

-- lag。返回当前数据行的上一行数据 
-- lead。功能上与lag类似 
select cid, ctime, pv, 
lag(pv) over(partition by cid order by ctime) lagpv, 
lead(pv) over(partition by cid order by ctime) leadpv 
from userpv; 

-- first_value / last_value
select cid, ctime, pv, 
first_value(pv) over (partition by cid order by ctime rows between unbounded preceding and unbounded following) as firstpv,
last_value(pv) over (partition by cid order by ctime rows between unbounded preceding and unbounded following) as lastpv 
from userpv; 

-- ntile。按照cid进行分组，每组数据分成2份 
select cid, ctime, pv, ntile(2) over(partition by cid order by ctime) ntile from userpv;

6、 自定义函数*

UDF（User Defined Function）。用户自定义函数，一进一出

UDAF（User Defined Aggregation Function）。用户自定义聚集函数，多进一 出；类似于：count/max/min

UDTF（User Defined Table-Generating Functions）。用户自定义表生成函 数，一进多出；类似于：explode

UDF开发：

继承org.apache.hadoop.hive.ql.exec.UDF 
需要实现evaluate函数；evaluate函数支持重载 
UDF必须要有返回类型，可以返回null，但是返回类型不能为void 

UDF开发步骤

创建maven java 工程，添加依赖 
<!-- pom.xml 文件 --> 
<dependencies> 
    <dependency> 
        <groupId>org.apache.hive</groupId> 
        <artifactId>hive-exec</artifactId> 
        <version>2.3.7</version> 
    </dependency> 
</dependencies>
开发java类继承UDF，实现evaluate 方法 
package cn.lagou.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF; 
import org.apache.hadoop.io.Text; 
public class nvl extends UDF { 
	public Text evaluate(final Text t, final Text x) {
    	if (t == null || t.toString().trim().length()==0) { 
    		return x; 
    	}
    	return t;
    } 
}
将项目打包上传服务器 
添加开发的jar包 
add jar /home/hadoop/hiveudf.jar;
设置函数与自定义函数关联 
create temporary function mynvl as "cn.lagou.hive.udf.nvl";
使用自定义函数
select mynvl(comm, 0) from mydb.emp;

可创建永久函数：

-- 将jar上传HDFS
hdfs dfs -put hiveudf.jar jar/
-- 在Hive命令行中创建永久函数
create function mynvl1 as 'cn.lagou.hive.udf.nvl' using jar 'hdfs:/user/hadoop/jar/hiveudf.jar';
-- 查询所有的函数，发现 mynvl1 在列表中 
show functions;
退出Hive，再进入，执行测试
select mynvl(comm, 0) from mydb.emp;	

删除
drop function mynvl1;
show functions;

8、HQL操作之–DML命令

1、注意事项

Hive提供行级别的ACID语义
BEGIN、COMMIT、ROLLBACK 暂时不支持，所有操作自动提交
目前只支持 ORC 的文件格式
默认事务是关闭的，需要设置开启
要是使用事务特性，表必须是分桶的
只能使用内部表
如果一个表用于ACID写入（INSERT、UPDATE、DELETE），必须在表中设置表属性 : "transactional=true"
必须使用事务管理器 org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
目前支持快照级别的隔离。就是当一次数据查询时，会提供一个数据一致性的快照
LOAD DATA语句目前在事务表中暂时不支持


HDFS是不支持文件的修改；并且当有数据追加到文件，HDFS不对读数据的用户提供一致性的。为了在HDFS上支持数据的更新：
表和分区的数据都被存在基本文件中（base files）
新的记录和更新，删除都存在增量文件中（delta files）
一个事务操作创建一系列的增量文件
在读取的时候，将基础文件和修改，删除合并，最后返回给查询

-- 这些参数也可以设置在hive-site.xml中
SET hive.support.concurrency = true; 
-- Hive 0.x and 1.x only 
SET hive.enforce.bucketing = true;
SET hive.exec.dynamic.partition.mode = nonstrict; 
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

9、元数据管理与存储

1、Metastore

Metadata即元数据。元数据包含用Hive创建的database、table、表的字段等元信

息。元数据存储在关系型数据库中。如hive内置的Derby、第三方如MySQL等。

Metastore即元数据服务，是Hive用来管理库表元数据的一个服务。有了它上层的

服务不用再跟裸的文件数据打交道，而是可以基于结构化的库表信息构建计算框架

metastore三种配置方式

1、内嵌方式

优点：配置简单，解压hive安装包 bin/hive 启动即可使用；
缺点：不同路径启动hive，每一个hive拥有一套自己的元数据，无法共享。

2、本地模式
缺点：每启动一次hive服务，都内置启动了一个metastore；在hive-site.xml中暴露 的数据库的连接信息；
优点：配置较简单，本地模式下hive的配置中指定mysql的相关信息即可。

3. 远程模式
需要单独起metastore服务，然后每个客户端都在配置文件里配置连接
到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。
在生产环境中，建议用远程模式来配置Hive Metastore
在这种模式下，其他依赖Hive的软件都可以通过Metastore访问Hive。此时需要配置 hive.metastore.uris 参数来指定 metastore 服务运行的机器ip和端口，并且需要单 独手动启动metastore服务。metastore服务可以配置多个节点上，避免单节点故障 导致整个集群的hive client不可用。同时hive client配置多个metastore地址，会自 动选择可用节点。

1、下载软件解压缩 
2、设置环境变量，并使之生效 
3、初始化数据库。 schematool -dbType derby -initSchema 
4、进入hive命令行 
5、再打开一个hive命令行，发现无法进入

1、在linux121、linux123上分别启动 metastore 服务
启动 metastore 服务 
nohup hive --service metastore & 
# 查询9083端口(metastore服务占用的端口) 
lsof -i:9083 
2、修改 linux122 上hive-site.xml
<!-- hive metastore 服务地址 --> 
<property> <name>hive.metastore.uris</name> <value>thrift://linux121:9083,thrift://linux123:9083</value> </property>
3、启动hive。此时client端无需实例化hive的metastore，启动速度会加快。
# 分别在linux121、linux123上执行以下命令，查看连接情况
lsof -i:9083
4、测试 高可用测试。关闭已连接的metastore服务，发现hive连到另一个节点的服务上，仍然能够正常使用。

2、HiveServer2

目前基于Thrift RPC的实现是HiveServer的改进版本

HiveServer2（HS2）是一种允许客户端对Hive执行查询的服务

1、修改集群上的 core-site.xml，增加以下内容
<!-- HiveServer2 连不上10000；hadoop为安装用户 --> 
<!-- root用户可以代理所有主机上的所有用户 --> 
<property><name>hadoop.proxyuser.root.hosts</name> 
<value>*</value> </property> <property><name>hadoop.proxyuser.root.groups</name> 
<value>*</value> </property> <property><name>hadoop.proxyuser.hadoop.hosts</name> 
<value>*</value> </property> <property><name>hadoop.proxyuser.hadoop.groups</name> 
<value>*</value> 
</property> 
2、修改 集群上的 hdfs-site.xml，
<!-- HiveServer2 连不上10000；启用 webhdfs 服务 -->
 <property>
 <name>dfs.webhdfs.enabled</name> 
 <value>true</value> 
 </property>
3、启动linux123上的 HiveServer2 服务
# 启动 hiveserver2 服务 
nohup hiveserver2 & 
# 检查 hiveserver2 端口 
lsof -i:10000 
# 从2.0开始，HiveServer2提供了WebUI 
# 还可以使用浏览器检查hiveserver2的启动情况。
http://linux123:10002/
4、启动 linux122 节点上的 beeline
!connect jdbc:hive2://linux123:10000
 use mydb; 
 show tables;
  select * from emp; 
  create table tabtest1 (c1 int, c2 string);
   !connect jdbc:mysql://linux123:3306 
   !help 
   !quit

3、HCatalog

HCatalog 提供了一个称为 hcat 的命令行工具

# 进入 hcat 所在目录。
$HIVE_HOME/hcatalog/bin 
cd $HIVE_HOME/hcatalog/bin 
# 执行命令，创建表 
./hcat -e "create table default.test1(id string, name string, age int)"
 # 长命令可写入文件，使用 -f 选项执行 
 ./hcat -f createtable.txt
  # 查看元数据 
  ./hcat -e "use mydb; show tables"
   # 查看表结构
./hcat -e "desc mydb.emp" 
# 删除表 
./hcat -e "drop table default.test1"

4、数据存储格式

Hive支持的存储数的格式主要有：
TEXTFILE（默认格式） 、
SEQUENCEFILE、
RCFILE、
ORCFILE、
PARQUET。

textfile为默认格式，建表时没有指定文件格式，则使用TEXTFILE，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；
sequencefile，rcfile，orcfile格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入sequencefile、rcfile、orcfile表中。

行式存储：
优点：数据被保存在一起，insert和update更加容易
缺点：选择（selection）时即使只涉及某几列，所有数据也都会被读取
列式存储：
优点：查询时只有涉及到的列会被读取，效率高
缺点：选中的列要重新组装，insert/update比较麻烦

TEXTFILE、SEQUENCEFILE 的存储格式是基于行存储的；
ORC和PARQUET 是基于列式存储的。

TextFile
Hive默认的数据存储格式，数据不做压缩，磁盘开销大，数据解析开销大.使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作

SEQUENCEFILE
SequenceFile是Hadoop API提供的一种二进制文件格式，其具有使用方便、可分割、可压缩的特点。 SequenceFile支持三种压缩选择：none，record，block。Record压缩率低，一般建议使用BLOCK压缩

RCFile
RCFile遵循的“先水平划分，再垂直划分”的设计理念

一张表可以包含多个HDFS block
在每个block中，RCFile以行组为单位存储其中的数据
row group又由三个部分组成
	用于在block中分隔两个row group的16字节的标志区
	存储row group元数据信息的header
	实际数据区，表中的实际数据以列为单位进行存储

ORCFile

文件脚注(file footer)：包含了文件中 stripe 的列表，每个stripe行数，以及每个列的数据类型。还包括每个列的最大、最小值、行计数、求和等信息

postscript：压缩参数和压缩大小相关信息

条带(stripe)：ORC文件存储数据的地方。在默认情况下，一个stripe的大小为250MB
	Index Data：一个轻量级的index，默认是每隔1W行做一个索引。包括该
	条带的一些统计信息，以及数据在stripe中的位置索引信息
	Rows Data：存放实际的数据。先取部分行，然后对这些行按列进行存储。
	对每个列进行了编码，分成多个stream来存储
	Stripe Footer：存放stripe的元数据信息

Parquet

Apache Parquet是Hadoop生态圈中一种新型列式存储格式，它可以兼容Hadoop生态圈中大多数计算框架(Mapreduce、Spark等)，被多种查询引擎支持（Hive、Impala、Drill等），与语言和平台无关的。
Parquet文件是以二进制方式存储的，不能直接读取的，文件中包括实际数据和元数据，Parquet格式文件是自解析的。

Row group:
	写入数据时的最大缓存单元
	MR任务的最小并发单元
	一般大小在50MB-1GB之间
Column chunk：
	存储当前Row group内的某一列数据
	最小的IO并发单元
Page：
	压缩、读数据的最小单元
	获得单条数据时最小的读取数据单元
	大小一般在8KB-1MB之间，越大压缩效率越高
Footer：
	数据Schema信息
	每个Row group的元信息：偏移量、大小
	每个Column chunk的元信息：每个列的编码格式、首页偏移量、首索引页
	偏移量、个数、大小等信息

在生产环境中，Hive表的数据格式使用最多的有三种：
TextFile、ORCFile、Parquet。

TextFile文件更多的是作为跳板来使用(即方便将数据转为其他格式)
有update、delete和事务性操作的需求，通常选择ORCFile
没有事务性要求，希望支持Impala、Spark，建议选择Parquet

10、Hive调优策略

从以下三个方面展开： 架构优化 参数优化 SQL优化

1、架构优化

执行引擎
Hive支持多种执行引擎，分别是 MapReduce、Tez、Spark、Flink。可以通过hivesite.xml文件中的hive.execution.engine属性控制。

优化器
Hive有两种优化器：Vectorize(向量化优化器) 与 Cost-Based
Optimization (CBO 成本优化器)。
矢量化查询执行 必须用ORC格式存储数据

set hive.vectorized.execution.enabled = true; 
-- 默认 false 
set hive.vectorized.execution.reduce.enabled = true; 
-- 默认 false

成本优化器

SET hive.cbo.enable=true; --从 v0.14.0默认 true 
SET hive.compute.query.using.stats=true; -- 默认false
 SET hive.stats.fetch.column.stats=true; -- 默认false 
 SET hive.stats.fetch.partition.stats=true; -- 默认true

分区表
分桶表
文件格式
数据压缩
压缩技术可以减少map与reduce之间的数据传输
hive-site.xml

SET hive.exec.compress.intermediate=true

压缩的编码器可以通过mapred-site.xml, hive-site.xml进行配置

-- 中间结果压缩 SET hive.intermediate.compression.codec=org.apache.hadoop.io.compr ess.SnappyCodec ; 
-- 输出结果压缩 
SET hive.exec.compress.output=true; 
SET mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodc

设计阶段：

执行引擎 优化器 分区、分桶 文件格式 数据压缩

2、参数优化

**本地模式 ：**当Hive处理的数据量较小时

SET hive.exec.mode.local.auto=true; -- 默认 false 
SET hive.exec.mode.local.auto.inputbytes.max=50000000; 
SET hive.exec.mode.local.auto.input.files.max=5; -- 默认 4 


一个作业只要满足下面的条件，会启用本地模式
输入文件的大小小于 hive.exec.mode.local.auto.inputbytes.max 配置的大小
map任务的数量小于 hive.exec.mode.local.auto.input.files.max 配置的大小
reduce任务的数量是1或者0

严格模式：
强制不允许用户执行3种有风险的HiveQL语句

一旦执行会直接失败。这3种语句是：
	查询分区表时不限定分区列的语句；
	两表join产生了笛卡尔积的语句；
	用order by来排序，但没有指定limit的语句。
set hive.mapred.mode=nostrict

JVM重用 ：

JVM的重用适用于同一个作业的map和reduce，对于不同作业的task不能够共享
JVM。如果要开启JVM重用，需要配置一个作业最大task数量，默认值为1，如果设置为-1，则表示不限制

# 代表同一个MR job中顺序执行的5个task重复使用一个JVM，减少启动和关闭的开销 
SET mapreduce.job.jvm.numtasks=5;

这个功能的缺点是，开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。

**并行执行 ： **

Hive的查询通常会被转换成一系列的stage，这些stage之间并不是一直相互依赖的，可以并行执行这些stage

SET hive.exec.parallel=true; -- 默认false SET hive.exec.parallel.thread.number=16; -- 默认8

**推测执行： **

一个作业的多个任务之间运行速度不一致
set mapreduce.map.speculative=true 
set mapreduce.reduce.speculative=true 
set hive.mapred.reduce.tasks.speculative.execution=true

**合并小文件 : **

在map执行前合并小文件，减少map数
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInpu tFormat;

在Map-Reduce的任务结束时合并小文件
# 在 map-only 任务结束时合并小文件，默认true 
SET hive.merge.mapfiles = true; 
# 在 map-reduce 任务结束时合并小文件，默认false 
SET hive.merge.mapredfiles = true; 
# 合并文件的大小，默认256M 
SET hive.merge.size.per.task = 268435456; 
# 当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件 merge 
SET hive.merge.smallfiles.avgsize = 16777216;

Fetch模式
Fetch模式是指Hive中对某些情况的查询可以不必使用MapReduce计算。selectcol1, col2 from tab ;

在开启
fetch模式之后，在全局查找、字段查找、limit查找等都不启动 MapReduce 。# Default Value: minimal in Hive 0.10.0 through 0.13.1, more in Hive 0.14.0 and later 
hive.fetch.task.conversion=more

参数调整：

本地模式 严格模式 JVM重用 并行执行 推测还行 合并小文件 Fetch模式

3、SQL优化

列裁剪和分区裁剪
列裁剪是在查询时只读取需要的列；分区裁剪就是只读取需要的分区。
简单的说：select 中不要有多余的列，坚决避免 select * from tab;
sort by 代替 order by

如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by 一同使用。如果不加 distribute by 的话，map端数据就会随机分配到reducer。

group by 代替 count(distinct)
当要统计某一列的去重数时，如果数据量很大，count(distinct) 会非常慢。原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理
group by 配置调整

1、map端预聚合  
group by时，如果先起一个combiner在map端做部分预聚合，可以有效减少shuffle数据量
-- 默认为true 
set hive.map.aggr = true
Map端进行聚合操作的条目数
set hive.groupby.mapaggr.checkinterval = 100000

2、倾斜均衡配置项
Hive自带了一个均
衡数据倾斜的配置项 hive.groupby.skewindata ，默认值false。
建议了解数据倾斜的细节，并优化查询语句

join 基础优化
map join

map端连接，与普通连接的区别是这个连接中不会有reduce阶段存在，连接在map端完成
适用场景：大表与小表连接，小表数据量应该能够完全加载到内存，否则不适用

bucket map join 分桶连接

注意点：小表与大表的分桶数量需要是倍数关系，这个是因为分桶策略决定的，分桶时会根据分桶字段对桶数取余后决定哪个桶的，所以要保证成倍数关系。
优点：比map join对内存的要求降低，能在逐行对比时减少数据计算量（不用比对小表全量）
缺点：只适用于分桶表

利用map join特性
分桶表map join
倾斜均衡配置项
处理空值或无意义值
单独处理倾斜key
调整 Map 数
对于小文件采用的策略是合并
对于复杂文件采用的策略是增加 Map 数。
调整 Reduce 数

4、优化总结

不怕数据多，就怕数据倾斜
对 job 数比较多的作业运行效率相对比较低，比如即使有几百行的表，多次关联多次汇总，产生十几个jobs，执行也需要较长的时间。MapReduce 作业初始化的时间是比较长的
对sum、count等聚合操作而言，不存在数据倾斜问题
count(distinct) 效率较低，数据量大容易出问题
从大的方面来说，优化可以从几个方面着手：
好的模型设计，事半功倍
解决数据倾斜问题。仅仅依靠参数解决数据倾斜，是通用的优化手段，收获有
限。开发人员应该熟悉业务，了解数据规律，通过业务逻辑解决数据倾斜往往更
可靠减少 job 数
设置合理的map、reduce task数
对小文件进行合并，是行之有效的提高Hive效率的方法
优化把握整体，单一作业的优化不如整体最优

11、数据采集工具 – Flume

Flume是实时采集日志的数据采集引擎。
Flume有3个重要组件：Source、Channel、Sink

分布式：flume分布式集群部署，扩展性好
可靠性好: 当节点出现故障时，日志能够被传送到其他节点上而不会丢失
易用性：flume配置使用较繁琐，对使用人员专业技术要求高
实时采集：flume采集流模式进行数据实时采集

其他数据采集工具还有：dataX、kettle、Logstash、Scribe、sqoop。`

1、 Flume体系结构

2、Flume拓扑结构

串行模式
将多个flume给顺序连接起来，从最初的source开始到最终sink传送的目的存储系统。

复制模式(单Souce多Channel、Sink模式) 将数据源复制到多个channel中，每个channel都有相同的数据，sink可以选择传送的不同的目的地。

负载均衡模式(单Source、Channel多Sink) 主要解决负载
均衡和故障转移问题

聚合模式 每台服务器部署一个flume采集日志，传送到一个集中收集日志的flume，再由此flume上传到hdfs、hive、hbase、消息队列中。

原理

中文flume帮助文档
https://flume.liyifeng.org/

3、 监控目录采集信息到HDFS

flume-spooldir-hdfs.conf

a3.sources = r3 
a3.channels = c3 
a3.sinks = k3
a3.sources.r3.type = spooldir 
a3.sources.r3.spoolDir = /root/upload a3.sources.r3.fileSuffix = .COMPLETED a3.sources.r3.fileHeader = true
--忽略以.tmp结尾的文件，不上传
a3.sources.r3.ignorePattern = ([^ ]*\.tmp)

a3.channels.c3.type = memory 
a3.channels.c3.capacity = 10000 a3.channels.c3.transactionCapacity = 500

a3.sinks.k3.type = hdfs a3.sinks.k3.hdfs.path = hdfs://linux121:8020/flume/upload/%Y%m%d/%H%M


# 上传文件的前缀 
a3.sinks.k3.hdfs.filePrefix = upload- 
# 是否使用本地时间戳 
a3.sinks.k3.hdfs.useLocalTimeStamp = true
 # 积攒500个Event，flush到HDFS一次 
 a3.sinks.k3.hdfs.batchSize = 500 
 # 设置文件类型 
 a3.sinks.k3.hdfs.fileType = DataStream 
 # 60秒滚动一次 
 a3.sinks.k3.hdfs.rollInterval = 60 
 # 128M滚动一次 
 a3.sinks.k3.hdfs.rollSize = 134217700 
 # 文件滚动与event数量无关 
 a3.sinks.k3.hdfs.rollCount = 0 
 # 最小冗余数 
 a3.sinks.k3.hdfs.minBlockReplicas = 1
 # Bind the source and sink to the channel 
 a3.sources.r3.channels = c3 a3.sinks.k3.channel = c3
2、启动Agent
$FLUME_HOME/bin/flume-ng agent --name a3 \ 
--conf-file ~/conf/flume-spooldir-hdfs.conf \ 
-Dflume.root.logger=INFO,console 123
3、向upload文件夹中添加文件
4、查看HDFS上的数据
 

一般使用 HDFS Sink 都会采用滚动生成文件的方式，滚动生成文件的策略有：
基于时间
hdfs.rollInterval
缺省值：30，单位秒
0禁用
基于文件大小
hdfs.rollSize
缺省值：1024字节
0禁用
基于event数量
hdfs.rollCount
10
0禁用
基于文件空闲时间
hdfs.idleTimeout
缺省值：0。禁用
基于HDFS文件副本数
hdfs.minBlockReplicas
默认：与HDFS的副本数一致
要将该参数设置为1；否则HFDS文件所在块的复制会引起文件滚动



其他重要配置：
hdfs.useLocalTimeStamp
使用本地时间，而不是event header的时间戳
默认值：false
hdfs.round
时间戳是否四舍五入
默认值false
如果为true，会影响所有的时间，除了t%
hdfs.roundValue
四舍五入的最高倍数（单位配置在hdfs.roundUnit），但是要小于当前时间
默认值：1
hdfs.roundUnit
可选值为：second、minute、hour
默认值：second

如果要避免HDFS Sink产生小文件，参考如下参数设置：

a1.sinks.k1.type=hdfs 
a1.sinks.k1.hdfs.useLocalTimeStamp=true a1.sinks.k1.hdfs.path=hdfs://linux121:9000/flume/events/%Y/%m/ %d/%H/%M a1.sinks.k1.hdfs.minBlockReplicas=1 a1.sinks.k1.hdfs.rollInterval=3600 a1.sinks.k1.hdfs.rollSize=0 
a1.sinks.k1.hdfs.rollCount=0 
a1.sinks.k1.hdfs.idleTimeout=0

监控日志文件采集数据到HDFS、本地文件系统

需要多个Agent级联实现
source 选择 taildir
channel 选择 memory
最终的 sink 分别选择 hdfs、file_roll

类似
1

1、flume-taildir-avro.conf 配置文件包括：
	1个 taildir source
	2个 memory channel
	2个 avro sink
2、flume-avro-hdfs.conf配置文件包括：
	1个 avro source
	1个 memory channel
	1个 hdfs sink
3、flume-avro-file.conf配置文件包括
	1个 avro source
	1个 memory channel
	1个 file_roll sink
4、分别启动3个Agent 注意启动顺序
$FLUME_HOME/bin/flume-ng agent --name a3 \ 
--conf-file ~/conf/flume-avro-file.conf \ 
-Dflume.root.logger=INFO,console & 

$FLUME_HOME/bin/flume-ng agent --name a2 \ 
--conf-file ~/conf/flume-avro-hdfs.conf \ 
-Dflume.root.logger=INFO,console & 

$FLUME_HOME/bin/flume-ng agent --name a1 \ 
--conf-file ~/conf/flume-taildir-avro.conf \ 
-Dflume.root.logger=INFO,console &
5、执行hive命令产生日志
hive -e "show databases"
6、分别检查HDFS文件、本地文件、以及消费位置文件

```bash
# 3种监控日志文件Source的对比 
exec Source：适用于监控一个实时追加的文件，但不能保证数据不丢失； spooldir Source：能够保证数据不丢失，且能够实现断点续传，但延迟较高，不能 实时监控； 
taildir Source：既能够实现断点续传，又可以保证数据不丢失，还能够进行实时监 控。

4、高级特性

Flume里面的拦截器是实现了org.apache.flume.interceptor.Interceptor 接口的类；
拦截器可以根据配置 修改 甚至 丢弃 event；
Flume也支持链式的拦截器执行方式，在配置文件里面配置多个拦截器就可以了；
拦截器的顺序取决于它们配置的顺序，Event 按照顺序经过每一个拦截器；

1、拦截器

时间添加拦截器测试：

# 这部分是新增 时间拦截器的 内容 
a1.sources.r1.interceptors = i1 a1.sources.r1.interceptors.i1.type = timestamp 
# 是否保留Event header中已经存在的同名时间戳，缺省值false 
a1.sources.r1.interceptors.i1.preserveExisting= false 
# 这部分是新增 时间拦截器的 内容

Host添加拦截器

# 这部分是新增 主机名拦截器 的内容 
a1.sources.r1.interceptors.i2.type = host 
# 如果header中已经存在同名的属性是否保留 
a1.sources.r1.interceptors.i2.preserveExisting= false 
# true：使用IP地址；false：使用hostname 
a1.sources.r1.interceptors.i2.useIP = false 
# 这部分是新增 主机名拦截器 的内容

正则表达式过滤拦截器

2、选择器

a1.sources = r1 a1.channels = c1 c2 c3 c4 a1.sources.r1.selector.type = multiplexing a1.sources.r1.selector.header = state 
#以每个Event的 header中的state这个属性的值作为选择channel的依据 
a1.sources.r1.selector.mapping.CZ = c1
 #如果state=CZ，则选 择c1这个channel 
 a1.sources.r1.selector.mapping.US = c2 c3
  #如果state=US，则选 择c2 和 c3 这两个channel 
  a1.sources.r1.selector.default = c4 
  #默认使用c4这个 channel

自定义选择器
自定义选择器就是开发一个 org.apache.flume.ChannelSelector 接口的实现类。

a1.sources = r1 
a1.channels = c1
a1.sources.r1.selector.type = org.liyifeng.flume.channel.MyChannelSelector

3、 Sink组逻辑处理器

1、处理器

N个sink将Event输出到对应的N个目的地的，通过 Sink组逻辑处理器 可以把这N个sink配置成负载均衡或者故障转移的工作方式：

负载均衡是将channel里面的Event，按照配置的负载机制（比如轮询）分别发
送到sink各自对应的目的地

故障转移是这N个sink同一时间只有一个在工作，其余的作为备用，工作的sink
挂掉之后备用的sink顶上

a1.sinkgroups = g1 
a1.sinkgroups.g1.sinks = k1 k2 a1.sinkgroups.g1.processor.type = load_balance

故障转移
不仅要设置sink组的选择器为failover，还有为每一个sink设置一个唯一的优先级数值。 可以使用 maxpenalty 属性设置故障转移时间的上限（毫秒）

a1.sinkgroups = g1 a1.sinkgroups.g1.sinks = k1 k2 a1.sinkgroups.g1.processor.type = failover a1.sinkgroups.g1.processor.priority.k1 = 5 a1.sinkgroups.g1.processor.priority.k2 = 10 a1.sinkgroups.g1.processor.maxpenalty = 10000

负载均衡
支持轮询（ round_robin ）【默认值】和随机（ random ）两种选择机制分配负载

工作时，此选择器使用其配置的选择机制选择下一个sink并调用它。 如果所选sink无法正常工作，则处理器通过其配置的选择机制选择下一个可用sink。 此实现不会将失败的Sink列入黑名单，而是继续乐观地尝试每个可用的Sink。
如果所有sink调用都失败了，选择器会将故障抛给sink的运行器。
如果 backoff 设置为true则启用了退避机制，失败的sink会被放入黑名单，达到一定的超时时间后会自动从黑名单移除。 如从黑名单出来后sink仍然失败，则再次进入黑名单而且超时时间会翻倍，以避免在无响应的sink上浪费过长时间。 如果没有启用退避机制，在禁用此功能的情况下，发生sink传输失败后，会将本次负载传给下一个sink继续尝试，因此这种情况下是不均衡的。
a1.sinkgroups = g1 a1.sinkgroups.g1.sinks = k1 k2 a1.sinkgroups.g1.processor.type = load_balance a1.sinkgroups.g1.processor.backoff = true a1.sinkgroups.g1.processor.selector = random

2、事务机制与可靠性

在Flume中一共有两个事务：
Put事务。在Source到Channel之间
Take事务。Channel到Sink之间

Flume中的 Put 事务

事务开始的时候会调用一个 doPut 方法， doPut 方法将一批数据放在putList中；
	putList在向 Channel 发送数据之前先检查 Channel 的容量能否放得
	下，如果放不下一个都不放，只能doRollback；
	数据批的大小取决于配置参数 batch size 的值；
	putList的大小取决于配置 Channel 的参数 transaction capacity 
	的大小，该参数大小就体现在putList上；（Channel的另一个参数 
	capacity 指的是 Channel 的容量）；
数据顺利的放到putList之后，接下来可以调用 doCommit 方法，把putList中所有的 Event 放到 Channel 中，成功放完之后就清空putList；

在doCommit提交之后，事务在向 Channel 存放数据的过程中，事务容易出问题。
如 Sink取数据慢，而 Source 放数据速度快，容易造成 Channel 中数据的积压，如果 putList 中的数据放不进去，会如何呢？
此时会调用 doRollback 方法，doRollback方法会进行两项操作：将putList清空；
抛出 ChannelException异常。source会捕捉到doRollback抛出的异常，然后source就将刚才的一批数据重新采集，然后重新开始一个新的事务，这就是事务的回滚。

Flume中的 Take 事务

doTake方法会将channel中的event剪切到takeList中。如果后面接的是HDFS
Sink的话，在把Channel中的event剪切到takeList中的同时也往写入HDFS的IO缓冲流中放一份event(数据写入HDFS是先写入IO缓冲流然后flushHDFS）；

当takeList中存放了batch size 数量的event之后，就会调用doCommit方法，doCommit方法会做两个操作：
	针对HDFS Sink，手动调用IO流的flush方法，将IO流缓冲区的数据写入
	到HDFS磁盘中；
	清空takeList中的数据

flush到HDFS的时候组容易出问题。flush到HDFS的时候，可能由于网络原因超时导致数据传输失败，这个时候调用doRollback方法来进行回滚，回滚的时候由于takeList 中还有备份数据，所以将takeList中的数据原封不动地还给channel，这时候就完成了事务的回滚。


Flume在数据进行采集传输的时候，有可能会造成数据的重复，但不会丢失数据。
Flume在数据传输的过程中是否可靠，还需要考虑具体使用Source、Channel、Sink的类型。
分析Source
	exec Source ，后面接 tail -f ，这个数据也是有可能丢的
	TailDir Source ，这个是不会丢数据的，它可以保证数据不丢失
分析sink
	Hdfs Sink，数据有可能重复，但是不会丢失
最后，分析channel。理论上说：要想数据不丢失的话，还是要用 File
channel；memory channel 在 Flume 挂掉的时候是有可能造成数据的丢失

如果使用 TailDir source 和 HDFS sink，所以数据会重复但是不会丢失

12、数据迁移工具 – Sqoop

导入是指：从关系型数据库向大数据集群（HDFS、HIVE、HBASE）传输数据；使用import关键字；
导出是指：从 大数据集群 向 关系型数据库 传输数据；使用export关键字；

1、导入数据

sqoop import \ 
--connect jdbc:mysql://linux:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--table goodtbl \ 
--target-dir /root/lagou \ 
--delete-target-dir \ 
--num-mappers 1 \ 
--fields-terminated-by "\t"



target-dir：将数据导入 HDFS 的路径；
delete-target-dir：如果目标文件夹在 HDFS 上已经存在，那么再次运行就会报错。可以使用--delete-target-dir来先删除目录。也可以使用 append 参数，表示追加数据；
num-mappers：启动多少个Map Task；默认启动4个Map Task；
也可以写成 -m 1
fields-terminated-by：HDFS文件中数据的分隔符；

2、导入查询数据

sqoop import \ 
--connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--target-dir /root/lagou \ 
--append \ -m 1 \ 
--fields-terminated-by "\t" \ 
--query 'select gname, serialNumber, price, stock_number, create_time from goodtbl where price>88 and $CONDITIONS;'


查询语句的where子句中必须包含 '$CONDITIONS'
如果query后使用的是双引号，则$CONDITIONS前必须加转移符，防止shell识
别为自己的变量

3、导入指定的列

sqoop import \ 
-connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--target-dir /root/lagou \ 
--delete-target-dir \ --num-mappers 1 \ 
--fields-terminated-by "\t" \ 
--columns gname,serialNumber,price \ 
--table goodtbl

备注：columns中如果涉及到多列，用逗号分隔，不能添加空格
4、导入查询数据(使用关键字)

sqoop import \ 
--connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--target-dir /root/lagou \ 
--delete-target-dir \ -m 1 \ 
--fields-terminated-by "\t" \ 
--table goodtbl \ --where "price>=68"

5、启动多个Map Task导入数据

sqoop import \ 
--connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--target-dir /root/lagou \ 
--delete-target-dir \ 
--fields-terminated-by "\t" \ 
--table goodtbl \ 
--split-by gname



使用多个 Map Task 进行数据导入时，sqoop 要对每个Task的数据进行分区
	如果 MySQL 中的表有主键，指定 Map Task 的个数就行
	如果 MySQL 中的表有主键，要使用 split-by 指定分区字段
	如果分区字段是字符类型，使用 sqoop 命令的时候要添加：
	-Dorg.apache.sqoop.splitter.allow_text_splitter=true。即

MySQL 到 Hive

sqoop import \ 
--connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--table goodtbl \ 
--hive-import \ 
--create-hive-table \ 
--fields-terminated-by "\t" \ 
--hive-overwrite \ 
--hive-table mydb.goodtbl \ 
-m 1


hive-import。必须参数，指定导入hive
hive-database。Hive库名(缺省值default)
hive-table。Hive表名
fields-terminated-by。Hive字段分隔符
hive-overwrite。覆盖中已经存在的数据
create-hive-table。创建好 hive 表，但是表可能存在错误。不建议使用这个参数，建议提前建好表

2、导出数据

# 执行导出 
sqoop export \ 
--connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive \ 
--password 12345678 \ 
--table goodtbl2 \ 
--num-mappers 1 \ 
--export-dir /user/hive/warehouse/mydb.db/goodtbl \ 
--input-fields-terminated-by "\t"

增量数据导入

增量导入数据分为两种方式：
基于递增列的增量数据导入（Append方式）
基于时间列的数据增量导入（LastModified方式）

-- 删除 MySQL 表中的全部数据 
runcate table sqoop.goodtbl; 
-- 删除 Hive 表中的全部数据 
truncate table mydb.goodtbl;

sqoop import \ 
--connect jdbc:mysql://linux123:3306/sqoop \ 
--username hive 
--password 12345678 \ 
--table goodtbl \ 
--incremental append \ 
--hive-import \ 
--fields-terminated-by "\t" \ 
--hive-table mydb.goodtbl \ 
--check-column serialNumber \ 
--last-value 50 \ 
-m 1

check-column 用来指定一些列（即可以指定多个列），这些列在增量导入时用
来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时
间戳类似。这些被指定的列的类型不能使任意字符类型，如char、varchar等类
型都不可以
last-value 指定上一次导入中检查列指定字段最大值

3、执行 job

# 创建 sqoop job sqoop job 
--create myjob1 -- import \ 
--connect jdbc:mysql://linux123:3306/sqoop?useSSL=false \ 
--username hive \ 
--password-file /sqoop/pwd/sqoopPWD.pwd \ 
--table goodtbl \ 
--incremental append \ 
--hive-import \ 
--hive-table mydb.goodtbl \ 
--check-column serialNumber \ 
--last-value 0 \ 
-m 1 
# 查看已创建的job 
sqoop job --list 
# 查看job详细运行是参数 
sqoop job --show myjob1
 # 执行job
  sqoop job --exec myjob1

# 删除job
 sqoop job --delete myjob1
 # 执行
 sqoop job -exec myjob1

查看最后记录的值

cat metastore.db.script |grep incremental.last.value