剑指数据仓库-Hive04_hive state 42000 code 40000

一、上次课回顾

二、Hive04

• 2.1、大数据中的各种排序（order by、sort by、cluster by）

三、SQOOP的使用

• 3.1、SQOOP的产生背景&简介
• 3.2、SQOOP的安装部署
• 3.3、SQOOP的简单使用
• 3.4、SQOOPimport的详细使用

一、上次课回顾

• https://blog.csdn.net/SparkOnYarn/article/details/105182082
• join分为内连接、外连接、左外连接、右外连接，全连接；三种复杂的数据类型：Map、Struct、Array，这三种类型的定义，存值和取值；内置函数只需要查看详细定义即可；在Hive中不建议存储json数据，内置函数可以解析出网址（parse_url_tuple）

二、Hive04

2.1、大数据中的排序（order by、sort by、cluster by）

排序问题在数据量少的时候是没有问题的，但是数据量一多，问题就出来了：

1、order by(全局排序)

• 如上要实现全局排序，必然是只能有一个reduce；多个reduce的话，reduce1中有序，reduce2中有序，但是无法保证全局有序。生产上order by是慎用的，数据量大，但是只有一个reduce，

select * from emp order by empno desc;

0: jdbc:hive2://hadoop001:10000/ruozedata_hiv> set hive.mapred.mode=strict;
No rows affected (0.004 seconds)
0: jdbc:hive2://hadoop001:10000/ruozedata_hiv> select * from emp order by empno desc;
Error: Error while compiling statement: FAILED: SemanticException 1:27 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token ‘empno’ (state=42000,code=40000)

打开hive.configure.properties，注意参数：hive.mapred.mode；在严格模式中，一些负载重的查询是不被允许跑的，举例：全表扫描是被禁止的单单使用order by的话，必须加上limit字段：

我们找一张分区表进行操作：
/ruozedata_hiv> select * from order_partition where event_month=‘2020-01’ order by order_no desc;
Error: Error while compiling statement: FAILED: SemanticException 1:67 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token ‘order_no’ (state=42000,code=40000)

0: jdbc:hive2://hadoop001:10000/ruozedata_hiv> set hive.mapred.mode=nonstrict;
No rows affected (0.002 seconds)

0: jdbc:hive2://hadoop001:10000/ruozedata_hiv> select * from order_partition where event_month=‘2020-01’ order by order_no desc;

严格模式下：
如果是普通表：order by + limit
分区表：order by + limit where partition column

• order by 在生产上慎用：

sort by

• 能保证每个分区有序，你有几个reduce，它出来的结果都是有序的，但是不能保证全局有序；sort by 不受严格模式、非严格模式的影响：column列可以是多个，数值类型 --> 数字；字符串类型 --> 字典：
• 字典序就是按照字母abcd的顺序排列：

1、设置reduce数：
set mapred.redcue.tasks=3; 设置3个reduce
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 3

2、select * from emp sort by empno;
结果在控制台上输出是没那么明显的直观的：

3、MR中有多少个reduce作业就对应输出多少个文件、spark中多少个task就对应输出多少个输出文件：

4、把输出文件放到本地liunux目录上再做决断：
insert overwrite local directory '/home/hadoop/tmp/hivetmp/sort/' select * from emp sort by empno;

5、自行去到输出目录中查看确实是有3个输出文件 --> 这就是所谓的分区有序：
[hadoop@hadoop001 sort]$ pwd
/home/hadoop/tmp/hivetmp/sort
[hadoop@hadoop001 sort]$ ll
total 12
-rw-r--r-- 1 hadoop hadoop 335 Apr  3 13:59 000000_0
-rw-r--r-- 1 hadoop hadoop 282 Apr  3 13:59 000001_0
-rw-r--r-- 1 hadoop hadoop  91 Apr  3 13:59 000002_0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• select 语句在官网上的显示：
  https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

为什么严格模式下一定要加limit？默认的排序机制asc升序，lexicographical（字典序），

Distribute by：

使用方法：distribute by + col：根据指定的字段把数据分发到不同的reduce上去；相当于是MapReduce中的Partitioner，通常与Sort by连用；

• Hive uses the columns in Distribute By to distribute the rows among reducers. All rows with the same Distribute By columns will go to the same reducer. However, Distribute By does not guarantee clustering or sorting properties on the distributed keys.

1、按照名字的长度进行分发，又在分区内根据员工编号的升序排序：
insert overwrite local directory ‘/home/hadoop/tmp/hivetmp/distribute’ select * from emp distribute by length(ename) sort by empno;

//在大数据中数据倾斜的场景需要依赖于distribute的，

