Code & Queries

In diesem Blogbeitrag werden wir Schritt für Schritt eine CI/CD-Pipeline (Continuous Integration/Continuous Deployment) mit Python und DuckDB erstellen. DuckDB ist eine leistungsstarke, in-memory OLAP-Datenbank, die sich hervorragend für analytische Workloads eignet. Wir werden eine Pipeline aufbauen, die automatisch Tests durchführt, die Datenbank aktualisiert und bei Bedarf neue Versionen bereitstellt.

Voraussetzungen

Bevor wir beginnen, stellen Sie sicher, dass Sie folgende Tools installiert haben:

• Python 3.8 oder höher
• DuckDB: Installieren Sie DuckDB mit pip install duckdb.
• Git: Für die Versionskontrolle.
• GitHub Actions oder ein anderer CI/CD-Dienst (z.B. GitLab CI, Jenkins).
• Ein GitHub-Repository: Wo wir unseren Code und die Pipeline speichern werden.

Schritt 1: Projektstruktur erstellen

Zuerst erstellen wir eine grundlegende Projektstruktur:

my_duckdb_project/
│
├── .github/
│   └── workflows/
│       └── ci_cd_pipeline.yml
├── src/
│   └── main.py
├── tests/
│   └── test_main.py
├── requirements.txt
└── README.md

• .github/workflows/ci_cd_pipeline.yml: Hier definieren wir unsere CI/CD-Pipeline.
• src/main.py: Unser Hauptskript, das DuckDB verwendet.
• tests/test_main.py: Unit-Tests für unser Skript.
• requirements.txt: Liste der Python-Abhängigkeiten.
• README.md: Dokumentation des Projekts.

Schritt 2: Python-Skript mit DuckDB erstellen

In src/main.py schreiben wir ein einfaches Python-Skript, das DuckDB verwendet:

import duckdb

def create_table():
    conn = duckdb.connect('my_db.duckdb')
    conn.execute("CREATE TABLE IF NOT EXISTS my_table (id INTEGER, name STRING)")
    conn.close()

def insert_data(id, name):
    conn = duckdb.connect('my_db.duckdb')
    conn.execute(f"INSERT INTO my_table VALUES ({id}, '{name}')")
    conn.close()

def query_data():
    conn = duckdb.connect('my_db.duckdb')
    result = conn.execute("SELECT * FROM my_table").fetchall()
    conn.close()
    return result

if __name__ == "__main__":
    create_table()
    insert_data(1, 'Alice')
    insert_data(2, 'Bob')
    print(query_data())

Dieses Skript erstellt eine Tabelle, fügt Daten ein und gibt die Daten aus der Tabelle zurück.

Schritt 3: Unit-Tests schreiben

In tests/test_main.py schreiben wir Unit-Tests für unser Skript:

import unittest
from src.main import create_table, insert_data, query_data

class TestDuckDB(unittest.TestCase):
    def test_create_table(self):
        create_table()
        # Überprüfen, ob die Tabelle erstellt wurde
        self.assertTrue(True)  # Platzhalter für echte Überprüfung

    def test_insert_data(self):
        insert_data(1, 'Alice')
        data = query_data()
        self.assertIn((1, 'Alice'), data)

if __name__ == "__main__":
    unittest.main()

Schritt 4: Abhängigkeiten festlegen

In requirements.txt listen wir unsere Python-Abhängigkeiten auf:

duckdb==0.5.0

Schritt 5: CI/CD-Pipeline mit GitHub Actions erstellen

In .github/workflows/ci_cd_pipeline.yml definieren wir unsere CI/CD-Pipeline:

name: CI/CD Pipeline for DuckDB Project

on:
  push:
    branches:
      - main
  pull_request:
    branches:
      - main

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v2
    - name: Set up Python
      uses: actions/setup-python@v2
      with:
        python-version: '3.8'
    - name: Install dependencies
      run: |
        python -m pip install --upgrade pip
        pip install -r requirements.txt
    - name: Run tests
      run: |
        python -m unittest discover -s tests

  deploy:
    needs: test
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v2
    - name: Set up Python
      uses: actions/setup-python@v2
      with:
        python-version: '3.8'
    - name: Install dependencies
      run: |
        python -m pip install --upgrade pip
        pip install -r requirements.txt
    - name: Run main script
      run: |
        python src/main.py

Diese Pipeline führt die folgenden Schritte aus:

1. Test-Job: Führt die Unit-Tests aus, wenn ein Push oder Pull Request auf den main-Branch erfolgt.
2. Deploy-Job: Führt das Hauptskript aus, wenn die Tests erfolgreich sind.

Schritt 6: Pipeline in Aktion

Sobald Sie den Code in Ihr GitHub-Repository pushen, wird die Pipeline automatisch ausgeführt. Sie können den Fortschritt und die Ergebnisse der Pipeline in der GitHub Actions-Ansicht überprüfen.

Fazit

In diesem Blogbeitrag haben wir eine einfache CI/CD-Pipeline mit Python und DuckDB erstellt. Diese Pipeline führt automatisch Tests durch und stellt bei Erfolg das Skript bereit. Dieses Setup kann je nach Bedarf erweitert werden, z.B. durch das Hinzufügen von Datenbank-Migrationen, weiteren Tests oder Deployment-Schritten.

Was sind Common Table Expressions (CTEs)?

