How to Build an ETL App for HCL Domino Data in Python with CData Connect AI
The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData Connect AI Python SDK and the petl framework, you can build HCL Domino-connected applications and pipelines for extracting, transforming, and loading HCL Domino data. This article shows how to connect to Connect AI and use petl to extract, transform, and load HCL Domino data.
The Connect AI Python SDK (cdata-connect-ai) is a DB-API 2.0 (PEP 249) compliant client, so petl can read directly from the SDK connection with etl.fromdb. There is no driver to install per source: connect with a Personal Access Token and build your pipeline.
Connect to HCL Domino in Connect AI
CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.
- Log into Connect AI, click Sources, and then click Add Connection
- Select "HCL Domino" from the Add Connection panel
-
Enter the necessary authentication properties to connect to HCL Domino.
Connecting to Domino
To connect to Domino data, set the following properties:
- URL: The host name or IP of the server hosting the Domino database. Include the port of the server hosting the Domino database. For example: http://sampleserver:1234/
- DatabaseScope: The name of a scope in the Domino Web UI. The driver exposes forms and views for the schema governed by the specified scope. In the Domino Admin UI, select the Scopes menu in the sidebar. Set this property to the name of an existing scope.
Authenticating with Domino
Domino supports authenticating via login credentials or an Entra ID (formerly Azure AD) OAuth application:
Login Credentials
To authenticate with login credentials, set the following properties:
- AuthScheme: Set this to "OAuthPassword"
- User: The username of the authenticating Domino user
- Password: The password associated with the authenticating Domino user
The driver uses the login credentials to automatically perform an OAuth token exchange.
EntraID (formerly AzureAD)
This authentication method uses Entra ID (formerly Azure AD) as an IdP to obtain a JWT token. You need to create a custom OAuth application in Entra ID (formerly Azure AD) and configure it as an IdP. To do so, follow the instructions in the Help documentation. Then set the following properties:
- AuthScheme: Set this to "EntraID (formerly AzureAD)"
- InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
- OAuthClientId: The Client ID obtained when setting up the custom OAuth application.
- OAuthClientSecret: The Client secret obtained when setting up the custom OAuth application.
- CallbackURL: The redirect URI defined when you registered your app. For example: https://localhost:33333
- AzureTenant: The Microsoft Online tenant being used to access data. Supply either a value in the form companyname.microsoft.com or the tenant ID.
The tenant ID is the same as the directory ID shown in the Azure Portal's Entra ID (formerly Azure AD) > Properties page.
- Click Save & Test
- Navigate to the Permissions tab and update the user-based permissions.
Generate a Personal Access Token (PAT)
The Python SDK authenticates to Connect AI with your account email and a Personal Access Token (PAT). It is best practice to create a separate PAT for each application to maintain granularity of access.
- Click the Gear icon () at the top right of the Connect AI app to open the Settings page.
- On the Settings page, go to the Access Tokens section and click Create PAT.
- Give the PAT a name and click Create.
- The PAT is only visible at creation, so copy it and store it securely.
Install Required Modules
Install the SDK and the petl framework using the pip utility:
pip install cdata-connect-ai pip install petl
Build an ETL App for HCL Domino Data in Python
Once the required modules are installed, you are ready to build the ETL app. Code snippets follow, but the full source code is available at the end of the article.
First, import the modules and connect to Connect AI with your account email and PAT:
import petl as etl
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
Create a SQL Statement to Query HCL Domino
Use SQL to create a statement for querying HCL Domino. In this article, we read data from the ByName entity. Identifiers are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, Domino1).
sql = (
"SELECT Name, Address "
"FROM [Domino1].[Domino].[ByName] "
"WHERE City = 'Miami'"
)
Extract, Transform, and Load the HCL Domino Data
With a connection and query in hand, use petl to extract, transform, and load the HCL Domino data. In this example, we extract HCL Domino data, sort the data by the Address column, and load the data into a CSV file.
table1 = etl.fromdb(conn, sql) table2 = etl.sort(table1, 'Address') etl.tocsv(table2, 'byname_data.csv')
HCL Domino is a read-only source in Connect AI, so this pipeline can extract and transform HCL Domino data but not load rows back. Close the connection when the extract is complete:
conn.close()
With the CData Connect AI Python SDK, you can work with HCL Domino data just like you would with any database, including direct access to data in ETL packages like petl.
More Information and Free Trial
Now you can pipe live HCL Domino data through petl using the CData Connect AI Python SDK. For more information on connecting to HCL Domino (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start building data pipelines for live HCL Domino data in Python.
Full Source Code
import petl as etl
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
sql = (
"SELECT Name, Address "
"FROM [Domino1].[Domino].[ByName] "
"WHERE City = 'Miami'"
)
table1 = etl.fromdb(conn, sql)
table2 = etl.sort(table1, 'Address')
etl.tocsv(table2, 'byname_data.csv')
conn.close()