presto-jdbc

[vduta]

Hi,

I've been trying to connect to presto and run some queries through jdbc, but I keep getting errors. I used "jdbc:presto://localhost:8080/" for the connection string. Is the driver functional? And if so, does it work on :8080? Do I need to also call Class.forName in order to initialize the driver?

Thanks!

[David Phillips]

The driver is currently very alpha quality. It supports the bare minimum functionality to run queries and iterate over the results, along with a few metadata calls. Basically, it handles the few things that are covered by its unit tests and nothing more.

However, we do use it in a verification program that we run before every release to compare the results of queries against production and the new code, so it does work. (We will be cleaning this up and adding it to the main Presto code base soon.)

That connection string is correct. Modern JDBC uses the ServiceLoader framework and thus drivers do not require registration (this is accomplished via the java.sql.Driver file in META-INF/services).

Can you give an example of how you are using the driver and what error you are seeing?

--
You received this message because you are subscribed to the Google Groups "Presto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to presto-users...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[vduta]

This is how I try to connect:

Connection con = DriverManager.getConnection("jdbc:presto://localhost:8080/", "", "");

And the stacktrace I get is:

java.sql.SQLException: No suitable driver found for jdbc:presto://localhost:8080/

at java.sql.DriverManager.getConnection(DriverManager.java:596)

at java.sql.DriverManager.getConnection(DriverManager.java:215)

        at com.dropbox.analytics.prestobenchmark.RunTest.runQuery(RunTest.java:45)

Also, the maven dependency looks like this (I did check the maven repository and the jars are there):

<dependency>

          <groupId>com.facebook.presto</groupId>

          <artifactId>presto-jdbc</artifactId>

          <version>0.51-SNAPSHOT</version>

</dependency>

Thank you!

[David Phillips]

That is strange. You will actually need to specify a username (any will do -- for now the server just logs it to track who is running queries), though you would get a different error message if that was the problem. The following code works for me:

    try (Connection connection = DriverManager.getConnection("jdbc:presto://localhost:8080/", "test", null);

            Statement statement = connection.createStatement();

            ResultSet rs = statement.executeQuery("SELECT node_id FROM sys.node")) {

        while (rs.next()) {

            System.out.println(rs.getString(1));

        }

    }

How are you running your program? Can you verify that the Presto JDBC driver is actually in your classpath when you run it?

You can try printing your classpath with this and looking for "presto-jdbc":

    System.out.println(Arrays.toString(((URLClassLoader) ClassLoader.getSystemClassLoader()).getURLs()));

If you're building an "uber" JAR (packing the entire application into a single JAR), make sure that META-INF/services is merged properly. The Maven shade plugin has a resource transformer that does this.

[vduta]

The classpath is correct and the jars are there, but I suspect it is a dependency problem. I believe one of the libraries I compiled it against is different than the one I'm using at runtime. If I try to do this:

Driver driver = new com.facebook.presto.jdbc.Driver();

I get

Exception in thread "main" java.lang.VerifyError: (class: org/jboss/netty/channel/socket/nio/NioClientBoss, method: processConnectTimeout signature: (Ljava/util/Set;J)V) Incompatible argument to function

at org.jboss.netty.channel.socket.nio.NioClientBossPool.newBoss(NioClientBossPool.java:61)

at org.jboss.netty.channel.socket.nio.NioClientBossPool.newBoss(NioClientBossPool.java:27)

at org.jboss.netty.channel.socket.nio.AbstractNioBossPool.init(AbstractNioBossPool.java:65)

at org.jboss.netty.channel.socket.nio.NioClientBossPool.<init>(NioClientBossPool.java:45)

at io.airlift.http.client.netty.NettyIoPool.<init>(NettyIoPool.java:64)

at io.airlift.http.client.netty.StandaloneNettyAsyncHttpClient.<init>(StandaloneNettyAsyncHttpClient.java:40)

at com.facebook.presto.jdbc.QueryExecutor.<init>(QueryExecutor.java:53)

at com.facebook.presto.jdbc.QueryExecutor.create(QueryExecutor.java:83)

