Sqoop导出插入重复条目 [英] Sqoop export inserting duplicate entries
问题描述
我试图了解sqoop导出是如何工作的。我在mysql中有一个表格站点,它包含两列id和url,并包含两行
1,www.yahoo.com
2,www.gmail.com
表没有主键
当我通过执行下面的命令将条目从HDFS导出到mysql站点表时,它插入重复条目
我在HDFS中有以下项目
1,www.one.com
2 ,www.2.com
3,www.3.com
4,www.4.com
sqoop export --table site --connect jdbc:mysql:// localhost / loudacre - 用户名培训 - 密码培训--export-dir / site / --update -mode allowinsert --update-key id
因此,不是更新已经存在的id,而是再次插入重复的id(意思是两个1,1 www.one.com和1 for www.yahoo.com)即使我删除了--update键,结果也是一样的。
它的发生是因为表没有主键
我在Cloudera quickstart VM中使用sqoop 1.4.5
任何帮助?
根据Sqoop docs ,
$ b
MySQL会尝试插入新的如果插入失败并带有重复唯一键错误,它将更新相应的行。
因此,要么 --update-key
列应该是主键或者唯一索引。
在内部,sqoop会创建这样的查询
INSERT INTO表(ID,电子邮件)VALUES(1,www.one.com)ON DUPLICATE KEY UPDATE email = www.one.com
等所有其他值。
I am trying to understand how sqoop export works.I have a table site in mysql which contains two columns id and url and contains two rows
1,www.yahoo.com
2,www.gmail.com
The table has no primary key
When i am exporting the entries from HDFS to mysql site table by executing below command its inserting duplicate entries
I have below entries in HDFS
1,www.one.com
2,www.2.com
3,www.3.com
4,www.4.com
sqoop export --table site --connect jdbc:mysql://localhost/loudacre -- username training --password training --export-dir /site/ --update-mode allowinsert --update-key id
So instead of updating already existing id its inserting duplicate id again (meaning two 1 , 1 for www.one.com and 1 for www.yahoo.com)
even if I remove the --update-key the outcome is same.Does its happening because the table doesn't have primary key
I am using sqoop 1.4.5 in Cloudera quickstart VM
Any help ?
As per Sqoop docs,
MySQL will try to insert new row and if the insertion fails with duplicate unique key error it will update appropriate row instead.
So, either --update-key
column should be primary key or have unique index on it.
Internally, sqoop will create query like this
INSERT INTO table (id,email) VALUES (1,www.one.com) ON DUPLICATE KEY UPDATE email=www.one.com
and so on for all other values.
这篇关于Sqoop导出插入重复条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!