AI & DS HIVE EXPERIMENT

 open terminal

>hive

>CREATE DATABASE IF NOT EXISTS company_db;

USE company_db;

 

        

open new terminal

> vi  hivescript1.ql

       use company_db;

        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;

 

>  hive -S -f hivescript1.ql

> vi  hivescript2.ql

     use company_db;

        CREATE TABLE IF NOT EXISTS departments (

           department_id INT,

           department_name STRING

        ) ROW FORMAT DELIMITED

        FIELDS TERMINATED BY   ','

        STORED AS TEXTFILE;

>  hive -S -f hivescript2.ql

cat > emp.txt

1,John,101,50000
2,Anita,101,45000
3,Ravi,102,65000
4,Meena,103,55000
5,Kiran,104,40000

cat>department.txt

101,hr
102,mark
103,fin

go to hive

hive> load data local inpath 'emp.txt' into table employees;

hive> load data local inpath 'department.txt' into table departments;

-- 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;