How to Build an ETL App for Azure Data Lake Storage 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 Azure Data Lake Storage-connected applications and pipelines for extracting, transforming, and loading Azure Data Lake Storage data. This article shows how to connect to Connect AI and use petl to extract, transform, and load Azure Data Lake Storage 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 Azure Data Lake Storage in Connect AI

CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.

1. Log into Connect AI, click Sources, and then click Add Connection

2. Select "Azure Data Lake Storage" from the Add Connection panel

3. Enter the necessary authentication properties to connect to Azure Data Lake Storage.

   Authenticating to a Gen 1 DataLakeStore Account

   Gen 1 uses OAuth 2.0 in Entra ID (formerly Azure AD) for authentication.

   For this, an Active Directory web application is required. You can create one as follows:

   1. Sign in to your Azure Account through the .
   2. Select "Entra ID" (formerly Azure AD).
   3. Select "App registrations".
   4. Select "New application registration".
   5. Provide a name and URL for the application. Select Web app for the type of application you want to create.
   6. Select "Required permissions" and change the required permissions for this app. At a minimum, "Azure Data Lake" and "Windows Azure Service Management API" are required.
   7. Select "Key" and generate a new key. Add a description, a duration, and take note of the generated key. You won't be able to see it again.

   To authenticate against a Gen 1 DataLakeStore account, the following properties are required:

   • Schema: Set this to ADLSGen1.
   • Account: Set this to the name of the account.
   • OAuthClientId: Set this to the application Id of the app you created.
   • OAuthClientSecret: Set this to the key generated for the app you created.
   • TenantId: Set this to the tenant Id. See the property for more information on how to acquire this.
   • Directory: Set this to the path which will be used to store the replicated file. If not specified, the root directory will be used.

   Authenticating to a Gen 2 DataLakeStore Account

   To authenticate against a Gen 2 DataLakeStore account, the following properties are required:

   • Schema: Set this to ADLSGen2.
   • Account: Set this to the name of the account.
   • FileSystem: Set this to the file system which will be used for this account.
   • AccessKey: Set this to the access key which will be used to authenticate the calls to the API. See the property for more information on how to acquire this.
   • Directory: Set this to the path which will be used to store the replicated file. If not specified, the root directory will be used.
   
4. Click Save & Test
5. 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.

1. Click the Gear icon () at the top right of the Connect AI app to open the Settings page.
2. On the Settings page, go to the Access Tokens section and click Create PAT.
3. Give the PAT a name and click Create.
4. 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 Azure Data Lake Storage 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 Azure Data Lake Storage

Use SQL to create a statement for querying Azure Data Lake Storage. In this article, we read data from the Resources entity. Identifiers are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, ADLS1).

sql = (
    "SELECT FullPath, Permission "
    "FROM [ADLS1].[ADLS].[Resources] "
    "WHERE Type = 'FILE'"
)

Extract, Transform, and Load the Azure Data Lake Storage Data

With a connection and query in hand, use petl to extract, transform, and load the Azure Data Lake Storage data. In this example, we extract Azure Data Lake Storage data, sort the data by the Permission column, and load the data into a CSV file.

table1 = etl.fromdb(conn, sql)

table2 = etl.sort(table1, 'Permission')

etl.tocsv(table2, 'resources_data.csv')

Azure Data Lake Storage is a read-only source in Connect AI, so this pipeline can extract and transform Azure Data Lake Storage 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 Azure Data Lake Storage 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 Azure Data Lake Storage data through petl using the CData Connect AI Python SDK. For more information on connecting to Azure Data Lake Storage (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start building data pipelines for live Azure Data Lake Storage 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 FullPath, Permission "
    "FROM [ADLS1].[ADLS].[Resources] "
    "WHERE Type = 'FILE'"
)

table1 = etl.fromdb(conn, sql)

table2 = etl.sort(table1, 'Permission')

etl.tocsv(table2, 'resources_data.csv')
conn.close()