<jupyter_start><jupyter_text>First, we need to import the GitbaseSessionBuilder and create a session<jupyter_code>import tech.sourced.gitbase.spark.GitbaseSessionBuilder

val spark = SparkSession.builder().appName("example")
    .master("local[*]")
    .config("spark.driver.host", "localhost")
    .registerGitbaseSource("gitbase:3306")
    .getOrCreate()<jupyter_output><empty_output><jupyter_text>Get all repositories<jupyter_code>spark.sql("SELECT * FROM repositories").show()<jupyter_output>+-----------------------+
|repository_id          |
+-----------------------+
|gitbase                |
|gitbase-spark-connector|
+-----------------------+<jupyter_text>Get HEAD references of repositories<jupyter_code>spark.sql("SELECT * FROM refs WHERE ref_name = 'HEAD'").show(truncate=false)<jupyter_output>+-----------------------+--------+----------------------------------------+
|repository_id          |ref_name|commit_hash                             |
+-----------------------+--------+----------------------------------------+
|gitbase-spark-connector|HEAD    |d9c0363978f5c869022dcf3266fd88fb2268c35a|
|gitbase                |HEAD    |38168fb16f0bf05286c631d4700272e92560f55a|
+-----------------------+--------+----------------------------------------+<jupyter_text>Get all the repositories where a specific user contributes on HEAD reference<jupyter_code>spark.sql("""SELECT refs.repository_id
FROM refs
NATURAL JOIN commits
WHERE
    commits.commit_author_name = '<NAME>'
    AND refs.ref_name = 'HEAD'""").show()<jupyter_output>+-------------+
|repository_id|
+-------------+
|      gitbase|
+-------------+<jupyter_text>First commit on HEAD history for all repositories<jupyter_code>spark.sql("""SELECT
    f.file_path,
    f.repository_id
FROM
    commit_files f
NATURAL JOIN
    ref_commits r
WHERE
    r.ref_name = 'HEAD'
    AND r.history_index = 0""").show(truncate=false)<jupyter_output>+------------------------------------------------------------------+-----------------------+
|file_path                                                         |repository_id          |
+------------------------------------------------------------------+-----------------------+
|.gitignore                                                        |gitbase-spark-connector|
|.travis.yml                                                       |gitbase-spark-connector|
|Dockerfile                                                        |gitbase-spark-connector|
|LICENSE                                                           |gitbase-spark-connector|
|README.md                                                         |gitbase-spark-connector|
|build.sbt                                                         |gitbase-spark-connector|
|project/Dependencies.scala                                        |gitbase-spark-connector|
|project/build.properties                                          |gi[...]<jupyter_text>Get the number of blobs per HEAD commit<jupyter_code>spark.sql("""SELECT COUNT(commit_hash), commit_hash
FROM ref_commits as
NATURAL JOIN commits
NATURAL JOIN commit_blobs
WHERE ref_name = 'HEAD'
GROUP BY commit_hash""").show(truncate=false)<jupyter_output>+------------------+----------------------------------------+
|count(commit_hash)|commit_hash                             |
+------------------+----------------------------------------+
|33                |478a42cd2a28b12ae3dbded699b02b39c12ebbc2|
|35                |63f9df67185e23387adcb41b7915cc3c738ac88d|
|2448              |15324804bfc5c8f788a400ee8d060a9841f7cd29|
|3586              |b31a60153f1bc36fa322c8ff684393733cb629f3|
|5919              |579894afe27ff6e2fc28b2fb63f1e34a272b6384|
|41                |2a41599d9cf2838636b82f5fe2fc89ea177f803a|
|2446              |9e3dd760bdf2f0bc54c3f45a0952ea80e72a75b2|
|6661              |8bd1d81a17625e1b4a537199151abcac20401407|
|4820              |a51b92224b79589a9015e4edb5b9f7dea974a6ac|
|5478              |c52fcf58ea0a4b3b5e0cfe0dbbe99c70f1dfa9a4|
|6256              |9259869628f1f54be6df34bafe3bc6a52796ec2e|
|4543              |d3db798e7f0ce18ea18cf532ee8a50e1cc7542bf|
|6609              |9ac9f0bb55a1fc806bc39f8d3bdff9d3c1927f8f|
|64     [...]<jupyter_text>Get commits per committer, per month in 2018<jupyter_code>spark.sql("""SELECT COUNT(*) as num_commits, month, repo_id, committer_email
FROM (
    SELECT
        MONTH(committer_when) as month,
        r.repository_id as repo_id,
        committer_email
    FROM ref_commits r
    INNER JOIN commits c
        ON YEAR(c.committer_when) = 2018
        AND r.repository_id = c.repository_id
        AND r.commit_hash = c.commit_hash
    WHERE r.ref_name = 'HEAD'
) as t
GROUP BY committer_email, month, repo_id""").show(truncate=false)<jupyter_output>+-----------+-----+-----------------------+-------------------------------+
|num_commits|month|repo_id                |committer_email                |
+-----------+-----+-----------------------+-------------------------------+
|10         |9    |gitbase                |<EMAIL>       |
|4          |4    |gitbase                |<EMAIL>       |
|6          |10   |gitbase                |<EMAIL>              |
|1          |10   |gitbase-spark-connector|cool<EMAIL>         |
|14         |8    |gitbase                |<EMAIL>       |
|4          |5    |gitbase                |<EMAIL>           |
|25         |7    |gitbase                |<EMAIL>           |
|1          |4    |gitbase                |<EMAIL>                  |
|6          |5    |gitbase                |<EMAIL>       |
|9          |7    |gitbase                |<EMAIL>              |
|4          [...]
{'fraction_non_alphanumeric': 0.21874444246843322, 'fraction_numerical': 0.08038413658189579, 'mean_word_length': 1.3492063492063493, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 17, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '460785', 'license': 'Apache-2.0', 'n_tokens_mistral': 1919, 'n_tokens_neox': 1612, 'n_words': 254, 'path': '_examples/examples.ipynb', 'repo_name': 'mcarmonaa/gitbase-spark-connector', 'size': '5759'}