You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Marc Prud’hommeaux <mp...@apache.org> on 2014/07/04 21:16:36 UTC
Where to free connection resources?
Optiq Folk-
I'm writing an Optiq adapter, modeled on the excellent optiq-csv sample. I'm wondering when and where I should close the connection to the underlying datasource? The connection is being created in my AbstractSchema subclass, but there doesn't appear to be any close() or disconnect() method in there for me to override.
-Marc
Re: Where to free connection resources?
Posted by Marc Prud’hommeaux <mp...@apache.org>.
Thanks for the insights. I went ahead and created my own net.hydromatic.optiq.jdbc.Driver and net.hydromatic.avatica.Handler implementations in order to have complete control over my connection management. I wasn't really using the Json model stuff anyway, I had just been using it since that was what the CSV example used.
-Marc
On Jul 6, 2014, at 5:18 PM, Julian Hyde <ju...@hydromatic.net> wrote:
> It is by design that a schema does not reference an Optiq connection. We wanted schemas to be re-usable across connections, so we made it difficult to connections to leak into them. A schema is typically created before the first connection.
>
> There's nothing wrong with your schema containing a reference to your connection. Create a connection to your backend in your schema's constructor, based on credentials passed in from SchemaFactory.create, and store it as a field of your schema.
>
> This is exactly how MongoSchema does it. Note that MongoTable references neither a schema nor a connection, but when a scan is started it creates a MongoQueryable, which has both a QueryProvider (which you can down-cast to an OptiqConnection) and a SchemaPlus (from which you can access your schema, via unwrap).
>
> Julian
>
Re: Where to free connection resources?
Posted by Julian Hyde <ju...@hydromatic.net>.
It is by design that a schema does not reference an Optiq connection. We wanted schemas to be re-usable across connections, so we made it difficult to connections to leak into them. A schema is typically created before the first connection.
There's nothing wrong with your schema containing a reference to your connection. Create a connection to your backend in your schema's constructor, based on credentials passed in from SchemaFactory.create, and store it as a field of your schema.
This is exactly how MongoSchema does it. Note that MongoTable references neither a schema nor a connection, but when a scan is started it creates a MongoQueryable, which has both a QueryProvider (which you can down-cast to an OptiqConnection) and a SchemaPlus (from which you can access your schema, via unwrap).
Julian
Re: Where to free connection resources?
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Julian-
I could store my connection for re-use in OptiqConnection.properties
(despite the java,util.Properties javadoc "strongly discouraging" storing
non-String values), but that still doesn't help me get access to the
connection from my SchemaFactory implementation, since there doesn't appear
to be any way to access the current OptiqConnection from my
SchemaFactory.create() or Schema.getTableMap() implementations. None of the
parameters to those methods offer me any path to get to the
OptiqConnection, as far as I can tell.
-Marc
On Sun, Jul 6, 2014 at 12:21 AM, Julian Hyde <ju...@hydromatic.net> wrote:
> Try storing your connection in OptiqConnection.properties. Note that when
> you are passed a QueryProvider, it is usually safe to cast it to an
> OptiqConnection. YMMV.
>
> It's not essential, but if your connections have a similar lifecycle to
> JDBC connections (i.e. expensive to create, may be re-used between
> statements, but may not be safe to use by more than one thread at a time),
> consider using a connection pool. Store your connection in a connection
> pool, add a wrapper whose close() method returns the connection to the
> pool, and make sure the client (i.e. the Optiq schema) calls close() when
> it's done. The connection pool will probably only need one connection if
> the concurrency is low.
>
> Julian
>
>
> On Jul 5, 2014, at 9:09 PM, Marc Prud’hommeaux <mp...@apache.org>
> wrote:
>
> > Julian-
> >
> > My connection process is expensive, so I would like to keep the same
> connection open for the lifespan of the JDBC Connection (i.e., I want to
> use the same connection for schema querying as well as table enumeration).
> However, SchemaFactory.create(SchemaPlus, String, Map) doesn't seem to give
> me any access path to an OptiqConnection. How can I use the same connection
> for the duration of the JDBC connection without storing it in my Schema
> implementation? I notice that the mongodb driver holds onto the DB
> connection in its own MongoSchema subclass.
> >
> > And yes, I am the original author of sqlline (as well as jline). I'm
> delighted that you find it useful!
> >
> > -Marc
> >
> > On Jul 4, 2014, at 5:55 PM, Julian Hyde <ju...@hydromatic.net> wrote:
> >
> >> If you're working on a recent version of Optiq, schemas exist outside
> of connections. None of the objects in the schema (e.g. tables) should have
> a reference to an open connection. When you want to get data from these
> tables, an Optiq connection will be passed in. You can store the connection
> to the underlying system in the Optiq connection, or can create a
> connection for the duration of the scan, and make sure that you close it
> when the scan completes.
> >>
> >> See for example s.asQueryable. The Enumerator is from
> ResultSetEnumerable, which takes a DataSource as an argument, creates a
> java.sql.Connection, and calls java.sql.Connection.close() from
> ResultSetEnumerator.close().
> >>
> >> While you are initializing a schema, you will probably create a
> short-lived connection, populate metadata, and then close the connection.
> JdbcSchema.computeTables is an example of this. The metadata objects (in
> this case instances of JdbcTable) do not keep a reference to that
> connection.
> >>
> >> Hope that helps.
> >>
> >> By the way, I presume you are the same Marc Prud'hommeaux who started
> the sqlline project many years ago. Thank you! We are still enjoying (and
> improving) that project.
> >>
> >> Julian
> >>
> >> On Jul 4, 2014, at 12:16 PM, Marc Prud’hommeaux <mp...@apache.org>
> wrote:
> >>
> >>> Optiq Folk-
> >>>
> >>> I'm writing an Optiq adapter, modeled on the excellent optiq-csv
> sample. I'm wondering when and where I should close the connection to the
> underlying datasource? The connection is being created in my AbstractSchema
> subclass, but there doesn't appear to be any close() or disconnect() method
> in there for me to override.
> >>>
> >>> -Marc
> >>>
> >>
> >
>
>
Re: Where to free connection resources?
Posted by Julian Hyde <ju...@hydromatic.net>.
Try storing your connection in OptiqConnection.properties. Note that when you are passed a QueryProvider, it is usually safe to cast it to an OptiqConnection. YMMV.
It's not essential, but if your connections have a similar lifecycle to JDBC connections (i.e. expensive to create, may be re-used between statements, but may not be safe to use by more than one thread at a time), consider using a connection pool. Store your connection in a connection pool, add a wrapper whose close() method returns the connection to the pool, and make sure the client (i.e. the Optiq schema) calls close() when it's done. The connection pool will probably only need one connection if the concurrency is low.
Julian
On Jul 5, 2014, at 9:09 PM, Marc Prud’hommeaux <mp...@apache.org> wrote:
> Julian-
>
> My connection process is expensive, so I would like to keep the same connection open for the lifespan of the JDBC Connection (i.e., I want to use the same connection for schema querying as well as table enumeration). However, SchemaFactory.create(SchemaPlus, String, Map) doesn't seem to give me any access path to an OptiqConnection. How can I use the same connection for the duration of the JDBC connection without storing it in my Schema implementation? I notice that the mongodb driver holds onto the DB connection in its own MongoSchema subclass.
>
> And yes, I am the original author of sqlline (as well as jline). I'm delighted that you find it useful!
>
> -Marc
>
> On Jul 4, 2014, at 5:55 PM, Julian Hyde <ju...@hydromatic.net> wrote:
>
>> If you're working on a recent version of Optiq, schemas exist outside of connections. None of the objects in the schema (e.g. tables) should have a reference to an open connection. When you want to get data from these tables, an Optiq connection will be passed in. You can store the connection to the underlying system in the Optiq connection, or can create a connection for the duration of the scan, and make sure that you close it when the scan completes.
>>
>> See for example s.asQueryable. The Enumerator is from ResultSetEnumerable, which takes a DataSource as an argument, creates a java.sql.Connection, and calls java.sql.Connection.close() from ResultSetEnumerator.close().
>>
>> While you are initializing a schema, you will probably create a short-lived connection, populate metadata, and then close the connection. JdbcSchema.computeTables is an example of this. The metadata objects (in this case instances of JdbcTable) do not keep a reference to that connection.
>>
>> Hope that helps.
>>
>> By the way, I presume you are the same Marc Prud'hommeaux who started the sqlline project many years ago. Thank you! We are still enjoying (and improving) that project.
>>
>> Julian
>>
>> On Jul 4, 2014, at 12:16 PM, Marc Prud’hommeaux <mp...@apache.org> wrote:
>>
>>> Optiq Folk-
>>>
>>> I'm writing an Optiq adapter, modeled on the excellent optiq-csv sample. I'm wondering when and where I should close the connection to the underlying datasource? The connection is being created in my AbstractSchema subclass, but there doesn't appear to be any close() or disconnect() method in there for me to override.
>>>
>>> -Marc
>>>
>>
>
Re: Where to free connection resources?
Posted by Marc Prud’hommeaux <mp...@apache.org>.
Julian-
My connection process is expensive, so I would like to keep the same connection open for the lifespan of the JDBC Connection (i.e., I want to use the same connection for schema querying as well as table enumeration). However, SchemaFactory.create(SchemaPlus, String, Map) doesn't seem to give me any access path to an OptiqConnection. How can I use the same connection for the duration of the JDBC connection without storing it in my Schema implementation? I notice that the mongodb driver holds onto the DB connection in its own MongoSchema subclass.
And yes, I am the original author of sqlline (as well as jline). I'm delighted that you find it useful!
-Marc
On Jul 4, 2014, at 5:55 PM, Julian Hyde <ju...@hydromatic.net> wrote:
> If you're working on a recent version of Optiq, schemas exist outside of connections. None of the objects in the schema (e.g. tables) should have a reference to an open connection. When you want to get data from these tables, an Optiq connection will be passed in. You can store the connection to the underlying system in the Optiq connection, or can create a connection for the duration of the scan, and make sure that you close it when the scan completes.
>
> See for example s.asQueryable. The Enumerator is from ResultSetEnumerable, which takes a DataSource as an argument, creates a java.sql.Connection, and calls java.sql.Connection.close() from ResultSetEnumerator.close().
>
> While you are initializing a schema, you will probably create a short-lived connection, populate metadata, and then close the connection. JdbcSchema.computeTables is an example of this. The metadata objects (in this case instances of JdbcTable) do not keep a reference to that connection.
>
> Hope that helps.
>
> By the way, I presume you are the same Marc Prud'hommeaux who started the sqlline project many years ago. Thank you! We are still enjoying (and improving) that project.
>
> Julian
>
> On Jul 4, 2014, at 12:16 PM, Marc Prud’hommeaux <mp...@apache.org> wrote:
>
>> Optiq Folk-
>>
>> I'm writing an Optiq adapter, modeled on the excellent optiq-csv sample. I'm wondering when and where I should close the connection to the underlying datasource? The connection is being created in my AbstractSchema subclass, but there doesn't appear to be any close() or disconnect() method in there for me to override.
>>
>> -Marc
>>
>
Re: Where to free connection resources?
Posted by Julian Hyde <ju...@hydromatic.net>.
If you're working on a recent version of Optiq, schemas exist outside of connections. None of the objects in the schema (e.g. tables) should have a reference to an open connection. When you want to get data from these tables, an Optiq connection will be passed in. You can store the connection to the underlying system in the Optiq connection, or can create a connection for the duration of the scan, and make sure that you close it when the scan completes.
See for example JdbcTable.asQueryable. The Enumerator is from ResultSetEnumerable, which takes a DataSource as an argument, creates a java.sql.Connection, and calls java.sql.Connection.close() from ResultSetEnumerator.close().
While you are initializing a schema, you will probably create a short-lived connection, populate metadata, and then close the connection. JdbcSchema.computeTables is an example of this. The metadata objects (in this case instances of JdbcTable) do not keep a reference to that connection.
Hope that helps.
By the way, I presume you are the same Marc Prud'hommeaux who started the sqlline project many years ago. Thank you! We are still enjoying (and improving) that project.
Julian
On Jul 4, 2014, at 12:16 PM, Marc Prud’hommeaux <mp...@apache.org> wrote:
> Optiq Folk-
>
> I'm writing an Optiq adapter, modeled on the excellent optiq-csv sample. I'm wondering when and where I should close the connection to the underlying datasource? The connection is being created in my AbstractSchema subclass, but there doesn't appear to be any close() or disconnect() method in there for me to override.
>
> -Marc
>