at com.facebook.presto.jdbc.Driver.<init>(Driver.java:64)

at com.facebook.presto.jdbc.Driver.<clinit>(Driver.java:55)

I'll try to find out which jar is causing the problem.

[vduta]

It looks like my hadoop dependency also had an old netty dependency which was causing the problem. It works great now!

[Waldir Edison Farfan Caro]

Hi.

Please how do you make jdbc connection, and it would be possible to make queries in relational databases?

Thanks in advance.

[David Phillips]

Hi Waldir,

The JDBC driver is available as a standard Maven artifact. You can add it as a dependency to your project (replacing the version 0.52 with the most recent version):

<dependency>

    <groupId>com.facebook.presto</groupId>

    <artifactId>presto-jdbc</artifactId>

    <version>0.52</version>

</dependency>

You can connect and run a query like this:

 try (Connection connection = DriverManager.getConnection("jdbc:presto://localhost:8080/", "test", null);

            Statement statement = connection.createStatement();

            ResultSet rs = statement.executeQuery("SELECT node_id FROM sys.node")) {

        while (rs.next()) {

            System.out.println(rs.getString(1));

        }

    }

The username must be provided (the example above uses "test"), but the value can be anything.

Currently, the only available connector for Presto is for querying Hive data. Being able to query other relational databases (like MySQL or PostgreSQL) from Presto has been a popular feature request, so we expect to support that in a future release.

[Waldir Edison Farfan Caro]

Thanks David.

I have an error when i run that query on CLI:

Query 20131107_025621_00004_qbz54 failed: Too many requests to http://192.168.31.85:8080/v1/task/20131107_025621_00004_qbz54.0.0 failed: 14 failures: Time since last success 121187,92ms

I'll wait until Presto will be able to query relational databases, thanks.

[ms.a...@gmail.com]

Hi,

I tried the same. It is working fine for my program. The jdbc url what I used is "jdbc:presto://hostname:port/". But in the getConnection method I passed the jdbcurl, username and password. The port I used is the http.server.port which we are configuring in the config.properties under presto conf folder. The hostname is the one in which the presto server is running. And one configuration which I made in my program is to set the catalog in the connection object.

Connection con=DriverManager.getConnection("jdbc:presto://hostname:port/","username","password");

con.setCatalog("hive");
All others are same as the jdbc program for hive.

Thanks,
Ahalya

[haithe...@gmail.com]

