You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Mark Pasterkamp <ma...@gmail.com> on 2019/04/05 13:49:15 UTC
expose existing materialized views from postgres to calcite
Dear all,
I have connected my postgresql database as a datasource to calcite. I am
however not able to find out how I can expose the existing materialized
views in postgres to calcite. I am unable to find much help on the internet
so if anyone is knowledgeable about this I would really appreciate a small
code example as to how this is supposed to work in Java.
Mark
Re: expose existing materialized views from postgres to calcite
Posted by Mark Pasterkamp <ma...@gmail.com>.
Hi Stamatis,
I have looked at the example but I am still a bit confused what I need to
do when I want to create a new materialized view.
Am I then supposed to create a jdbc connection to postgres, create a
materialized view and then change the model again for calcite? Because I am
unable to create a materialized view directly via calcite.
Mark
On Tue, 9 Apr 2019 at 00:05, Stamatis Zampetakis <za...@gmail.com> wrote:
> Hi Mark,
>
> It is not easy to understand what is missing from the pieces of code you
> shared.
>
> In the example that I've shared with you, I don't remember seeing anywhere
> CREATE VIEW statements. I suppose you are trying to execute a
> java.sql.Statement but I am afraid this will not work. You don't need to
> create the view in Postgres (assuming that is already there) but you just
> need to make Calcite aware of it. This will not be done by executing a
> Statement but by using various APIs like the one in the test case mentioned
> earlier.
>
> I think the easiest way to go would be to modify the test case itself and
> see if how it can be adapted based on your schema.
>
> Best,
> Stamatis
>
>
>
> On Mon, Apr 8, 2019 at 3:29 PM Yuzhao Chen <yu...@gmail.com> wrote:
>
> > Or you can config your framework config with a schema implemented by your
> > self [1], the getTable should be implemented correctly [2]. Then config
> the
> > CalciteCatalogReader with the schema and schemaPath [3].
> > The validator used your CalciteCatalogReader will recognize your tables
> in
> > Postgres.
> >
> > [1]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
> > [2]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
> > [3]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
> >
> > Best,
> > Danny Chan
> > 在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <markpasterkamp1994@gmail.com
> > >，写道：
> > > Dear all,
> > >
> > > I have connected my postgresql database as a datasource to calcite. I
> am
> > > however not able to find out how I can expose the existing materialized
> > > views in postgres to calcite. I am unable to find much help on the
> > internet
> > > so if anyone is knowledgeable about this I would really appreciate a
> > small
> > > code example as to how this is supposed to work in Java.
> > >
> > >
> > > Mark
> >
>
Re: expose existing materialized views from postgres to calcite
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Mark,
Calcite is used in many ways with the most prominent being query parsing,
planning, and accessing data-sources that by default do not provide a
declarative query language (i.e., SQL). The main goal of the project is on
querying data rather than modifying the data (or the schema).
Lately, there have been various efforts to expand Calcite in order to
handle DDL and DML statements for various use-cases and as you noticed
(e.g., in the parser) there are parts in the codebase where this is taken
into account. However, I think that the JDBC adapter does no support DDL
statements and I don't think anybody is working on it right now.
Having said that, your use-case seems perfectly valid so I think it would
be great if we had some contributions on this part.
Regarding the schema name ("db1") I don't think you need to change the
schema in Postgres but only change the name in Calcite.
Best,
Stamatis
On Wed, Apr 10, 2019 at 10:59 AM mark pasterkamp <ma...@hotmail.com>
wrote:
> I'm sorry if I don't understand it correctly but if I can't insert data in
> my database through calcite nor create materialized views through calcite
> then what is the use case of Calcite?
>
> I would have assumed that it could do both of these use cases seeing as
> Calcite clearly supports both of those commands. However a "create
> materialized view v1 ...." command translates to a "insert into v1" command
> through [1] [2] on postgres (which obviously does not work seeing as I'm
> creating a materialized view which does not exist).
>
> And what means on an insert, when I try to execute "insert into
> db1.table_name ..." it retains the schemaname given to calcite in the final
> query to postgres (so on postgres, I get the incoming query "insert into
> db1.table_name"). This behaviour is not consistent with spja-queries so I
> cannot hack around this by renaming the postgres schema to db1.
>
> Now I am not sure if this is intented behaviour, but as of now I can not
> see a clear solution to these problems. Or perhaps Calcite is not intended
> to be used this way?
>
> Mark
>
> [1]
> https://github.com/apache/calcite/blob/72e952d1a79ee2d7ba05de88cbc2ac11f65cd879/server/src/main/java/org/apache/calcite/sql/ddl/SqlCreateMaterializedView.java#L125
> [2]
> https://github.com/apache/calcite/blob/72e952d1a79ee2d7ba05de88cbc2ac11f65cd879/server/src/main/java/org/apache/calcite/sql/ddl/SqlDdlNodes.java#L221
>
> ________________________________
> Van: Stamatis Zampetakis <za...@gmail.com>
> Verzonden: woensdag 10 april 2019 00:23
> Aan: dev@calcite.apache.org
> Onderwerp: Re: expose existing materialized views from postgres to calcite
>
> If I am not mistaken the current implementation of JdbcSchema (and in
> general implementations of Schema) is not modifiable which means that
> changes to the database will not be reflected to the schema.
> I don't think it is possible to dynamically create views or tables through
> the JdbcAdapter. Of course, you can create a new schema whenever you want
> so in principle you could do this after every change in the database.
>
> Best,
> Stamatis
>
>
> On Tue, Apr 9, 2019 at 1:32 PM mark pasterkamp <markpasterkamp@hotmail.com
> >
> wrote:
>
> > Hi Stamatis,
> >
> > I have looked at the example but I am still a bit confused what I need to
> > do when I want to create a new materialized view.
> > Am I then supposed to create a jdbc connection to postgres, create a
> > materialized view and then change the model again for calcite? Because I
> am
> > unable to create a materialized view directly via calcite.
> >
> > I am sorry if this reply reached you twice, I accidentally replied with
> > the wrong email and I was not able to find an entry of my reply in the
> > archives.
> >
> > Mark
> >
> > ________________________________
> > Van: Stamatis Zampetakis <za...@gmail.com>
> > Verzonden: dinsdag 9 april 2019 00:03
> > Aan: dev@calcite.apache.org
> > Onderwerp: Re: expose existing materialized views from postgres to
> calcite
> >
> > Hi Mark,
> >
> > It is not easy to understand what is missing from the pieces of code you
> > shared.
> >
> > In the example that I've shared with you, I don't remember seeing
> anywhere
> > CREATE VIEW statements. I suppose you are trying to execute a
> > java.sql.Statement but I am afraid this will not work. You don't need to
> > create the view in Postgres (assuming that is already there) but you just
> > need to make Calcite aware of it. This will not be done by executing a
> > Statement but by using various APIs like the one in the test case
> mentioned
> > earlier.
> >
> > I think the easiest way to go would be to modify the test case itself and
> > see if how it can be adapted based on your schema.
> >
> > Best,
> > Stamatis
> >
> >
> >
> > On Mon, Apr 8, 2019 at 3:29 PM Yuzhao Chen <yu...@gmail.com> wrote:
> >
> > > Or you can config your framework config with a schema implemented by
> your
> > > self [1], the getTable should be implemented correctly [2]. Then config
> > the
> > > CalciteCatalogReader with the schema and schemaPath [3].
> > > The validator used your CalciteCatalogReader will recognize your tables
> > in
> > > Postgres.
> > >
> > > [1]
> > >
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
> > > [2]
> > >
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
> > > [3]
> > >
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
> > >
> > > Best,
> > > Danny Chan
> > > 在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <
> markpasterkamp1994@gmail.com
> > > >，写道：
> > > > Dear all,
> > > >
> > > > I have connected my postgresql database as a datasource to calcite. I
> > am
> > > > however not able to find out how I can expose the existing
> materialized
> > > > views in postgres to calcite. I am unable to find much help on the
> > > internet
> > > > so if anyone is knowledgeable about this I would really appreciate a
> > > small
> > > > code example as to how this is supposed to work in Java.
> > > >
> > > >
> > > > Mark
> > >
> >
>
Re: expose existing materialized views from postgres to calcite
Posted by mark pasterkamp <ma...@hotmail.com>.
I'm sorry if I don't understand it correctly but if I can't insert data in my database through calcite nor create materialized views through calcite then what is the use case of Calcite?
I would have assumed that it could do both of these use cases seeing as Calcite clearly supports both of those commands. However a "create materialized view v1 ...." command translates to a "insert into v1" command through [1] [2] on postgres (which obviously does not work seeing as I'm creating a materialized view which does not exist).
And what means on an insert, when I try to execute "insert into db1.table_name ..." it retains the schemaname given to calcite in the final query to postgres (so on postgres, I get the incoming query "insert into db1.table_name"). This behaviour is not consistent with spja-queries so I cannot hack around this by renaming the postgres schema to db1.
Now I am not sure if this is intented behaviour, but as of now I can not see a clear solution to these problems. Or perhaps Calcite is not intended to be used this way?
Mark
[1] https://github.com/apache/calcite/blob/72e952d1a79ee2d7ba05de88cbc2ac11f65cd879/server/src/main/java/org/apache/calcite/sql/ddl/SqlCreateMaterializedView.java#L125
[2] https://github.com/apache/calcite/blob/72e952d1a79ee2d7ba05de88cbc2ac11f65cd879/server/src/main/java/org/apache/calcite/sql/ddl/SqlDdlNodes.java#L221
________________________________
Van: Stamatis Zampetakis <za...@gmail.com>
Verzonden: woensdag 10 april 2019 00:23
Aan: dev@calcite.apache.org
Onderwerp: Re: expose existing materialized views from postgres to calcite
If I am not mistaken the current implementation of JdbcSchema (and in
general implementations of Schema) is not modifiable which means that
changes to the database will not be reflected to the schema.
I don't think it is possible to dynamically create views or tables through
the JdbcAdapter. Of course, you can create a new schema whenever you want
so in principle you could do this after every change in the database.
Best,
Stamatis
On Tue, Apr 9, 2019 at 1:32 PM mark pasterkamp <ma...@hotmail.com>
wrote:
> Hi Stamatis,
>
> I have looked at the example but I am still a bit confused what I need to
> do when I want to create a new materialized view.
> Am I then supposed to create a jdbc connection to postgres, create a
> materialized view and then change the model again for calcite? Because I am
> unable to create a materialized view directly via calcite.
>
> I am sorry if this reply reached you twice, I accidentally replied with
> the wrong email and I was not able to find an entry of my reply in the
> archives.
>
> Mark
>
> ________________________________
> Van: Stamatis Zampetakis <za...@gmail.com>
> Verzonden: dinsdag 9 april 2019 00:03
> Aan: dev@calcite.apache.org
> Onderwerp: Re: expose existing materialized views from postgres to calcite
>
> Hi Mark,
>
> It is not easy to understand what is missing from the pieces of code you
> shared.
>
> In the example that I've shared with you, I don't remember seeing anywhere
> CREATE VIEW statements. I suppose you are trying to execute a
> java.sql.Statement but I am afraid this will not work. You don't need to
> create the view in Postgres (assuming that is already there) but you just
> need to make Calcite aware of it. This will not be done by executing a
> Statement but by using various APIs like the one in the test case mentioned
> earlier.
>
> I think the easiest way to go would be to modify the test case itself and
> see if how it can be adapted based on your schema.
>
> Best,
> Stamatis
>
>
>
> On Mon, Apr 8, 2019 at 3:29 PM Yuzhao Chen <yu...@gmail.com> wrote:
>
> > Or you can config your framework config with a schema implemented by your
> > self [1], the getTable should be implemented correctly [2]. Then config
> the
> > CalciteCatalogReader with the schema and schemaPath [3].
> > The validator used your CalciteCatalogReader will recognize your tables
> in
> > Postgres.
> >
> > [1]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
> > [2]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
> > [3]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
> >
> > Best,
> > Danny Chan
> > 在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <markpasterkamp1994@gmail.com
> > >，写道：
> > > Dear all,
> > >
> > > I have connected my postgresql database as a datasource to calcite. I
> am
> > > however not able to find out how I can expose the existing materialized
> > > views in postgres to calcite. I am unable to find much help on the
> > internet
> > > so if anyone is knowledgeable about this I would really appreciate a
> > small
> > > code example as to how this is supposed to work in Java.
> > >
> > >
> > > Mark
> >
>
Re: expose existing materialized views from postgres to calcite
Posted by Stamatis Zampetakis <za...@gmail.com>.
If I am not mistaken the current implementation of JdbcSchema (and in
general implementations of Schema) is not modifiable which means that
changes to the database will not be reflected to the schema.
I don't think it is possible to dynamically create views or tables through
the JdbcAdapter. Of course, you can create a new schema whenever you want
so in principle you could do this after every change in the database.
Best,
Stamatis
On Tue, Apr 9, 2019 at 1:32 PM mark pasterkamp <ma...@hotmail.com>
wrote:
> Hi Stamatis,
>
> I have looked at the example but I am still a bit confused what I need to
> do when I want to create a new materialized view.
> Am I then supposed to create a jdbc connection to postgres, create a
> materialized view and then change the model again for calcite? Because I am
> unable to create a materialized view directly via calcite.
>
> I am sorry if this reply reached you twice, I accidentally replied with
> the wrong email and I was not able to find an entry of my reply in the
> archives.
>
> Mark
>
> ________________________________
> Van: Stamatis Zampetakis <za...@gmail.com>
> Verzonden: dinsdag 9 april 2019 00:03
> Aan: dev@calcite.apache.org
> Onderwerp: Re: expose existing materialized views from postgres to calcite
>
> Hi Mark,
>
> It is not easy to understand what is missing from the pieces of code you
> shared.
>
> In the example that I've shared with you, I don't remember seeing anywhere
> CREATE VIEW statements. I suppose you are trying to execute a
> java.sql.Statement but I am afraid this will not work. You don't need to
> create the view in Postgres (assuming that is already there) but you just
> need to make Calcite aware of it. This will not be done by executing a
> Statement but by using various APIs like the one in the test case mentioned
> earlier.
>
> I think the easiest way to go would be to modify the test case itself and
> see if how it can be adapted based on your schema.
>
> Best,
> Stamatis
>
>
>
> On Mon, Apr 8, 2019 at 3:29 PM Yuzhao Chen <yu...@gmail.com> wrote:
>
> > Or you can config your framework config with a schema implemented by your
> > self [1], the getTable should be implemented correctly [2]. Then config
> the
> > CalciteCatalogReader with the schema and schemaPath [3].
> > The validator used your CalciteCatalogReader will recognize your tables
> in
> > Postgres.
> >
> > [1]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
> > [2]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
> > [3]
> >
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
> >
> > Best,
> > Danny Chan
> > 在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <markpasterkamp1994@gmail.com
> > >，写道：
> > > Dear all,
> > >
> > > I have connected my postgresql database as a datasource to calcite. I
> am
> > > however not able to find out how I can expose the existing materialized
> > > views in postgres to calcite. I am unable to find much help on the
> > internet
> > > so if anyone is knowledgeable about this I would really appreciate a
> > small
> > > code example as to how this is supposed to work in Java.
> > >
> > >
> > > Mark
> >
>
Re: expose existing materialized views from postgres to calcite
Posted by mark pasterkamp <ma...@hotmail.com>.
Hi Stamatis,
I have looked at the example but I am still a bit confused what I need to do when I want to create a new materialized view.
Am I then supposed to create a jdbc connection to postgres, create a materialized view and then change the model again for calcite? Because I am unable to create a materialized view directly via calcite.
I am sorry if this reply reached you twice, I accidentally replied with the wrong email and I was not able to find an entry of my reply in the archives.
Mark
________________________________
Van: Stamatis Zampetakis <za...@gmail.com>
Verzonden: dinsdag 9 april 2019 00:03
Aan: dev@calcite.apache.org
Onderwerp: Re: expose existing materialized views from postgres to calcite
Hi Mark,
It is not easy to understand what is missing from the pieces of code you
shared.
In the example that I've shared with you, I don't remember seeing anywhere
CREATE VIEW statements. I suppose you are trying to execute a
java.sql.Statement but I am afraid this will not work. You don't need to
create the view in Postgres (assuming that is already there) but you just
need to make Calcite aware of it. This will not be done by executing a
Statement but by using various APIs like the one in the test case mentioned
earlier.
I think the easiest way to go would be to modify the test case itself and
see if how it can be adapted based on your schema.
Best,
Stamatis
On Mon, Apr 8, 2019 at 3:29 PM Yuzhao Chen <yu...@gmail.com> wrote:
> Or you can config your framework config with a schema implemented by your
> self [1], the getTable should be implemented correctly [2]. Then config the
> CalciteCatalogReader with the schema and schemaPath [3].
> The validator used your CalciteCatalogReader will recognize your tables in
> Postgres.
>
> [1]
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
> [2]
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
> [3]
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
>
> Best,
> Danny Chan
> 在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <markpasterkamp1994@gmail.com
> >，写道：
> > Dear all,
> >
> > I have connected my postgresql database as a datasource to calcite. I am
> > however not able to find out how I can expose the existing materialized
> > views in postgres to calcite. I am unable to find much help on the
> internet
> > so if anyone is knowledgeable about this I would really appreciate a
> small
> > code example as to how this is supposed to work in Java.
> >
> >
> > Mark
>
Re: expose existing materialized views from postgres to calcite
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Mark,
It is not easy to understand what is missing from the pieces of code you
shared.
In the example that I've shared with you, I don't remember seeing anywhere
CREATE VIEW statements. I suppose you are trying to execute a
java.sql.Statement but I am afraid this will not work. You don't need to
create the view in Postgres (assuming that is already there) but you just
need to make Calcite aware of it. This will not be done by executing a
Statement but by using various APIs like the one in the test case mentioned
earlier.
I think the easiest way to go would be to modify the test case itself and
see if how it can be adapted based on your schema.
Best,
Stamatis
On Mon, Apr 8, 2019 at 3:29 PM Yuzhao Chen <yu...@gmail.com> wrote:
> Or you can config your framework config with a schema implemented by your
> self [1], the getTable should be implemented correctly [2]. Then config the
> CalciteCatalogReader with the schema and schemaPath [3].
> The validator used your CalciteCatalogReader will recognize your tables in
> Postgres.
>
> [1]
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
> [2]
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
> [3]
> https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
>
> Best,
> Danny Chan
> 在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <markpasterkamp1994@gmail.com
> >，写道：
> > Dear all,
> >
> > I have connected my postgresql database as a datasource to calcite. I am
> > however not able to find out how I can expose the existing materialized
> > views in postgres to calcite. I am unable to find much help on the
> internet
> > so if anyone is knowledgeable about this I would really appreciate a
> small
> > code example as to how this is supposed to work in Java.
> >
> >
> > Mark
>
Re: expose existing materialized views from postgres to calcite
Posted by Yuzhao Chen <yu...@gmail.com>.
Or you can config your framework config with a schema implemented by your self [1], the getTable should be implemented correctly [2]. Then config the CalciteCatalogReader with the schema and schemaPath [3].
The validator used your CalciteCatalogReader will recognize your tables in Postgres.
[1] https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/tools/FrameworkConfig.java#L55
[2] https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/schema/Schema.java#L63
[3] https://github.com/apache/calcite/blob/a8e71f9f295e36e472d9f650d1c76d2501e1fdbf/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java#L100
Best,
Danny Chan
在 2019年4月5日 +0800 PM10:00，Mark Pasterkamp <ma...@gmail.com>，写道：
> Dear all,
>
> I have connected my postgresql database as a datasource to calcite. I am
> however not able to find out how I can expose the existing materialized
> views in postgres to calcite. I am unable to find much help on the internet
> so if anyone is knowledgeable about this I would really appreciate a small
> code example as to how this is supposed to work in Java.
>
>
> Mark
Re: expose existing materialized views from postgres to calcite
Posted by Mark Pasterkamp <ma...@gmail.com>.
Dear Stamatis,
Thank you for your reply.
I have since been trying to create materialized views via calcite without
much success, I don't know if you can perhaps help with that?
I have set up my rootschema as:
final DataSource ds = JdbcSchema.dataSource( dburl, "org.postgresql.Driver"
);
rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", ds, null, null));
Now I have tried multiple times to create a simple materialized view like:
create materialized view v1
create materialized view \"v1\"
create materialized view db1.\"v1\"
But I keep receiving the same exception: org.postgresql.util.PSQLException:
ERROR: relation "DB1.v1" does not exist. I also tried to rename the
"public" schema in postgres to "DB1" in the off chance that their might be
an issue with schema names but that did not work.
Would you perhaps have any idea how to make it work?
Mark
On Mon, 8 Apr 2019 at 12:34, Stamatis Zampetakis <za...@gmail.com> wrote:
> Hi Mark,
>
> I suppose that the views cannot be discovered automatically so you will
> have to register them explicitly. If you are connecting to Postgres using
> the JsonModel then you might find the tests in MaterializationTest [1]
> useful. Most likely you will have to change the SchemaFactory to use the
> one related to JdbcSchema [2].
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/apache/calcite/blob/16f518b359cdf529d5bbe42cd5e8663001030d14/core/src/test/java/org/apache/calcite/test/MaterializationTest.java#L129
> [2]
>
> https://github.com/apache/calcite/blob/16f518b359cdf529d5bbe42cd5e8663001030d14/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java#L497
>
> On Fri, Apr 5, 2019 at 4:00 PM Mark Pasterkamp <
> markpasterkamp1994@gmail.com>
> wrote:
>
> > Dear all,
> >
> > I have connected my postgresql database as a datasource to calcite. I am
> > however not able to find out how I can expose the existing materialized
> > views in postgres to calcite. I am unable to find much help on the
> internet
> > so if anyone is knowledgeable about this I would really appreciate a
> small
> > code example as to how this is supposed to work in Java.
> >
> >
> > Mark
> >
>
Re: expose existing materialized views from postgres to calcite
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Mark,
I suppose that the views cannot be discovered automatically so you will
have to register them explicitly. If you are connecting to Postgres using
the JsonModel then you might find the tests in MaterializationTest [1]
useful. Most likely you will have to change the SchemaFactory to use the
one related to JdbcSchema [2].
Best,
Stamatis
[1]
https://github.com/apache/calcite/blob/16f518b359cdf529d5bbe42cd5e8663001030d14/core/src/test/java/org/apache/calcite/test/MaterializationTest.java#L129
[2]
https://github.com/apache/calcite/blob/16f518b359cdf529d5bbe42cd5e8663001030d14/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java#L497
On Fri, Apr 5, 2019 at 4:00 PM Mark Pasterkamp <ma...@gmail.com>
wrote:
> Dear all,
>
> I have connected my postgresql database as a datasource to calcite. I am
> however not able to find out how I can expose the existing materialized
> views in postgres to calcite. I am unable to find much help on the internet
> so if anyone is knowledgeable about this I would really appreciate a small
> code example as to how this is supposed to work in Java.
>
>
> Mark
>