Hive - Getting Started - Hands-on




Not able to play video? Try with vimeo

Important Note - We recommend you to execute the given commands on Hive Console instead of Hue. The video is only for representational purposes.

We can access Hive from the command line and Hue.

Let's connect to hive from the command line. Login into CloudxLab Linux console. Type Hive and wait for Hive command-line interface - CLI to appear.

By default, the database with the name "default" is the current database in the hive shell. To see the list of all databases, type "show databases". As you can see, there are 1623 databases in Hive. To see the list of all tables, type "show tables". There are 1811 tables in the default database. Please create your own database with your own username. Make sure to create all the tables in your database instead of polluting the "default" database.

Let's say your username is abhinav9884. Type create database abhinav9884 to create your own database. Type describe database your-database-name to see the metadata of the database. As you can see, the database is located inside the /apps/hive/warehouse directory.

Let us create a table with the name x inside your database. Type use your-database-name and press enter. As you can see the current database has changed to your database. Type CREATE TABLE x(a INT), press enter, and wait till table gets created.

Let us access Hive using Hue. Hue provides a really good user interface for interacting with Hive. Login to Hue. Click on "Query Editors", select hive and wait for hive query editor to load. Please note that you will have to refresh the list every time you create or update a table or database. Click on database dropdown to see the list of all of the databases. Select your database with the name as your username because we created the database with our username. In case it is not listed, you can type first few characters of your database in the search box and your database would appear. Now, we have selected our database. And you can see that it has only one table x. Make sure to always select your database from the list of databases before creating any table.

Let us select data from table x. Type select * from x in the query editor and click on execute. Since we do not have any data in this table, we will not get any record.

The DESCRIBE command displays metadata or information about a table, such as names of columns and their data types. To see metadata of table x type describe x and click on execute. There is only one column "a" with "int" datatype.

To see the metadata and low-level details, type describe formatted x. We can see column names, their data types, database name, owner name, created time, hive warehouse location, and table type.

INSTRUCTIONS

Steps:

  • Login to the web console using your cloudxlab username and password
  • Launch Hive by typing hive in the web console
  • To see the list of all databases type command:

    show databases;
    
  • To see the list of all tables type command:

    show tables;
    
  • To create your own database run below commands. ${env:USER} gets replaced by your username automatically:

    create database ${env:USER};
    
  • To see the metadata of your database run below commands. ${env:USER} gets replaced by your username automatically:

    describe database ${env:USER};
    
  • To use your database run below commands. ${env:USER} gets replaced by your username automatically:

    use ${env:USER};
    
  • To create a table x in your database type command:

    create table x (a int);
    
  • To view the data of table x type command:

    select * from x;
    
  • To view the metadata(structure) of the table type command:

    describe x;
    
  • To see the metadata and low-level details type command:

    describe formatted x;
    

Loading comments...