Sqoop -边界查询- getLong()的值无效- 'Fitness‘

Question

我的朋友们

我正在尝试使用Sqoop使用过滤器来仅提取数据。为此，我使用了Bondary查询。我只想过滤3到6之间的departments_id。我有以下命令：

[cloudera@quickstart ~]$ sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username retail_dba --password cloudera --table departments --target-dir=wareouse/departments_v1 --boundary-query "SELECT department_id, department_name FROM departments WHERE department_id BETWEEN 3 AND 6"

但是我得到了以下错误：

18/12/05 12:48:27 ERROR tool.ImportTool: Import failed: java.io.IOException: java.sql.SQLException: Invalid value for getLong() - 'Fitness'

你知道我的命令出了什么问题吗？

源数据如下：

  department_id | department_name |
+---------------+-----------------+
|             2 | Fitness         |
|             3 | Footwear        |
|             4 | Apparel         |
|             5 | Golf            |
|             6 | Outdoors        |
|             7 | Fan Shop        |
+---------------+-----------

谢谢!

Answer 1

边界查询需要稍微修改一下。默认情况下，Sqoop将使用以下查询来查找用于创建拆分的边界：

SELECT MIN(department_id), MAX(department_id) FROM departments

要导入数据的子集，您可以使用此边界查询来提供下限和上限：

SELECT 3,6 FROM departments

下图提供了更多详细信息：

1)创建表并填充数据

mysql> create database retail_db;
mysql> use retail_db;
mysql> create table departments (department_id int primary key, department_name varchar(255));
mysql> insert into departments values(2, 'Fitness');
mysql> insert into departments values(3, 'Footwear');
mysql> insert into departments values(4, 'Apparel');
mysql> insert into departments values(5, 'Golf');
mysql> insert into departments values(6, 'Outdoors');
mysql> insert into departments values(7, 'Fan Shop');

2)检查data

mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             2 | Fitness         |
|             3 | Footwear        |
|             4 | Apparel         |
|             5 | Golf            |
|             6 | Outdoors        |
|             7 | Fan Shop        |
+---------------+-----------------+
6 rows in set (0.00 sec)

3)运行Sqoop作业

$ sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username user --password password --table departments --target-dir /test/run --boundary-query 'SELECT 3,6 FROM departments'

4)检查结果

$ hadoop fs -cat /test/run/part-*
3,Footwear
4,Apparel
5,Golf
6,Outdoors