/opt/iamguarded/clickhouse/bin/clickhouse-git-import

A tool to extract information from Git repository for analytics.

It dumps the data for the following tables:
- commits - commits with statistics;
- file_changes - files changed in every commit with the info about the change and statistics;
- line_changes - every changed line in every changed file in every commit with full info about the line and the information about previous change of this line.

The largest and the most important table is "line_changes".

Allows to answer questions like:
- list files with maximum number of authors;
- show me the oldest lines of code in the repository;
- show me the files with longest history;
- list favorite files for author;
- list largest files with lowest number of authors;
- at what weekday the code has highest chance to stay in repository;
- the distribution of code age across repository;
- files sorted by average code age;
- quickly show file with blame info (rough);
- commits and lines of code distribution by time; by weekday, by author; for specific subdirectories;
- show history for every subdirectory, file, line of file, the number of changes (lines and commits) across time; how the number of contributors was changed across time;
- list files with most modifications;
- list files that were rewritten most number of time or by most of authors;
- what is percentage of code removal by other authors, across authors;
- the matrix of authors that shows what authors tends to rewrite another authors code;
- what is the worst time to write code in sense that the code has highest chance to be rewritten;
- the average time before code will be rewritten and the median (half-life of code decay);
- comments/code percentage change in time / by author / by location;
- who tend to write more tests / cpp code / comments.

The data is intended for analytical purposes. It can be imprecise by many reasons but it should be good enough for its purpose.

The data is not intended to provide any conclusions for managers, it is especially counter-indicative for any kinds of "performance review". Instead you can spend multiple days looking at various interesting statistics.

Run this tool inside your git repository. It will create .tsv files that can be loaded into ClickHouse (or into other DBMS if you dare).

The tool can process large enough repositories in a reasonable time.
It has been tested on:
- ClickHouse: 31 seconds; 3 million rows;
- LLVM: 8 minutes; 62 million rows;
- Linux - 12 minutes; 85 million rows;
- Chromium - 67 minutes; 343 million rows;
(the numbers as of Sep 2020)


Prepare the database by executing the following queries:

DROP DATABASE IF EXISTS git;
CREATE DATABASE git;

CREATE TABLE git.commits
(
    hash String,
    author LowCardinality(String),
    time DateTime,
    message String,
    files_added UInt32,
    files_deleted UInt32,
    files_renamed UInt32,
    files_modified UInt32,
    lines_added UInt32,
    lines_deleted UInt32,
    hunks_added UInt32,
    hunks_removed UInt32,
    hunks_changed UInt32
) ENGINE = MergeTree ORDER BY time;

CREATE TABLE git.file_changes
(
    change_type Enum('Add' = 1, 'Delete' = 2, 'Modify' = 3, 'Rename' = 4, 'Copy' = 5, 'Type' = 6),
    path LowCardinality(String),
    old_path LowCardinality(String),
    file_extension LowCardinality(String),
    lines_added UInt32,
    lines_deleted UInt32,
    hunks_added UInt32,
    hunks_removed UInt32,
    hunks_changed UInt32,

    commit_hash String,
    author LowCardinality(String),
    time DateTime,
    commit_message String,
    commit_files_added UInt32,
    commit_files_deleted UInt32,
    commit_files_renamed UInt32,
    commit_files_modified UInt32,
    commit_lines_added UInt32,
    commit_lines_deleted UInt32,
    commit_hunks_added UInt32,
    commit_hunks_removed UInt32,
    commit_hunks_changed UInt32
) ENGINE = MergeTree ORDER BY time;

CREATE TABLE git.line_changes
(
    sign Int8,
    line_number_old UInt32,
    line_number_new UInt32,
    hunk_num UInt32,
    hunk_start_line_number_old UInt32,
    hunk_start_line_number_new UInt32,
    hunk_lines_added UInt32,
    hunk_lines_deleted UInt32,
    hunk_context LowCardinality(String),
    line LowCardinality(String),
    indent UInt8,
    line_type Enum('Empty' = 0, 'Comment' = 1, 'Punct' = 2, 'Code' = 3),

    prev_commit_hash String,
    prev_author LowCardinality(String),
    prev_time DateTime,

    file_change_type Enum('Add' = 1, 'Delete' = 2, 'Modify' = 3, 'Rename' = 4, 'Copy' = 5, 'Type' = 6),
    path LowCardinality(String),
    old_path LowCardinality(String),
    file_extension LowCardinality(String),
    file_lines_added UInt32,
    file_lines_deleted UInt32,
    file_hunks_added UInt32,
    file_hunks_removed UInt32,
    file_hunks_changed UInt32,

    commit_hash String,
    author LowCardinality(String),
    time DateTime,
    commit_message String,
    commit_files_added UInt32,
    commit_files_deleted UInt32,
    commit_files_renamed UInt32,
    commit_files_modified UInt32,
    commit_lines_added UInt32,
    commit_lines_deleted UInt32,
    commit_hunks_added UInt32,
    commit_hunks_removed UInt32,
    commit_hunks_changed UInt32
) ENGINE = MergeTree ORDER BY time;

Run the tool.

Then insert the data with the following commands:

clickhouse-client --query "INSERT INTO git.commits FORMAT TSV" < commits.tsv
clickhouse-client --query "INSERT INTO git.file_changes FORMAT TSV" < file_changes.tsv
clickhouse-client --query "INSERT INTO git.line_changes FORMAT TSV" < line_changes.tsv

Check out this presentation: https://presentations.clickhouse.com/matemarketing_2020/

Usage: clickhouse git-import
Allowed options:
  -h [ --help ]     produce help message
  --skip-commits-without-parents arg (=1)
                    Skip commits without parents (except
                    the initial commit). These commits are
                    usually erroneous but they can make
                    sense in very rare cases.
  --skip-commits-with-duplicate-diffs arg (=1)
                    Skip commits with duplicate diffs.
                    These commits are usually results of
                    cherry-pick or merge after rebase.
  --skip-commit arg Skip commit with specified hash. The
                    option can be specified multiple times.
  --skip-paths arg  Skip paths that matches regular
                    expression (re2 syntax).
  --skip-commits-with-messages arg
                    Skip commits whose messages matches
                    regular expression (re2 syntax).
  --diff-size-limit arg (=100000)
                    Skip commits whose diff size (number of
                    added + removed lines) is larger than
                    specified threshold. Does not apply for
                    initial commit.
  --stop-after-commit arg
                    Stop processing after specified commit
                    hash.
  --threads arg (=64)
                    Number of concurrent git subprocesses
                    to spawn


Example:

clickhouse git-import --skip-paths 'generated\.cpp|^(contrib|docs?|website|libs/(libcityhash|liblz4|libdivide|libvectorclass|libdouble-conversion|libcpuid|libzstd|libfarmhash|libmetrohash|libpoco|libwidechar_width))/' --skip-commits-with-messages '^Merge branch '