[Sqoop]利用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./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| —————————————————————————————————————————————————————————————————————————-

删除操作：

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.-cdh5/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操作。

，不愧是春城，花香四季，品种繁多。