第28章 HIVEQL
HIVEQL(HIVEQuery Language:Hive查询语言)是一种查询语言,该语言为Hive处理并分析Metastore的结构数据。
28.1查询语句(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];
28.1.1查询语句实例
根据emp表,生成一个查询来检索薪资大于30000的用户详细信息。执行如下查询检索薪资大于30000的员工详细信息:SELECT * FROM emp WHEREsalary>30000; 执行语句成功后,会获取如下:
28.1.2JDBC查询语句实例
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:9000/hive", "", "");
// create statement
Statement stmt = con.createStatement();
// execute statement
Resultset res = stmt.executeQuery("SELECT * FROM emp 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.get
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:9000/hive", "", "");
// create statement
Statement stmt = con.createStatement();
// execute statement
Resultset res = stmt.executeQuery("SELECT * FROM emp 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.get