IntelliJ에서 Presto Query 날리기

0. 윈도우 hosts파일 작성

C:\Windows\System32\drivers\etc\hosts

192.168.5.183 dss03.nexr.com

 

인증서 서명이 dss03.nexr.com 로 되어있기 때문에 클라이언트도 dss03.nexr.com로 요청해야 인증에 성공한다.

 

 

1. presto-jdbc 드라이버 다운로드

https://prestodb.io/download.html

 

2. intelliJ에 presto-jdbc 드라이버 추가

 

3. 인증서 파일 다운로드 (ndap-truststore.jks)

presto server에 있는 인증서를 PC에 다운로드 받았다.

나중에 API로 호출하면 따로 다운로드 받지 않아도 브라우저 통해서 인증서를 받겠지?

 

4. 코드 작성 (https)

package com.company;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) {
	// write your code here
        //Connect to Presto server using Presto JDBC

        final String JDBC_DRIVER = "com.facebook.presto.jdbc.PrestoDriver";
        final String DB_URL = "jdbc:presto://dss03.nexr.com:29443/hive/test?SSL=true&SSLTrustStorePath=C:/Users/sunny/Downloads/ndap-truststore.jks";
        //  Database credentials
        final String USER = "admin";
        final String PASS = "admin";
        Connection conn = null;
        Statement stmt = null;
        
        try {
            //Register JDBC driver
            Class.forName(JDBC_DRIVER);
            //Open a connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            //Execute a query
            stmt = conn.createStatement();
            String sql = "show tables";
            ResultSet res = stmt.executeQuery(sql);
            //Extract data from result set
            while (res.next()) {
                //Retrieve by column name
                String name = res.getString("Table");
                //Display values
                System.out.println("Table : " + name);
            }
            //Clean-up environment
            res.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

 

4. 코드 작성 (http)

package com.company;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) {
	// write your code here
        //Connect to Presto server using Presto JDBC

        final String JDBC_DRIVER = "com.facebook.presto.jdbc.PrestoDriver";
        //final String DB_URL = "jdbc:presto://dss03.nexr.com:29443/hive/test?SSL=true&SSLTrustStorePath=C:/Users/sunny/Downloads/ndap-truststore.jks";
        final String DB_URL = "jdbc:presto://dss03.nexr.com:29080/hive/test?SSL=false";
        //  Database credentials
        //final String USER = "admin";
        final String USER = "admin";
        //final String PASS = "admin";
        final String PASS = null;
        Connection conn = null;
        Statement stmt = null;
        try {
            //Register JDBC driver
            Class.forName(JDBC_DRIVER);
            //Open a connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //Execute a query
            stmt = conn.createStatement();
            String sql = "SELECT * FROM test_0507";
            ResultSet res = stmt.executeQuery(sql);
            //Extract data from result set
            while (res.next()) {
                //Retrieve by column name
                String name = res.getString("c1");
                //Display values
                System.out.println("c1 : " + name);
            }
            //Clean-up environment
            res.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

USER는 반드시 입력해야 하네. SSL=false 옵션을 넣어야 함.

 

 

참고사항

presto 클러스터는 NDAP이라는 제품을 사용하여 구성하였음. 따라서 NDAP 자체 Authentication 로직을 탈 것임.

 

참고링크

https://blog.knoldus.com/connecting-presto-server-using-jdbc/