quickstart.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b5ea5fc7-370d-4959-a700-42ec8742be34",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "871ee45c-098d-4bd0-b180-1068d31f0946",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'oso_gcp_credentials.json'\n",
    "GCP_PROJECT = 'opensource-observer'\n",
    "client = bigquery.Client(GCP_PROJECT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3f8a64df-317c-4a7e-a5e4-1eca9a5c6047",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>project_name</th>\n",
       "      <th>artifact_namespace</th>\n",
       "      <th>artifact_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>853</th>\n",
       "      <td>aave</td>\n",
       "      <td>bgd-labs</td>\n",
       "      <td>aave-v3-core</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <td>aave</td>\n",
       "      <td>bgd-labs</td>\n",
       "      <td>web3-react</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>855</th>\n",
       "      <td>aave</td>\n",
       "      <td>bgd-labs</td>\n",
       "      <td>how-to-delegate-aave</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>856</th>\n",
       "      <td>aave</td>\n",
       "      <td>bgd-labs</td>\n",
       "      <td>github-workflows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857</th>\n",
       "      <td>aave</td>\n",
       "      <td>bgd-labs</td>\n",
       "      <td>adi-deploy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    project_name artifact_namespace         artifact_name\n",
       "853         aave           bgd-labs          aave-v3-core\n",
       "854         aave           bgd-labs            web3-react\n",
       "855         aave           bgd-labs  how-to-delegate-aave\n",
       "856         aave           bgd-labs      github-workflows\n",
       "857         aave           bgd-labs            adi-deploy"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.query(\"\"\"\n",
    "    select\n",
    "        project_name,\n",
    "        artifact_namespace,\n",
    "        artifact_name\n",
    "    from `oso_playground.artifacts_by_project_v1`\n",
    "    where artifact_source = 'GITHUB'\n",
    "\"\"\")\n",
    "df = results.to_dataframe()\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "949e143f-38bc-46d6-aa7f-be0aebf04610",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>metric_name</th>\n",
       "      <th>project_name</th>\n",
       "      <th>sample_date</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>active_developers</td>\n",
       "      <td>uniswap</td>\n",
       "      <td>2024-08-06 00:00:00+00:00</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>fulltime_developers</td>\n",
       "      <td>uniswap</td>\n",
       "      <td>2024-08-06 00:00:00+00:00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>fulltime_developers</td>\n",
       "      <td>uniswap</td>\n",
       "      <td>2024-08-07 00:00:00+00:00</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>parttime_developers</td>\n",
       "      <td>uniswap</td>\n",
       "      <td>2024-08-07 00:00:00+00:00</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>active_developers</td>\n",
       "      <td>uniswap</td>\n",
       "      <td>2024-08-07 00:00:00+00:00</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             metric_name project_name               sample_date  amount\n",
       "247    active_developers      uniswap 2024-08-06 00:00:00+00:00    18.0\n",
       "248  fulltime_developers      uniswap 2024-08-06 00:00:00+00:00     1.0\n",
       "249  fulltime_developers      uniswap 2024-08-07 00:00:00+00:00     2.0\n",
       "250  parttime_developers      uniswap 2024-08-07 00:00:00+00:00    15.0\n",
       "251    active_developers      uniswap 2024-08-07 00:00:00+00:00    17.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.query(\"\"\"\n",
    "    select\n",
    "        m.metric_name,\n",
    "        p.project_name,\n",
    "        tsm.sample_date,\n",
    "        tsm.amount\n",
    "    from `oso_playground.timeseries_metrics_by_project_v0` as tsm\n",
    "    join `oso_playground.metrics_v0` as m\n",
    "        on tsm.metric_id = m.metric_id\n",
    "    join `oso_playground.projects_v1` as p\n",
    "        on tsm.project_id = p.project_id\n",
    "    where p.project_name = 'uniswap'\n",
    "    order by sample_date\n",
    "\"\"\")\n",
    "df = results.to_dataframe()\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5105e2a2-0396-43cf-bdf3-e6fbc7ffd9b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>project_id</th>\n",
       "      <th>project_source</th>\n",
       "      <th>project_namespace</th>\n",
       "      <th>project_name</th>\n",
       "      <th>display_name</th>\n",
       "      <th>event_source</th>\n",
       "      <th>repository_count</th>\n",
       "      <th>first_commit_date</th>\n",
       "      <th>last_commit_date</th>\n",
       "      <th>star_count</th>\n",
       "      <th>...</th>\n",
       "      <th>contributor_count</th>\n",
       "      <th>contributor_count_6_months</th>\n",
       "      <th>new_contributor_count_6_months</th>\n",
       "      <th>fulltime_developer_average_6_months</th>\n",
       "      <th>active_developer_count_6_months</th>\n",
       "      <th>commit_count_6_months</th>\n",
       "      <th>opened_pull_request_count_6_months</th>\n",
       "      <th>merged_pull_request_count_6_months</th>\n",
       "      <th>opened_issue_count_6_months</th>\n",
       "      <th>closed_issue_count_6_months</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SNdMHnnes04aSQQwpNdWT0uBt6VdKyT3Zog7miIsaK0=</td>\n",
       "      <td>OSS_DIRECTORY</td>\n",
       "      <td>oso</td>\n",
       "      <td>gitcoin</td>\n",
       "      <td>Gitcoin</td>\n",
       "      <td>GITHUB</td>\n",
       "      <td>87</td>\n",
       "      <td>2024-05-12 05:05:54+00:00</td>\n",
       "      <td>2024-09-09 18:00:27+00:00</td>\n",
       "      <td>3503</td>\n",
       "      <td>...</td>\n",
       "      <td>37.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0.415301</td>\n",
       "      <td>11.0</td>\n",
       "      <td>279.0</td>\n",
       "      <td>253.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>272.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>XSDgPwFuQVcj57ARcKTGrm2w80KKlqJxaBWF6jZqe7w=</td>\n",
       "      <td>OSS_DIRECTORY</td>\n",
       "      <td>oso</td>\n",
       "      <td>uniswap</td>\n",
       "      <td>Uniswap</td>\n",
       "      <td>GITHUB</td>\n",
       "      <td>59</td>\n",
       "      <td>2024-05-10 18:36:17+00:00</td>\n",
       "      <td>2024-09-09 21:53:42+00:00</td>\n",
       "      <td>28622</td>\n",
       "      <td>...</td>\n",
       "      <td>294.0</td>\n",
       "      <td>294.0</td>\n",
       "      <td>294.0</td>\n",
       "      <td>1.142077</td>\n",
       "      <td>24.0</td>\n",
       "      <td>621.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>672.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>147.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>HW-DsDcsApBwqtMlSu8S7BpNvtLdq5jvBrJD7kxz7pM=</td>\n",
       "      <td>OSS_DIRECTORY</td>\n",
       "      <td>oso</td>\n",
       "      <td>safe-global</td>\n",
       "      <td>Safe</td>\n",
       "      <td>GITHUB</td>\n",
       "      <td>138</td>\n",
       "      <td>2024-05-10 23:03:16+00:00</td>\n",
       "      <td>2024-09-10 18:35:59+00:00</td>\n",
       "      <td>5265</td>\n",
       "      <td>...</td>\n",
       "      <td>242.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>4.688525</td>\n",
       "      <td>32.0</td>\n",
       "      <td>4733.0</td>\n",
       "      <td>2096.0</td>\n",
       "      <td>1769.0</td>\n",
       "      <td>816.0</td>\n",
       "      <td>892.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Erx9J64anc8oSeN-wDKm0sojJf8ONrFVYbQ7GFnqSyc=</td>\n",
       "      <td>OSS_DIRECTORY</td>\n",
       "      <td>oso</td>\n",
       "      <td>opensource-observer</td>\n",
       "      <td>Open Source Observer</td>\n",
       "      <td>GITHUB</td>\n",
       "      <td>11</td>\n",
       "      <td>2024-05-10 17:00:09+00:00</td>\n",
       "      <td>2024-09-10 21:58:47+00:00</td>\n",
       "      <td>142</td>\n",
       "      <td>...</td>\n",
       "      <td>90.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1.672131</td>\n",
       "      <td>51.0</td>\n",
       "      <td>623.0</td>\n",
       "      <td>560.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>305.0</td>\n",
       "      <td>284.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DIFwzDKrLDsrfbdzXMd0V-K852SAS7sR0loKq0bIlco=</td>\n",
       "      <td>OSS_DIRECTORY</td>\n",
       "      <td>oso</td>\n",
       "      <td>libp2p</td>\n",
       "      <td>libp2p</td>\n",
       "      <td>GITHUB</td>\n",
       "      <td>257</td>\n",
       "      <td>2024-05-10 16:46:29+00:00</td>\n",
       "      <td>2024-09-10 16:17:41+00:00</td>\n",
       "      <td>25017</td>\n",
       "      <td>...</td>\n",
       "      <td>150.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>1.076503</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1482.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>471.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>143.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     project_id project_source  \\\n",
       "4  SNdMHnnes04aSQQwpNdWT0uBt6VdKyT3Zog7miIsaK0=  OSS_DIRECTORY   \n",
       "5  XSDgPwFuQVcj57ARcKTGrm2w80KKlqJxaBWF6jZqe7w=  OSS_DIRECTORY   \n",
       "6  HW-DsDcsApBwqtMlSu8S7BpNvtLdq5jvBrJD7kxz7pM=  OSS_DIRECTORY   \n",
       "7  Erx9J64anc8oSeN-wDKm0sojJf8ONrFVYbQ7GFnqSyc=  OSS_DIRECTORY   \n",
       "8  DIFwzDKrLDsrfbdzXMd0V-K852SAS7sR0loKq0bIlco=  OSS_DIRECTORY   \n",
       "\n",
       "  project_namespace         project_name          display_name event_source  \\\n",
       "4               oso              gitcoin               Gitcoin       GITHUB   \n",
       "5               oso              uniswap               Uniswap       GITHUB   \n",
       "6               oso          safe-global                  Safe       GITHUB   \n",
       "7               oso  opensource-observer  Open Source Observer       GITHUB   \n",
       "8               oso               libp2p                libp2p       GITHUB   \n",
       "\n",
       "   repository_count         first_commit_date          last_commit_date  \\\n",
       "4                87 2024-05-12 05:05:54+00:00 2024-09-09 18:00:27+00:00   \n",
       "5                59 2024-05-10 18:36:17+00:00 2024-09-09 21:53:42+00:00   \n",
       "6               138 2024-05-10 23:03:16+00:00 2024-09-10 18:35:59+00:00   \n",
       "7                11 2024-05-10 17:00:09+00:00 2024-09-10 21:58:47+00:00   \n",
       "8               257 2024-05-10 16:46:29+00:00 2024-09-10 16:17:41+00:00   \n",
       "\n",
       "   star_count  ...  contributor_count  contributor_count_6_months  \\\n",
       "4        3503  ...               37.0                        37.0   \n",
       "5       28622  ...              294.0                       294.0   \n",
       "6        5265  ...              242.0                       242.0   \n",
       "7         142  ...               90.0                        90.0   \n",
       "8       25017  ...              150.0                       150.0   \n",
       "\n",
       "   new_contributor_count_6_months  fulltime_developer_average_6_months  \\\n",
       "4                            37.0                             0.415301   \n",
       "5                           294.0                             1.142077   \n",
       "6                           242.0                             4.688525   \n",
       "7                            90.0                             1.672131   \n",
       "8                           150.0                             1.076503   \n",
       "\n",
       "   active_developer_count_6_months  commit_count_6_months  \\\n",
       "4                             11.0                  279.0   \n",
       "5                             24.0                  621.0   \n",
       "6                             32.0                 4733.0   \n",
       "7                             51.0                  623.0   \n",
       "8                             27.0                 1482.0   \n",
       "\n",
       "   opened_pull_request_count_6_months  merged_pull_request_count_6_months  \\\n",
       "4                               253.0                               213.0   \n",
       "5                               865.0                               672.0   \n",
       "6                              2096.0                              1769.0   \n",
       "7                               560.0                               519.0   \n",
       "8                               689.0                               471.0   \n",
       "\n",
       "   opened_issue_count_6_months  closed_issue_count_6_months  \n",
       "4                        142.0                        272.0  \n",
       "5                        307.0                        147.0  \n",
       "6                        816.0                        892.0  \n",
       "7                        305.0                        284.0  \n",
       "8                        154.0                        143.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.query(\"\"\"\n",
    "    select *\n",
    "    from `oso_playground.code_metrics_by_project_v1`\n",
    "\"\"\")\n",
    "df = results.to_dataframe()\n",
    "df.tail()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}