Untitled

BIGDATA SPARK

EXPERIMENT-7:

(1) Creating hive tables(External and internall

(ii) Loading data to external hive tables from sal tables(or)Structured csv using scoop

(iii) Performing operations like filterations and updations

(iv) Performing Join(inner, outer etc)

(V) Writing User defined function on hive tables

(1) Creating Hive Tables (External and Internal)

Internal Table:

CREATE TABLE internal table (

    id INT,

    name STRING

    age INT

    ROW FORMAT DELIMITED

    FIELDS TERMINATED BY

    External Table:

    CREATE EXTERNAL TABLE external table (

        id INT.

        name STRING

        age INT

        ROW FORMAT DELIMITED

        FIELDS TERMINATED BY

        LOCATION/path/to/external/data

        (ii) Loading Data to External Hive Table from SQL Table or Structured CSV using Sqoop

        Using Sqoop:

        bash

        sqoop import

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

        --username myuser\

        password mypass

        table mytable

        -target-dir/path/to/hive/data\

        -hive-import

        --hive-table external table:

        Using Hive Query:

        LOAD DATA LOCAL INPATH /path/to/csv/data.csv

        OVERWRITE INTO TABLE external table:

        CBIGDATA:SPARK

        (iii) Performing Operations like Filterations and Updations.

        Filteration:

        SELECT FROM mytable WHERE age > 25:

        Updation (Hive doesn't support UPDATE, but you can use INSERT OVERWRITE)

        INSERT OVERWRITE TABLE mytable SELECT id, name, age + 1 AS age FROM mytable:

        (iv) Performing Join (Inner, Outer, etc.)

        Inner Join:

        SELECT FROM table1

        INNER JOIN table2

        ON table1 id table2 id:

        Left Outer Join:

        SELECT FROM table1 LEFT OUTER JOIN table2 ON table1.id table2.id;

        (v) Writing User-Defined Function (UDF) on Hive Tables

        Example UDF in Java:

        import org.apache.hadoop.hive.ql.exec.UDF: import org.apache.hadoop.io.Text;

        public class UpperCaseUDF extends UDF ( public Text evaluate(Text input) { return new Text(input.toString().toUpperCase());
        
        Compile the java class and create a JAR file,
        
        Add UDF to Hive:
        
        CREATE TEMPORARY FUNCTION upper_case AS 'UpperCaseUDF;
        
        Use UDF in Hive Query:
        
        SELECT upper_case(name) FROM mytable:})
    )
))