sqoop: truncate table prior export data from hdfs

We are using Sqoop to export data from the hive to SQL Server. The new data is always appended to the existing data in SQL Server.

Is it possible to truncate the SQL Server table via Sqoop before starting the export?

 

sqoop eval --connect 'jdbc:sqlserver://1.1.1.1;database=SomeDatabase;username=someUser;password=somePassword' --query "TRUNCATE TABLE some_table"

In hue, I create a sqoop action likes

<workflow-app name="inokmobile-delete-before-export" xmlns="uri:oozie:workflow:0.4">
    <start to="inokmobile-delete-before-export"/>
    <action name="inokmobile-delete-before-export">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
              <arg> eval</arg>
              <arg>--connect</arg>
              <arg>jdbc:mysql://192.168.10.200:3306/new_inoknok</arg>
              <arg>--username</arg>
              <arg>dba</arg>
              <arg>--password</arg>
              <arg>123456</arg>
              <arg>--query</arg>
              <arg>TRUNCATE TABLE  ${tablename}</arg>
        </sqoop>
        <ok to="end"/>
        <error to="kill"/>
    </action>
    <kill name="kill">
        <message>Action failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

 

The table is truncated correctly. But the job is killed due to erro

 IllegalArgumentException: JobId string : is not properly formed 

 

I thought the right way is to write a java procedure to execute this functionality.

 

package com.inoknok.mysql.tool;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TruncateMysqlTable {
    private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    private static final String sql_prefix = "TRUNCATE TABLE ";

    public static void main(String[] args) {
	int argsLength = args.length;

	if (argsLength < 4) {
	    System.out
		    .println("Please input parameters: url, user,password, tableTruncated");
	    System.exit(-1);

	}

	String url = args[0];
	String user = args[1];
	String password = args[2];

	List<String> tables = new ArrayList<String>();

	for (int i = 3; i < argsLength; i++) {
	    tables.add(args[i]);
	}

	try {
	    Class.forName(MYSQL_DRIVER);
	    Connection conn = DriverManager.getConnection(url, user, password);
	    if (!conn.isClosed()) {
		Statement statement = conn.createStatement();

		for (String table : tables) {
		    String sql = sql_prefix + table;
		    statement.execute(sql);
		}

		closeResources(conn, statement);
	    }
	} catch (Exception e) {
	    System.out.println("Fail to truncate table !");
	    e.printStackTrace();
	}
    }

    public static void closeResources(Connection conn, Statement pstmt,
	    ResultSet rs) {
	if (null != rs) {
	    try {
		rs.close();
	    } catch (SQLException e) {
		e.printStackTrace();
		throw new RuntimeException(e);
	    } finally {
		if (null != pstmt) {
		    try {
			pstmt.close();
		    } catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		    } finally {
			if (null != conn) {
			    try {
				conn.close();
			    } catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			    }
			}
		    }
		}
	    }
	}
    }

    public static void closeResources(Connection conn, Statement pstmt) {

	if (null != pstmt) {
	    try {
		pstmt.close();
	    } catch (SQLException e) {
		e.printStackTrace();
		throw new RuntimeException(e);
	    } finally {
		if (null != conn) {
		    try {
			conn.close();
		    } catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		    }
		}
	    }
	}
    }

}

 

 

 

References

http://stackoverflow.com/questions/15808257/sqoop-truncate-sql-server-table-before-exporting-data-from-hadoop