Deutsch
Support

2025-06-27

DuckDB-Use-Cases über GitHub entdecken

Petrica Leuca

Einleitung

Eine der dauerhaften Herausforderungen beim Pflegen einer Open-Source-Bibliothek ist der mangelnde Einblick, wie, wo und von wem sie genutzt wird. Bei DuckDB schauen wir uns das awesome-duckdb-Repository an, das eine kuratierte Liste von DuckDB-Bibliotheken, Tools und Ressourcen enthält. Ergänzend dazu wollten wir Insights von GitHub automatisieren, um neue und aufkommende Projekte zu finden, die DuckDB nutzen. In diesem Beitrag schreiben wir, wie wir DuckDB nutzen, um die GitHub-API abzufragen, die Daten in eine Markdown-Datei zu exportieren und die historischen Daten aus Git-Commits zu visualisieren.

Datenabruf von GitHub

GitHub bietet eine Reihe von REST-APIs, mit denen Daten von GitHub abgerufen werden können. Darunter gibt die Search-Repositories-API eine Liste öffentlicher Repositories zurück, deren Name, Beschreibung oder README dem Suchtext entspricht.

GitHub hat auch eine Search-Code-API, um auf Code-Abhängigkeiten zu matchen, aber sie gibt nicht alle Informationen zurück, die die Search-Repository-API liefert. Man könnte die Search-Code-API nutzen und für jedes Ergebnis die Repository-Informationen aus der Repository-API holen.

Um die GitHub-API zu nutzen, erzeugen wir ein Access Token mit Lesezugriff auf öffentliche Repositories. Mit dem Access Token, das als Umgebungsvariable gespeichert ist, erzeugen wir ein Secret in DuckDB, das für den API-Request genutzt wird:

import os


import duckdb




def get_duckdb_conn():
    conn = duckdb.connect()
    conn.sql(f"""
        CREATE SECRET http_auth (
            TYPE http,
            BEARER_TOKEN '{os.getenv("READ_PUBLIC_REPO_TOKEN")}',
            SCOPE 'https://api.github.com/search'
        );
    """)
    return conn

Mit der Funktion read_json des DuckDB-Python-Clients können wir die API-Antwort mit SQL abfragen. Das obige Secret wird von DuckDB automatisch an den API-Call übergeben:

duckdb_conn = get_duckdb_conn()


api_response = duckdb_conn.read_json(
    "https://api.github.com/search/repositories?q=duckdb"
)

Der API-Call gibt die folgende Antwort zurück:

