Currently, I'm unable to access objects in other schemas. In a PostgreSQL
database in Neon, I have a sample from their postgres_air
<https://neon.tech/docs/import/import-sample-data#postgres-air-database> sample
data set. The database has the public schema, but it also has a
postgres_air schema, the latter of which has the actual sample tables. The
public schema is largely empty, though I did create a simple person table
for testing purposes. In Java, with a connection to PostgreSQL via the
PostgreSQL JDBC driver, these work:
- ✅ "select * from person"
- ✅ "select * from public.person"
- ✅ "select * from \"public\".\"person\""
- ✅ "select * from postgres_air.person"
- ✅ "select * from \"postgres_air\".\"aircraft\""
With a connection to PostgreSQL via the Calcite driver, it runs into a
little trouble:
- ✅ "select * from person"
- ❌ "select * from public.person"
- ❌ "select * from \"public\".\"person\""
- ❌ "select * from postgres_air.aircraft"
- ❌ "select * from \"postgres_air\".\"aircraft\""
I guess it's safe to say that I don't know how to query objects (tables,
views, etc.) using a fully-qualified name that includes the schema, and
that includes knowing how to quote the objects. ¯\_(ツ)_/¯
Sorry to be a trouble, but are there any clues about how to surmount this
obstacle? Thanks so much.
Best,
David
Trying to find
On Wed, Aug 21, 2024 at 3:46 PM David Ventimiglia <
davidaventimig...@hasura.io> wrote:
> Hey Stamatis,
>
> Thanks! That helped. I tried Gavin's script to download the Jar files,
> but it turns out it wasn't entirely necessary. I don't know about SQirreL,
> but DBeaver accepts Maven dependencies and will download the dependencies
> transitively. DBeaver ships with an "Apache Calcite" connection template,
> but it only has the org.apache.calcite.avatica.:avatica-core artifact. It
> doesn't have the org.apache.calcite:calcite-core artifact. I added that
> and asked DBeaver then to "Download/Update" its dependencies, at which
> point it retrieved all of the Jar files that Gavin's script did. I also had
> to change the Driver class from org.apache.calcite.avatica.remote.Driver to
> org.apache.calcite.jdbc.Driver, and of course I had to add the artifact for
> the PostgreSQL JDBC driver as well.
>
> With that, I was able to connect to a PostgreSQL database through Apache
> Calcite, via DBeaver. Unfortunately, I haven't been able to access any
> tables or views, as I haven't yet coaxed Calcite into making them
> available. Perhaps the trouble lies in the model.json file (shown below):
>
> {
> version: "1.0",
> defaultSchema: "postgres_air",
> schemas: [
> {
> name: "postgres_air",
> type: "custom",
> factory: "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
> operand: {
> jdbcDriver: "org.postgresql.Driver",
> jdbcUrl:
> "jdbc:postgresql://<redacted>/postgres_air?sslmode=require",
> jdbcUser: "<redacted>",
> jdbcPassword: "<redacted>"
> }
> }
> ]
> }
>
> My JDBC URL is: jdbc:calcite:model=<redacted>/model.json
>
> Anyway, I'll keep poking at it! Thanks
>
>
>
>
>
> On Wed, Aug 21, 2024 at 12:33 AM Stamatis Zampetakis <zabe...@gmail.com>
> wrote:
>
>> Hey David,
>>
>> Your understanding so far is correct and you are pretty close in making
>> it work.
>>
>> Indeed, the JDBC URL should start with "jdbc:calcite" if you are to
>> use the Calcite JDBC driver but for this to happen the driver needs to
>> be loaded. If you are using some of the recent Calcite versions and
>> Java version >= 6 normally the registration should happen
>> transparently since Calcite is using the automatic driver registration
>> features of JDBC 4 which relies on Java SPI.
>>
>> The Calcite JDBC driver itself is in the core module but the latter is
>> not self-contained and has dependencies on other libraries and
>> modules. For things to work all required dependencies must be in the
>> classpath as well otherwise you will encounter ClassNotFoundException
>> and other similar errors; it is plausible that some errors are already
>> happening in the background and that's why the driver is not loaded.
>>
>> Sometime ago Gavin wrote a nice post on how to make Calcite work with
>> SQuirreL [1] which seems pretty close to what you are trying to
>> achieve.
>>
>> Best,
>> Stamatis
>>
>> [1] https://gist.github.com/GavinRay97/44fcd0f17e5c62ed6e7880fed0b0042e
>>
>> On Wed, Aug 21, 2024 at 1:11 AM David Ventimiglia
>> <davidaventimig...@hasura.io.invalid> wrote:
>> >
>> > Hello!
>> >
>> > The Calcite tutorial <https://calcite.apache.org/docs/tutorial.html>
>> shows
>> > how to use a custom sqlline to connect to the Calcite JDBC driver with a
>> > model.json to use a simple adapter that makes a directory of CSV files
>> > appear to be a schema containing tables. Later, the tutorial shows
>> what I
>> > think is a model.json file for using the JDBC Adapter
>> > <https://calcite.apache.org/docs/tutorial.html#jdbc-adapter> but I
>> don't
>> > really understand how to use it. Evidently, there's a Calcite JDBC
>> driver
>> > that can handle JDBC URLs of the form "jdbc:calcite:model=model.json".
>> > Given that, I feel like using the JDBC adapter might be as easy as
>> > substituting a suitable model.json file, like the one for MySQL in the
>> > tutorial, but that might be overly optimistic. For that really to work,
>> > the Calcite JDBC driver would have to be on the classpath as well as the
>> > MySQL driver (or PostgreSQL driver, or whatever) and I can't figure out
>> how
>> > to arrange that. It's easy enough to put the database drivers into the
>> > CLASSPATH environment variable, but where is the Calcite JDBC driver? I
>> > tried adding calcite/core/build/libs/calcite-core-1.38.0-SNAPSHOT.jar to
>> > CLASSPATH and using the stock (unmodified) sqlline, but it failed to
>> pick
>> > up the driver, and now I'm wondering if this is even the right approach.
>> > I'm really kinda lost here and any help would be appreciated. Thanks!
>> >
>> > Best,
>> > David
>>
>
