Big Data Analytics with Apache Hadoop Study Notes 3

 

 

 

     LLAP

 

 

-- 1.Creating a Table:
CREATE TABLE employees (emp_id INT, emp_name STRING, emp_salary DOUBLE) STORED AS ORC TBLPROPERTIES ('transactional'='true');

-- 2. Inserting Data:
INSERT INTO employees VALUES (1, 'Alice', 65000.0), (2, 'Bob', 75000.0), (3, 'Charlie', 60000.0);

-- 3. Selecting Data:
SELECT emp_name, emp_salary FROM employees;

-- 4. Updating Data:
CREATE TABLE departments (dept_id INT, dept_name STRING);

-- 5. Deleting Data:
DELETE FROM employees WHERE emp_id = 2;

-- 6. Sorting Data:
SELECT emp_name, emp_salary FROM employees ORDER BY emp_salary DESC;

-- 7. Aggregating Data:
SELECT AVG(emp_salary) AS avg_salary, MAX(emp_salary) AS max_salary FROM employees;

-- 8. Joining Tables:
CREATE TABLE departments ( dept_id INT, dept_name STRING ); 
INSERT INTO TABLE departments VALUES (1, 'HR'), (2, 'IT'); 
SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d ON e.emp_id = d.dept_id;

-- 9. Subqueries:
SELECT emp_name FROM employees WHERE emp_salary > (SELECT AVG(emp_salary) FROM employees);

-- 10. Creating Views:
CREATE VIEW high_earners AS SELECT emp_name, emp_salary FROM employees WHERE emp_salary > 60000.0;

-- 11. Inserting Data with SELECT:
INSERT INTO TABLE employees SELECT 4, 'David', 72000.0;

-- 12. Merging Data (Upsert):
MERGE INTO employees AS target 
USING temp_employees AS source 
ON target.emp_id = source.emp_id 
WHEN MATCHED THEN UPDATE SET target.emp_salary = source.emp_salary 
WHEN NOT MATCHED THEN INSERT VALUES (source.emp_id, source.emp_name, source.emp_salary);

-- 13. Renaming a Table:
ALTER TABLE employees RENAME TO staff;

-- 14. Adding a Column:
ALTER TABLE employees ADD COLUMNS ( emp_department STRING );

-- 15. Dropping a Table:
DROP TABLE IF EXISTS employees;

-- 16. Truncating a Table:
TRUNCATE TABLE staff;