Cluster by：

• cluster by is a short-cut for both Distribute by and sort by：只做数据分发：

• insert overwrite local directory ‘/home/hadoop/tmp/hivetmp/distribute’ select * from emp cluster by empno;

总结：

order by：全局有序，一个reduce 大数据量下效率低
sort by: 每个reduce内有序，不能保证全局有序
distribute by：是按照指定字段进行数据分发
常与sort by连用，确保每个reduce内有序
cluster by = distribute by + cluster by

思考：生产上要求业务统计结果有序，order by无法使用，sort by又无法保证全局有序，应该如何解决？

三、Sqoop的使用

3.1、Sqoop的产生背景、简介

场景：
1）、数据存储在MySQL中，你想使用Hive进行处理
2）、使用Hive统计分析好了，数据还在Hive中，如何导出到MySQL中：
–> 最终是通过报表可视化展示的，结果如何对接到报表中：（1）、HiveServer2 （2）、Hive的统计结果导出到RDBMS中，报表数据直接对接RDBMS中，必备的两大场景

解决方案：MapReduce（太过繁杂）
–> 抽象成常用的工具

Sqoop是一个数据导入导出工具：
类似Hive访问MySQL在$HIVE_HOME/cong下的hive-site.xml中的信息一样：
RDBMS需要如下链接信息：url driver db table user password（必要信息）
链接到hdfs需要：path
链接到Hive需要：database table partition

–> 引出了Sqoop这个框架：sqoop.apache,org

sqoop：sql to hadoop
hue：一个可视化的框架，在上面写SQL，结果可以采用报表的方式出来：在CDH一配置结果就都出来了。

生产集群中看公司，先通过跳板机在再链接到服务器上去，Dbeaver能否链接到生产服务器，可以

Sqoop简介：

• Apache Sqoop™ is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.
• sqoop是一个工具用来高效传输数据的工具，是在hadoop和结构化数据库之间，比如RDBMS和Hadoop进行相互操作的一个工具。

RDBMS <==>Hadoop，同比的也可以把数据导到Hbase、Hive中去。

两大版本：
sqoop1.x：sqoop1 ***（70%左右公司都是使用这个的）
sqoop1.99.x：sqoop2（用起来很麻烦）

• 我们使用的是sqoop1，两个框架是风马牛不相及的。

职责：

把数据从RDBMS和Hadoop之间记性导入导出操作，底层就是使用MapReduce来实现的。
Sqoop只有Map，它不需要Reduce；
1
2

Sqoop的导入：基于Hadoop作为参考点、基准点：
RDBMS <==>Hadoop

Sqoop的导出：从Hadoop作为出发点往外导：
Hadoop <==> RDBMS

3.2、Sqoop的安装部署

1、下载：
wget http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.16.2.tar.gz

2、解压并且做一个软连接：
tar -zxvf sqoop-1.4.6-cdh5.16.2.tar.gz -C ~/app/
ln -s sqoop-1.4.6-cdh5.16.2 sqoop

3、配置系统环境变量：vi ~/.bashrc
export SQOOP_HOME=/home/hadoop/app/sqoop
export PATH=$SQOO{P}_{H}OME/bin:$PATH

4、生效环境变量：
source ~/.bashrc

5、在$SQOOP_HOME/conf下拷贝一份文件，再进行参数配置：
cp sqoop-env-template.sh sqoop-env.sh
export HADOOP_COMMON_HOME=/home/hadoop/app/hadoop
export HADOOP_MAPRED_HOME=/home/hadoop/app/hadoop
export HIVE_HOME=/home/hadoop/app/hive

6、拷贝一个驱动包到$SQOOP_HOME/lib目录下：
cp mysql-connector-java-5.1.27-bin.jar $SQOOP_HOME/lib/

3.3、Sqoop的简单使用

sqoop help查看命令帮助，学习sqoop就是查字典：

1、sqoop version：
查看版本号

2、列出mysql下的数据库，查看命令帮助：sqoop help list_databases

sqoop list-databases \
--connect jdbc:mysql://hadoop001:3306 \
--username root \
--password 960210

输出结果如下：
20/04/03 15:08:06 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
mysql
performance_schema
ruozedata_hive
sqoop
sys
wordpress
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3、列出数据库下的表：

sqoop list-tables \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 960210

//运行结果如下：
20/04/03 15:39:54 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
dept
emp
salgrade
1
2
3
4
5
6
7
8
9
10

3.4、Sqoop import的详细使用（从MySQL–>hdfs）

sqoop help import：查看命令帮助：

