Big Data Spark
Lab Experiment – 8
Aim : Create a sql table of employees Employee table with id, designation Salary table (salary,
dept id) Create external table in hive with similar schema of above tables, Move data to
hive using scoop and load the contents into tables, filter a new table and write a UDF to
encrypt the table with AES-algorithm, Decrypt it with key to show contents.
Program : Open Online MySQL compiler at https://onecompiler.com/mysql/ and run below
commands.
Step 1: Create SQL Tables
Code:
CREATE TABLE Employee (
id INT PRIMARY KEY,
name VARCHAR(100),
designation VARCHAR(100)
);
Out – Put :
//Check left side you will observe table
Step 2: Create the Salary Table
Code:
CREATE TABLE Salary (
salary DECIMAL(10,2),
dept_id INT
);
Out – Put :
//Check left side you will observe table
Step 3: Insert Sample Data
Code:
INSERT INTO Employee (id, name, designation) VALUES
(1, 'Alice', 'Software Engineer'),
(2, 'Bob', 'Data Analyst'),
(3, 'Charlie', 'Project Manager');
INSERT INTO Salary (salary, dept_id) VALUES
(70000, 101),
(60000, 102),
(90000, 103);
Computer Science & Engineering Dr.Vadali Srinivas
Big Data Spark
Out – Put :
//Check left side you will observe table
Step 2: Simulating an External Hive Table (Alternative)
Since Hive External Tables are not possible in a standard SQL environment, we can create a
view to represent external data.
2.1 Create a View to Simulate an External Table
Code:
CREATE VIEW ExternalEmployee AS
SELECT * FROM Employee;
Out – Put :
//Check left side you will observe table
This works like an external table but without modifying actual data.
Step 3: Move Data to Hive using Sqoop (Alternative SQL Approach)
Since Sqoop is not available, we simulate data loading using SQL INSERT INTO SELECT:
Code:
INSERT INTO Employee (id, name, designation)
SELECT * FROM ExternalEmployee;
Out – Put :
//Check left side you will observe table
This moves data from an external source (simulated view) to the table.
Step 4: Filtering Data into a New Table
Code:
CREATE TABLE FilteredEmployee AS
SELECT * FROM Employee WHERE designation = 'Software Engineer';
Out – Put :
//Check left side you will observe table
This filters employees only with the designation "Software Engineer" into a new table.
Computer Science & Engineering Dr.Vadali Srinivas
Big Data Spark
Step 5: Writing a UDF for AES Encryption
Since SQL does not directly support writing User-Defined Functions (UDFs) in all
environments, we use AES encryption functions available in MySQL.
5.1 Encrypt the Data
Code:
ALTER TABLE Employee ADD COLUMN encrypted_name VARBINARY(255);
UPDATE Employee
SET encrypted_name = AES_ENCRYPT(name, 'my_secret_key');
Out – Put :
//Check left side you will observe table
5.2 Decrypt the Data
SELECT id, AES_DECRYPT(encrypted_name, 'my_secret_key') AS decrypted_name FROM
Employee;
Out – Put :
Output:
+------+----------------+
| id | decrypted_name |
+------+----------------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
+------+----------------+
Computer Science & Engineering Dr.Vadali Srinivas
