SQL Server Integration Services (SSIS) – мощный инструмент для импорта данных в SQL Server, но иногда использование Python для задач ETL может быть более удобным. Будь то из-за знакомства с Python или совместимости с предыдущим кодом, Python предлагает гибкое и эффективное решение для обработки нестандартных текстовых файлов. В этой статье мы рассмотрим, как Python может использоваться для обработки задач ETL для SQL Server с нестандартными текстовыми файлами.

Почему использовать Python для ETL?

Хотя SSIS является универсальным и простым в использовании инструментом для большинства операций ETL, есть ситуации, когда использование других инструментов, таких как Python, может быть более выгодным. Если вы интегрируете задачу ETL в более крупную программу, может быть лучше написать часть ETL на основном языке, используемом в проекте. Кроме того, если вы выполняете сложные преобразования или имеете пользовательские функции или библиотеки, которые полезны для задачи ETL, использование Python может обеспечить большую гибкость и эффективность.

Python – популярный и простой в использовании язык программирования, который требует небольшого времени разработки для создания простых скриптов. Он может быть хорошим выбором для обработки задач ETL с SQL Server, особенно при работе с нестандартными текстовыми файлами.

Обработка нестандартных текстовых файлов

В этой статье мы сосредоточимся на двух сценариях: обработка текстовых файлов с заголовками встроенными в данные и обработка текстовых файлов без четких разделителей.

Заголовки встроенные в данные

При работе с текстовыми файлами, в которых заголовки встроены в данные, Python может использоваться для извлечения соответствующей информации и импорта ее в SQL Server. Вот пример скрипта:

import pyodbc
import re

sqlConnStr = ('DRIVER={SQL Server Native Client 11.0};Server=YourServer;Database=Test;Trusted_Connection=YES')
sqlConn = pyodbc.connect(sqlConnStr, autocommit=True)
curs = sqlConn.cursor()

fNameRe = re.compile(r'(?i)(?<=/FName )[^/]+')
lNameRe = re.compile(r'(?i)(?<=/LName )[^/]+')
phoneRe = re.compile(r'(?i)(?<=/phone )[^/]+')

with open('example.txt') as inF:
    for line in inF:
        fName = fNameRe.findall(line)[0].strip()
        lName = lNameRe.findall(line)[0].strip()
        phone = phoneRe.findall(line)[0].strip()

        sql = "INSERT INTO dbo.people (FName, LName, phone) VALUES (?, ?, ?)"
        curs.execute(sql, fName, lName, phone)

Этот скрипт использует библиотеку pyodbc для подключения к SQL Server и библиотеку re для работы с регулярными выражениями. Он определяет три шаблона регулярных выражений для извлечения имени, фамилии и номера телефона из каждой строки текстового файла. Извлеченные значения затем вставляются в таблицу “People” в SQL Server.

Отсутствие четких разделителей

При работе с текстовыми файлами без четких разделителей, Python все равно может использоваться для извлечения соответствующей информации на основе структуры данных. Вот пример скрипта:

import pyodbc
import re

sqlConnStr = ('DRIVER={SQL Server Native Client 11.0};Server=YourServer;Database=Test;Trusted_Connection=YES')
sqlConn = pyodbc.connect(sqlConnStr, autocommit=True)
curs = sqlConn.cursor()

phoneRe = re.compile('\d{3}-\d{3}-\d{4}')
emailRe = re.compile(r'[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}', re.I)

with open('companies.txt') as inF:
    for line in inF:
        email = emailRe.findall(line)[0]
        phone = phoneRe.findall(line)[0]

        emailStart = emailRe.search(line).start()
        companyName = line[0:emailStart-1]

        sql = "INSERT INTO dbo.Companies (CompanyName, EmailAddress, Phone) VALUES (?, ?, ?)"
        curs.execute(sql, companyName, email, phone)

Этот скрипт использует те же библиотеки pyodbc и re для подключения к SQL Server и определения шаблонов регулярных выражений. Он извлекает название компании, адрес электронной почты и номер телефона из каждой строки текстового файла на основе структуры данных. Извлеченные значения затем вставляются в таблицу “Companies” в SQL Server.

Заключение

Python, благодаря своей гибкости и простоте использования, может быть ценным инструментом для обработки задач ETL с SQL Server, особенно при работе с нестандартными текстовыми файлами. Будь то извлечение данных из текстовых файлов с встроенными заголовками или без четких разделителей, возможности регулярных выражений Python и интеграция с SQL Server делают его мощным выбором для определенных типов ситуаций ETL.