Support Questions
Find answers, ask questions, and share your expertise

Sqoop export to MySQL table containing a auto populating time stamp

New Contributor

I am looking to use Sqoop to export data from a Hive table to MySQL. The Hive table and MySQL table are identically defined, with the same columns and types. There is one difference, the MySQL table contains one extra column that auto populates the timestamp of when each row was inserted. When we try the export with that table, we get errors. Is there an option to handle this kind of case? We would like to store the timestamp of when the rows were loaded, as that is useful information

2 REPLIES 2

Mentor

@Hemant Varma you will need to run it as query and add a timestamp to table schema in MySQL take a look at this example http://www.mysqltutorial.org/mysql-timestamp.aspx

Hi @Hemant Varma, I have completely rewritten my answer as I initially thought you are talking about import. In case of an export to a table with default timestamp, the solution is to use the "--columns" option for export available only in recent versions of Sqoop like 1.4.5 and 1.4.6 (HDP-2.3.x includes Sqoop-1.4.6 and so you are good to go). In the "--columns" you should specify all columns from your Mysql table except the timestamp column. Here is an example.

Mysql table

create table et1 (id int, name varchar(16), ts timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

A few lines from my input file

100,jim 
110,mary 
120,bob

Sqoop export

sqoop export --connect jdbc:mysql://localhost:3306/test --driver com.mysql.jdbc.Driver --username it1 -P --table et1 -m 1 --columns id,name --export-dir /user/it1/exp1
16/02/20 20:31:50 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950
...
16/02/20 20:32:26 INFO mapreduce.ExportJobBase: Exported 300 records.

Some exported lines

mysql> select * from et1 limit 3;
+------+------+---------------------+
| id   | name | ts                  |
+------+------+---------------------+
|  100 | jim  | 2016-02-20 20:32:24 |
|  110 | mary | 2016-02-20 20:32:24 |
|  120 | bob  | 2016-02-20 20:32:24 |
+------+------+---------------------+
3 rows in set (0.00 sec)