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

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

    Avikumar
    Participant

    HiveQL – Select-Where:

    Syntax
    Given below is the syntax of the SELECT query:

    SELECT [ALL | DISTINCT] select_expr, select_expr, …
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list]
    [HAVING having_condition]
    [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
    [LIMIT number];

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

    hive> SELECT * FROM employee WHERE salary>30000;

    HiveQL – Select-Order By:

    Syntax
    Given below is the syntax of the ORDER BY clause:

    SELECT [ALL | DISTINCT] select_expr, select_expr, …
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list]
    [HAVING having_condition]
    [ORDER BY col_list]]
    [LIMIT number];

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

    hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;

    HiveQL – Select-Group By

    Syntax
    The syntax of GROUP BY clause is as follows:

    SELECT [ALL | DISTINCT] select_expr, select_expr, …
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list]
    [HAVING having_condition]
    [ORDER BY col_list]]
    [LIMIT number];

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

    hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT;

    HiveQL – Select-Joins:
    Syntax
    join_table:

    table_reference JOIN table_factor [join_condition]
    | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
    join_condition
    | table_reference LEFT SEMI JOIN table_reference join_condition
    | table_reference CROSS JOIN table_reference [join_condition]

    There are different types of joins given as follows:

    JOIN
    LEFT OUTER JOIN
    RIGHT OUTER JOIN
    FULL OUTER JOIN
    JOIN
    JOIN clause is used to combine and retrieve the records from multiple tables. JOIN is same as OUTER JOIN in SQL. A JOIN condition is to be raised using the primary keys and foreign keys of the tables.

    The following query executes JOIN on the CUSTOMER and ORDER tables, and retrieves the records:

    hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT
    FROM CUSTOMERS c JOIN ORDERS o
    ON (c.ID = o.CUSTOMER_ID);

    LEFT OUTER JOIN
    The HiveQL LEFT OUTER JOIN returns all the rows from the left table, even if there are no matches in the right table. This means, if the ON clause matches 0 (zero) records in the right table, the JOIN still returns a row in the result, but with NULL in each column from the right table.

    A LEFT JOIN returns all the values from the left table, plus the matched values from the right table, or NULL in case of no matching JOIN predicate.

    The following query demonstrates LEFT OUTER JOIN between CUSTOMER and ORDER tables:

    hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
    FROM CUSTOMERS c
    LEFT OUTER JOIN ORDERS o
    ON (c.ID = o.CUSTOMER_ID);

    RIGHT OUTER JOIN
    The HiveQL RIGHT OUTER JOIN returns all the rows from the right table, even if there are no matches in the left table. If the ON clause matches 0 (zero) records in the left table, the JOIN still returns a row in the result, but with NULL in each column from the left table.

    A RIGHT JOIN returns all the values from the right table, plus the matched values from the left table, or NULL in case of no matching join predicate.

    The following query demonstrates RIGHT OUTER JOIN between the CUSTOMER and ORDER tables.

    notranslate”> hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);

    FULL OUTER JOIN
    The HiveQL FULL OUTER JOIN combines the records of both the left and the right outer tables that fulfil the JOIN condition. The joined table contains either all the records from both the tables, or fills in NULL values for missing matches on either side.

    The following query demonstrates FULL OUTER JOIN between CUSTOMER and ORDER tables:

    hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
    FROM CUSTOMERS c
    FULL OUTER JOIN ORDERS o
    ON (c.ID = o.CUSTOMER_ID);

Viewing 1 post (of 1 total)
Reply To: HiveQL
Your information:




cf22

Your Name (required)

Your Email (required)

Subject

Phone No

Your Message

Cart

  • No products in the cart.