Hi,
I prepared some tables with cassandra using CQL. In fact, I tried to apply aggregation queries on my data (sum, avg, etc..). So, I used presto-cassandra with java.
I've been trying to connect to presto and run some queries through jdbc, but I keep getting errors. The code is :
public class tester_cass {

public static void connect() {
try {
Class.forName("com.facebook.presto.jdbc.PrestoDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
PrestoConnection connection = null;
try {
connection = (PrestoConnection) DriverManager.getConnection("jdbc:presto://localhost:8888/cassandra/premierkeys", "test", "");
} catch (SQLException e) {
e.printStackTrace();
}
PrestoStatement statement = null;
try {
statement = (PrestoStatement) connection.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
String query = "SELECT * FROM client";
ResultSet rs = null;
try {
rs = statement.executeQuery(query);
} catch (SQLException e) {
e.printStackTrace();
}
try {
while(rs.next()) {
System.out.println(rs.toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
connect();
}

}

But when I run this code I get:

java.sql.SQLException: Error executing query
at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:54)
at tester_cass.connect(tester_cass.java:42)
at tester_cass.main(tester_cass.java:58)
Caused by: java.lang.IllegalStateException: Response does not contain a JSON value
at com.facebook.presto.jdbc.internal.airlift.http.client.FullJsonResponseHandler$JsonResponse.getValue(FullJsonResponseHandler.java:148)
at com.facebook.presto.jdbc.internal.client.StatementClient.<init>(StatementClient.java:82)
at com.facebook.presto.jdbc.QueryExecutor.startQuery(QueryExecutor.java:60)
at com.facebook.presto.jdbc.PrestoConnection.startQuery(PrestoConnection.java:541)
at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:49)
... 2 more
Caused by: java.lang.IllegalArgumentException: Unable to create class com.facebook.presto.jdbc.internal.client.QueryResults from JSON response:
{"message": "Resource not found.", "type": "NoSuchResource", "brief": "error"}
at com.facebook.presto.jdbc.internal.airlift.http.client.FullJsonResponseHandler$JsonResponse.<init>(FullJsonResponseHandler.java:109)
at com.facebook.presto.jdbc.internal.airlift.http.client.FullJsonResponseHandler.handle(FullJsonResponseHandler.java:65)
at com.facebook.presto.jdbc.internal.airlift.http.client.FullJsonResponseHandler.handle(FullJsonResponseHandler.java:33)
at com.facebook.presto.jdbc.internal.airlift.http.client.jetty.JettyHttpClient.execute(JettyHttpClient.java:224)
... 6 more
Caused by: java.lang.IllegalArgumentException: Invalid [simple type, class com.facebook.presto.jdbc.internal.client.QueryResults] json bytes
at com.facebook.presto.jdbc.internal.airlift.json.JsonCodec.fromJson(JsonCodec.java:174)
at com.facebook.presto.jdbc.internal.airlift.http.client.FullJsonResponseHandler$JsonResponse.<init>(FullJsonResponseHandler.java:106)
... 9 more
Caused by: com.facebook.presto.jdbc.internal.jackson.databind.JsonMappingException: Instantiation of [simple type, class com.facebook.presto.jdbc.internal.client.QueryResults] value failed: id is null
at com.facebook.presto.jdbc.internal.jackson.databind.deser.std.StdValueInstantiator.wrapException(StdValueInstantiator.java:434)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.std.StdValueInstantiator.createFromObjectWith(StdValueInstantiator.java:243)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.impl.PropertyBasedCreator.build(PropertyBasedCreator.java:158)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.BeanDeserializer._deserializeUsingPropertyBased(BeanDeserializer.java:614)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.BeanDeserializer.deserializeFromObjectUsingNonDefault(BeanDeserializer.java:393)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.BeanDeserializer.deserializeFromObject(BeanDeserializer.java:289)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:121)
at com.facebook.presto.jdbc.internal.jackson.databind.ObjectMapper._readMapAndClose(ObjectMapper.java:2793)
at com.facebook.presto.jdbc.internal.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:2044)
at com.facebook.presto.jdbc.internal.airlift.json.JsonCodec.fromJson(JsonCodec.java:171)
... 10 more
Caused by: java.lang.NullPointerException: id is null
at com.facebook.presto.jdbc.internal.guava.base.Preconditions.checkNotNull(Preconditions.java:229)
at com.facebook.presto.jdbc.internal.client.QueryResults.<init>(QueryResults.java:70)
at com.facebook.presto.jdbc.internal.client.QueryResults.<init>(QueryResults.java:57)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.facebook.presto.jdbc.internal.jackson.databind.introspect.AnnotatedConstructor.call(AnnotatedConstructor.java:125)
at com.facebook.presto.jdbc.internal.jackson.databind.deser.std.StdValueInstantiator.createFromObjectWith(StdValueInstantiator.java:239)
... 18 more
Exception in thread "main" java.lang.NullPointerException
at tester_cass.connect(tester_cass.java:48)
at tester_cass.main(tester_cass.java:58)

what is the problem ?

[Dain Sundstrom]

The root cause is that when the JDBC driver sends the initial query, the server is responding with:

  

  {"message": "Resource not found.", "type": "NoSuchResource", "brief": "error"} 

I would guess the means that the JDBC driver is sending the request to the wrong URL, or that there is a non-presto server running at localhost:8888.  To debug this, I would first make sure the Presto cli can communicate with the server.  Assuming that works, I would use a debugger and place a break point at:

  com.facebook.presto.jdbc.internal.client.StatementClient.<init>(StatementClient.java:82)

When the debugger stops, inspect the "request" to object to see the actual URL you are connection to.  You should be able to verify the is the same URL the CLI is using, but looking a the Presto http logs.

-dain