How to Build an ETL App for SAS xpt 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 SAS xpt-connected applications and pipelines for extracting, transforming, and loading SAS xpt data. This article shows how to connect to Connect AI and use petl to extract, transform, and load SAS xpt 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 SAS xpt 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 "SAS xpt" from the Add Connection panel

3. Enter the necessary authentication properties to connect to SAS xpt.

   Connecting to Local SASXpt Files

   You can connect to local SASXpt file by setting the URI to a folder containing SASXpt files.

   Connecting to S3 data source

   You can connect to Amazon S3 source to read SASXpt files. Set the following properties to connect:

   • URI: Set this to the folder within your bucket that you would like to connect to.
   • AWSAccessKey: Set this to your AWS account access key.
   • AWSSecretKey: Set this to your AWS account secret key.
   • TemporaryLocalFolder: Set this to the path, or URI, to the folder that is used to temporarily download SASXpt file(s).

   Connecting to Azure Data Lake Storage Gen2

   You can connect to ADLS Gen2 to read SASXpt files. Set the following properties to connect:

   • URI: Set this to the name of the file system and the name of the folder which contacts your SASXpt files.
   • AzureAccount: Set this to the name of the Azure Data Lake storage account.
   • AzureAccessKey: Set this to our Azure DataLakeStore Gen 2 storage account access key.
   • TemporaryLocalFolder: Set this to the path, or URI, to the folder that is used to temporarily download SASXpt file(s).
   
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 SAS xpt 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 SAS xpt

Use SQL to create a statement for querying SAS xpt. In this article, we read data from the SampleTable_1 entity. Identifiers are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, SASXpt1).

sql = (
    "SELECT Id, Column1 "
    "FROM [SASXpt1].[SASXpt].[SampleTable_1] "
    "WHERE Column2 = '100'"
)

Extract, Transform, and Load the SAS xpt Data

With a connection and query in hand, use petl to extract, transform, and load the SAS xpt data. In this example, we extract SAS xpt data, sort the data by the Column1 column, and load the data into a CSV file.

table1 = etl.fromdb(conn, sql)

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

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

SAS xpt is a read-only source in Connect AI, so this pipeline can extract and transform SAS xpt 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 SAS xpt 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 SAS xpt data through petl using the CData Connect AI Python SDK. For more information on connecting to SAS xpt (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start building data pipelines for live SAS xpt 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 Id, Column1 "
    "FROM [SASXpt1].[SASXpt].[SampleTable_1] "
    "WHERE Column2 = '100'"
)

table1 = etl.fromdb(conn, sql)

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

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