Hive: How to view different objects present in Hive Metastore

How to view the databases present in Hive Metastore?

Execute the following command at Hive prompt

         SHOW DATABASES;

How to change / use different database?

Execute the following command at Hive prompt. Replace the <<Database_Name>> with actual database name.

       USE <<Database_Name>>;
 
       USE default;

How to view the tables present in a database?

Execute the following command at Hive prompt

       SHOW TABLES;

Hive: How to clear Hive Console

How to clear Hive Console / Screen?

Execute any of the following commands on Hive Console

     !clear;

OR

    CTRL + L

Hive: How to display column headers when executed SELECT statement

How to display Column Names / Headers when executed SELECT statement?

Set the below Hive property before executing the SELECT statement at hive prompt.

        SET hive.cli.print.header=true;

After executing the above statement execute the SELECT statement

Hive: Insert OR Load data

1. Load data from another table
insert overwrite table batting
SELECT
regexp_extract(col_value,'^(?:([^,]*)\,?){1}',1) player_id,
regexp_extract(col_value,'^(?:([^,]*)\,?){2}',1) year,
regexp_extract(col_value,'^(?:([^,]*)\,?){9}',1) run
FROM temp_batting;
2. Load data from File
LOAD DATA INPATH '/user/hue/Batting.csv' OVERWRITE INTO TABLE temp_batting

HIVE: Create Table

CREATE TABLE TABLE_NAME (COL1 STRING, COL2 INT);

create table Batting
(PlayerId STRING, Year INT,Runs INT);

Hive: Select Statements

1. SELECT * FROM Table_Name;
2. SELECT * FROM Table_Name WHERE Column_Name =’’;
3. SELECT COUNT(*) FROM TABLE_Name;
4. SELECT COL_1, MAX(COL_2) FROM Table_Name
GROUP BY COL_1
5. SELECT a.* from Table_Name a
Join (select * from table_name ) b
On (a.col_1 = b.col_1)

Hive: Hive Commands

How to invoke Hive


hive


How to exit from Hive


quit;


List all databases present in Hive


show databases;


Execute commands in a database


use database_name;


List all tables present in a database


show tables


How to view table structure


describe table_name;