Sqoop : Truncate SQL Server table before exporting data from Hadoop

Question

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?

Answer 1

You can use sqoop eval to execute arbitrary SQL on the database. This will allow you to truncate the table without "leaving" Sqoop. For example:

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

sqoop export --connect 'jdbc:sqlserver://1.1.1.1;database=SomeDatabase;username=someUser;password=somePassword' --export-dir /path/to/someTable/on/HDFS --table some_table --fields-terminated-by \001

--fields-terminated-by \001 assumes that the Hive table is using the default delimiters.