Durga Gadiraju

Durga Gadiraju

208.Prepare data for Export

  • Command to launch hive connecting to the specified database
    • hive --database training_sqoop_retail
  • Command to get the list of tables in database
    • SHOW tables;
  • Command to get meta-data on tables
    • DESCRIBE orders;
    • DESCRIBE order_items;
  • Command to preview the data in tables
    • SELECT * FROM orders LIMIT 10;
    • SELECT * FROM order_items LIMIT 10;
  • Command to count total records in a table
    • SELECT count(1) FROM orders;
    • SELECT count(1) FROM order_items;
  • Command to create a empty table
    • CREATE TABLE daily_revenue (order_date STRING,revenue FLOAT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
  • Command to insert data into table
    • INSERT INTO daily_revenue SELECT order_date, round(sum(order_item_subtotal),2) AS revenue FROM orders JOIN order_items ON order_id = order_item_order_id WHERE order_status IN ('COMPLETE' , 'CLOSED') GROUP BY order_date;
  • To validate table
    • SELECT * FROM daily_revenue;
  • Command to get meta-data on table
    • DESCRIBE FORMATTED daily_revenue;

Share this post