sqoop使用中的小问题

　　1、数据库连接异常

执行数据导出

sqoop export --connect jdbc:mysql://192.168.208.129:3306/test   --username hive --P --table tvdata --export-dir /user/hive/warehouse/tvdata --input-fields-terminated-by '@' --input-null-string '\N' --input-null-non-string '\N'

ip地址必须是真实IP ，不能是localhost，127.0.0.1  

2、

同步的为mysql结构化数据，对数据格式有固定的要求。所以说当集群文件中数据格式与mysql表中存在冲突时，会导致数据传输异常。

问题解决：1.查看hdfs中文件数据是否与mysql表中对应的表字段数据类型一致（hdfs中string对应mysql中varchar或者char类型等等）

                   2.查看sqoop命令中分隔符是否与hdfs相应文件中分隔符一致

3、导出中文数据乱码问题

先查看mysql的编码

mysql> show variables like 'collation_%';  
+----------------------+-------------------+  
| Variable_name | Value |  
+----------------------+-------------------+  
| collation_connection | latin1_swedish_ci |  
| collation_database | latin1_swedish_ci |  
| collation_server | latin1_swedish_ci |  
+----------------------+-------------------+  
3 rows in set (0.00 sec)  
mysql> show variables like 'character_set_%';  
+--------------------------+----------------------------+  
  
| Variable_name | Value |  
+--------------------------+----------------------------+  
| character_set_client | latin1 |  
| character_set_connection | latin1 |  
| character_set_database | latin1 |  
| character_set_filesystem | binary |  
| character_set_results | latin1 |  
| character_set_server | latin1 |  
| character_set_system | utf8 |  
| character_sets_dir | /usr/share/mysql/charsets/ |  
+--------------------------+----------------------------+  
8 rows in set (0.00 sec)  

更改编码

[root@Hadoop48 ~]# vi /etc/my.cnf  
[mysql]  
default-character-set=utf8  
[client]  
default-character-set=utf8  
[mysqld]  
default-character-set=utf8  
character_set_server=utf8  
init_connect='SET NAMES utf8'  

重启

[root@Hadoop48 ~]# service mysqld restart

在查看编码，已经修改成功

mysql> show variables like "char%";  
+--------------------------+----------------------------+  
| Variable_name | Value |  
+--------------------------+----------------------------+  
| character_set_client | utf8 |  
| character_set_connection | utf8 |  
| character_set_database | utf8 |  
| character_set_filesystem | binary |  
| character_set_results | utf8 |  
| character_set_server | utf8 |  
| character_set_system | utf8 |  
| character_sets_dir | /usr/share/mysql/charsets/ |  
+--------------------------+----------------------------+  
8 rows in set (0.00 sec)  
  
mysql> show variables like "colla%";  
+----------------------+-----------------+  
| Variable_name | Value |  
+----------------------+-----------------+  
| collation_connection | utf8_general_ci |  
| collation_database | utf8_general_ci |  
| collation_server | utf8_general_ci |  
+----------------------+-----------------+  

在进行数据导出，乱码问题解决

从hive导出到mysql中

sqoop export --connect "jdbc:mysql://192.168.208.129:3306/test?useUnicode=true&characterEncoding=utf-8"   --username hive --P --table tvdata --export-dir /user/hive/warehouse/tvdata --input-fields-terminated-by '@' --input-null-string '\N' --input-null-non-string '\N'