PHP 连接 Hive 执行 SQL 查询

Hive 中的 Thrift 脚本有两个版本的，一个有命名空间，一个没有命名空间，下面使用的是没有命名空间的版本。

无命名空间的PEAR

$ cd /opt/hive/lib/php/packages/
$ mv hive_service hive_service.bak
$ mv hive_service.bak/hive_service ./
$ cd /opt/hive/lib/php/packages/queryplan/queryplan
$ cp queryplan_types.php ../

$ cd /opt/hive/lib/php/packages/hive_metastore/hive_metastore
$ cp ThriftHiveMetastore.php ../

PHP

<?php
$GLOBALS['THRIFT_ROOT'] = '/opt/hive/lib/php';

require_once '/opt/hive/lib/php/autoload.php';
require_once '/opt/hive/lib/php/Thrift.php';
require_once '/opt/hive/lib/php/packages/fb303/FacebookService.php';
require_once '/opt/hive/lib/php/transport/TSocket.php';
require_once '/opt/hive/lib/php/protocol/TBinaryProtocol.php';
require_once '/opt/hive/lib/php/packages/hive_service/ThriftHive.php';

/**
 * @author chenliujin <liujin.chen@qq.com>
 * @since 2013-08-20
 */
class Hive
{
    /**
     * @author chenliujin <liujin.chen@qq.com>
     * @since 2013-08-20
     */
    public function query($sql)
    {

        try {
            // Set up the transport/protocol/client
            $transport = new TSocket('localhost', 10000);
            $transport->setSendTimeout(10000);
            $transport->setRecvTimeout(100000);
            $protocol = new TBinaryProtocol($transport);
            $client = new ThriftHiveClient($protocol);
            $transport->open();

            // run queries, metadata calls etc
            $client->execute($sql);
            $result = $client->fetchAll();

            $data = array();
            foreach ($result as $row) {
                $row = explode("\t", $row);
                $data[] = $row;
            }
            $transport->close();

            return $data;
        } catch (Exception $e) {
            return array();
        }
    }
}

FAQ

PHP Fatal error:  Uncaught exception 'HiveServerException' with message 'Query returned non-zero code: 40000, cause: FAILED: ParseException line 5:23 cannot recognize input near 'ab' ';' '<EOF>' in expression specification' in /opt/hive/lib/php/packages/hive_service/ThriftHive.php:618

When you write a query in the shell, you should use ';' at the end of the SQL statement, However, you CANNOT use ';' at the end of the SQL statement when you're using Java.

TSocket: timed out reading 4 bytes from localhost:10000

加大超时时间

$transport->setSendTimeout(1000000);
$transport->setRecvTimeout(100000000);