Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop >> mail # user >> SQOOP export from Hadoop Hive to MySQL


Copy link to this message
-
SQOOP export from Hadoop Hive to MySQL
Greeting experts,

   I have a table "CHRYSLER_Anametrix_Aggregated" in Hive and in MySQL.
Similarly I can find this table in hdfs --
/user/hive/warehouse/chrysler_anametrix_aggregated.
   I can successfully execute the following SQOOP command from namenode CLI -

   sqoop export --verbose --connect
jdbc:mysql://ec2-23-22-223-47.compute-1.amazonaws.com:3306/test --table
CHRYSLER_Anametrix_Aggregated --update-key
VisitorID,VisitorDay,VisitorMonth,VisitorYear,VisitorSession
--update-mode allowinsert --export-dir
/user/hive/warehouse/chrysler_anametrix_aggregated --username root -m 1
--input-fields-terminated-by '|' --input-null-string '\\N'
--input-null-non-string '\\N'

   However, when I want to run the above as part of Oozie workflow action ...
    <action name="sqoop-node">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
           
 <configuration>
                <property>
                    <name>sqoop.connection.factories</name>
                    <value>com.cloudera.sqoop.manager.DefaultManagerFactory</value>
                </property>
                <property>
                   
 <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
                <property>
                    <name>mapred.compress.map.output</name>
                    <value>true</value>
                </property>
               
 <property>
                    <name>oozie.service.WorkflowAppService.system.libpath</name>
                    <value>${nameNode}/user/oozie/share/lib/sqoop</value>
                </property>
            </configuration>
           
 <command>export --verbose --connect
jdbc:mysql://ec2-23-22-223-47.compute-1.amazonaws.com:3306/test --table
CHRYSLER_Anametrix_Aggregated --update-key
VisitorID,VisitorDay,VisitorMonth,VisitorYear,VisitorSession
--update-mode allowinsert --export-dir
 /user/hive/warehouse/chrysler_anametrix_aggregated --username root -m 1
 --input-fields-terminated-by '|' --input-null-string '\\N'
--input-null-non-string '\\N'</command>
        </sqoop>
        <ok to="end"/>
        <error to="fail"/>
    </action>

   I get the following error message. I have "mysql-connector-java.5.1.24-bin.jar" shared library - /user/oozie/share/lib/sqoop.

   Hadoop version - 2.00 - CDH4.2.0
   Sqoop version - 1.4.2. - CDH4.2.0
   Can you experts please guide me  ... a rookie to Hadoop and its projects.

2013-05-21 00:48:35,135 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead 2013-05-21 00:48:37,394 WARN org.apache.hadoop.conf.Configuration: session.id is deprecated. Instead, use dfs.metrics.session-id 2013-05-21 00:48:37,408 INFO org.apache.hadoop.metrics.jvm.JvmMetrics: Initializing JVM Metrics with processName=MAP, sessionId= 2013-05-21 00:48:38,042 INFO org.apache.hadoop.util.ProcessTree: setsid exited with exit code 0 2013-05-21 00:48:38,058 INFO org.apache.hadoop.mapred.Task:  Using ResourceCalculatorPlugin : org.apache.hadoop.util.LinuxResourceCalculatorPlugin@5dce1bea 2013-05-21 00:48:38,423 INFO org.apache.hadoop.mapred.MapTask: Processing split: hdfs://ip-10-147-191-48.ec2.internal:8020/user/ubuntu/oozie-oozi/0000042-130520131050057-oozie-oozi-W/sqoop-node--sqoop/input/dummy.txt:0+5 2013-05-21 00:48:38,453 WARN mapreduce.Counters: Counter name MAP_INPUT_BYTES
 is deprecated. Use FileInputFormatCounters as group name and  BYTES_READ as counter name instead 2013-05-21 00:48:38,459 INFO org.apache.hadoop.mapred.MapTask: numReduceTasks: 0 2013-05-21 00:48:39,743 WARN org.apache.sqoop.tool.SqoopTool: $SQOOP_CONF_DIR has not been set in the environment. Cannot check for additional configuration. 2013-05-21 00:48:40,032 DEBUG org.apache.sqoop.tool.BaseSqoopTool: Enabled debug logging. 2013-05-21 00:48:40,036 WARN org.apache.sqoop.SqoopOptions: Character argument '|' has multiple characters; only the first will be used. 2013-05-21 00:48:40,084 DEBUG org.apache.sqoop.ConnFactory: sqoop.connection.factories is set; ignoring managers.d 2013-05-21 00:48:40,139 DEBUG org.apache.sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory 2013-05-21 00:48:40,139 DEBUG org.apache.sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory 2013-05-21 00:48:40,140
 DEBUG org.apache.sqoop.manager.DefaultManagerFactory: Trying with scheme: jdbc:mysql: 2013-05-21 00:48:40,183 INFO org.apache.sqoop.manager.MySQLManager: Preparing to use a MySQL streaming resultset. 2013-05-21 00:48:40,183 DEBUG org.apache.sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.MySQLManager@795e0c2b 2013-05-21 00:48:40,184 INFO org.apache.sqoop.tool.CodeGenTool: Beginning code generation 2013-05-21 00:48:40,233 DEBUG org.apache.sqoop.manager.SqlManager: No connection paramenters specified. Using regular API for making connection. 2013-05-21 00:48:40,911 DEBUG org.apache.sqoop.manager.SqlManager: Using fetchSize for next query: -2147483648 2013-05-21 00:48:40,911 INFO org.apache.sqoop.manager.SqlManager: Executing SQL statement: SELECT t.* FROM `CHRYSLER_Anametrix_Aggregated` AS t LIMIT 1 2013-05-21 00:48:40,930 DEBUG org.apache.sqoop.orm.ClassWriter: selected columns: 2013-05-21 00:48:40,931 DEBUG
 org.apache.sqoop.orm.ClassWriter:   VisitorID 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   VisitorDay 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   VisitorMonth 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   VisitorYear 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   VisitorSession 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   Brand 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   DeviceType 2013-05-21 00:48:40,931 DEBUG org.apache.sqoop.orm.ClassWriter:   De
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB