7 / 18

Hive - Select and Aggregation Queries

Not able to play video? Try with youtube

Type hive and wait for hive prompt to appear. This is basically command-line interface or CLI. Please use your own database and run use space followed by your database name, in this command the string ${env:USER} is replaced by your username directly if your username and the database name are same you can use this variable instead. To see the values of all the columns of the table we can use select * from table name so type select * from nyse; to see all columns of nyse table to see only exchange1 and symbol1 columns type select exchange1, symbol1 from nyse followed by semicolon;. We can write aggregation queries in hive in the same way we write in SQL. To find the average opening price of each stock in nyse table type select symbol1, AVG(price_open) as avg_price from nyse group by symbol1;, we can see the average opening price of each stock



  • Login to the web console.
  • Launch Hive by running command hive. Run the below commands in Hive.
  • Use your database. Run the following command. ${env:USER} gets replaced by your username automatically:

    use ${env:USER};
  • To select all columns from the table nyse

    SELECT * FROM nyse;
  • To select only required columns from the table nyse

    SELECT exchange1, symbol1 FROM nyse;
  • Find the average opening price for each stock

    SELECT symbol1, AVG(price_open) AS avg_price FROM nyse
    GROUP BY symbol1;

Loading comments...