bob007 发表于 2015-6-9 21:01:50

利用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"
执行结果如下:

$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'"

执行结果:


$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操作。



xuliang123789 发表于 2016-4-5 18:32:49

谢谢楼主,学习了,赞~~
页: [1]
查看完整版本: 利用sqoop对mysql执行DML操作