hive lab manual

 

Use Hive to create, alter, and drop databases, tables, views, functions, and

    indexes.

 

        -- Create a new database

        CREATE DATABASE IF NOT EXISTS company_db;

 

        -- Switch to the newly created database

        USE company_db;

 

        -- Create an employees table

        CREATE TABLE IF NOT EXISTS employees (

           employee_id INT,

           name STRING,

           department_id INT,

           salary FLOAT

        ) ROW FORMAT DELIMITED

        FIELDS TERMINATED BY ','

        STORED AS TEXTFILE;

 

        -- Create a departments table

        CREATE TABLE IF NOT EXISTS departments (

           department_id INT,

           department_name STRING

        ) ROW FORMAT DELIMITED

        FIELDS TERMINATED BY ','

        STORED AS TEXTFILE;

 

        -- Add a new column to the employees table

        ALTER TABLE employees ADD COLUMNS (hire_date DATE);

 

        -- Create a view to show high salary employees

        CREATE VIEW IF NOT EXISTS high_salary_employees AS

        SELECT name, salary

        FROM employees

        WHERE salary > 60000;

 

        -- Create an index on the employees table for the department_id column

        CREATE INDEX idx_department_id

        ON TABLE employees (department_id)

  AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'

     WITH DEFERRED REBUILD;

 

     -- Drop the view

     DROP VIEW IF EXISTS high_salary_employees;

 

     -- Drop the employees table

     DROP TABLE IF EXISTS employees;

 

     -- Drop the departments table

     DROP TABLE IF EXISTS departments;

 

     -- Drop the index

     DROP INDEX IF EXISTS idx_department_id ON TABLE employees;

 

     -- Drop the database

     DROP DATABASE IF EXISTS company_db CASCADE;

 

     Notes

    • Make sure to replace the paths and class names with the actual paths and class names for your

       UDFs.

    • The CASCADE option in the DROP DATABASE command will drop all tables and views in the

       database.

    • You can run these commands in a Hive shell or through a Hive client that supports HiveQL.