Common Table Expressions, kurz CTEs, bieten eine mächtige Möglichkeit in der Strukturierten Abfragesprache (SQL), temporäre Ergebnismengen zu erstellen und zu verwenden. Diese temporären Resultsets können innerhalb einer einzelnen SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung verwendet werden. Sie erleichtern die Lesbarkeit und Wartbarkeit von SQL-Abfragen und ermöglichen es, komplexe Abfragen aufzuteilen und sie modular zu gestalten.

---

Warum sollten wir CTEs verwenden?

1. Verbesserte Lesbarkeit: Durch das Aufteilen komplexer Abfragen in logische Abschnitte wird der Code übersichtlicher.
2. Wiederverwendbarkeit: Innerhalb einer Abfrage können CTEs mehrfach verwendet werden.
3. Rekursive Abfragen: CTEs unterstützen rekursive Abfragen, was bei Hierarchien oder Baumstrukturen sehr nützlich ist.
4. Optimierung: Manche Datenbank-Management-Systeme (DBMS) optimieren CTEs besser als alternative Methoden wie Unterabfragen.

---

Syntax einer CTE

Die grundlegende Syntax für eine CTE sieht wie folgt aus:

WITH cte_name AS (
    -- SQL-Abfrage, die das temporäre Resultset definiert
)
SELECT * FROM cte_name;

Beispiel 1: Einfache CTE

Angenommen, wir haben eine Tabelle employees mit den Spalten id, name und salary. Wir möchten die Mitarbeiter mit einem Gehalt über 50.000 anzeigen.

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT * 
FROM high_salary_employees;

In diesem Beispiel erstellt die CTE high_salary_employees ein temporäres Resultset, das nur die Mitarbeiter mit einem Gehalt über 50.000 enthält. Die äußere SELECT-Anweisung gibt dieses Resultset dann aus.

---

Verkettete CTEs

Es ist möglich, mehrere CTEs in einer Abfrage zu verketten. Jede CTE kann auf die vorherige verweisen.

Beispiel 2: Verkettete CTEs

Angenommen, wir möchten nicht nur die Mitarbeiter mit einem hohen Gehalt anzeigen, sondern auch ihre durchschnittliche Gehaltsdifferenz zur Gesamtbelegschaft berechnen.

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
),
average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT hse.name, hse.salary, (hse.salary - av.avg_salary) AS salary_difference
FROM high_salary_employees hse, average_salary av;

Hier erstellen wir zwei CTEs:

1. high_salary_employees: Filtert die Mitarbeiter mit einem Gehalt über 50.000.
2. average_salary: Berechnet den Durchschnittsgewinn aller Mitarbeiter.

Die äußere SELECT-Anweisung kombiniert diese beiden CTEs, um die Gehaltsdifferenz zu berechnen.

---

Rekursive CTEs

Eine der beeindruckendsten Funktionen von CTEs ist ihre Fähigkeit, rekursive Abfragen durchzuführen. Dies ist besonders nützlich bei der Arbeit mit Hierarchien oder Baumstrukturen, wie Organisationen oder Kategorien.

Beispiel 3: Rekursive CTE für Organisationshierarchie

Angenommen, wir haben eine Tabelle employees mit den Spalten id, name und manager_id, wobei manager_id die ID des Vorgesetzten eines Mitarbeiters darstellt. Wir möchten alle Untergebenen eines bestimmten Mitarbeiters finden.

WITH RECURSIVE employee_hierarchy AS (
    -- Ankerabfrage: Startpunkt der Hierarchie
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1 -- Beginne mit dem Mitarbeiter mit ID 1

    UNION ALL

    -- Rekursiver Teil: Suche nach Untergebenen
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

In diesem Beispiel: - Die Ankerabfrage startet die Rekursion mit dem Mitarbeiter mit der ID 1. - Der rekursive Teil fügt in jeder Iteration die Untergebenen des aktuellen Mitarbeiters hinzu. - Das Resultset enthält schließlich alle Mitarbeiter in der Hierarchie unter dem Startmitarbeiter.

---

CTEs vs. Unterabfragen

CTEs unterscheiden sich von Unterabfragen in mehreren Aspekten:

Merkmal	CTEs	Unterabfragen
Lesbarkeit	Besser strukturiert und leichter lesbar	Kann schwer lesbar sein
Wiederverwendbarkeit	Innerhalb einer Abfrage wiederverwendbar	Muss oft dupliziert werden
Performance	Optimierung durch DBMS	Kann ineffizient sein

---

Limitationen von CTEs

Obwohl CTEs viele Vorteile bieten, gibt es auch einige Einschränkungen:

1. Gültigkeitsbereich: Eine CTE ist nur innerhalb der Abfrage gültig, in der sie definiert wurde.
2. Keine Indexierung: CTEs können nicht indiziert werden, was bei sehr großen Datensätzen zu Leistungsproblemen führen kann.
3. Keine direkte Persistierung: CTEs sind temporär und können nicht persistent gespeichert werden.

---

Schlussfolgerung

Common Table Expressions sind eine leistungsstarke Erweiterung von SQL, die die Lesbarkeit und Modularität von Abfragen erheblich verbessert. Sie eignen sich besonders gut für komplexe Abfragen und rekursive Operationen. Durch die Verwendung von CTEs können Entwickler ihre SQL-Codebasis klarer und wartbarer gestalten.

---