Create Foreign Tables Using SQL via JDBC

Hi everyone. Long time no see. Again, I would like to share what I am studying recently Create Foreign Tables Using SQL via JDBC.

Since I am learning about the IRIS BI at the same time, so I started with this Sample-BI docker environment

https://github.com/intersystems/Samples-BI

 

After starting the environment, login to the management portal http://localhost:52773/csp/sys/UtilHome.csp  with the user Superuser.

Switch the namespace to IRISAPP.

 

😀 We will find out that all the sample data are stored in this namespace IRISAPP. Let's find a simple table for testing the Foreign Table feature 🤣

System Explorer > SQL

 

Let's try the table HoleFoods.Outlet by Executing the following SQL query in the Execute Query tab

SELECT
ID, City, Country, Latitude, Longitude, Population, Type
FROM HoleFoods.Outlet

 

Great!!😉 there are 35 entries in this table. Let's use it.

 

---

Step1  -  Setup the JDBC connection

By studying the document below, InterSystems IRIS db can be connected by JDBC by the following way

Using the SQL Gateway as a JDBC Data Source

So, let's using the Namespace IRISAPP as the JDBC source database for testing, and set up a connection for it.

Add a new connection by System  > Configuration  > SQL Gateway Connections 

 

Input the following

Leave Class path blank in this case, because is connecting to InterSystems IRIS DB. If connecting to the 3rd party DB, remember to download the JDBC driver file (.jar) and specific the path of the drive in the Class path filed.

 

Save and Test the Connection. Connection successful should be expected. 😁

Yeah!!! we finished the 1st step.

 

---

Step 2 - Create Foreign Table

Now switch to another Namespace USER, which do no contain the table HoleFoods.Outlet. 😉

Here we would like to query this external table HoleFoods.Outlet by making use the feature of Foreign Table. (In InterSystems IRIS, different Namespace, can be config to point to different DB. In this example, Namespace IRISAPP and Namespace USER are pointing to different DB. As a result, we cannot query the table Namespace IRISAPP from Namespace USER directly. )

 

By studying the document below, we can directly create foreign table by SQL.😁👍

Foreign Tables

So let's go to the SQL execution page again. System Explorer > SQL

 

1. Define a Foreign Server

Now we are going to define a Foreign Server Sample.testDB by the following SQL. testjdbc is the name that we defined in the SQL Gateway JDBC connection in the pervious step.

CREATE FOREIGN SERVER Sample.testDB FOREIGN DATA WRAPPER JDBC CONNECTION 'testjdbc'

 

 

2. Define a Foreign Table

Now we are going to define a Foreign Table demo.outlet, which is pointing to the table HoleFoods.Outlet through the JDBC gateway

 

CREATE FOREIGN TABLE demo.outlet SERVER Sample.testDB TABLE 'HoleFoods.Outlet'

Let's check the result from the table list

Yeah!!!!  A Foreign Table demo.outlet is created. Oooo the schema is read and created automatically btw😂Happy happy.

 

---

Finally, test the Foreign Table by running the following SQL

SELECT * FROM demo.outlet

 

 

35 Outlets are returned!!! Yeah!! We made it.🤣

 

---

Only for your reference

Dropping the Foreign Table demo.outlet 

DROP FOREIGN TABLE demo.outlet

 

 

Dropping the  Foreign Server Sample.testDB 

DROP FOREIGN SERVER Sample.testDB CASCADE

 

That's all I want to share!!! Thanks for reading😘