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 <[email protected]> > wrote: > > Hi Julian, > > I am following the links below - > > https://calcite.apache.org/docs/index.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 = \"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" <[email protected]> wrote: > > What documentation did you read already? > > Julian > > > On Sat, Sep 23, 2017 at 12:03 PM, AshwinKumar AshwinKumar > <[email protected]> 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
