HiveQL Select Where

广告位

Hive查询语言(HiveQL)是一种查询语言,Hive处理在Metastore分析结构化数据。本章介绍了如何…

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

关于作者: 大数据教程学习

为您推荐