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/01 19:22:46 UTC
Re: To get started with Postgres and SqlServer
Hi Team,
Let me elaborate the issue I am facing.
I have two databases , one on postgres engine and one on sqlserver engine.
The sqlserver database has a table -
table4 with the records -
select * from table4 -
[image: Inline image 2]
The postgres engine has the table table 3 -
with records -
select * from table 3 -
[image: Inline image 3]
I am trying to create a connection to both the databases using apache
calcite and query both the tables in one single query. below is the code -
package test.calcite_rdbms;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;
/**
* This class demonstrates Calcite unable to recognize tables in Postgres
on
* Mac OS X 10.11.5 with Calcite 1.7.0, Postgres 9.5.2.0 and Java 1.8.0_77.
*
* Before you run this class, you must create the user and database in
* Postgres by executing the following SQL:
*
* create user johnsnow with password 'password';
* create database db1 with owner johnsnow;
*
*/
public class postgresTests {
public static void main(String[] args) throws SQLException,
ClassNotFoundException {
final String dbUrl =
"jdbc:sqlserver://localhost;databaseName=testcal";
final String dbUrl2 = "jdbc:postgresql://localhost/bigdawg_catalog";
/*Connection con = DriverManager.getConnection(dbUrl, "testAdmin",
"test@1234");
Statement stmt1 = con.createStatement();
stmt1.execute("drop table if exists table2");
stmt1.execute("create table table2(id varchar(100) not null primary
key, field1 varchar(500))");
stmt1.execute("insert into table2 values('a', 'aaaa')");
stmt1.execute("insert into table2 values('b', 'bbbb')");
con.close();*/
Connection connection =
DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
final DataSource ds = JdbcSchema.dataSource(dbUrl,
"com.microsoft.sqlserver.jdbc.SQLServerDriver", "testAdmin", "test@1234");
final DataSource ds2 = JdbcSchema.dataSource(dbUrl2,
"org.postgresql.Driver", "pguser", "test");
rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", ds,
null, null));
rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2", ds2,
null, null));
Statement stmt3 = connection.createStatement();
ResultSet rs = stmt3.executeQuery("select a.name from
DB2.\"table3\" as a join DB1.\"table4\" as b on a.empno = b.id ");
while (rs.next()) {
System.out.println(rs.getString(1) + '=' + rs.getString(2));
}
}
}
Now when I try to run this , It errors out saying -
Exception in thread "main" java.sql.SQLException: Error while executing SQL
"select a.name from DB2."table3" as a join DB1."table4" as b on a.empno =
b.id ": From line 1, column 66 to line 1, column 70: Column 'EMPNO' not
found in table 'A'
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
As seen above it shows there is no column called empno in table a(which is
an alias). Could you please le me know If anything wrong with the query
syntax in the stmt3.executeQuery(). I tried almost everything. Could you
please look into this.
Thanks,
Ashwin
On Mon, Sep 25, 2017 at 6:18 PM, Josh Elser <el...@apache.org> wrote:
> Apache mailing lists strip attachments for spam/security reasons.
>
> Please use any number of online services to post a log. Even better,
> please distill the error down to something you can include inline in an
> email.
>
> Thanks.
>
> On 9/25/17 10:26 AM, AshwinKumar AshwinKumar wrote:
>
>> Hi Julian,
>>
>> PFA the error log .
>>
>> Could you please let me know if I am following the syntax correctly.
>> Thanks,
>> Ashwin
>>
>> On Sun, Sep 24, 2017 at 9:35 PM, Julian Hyde <jhyde.apache@gmail.com
>> <ma...@gmail.com>> wrote:
>>
>> That looks good. You’ve created a schema for each data source, then
>> written a query that uses a table from each schema.
>>
>> Does the query work? If not, what is the error?
>>
>> Julian
>>
>> > On Sep 23, 2017, at 20:28, AshwinKumar AshwinKumar <
>> aashwin@g.clemson.edu <ma...@g.clemson.edu>> wrote:
>> >
>> > Hi Julian,
>> >
>> > I am following the links below -
>> >
>> > https://calcite.apache.org/docs/index.html
>> <https://calcite.apache.org/docs/index.html>
>> >
>> > *https://calcite.apache.org/docs/howto.html
>> <https://calcite.apache.org/docs/howto.html>
>> > <https://calcite.apache.org/docs/howto.html
>> <https://calcite.apache.org/docs/howto.html>>*
>> >
>> > I was able to run a basic java class which connects to the
>> postgresql and
>> > sqlserver engines. I was able to fire query on each of the engines
>> > separately. Now I want to query both the engines in a single
>> query. Could
>> > you please let me know if there is any special syntax for the
>> same. I was
>> > not able to find any examples where we are referring both the
>> engines in a
>> > same query. For eg : I have table1 on sqlserver and table 2 on
>> postgres .
>> > How to query both the tables at once. I have written the below
>> java class -
>> >
>> > import javax.sql.DataSource;
>> >
>> > import org.apache.calcite.adapter.jdbc.JdbcSchema;
>> > import org.apache.calcite.jdbc.CalciteConnection;
>> > import org.apache.calcite.schema.SchemaPlus;
>> >
>> > /
>> > public class postgresTests {
>> > public static void main(String[] args) throws SQLException,
>> > ClassNotFoundException {
>> > final String dbUrl = "jdbc:sqlserver://localhost;da
>> > tabaseName=testcal";
>> > final String dbUrl2 =
>> "jdbc:postgresql://localhost/bigdawg_catalog";
>> >
>> > Connection con = DriverManager.getConnection(dbUrl,
>> "testAdmin",
>> > "test@1234");
>> > Statement stmt1 = con.createStatement();
>> > stmt1.execute("drop table if exists table2");
>> > stmt1.execute("create table table2(id varchar(100) not
>> null primary
>> > key, field1 varchar(500))");
>> > stmt1.execute("insert into table2 values('a', 'aaaa')");
>> > stmt1.execute("insert into table2 values('b', 'bbbb')");
>> > con.close();
>> >
>> > Connection connection = DriverManager.getConnection("j
>> > dbc:calcite:");
>> > CalciteConnection calciteConnection =
>> connection.unwrap(CalciteConne
>> > ction.class);
>> > SchemaPlus rootSchema = calciteConnection.getRootSchema();
>> >
>> > final DataSource ds = JdbcSchema.dataSource(dbUrl,
>> > "com.microsoft.sqlserver.jdbc.SQLServerDriver", "testAdmin",
>> "test@1234");
>> > final DataSource ds2 = JdbcSchema.dataSource(dbUrl2,
>> > "org.postgresql.Driver", "pguser", "test");
>> > rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1",
>> ds,
>> > null, null));
>> > rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2",
>> ds2,
>> > null, null));
>> > Statement stmt3 = connection.createStatement();
>> > ResultSet rs = stmt3.executeQuery("select * from
>> \"DB1\".\"table2\"
>> > as a,DB2.\"table1\" as b where b.id <http://b.id> = \"a\"");
>> >
>> > while (rs.next()) {
>> > System.out.println(rs.getString(1) + '=' +
>> rs.getString(2));
>> > }
>> >
>> > }
>> > }
>> >
>> > could you please let me know if the syntax mentioned in
>> executeQuery () is
>> > proper one.
>> >
>> > Thanks,
>> > Ashwin
>> >
>> >
>> >
>> >
>> >
>> > On Sep 23, 2017 6:52 PM, "Julian Hyde" <jhyde@apache.org
>> <ma...@apache.org>> wrote:
>> >
>> > What documentation did you read already?
>> >
>> > Julian
>> >
>> >
>> > On Sat, Sep 23, 2017 at 12:03 PM, AshwinKumar AshwinKumar
>> > <aashwin@g.clemson.edu <ma...@g.clemson.edu>> wrote:
>> >> Hi ,
>> >>
>> >>
>> >> I am completely new to apache calcite. I have just installed
>> apache
>> > calcite
>> >> (version - calcite - 1.13.0) using the mvn install command.
>> Could you
>> >> please let me know the steps to connect to a postgres and
>> sqlserver
>> >> instance through calcite. I want to how to get started with
>> this. Could
>> >> you please send some pointers.
>> >>
>> >> Thanks,
>> >> Ashwin
>>
>>
>>
Re: Assunto: Re: To get started with Postgres and SqlServer
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Thanks Luis,
That worked. I created the table with column names inuppercase.
On Sun, Oct 1, 2017 at 6:38 PM, Luis Fernando Kauer <lf...@yahoo.com.br>
wrote:
> Hi.
>
> Have you tried a simple query that selects the empno column from table3?
> Calcite converts column names to uppercase and it is case sensitive.
> I think that is your problem.
> Try it in uppercase or use double quotes for exact match.
>
>
> Enviado do Yahoo Mail no Android
> <https://overview.mail.yahoo.com/mobile/?.src=Android>
>
> Em dom, 1 1e out 1e 2017 às 16:23, AshwinKumar AshwinKumar
> &It;aashwin@g.clemson.edu> escreveu:
> Hi Team,
>
> Let me elaborate the issue I am facing.
>
> I have two databases , one on postgres engine and one on sqlserver engine.
> The sqlserver database has a table -
>
> table4 with the records -
> select * from table4 -
>
>
> [image: Inline image 2]
>
> The postgres engine has the table table 3 -
> with records -
>
> select * from table 3 -
>
>
> [image: Inline image 3]
>
> I am trying to create a connection to both the databases using apache
> calcite and query both the tables in one single query. below is the code -
> package test.calcite_rdbms;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> import javax.sql.DataSource;
>
> import org.apache.calcite.adapter.jdbc.JdbcSchema;
> import org.apache.calcite.jdbc.CalciteConnection;
> import org.apache.calcite.schema.SchemaPlus;
>
> /**
> * This class demonstrates Calcite unable to recognize tables in Postgres
> on
> * Mac OS X 10.11.5 with Calcite 1.7.0, Postgres 9.5.2.0 and Java 1.8.0_77.
> *
> * Before you run this class, you must create the user and database in
> * Postgres by executing the following SQL:
> *
> * create user johnsnow with password 'password';
> * create database db1 with owner johnsnow;
> *
> */
> public class postgresTests {
> public static void main(String[] args) throws SQLException,
> ClassNotFoundException {
> final String dbUrl = "jdbc:sqlserver://localhost;
> databaseName=testcal";
> final String dbUrl2 = "jdbc:postgresql://localhost/
> bigdawg_catalog";
>
> /*Connection con = DriverManager.getConnection(dbUrl,
> "testAdmin", "test@1234");
> Statement stmt1 = con.createStatement();
> stmt1.execute("drop table if exists table2");
> stmt1.execute("create table table2(id varchar(100) not null
> primary key, field1 varchar(500))");
> stmt1.execute("insert into table2 values('a', 'aaaa')");
> stmt1.execute("insert into table2 values('b', 'bbbb')");
> con.close();*/
>
> Connection connection = DriverManager.getConnection("
> jdbc:calcite:");
> CalciteConnection calciteConnection = connection.unwrap(
> CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema();
>
> final DataSource ds = JdbcSchema.dataSource(dbUrl,
> "com.microsoft.sqlserver.jdbc.SQLServerDriver", "testAdmin", "test@1234");
> final DataSource ds2 = JdbcSchema.dataSource(dbUrl2,
> "org.postgresql.Driver", "pguser", "test");
> rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", ds,
> null, null));
> rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2", ds2,
> null, null));
> Statement stmt3 = connection.createStatement();
> ResultSet rs = stmt3.executeQuery("select a.name from
> DB2.\"table3\" as a join DB1.\"table4\" as b on a.empno = b.id ");
>
> while (rs.next()) {
> System.out.println(rs.getString(1) + '=' + rs.getString(2));
> }
>
> }
> }
>
> Now when I try to run this , It errors out saying -
>
> Exception in thread "main" java.sql.SQLException: Error while executing
> SQL "select a.name from DB2."table3" as a join DB1."table4" as b on
> a.empno = b.id ": From line 1, column 66 to line 1, column 70: Column
> 'EMPNO' not found in table 'A'
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(
> AvaticaStatement.java:143)
> at org.apache.calcite.avatica.AvaticaStatement.executeQuery(
> AvaticaStatement.java:186)
>
> As seen above it shows there is no column called empno in table a(which is
> an alias). Could you please le me know If anything wrong with the query
> syntax in the stmt3.executeQuery(). I tried almost everything. Could you
> please look into this.
>
> Thanks,
> Ashwin
>
>
>
>
>
>
>
> On Mon, Sep 25, 2017 at 6:18 PM, Josh Elser <el...@apache.org> wrote:
>
> Apache mailing lists strip attachments for spam/security reasons.
>
> Please use any number of online services to post a log. Even better,
> please distill the error down to something you can include inline in an
> email.
>
> Thanks.
>
> On 9/25/17 10:26 AM, AshwinKumar AshwinKumar wrote:
>
> Hi Julian,
>
> PFA the error log .
>
> Could you please let me know if I am following the syntax correctly.
> Thanks,
> Ashwin
>
> On Sun, Sep 24, 2017 at 9:35 PM, Julian Hyde <jhyde.apache@gmail.com
> <mailto:jhyde.apache@gmail.com >> wrote:
>
> That looks good. You’ve created a schema for each data source, then
> written a query that uses a table from each schema.
>
> Does the query work? If not, what is the error?
>
> Julian
>
> > On Sep 23, 2017, at 20:28, AshwinKumar AshwinKumar <
> aashwin@g.clemson.edu <ma...@g.clemson.edu> > wrote:
> >
> > Hi Julian,
> >
> > I am following the links below -
> >
> > https://calcite.apache.org/doc s/index.html
> <https://calcite.apache.org/docs/index.html>
> <https://calcite.apache.org/do cs/index.html
> <https://calcite.apache.org/docs/index.html>>
> >
> > *https://calcite.apache.org/do cs/howto.html
> <https://calcite.apache.org/docs/howto.html>
> <https://calcite.apache.org/do cs/howto.html
> <https://calcite.apache.org/docs/howto.html>>
> > <https://calcite.apache.org/do cs/howto.html
> <https://calcite.apache.org/docs/howto.html>
> <https://calcite.apache.org/do cs/howto.html
> <https://calcite.apache.org/docs/howto.html>>>*
> >
> > I was able to run a basic java class which connects to the
> postgresql and
> > sqlserver engines. I was able to fire query on each of the engines
> > separately. Now I want to query both the engines in a single
> query. Could
> > you please let me know if there is any special syntax for the
> same. I was
> > not able to find any examples where we are referring both the
> engines in a
> > same query. For eg : I have table1 on sqlserver and table 2 on
> postgres .
> > How to query both the tables at once. I have written the below
> java class -
> >
> > import javax.sql.DataSource;
> >
> > import org.apache.calcite.adapter.jdb c.JdbcSchema;
> > import org.apache.calcite.jdbc.Calcit eConnection;
> > import org.apache.calcite.schema.Sche maPlus;
> >
> > /
> > public class postgresTests {
> > public static void main(String[] args) throws SQLException,
> > ClassNotFoundException {
> > final String dbUrl = "jdbc:sqlserver://localhost;da
> > tabaseName=testcal";
> > final String dbUrl2 =
> "jdbc:postgresql://localhost/b igdawg_catalog";
> >
> > Connection con = DriverManager.getConnection(db Url,
> "testAdmin",
> > "test@1234");
> > Statement stmt1 = con.createStatement();
> > stmt1.execute("drop table if exists table2");
> > stmt1.execute("create table table2(id varchar(100) not
> null primary
> > key, field1 varchar(500))");
> > stmt1.execute("insert into table2 values('a', 'aaaa')");
> > stmt1.execute("insert into table2 values('b', 'bbbb')");
> > con.close();
> >
> > Connection connection = DriverManager.getConnection("j
> > dbc:calcite:");
> > CalciteConnection calciteConnection =
> connection.unwrap(CalciteConne
> > ction.class);
> > SchemaPlus rootSchema = calciteConnection.getRootSchem a();
> >
> > final DataSource ds = JdbcSchema.dataSource(dbUrl,
> > "com.microsoft.sqlserver.jdbc. SQLServerDriver", "testAdmin",
> "test@1234");
> > final DataSource ds2 = JdbcSchema.dataSource(dbUrl2,
> > "org.postgresql.Driver", "pguser", "test");
> > rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1",
> ds,
> > null, null));
> > rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2",
> ds2,
> > null, null));
> > Statement stmt3 = connection.createStatement();
> > ResultSet rs = stmt3.executeQuery("select * from
> \"DB1\".\"table2\"
> > as a,DB2.\"table1\" as b where b.id <http://b.id> = \"a\"");
> >
> > while (rs.next()) {
> > System.out.println(rs.getStrin g(1) + '=' +
> rs.getString(2));
> > }
> >
> > }
> > }
> >
> > could you please let me know if the syntax mentioned in
> executeQuery () is
> > proper one.
> >
> > Thanks,
> > Ashwin
> >
> >
> >
> >
> >
> > On Sep 23, 2017 6:52 PM, "Julian Hyde" <jhyde@apache.org
> <ma...@apache.org>> wrote:
> >
> > What documentation did you read already?
> >
> > Julian
> >
> >
> > On Sat, Sep 23, 2017 at 12:03 PM, AshwinKumar AshwinKumar
> > <aashwin@g.clemson.edu <ma...@g.clemson.edu> > wrote:
> >> Hi ,
> >>
> >>
> >> I am completely new to apache calcite. I have just installed apache
> > calcite
> >> (version - calcite - 1.13.0) using the mvn install command.
> Could you
> >> please let me know the steps to connect to a postgres and sqlserver
> >> instance through calcite. I want to how to get started with
> this. Could
> >> you please send some pointers.
> >>
> >> Thanks,
> >> Ashwin
>
>
>
>