215.Sqoop Export – Quick Overview of MySQL for Upsert

  • Command to connect to mysql
    • mysql -u retail_user -h ms.itversity.com -p
  • Command to connect to a database
    • USE retail_export;
  • Command to get meta-data on table
    • DESCRIBE training_daily_revenue;
  • Command to truncate the table
    • TRUNCATE TABLE training_daily_revenue;
  • Command to insert single record into table
    • INSERT INTO training_daily_revenue VALUES ('2013-07-25 00:00:00.0',12500.0) ;
    • INSERT INTO training_daily_revenue VALUES ('2013-07-26 00:00:00.0',15000.0) ;
  • Command to update a record in table
    • UPDATE training_daily_revenue SET revenue = 1750.0 WHERE order-date = '2013-07-25 00:00:00.0' ;
  • Command to commit the changes made in table
    • COMMIT
  • Command for upsert operation
    • INSERT INTO training_daily_revenue VALUES ('2013-07-25 00:00:00.0', 10500.0) ON DUPLICATE KEY UPDATE revenue = 10500.0;
  • To validate table
    • SELECT * FROM training_daily_revenue;

Share this post