Wait! Nevermind? Evidently, this does work after all: - ✅ "select * from \"postgres_air\".\"aircraft\""
Must've been a typo... On Wed, Aug 21, 2024 at 4:54 PM David Ventimiglia < [email protected]> wrote: > 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 < > [email protected]> 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 <[email protected]> >> 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 >>> <[email protected]> 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 >>> >>
