hive select where

Hive查詢語言(HiveQL)是一種查詢語言,Hive處理在Metastore分析結構化資料。本章介紹了如何使用SELECT語句的WHERE子句。

SELECT語句用來從表中檢索的資料。 WHERE子句中的工作原理類似于一個條件。它使用這個條件過濾資料，并傳回給出一個有限的結果。内置運算符和函數産生一個表達式，滿足以下條件。

文法

下面給出的是SELECT查詢的文法：

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];

示例

讓我們舉個例子SELECT ... WHERE子句。假設employee表有如下 Id, Name, Salary, Designation, 和 Dept等字段，生成一個查詢檢索超過30000薪水的員工詳細資訊。

+------+--------------+-------------+-------------------+--------+

 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  |

+------+--------------+-------------+-------------------+--------+

下面的查詢檢索使用上述業務情景的員工詳細資訊：

hive> SELECT * FROM employee WHERE salary>30000;

成功執行查詢後，能看到以下回應：

+------+--------------+-------------+-------------------+--------+

| 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     |

+------+--------------+-------------+-------------------+--------+

JDBC 程式

在JDBC程式應用，其中針對給定的例子如下子句。import java.sql.SQLException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.DriverManager;

public class HiveQLWhere {

   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";

   public static void main(String[] args) throws SQLException {

      // Register driver and create driver instance

      Class.forName(driverName);

      // get connection

      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");

      // create statement

      Statement stmt = con.createStatement();

      // execute statement

      Resultset res = stmt.executeQuery("SELECT * FROM employee WHERE salary>30000;");

      System.out.println("Result:");

      System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");

      while (res.next()) {

         System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4) + " " + res.getString(5));

      }

      con.close();

   }

}

儲存程式在一個名為HiveQLWhere.java檔案。使用下面的指令來編譯和執行這個程式。

$ javac HiveQLWhere.java

$ java HiveQLWhere

輸出：

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