You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by AshwinKumar AshwinKumar <aa...@g.clemson.edu> on 2017/10/02 18:40:48 UTC
Joining Elastisearch types and Postgres Database tables
Hello Team,
I had a question regarding connecting Elastisearch and Postgres Database in
a single query. Could you please let me know If we can query a elastisearch
type(table) and a postgres table in the same query.
Thanks,
Ashwin
Re: Joining Elastisearch types and Postgres Database tables
Posted by Julian Hyde <jh...@apache.org>.
Sorry, I have no idea.
> On Oct 2, 2017, at 5:26 PM, AshwinKumar AshwinKumar <aa...@g.clemson.edu> wrote:
>
> Hi Julian,
>
> I tried that too. But it happens to give the same error. Actually I am
> executing from the folder */calcite/example/csv. *I also changed the
> pom.xml file to add the postgresql dependency and executed the command -
>
> !connect jdbc:calcite:model=target/test-classes/model.json admin admin
>
> But it gives the following error -
>
>
> sqlline> !connect jdbc:calcite:model=target/test-classes/model.json admin
> admin
> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
> SLF4J: Defaulting to no-operation (NOP) logger implementation
> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further
> details.
> java.lang.RuntimeException: Error instantiating
> JsonCustomSchema(name=postgrestest)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:274)
> at
> org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:45)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:195)
> at org.apache.calcite.model.ModelHandler.<init>(ModelHandler.java:87)
> at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:104)
> at
> org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:139)
> at sqlline.DatabaseConnection.connect(DatabaseConnection.java:156)
> at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:204)
> at sqlline.Commands.connect(Commands.java:1095)
> at sqlline.Commands.connect(Commands.java:1001)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:38)
> at sqlline.SqlLine.dispatch(SqlLine.java:791)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> Caused by: java.lang.RuntimeException:
> org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class
> 'org.postgresql.Driver'
> at
> org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.get(JdbcUtils.java:88)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema.createDialect(JdbcSchema.java:149)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:107)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:143)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema$Factory.create(JdbcSchema.java:446)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:269)
>
> I thought doing an mvn install will make a difference and executed the
> command -
>
> mvn install -DskipTests -Dcheckstyle.skip=true
>
> But it gives the error -
> [INFO] --- maven-dependency-plugin:2.10:analyze-only (analyze) @
> calcite-example-csv ---
> [WARNING] Unused declared dependencies found:
> [WARNING] org.postgresql:postgresql:jar:9.4-1200-jdbc41:compile
> [INFO] ------------------------------------------------------------------------
> [INFO] BUILD FAILURE
> [INFO] ------------------------------------------------------------------------
> [INFO] Total time: 29.911 s
> [INFO] Finished at: 2017-10-03T01:22:25+01:00
> [INFO] Final Memory: 22M/167M
> [INFO] ------------------------------------------------------------------------
> [ERROR] Failed to execute goal
> org.apache.maven.plugins:maven-dependency-plugin:2.10:analyze-only
> (analyze) on project calcite-example-csv: Dependency problems found ->
> [Help 1]
>
> Below is the postgres dependency I have used in the pom.xml file -
>
> <dependency>
> <groupId>org.postgresql</groupId>
> <artifactId>postgresql</artifactId>
> <version>9.4-1200-jdbc41</version>
> </dependency>
>
>
> Could you please help here.
>
> Thanks,
>
> Ashwin
>
>
>
>
>
>
> On Tue, Oct 3, 2017 at 12:45 AM, Julian Hyde <jh...@apache.org> wrote:
>
>> Looks good, except that I’d change
>>
>>> "defaultSchema": "SALES Samplepostgres”,
>>
>> to
>>
>>> "defaultSchema": "SALES”,
>>
>> Julian
>>
>>> On Oct 2, 2017, at 2:38 PM, AshwinKumar AshwinKumar <
>> aashwin@g.clemson.edu> wrote:
>>>
>>> Sorry this is the proper json file -
>>>
>>> {
>>> "version": "1.0",
>>> "defaultSchema": "SALES Samplepostgres",
>>> "schemas": [
>>> {
>>> "name": "SALES",
>>> "type": "custom",
>>> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
>>> "operand": {
>>> "directory": "sales"
>>> }
>>> },
>>> {
>>> "name": "Samplepostgres",
>>> "type": "custom",
>>> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
>>> "operand": {
>>> "jdbcDriver": "org.postgresql.Driver",
>>> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
>>> "jdbcUser": "pguser",
>>> "jdbcPassword": "test"
>>> }
>>> }
>>> ]
>>>
>>>
>>> Thanks,
>>> Ashwin
>>>
>>> On Mon, Oct 2, 2017 at 10:37 PM, AshwinKumar AshwinKumar <
>>> aashwin@g.clemson.edu> wrote:
>>>
>>>> Hi ,
>>>>
>>>> Do you mean to say, I need to change the model.json file present in the
>>>> examples directory. I have edited the existing model.json file present
>> in
>>>> the example/csv/target/test-classes to include the postgres schema .
>> But
>>>> I am not able to connect . Below is the edited json just to include
>>>> postgres -
>>>>
>>>> {
>>>> "version": "1.0",
>>>> "defaultSchema": "SALES Samplepostgres",
>>>> "schemas": [
>>>> {
>>>> "name": "SALES",
>>>> "type": "custom",
>>>> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
>>>> "operand": {
>>>> "directory": "sales"
>>>> }
>>>> },
>>>> {
>>>> "name": "Samplepostgres",
>>>> "type": "custom",
>>>> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
>>>> "version": "1.0",
>>>> "defaultSchema": "SALES Samplepostgres",
>>>> "schemas": [
>>>> {
>>>> "name": "SALES",
>>>> "type": "custom",
>>>> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
>>>> "operand": {
>>>> "directory": "sales"
>>>> }
>>>> },
>>>> {
>>>> "name": "Samplepostgres",
>>>> "type": "custom",
>>>> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
>>>> "operand": {
>>>> "jdbcDriver": "org.postgresql.Driver",
>>>> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
>>>> "jdbcUser": "pguser",
>>>> "jdbcPassword": "test"
>>>> }
>>>> }
>>>> ]
>>>> }
>>>> tory",
>>>> "operand": {
>>>> "jdbcDriver": "org.postgresql.Driver",
>>>> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
>>>> "jdbcUser": "pguser",
>>>> "jdbcPassword": "test"
>>>> }
>>>> }
>>>> ]
>>>> }
>>>>
>>>> Could you please let me know if it is correct. I am getting an error -
>>>>
>>>> sqlline version 1.3.0
>>>> sqlline> select * from table3;
>>>> No current connection
>>>>
>>>> The table table3 is present in the postgres database.
>>>>
>>>> Thanks,
>>>> Ashwin
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org> wrote:
>>>>
>>>>> Easiest thing is to create a JSON model file with two schemas in it.
>>>>>
>>>>> https://calcite.apache.org/docs/model.html <
>> https://calcite.apache.org/do
>>>>> cs/model.html>
>>>>>
>>>>> https://calcite.apache.org/docs/adapter.html <
>>>>> https://calcite.apache.org/docs/adapter.html>
>>>>>
>>>>>
>>>>>> On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
>>>>> aashwin@g.clemson.edu> wrote:
>>>>>>
>>>>>> Could you please give me some pointers for where can I get some
>>>>>> documentation or links on how to connect elastisearch and postgres in
>>>>> same
>>>>>> query.
>>>>>> As we know Elastisearch has a separate adapter and postgres comes
>> under
>>>>>> jdbc adapter. Do we need to create a root schema using jdbc adapter
>> for
>>>>>> elastisearch instance.
>>>>>>
>>>>>> Thanks,
>>>>>> Ashwin
>>>>>>
>>>>>> On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
>>>>>>
>>>>>>> Yes, you should be able to combine any data source with any other
>> data
>>>>>>> source in a query.
>>>>>>>
>>>>>>> Julian
>>>>>>>
>>>>>>>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
>>>>>>> aashwin@g.clemson.edu> wrote:
>>>>>>>>
>>>>>>>> Hello Team,
>>>>>>>>
>>>>>>>> I had a question regarding connecting Elastisearch and Postgres
>>>>> Database
>>>>>>> in
>>>>>>>> a single query. Could you please let me know If we can query a
>>>>>>> elastisearch
>>>>>>>> type(table) and a postgres table in the same query.
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Ashwin
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>
>>
>>
Re: Joining Elastisearch types and Postgres Database tables
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Hi Team,
Could you please let me know how to execute sqlline from the root
directory. Currently I am running from examples/csv directory which is only
for csv.
Please let me know how to connect to a jdbc adapter with model.json file.
Thanks,
Ashwin
Re: Joining Elastisearch types and Postgres Database tables
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Hi ,
I tried running from the root directory. But sqlline gives error saying
there is no model.json file when* I do a connect. PFB the output of the
command - *
*mvn dependency:analyze-only -X*
*I executed this command in the directory - *
*/calcite/example/csv*
*[DEBUG] Configuring mojo
org.apache.maven.plugins:maven-dependency-plugin:2.10:analyze-only from
plugin realm
ClassRealm[plugin>org.apache.maven.plugins:maven-dependency-plugin:2.10,
parent: sun.misc.Launcher$AppClassLoader@55f96302][DEBUG] Configuring mojo
'org.apache.maven.plugins:maven-dependency-plugin:2.10:analyze-only' with
basic configurator -->[DEBUG] (f) analyzer = default[DEBUG] (f) baseDir
= /home/osboxes/calcite/example/csv[DEBUG] (f) failOnWarning =
false[DEBUG] (f) ignoreNonCompile = false[DEBUG] (f) outputDirectory =
/home/osboxes/calcite/example/csv/target[DEBUG] (f) outputXML =
false[DEBUG] (f) project = MavenProject:
org.apache.calcite:calcite-example-csv:1.14.0-SNAPSHOT @
/home/osboxes/calcite/example/csv/pom.xml[DEBUG] (f) scriptableFlag =
$$%%%[DEBUG] (f) scriptableOutput = false[DEBUG] (s) skip =
false[DEBUG] (f) verbose = false[DEBUG] -- end configuration --[WARNING]
Unused declared dependencies found:[WARNING]
org.postgresql:postgresql:jar:9.4-1200-jdbc41:compile[WARNING]
sqlline:sqlline:jar:1.3.0:test[INFO]
------------------------------------------------------------------------[INFO]
BUILD SUCCESS[INFO]
------------------------------------------------------------------------[INFO]
Total time: 5.009 s[INFO] Finished at: 2017-10-03T21:20:30+01:00[INFO]
Final Memory: 14M/60M*
*As you can see in the log, the highlighted- its saying the dependency for
postgresql is unused. Could you please let me know the whats the issue here
and also please let me know how to connect to sqlline from the root
directory.*
*When you say root directory - Its calcite/ correct?.*
*Thanks,*
*Ashwin*
On Tue, Oct 3, 2017 at 2:47 PM, Michael Mior <mm...@uwaterloo.ca> wrote:
> Have you tried running sqlline from the root directory of the Calcite
> repository? I'm not sure the full class path gets picked up by
> sqlline otherwise. When you tried `mvn install` is that the full error
> message you got? It looks to be truncated.
>
> If running from the root directory doesn't help and you're still having
> dependency issues, sharing the output of the following command may be
> helpful:
>
> mvn dependency:analyze-only -X
>
> --
> Michael Mior
> mmior@apache.org
>
> 2017-10-02 20:26 GMT-04:00 AshwinKumar AshwinKumar <aashwin@g.clemson.edu
> >:
>
> > Hi Julian,
> >
> > I tried that too. But it happens to give the same error. Actually I am
> > executing from the folder */calcite/example/csv. *I also changed the
> > pom.xml file to add the postgresql dependency and executed the command -
> >
> > !connect jdbc:calcite:model=target/test-classes/model.json admin admin
> >
> > But it gives the following error -
> >
> >
> > sqlline> !connect jdbc:calcite:model=target/test-classes/model.json
> admin
> > admin
> > SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
> > SLF4J: Defaulting to no-operation (NOP) logger implementation
> > SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for
> further
> > details.
> > java.lang.RuntimeException: Error instantiating
> > JsonCustomSchema(name=postgrestest)
> > at org.apache.calcite.model.ModelHandler.visit(
> ModelHandler.java:274)
> > at
> > org.apache.calcite.model.JsonCustomSchema.accept(
> JsonCustomSchema.java:45)
> > at org.apache.calcite.model.ModelHandler.visit(
> ModelHandler.java:195)
> > at org.apache.calcite.model.ModelHandler.<init>(
> ModelHandler.java:87)
> > at org.apache.calcite.jdbc.Driver$1.onConnectionInit(
> Driver.java:104)
> > at
> > org.apache.calcite.avatica.UnregisteredDriver.connect(
> > UnregisteredDriver.java:139)
> > at sqlline.DatabaseConnection.connect(DatabaseConnection.java:156)
> > at sqlline.DatabaseConnection.getConnection(
> > DatabaseConnection.java:204)
> > at sqlline.Commands.connect(Commands.java:1095)
> > at sqlline.Commands.connect(Commands.java:1001)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > at
> > sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:
> > 62)
> > at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(
> > DelegatingMethodAccessorImpl.java:43)
> > at java.lang.reflect.Method.invoke(Method.java:498)
> > at
> > sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:
> 38)
> > at sqlline.SqlLine.dispatch(SqlLine.java:791)
> > at sqlline.SqlLine.begin(SqlLine.java:668)
> > at sqlline.SqlLine.start(SqlLine.java:373)
> > at sqlline.SqlLine.main(SqlLine.java:265)
> > Caused by: java.lang.RuntimeException:
> > org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver
> class
> > 'org.postgresql.Driver'
> > at
> > org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.
> > get(JdbcUtils.java:88)
> > at
> > org.apache.calcite.adapter.jdbc.JdbcSchema.createDialect(
> > JdbcSchema.java:149)
> > at
> > org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:107)
> > at
> > org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:143)
> > at
> > org.apache.calcite.adapter.jdbc.JdbcSchema$Factory.
> > create(JdbcSchema.java:446)
> > at org.apache.calcite.model.ModelHandler.visit(
> ModelHandler.java:269)
> >
> > I thought doing an mvn install will make a difference and executed the
> > command -
> >
> > mvn install -DskipTests -Dcheckstyle.skip=true
> >
> > But it gives the error -
> > [INFO] --- maven-dependency-plugin:2.10:analyze-only (analyze) @
> > calcite-example-csv ---
> > [WARNING] Unused declared dependencies found:
> > [WARNING] org.postgresql:postgresql:jar:9.4-1200-jdbc41:compile
> > [INFO] ------------------------------------------------------------
> > ------------
> > [INFO] BUILD FAILURE
> > [INFO] ------------------------------------------------------------
> > ------------
> > [INFO] Total time: 29.911 s
> > [INFO] Finished at: 2017-10-03T01:22:25+01:00
> > [INFO] Final Memory: 22M/167M
> > [INFO] ------------------------------------------------------------
> > ------------
> > [ERROR] Failed to execute goal
> > org.apache.maven.plugins:maven-dependency-plugin:2.10:analyze-only
> > (analyze) on project calcite-example-csv: Dependency problems found ->
> > [Help 1]
> >
> > Below is the postgres dependency I have used in the pom.xml file -
> >
> > <dependency>
> > <groupId>org.postgresql</groupId>
> > <artifactId>postgresql</artifactId>
> > <version>9.4-1200-jdbc41</version>
> > </dependency>
> >
> >
> > Could you please help here.
> >
> > Thanks,
> >
> > Ashwin
> >
> >
> >
> >
> >
> >
> > On Tue, Oct 3, 2017 at 12:45 AM, Julian Hyde <jh...@apache.org> wrote:
> >
> > > Looks good, except that I’d change
> > >
> > > > "defaultSchema": "SALES Samplepostgres”,
> > >
> > > to
> > >
> > > > "defaultSchema": "SALES”,
> > >
> > > Julian
> > >
> > > > On Oct 2, 2017, at 2:38 PM, AshwinKumar AshwinKumar <
> > > aashwin@g.clemson.edu> wrote:
> > > >
> > > > Sorry this is the proper json file -
> > > >
> > > > {
> > > > "version": "1.0",
> > > > "defaultSchema": "SALES Samplepostgres",
> > > > "schemas": [
> > > > {
> > > > "name": "SALES",
> > > > "type": "custom",
> > > > "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > > > "operand": {
> > > > "directory": "sales"
> > > > }
> > > > },
> > > > {
> > > > "name": "Samplepostgres",
> > > > "type": "custom",
> > > > "factory": "org.apache.calcite.adapter.
> > jdbc.JdbcSchema$Factory",
> > > > "operand": {
> > > > "jdbcDriver": "org.postgresql.Driver",
> > > > "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> > > > "jdbcUser": "pguser",
> > > > "jdbcPassword": "test"
> > > > }
> > > > }
> > > > ]
> > > >
> > > >
> > > > Thanks,
> > > > Ashwin
> > > >
> > > > On Mon, Oct 2, 2017 at 10:37 PM, AshwinKumar AshwinKumar <
> > > > aashwin@g.clemson.edu> wrote:
> > > >
> > > >> Hi ,
> > > >>
> > > >> Do you mean to say, I need to change the model.json file present in
> > the
> > > >> examples directory. I have edited the existing model.json file
> present
> > > in
> > > >> the example/csv/target/test-classes to include the postgres schema
> .
> > > But
> > > >> I am not able to connect . Below is the edited json just to include
> > > >> postgres -
> > > >>
> > > >> {
> > > >> "version": "1.0",
> > > >> "defaultSchema": "SALES Samplepostgres",
> > > >> "schemas": [
> > > >> {
> > > >> "name": "SALES",
> > > >> "type": "custom",
> > > >> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > > >> "operand": {
> > > >> "directory": "sales"
> > > >> }
> > > >> },
> > > >> {
> > > >> "name": "Samplepostgres",
> > > >> "type": "custom",
> > > >> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
> > > >> "version": "1.0",
> > > >> "defaultSchema": "SALES Samplepostgres",
> > > >> "schemas": [
> > > >> {
> > > >> "name": "SALES",
> > > >> "type": "custom",
> > > >> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > > >> "operand": {
> > > >> "directory": "sales"
> > > >> }
> > > >> },
> > > >> {
> > > >> "name": "Samplepostgres",
> > > >> "type": "custom",
> > > >> "factory": "org.apache.calcite.adapter.
> > jdbc.JdbcSchema$Factory",
> > > >> "operand": {
> > > >> "jdbcDriver": "org.postgresql.Driver",
> > > >> "jdbcUrl": "jdbc:postgresql://localhost/
> bigdawg_catalog",
> > > >> "jdbcUser": "pguser",
> > > >> "jdbcPassword": "test"
> > > >> }
> > > >> }
> > > >> ]
> > > >> }
> > > >> tory",
> > > >> "operand": {
> > > >> "jdbcDriver": "org.postgresql.Driver",
> > > >> "jdbcUrl": "jdbc:postgresql://localhost/
> bigdawg_catalog",
> > > >> "jdbcUser": "pguser",
> > > >> "jdbcPassword": "test"
> > > >> }
> > > >> }
> > > >> ]
> > > >> }
> > > >>
> > > >> Could you please let me know if it is correct. I am getting an error
> > -
> > > >>
> > > >> sqlline version 1.3.0
> > > >> sqlline> select * from table3;
> > > >> No current connection
> > > >>
> > > >> The table table3 is present in the postgres database.
> > > >>
> > > >> Thanks,
> > > >> Ashwin
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >> On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org>
> wrote:
> > > >>
> > > >>> Easiest thing is to create a JSON model file with two schemas in
> it.
> > > >>>
> > > >>> https://calcite.apache.org/docs/model.html <
> > > https://calcite.apache.org/do
> > > >>> cs/model.html>
> > > >>>
> > > >>> https://calcite.apache.org/docs/adapter.html <
> > > >>> https://calcite.apache.org/docs/adapter.html>
> > > >>>
> > > >>>
> > > >>>> On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
> > > >>> aashwin@g.clemson.edu> wrote:
> > > >>>>
> > > >>>> Could you please give me some pointers for where can I get some
> > > >>>> documentation or links on how to connect elastisearch and postgres
> > in
> > > >>> same
> > > >>>> query.
> > > >>>> As we know Elastisearch has a separate adapter and postgres comes
> > > under
> > > >>>> jdbc adapter. Do we need to create a root schema using jdbc
> adapter
> > > for
> > > >>>> elastisearch instance.
> > > >>>>
> > > >>>> Thanks,
> > > >>>> Ashwin
> > > >>>>
> > > >>>> On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org>
> > wrote:
> > > >>>>
> > > >>>>> Yes, you should be able to combine any data source with any other
> > > data
> > > >>>>> source in a query.
> > > >>>>>
> > > >>>>> Julian
> > > >>>>>
> > > >>>>>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
> > > >>>>> aashwin@g.clemson.edu> wrote:
> > > >>>>>>
> > > >>>>>> Hello Team,
> > > >>>>>>
> > > >>>>>> I had a question regarding connecting Elastisearch and Postgres
> > > >>> Database
> > > >>>>> in
> > > >>>>>> a single query. Could you please let me know If we can query a
> > > >>>>> elastisearch
> > > >>>>>> type(table) and a postgres table in the same query.
> > > >>>>>>
> > > >>>>>> Thanks,
> > > >>>>>> Ashwin
> > > >>>>>
> > > >>>>>
> > > >>>
> > > >>>
> > > >>
> > >
> > >
> >
>
Re: Joining Elastisearch types and Postgres Database tables
Posted by Michael Mior <mm...@uwaterloo.ca>.
Have you tried running sqlline from the root directory of the Calcite
repository? I'm not sure the full class path gets picked up by
sqlline otherwise. When you tried `mvn install` is that the full error
message you got? It looks to be truncated.
If running from the root directory doesn't help and you're still having
dependency issues, sharing the output of the following command may be
helpful:
mvn dependency:analyze-only -X
--
Michael Mior
mmior@apache.org
2017-10-02 20:26 GMT-04:00 AshwinKumar AshwinKumar <aa...@g.clemson.edu>:
> Hi Julian,
>
> I tried that too. But it happens to give the same error. Actually I am
> executing from the folder */calcite/example/csv. *I also changed the
> pom.xml file to add the postgresql dependency and executed the command -
>
> !connect jdbc:calcite:model=target/test-classes/model.json admin admin
>
> But it gives the following error -
>
>
> sqlline> !connect jdbc:calcite:model=target/test-classes/model.json admin
> admin
> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
> SLF4J: Defaulting to no-operation (NOP) logger implementation
> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further
> details.
> java.lang.RuntimeException: Error instantiating
> JsonCustomSchema(name=postgrestest)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:274)
> at
> org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:45)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:195)
> at org.apache.calcite.model.ModelHandler.<init>(ModelHandler.java:87)
> at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:104)
> at
> org.apache.calcite.avatica.UnregisteredDriver.connect(
> UnregisteredDriver.java:139)
> at sqlline.DatabaseConnection.connect(DatabaseConnection.java:156)
> at sqlline.DatabaseConnection.getConnection(
> DatabaseConnection.java:204)
> at sqlline.Commands.connect(Commands.java:1095)
> at sqlline.Commands.connect(Commands.java:1001)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
> 62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:38)
> at sqlline.SqlLine.dispatch(SqlLine.java:791)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> Caused by: java.lang.RuntimeException:
> org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class
> 'org.postgresql.Driver'
> at
> org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.
> get(JdbcUtils.java:88)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema.createDialect(
> JdbcSchema.java:149)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:107)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:143)
> at
> org.apache.calcite.adapter.jdbc.JdbcSchema$Factory.
> create(JdbcSchema.java:446)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:269)
>
> I thought doing an mvn install will make a difference and executed the
> command -
>
> mvn install -DskipTests -Dcheckstyle.skip=true
>
> But it gives the error -
> [INFO] --- maven-dependency-plugin:2.10:analyze-only (analyze) @
> calcite-example-csv ---
> [WARNING] Unused declared dependencies found:
> [WARNING] org.postgresql:postgresql:jar:9.4-1200-jdbc41:compile
> [INFO] ------------------------------------------------------------
> ------------
> [INFO] BUILD FAILURE
> [INFO] ------------------------------------------------------------
> ------------
> [INFO] Total time: 29.911 s
> [INFO] Finished at: 2017-10-03T01:22:25+01:00
> [INFO] Final Memory: 22M/167M
> [INFO] ------------------------------------------------------------
> ------------
> [ERROR] Failed to execute goal
> org.apache.maven.plugins:maven-dependency-plugin:2.10:analyze-only
> (analyze) on project calcite-example-csv: Dependency problems found ->
> [Help 1]
>
> Below is the postgres dependency I have used in the pom.xml file -
>
> <dependency>
> <groupId>org.postgresql</groupId>
> <artifactId>postgresql</artifactId>
> <version>9.4-1200-jdbc41</version>
> </dependency>
>
>
> Could you please help here.
>
> Thanks,
>
> Ashwin
>
>
>
>
>
>
> On Tue, Oct 3, 2017 at 12:45 AM, Julian Hyde <jh...@apache.org> wrote:
>
> > Looks good, except that I’d change
> >
> > > "defaultSchema": "SALES Samplepostgres”,
> >
> > to
> >
> > > "defaultSchema": "SALES”,
> >
> > Julian
> >
> > > On Oct 2, 2017, at 2:38 PM, AshwinKumar AshwinKumar <
> > aashwin@g.clemson.edu> wrote:
> > >
> > > Sorry this is the proper json file -
> > >
> > > {
> > > "version": "1.0",
> > > "defaultSchema": "SALES Samplepostgres",
> > > "schemas": [
> > > {
> > > "name": "SALES",
> > > "type": "custom",
> > > "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > > "operand": {
> > > "directory": "sales"
> > > }
> > > },
> > > {
> > > "name": "Samplepostgres",
> > > "type": "custom",
> > > "factory": "org.apache.calcite.adapter.
> jdbc.JdbcSchema$Factory",
> > > "operand": {
> > > "jdbcDriver": "org.postgresql.Driver",
> > > "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> > > "jdbcUser": "pguser",
> > > "jdbcPassword": "test"
> > > }
> > > }
> > > ]
> > >
> > >
> > > Thanks,
> > > Ashwin
> > >
> > > On Mon, Oct 2, 2017 at 10:37 PM, AshwinKumar AshwinKumar <
> > > aashwin@g.clemson.edu> wrote:
> > >
> > >> Hi ,
> > >>
> > >> Do you mean to say, I need to change the model.json file present in
> the
> > >> examples directory. I have edited the existing model.json file present
> > in
> > >> the example/csv/target/test-classes to include the postgres schema .
> > But
> > >> I am not able to connect . Below is the edited json just to include
> > >> postgres -
> > >>
> > >> {
> > >> "version": "1.0",
> > >> "defaultSchema": "SALES Samplepostgres",
> > >> "schemas": [
> > >> {
> > >> "name": "SALES",
> > >> "type": "custom",
> > >> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > >> "operand": {
> > >> "directory": "sales"
> > >> }
> > >> },
> > >> {
> > >> "name": "Samplepostgres",
> > >> "type": "custom",
> > >> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
> > >> "version": "1.0",
> > >> "defaultSchema": "SALES Samplepostgres",
> > >> "schemas": [
> > >> {
> > >> "name": "SALES",
> > >> "type": "custom",
> > >> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > >> "operand": {
> > >> "directory": "sales"
> > >> }
> > >> },
> > >> {
> > >> "name": "Samplepostgres",
> > >> "type": "custom",
> > >> "factory": "org.apache.calcite.adapter.
> jdbc.JdbcSchema$Factory",
> > >> "operand": {
> > >> "jdbcDriver": "org.postgresql.Driver",
> > >> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> > >> "jdbcUser": "pguser",
> > >> "jdbcPassword": "test"
> > >> }
> > >> }
> > >> ]
> > >> }
> > >> tory",
> > >> "operand": {
> > >> "jdbcDriver": "org.postgresql.Driver",
> > >> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> > >> "jdbcUser": "pguser",
> > >> "jdbcPassword": "test"
> > >> }
> > >> }
> > >> ]
> > >> }
> > >>
> > >> Could you please let me know if it is correct. I am getting an error
> -
> > >>
> > >> sqlline version 1.3.0
> > >> sqlline> select * from table3;
> > >> No current connection
> > >>
> > >> The table table3 is present in the postgres database.
> > >>
> > >> Thanks,
> > >> Ashwin
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org> wrote:
> > >>
> > >>> Easiest thing is to create a JSON model file with two schemas in it.
> > >>>
> > >>> https://calcite.apache.org/docs/model.html <
> > https://calcite.apache.org/do
> > >>> cs/model.html>
> > >>>
> > >>> https://calcite.apache.org/docs/adapter.html <
> > >>> https://calcite.apache.org/docs/adapter.html>
> > >>>
> > >>>
> > >>>> On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
> > >>> aashwin@g.clemson.edu> wrote:
> > >>>>
> > >>>> Could you please give me some pointers for where can I get some
> > >>>> documentation or links on how to connect elastisearch and postgres
> in
> > >>> same
> > >>>> query.
> > >>>> As we know Elastisearch has a separate adapter and postgres comes
> > under
> > >>>> jdbc adapter. Do we need to create a root schema using jdbc adapter
> > for
> > >>>> elastisearch instance.
> > >>>>
> > >>>> Thanks,
> > >>>> Ashwin
> > >>>>
> > >>>> On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org>
> wrote:
> > >>>>
> > >>>>> Yes, you should be able to combine any data source with any other
> > data
> > >>>>> source in a query.
> > >>>>>
> > >>>>> Julian
> > >>>>>
> > >>>>>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
> > >>>>> aashwin@g.clemson.edu> wrote:
> > >>>>>>
> > >>>>>> Hello Team,
> > >>>>>>
> > >>>>>> I had a question regarding connecting Elastisearch and Postgres
> > >>> Database
> > >>>>> in
> > >>>>>> a single query. Could you please let me know If we can query a
> > >>>>> elastisearch
> > >>>>>> type(table) and a postgres table in the same query.
> > >>>>>>
> > >>>>>> Thanks,
> > >>>>>> Ashwin
> > >>>>>
> > >>>>>
> > >>>
> > >>>
> > >>
> >
> >
>
Re: Joining Elastisearch types and Postgres Database tables
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Hi Julian,
I tried that too. But it happens to give the same error. Actually I am
executing from the folder */calcite/example/csv. *I also changed the
pom.xml file to add the postgresql dependency and executed the command -
!connect jdbc:calcite:model=target/test-classes/model.json admin admin
But it gives the following error -
sqlline> !connect jdbc:calcite:model=target/test-classes/model.json admin
admin
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further
details.
java.lang.RuntimeException: Error instantiating
JsonCustomSchema(name=postgrestest)
at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:274)
at
org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:45)
at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:195)
at org.apache.calcite.model.ModelHandler.<init>(ModelHandler.java:87)
at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:104)
at
org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:139)
at sqlline.DatabaseConnection.connect(DatabaseConnection.java:156)
at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:204)
at sqlline.Commands.connect(Commands.java:1095)
at sqlline.Commands.connect(Commands.java:1001)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:38)
at sqlline.SqlLine.dispatch(SqlLine.java:791)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
Caused by: java.lang.RuntimeException:
org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class
'org.postgresql.Driver'
at
org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.get(JdbcUtils.java:88)
at
org.apache.calcite.adapter.jdbc.JdbcSchema.createDialect(JdbcSchema.java:149)
at
org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:107)
at
org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:143)
at
org.apache.calcite.adapter.jdbc.JdbcSchema$Factory.create(JdbcSchema.java:446)
at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:269)
I thought doing an mvn install will make a difference and executed the
command -
mvn install -DskipTests -Dcheckstyle.skip=true
But it gives the error -
[INFO] --- maven-dependency-plugin:2.10:analyze-only (analyze) @
calcite-example-csv ---
[WARNING] Unused declared dependencies found:
[WARNING] org.postgresql:postgresql:jar:9.4-1200-jdbc41:compile
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 29.911 s
[INFO] Finished at: 2017-10-03T01:22:25+01:00
[INFO] Final Memory: 22M/167M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal
org.apache.maven.plugins:maven-dependency-plugin:2.10:analyze-only
(analyze) on project calcite-example-csv: Dependency problems found ->
[Help 1]
Below is the postgres dependency I have used in the pom.xml file -
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4-1200-jdbc41</version>
</dependency>
Could you please help here.
Thanks,
Ashwin
On Tue, Oct 3, 2017 at 12:45 AM, Julian Hyde <jh...@apache.org> wrote:
> Looks good, except that I’d change
>
> > "defaultSchema": "SALES Samplepostgres”,
>
> to
>
> > "defaultSchema": "SALES”,
>
> Julian
>
> > On Oct 2, 2017, at 2:38 PM, AshwinKumar AshwinKumar <
> aashwin@g.clemson.edu> wrote:
> >
> > Sorry this is the proper json file -
> >
> > {
> > "version": "1.0",
> > "defaultSchema": "SALES Samplepostgres",
> > "schemas": [
> > {
> > "name": "SALES",
> > "type": "custom",
> > "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> > "operand": {
> > "directory": "sales"
> > }
> > },
> > {
> > "name": "Samplepostgres",
> > "type": "custom",
> > "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
> > "operand": {
> > "jdbcDriver": "org.postgresql.Driver",
> > "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> > "jdbcUser": "pguser",
> > "jdbcPassword": "test"
> > }
> > }
> > ]
> >
> >
> > Thanks,
> > Ashwin
> >
> > On Mon, Oct 2, 2017 at 10:37 PM, AshwinKumar AshwinKumar <
> > aashwin@g.clemson.edu> wrote:
> >
> >> Hi ,
> >>
> >> Do you mean to say, I need to change the model.json file present in the
> >> examples directory. I have edited the existing model.json file present
> in
> >> the example/csv/target/test-classes to include the postgres schema .
> But
> >> I am not able to connect . Below is the edited json just to include
> >> postgres -
> >>
> >> {
> >> "version": "1.0",
> >> "defaultSchema": "SALES Samplepostgres",
> >> "schemas": [
> >> {
> >> "name": "SALES",
> >> "type": "custom",
> >> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> >> "operand": {
> >> "directory": "sales"
> >> }
> >> },
> >> {
> >> "name": "Samplepostgres",
> >> "type": "custom",
> >> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
> >> "version": "1.0",
> >> "defaultSchema": "SALES Samplepostgres",
> >> "schemas": [
> >> {
> >> "name": "SALES",
> >> "type": "custom",
> >> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> >> "operand": {
> >> "directory": "sales"
> >> }
> >> },
> >> {
> >> "name": "Samplepostgres",
> >> "type": "custom",
> >> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
> >> "operand": {
> >> "jdbcDriver": "org.postgresql.Driver",
> >> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> >> "jdbcUser": "pguser",
> >> "jdbcPassword": "test"
> >> }
> >> }
> >> ]
> >> }
> >> tory",
> >> "operand": {
> >> "jdbcDriver": "org.postgresql.Driver",
> >> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> >> "jdbcUser": "pguser",
> >> "jdbcPassword": "test"
> >> }
> >> }
> >> ]
> >> }
> >>
> >> Could you please let me know if it is correct. I am getting an error -
> >>
> >> sqlline version 1.3.0
> >> sqlline> select * from table3;
> >> No current connection
> >>
> >> The table table3 is present in the postgres database.
> >>
> >> Thanks,
> >> Ashwin
> >>
> >>
> >>
> >>
> >>
> >> On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org> wrote:
> >>
> >>> Easiest thing is to create a JSON model file with two schemas in it.
> >>>
> >>> https://calcite.apache.org/docs/model.html <
> https://calcite.apache.org/do
> >>> cs/model.html>
> >>>
> >>> https://calcite.apache.org/docs/adapter.html <
> >>> https://calcite.apache.org/docs/adapter.html>
> >>>
> >>>
> >>>> On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
> >>> aashwin@g.clemson.edu> wrote:
> >>>>
> >>>> Could you please give me some pointers for where can I get some
> >>>> documentation or links on how to connect elastisearch and postgres in
> >>> same
> >>>> query.
> >>>> As we know Elastisearch has a separate adapter and postgres comes
> under
> >>>> jdbc adapter. Do we need to create a root schema using jdbc adapter
> for
> >>>> elastisearch instance.
> >>>>
> >>>> Thanks,
> >>>> Ashwin
> >>>>
> >>>> On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
> >>>>
> >>>>> Yes, you should be able to combine any data source with any other
> data
> >>>>> source in a query.
> >>>>>
> >>>>> Julian
> >>>>>
> >>>>>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
> >>>>> aashwin@g.clemson.edu> wrote:
> >>>>>>
> >>>>>> Hello Team,
> >>>>>>
> >>>>>> I had a question regarding connecting Elastisearch and Postgres
> >>> Database
> >>>>> in
> >>>>>> a single query. Could you please let me know If we can query a
> >>>>> elastisearch
> >>>>>> type(table) and a postgres table in the same query.
> >>>>>>
> >>>>>> Thanks,
> >>>>>> Ashwin
> >>>>>
> >>>>>
> >>>
> >>>
> >>
>
>
Re: Joining Elastisearch types and Postgres Database tables
Posted by Julian Hyde <jh...@apache.org>.
Looks good, except that I’d change
> "defaultSchema": "SALES Samplepostgres”,
to
> "defaultSchema": "SALES”,
Julian
> On Oct 2, 2017, at 2:38 PM, AshwinKumar AshwinKumar <aa...@g.clemson.edu> wrote:
>
> Sorry this is the proper json file -
>
> {
> "version": "1.0",
> "defaultSchema": "SALES Samplepostgres",
> "schemas": [
> {
> "name": "SALES",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> "operand": {
> "directory": "sales"
> }
> },
> {
> "name": "Samplepostgres",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
> "operand": {
> "jdbcDriver": "org.postgresql.Driver",
> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> "jdbcUser": "pguser",
> "jdbcPassword": "test"
> }
> }
> ]
>
>
> Thanks,
> Ashwin
>
> On Mon, Oct 2, 2017 at 10:37 PM, AshwinKumar AshwinKumar <
> aashwin@g.clemson.edu> wrote:
>
>> Hi ,
>>
>> Do you mean to say, I need to change the model.json file present in the
>> examples directory. I have edited the existing model.json file present in
>> the example/csv/target/test-classes to include the postgres schema . But
>> I am not able to connect . Below is the edited json just to include
>> postgres -
>>
>> {
>> "version": "1.0",
>> "defaultSchema": "SALES Samplepostgres",
>> "schemas": [
>> {
>> "name": "SALES",
>> "type": "custom",
>> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
>> "operand": {
>> "directory": "sales"
>> }
>> },
>> {
>> "name": "Samplepostgres",
>> "type": "custom",
>> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
>> "version": "1.0",
>> "defaultSchema": "SALES Samplepostgres",
>> "schemas": [
>> {
>> "name": "SALES",
>> "type": "custom",
>> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
>> "operand": {
>> "directory": "sales"
>> }
>> },
>> {
>> "name": "Samplepostgres",
>> "type": "custom",
>> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
>> "operand": {
>> "jdbcDriver": "org.postgresql.Driver",
>> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
>> "jdbcUser": "pguser",
>> "jdbcPassword": "test"
>> }
>> }
>> ]
>> }
>> tory",
>> "operand": {
>> "jdbcDriver": "org.postgresql.Driver",
>> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
>> "jdbcUser": "pguser",
>> "jdbcPassword": "test"
>> }
>> }
>> ]
>> }
>>
>> Could you please let me know if it is correct. I am getting an error -
>>
>> sqlline version 1.3.0
>> sqlline> select * from table3;
>> No current connection
>>
>> The table table3 is present in the postgres database.
>>
>> Thanks,
>> Ashwin
>>
>>
>>
>>
>>
>> On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org> wrote:
>>
>>> Easiest thing is to create a JSON model file with two schemas in it.
>>>
>>> https://calcite.apache.org/docs/model.html <https://calcite.apache.org/do
>>> cs/model.html>
>>>
>>> https://calcite.apache.org/docs/adapter.html <
>>> https://calcite.apache.org/docs/adapter.html>
>>>
>>>
>>>> On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
>>> aashwin@g.clemson.edu> wrote:
>>>>
>>>> Could you please give me some pointers for where can I get some
>>>> documentation or links on how to connect elastisearch and postgres in
>>> same
>>>> query.
>>>> As we know Elastisearch has a separate adapter and postgres comes under
>>>> jdbc adapter. Do we need to create a root schema using jdbc adapter for
>>>> elastisearch instance.
>>>>
>>>> Thanks,
>>>> Ashwin
>>>>
>>>> On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
>>>>
>>>>> Yes, you should be able to combine any data source with any other data
>>>>> source in a query.
>>>>>
>>>>> Julian
>>>>>
>>>>>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
>>>>> aashwin@g.clemson.edu> wrote:
>>>>>>
>>>>>> Hello Team,
>>>>>>
>>>>>> I had a question regarding connecting Elastisearch and Postgres
>>> Database
>>>>> in
>>>>>> a single query. Could you please let me know If we can query a
>>>>> elastisearch
>>>>>> type(table) and a postgres table in the same query.
>>>>>>
>>>>>> Thanks,
>>>>>> Ashwin
>>>>>
>>>>>
>>>
>>>
>>
Re: Joining Elastisearch types and Postgres Database tables
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Sorry this is the proper json file -
{
"version": "1.0",
"defaultSchema": "SALES Samplepostgres",
"schemas": [
{
"name": "SALES",
"type": "custom",
"factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
"operand": {
"directory": "sales"
}
},
{
"name": "Samplepostgres",
"type": "custom",
"factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
"operand": {
"jdbcDriver": "org.postgresql.Driver",
"jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
"jdbcUser": "pguser",
"jdbcPassword": "test"
}
}
]
Thanks,
Ashwin
On Mon, Oct 2, 2017 at 10:37 PM, AshwinKumar AshwinKumar <
aashwin@g.clemson.edu> wrote:
> Hi ,
>
> Do you mean to say, I need to change the model.json file present in the
> examples directory. I have edited the existing model.json file present in
> the example/csv/target/test-classes to include the postgres schema . But
> I am not able to connect . Below is the edited json just to include
> postgres -
>
> {
> "version": "1.0",
> "defaultSchema": "SALES Samplepostgres",
> "schemas": [
> {
> "name": "SALES",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> "operand": {
> "directory": "sales"
> }
> },
> {
> "name": "Samplepostgres",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
> "version": "1.0",
> "defaultSchema": "SALES Samplepostgres",
> "schemas": [
> {
> "name": "SALES",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
> "operand": {
> "directory": "sales"
> }
> },
> {
> "name": "Samplepostgres",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
> "operand": {
> "jdbcDriver": "org.postgresql.Driver",
> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> "jdbcUser": "pguser",
> "jdbcPassword": "test"
> }
> }
> ]
> }
> tory",
> "operand": {
> "jdbcDriver": "org.postgresql.Driver",
> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> "jdbcUser": "pguser",
> "jdbcPassword": "test"
> }
> }
> ]
> }
>
> Could you please let me know if it is correct. I am getting an error -
>
> sqlline version 1.3.0
> sqlline> select * from table3;
> No current connection
>
> The table table3 is present in the postgres database.
>
> Thanks,
> Ashwin
>
>
>
>
>
> On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org> wrote:
>
>> Easiest thing is to create a JSON model file with two schemas in it.
>>
>> https://calcite.apache.org/docs/model.html <https://calcite.apache.org/do
>> cs/model.html>
>>
>> https://calcite.apache.org/docs/adapter.html <
>> https://calcite.apache.org/docs/adapter.html>
>>
>>
>> > On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
>> aashwin@g.clemson.edu> wrote:
>> >
>> > Could you please give me some pointers for where can I get some
>> > documentation or links on how to connect elastisearch and postgres in
>> same
>> > query.
>> > As we know Elastisearch has a separate adapter and postgres comes under
>> > jdbc adapter. Do we need to create a root schema using jdbc adapter for
>> > elastisearch instance.
>> >
>> > Thanks,
>> > Ashwin
>> >
>> > On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
>> >
>> >> Yes, you should be able to combine any data source with any other data
>> >> source in a query.
>> >>
>> >> Julian
>> >>
>> >>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
>> >> aashwin@g.clemson.edu> wrote:
>> >>>
>> >>> Hello Team,
>> >>>
>> >>> I had a question regarding connecting Elastisearch and Postgres
>> Database
>> >> in
>> >>> a single query. Could you please let me know If we can query a
>> >> elastisearch
>> >>> type(table) and a postgres table in the same query.
>> >>>
>> >>> Thanks,
>> >>> Ashwin
>> >>
>> >>
>>
>>
>
Re: Joining Elastisearch types and Postgres Database tables
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Hi ,
Do you mean to say, I need to change the model.json file present in the
examples directory. I have edited the existing model.json file present in
the example/csv/target/test-classes to include the postgres schema . But I
am not able to connect . Below is the edited json just to include postgres
-
{
"version": "1.0",
"defaultSchema": "SALES Samplepostgres",
"schemas": [
{
"name": "SALES",
"type": "custom",
"factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
"operand": {
"directory": "sales"
}
},
{
"name": "Samplepostgres",
"type": "custom",
"factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Fac{
"version": "1.0",
"defaultSchema": "SALES Samplepostgres",
"schemas": [
{
"name": "SALES",
"type": "custom",
"factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory",
"operand": {
"directory": "sales"
}
},
{
"name": "Samplepostgres",
"type": "custom",
"factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
"operand": {
"jdbcDriver": "org.postgresql.Driver",
"jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
"jdbcUser": "pguser",
"jdbcPassword": "test"
}
}
]
}
tory",
"operand": {
"jdbcDriver": "org.postgresql.Driver",
"jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
"jdbcUser": "pguser",
"jdbcPassword": "test"
}
}
]
}
Could you please let me know if it is correct. I am getting an error -
sqlline version 1.3.0
sqlline> select * from table3;
No current connection
The table table3 is present in the postgres database.
Thanks,
Ashwin
On Mon, Oct 2, 2017 at 7:59 PM, Julian Hyde <jh...@apache.org> wrote:
> Easiest thing is to create a JSON model file with two schemas in it.
>
> https://calcite.apache.org/docs/model.html <https://calcite.apache.org/
> docs/model.html>
>
> https://calcite.apache.org/docs/adapter.html <https://calcite.apache.org/
> docs/adapter.html>
>
>
> > On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <
> aashwin@g.clemson.edu> wrote:
> >
> > Could you please give me some pointers for where can I get some
> > documentation or links on how to connect elastisearch and postgres in
> same
> > query.
> > As we know Elastisearch has a separate adapter and postgres comes under
> > jdbc adapter. Do we need to create a root schema using jdbc adapter for
> > elastisearch instance.
> >
> > Thanks,
> > Ashwin
> >
> > On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
> >
> >> Yes, you should be able to combine any data source with any other data
> >> source in a query.
> >>
> >> Julian
> >>
> >>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
> >> aashwin@g.clemson.edu> wrote:
> >>>
> >>> Hello Team,
> >>>
> >>> I had a question regarding connecting Elastisearch and Postgres
> Database
> >> in
> >>> a single query. Could you please let me know If we can query a
> >> elastisearch
> >>> type(table) and a postgres table in the same query.
> >>>
> >>> Thanks,
> >>> Ashwin
> >>
> >>
>
>
Re: Joining Elastisearch types and Postgres Database tables
Posted by Julian Hyde <jh...@apache.org>.
Easiest thing is to create a JSON model file with two schemas in it.
https://calcite.apache.org/docs/model.html <https://calcite.apache.org/docs/model.html>
https://calcite.apache.org/docs/adapter.html <https://calcite.apache.org/docs/adapter.html>
> On Oct 2, 2017, at 11:48 AM, AshwinKumar AshwinKumar <aa...@g.clemson.edu> wrote:
>
> Could you please give me some pointers for where can I get some
> documentation or links on how to connect elastisearch and postgres in same
> query.
> As we know Elastisearch has a separate adapter and postgres comes under
> jdbc adapter. Do we need to create a root schema using jdbc adapter for
> elastisearch instance.
>
> Thanks,
> Ashwin
>
> On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
>
>> Yes, you should be able to combine any data source with any other data
>> source in a query.
>>
>> Julian
>>
>>> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
>> aashwin@g.clemson.edu> wrote:
>>>
>>> Hello Team,
>>>
>>> I had a question regarding connecting Elastisearch and Postgres Database
>> in
>>> a single query. Could you please let me know If we can query a
>> elastisearch
>>> type(table) and a postgres table in the same query.
>>>
>>> Thanks,
>>> Ashwin
>>
>>
Re: Joining Elastisearch types and Postgres Database tables
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Could you please give me some pointers for where can I get some
documentation or links on how to connect elastisearch and postgres in same
query.
As we know Elastisearch has a separate adapter and postgres comes under
jdbc adapter. Do we need to create a root schema using jdbc adapter for
elastisearch instance.
Thanks,
Ashwin
On Mon, Oct 2, 2017 at 2:44 PM, Julian Hyde <jh...@apache.org> wrote:
> Yes, you should be able to combine any data source with any other data
> source in a query.
>
> Julian
>
> > On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <
> aashwin@g.clemson.edu> wrote:
> >
> > Hello Team,
> >
> > I had a question regarding connecting Elastisearch and Postgres Database
> in
> > a single query. Could you please let me know If we can query a
> elastisearch
> > type(table) and a postgres table in the same query.
> >
> > Thanks,
> > Ashwin
>
>
Re: Joining Elastisearch types and Postgres Database tables
Posted by Julian Hyde <jh...@apache.org>.
Yes, you should be able to combine any data source with any other data source in a query.
Julian
> On Oct 2, 2017, at 11:40 AM, AshwinKumar AshwinKumar <aa...@g.clemson.edu> wrote:
>
> Hello Team,
>
> I had a question regarding connecting Elastisearch and Postgres Database in
> a single query. Could you please let me know If we can query a elastisearch
> type(table) and a postgres table in the same query.
>
> Thanks,
> Ashwin