Perform the below steps to ingest data from Microsoft Exchange using Infoworks
a. Download the MS Exchange CData driver from the CData website
https://www.cdata.com/drivers/exchange/download/
OR
You can avail of the trial version of the same JDBC jar from the below location.
http://cdatabuilds.s3.
b. UnZip the driver file, open a command-line interface and go to the download location.
c. Enter ‘java -jar setup.jar’ to extract the needed files. (ensure you have java installed on your machine)
This will provide the three files identified below.
d. In the Infoworks UI click on Admin and go to the Extensions screen.
e. Provide the Driver class as cdata.jdbc.exchange.ExchangeDriver, click Save
f. (Note: Ensure that you upload all the below mentioned files in the screenshot while getting the Driver extension created on Infoworks)
2. Steps to generate OAuthSettings.txt file and the JDBC URL using the driver-jar.
Double click on the cdata.jdbc.exchange.jar and it will open a CData JDBC Driver For Exchange 2020 window as shown below.
Provide the Authentication details as shown below in the screenshot. Provide the MS Exchange server details under Server and use the AuthScheme as OAuth.
You do not need to provide the User/Password in the OAuth section as we would be using the access token for authentication.
d. Under Miscellaneous(Misc section) select Other and provide the below values.
CallbackURL='https://localhost:33333';EncryptOAuthSettings=False;
The Callback URL will be used to get access while making the test connection from your local machine.
e.Provide the InitiateOAuth as GetANDREFRESH as you would need to generate the access token first using the JDBC jar first.
f. Generate the OAuthClientId and OAuthClientSecret by performing the steps mentioned in the below CData links.
g. OAuth requires the authenticating user to interact with Exchange using the browser. The driver facilitates this in various ways as described below. Go through the below CData documentation on how to create a Custom OAuth application for Headless machines.
http://cdn.cdata.com/help/CEF/jdbc/pg_oauth.htm
http://cdn.cdata.com/help/CEF/jdbc/pg_oauthcustomappcreate.htm
h. After creating the app, go to the Certificates & Secrets section, create a Client Secret for the app and select a duration.
i. After you save the key, a value for the key is displayed once. Set OAuthClientSecret to the key value. Set OAuthClientId to the Application Id.
j. Provide the OAuthSettingsLocation to a directory on your local machine where you want the OAuthSettings.txt file to be generated as shown above.
k.Click on Test Connection and you would be redirected to a webpage as shown below.
l. Provide your login credentials to log in to Microsoft Exchange and it shows that the Authorization is successful on the callback URL that you have provided.
m.This will also generate the JDBC URL and the OAuthSettings.txt file in the location that you have provided for OAuthSettingsLocation
n. Copy the connection string from the driver window.
Sample JDBC URL format
jdbc:exchange:AuthScheme="OAuth";User="";Password="";Server="https://admin.exchange.microsoft.com/#/";AzureTenant="";InitiateOAuth="GETANDREFRESH";OAuthClientId="3f87f804-3514-4696-a985-2c7d116fae8e";OAuthClientSecret="~U9AC-ne1f1.b68-JED7_xc4My6uAe7~y9";OAuthGrantType="CODE";OAuthSettingsLocation="/Applications/CData/OAuthSettings.txt";Other="CallbackURL='https://localhost:33333';EncryptOAuthSettings=False;";Schema="MSGraph";
3) Once you have the JDBC URL ready, Create the CData MS Exchange source providing the below-mentioned details in the screenshot.
Click on the Manage Sources link (under Admin)
Click Add New Source
Select the driver name that was just created
Provide your Source Name
Select Environment, Schema, Storage, and Location
Click Save.
Add the new source to a Domain
Click DataCatalog, identify your new data source and click Ingest to get to Source Configurations
Provide the JDBC URL that you captured from the above steps. Change the InitiateOAuth to REFRESH as Infoworks will just refresh the access token whenever it expires and you no longer need to generate it manually.
jdbc:exchange:AuthScheme="OAuth";User="";Password="";Server="https://admin.exchange.microsoft.com/#/";AzureTenant="";InitiateOAuth="REFRESH";OAuthClientId="3f87f804-3514-4696-a985-2c7d116fae8e";OAuthClientSecret="~U9AC-ne1f1.b68-JED7_xc4My6uAe7~y9";OAuthGrantType="CODE";OAuthSettingsLocation="/Applications/CData/OAuthSettings.txt";Other="CallbackURL='https://localhost:33333';EncryptOAuthSettings=False;";Schema="MSGraph";
Provide dummy values for Username and Password as IWX will be using the OAuth access token but not the user credentials.
Ensure that you copy the OAuthSettings.txt to a location on the Infoworks Edge node. Make sure Infoworks user has read/write permissions on that file.
Eg: /home/infoworks/OAuthSettings.txt
Run Save and Test Connection. Ensure that the connection is successful.
Once you have the OAuth settings file, you would need to move that file to the dbfs location.
From edgenode run the below command to move the file to dbfs:
Eg: dbfs cp /home/infoworks/OAuthSettings.txt dbfs:/FileStore/tables/OAuthSetting.txt
Create an init script with name lets say init_auth.sh
With contents:
#!/bin/bash
mkdir -p /home/infoworks/
cp /dbfs/FileStore/tables/OAuthSetting.txt /home/infoworks/OAuthSetting.txt
ls -ltrh /home/infoworks/OAuthSetting.txt
sudo chmod -R 777 /home/infoworks/
Ensure that while providing the above copy command, the source location points to your dbfs OAuthSettings.txt file.
Eg:
/dbfs/FileStore/tables/OAuthSetting.txt
And destination location is the same as what you provided the value for the OAuthSettingsLocation parameter while creating the source.
Eg:/home/infoworks/OAuthSetting.txt
q. Copy the init_auth.sh script to dbfs
dbfs cp ./init_auth.sh dbfs:/infoworks/lib/scripts/init_auth.sh
r. Open /opt/infoworks/conf/databricks_defaults_azure.json on the Edgenode( for Databricks running on Azure)
Search for init_scripts keyword and update the value as shown below:
s. If there are entries already present in the init_scripts, just add the below entry to the array.
"init_scripts": [
{
"dbfs": {
"destination":"dbfs:${databricks_iw_home}/lib/scripts/init_auth.sh"
}
}
]
t. This would allow all the job cluster launched by Infoworks jobs to be launched with the access to
OAuthSettings.txt file.
u. Run the Ingestion Job and validate the data.
Applicable Infoworks on Databricks versions:
v 4.2