参数先捋一遍：
--append
--columns <col,col,col...> 
--delete-target-dir     
-m,--num-mappers <n> 
--mapreduce-job-name <name>
--target-dir <dir>
1
2
3
4
5
6
7

需求一：把sqoop中的emp数据表导到HDFS上去：

sqoop import \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 960210 \
--table emp \
-m 1

//出现如下报错：
Exception in thread "main" java.lang.NoClassDefFoundError: org/json/JSONObject
        at org.apache.sqoop.util.SqoopJsonUtil.getJsonStringforMap(SqoopJsonUtil.java:43)
        at org.apache.sqoop.SqoopOptions.writeProperties(SqoopOptions.java:784)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.lang.ClassNotFoundException: org.json.JSONObject
        at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:424)

//原因是缺少java-json.jar包，上传一个即可解决问题：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 这个sqoop语句是会跑mapreduce任务的，App Name叫emp.jar（没有指定名字默认的名字就是emp.jar），输出结果默认是在/user/hadoop/emp下

1、如下所示，顺利的把MySQL中的数据导入到了hdfs目录上：
[hadoop@hadoop001 bin]$ hdfs dfs -ls /user/hadoop/emp
20/04/03 16:10:18 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 2 items
-rw-r--r--   1 hadoop supergroup          0 2020-04-03 16:08 /user/hadoop/emp/_SUCCESS
-rw-r--r--   1 hadoop supergroup        282 2020-04-03 16:08 /user/hadoop/emp/part-m-00000

2、使用了一个map所以结果是一个文件
[hadoop@hadoop001 bin]$ hdfs dfs -text /user/hadoop/emp/part-m-00000
20/04/03 16:10:31 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
7369,SMITH,CLERK,7902,1980-12-17,800.0,20.0,40
7499,SMITH,CLERK,7902,1980-12-17,800.0,20.0,40
7499,SMITH,CLERK,7902,1980-12-17,800.0,20.0,40
7499,SMITH,CLERK,7902,1980-12-17,800.0,20.0,40
7499,SMITH,CLERK,7902,1980-12-17,800.0,20.0,40
7499,SMITH,CLERK,7902,1980-12-17,800.0,20.0,40
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

测试：

ERROR tool：FileAlreadyExist，文件目录已存在；意思就是在导入命令中需要删除目标表：

1、在$SQOOP_HOME/bin目录下执行命令的时候打印在控制台上的log信息，为什么会有一个limit1，主要是查看这张表是否存在：
20/04/03 16:21:09 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM emp AS t LIMIT 1

2、为什么number of splits:1
设置的map就是1

3、在$SQOOP_HOME/bin目录下每执行一个sqoop导入就会生成一个.java文件：

引出的就是：每一次执行导入命令前删除目标表：

• 此时我们增加–delete-target-dir这个参数，通过–mapreduce-job-name参数指定作业运行的名字frommysql2hdfs，然后把指定map数恢复成默认值也就是4；

sqoop import \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 960210 \
--table emp \
--mapreduce-job-name frommysql2hdfs \
--delete-target-dir 
1
2
3
4
5
6
7

• 我们把map的数量为1删掉，执行如上出现报错：原因说的很清楚了，emp表中有主键的话就不会报错，默认值就是4个map，emp表中没有主键的话就需要指定map数：
• 20/04/03 16:32:18 ERROR tool.ImportTool: Import failed: No primary key could be found for table emp. Please specify one with --split-by or perform a sequential import with ‘-m 1’

需求二：在往hdfs中导数据的时候自己指定列，顺便更换目录（target-dir）

sqoop import \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 960210 \
--table emp \
--mapreduce-job-name frommysql2hdfs2 \
--delete-target-dir \
--target-dir emp_column \
--columns "empno,ename,job,sal,comm" \
-m 1
1
2
3
4
5
6
7
8
9
10

需求三：导出的数据分割符是，我们要修改成’\t’，把null值替换为0；

sqoop import \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 960210 \
--table emp \
--mapreduce-job-name frommysql2hdfs2 \
--delete-target-dir \
--target-dir emp_column \
--columns "empno,ename,job,sal,comm" \
--fields-terminated-by '\t' \
-- null-string '' \
--null-non-string '0' \
-m 1

//--fields-terminated-by '\t'把导出数据的分割符换成\t，-- null-string '' \
--null-non-string '0' \ -->意思是把空的字符串值替换为0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

常见的面试题：

1、Sort by和order by的区别？

• sort by是每个分区有序，order by是保证了全局有序：

2、hadoop fs -ls和hadoop fs -ls /的区别？
不加斜杠意味着的是：/user/hadoop -->/用户/当前用户名
加斜杠意味着的是hdfs的根目录