Durga Gadiraju

Durga Gadiraju

220.Sqoop Export – Using Stage Table

  • To display information and usage instructions related to export command
    • sqoop help export
  • Command to connect to mysql
    • mysql -u retail_user -h ms.itversity.com -p
  • Command to connect to a database
    • USE retail_export;
  • Select statement on table
    • SELECT count(1) FROM training_daily_revenue;
  • Command to view records in table
    • SELECT * FROM training_daily_revenue LIMIT 10;
  • Command to delete records from table
    • DELETE FROM training_daily_revenue WHERE order_date !=  '2013-08-03 00:00:00.0';
  • Command to commit the changes made in table
    • COMMIT
  • Command to launch hive connecting to the specified database
    • hive --database training_sqoop_retail
  • Command to get meta-data on table
    • DESCRIBE FORMATTED daily_revenue;
  • Sqoop command to export data
    • sqoop export --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --export-dir /apps/hive/warehouse/training_sqoop_retail.db/daily_revenue --table training_daily_revenue
  • Command to create a staging table
    • CREATE TABLE training_daily_revenue_stage AS SELECT * FROM training_daily_revenue WHERE 1=2;
  • Command to delete records from table
    • DELETE FROM training_daily_revenue WHERE order_date != '2013-08-03 00:00:00.0' ;
  • Command to commit the changes made in table
    • COMMIT
  • Command to alter table column by adding primary key
    • ALTER TABLE training_daily_revenue_stage ADD PRIMARY KEY (order_date);
  • Command to export data using staging table
    • sqoop export --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --export-dir /apps/hive/warehouse/training_sqoop_retail.db/daily_revenue --table training_daily_revenue --staging-table training_daily_revenue_stage
  • Command to export data using clear staging table if staging table already consists data
    • sqoop export --connect jdbc:mysql://ms.itversity.com:3306/retail_export --username retail_user --password itversity --export-dir /apps/hive/warehouse/training_sqoop_retail.db/daily_revenue --table training_daily_revenue --staging-table training_daily_revenue_stage --clear-staging-table

Share this post