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.
.png)
😀 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
.png)
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
.png)
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
.png)
.png)
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. 😁
.png)
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. )
.png)
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
.png)
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'
.png)
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'
.png)
Let's check the result from the table list
.png)
.png)
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
.png)
35 Outlets are returned!!! Yeah!! We made it.🤣
Only for your reference
Dropping the Foreign Table demo.outlet
DROP FOREIGN TABLE demo.outlet
.png)
Dropping the Foreign Server Sample.testDB
DROP FOREIGN SERVER Sample.testDB CASCADE
.png)
That's all I want to share!!! Thanks for reading😘
.png)