sqoop导入mysql数据到hive中

问题导读：
1.sqoop 在同步mysql表结构到hive找不到包目录的可能原因是什么？
2.如何查看sqoop同步mysql数据到hive过程？
3.错误就是hive的jar包有缺失，应该有哪些包？






一、sqoop 在同步mysql表结构到hive
  sqoop create-hive-table --connect jdbc:mysql://ip:3306/sampledata --table t1--username dev --password 1234 --hive-table t1;

  

执行到这一步就退出了，但是在hadoop的hdfs上的/hive/warehouse/的目录下是找不到t1表的目录,
但是正常执行完成是下面这样的：





错误就是hive的jar包有缺失
全部的jar包该是这样的：












这是hadoop-2.2.0和hbase-0.96.2和hive-0.13.1整合的全部jar包
还有就是sqoop-1.4.4.jar 和mysql的驱动包也要放进去，这两个包最好也放在在hadoop/etc/share/common/




hive启动日志检查
hive -hiveconf hive.root.logger=DEBUG,console

控制台查看
或者把hive和hadoop的启动日志优先级调成DEBUG，
输出到一个文件中查看
如果没有异常信息，那就说明sqoop执行是没有问题的，至此问题解决！

楼主，我的调试信息中出现这样的异常，请问是什么原因：
InvalidObjectException(message:Role admin already exists.)
        at org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3020)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
        at com.sun.proxy.$Proxy9.addRole(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles(HiveMetaStore.java:544)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:398)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:356)


