Untitled

BIGDATA:SPARK

EXPERIMENT-8:

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

SQL Table Creation

Create two SQL tables, employees and salaries, using the following commands:

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(255), designation VARCHAR(255) );

CREATE TABLE salaries ( salary DECIMAL(10, 2), dept id INT, FOREIGN KEY (dept_id) REFERENCES employees(id) );

Hive External Table Creation

Create two external tables in Hive with similar schema to the SQL tables:

CREATE EXTERNAL TABLE employees hive ( id INT, name STRING, designation STRING )

ROW FORMAT DELIMITED FIELDS TERMINATED BY LOCATION/user/hive/employees';

CREATE EXTERNAL TABLE salaries_hive ( salary DECIMAL(10, 2), dept_id INT )

ROW FORMAT DELIMITED FIELDS TERMINATED BY LOCATION/user/hive/salaries":

BIGDATA:SPARK

Moving Data to Hive using Sqoop

Use Sqoop to import data from the SQL tables to the Hive external tables:

bash

sqoop import

-connect jdbc:mysql://localhost:3306/mydb

username myuser\

-password mypass

-table employees \

-target-dir/user/hive/employees \

-hive-import

-hive-table employees_hive;

sqoop import

connect jdbc:mysql://localhost:3306/mydb\

username myuser\

password mypass

-table salaries\

-target-dir/user/hive/salaries\

-hive-import

-hive-table salaries_hive;

Filtering a New Table

Create a new table in Hive that filters the data from the employees hive table

CREATE TABLE filtered_employees AS

SELECT FROM employees_hive

WHERE designation = 'Manager;

Writing a UDF to Encrypt the Table with AES-Algorithm

Create a Java class AESEncryptUDF.java that extends the UDF class:

import org.apache.hadoop.hive.ql.exec.UDF,

import org.apache.hadoop.io.Text;

import javax.crypto.Cipher

import javax.crypto.spec SecretKeySpec

import java.security. Key;

public class AESEncryptUDF extends UDF (

    public Text evaluate(Text input) throws Exception (

        String key "mysecretkey"; // replace with your secret key

        Key aeskey new SecretKeySpecikey.getBytes(), "AES");

        Cipher cipher Cipher.getInstance("AES");

        cipher.init(Cipher.ENCRYPT_MODE, aeskey):

        byte[] encryptedBytes = cipher.doFinal(input.getBytes());

        return new Text(encryptedBytes);BIGDATA:SPARK

        Compile the Java class and create a JAR file.

        Adding the UDF to Hive

        Add the UDF to Hive using the following command

        CREATE TEMPORARY FUNCTION aes encrypt AS AESEncryptUDF

        Encrypting the Table

        Encrypt the filtered_employees table using the aes encrypt UDF

        CREATE TABLE encrypted_employees AS

        SELECT aes encrypt(name), aes_encrypt(designation) FROM filtered employees

        Decrypting the Table

        To decrypt the table, you'll need to create another UDF that decrypts the data using the same s

        Create a Java class AESDecryptUDF java that extends the UDF class

        import org.apache.hadoop.hive.ql.exec.UDF: import org.apache.hadoop.io.Text import javax.crypto.Cipher,

        import javax.crypto.spec SecretKeySpec

        import java.security.Key:

        public class AESDecryptUDF extends UDF (

            public Text evaluate(Text input) throws Exception (

                String key="mysecretkey"; // replace with your secret key

                Key aesKey= new SecretKeySpec(key.getBytes(), "AES"): Cipher cipher Cipher.getInstance("AES");

                cipher.init(Cipher.DECRYPT_MODE, aeskey):

                byte[] decryptedBytes = cipher.doFinal(input.getBytes());

                return new Text(decryptedBytes):

                の

                ১

                و

                Compile the Java class and create a JAR file.

                Adding the UDF to Hive

                Add the UDF to Hive using the following command:

                CREATE TEMPORARY FUNCTION aes_decrypt AS 'AESDecryptUDF;

                Decrypting the Table

                Decrypt the encrypted_employees table using the aes decrypt UDF:

                CREATE TABLE decrypted_employees AS

                SELECT aes_decrypt(name), aes_decrypt(designation) FROM encrypted_employees;
            )
        )
    )
)