Presto-[6]-JDBC Driver 简介与Demo测试

官方文档

2.3. JDBC Driver

1-JDBC jar 或maven 

Presto支持JDBC driver访问，下载 presto-jdbc-0.191.jar，添加到应用程序的 class path 中，同时也可以通过Maven中心仓库连接：

<dependency>    <groupId>com.facebook.presto</groupId>    <artifactId>presto-jdbc</artifactId>    <version>0.191</version></dependency>

2-Connecting

支持以下URL形式：

jdbc:presto://host:portjdbc:presto://host:port/catalog（你的分类）jdbc:presto://host:port/catalog（你的分类）/schema（你的schema）

例如，用以下URL访问运行于 example.net port 8080 的Presto的catalog hive、 schema sales: 

jdbc:presto://example.net:8080/hive/sales

以上URL可以像下面的代码一样构建连接connection:

String url = "jdbc:presto://example.net:8080/hive/sales";Connection connection = DriverManager.getConnection(url, "test", null);

 3-Connection Parameters

driver支持多项参数

• 作为URL的参数

• 以properties传给DriverManager

• 本地安装的presto没有设置密码，不用设置properties
• 若通过usr和password则必须设置SSL

以下两种形式等价:

// URL parametersString url = "jdbc:presto://example.net:8080/hive/sales";Properties properties = new Properties();properties.setProperty("user", "test");properties.setProperty("password", "secret");properties.setProperty("SSL", "true");Connection connection = DriverManager.getConnection(url, properties);// propertiesString url = "jdbc:presto://example.net:8080/hive/sales?user=test&password=secret&SSL=true";Connection connection = DriverManager.getConnection(url);

这些methods可能mixed，一些参数特定用在URL，一些特定用于properties，还有一些参数两种方法都可以使用

4-Parameter Reference

Name

Description

user

Username用于身份验证与授权

password

LDAP 身份验证密码

socksProxy

SOCKS proxy host、port，例如：localhost:1080

httpProxy

HTTP proxy host、port，例如：localhost:8888

SSL

用于HTTPS的connections

SSLKeyStorePath

Java KeyStore 文件location，Java KeyStore 包含certificate ，提供授权使用的key

 

SSLKeyStorePassword

KeyStore的password

SSLTrustStorePath

Java TrustStore文件的location，Java TrustStore用于HTTPS server授权

 

SSLTrustStorePasswordTrustStore. passwordKerberosRemoteServiceName

Presto coordinator Kerberos service name.该参数用于Kerberos 验证

KerberosPrincipal

principal 在Presto coordinator 验证时使用

KerberosUseCanonicalHostname

Use the canonical hostname of the Presto coordinator for the Kerberos service principal by first resolving the hostname to an IP address

and then doing a reverse DNS lookup for that IP address.

This is enabled by default.

KerberosConfigPathKerberos configuration file.KerberosKeytabPathKerberos keytab file.KerberosCredentialCachePathKerberos credential cache.

 5-Demo测试

启动hadoop、Presto、启动hive-meta和hive-server2 、运行MainApp 输出如下：

代码示例

package com.learn; import java.sql.*;import java.util.Properties; public class MainApp {  public static void main(String[] args) throws SQLException {//    // properties//    String url = "jdbc:presto://localhost:8080/hive/hive_test?user=hjw";//    Connection connection = DriverManager.getConnection(url);     // URL parameters    String url = "jdbc:presto://localhost:8080/hive/hive_test";    //这里本地安装的presto没有设置密码，不用设置properties    //若通过usr和password则必须设置SSL    //Properties properties = new Properties();    //properties.setProperty("user", "root");    //properties.setProperty("password", "root");    //properties.setProperty("SSL", "true");    Connection connection = null;    try {      connection = DriverManager.getConnection(url, "hjw", null);      Statement stmt = connection.createStatement();       String sql = "select * from student";      //executeQuery会返回结果的集合，否则返回空值      ResultSet rs = stmt.executeQuery(sql);      System.out.println("学生id\t姓名");      while (rs.next()) {        // 如果返回的是int类型可以用getInt()        System.out.println(rs.getString(1) + "\t" + rs.getString(2));      }    } catch (SQLException e) {      e.printStackTrace();    } finally {      connection.close();    }  }}//查询结果//学生id  姓名//1001  zhangsan//1002  lisi