下面是我在Cloudera快速启动VM中尝试的查询
sqoop import --username training --password training --connect jdbc:mysql:// localhost / loudacre --target -dir / sample --split-by accounts.acct_num --query'select accounts.first_name FROM accounts JOIN accountdevice ON(accounts.acct_num = accountdevice.account_id)WHERE $ CONDITIONS'kquote
下面是查询中使用的两个表的表结构
mysql> describe accounts;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| acct_num | int(11) | NO | PRI | NULL | |
| acct_create_dt | datetime | NO | | NULL | |
| acct_close_dt | datetime | YES | | NULL | |
| first_name | varchar(255) | NO | | NULL | |
| last_name | varchar(255) | NO | | NULL | |
| address | varchar(255) | NO | | NULL | |
| city | varchar(255) | NO | | NULL | |
| state | varchar(255) | NO | | NULL | |
| zipcode | varchar(255) | NO | | NULL | |
| phone_number | varchar(255) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| modified | datetime | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
mysql> describe accountdevice;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| account_id | int(11) | NO | MUL | NULL | |
| device_id | int(11) | NO | MUL | NULL | |
| activation_date | datetime | NO | | NULL | |
| account_device_id | varchar(255) | NO | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
我得到以下例外
SQLSyntaxErrorException: Unknown column 't1.acct_num' in 'field list'
16/10/15 13:48:12 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.acct_num' in 'field list'
有人可以帮忙吗?
1 回答
这是预期的行为 .
首先,sqoop将根据您的查询从RDBMS获取元数据(列详细信息) .
使用查询:
你看
$CONDITIINS
被 1 = 0 取代以获取元数据 .现在,您的查询将仅返回1列
first_name
,并且您将在acct_num
上拆分,而不是从RDBMS表中查询 . 这就是为什么你得到 Unknown column 错误 .因此,请确保在SQL查询中也按列拆分SELECT .