jupyterhub/notebooks/Import_to_Superset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import requests\n",
    "import urllib.parse\n",
    "\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## xAPIステートメントを取得"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>Learning Lockerで作成したLRSのクライアント情報を設定すること。</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user = '<Key>'\n",
    "passwd = '<Secret>'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[AGGREGATION HTTP INTERFACE](https://learninglocker.atlassian.net/wiki/spaces/DOCS/pages/106037259/Aggregation+API)で検索条件( `$match` )と件数( `$limit` )を指定し、ステートメントを取得する。  \n",
    "ステートメントの詳細は[xAPI仕様](https://github.com/adlnet/xAPI-Spec)([日本語版](https://github.com/elc-gh/xAPI-Spec_ja))を参照すること。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pipeline = urllib.parse.quote(json.dumps([\n",
    "    {'$project': {\n",
    "        'timestamp': 1,\n",
    "        'statement': 1,\n",
    "        '_id': 0\n",
    "    }},\n",
    "    # 検索条件\n",
    "    {'$match': {\n",
    "        #'statement.actor.name': '...', # ユーザの絞り込み\n",
    "        #'statement.verb.display.en': '...', # 操作の絞り込み\n",
    "        'timestamp': { # 操作日時の絞り込み\n",
    "            \"$gte\": {\n",
    "                '$dte': '2022-04-01T00:00:00Z'\n",
    "            },\n",
    "            \"$lte\": {\n",
    "                '$dte': '9999-12-31T00:00:00Z'\n",
    "            }\n",
    "        }\n",
    "    }},\n",
    "    # 取得する最大件数\n",
    "    {'$limit': 30000},\n",
    "]))\n",
    "url = f'http://learninglocker:8080/statements/aggregate?cache=false&pipeline={pipeline}'\n",
    "\n",
    "r = requests.get(url, auth=(user, passwd))\n",
    "\n",
    "# Jsonを辞書型に変換\n",
    "response_data = json.loads(r.text)\n",
    "# 辞書からステートメントのみを抽出\n",
    "stats = [d['statement'] for d in response_data]\n",
    "# ネスト項目をフラットにしてpandasのDataFrameを作成\n",
    "df = pd.io.json.json_normalize(stats)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ステートメントの分析\n",
    "分析結果は最終的にpandasのDataframe形式とすること"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 任意の分析処理\n",
    "# ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Supersetで可視化するデータを用意\n",
    "df = df[['object.definition.description.en', 'object.id', 'timestamp']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Supersetに処理結果を登録"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql://superset:superset@superset-db:5432/jupyter')\n",
    "\n",
    "# 登録するテーブルの名前を指定\n",
    "tablename = 'notebook_results'\n",
    "\n",
    "# Supersetにテーブルを登録\n",
    "from sqlalchemy.types import TIMESTAMP\n",
    "df.to_sql(\n",
    "    tablename,\n",
    "    engine,\n",
    "    if_exists='replace',\n",
    "    dtype={'timestamp': TIMESTAMP()}  # 必要に応じてdtypeでカラムの属性を指定する\n",
    ")\n",
    "\n",
    "s = requests.Session()\n",
    "r = s.post(\n",
    "    'http://superset:8088/api/v1/security/login',\n",
    "    json={\n",
    "        'username': 'admin',\n",
    "        'password': 'admin',\n",
    "        'provider': 'db', \n",
    "    }\n",
    ")\n",
    "headers={\n",
    "    'Authorization': f'Bearer {r.json()[\"access_token\"]}'\n",
    "}\n",
    "\n",
    "r = s.get(\n",
    "    'http://superset:8088/api/v1/database?q=(filters:!((col:database_name,opr:eq,value:Jupyter)))',\n",
    "    headers=headers,\n",
    ")\n",
    "database_id = r.json()['ids'][0]\n",
    "\n",
    "r = s.get(\n",
    "    'http://superset:8088/api/v1/security/csrf_token',\n",
    "    headers=headers\n",
    ")\n",
    "headers['X-CSRFToken'] = r.json()['result']\n",
    "\n",
    "r = s.post(\n",
    "    'http://superset:8088/api/v1/dataset',\n",
    "    headers=headers,\n",
    "    json={\n",
    "        'database': database_id,\n",
    "        'schema': 'public',\n",
    "        'table_name': tablename\n",
    "    }\n",
    ")\n",
    "print(r.json())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Supersetに登録済みのテーブル一覧"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = s.get(\n",
    "    'http://superset:8088/api/v1/dataset',\n",
    "    headers=headers\n",
    ")\n",
    "for dataset in r.json()['result']:\n",
    "    print(dataset['table_name'])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}