InvalidObjectException(message:Role public already exists.)
        at org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3020)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
        at com.sun.proxy.$Proxy9.addRole(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles(HiveMetaStore.java:553)

InvalidObjectException(message:All is already granted by admin)
        at org.apache.hadoop.hive.metastore.ObjectStore.grantPrivileges(ObjectStore.java:3713)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
        at com.sun.proxy.$Proxy9.grantPrivileges(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles(HiveMetaStore.java:567)

quenlang 发表于 2014-9-28 12:02
楼主，我的调试信息中出现这样的异常，请问是什么原因：
InvalidObjectException(message:Role admin alre ...

应该是元数据库mysql出问题了。可以手工尝试创建hive表

请问解决了吗，我也是同样的问题，<configuration>
    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://10.6.0.127:3306/hive?createDatabaseIfNotExist=true</value>
      <description>JDBC connect string for a JDBC metastore</description>
    </property>

    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
      <description>Driver class name for a JDBC metastore</description>
    </property>

    <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hive</value>
      <description>username to use against metastore database</description>
    </property>

    <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>hive</value>
      <description>password to use against metastore database</description>
    </property>
</configuration>
然后我本地使用命令可以直接进入mysql。mysql -h10.6.0.127 -phive -uhive hive  那么这样的话我就是有权限了问题还是这样的debug出现InvalidObjectException(message:Role admin already exists.)
        at org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3020)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

小小布衣 发表于 2015-2-13 15:47
请问解决了吗，我也是同样的问题，
   
      javax.jdo.option.ConnectionURL

如果本地安装，建议安装下面配置

<configuration>  
<property>  
  <name>hive.metastore.warehouse.dir</name>  
  <value>/user/hive_remote/warehouse</value>  
</property>  

<property>  
  <name>hive.metastore.local</name>  
  <value>true</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:mysql://localhost/hive_remote?createDatabaseIfNotExist=true</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionDriverName</name>  
  <value>com.mysql.jdbc.Driver</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionUserName</name>  
  <value>root</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionPassword</name>  
  <value>123</value>  
</property>  
</configuration>  
复制代码

更多参考：hbase0.96与hive0.12整合高可靠文档及问题总结

InvalidObjectException(message:All is already granted by admin)
我也有这些debug信息。貌似没什么影响。

O(∩_∩)O谢谢~\(≧▽≦)/~啦啦啦

楼主我出现问题传不到HDFS

[hadoop@master sqoop-1.4.5]$ bin/sqoop import --connect jdbc:mysql://localhost:3306/sqoop --username root --password root --table tb1 -m 1
15/07/25 20:38:46 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
15/07/25 20:38:46 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/07/25 20:38:47 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
15/07/25 20:38:47 INFO tool.CodeGenTool: Beginning code generation
15/07/25 20:38:47 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `tb1` AS t LIMIT 1
15/07/25 20:38:47 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `tb1` AS t LIMIT 1
15/07/25 20:38:47 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/hadoop-2.2.0
注: /tmp/sqoop-hadoop/compile/91a7d34dd0de631b54bb8f45da12766c/tb1.java使用或覆盖了已过时的 API。
注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。
15/07/25 20:38:50 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/91a7d34dd0de631b54bb8f45da12766c/tb1.jar
15/07/25 20:38:50 WARN manager.MySQLManager: It looks like you are importing from mysql.
15/07/25 20:38:50 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
15/07/25 20:38:50 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
15/07/25 20:38:50 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
15/07/25 20:38:50 INFO mapreduce.ImportJobBase: Beginning import of tb1
15/07/25 20:38:51 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
15/07/25 20:38:51 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
15/07/25 20:38:52 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
15/07/25 20:38:52 INFO client.RMProxy: Connecting to ResourceManager at master/192.168.1.20:8032
15/07/25 20:38:58 INFO db.DBInputFormat: Using read commited transaction isolation
15/07/25 20:38:58 INFO mapreduce.JobSubmitter: number of splits:1
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.job.name is deprecated. Instead, use mapreduce.job.name
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.cache.files.timestamps is deprecated. Instead, use mapreduce.job.cache.files.timestamps
15/07/25 20:38:58 INFO Configuration.deprecation: mapreduce.map.class is deprecated. Instead, use mapreduce.job.map.class
15/07/25 20:38:58 INFO Configuration.deprecation: mapreduce.inputformat.class is deprecated. Instead, use mapreduce.job.inputformat.class
15/07/25 20:38:58 INFO Configuration.deprecation: mapreduce.outputformat.class is deprecated. Instead, use mapreduce.job.outputformat.class
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.output.value.class is deprecated. Instead, use mapreduce.job.output.value.class
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.output.dir is deprecated. Instead, use mapreduce.output.fileoutputformat.outputdir
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.cache.files is deprecated. Instead, use mapreduce.job.cache.files
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.working.dir is deprecated. Instead, use mapreduce.job.working.dir
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.job.classpath.files is deprecated. Instead, use mapreduce.job.classpath.files
15/07/25 20:38:58 INFO Configuration.deprecation: user.name is deprecated. Instead, use mapreduce.job.user.name
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.cache.files.filesizes is deprecated. Instead, use mapreduce.job.cache.files.filesizes
15/07/25 20:38:58 INFO Configuration.deprecation: mapred.output.key.class is deprecated. Instead, use mapreduce.job.output.key.class
15/07/25 20:38:59 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1437826754583_0003
15/07/25 20:39:00 INFO impl.YarnClientImpl: Submitted application application_1437826754583_0003 to ResourceManager at master/192.168.1.20:8032
15/07/25 20:39:00 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1437826754583_0003/
15/07/25 20:39:00 INFO mapreduce.Job: Running job: job_1437826754583_0003
15/07/25 20:39:24 INFO mapreduce.Job: Job job_1437826754583_0003 running in uber mode : false
15/07/25 20:39:24 INFO mapreduce.Job:  map 0% reduce 0%
15/07/25 20:39:42 INFO mapreduce.Job:  map 100% reduce 0%
15/07/25 20:39:42 INFO mapreduce.Job: Task Id : attempt_1437826754583_0003_m_000000_0, Status : FAILED
Error: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:167)
        at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73)
        at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:725)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:339)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:162)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1491)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:157)
Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:220)
        at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:165)
        ... 9 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:356)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2504)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2541)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2323)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:302)
        at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:213)
        ... 10 more
Caused by: java.net.ConnectException: 拒绝连接
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:589)
        at java.net.Socket.connect(Socket.java:538)
        at java.net.Socket.<init>(Socket.java:434)
        at java.net.Socket.<init>(Socket.java:244)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:258)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:306)
        ... 26 more

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143

楼主，做sqoop mysql到hdfs没有问题，但是导入hive报错，这是为什么，
报错：
mapred.jar is deprecated instead use mapreduce.job.jar classnotfound org/kitesdk/data/mapreduce/DatasetKeyOutputFormat