利用Sqoop对MySQL执行DML操作

业务背景

利用Sqoop对MySQL进行查询、添加、删除等操作。

业务实现

select操作：

sqoop eval \
--connect jdbc:mysql://127.0.0.1:3306/market \
--username admin \
--password 123456 \
--query "select end_user_id, category_id, score, last_bought_date, days_left, update_time 
from market.PERIOD_RECOMMEND limit 10"

执行结果如下：

[pms@yhd-jqHadoop39 /home/pms/workspace/ouyangyewei/data]
$sqoop eval \
> --connect jdbc:mysql://10.0.2.54:3307/market \
> --username marketuser \
> --password 123456 \
> --query "select end_user_id, category_id, score, last_bought_date, days_left, update_time 
> from market.PERIOD_RECOMMEND limit 10" 
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
15/06/09 09:36:53 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4-cdh5.0.2
15/06/09 09:36:53 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/06/09 09:36:53 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
--------------------------------------------------------------------------------------------------------------
| END_USER_ID          | CATEGORY_ID          | SCORE                | LAST_BOUGHT_DATE | DAYS_LEFT | UPDATE_TIME         | 
--------------------------------------------------------------------------------------------------------------
| 1020                 | 6639                 | 0.03765381553445836  | 2015-02-26 | 42 | 2015-06-01 10:57:09.0 | 
| 1180                 | 6008                 | 0.49232472007320505  | 2015-05-16 | 5  | 2015-06-01 11:00:30.0 | 
| 1180                 | 6638                 | 0.12863038975524008  | 2015-05-16 | 10 | 2015-06-04 11:04:18.0 | 
| 1180                 | 6641                 | 0.016126884197067093 | 2015-05-16 | 17 | 2015-06-08 11:06:54.0 | 
| 1180                 | 972108               | 0.9059551911095096   | 2015-04-12 | 4  | 2015-06-01 11:00:30.0 | 
| 1580                 | 959307               | 0.015703642709355377 | 2015-04-26 | 29 | 2015-06-07 11:20:13.0 | 
| 1620                 | 5059                 | 0.03979611383975357  | 2015-03-11 | 39 | 2015-06-02 11:24:50.0 | 
| 1680                 | 959307               | 0.21385565120306954  | 2015-05-07 | 11 | 2015-06-04 11:28:21.0 | 
| 2030                 | 6638                 | 0.03160084296270789  | 2015-03-09 | 41 | 2015-06-03 11:35:04.0 | 
| 2110                 | 6641                 | 0.9888130446112331   | 2015-03-14 | -6 | 2015-06-05 11:41:16.0 | 
--------------------------------------------------------------------------------------------------------------

删除操作：

sqoop eval \
--connect jdbc:mysql://127.0.0.1:3306/market \
--username admin \
--password 123456 \
--query "delete from PERIOD_RECOMMEND where update_time < '2015-06-01'"

执行结果：

[pms@yhd-jqhadoop39 /home/pms/workspace/ouyangyewei/data]
$sqoop eval \
> --connect jdbc:mysql://10.0.2.54:3307/market \
> --username marketuser \
> --password 123456 \
> --query "delete from PERIOD_RECOMMEND where update_time < '2015-06-05'" 
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
15/06/09 09:47:18 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4-cdh5.0.2
15/06/09 09:47:18 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/06/09 09:47:18 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
15/06/09 09:47:31 INFO tool.EvalSqlTool: 556430 row(s) updated.

利用sqoop eval，只需要在参数--query中指定sql语句即可对mysql执行DML操作。