Forum

This topic contains 0 replies, has 1 voice, and was last updated by  Avikumar 2 years, 7 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #1271 Reply

    Avikumar
    Participant

    This chapter explains the built-in operators of Hive. There are four types of operators in Hive:

    Relational Operators
    Arithmetic Operators
    Logical Operators
    Complex Operators

    Relational Operators:
    These operators are used to compare two operands. The following table describes the relational operators available in Hive:

    Example
    Let us assume the employee table is composed of fields named Id, Name, Salary, Designation, and Dept as shown below. Generate a query to retrieve the employee details whose Id is 1205.

    +—–+————–+——–+—————————+——+
    | Id | Name | Salary | Designation | Dept |
    +—–+————–+————————————+——+
    |1201 | Gopal | 45000 | Technical manager | TP |
    |1202 | Manisha | 45000 | Proofreader | PR |
    |1203 | Masthanvali | 40000 | Technical writer | TP |
    |1204 | Krian | 40000 | Hr Admin | HR |
    |1205 | Kranthi | 30000 | Op Admin | Admin|
    +—–+————–+——–+—————————+——+
    The following query is executed to retrieve the employee details using the above table:

    hive> SELECT * FROM employee WHERE Id=1205;
    On successful execution of query, you get to see the following response:

    +—–+———–+———–+———————————-+
    | ID | Name | Salary | Designation | Dept |
    +—–+—————+——-+———————————-+
    |1205 | Kranthi | 30000 | Op Admin | Admin |
    +—–+———–+———–+———————————-+
    The following query is executed to retrieve the employee details whose salary is more than or equal to Rs 40000.

    hive> SELECT * FROM employee WHERE Salary>=40000;
    On successful execution of query, you get to see the following response:

    +—–+————+——–+—————————-+——+
    | ID | Name | Salary | Designation | Dept |
    +—–+————+——–+—————————-+——+
    |1201 | Gopal | 45000 | Technical manager | TP |
    |1202 | Manisha | 45000 | Proofreader | PR |
    |1203 | Masthanvali| 40000 | Technical writer | TP |
    |1204 | Krian | 40000 | Hr Admin | HR |
    +—–+————+——–+—————————-+——+
    Arithmetic Operators:
    These operators support various common arithmetic operations on the operands. All of them return number types. The following table describes the arithmetic operators available in Hive:

    Example
    The following query adds two numbers, 20 and 30.

    hive> SELECT 20+30 ADD FROM temp;
    On successful execution of the query, you get to see the following response:

    +——–+
    | ADD |
    +——–+
    | 50 |
    +——–+

    Logical Operators:
    The operators are logical expressions. All of them return either TRUE or FALSE.

    Example
    The following query is used to retrieve employee details whose Department is TP and Salary is more than Rs 40000.

    hive> SELECT * FROM employee WHERE Salary>40000 && Dept=TP;
    On successful execution of the query, you get to see the following response:

    +——+————–+————-+——————-+——–+
    | ID | Name | Salary | Designation | Dept |
    +——+————–+————-+——————-+——–+
    |1201 | Gopal | 45000 | Technical manager | TP |
    +——+————–+————-+——————-+——–+
    Complex Operators:
    These operators provide an expression to access the elements of Complex Types.

    Built-In Functions:
    Hive supports the following built-in functions:

    Example
    The following queries demonstrate some built-in functions:

    round() function:
    hive> SELECT round(2.6) from temp;
    On successful execution of query, you get to see the following response:

    floor() function:
    hive> SELECT floor(2.6) from temp;
    On successful execution of the query, you get to see the following response:

    floor() function:
    hive> SELECT ceil(2.6) from temp;
    On successful execution of the query, you get to see the following response:

    Creating a View
    You can create a view at the time of executing a SELECT statement. The syntax is as follows:

    CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], …) ]
    [COMMENT table_comment]
    AS SELECT …

    The following query retrieves the employee details using the above scenario:

    hive> CREATE VIEW emp_30000 AS
    SELECT * FROM employee
    WHERE salary>30000;
    Dropping a View
    Use the following syntax to drop a view:

    DROP VIEW view_name:
    The following query drops a view named as emp_30000:

    hive> DROP VIEW emp_30000;
    Creating an Index
    An Index is nothing but a pointer on a particular column of a table. Creating an index means creating a pointer on a particular column of a table. Its syntax is as follows:

    CREATE INDEX index_name
    ON TABLE base_table_name (col_name, …)
    AS ‘index.handler.class.name’
    [WITH DEFERRED REBUILD]
    [IDXPROPERTIES (property_name=property_value, …)]
    [IN TABLE index_table_name]
    [PARTITIONED BY (col_name, …)]
    [
    [ ROW FORMAT …] STORED AS …
    | STORED BY …
    ]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (…)]

    Dropping an Index
    The following syntax is used to drop an index:

    DROP INDEX <index_name> ON <table_name>
    The following query drops an index named index_salary:

    hive> DROP INDEX index_salary ON employee;

Viewing 1 post (of 1 total)
Reply To: Hive – Built-in Operators
Your information:




cf22

Your Name (required)

Your Email (required)

Subject

Phone No

Your Message

Cart

  • No products in the cart.