The Infor Communities is globally accessed system by
Infor and its customers, please do not post or upload
any content that could be deemed as sensitive, confidential or
subject to any data regulation requirements.
If you are unsure whether or not the content contains sensitive,
confidential or has any data regulation requirements,
please seek the advice of your internal security, legal, and compliance experts.
[How To] Setup the Compass JDBC Driver by Mike Kalinowski
Time to revisit some of the [How To] tutorials and I've seen quite a few members on the forum as well as meeting some of you ask how you can start using the Compass JDBC driver to experiment with development setup and querying the Data Lake. We'll take a look through how and where to access the download, setting up our authorized app in the ION API Gateway, and using a universal SQL editor to start querying our Data Lake.
Also, be sure to take a look @ KB 2103864 (Setup and configure the Infor Compass JDBC Driver) if you're looking to grab resources directly from the Infor Support Portal.
Downloading the JDBC Driver
Editor's Note: Before we get started, note that the Compass JDBC driver is only visible & accessible for download if you've purchased Infor entitlements for data egress out of the Cloud. Because the driver is ultimately moving data out of our cloud boundaries, data moved out is metered against this license. If you don't see the Compass JDBC driver available for download, reach out to your account rep and they can get you sorted out.
Let's jump over to the Infor Support Portal, log-in, and navigate over to Products > Downloads. A new tab should open to the Product Download Center. You'll see the GIF below probably has more download selections than you have available but you'll want to drill down to Infor SaaS Add-Ons > Technology > Infor OS (formerly Xi Platform) > Infor Compass JDBC Driver.
Just simply click the link on the .zip file and you'll begin your download. The .zip includes:
Because much of the platform is server-side, the JDBC interface is fairly thin and isn't updated with every Infor OS Cloud release. You'll often see, as is the case at the time of writing, that the version of the JDBC downloaded was released in December of 2019. We publish updated copies of the JDBC when new authentication options or client-side code is released.
I would recommend creating a new directory somewhere on your machine and copy the .jar file over to this new directory. For example:
Setting up our Authorized App
The JDBC doesn't use Basic Authentication (username and password) to authenticate against the Data Lake. Rather, we use a credentials file generated from the Infor ION API Gateway that impersonates our IFS user account. That means we'll have to create a new authorized app in the gateway and download a credentials .ionapi file built around a service account.
Navigate over to the App Switcher and select the Infor ION API application. In the left-hand navigation menu, select Authorized Apps and click the + to create a new application.
* You must use this name format when creating your authorized app. If you don't name this correctly, this may cause issues when trying to authenticate against the Data Lake.
Once you save your authorized app, select the Download Credentials button and hit the Create Service Account slider before entering in your IFS Username. Select the DOWNLOAD button and you'll now have the Infor Compass JDBC Driver.ionapi in your notebook's Download Folder.
Cut or copy your new .ionapi credentials file over to your C:\Infor Data Lake or ~/Library/Infor Data Lake directory
Configuring a Universal SQL Manager
Now, we've just got to download and/or configure your universal SQL manager of choice to connect to the Data Lake using the .jar & .ionapi files we've bundled together in our folder. There's a lot of options out there -
Keep in mind these are open source, 3rd party tools and Infor makes no claims around support, configurability, and so forth regarding usage of them.
We're going to be using DBeaver during this [How To] as it has a similar "look and feel" to other SQL products out there like Microsoft SQL Management Studio or Oracle SQL Navigator. We'll be creating our own custom driver reference within DBeaver that will connect back to the Infor Data Lake.
Let's launch DBeaver and from the application's menu, select Database and then Driver Manager. Select New and use the following information to enter in your driver details:
Select the OK button to complete set-up of your new driver.
Selecting the .jar file step may be more obvious than the reason why we're also selecting the parent directory hosting these files. By adding in the folder, DBeaver now knows which files and folders are being used to coordinate authentication on your behalf over to the server.
* Remember, you've set-up a service account within a specific tenant that'll be used to authenticate your session against that tenant. If your tenant name is "ACME", then your URL template would be jdbc:infordatalake://ACME
So, we've set up our driver and DBeaver now recognizes how to connect to the Data Lake. Let's go ahead and actually test to see whether we can get a connection to the Data Lake.
In DBeaver's Database Navigator pane on the left, select Create > Connection and filter for your new driver name. If you followed the naming convention above, filtering for "Infor Data Lake" should work here. If you don't see your driver listed as an option, make sure you're driver filter on the left is set to All and not Popular.
Select the Infor Data Lake and select the Next button. You'll see your JDBC URL already populated. Because we're using .ionapi to authenticate, you do not need to enter in a username or password. Simply select the Finish button.
Querying the Data Lake & Additional Notes
Equally familiar to most SQL management interfaces, you'll now see you have a connect to the Infor Data Lake within DBeaver's Navigator pane. You can start expanding the tree to see a list of all of the Tables within Data Catalog and even start querying the tables themselves, providing a relational view on top of your lake. All of the same queries you can run within Compass UI and APIs can be equally done here. Note that Views, Stored Procedures, and functions are not supported by the Data Lake at this time.
The Compass UI within Infor OS is an excellent tool for prototyping queries but there are limitations including a 100 record default limit at this time. Having the JDBC driver is a useful tool for developers and applications to begin working with the Data Lake and provide more robust dataset retrieval than is possible through the Compass browser interface.
I'll leave a final comment here - always remember that your .ionapi file is tied to the service account that's impersonating a user registered within IFS. The JDBC driver shouldn't be distributed to normal, everyday functional & power users because this is a heavily-oriented developer deliverable. I would certainly recommend that for any user that needs to use the JDBC driver (colleague developers), they follow the same steps for their own .ionapi generation