Home | About | Sematext search-lucene.com search-hadoop.com
 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