┌─────────────┬────────────────────┬──────────────────────────────────────┐
│ total_count │ incomplete_results │    items                             │
│    int64    │      boolean       │ struct(id bigint, node_id varchar,...│
├─────────────┼────────────────────┼──────────────────────────────────────┤
│        3837 │ false              │ [{'id': 138754790, 'node_id': ...    │
└─────────────┴────────────────────┴──────────────────────────────────────┘

Wir können die Antwort analysieren, indem wir:

The total number of repositories mentioning DuckDB is 3837, but the items object contains only 30. This is due to pagination that is set to 30 records per page by default. We can retrieve a maximum of 100 records by using the per_page query parameter. Additionally, we use the page query parameter in order to retrieve the results for each page:

(
    duckdb_conn
    .read_json("https://api.github.com/search/repositories?q=duckdb&per_page=100&page=2")
    .select("len(items)")
)
┌────────────┐
│ len(items) │
│   int64    │
├────────────┤
│        100 │
└────────────┘

Because GitHub has a limit of returning maximum 1000 results in the search API and we retrieve the data daily, we added a filter to retrieve only the repositories which had a push in the last 7 days:

api_url = "https://api.github.com/search/repositories?q=duckdb"


last_pushed_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")


api_url = f"{api_url}+pushed:>={last_pushed_date}"


duckdb_conn.read_json(f"{api_url}&per_page=100&page=1").to_table("github_raw_data")

We store the first page in a table called github_raw_data and calculate how many pages we need to retrieve based on the total count returned by the first call. Then, with the insert_into method, we append the data from each page into github_raw_data:

for page in range(2, number_pages + 1):
    logger.info(f"Fetching {page} out of {number_pages}")
    (
        duckdb_conn
        .read_json(f"{api_url}&per_page=100&page={page}")
        .insert_into("github_raw_data")
    )

Saving Data to a Markdown File

With the data available in a table, we can continue our data processing, by using the unnest function, which will flatten the items returned by the API:

(
    duckdb_conn.table("github_raw_data")
    .select("unnest(items, recursive := true)")
)

By using recursive unnesting, the STRUCT objects within items will be flattened too; below is a sample of the columns derived from items after unnesting:

['id',
 'node_id',
 'name',
 'full_name',
 'private',
 'login',
 'id',
 ...
 'pull',
 'score']

Our scope is to create a Markdown file containing a table, with the following format:

To retrieve the name field we use concat_ws and concat functions, in order to generate the hyperlink and text with the Markdown newline character(<br>):

selection_query = (
    duckdb_conn.table("github_raw_data")
    .select("unnest(items, recursive := true)")
    .select("""
        concat_ws(
            '<br>',
            concat('[', name, '](', concat('https://github.com/', full_name),')'),
            coalesce(description, ' '),
            concat('**License** ', coalesce(name_1, 'unknown')),
            concat('**Owner** ', login)
        ) as repo_details
    """)
)

The above returns:

repo_details = [duckdb-web](https://github.com/duckdb/duckdb-web)<br>DuckDB website and documentation<br>**License** MIT License<br>**Owner** duckdb
repo_details = [duckdb](https://github.com/duckdb/duckdb)<br>DuckDB is an analytical in-process SQL database management system<br>**License** MIT License<br>**Owner** duckdb
...

We also calculate metrics such as stars, open issues count and forks, which we sum into a field activity_count. A repository will end up in the list only if it has an activity_count greater than 3 and if it is not a fork. From above we also see that repositories owned by DuckDB are returned, therefore we filter them out too:

selection_query.filter("""
    login != 'duckdb'
    and not fork
    and activity_count >= 3
""")

A Markdown table is similar to a CSV file, separated by pipe (|), but it must:

In order to export the data as a Markdown file we are applying a few tricks. The first one is to select the header by adding dummy columns, at the beginning and end, containing NULL:

duckdb_conn.sql("""
    select
        NULL,
        'Name',
        'Topics',
        'Stars',
        'Open Issues',
        'Forks',
        'Created At',
        'Updated At',
        NULL
""")

We then union the above header with the delimiter row:

.union(
    duckdb_conn.sql("""
    select
        NULL as '',
        '--' as "Name",
        '--' as "Topics",
        '--' as "Stars",
        '--' as "Open Issues",
        '--' as "Forks",
        '--' as "Created At",
        '--' as "Updated At",
        NULL as ''
""")
)

Returning:

┌───────┬─────────┬──────────┬─────────┬───────────────┬─────────┬──────────────┬──────────────┬───────┐
│ NULL  │ 'Name'  │ 'Topics' │ 'Stars' │ 'Open Issues' │ 'Forks' │ 'Created At' │ 'Updated At' │ NULL  │
│ int32 │ varchar │ varchar  │ varchar │    varchar    │ varchar │   varchar    │   varchar    │ int32 │
├───────┼─────────┼──────────┼─────────┼───────────────┼─────────┼──────────────┼──────────────┼───────┤
│  NULL │ Name    │ Topics   │ Stars   │ Open Issues   │ Forks   │ Created At   │ Updated At   │  NULL │
│  NULL │ --      │ --       │ --      │ --            │ --      │ --           │ --           │  NULL │
└───────┴─────────┴──────────┴─────────┴───────────────┴─────────┴──────────────┴──────────────┴───────┘

And finally we union with the initial GitHub selection query and export the data to CSV, by disabling the header export:

(
    ...
    .union(selection_query)
).to_csv("./exported_records.md", sep="|", header=False)

Below is a sample of the exported_records.md file, rendered as HTML:

Name Topics Stars Open Issues Forks Created At Updated At
tailpipe
select * from logs;
License GNU Affero General Public License v3.0
Owner turbot		 [aws, azure, detections, devops, duckdb, forensics, gcp, incident-response, log-analysis, mitre-attack, open-source, parquet, siem, tailpipe, threat-detection] 438 41 9 2024-04-18 02:44:35 2025-06-17 11:57:42

Why a Markdown file you may wonder. Because it is rendered automatically by GitHub and there is no need to host our tiny data application somewhere else. In fact, we copy the above table to README, such that it is displayed automatically on the first page of the repository:

Terminal window
echo '# Repositories using `duckdb`' > README.md
cat exported_records.md >> README.md

Another way to create the README file is by using the string_agg function:

selected_data = (
    selection_query
    .select("""
        concat(
            '|',
            concat_ws(
                '|',
                repo_details,
                topics, stars,
                open_issues,
                forks,
                created_at,
                updated_at
            )
        ) as line
    """)
    .string_agg(
        'line',
        sep='|\n'
    )
).fetchone()[0]


with open('README.md', 'w') as readme_file:
    readme_file.write("# Repositories using `duckdb`\n")
    readme_file.write("|Name|Topics|Stars|Open Issues|Forks|Created At|Updated At|\n")
    readme_file.write(f"{duckdb_conn.sql("select concat(repeat('|--', 7),'|')").fetchone()[0]}\n")
    readme_file.write(f"{selected_data}|")

In the above code snippet we concatenate the columns with the pipe character and then we aggregate the records into a string, separated by |\n in order to add a pipe and newline at the end of each line. We then use Python to write to README the title of the page, the Markdown table header, the delimiter row (by using the repeat function) and the data itself.

The data size is very small, each commit having a README of approx. 35 KB.

Automating with GitHub Workflow

With GitHub workflows we automated the above data processing steps. We first define a Makefile in our project to configure the steps needed to be executed in the workflow:

search-repos:
    uv run using_duckdb/search_repositories.py


readme:
    echo '# Repositories using `duckdb`' > README.md && \
    cat exported_records.md >> README.md

In the settings of the GitHub repository we create a repository secret which contains the value of the API token generated above. We can then configure an environment variable in our workflow and define the processing steps:

name: Search


on:
  workflow_dispatch:
  schedule:
    - cron: '37 5 * * *'


env:
  READ_PUBLIC_REPO_TOKEN : ${{ secrets.READ_PUBLIC_REPO_TOKEN }}


jobs:
  make-readme-md:
    runs-on: ubuntu-latest


    permissions:
      contents: write


    steps:
      - uses: actions/checkout@v4
        with:
          ref: ${{ github.head_ref }}
          fetch-depth: 0


      - name: Install uv
        uses: astral-sh/setup-uv@v6


      - name: Install the project
        run: uv sync


      - name: Get search results
        run: make search-repos


      - name: Export current results
        run: make readme


      - name: Get datetime
        id: datetime
        run: echo "datetime=$(date -u +'%Y-%m-%dT%H:%M:%SZ')" >> $GITHUB_OUTPUT


      - uses: stefanzweifel/git-auto-commit-action@v5
        with:
          commit_message: 'Update as of ${{ steps.datetime.outputs.datetime }}'
          file_pattern: 'README.md'

With git-auto-commit-action we delegate to GitHub the right to create a commit with the updated README.md file:

GitHub commits history

Visualizing Historical Data

Because we store the search results into the README file and we update it daily with Git, we can retrieve the history of the search by getting the Git difference for each commit:

git-log:
    echo '|Name|Topics|Stars|Open Issues|Forks|Created At|Updated At|' > git_log.md && \
    echo '|--|--|--|--|--|--|--|' >> git_log.md && \
    git log --follow -p --pretty=format:"" -- README.md | grep '^+|\[' | sed 's/^+//' >> git_log.md && \
    git diff -- README.md | grep '^+|\[' | sed 's/^+//' >> git_log.md

In the above Makefile command we:

The git_log.md contains now the entire history of appended records to the README file and it can be used to display a bubble chart with the repositories mentioning DuckDB:

GitHub repositories mentioning DuckDB

The above plot is generated with Plotly by providing as data source a DuckDB Python relation:

px.scatter(
    duckdb_conn.read_csv(
        "./git_log.md"
        skiprows=2
    )
        .select("""
        #2 as repo,
        #3 as topics,
        #4 as stars,
        #5 as open_issues,
        #6 as forks,
        strftime(#7, '%B %d, %Y, %H:%m' ) as created_at,
        #8 as updated_at,
        if(stars + forks + open_issues = 0, 0, log(stars + forks + open_issues)) as log_activity_count,
        substr(repo, position('[' in repo) + 1 , position(']' in repo) - 2) as repo_name,
        count(distinct updated_at::date) over (partition by repo_name) as number_of_updates,
        row_number() over (partition by repo_name order by updated_at desc) as rn
    """)
    .filter("rn = 1")
    .order("updated_at"),
    x="updated_at",
    y="log_activity_count",
    labels={
        "updated_at": "Updated Date",
        "log_activity_count": "Activity count, based on stars, open issues and forks"
    },
    ...

Markdown als Dateiformat für unsere historischen Daten ist vielleicht nicht die beste Lösung, weil es – unter anderem – keine Schema-Evolution unterstützt. Während wir keine Änderungen in der Mitte der Datei machen können (außer Spaltenumbenennungen), können wir die letzte Spalte entfernen oder neue am Ende ergänzen, wegen der Art, wie DuckDB fehlerhafte CSV-Dateien liest.

Schlussfolgerung

In diesem Beitrag haben wir gezeigt, wie DuckDB genutzt werden kann, um API-Requests und historische Daten aus Git-Commits zu verarbeiten. Einige der von der GitHub-Search-API zurückgegebenen Repositories haben es in awesome-duckdb geschafft, unsere Go-to-Liste für kuratierte DuckDB-bezogene Projekte; zum Beispiel tailpipe, ein Open-Source-Security-Information-and-Event-Management für sofortige Log-Insights, und preswald, ein Wasm-Packager für interaktive datenbasierte Python-Apps.