多语言连接hive（Java、python、ruby）

向右看齐 2023-07-10 12:51 22阅读 0赞

**Table of Contents**

JDBC

Using JDBC

Python Client

Ruby Client

Connecting to Hiveserver and Hiveserver2

Hiveserver

Hiveserver2

--------------------

# JDBC #

HiveServer2 有一个 JDBC 驱动程序。它支持对 HiveServer2 的嵌入式和远程访问。建议将 Remote HiveServer2 模式用于生产环境，因为它更安全，而且不需要为用户授予直接的 HDFS/metastore 访问权限。

## Using JDBC ##

可以使用 JDBC 访问存储在关系数据库或其他表格格式中的数据。

1、加载 HiveServer2 JDBC 驱动程序。从1.2.0开始，应用程序不再需要使用 Class.forName()显式加载JDBC驱动程序。

`Class.forName("org.apache.hive.jdbc.HiveDriver");`

2、通过使用JDBC驱动程序创建连接对象来连接数据库。

Connection cnct = DriverManager.getConnection("jdbc:hive2://<host>:<port>", "<user>", "<password>");

默认<端口>是10000。在非安全配置中，为要作为的查询指定一个<user>。<password>字段值在非安全模式下被忽略。

Connection cnct = DriverManager.getConnection("jdbc:hive2://<host>:<port>", "<user>", "");

在Kerberos安全模式中，用户信息基于Kerberos凭证。

3、通过创建一个语句对象并使用它的executeQuery()方法向数据库提交SQL。

Statement stmt = cnct.createStatement();
    ResultSet rset = stmt.executeQuery("SELECT foo FROM bar");

4、必要时处理结果集。

**JDBC Client Sample Code**

import java.sql.SQLException;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.sql.DriverManager;
     
    public class HiveJdbcClient {
      private static String driverName = "org.apache.hive.jdbc.HiveDriver";
     
      /**
       * @param args
       * @throws SQLException
       */
      public static void main(String[] args) throws SQLException {
          try {
          Class.forName(driverName);
        } catch (ClassNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
          System.exit(1);
        }
        //replace "hive" here with the name of the user the queries should run as
        Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hive", "");
        Statement stmt = con.createStatement();
        String tableName = "testHiveDriverTable";
        stmt.execute("drop table if exists " + tableName);
        stmt.execute("create table " + tableName + " (key int, value string)");
        // show tables
        String sql = "show tables '" + tableName + "'";
        System.out.println("Running: " + sql);
        ResultSet res = stmt.executeQuery(sql);
        if (res.next()) {
          System.out.println(res.getString(1));
        }
           // describe table
        sql = "describe " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
          System.out.println(res.getString(1) + "\t" + res.getString(2));
        }
     
        // load data into table
        // NOTE: filepath has to be local to the hive server
        // NOTE: /tmp/a.txt is a ctrl-A separated file with two fields per line
        String filepath = "/tmp/a.txt";
        sql = "load data local inpath '" + filepath + "' into table " + tableName;
        System.out.println("Running: " + sql);
        stmt.execute(sql);
     
        // select * query
        sql = "select * from " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
          System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2));
        }
     
        // regular hive query
        sql = "select count(1) from " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
          System.out.println(res.getString(1));
        }
      }
    }

原文：[https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients\#HiveServer2Clients-UsingJDBC][https_cwiki.apache.org_confluence_display_Hive_HiveServer2_Clients_HiveServer2Clients-UsingJDBC]

# Python Client #

import pyhs2
     
    with pyhs2.connect(host='localhost',
                       port=10000,
                       authMechanism="PLAIN",
                       user='root',
                       password='test',
                       database='default') as conn:
        with conn.cursor() as cur:
            #Show databases
            print cur.getDatabases()
     
            #Execute query
            cur.execute("select * from table")
      
            #Return column info from query
            print cur.getSchema()
     
            #Fetch table results
            for i in cur.fetch():
                print i

** DB-API (asynchronous)**

from pyhive import hive
    from TCLIService.ttypes import TOperationState
    cursor = hive.connect('localhost').cursor()
    cursor.execute('SELECT * FROM my_awesome_data LIMIT 10', async=True)
    
    status = cursor.poll().operationState
    while status in (TOperationState.INITIALIZED_STATE, TOperationState.RUNNING_STATE):
        logs = cursor.fetch_logs()
        for message in logs:
            print message
    
        # If needed, an asynchronous query can be cancelled at any time with:
        # cursor.cancel()
    
        status = cursor.poll().operationState
    
    print cursor.fetchall()

原文：[https://cwiki.apache.org/confluence/display/Hive/Setting+Up+HiveServer2\#SettingUpHiveServer2-PythonClientDriver][https_cwiki.apache.org_confluence_display_Hive_Setting_Up_HiveServer2_SettingUpHiveServer2-PythonClientDriver]

[https://github.com/dropbox/PyHive][https_github.com_dropbox_PyHive]

# Ruby Client #

## Connecting to Hiveserver and Hiveserver2 ##

### Hiveserver ###

RBHive.connect('hive.server.address', 10_000) do |connection|
      connection.fetch 'SELECT city, country FROM cities'
    end 
    ➔ [{:city => "London", :country => "UK"}, {:city => "Mumbai", :country => "India"}, {:city => "New York", :country => "USA"}]

### Hiveserver2 ###

Hiveserver2对于如何运行有几个选项。连接代码接受一个哈希表，包含以下可能的参数：

*  `:transport` \- `buffered` (BufferedTransport), `:http` (HTTPClientTransport), or `:sasl` (SaslClientTransport) 其中一个
 *  `:hive_version` \- hive版本号
 *  `:timeout` \- 如果使用BufferedTransport或SaslClientTransport，这是套接字上的超时时间
 *  `:sasl_params` \- 如果使用SaslClientTransport，这是设置SASL连接的参数散列

如果您传递一个空的散列或nil来代替选项(或不提供它们)，则尝试将Hive版本设置为0.10，使用:buffered作为传输，超时时间为1800秒。

**Connecting with the defaults**

RBHive.tcli_connect('hive.server.address', 10_000) do |connection|
      connection.fetch('SHOW TABLES')
    end

原文：[https://github.com/forward3d/rbhive][https_github.com_forward3d_rbhive]

[https_cwiki.apache.org_confluence_display_Hive_HiveServer2_Clients_HiveServer2Clients-UsingJDBC]: https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-UsingJDBC
[https_cwiki.apache.org_confluence_display_Hive_Setting_Up_HiveServer2_SettingUpHiveServer2-PythonClientDriver]: https://cwiki.apache.org/confluence/display/Hive/Setting+Up+HiveServer2#SettingUpHiveServer2-PythonClientDriver
[https_github.com_dropbox_PyHive]: https://github.com/dropbox/PyHive
[https_github.com_forward3d_rbhive]: https://github.com/forward3d/rbhive