You can use DuckDB to analyze Git logs using the output of the git log command.
Exporting the Git Log
We start by picking a character that doesn't occur in any part of the commit log (author names, messages, etc). Since version v1.2.0, DuckDB's CSV reader supports 4-byte delimiters, making it possible to use emojis! 🎉
Despite being featured in the Emoji Movie (IMDb rating: 3.4),
we can assume that the Fish Cake with Swirl emoji (🍥) is not a common occurrence in most Git logs.
So, let's clone the duckdb/duckdb repository and export its log as follows:
git log --date=iso-strict --pretty=format:%ad🍥%h🍥%an🍥%s > git-log.csv
The resulting file looks like this:
2025-02-25T18:12:54+01:00🍥d608a31e13🍥Mark🍥MAIN_BRANCH_VERSIONING: Adopt also for Python build and amalgamation (#16400)
2025-02-25T15:05:56+01:00🍥920b39ad96🍥Mark🍥Read support for Parquet Float16 (#16395)
2025-02-25T13:43:52+01:00🍥61f55734b9🍥Carlo Piovesan🍥MAIN_BRANCH_VERSIONING: Adopt also for Python build and amalgamation
2025-02-25T12:35:28+01:00🍥87eff7ebd3🍥Mark🍥Fix issue #16377 (#16391)
2025-02-25T10:33:49+01:00🍥35af26476e🍥Hannes Mühleisen🍥Read support for Parquet Float16
Loading the Git Log into DuckDB
Start DuckDB and read the log as a CSV 🍥SV:
CREATE TABLE commits AS
FROM read_csv(
'git-log.csv',
delim = '🍥',
header = false,
column_names = ['timestamp', 'hash', 'author', 'message']
);
This will result in a nice DuckDB table:
FROM commits
LIMIT 5;
┌─────────────────────┬────────────┬──────────────────┬───────────────────────────────────────────────────────────────────────────────┐
│ timestamp │ hash │ author │ message │
│ timestamp │ varchar │ varchar │ varchar │
├─────────────────────┼────────────┼──────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ 2025-02-25 17:12:54 │ d608a31e13 │ Mark │ MAIN_BRANCH_VERSIONING: Adopt also for Python build and amalgamation (#16400) │
│ 2025-02-25 14:05:56 │ 920b39ad96 │ Mark │ Read support for Parquet Float16 (#16395) │
│ 2025-02-25 12:43:52 │ 61f55734b9 │ Carlo Piovesan │ MAIN_BRANCH_VERSIONING: Adopt also for Python build and amalgamation │
│ 2025-02-25 11:35:28 │ 87eff7ebd3 │ Mark │ Fix issue #16377 (#16391) │
│ 2025-02-25 09:33:49 │ 35af26476e │ Hannes Mühleisen │ Read support for Parquet Float16 │
└─────────────────────┴────────────┴──────────────────┴───────────────────────────────────────────────────────────────────────────────┘
Analyzing the Log
We can analyze the table as any other in DuckDB.
Common Topics
Let's start with a simple question: which topic was the most commonly mentioned in the commit messages: CI, CLI, or Python?
SELECT
message.lower().regexp_extract('\b(ci|cli|python)\b') AS topic,
count(*) AS num_commits
FROM commits
WHERE topic <> ''
GROUP BY ALL
ORDER BY num_commits DESC;
┌─────────┬─────────────┐
│ topic │ num_commits │
│ varchar │ int64 │
├─────────┼─────────────┤
│ ci │ 828 │
│ python │ 666 │
│ cli │ 49 │
└─────────┴─────────────┘
Out of these three topics, commits related to continuous integration dominate the log!
We can also do a more exploratory analysis by looking at all words in the commit messages. To do so, we first tokenize the messages:
CREATE TABLE words AS
SELECT unnest(
message
.lower()
.regexp_replace('\W', ' ')
.trim(' ')
.string_split_regex('\W')
) AS word
FROM commits;
Then, we remove stopwords using a pre-defined list:
CREATE TABLE stopwords AS
SELECT unnest(['a', 'about', 'above', 'after', 'again', 'against', 'all', 'am', 'an', 'and', 'any', 'are', 'as', 'at', 'be', 'because', 'been', 'before', 'being', 'below', 'between', 'both', 'but', 'by', 'can', 'did', 'do', 'does', 'doing', 'don', 'down', 'during', 'each', 'few', 'for', 'from', 'further', 'had', 'has', 'have', 'having', 'he', 'her', 'here', 'hers', 'herself', 'him', 'himself', 'his', 'how', 'i', 'if', 'in', 'into', 'is', 'it', 'its', 'itself', 'just', 'me', 'more', 'most', 'my', 'myself', 'no', 'nor', 'not', 'now', 'of', 'off', 'on', 'once', 'only', 'or', 'other', 'our', 'ours', 'ourselves', 'out', 'over', 'own', 's', 'same', 'she', 'should', 'so', 'some', 'such', 't', 'than', 'that', 'the', 'their', 'theirs', 'them', 'themselves', 'then', 'there', 'these', 'they', 'this', 'those', 'through', 'to', 'too', 'under', 'until', 'up', 'very', 'was', 'we', 'were', 'what', 'when', 'where', 'which', 'while', 'who', 'whom', 'why', 'will', 'with', 'you', 'your', 'yours', 'yourself', 'yourselves']) AS word;
CREATE OR REPLACE TABLE words AS
FROM words
NATURAL ANTI JOIN stopwords
WHERE word != '';
We use the
NATURAL ANTI JOINclause here, which allows us to elegantly filter out values that occur in thestopwordstable.
Finally, we select the top-20 most common words.
SELECT word, count(*) AS count FROM words
GROUP BY ALL
ORDER BY count DESC
LIMIT 20;
┌──────────┬───────┐
│ w │ count │
│ varchar │ int64 │
├──────────┼───────┤
│ merge │ 12550 │
│ fix │ 6402 │
│ branch │ 6005 │
│ pull │ 5950 │
│ request │ 5945 │
│ add │ 5687 │
│ test │ 3801 │
│ master │ 3289 │
│ tests │ 2339 │
│ issue │ 1971 │
│ main │ 1935 │
│ remove │ 1884 │
│ format │ 1819 │
│ duckdb │ 1710 │
│ use │ 1442 │
│ mytherin │ 1410 │
│ fixes │ 1333 │
│ hawkfish │ 1147 │
│ feature │ 1139 │
│ function │ 1088 │
├──────────┴───────┤
│ 20 rows │
└──────────────────┘
As expected, there are many Git terms (merge, branch, pull, etc.), followed by terminology related to development (fix, test/tests, issue, format).
We also see the account names of some developers (mytherin, hawkfish), which are likely there due to commit message for merging pull requests (e.g., ”Merge pull request #13776 from Mytherin/expressiondepth”).
Finally, we also see some DuckDB-related terms such as duckdb (shocking!) and function.
Visualizing the Number of Commits
Let's visualize the number of commits each year:
SELECT
year(timestamp) AS year,
count(*) AS num_commits,
num_commits.bar(0, 20_000) AS num_commits_viz
FROM commits
GROUP BY ALL
ORDER BY ALL;
┌───────┬─────────────┬──────────────────────────────────────────────────────────────────────────────────┐
│ year │ num_commits │ num_commits_viz │
│ int64 │ int64 │ varchar │
├───────┼─────────────┼──────────────────────────────────────────────────────────────────────────────────┤
│ 2018 │ 870 │ ███▍ │
│ 2019 │ 1621 │ ██████▍ │
│ 2020 │ 3484 │ █████████████▉ │
│ 2021 │ 6488 │ █████████████████████████▉ │
│ 2022 │ 9817 │ ███████████████████████████████████████▎ │
│ 2023 │ 14585 │ ██████████████████████████████████████████████████████████▎ │
│ 2024 │ 15949 │ ███████████████████████████████████████████████████████████████▊ │
│ 2025 │ 1788 │ ███████▏ │
└───────┴─────────────┴──────────────────────────────────────────────────────────────────────────────────┘
We see a steady growth over the years – especially considering that many of DuckDB's functionalities and clients, which were originally part of the main repository, are now maintained in separate repositories (e.g., Java, R).
Happy hacking!