Hive查询

1、Hive的查询与SQL类似，基本语法如下：

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

查询emp表中，salary>45000的员工信息：

    SELECT * FROM employee WHERE salary>45000;

执行结果如下：

2、通过python访问hive，进行数据查询，代码如下：

    # coding:utf-8
    from pyhive import hive
    from TCLIService.ttypes import TOperationState

    # 打开hive连接
    hiveConn = hive.connect(host='192.168.83.135',port=11111,username='hadoop',database='userdbbypy')
    cursor = hiveConn.cursor()

    sql = ''' SELECT * FROM emp WHERE salary>45000 '''
    cursor.execute(sql, async=True)

    # 得到执行语句的状态
    status = cursor.poll().operationState
    print "status:",status
    for eid,ename,salary,destination,dept, in cursor.fetchall():
        print eid,ename,salary,destination,dept

    # 关闭hive连接
    cursor.close()
    hiveConn.close()

执行代码，结果如下：

3、Order By查询

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

查询emp表，并将salary按照由高到低排序：

    SELECT * FROM emp ORDER BY salary;

中间出了很多执行过程，不明所以。。
4、Group By查询
统计emp中工资一样的数量：

    SELECT count(*),salary from emp GROUP BY salary;