How to Build an ETL App for Short.io Data in Python with CData

The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData API Driver for Python and the petl framework, you can build Short.io-connected applications and pipelines for extracting, transforming, and loading Short.io data. This article shows how to connect to Short.io with the CData Python Connector and use petl and pandas to extract, transform, and load Short.io data.

With built-in, optimized data processing, the CData Python Connector offers unmatched performance for interacting with live Short.io data in Python. When you issue complex SQL queries from Short.io, the driver pushes supported SQL operations, like filters and aggregations, directly to Short.io and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).

Connecting to Short.io Data

Connecting to Short.io data looks just like connecting to any relational data source. Create a connection string using the required connection properties. For this article, you will pass the connection string as a parameter to the create_engine function.

Using API Key Authentication

Short.io uses API Key authentication. To obtain your API key:

1. Log in to your Short.io account
2. Navigate to Settings > Integrations & API > API
3. Click Create API Key and copy your API key

After obtaining the API key, you are ready to connect:

• AuthScheme: Set this to APIKey.
• APIKey: Set this to your Short.io API key obtained from Settings > Integrations & API > API.

Example connection string:

Profile=C:\profiles\ShortIo.apip;AuthScheme=APIKey;ProfileSettings='APIKey=your_api_key';

Available Tables

The Short.io profile provides access to the following tables:

• Domains - Short.io domains associated with the authenticated account
• Links - Short links for a domain
• LinkExpand - Expand a short link by domain and path
• LinksByOriginalUrl - Retrieve multiple short links matching a given original destination URL
• Folders - Link folders within a specific domain
• LinkPermissions - Permission records for a specific link within a domain
• CountryTargeting - Country-based redirect targeting rules for a specific short link
• RegionTargeting - Region-based redirect targeting rules for a specific short link
• Regions - List of available regions/states for a given country code
• DomainStatistics - Aggregated click and traffic statistics for a Short.io domain
• LinkStatistics - Aggregated click and traffic statistics for a specific Short.io link

After installing the CData Short.io Connector, follow the procedure below to install the other required modules and start accessing Short.io through Python objects.

Install Required Modules

Use the pip utility to install the required modules and frameworks:

pip install petl
pip install pandas

Build an ETL App for Short.io Data in Python

Once the required modules and frameworks are installed, we are ready to build our ETL app. Code snippets follow, but the full source code is available at the end of the article.

First, be sure to import the modules (including the CData Connector) with the following:

import petl as etl
import pandas as pd
import cdata.api as mod

You can now connect with a connection string. Use the connect function for the CData Short.io Connector to create a connection for working with Short.io data.

cnxn = mod.connect("Profile=C:\profiles\ShortIo.apip;AuthScheme=APIKey;ProfileSettings='APIKey=your_api_key';")

Create a SQL Statement to Query Short.io

Use SQL to create a statement for querying Short.io. In this article, we read data from the Domains entity.

sql = "SELECT ,  FROM Domains WHERE  = ''"

Extract, Transform, and Load the Short.io Data

With the query results stored in a DataFrame, we can use petl to extract, transform, and load the Short.io data. In this example, we extract Short.io data, sort the data by the column, and load the data into a CSV file.

Loading Short.io Data into a CSV File

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'')

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

With the CData API Driver for Python, you can work with Short.io data just like you would with any database, including direct access to data in ETL packages like petl.

Free Trial & More Information

Download a free, 30-day trial of the CData API Driver for Python to start building Python apps and scripts with connectivity to Short.io data. Reach out to our Support Team if you have any questions.

Full Source Code

import petl as etl
import pandas as pd
import cdata.api as mod

cnxn = mod.connect("Profile=C:\profiles\ShortIo.apip;AuthScheme=APIKey;ProfileSettings='APIKey=your_api_key';")

sql = "SELECT ,  FROM Domains WHERE  = ''"

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'')

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