i want import data oracle database hive using sqoop. want sqoop create table destination hive database.
i put oracle jdbc (ojdbc6.jar) sqoop lib directory.
i try 2 methods none of them work.
sqoop import \ --connect jdbc:oracle:thin:@${db_host}:${db_port}:${db_name} \ --username ${db_user} \ --password ${db_pwd} \ --table ${input_table} \ --hcatalog-home /usr/hdp/current/hive-webhcat \ --hcatalog-database ${output_db} \ --hcatalog-table ${output_table} \ --create-hcatalog-table \ --num-mappers 1 sqoop import \ --connect jdbc:oracle:thin:@${db_host}:${db_port}:${db_name} \ --username ${db_user} \ --password ${db_pwd} \ --hive-home /usr/hdp/current/hive \ --hive-import \ --create-hive-table \ --hive-table "${output_db}.${output_table}" \ --table ${input_table} i have error message:
error tool.importtool: imported failed: there no column found in target table input_table. please ensure table name correct.
it seems sqoop doesn't take account --create-hcatalog-table or --create-hive-table
however, when import data postgresql sqoop, table creation works well. ideas? thanks
for information, sqoop read oracle table. ran command , got result:
sqoop eval \ --connect jdbc:oracle:thin:@${db_host}:${db_port}:${db_name} \ --username ${db_user} \ --password ${db_pwd} \ --query "select count(1) input_table" the full logs of error:
16/07/21 18:08:29 info sqoop.sqoop: running sqoop version: 1.4.6.2.4.0.0-169 16/07/21 18:08:29 debug tool.basesqooptool: enabled debug logging. 16/07/21 18:08:29 warn tool.basesqooptool: setting password on command-line insecure. consider using -p instead. 16/07/21 18:08:29 info tool.basesqooptool: using hive-specific delimiters output. can override 16/07/21 18:08:29 info tool.basesqooptool: delimiters --fields-terminated-by, etc. 16/07/21 18:08:29 debug sqoop.connfactory: loaded manager factory: org.apache.sqoop.manager.oracle.oraoopmanagerfactory 16/07/21 18:08:29 debug sqoop.connfactory: loaded manager factory: com.cloudera.sqoop.manager.defaultmanagerfactory 16/07/21 18:08:29 debug sqoop.connfactory: trying managerfactory: org.apache.sqoop.manager.oracle.oraoopmanagerfactory 16/07/21 18:08:29 debug oracle.oraoopmanagerfactory: data connector oracle , hadoop can called sqoop! 16/07/21 18:08:29 info oracle.oraoopmanagerfactory: data connector oracle , hadoop disabled. 16/07/21 18:08:29 debug sqoop.connfactory: trying managerfactory: com.cloudera.sqoop.manager.defaultmanagerfactory 16/07/21 18:08:29 debug manager.defaultmanagerfactory: trying scheme: jdbc:oracle:thin:@host:1521:sid 16/07/21 18:08:29 debug manager.oraclemanager$conncache: instantiated new connection cache. 16/07/21 18:08:29 info manager.sqlmanager: using default fetchsize of 1000 16/07/21 18:08:29 debug sqoop.connfactory: instantiated connmanager org.apache.sqoop.manager.oraclemanager@7d8704ef 16/07/21 18:08:29 info tool.codegentool: beginning code generation 16/07/21 18:08:29 debug manager.oraclemanager: using column names query: select t.* input_table t 1=0 16/07/21 18:08:29 debug manager.sqlmanager: execute getcolumninforawquery : select t.* input_table t 1=0 slf4j: class path contains multiple slf4j bindings. slf4j: found binding in [jar:file:/usr/hdp/2.4.0.0-169/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/staticloggerbinder.class] slf4j: found binding in [jar:file:/usr/hdp/2.4.0.0-169/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/staticloggerbinder.class] slf4j: see http://www.slf4j.org/codes.html#multiple_bindings explanation. slf4j: actual binding of type [org.slf4j.impl.log4jloggerfactory] 16/07/21 18:08:30 debug manager.oraclemanager: creating new connection jdbc:oracle:thin:@host:1521:sid/user, using username: user 16/07/21 18:08:30 debug manager.oraclemanager: no connection paramenters specified. using regular api making connection. 16/07/21 18:08:30 info manager.oraclemanager: time zone has been set gmt 16/07/21 18:08:30 debug manager.sqlmanager: using fetchsize next query: 1000 16/07/21 18:08:30 info manager.sqlmanager: executing sql statement: select t.* input_table t 1=0 16/07/21 18:08:30 debug manager.sqlmanager: found column c1 of type [2, 10, 0] 16/07/21 18:08:30 debug manager.sqlmanager: found column c2 of type [2, 10, 0] 16/07/21 18:08:30 debug manager.sqlmanager: found column c3 of type [12, 20, 0] 16/07/21 18:08:30 debug manager.sqlmanager: found column c4 of type [2, 0, -127] 16/07/21 18:08:30 debug manager.sqlmanager: found column c5 of type [2, 0, -127] 16/07/21 18:08:30 debug manager.sqlmanager: found column c6 of type [12, 80, 0] 16/07/21 18:08:30 debug manager.sqlmanager: found column c7 of type [93, 0, 0] 16/07/21 18:08:30 debug manager.sqlmanager: found column c8 of type [12, 20, 0] 16/07/21 18:08:30 debug manager.oraclemanager$conncache: caching released connection jdbc:oracle:thin:@host:1521:sid/user 16/07/21 18:08:30 error tool.importtool: imported failed: there no column found in target table intput_table. please ensure table name correct.
i found solution, seems --table parameter didn't work me, used --query parameter instead.
sqoop import \ --connect ${db_cnx_str} \ --username ${db_user} \ --password ${db_pwd} \ --query "select * ${input_table} \$conditions" \ --target-dir ${target_dir}/${input_table} \ --hive-import \ --hive-home "/usr/hdp/current/hive" \ --create-hive-table \ --hive-table "${output_db}.${output_table}" \ --num-mappers 1 
Comments
Post a Comment