Sqoop1:export data to mysql

最新推荐文章于 2023-04-07 16:52:41 发布

原创最新推荐文章于 2023-04-07 16:52:41 发布 · 371 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #shell

Sqoop 专栏收录该内容

10 篇文章

订阅专栏

本文针对使用SQOOP将数据从HDFS导出到MySQL时遇到的典型问题进行了解析，并提供了详细的解决方案。包括处理更新模式、直接导出、数据截断等问题。

When I export data to mysql using the following sqoop commands

--options-file  sqoop.export.opt --export-dir inok/test/friendrecomend2 
--table friend_rec --staging-table friend_rec_stage --clear-staging-table 
--update-key id --update-mode  allowinsert

the content of sqoop.export.opt likes

export
--verbose
--connect
jdbc:mysql://192.168.122.1:3306/inokdatamine
--username
zhaohj
--password
123456
--direct
-m
1

The error comes:

Staging table cannot be used when export is running in update mode.

Solution: delete --update-xx parameters

Error during export: The active connection manager 
(org.apache.sqoop.manager.DirectMySQLManager) does not support staging of data for export.

Solution: delete --staging-xxxx parameters

Note

Support for staging data prior to pushing it into the destination table is not available for --direct exports. It is also not available when export is invoked using the --update-key option for updating existing data, and when stored procedures are used to insert the data.

Cannot run program "mysqlimport": error=2, No such file or directory
	at java.lang.ProcessBuilder.start(ProcessBuilder.java:1041)
	at java.lang.Runtime.exec(Runtime.java:617)

Solution: Some suggest to install mysql-client in all nodes not only in some of ones. But When I add

--driver com.mysql.jdbc.Driver , then the error disappers.

Note

When using export in direct mode with MySQL, the MySQL bulk utility mysqlimport must be available in the shell path of the task process.

Caused by: java.lang.NumberFormatException: For input string: "1	156 (12: [69"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
	at java.lang.Integer.parseInt(Integer.java:492)

I run a mapreduce job to produce the friendrecomend2 data which using org.apache.hadoop.mapreduce.lib.output.TextOutputFormat; format. The content likes

2	4 (1: [5])
3	5 (2: [1, 4])
4	2 (1: [5]),6 (1: [5])
5	3 (2: [1, 4]),1 (1: [4])
6	2 (1: [5]),4 (1: [5]),5 (1: [2])

I thought the error due to sqoop parse the line use default delimiters(comma-separated fields with newline-separated records).

Solution: add parameter --input-fields-terminated-by \t (Note: don't put sqoop commands is command input area

when there is space in parameter value. Instead, Using Params)

java.io.IOException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: 
Data too long for column 'friends' at row 1
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:220)

the friend_rec table in mysql is :

Solution: change the friends filed length to 30000. This solve the sample data. But in my situation, the second filed in the files in hdfs may be longer more than 30000. So,I need to design the file output format

or schema to avoid this error.

ERROR org.apache.sqoop.tool.ExportTool  - Error during export: 
Mixed update/insert is not supported against the target database yet

where the sqoop export action likes

<sqoop xmlns="uri:oozie:sqoop-action:0.2">
  <job-tracker>192.168.122.1:2015</job-tracker>
  <name-node>hdfs://192.168.122.1:2014</name-node>
  <arg>--options-file</arg>
  <arg>sqoop.export.opt</arg>
  <arg>--export-dir</arg>
  <arg>/user/zhaohj/inok/friendrec2/data/friendrecomend</arg>
  <arg>--table</arg>
  <arg>friend_rec</arg>
  <arg>--driver</arg>
  <arg>com.mysql.jdbc.Driver</arg>
  <arg>--input-fields-terminated-by</arg>
  <arg>\t</arg>
  <arg>--update-key</arg>
  <arg>id</arg>
  <arg>--update-mode</arg>
  <arg>allowinsert</arg>
  <file>/user/zhaohj/inok/friendrec/sqoop.export.opt#sqoop.export.opt</file>
</sqoop>

google results show that it is a bug in sqoop1.4.2 but my sqoop is 1.4.4

When I delete the --driver com.mysql.jdbc.Driver. The error No.6 disappers. But new one comes:

 Error during export: MySQL direct connector does not support upsert mode.
 Please use JDBC based connector (remove --direct parameter)

Solution: remove --direct

The root reason is that mysql connector does't support upsert mode.

http://mail-archives.apache.org/mod_mbox/sqoop-user/201210.mbox/%3C20121004152956.GT16616@jarcec-thinkpad%3E

Note:Sqoop automatically generates code to parse and interpret records of the files containing the data to be exported back to the database. If these files were created with non-default delimiters (comma-separated fields with newline-separated records), you should specify the same delimiters again so that Sqoop can parse your files.

References

https://groups.google.com/a/cloudera.org/forum/#!topic/cdh-user/K44cK_ZI_2w