Batch_Jobs_and_Datasubjects_blogpost.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Batch jobs and the Data Subjects Service\n",
    "This blog post describes an interaction with batch jobs on StrmPrivacy, and the kind of data we would see in the 'Data Subjects Service'. The example uses the well known [UCI online retail dataset](https://archive.ics.uci.edu/ml/datasets/online+retail) that provides online shopping details.\n",
    "\n",
    "This notebook on github contains a subset of the csv data in the `s3` subdirectory, and the steps that are shown should work even without access to a S3 bucket or the `strm` command line tool. Comments in the notebook show what one would to do actually execute the batch-job and `strm` commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "eAmzOTPOe6xk"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "id": "sjgN6yoNfFW2"
   },
   "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>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536365</td>\n",
       "      <td>85123A</td>\n",
       "      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>2.55</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536365</td>\n",
       "      <td>71053</td>\n",
       "      <td>WHITE METAL LANTERN</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>536365</td>\n",
       "      <td>84406B</td>\n",
       "      <td>CREAM CUPID HEARTS COAT HANGER</td>\n",
       "      <td>8</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>2.75</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>536365</td>\n",
       "      <td>84029G</td>\n",
       "      <td>KNITTED UNION FLAG HOT WATER BOTTLE</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>536365</td>\n",
       "      <td>84029E</td>\n",
       "      <td>RED WOOLLY HOTTIE WHITE HEART.</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  InvoiceNo StockCode                          Description Quantity  \\\n",
       "0    536365    85123A   WHITE HANGING HEART T-LIGHT HOLDER        6   \n",
       "1    536365     71053                  WHITE METAL LANTERN        6   \n",
       "2    536365    84406B       CREAM CUPID HEARTS COAT HANGER        8   \n",
       "3    536365    84029G  KNITTED UNION FLAG HOT WATER BOTTLE        6   \n",
       "4    536365    84029E       RED WOOLLY HOTTIE WHITE HEART.        6   \n",
       "\n",
       "          InvoiceDate  UnitPrice CustomerID         Country  \n",
       "0 2010-12-01 08:26:00       2.55      17850  United Kingdom  \n",
       "1 2010-12-01 08:26:00       3.39      17850  United Kingdom  \n",
       "2 2010-12-01 08:26:00       2.75      17850  United Kingdom  \n",
       "3 2010-12-01 08:26:00       3.39      17850  United Kingdom  \n",
       "4 2010-12-01 08:26:00       3.39      17850  United Kingdom  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You could read the UCI Excel file from its website\n",
    "# df = pd.read_excel(\"https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx\", dtype=str)\n",
    "\n",
    "# or use the 20k lines subset in this repo\n",
    "df = pd.read_csv(\"s3/uci_online_retail.csv\", dtype=str, keep_default_na=False)\n",
    "df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])\n",
    "df['UnitPrice'] = df['UnitPrice'].astype(float)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## preparation\n",
    "Before you can run the rest of the notebook, you\n",
    "1. must prepare a [data-connector](https://docs.strmprivacy.io/docs/latest/quickstart/batch/data-connectors/) that allows access to some cloud bucket like S3, gcloud or Azure\n",
    "2. make sure you have access to the files created by the batch-job on that bucket. In this example I use\n",
    "   a _mounted_ s3 bucket with [`s3fs`](https://github.com/s3fs-fuse/s3fs-fuse). Similar tools exist for [gcloud](https://github.com/GoogleCloudPlatform/gcsfuse) and [Azure](https://github.com/Azure/azure-storage-fuse)\n",
    "   \n",
    "We have prepared a dataconnector named `s3-batch-demo` that points to an AWS S3 bucket\n",
    "```\n",
    "strm get data-connector s3-batch-demo\n",
    " NAME            TYPE               TARGET NAME\n",
    "\n",
    " s3-batch-demo   Amazon S3 Bucket   strm-batch-demo\n",
    "```\n",
    "You could ofcourse use any of the supported data-connector types. See [the data-connector documentation](https://docs.strmprivacy.io/docs/latest/quickstart/batch/data-connectors/) for how to create one.\n",
    "\n",
    "I have mounted my s3 bucket in a subdirectory named `s3`\n",
    "\n",
    "    s3fs strm-batch-demo s3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since Strm batch processing currently only supports csv files we convert the dataframe and store it in the S3 bucket. You'd execute this step if you had downloaded the Excel from UCI\n",
    "\n",
    "    df.to_csv(\"s3/uci_online_retail.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a sanity check we read back the top of the csv file. It looks okay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "InvoiceNo,StockCode,Description,Quantity,InvoiceDate,UnitPrice,CustomerID,Country\r\n",
      "536365,85123A,WHITE HANGING HEART T-LIGHT HOLDER,6,2010-12-01 08:26:00,2.55,17850,United Kingdom\r\n",
      "536365,71053,WHITE METAL LANTERN,6,2010-12-01 08:26:00,3.39,17850,United Kingdom\r\n",
      "536365,84406B,CREAM CUPID HEARTS COAT HANGER,8,2010-12-01 08:26:00,2.75,17850,United Kingdom\r\n",
      "536365,84029G,KNITTED UNION FLAG HOT WATER BOTTLE,6,2010-12-01 08:26:00,3.39,17850,United Kingdom\r\n"
     ]
    }
   ],
   "source": [
    "! head -5 s3/uci_online_retail.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PRzLUJNSgT0Z"
   },
   "source": [
    "# The data contract\n",
    "In order to be able to process the rows in the csv file, these rows need to conform to a _data contract_. This is a combination of a _schema_ and an _event-contract_.\n",
    "\n",
    "The schema defines the _shape_ of the rows, in this case it pretty much matches csv column headers. The event-contract defines the rules that govern the event. Together these two are called the _datacontract_.\n",
    "\n",
    "**These steps where done by us so you don't have to do them**\n",
    "```\n",
    "strm create schema strmprivacy/online-retail/1.0.0 --definition=online-retail.json --public\n",
    "strm activate schema strmprivacy/online-retail/1.0.0\n",
    "strm create event-contract strmprivacy/online-retail/1.0.0 --public \\\n",
    "    -F online-retail-contract.json -S strmprivacy/online-retail/1.0.0\n",
    "strm activate event-contract strmprivacy/online-retail/1.0.0\n",
    "```\n",
    "With schema\n",
    "\n",
    "```online-retail.json\n",
    "{\n",
    "  \"name\": \"UCI Online Retail\",\n",
    "  \"nodes\": [\n",
    "    { \"type\": \"STRING\", \"name\": \"InvoiceNo\" },\n",
    "    { \"type\": \"STRING\", \"name\": \"StockCode\" },\n",
    "    { \"type\": \"STRING\", \"name\": \"Description\" },\n",
    "    { \"type\": \"INTEGER\", \"name\": \"Quantity\" },\n",
    "    { \"type\": \"STRING\", \"name\": \"InvoiceDate\" },\n",
    "    { \"type\": \"STRING\", \"name\": \"UnitPrice\" },\n",
    "    { \"type\": \"STRING\", \"name\": \"CustomerID\" },\n",
    "    { \"type\": \"STRING\", \"name\": \"Country\" }\n",
    "  ]\n",
    "}\n",
    "```\n",
    "\n",
    "and event contract\n",
    "\n",
    "```online-retail-contract.json\n",
    "{\n",
    "  \"keyField\": \"CustomerID\",\n",
    "  \"piiFields\": { \"CustomerID\": 1 },\n",
    "  \"dataSubjectField\": \"CustomerID\"\n",
    "}\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuring the batch job.\n",
    "In order to define a batch job, we need to\n",
    "\n",
    "1. figure out the bucket locations and access. This is a matter of setting up the correct [data-connector](https://docs.strmprivacy.io/docs/latest/quickstart/batch/data-connectors/).\n",
    "2. figuring out the timestamp format. This uses\n",
    "   [Java time format](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html)\n",
    "3. determine the consent that was given, per batch job or even per record\n",
    "4. write the _batch job configuration file_. This file has a json-schema defined format; if your text editor knows about json schema, it can use `strmprivacy-batch-job-configuration` from [schemastore.org](https://schemastore.org)\n",
    "   \n",
    "   \n",
    "### the time format\n",
    "The way we converted the Excel file to csv *changed the timestamp format*. Make sure you look at the timestamp in the csv file.\n",
    "A sample timestamp in the UCI *csv* file is `2010-12-01 08:26:00`. This suggests the following format pattern: `yyyy-MM-dd HH:mm:ss`. Since this timestamp has no timezone information, it's necessary to add a default timezone. Look [here](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List) for a list.\n",
    "\n",
    "If you get the format *wrong*, you'll only notice after the batch job has started:\n",
    "\n",
    "```\n",
    "strm list batch-jobs\n",
    " BATCH JOB ID                           TIMESTAMP                           STATE   DETAILS                                           \n",
    " b2cead50-f85b-42cd-9198-53d7caa998e0   2022-09-13 08:24:03.226 +0000 UTC   ERROR   Invalid timestamp [Text '2010-12-01 08:26:00' could not be parsed at index 4] in row #1\n",
    "```\n",
    "\n",
    "### Consent\n",
    "The batch jobs _can_ extract consent per record using pattern matching, but we will leave this for another blog post. In the sample dataset, such information is absent, and we provide a bulk level consent to all the records in the whole file. In the sample below we've defined\n",
    "\n",
    "        \"consent\": { \"default_consent_levels\": [ 2 ] },\n",
    "\n",
    "whatever the `2` means in your organization. See [our documentation](https://docs.strmprivacy.io/docs/latest/concepts/batch-jobs/#consent) for more information about consent."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# the final configuration file\n",
    "\n",
    "\n",
    "```batch-job-config.json\n",
    "{\n",
    "    \"source_data\": {\n",
    "      \"data_connector_ref\": { \"name\": \"s3-batch-demo\"},\n",
    "      \"file_name\": \"uci_online_retail.csv\",\n",
    "      \"data_type\": { \"csv\": { \"charset\": \"UTF-8\" } }\n",
    "    },\n",
    "    \"consent\": { \"default_consent_levels\": [ 2 ] },\n",
    "    \"encryption\": {\n",
    "      \"batch_job_group_id\": \"7824e975-20e1-4995-b129-2f9582728ca5\",\n",
    "      \"timestamp_config\": {\n",
    "        \"field\": \"InvoiceDate\",\n",
    "        \"format\": \"yyyy-MM-dd HH:mm:ss\",\n",
    "        \"default_time_zone\": { \"id\": \"Europe/Amsterdam\" }\n",
    "      }\n",
    "    },\n",
    "    \"event_contract_ref\": {\n",
    "      \"handle\": \"strmprivacy\",\n",
    "      \"name\": \"online-retail\",\n",
    "      \"version\": \"1.0.0\"\n",
    "    },\n",
    "    \"encrypted_data\": {\n",
    "      \"target\": {\n",
    "        \"data_connector_ref\": { \"name\": \"s3-batch-demo\"},\n",
    "        \"data_type\": { \"csv\": { \"charset\": \"UTF-8\" } },\n",
    "        \"file_name\": \"uci_online_retail/encrypted.csv\"\n",
    "      }\n",
    "    },\n",
    "    \"encryption_keys_data\": {\n",
    "      \"target\": {\n",
    "        \"data_connector_ref\": { \"name\": \"s3-batch-demo\"},\n",
    "        \"data_type\": { \"csv\": { \"charset\": \"UTF-8\" } },\n",
    "        \"file_name\": \"uci_online_retail/keys.csv\"\n",
    "      }\n",
    "    },\n",
    "    \"derived_data\": [      {\n",
    "        \"target\": {\n",
    "          \"data_connector_ref\": { \"name\": \"s3-batch-demo\"},\n",
    "          \"data_type\": { \"csv\": { \"charset\": \"UTF-8\" } },\n",
    "          \"file_name\": \"uci_online_retail/decrypted-0.csv\"\n",
    "        },\n",
    "        \"consent_levels\": [ 2 ],\n",
    "        \"consent_level_type\": \"CUMULATIVE\"\n",
    "      }\n",
    "\n",
    "    ]\n",
    "  }\n",
    "  ```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Executing the batch job\n",
    "\n",
    "```\n",
    "strm create batch-job -F batch-job-config.json\n",
    "# shows a uuid that you use to access its progress.\n",
    "strm get batch-job <uuid>\n",
    "```\n",
    "I like to do the following to see how the batch job is progressing\n",
    "```\n",
    "watch \"strm get batch-job <uuid> -o json  | jq '.batchJob.states  | last'\"\n",
    "```\n",
    "which gives you somewhat realtime information on how the job is progressing. When finished, you should see something like\n",
    "\n",
    "```\n",
    "{\n",
    "  \"stateTime\": \"2022-09-13T10:06:47.113Z\",\n",
    "  \"state\": \"FINISHED\",\n",
    "  \"message\": \"Processed 541909 records in 194 s. which is 2790 records/s.\"\n",
    "}\n",
    "```\n",
    "Once the job has finished, you'll see output files in your bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "s3:\r\n",
      "total 1.7M\r\n",
      "drwxr-xr-x 2 bvdeenen bvdeenen 4.0K Sep 15 10:43 uci_online_retail\r\n",
      "-rw-r--r-- 1 bvdeenen bvdeenen 1.7M Sep 15 10:43 uci_online_retail.csv\r\n",
      "\r\n",
      "s3/uci_online_retail:\r\n",
      "total 16M\r\n",
      "-rw-r--r-- 1 bvdeenen bvdeenen 3.9M Sep 15 10:43 decrypted-0.csv\r\n",
      "-rw-r--r-- 1 bvdeenen bvdeenen 4.5M Sep 15 10:43 encrypted.csv\r\n",
      "-rw-r--r-- 1 bvdeenen bvdeenen 7.0M Sep 15 10:43 keys.csv\r\n"
     ]
    }
   ],
   "source": [
    "# note that the csv files in this repository are substantially smaller than if you\n",
    "# had downloaded the Excel file from UCI and actually ran the batch job.\n",
    "!ls -lRh s3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parsing the encrypted dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loaded timezone Europe/Amsterdam from config file\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "import pytz\n",
    "import datetime\n",
    "config = json.loads(open(\"batch-job-config.json\",\"r\").read())\n",
    "config_timezone = config[\"encryption\"][\"timestamp_config\"][\"default_time_zone\"][\"id\"]\n",
    "\n",
    "# The batch job configuration specified that the localtime InvoiceDate fields should be interpreted\n",
    "# in the timezone Europe/Amsterdam\n",
    "config_timezone = pytz.timezone(config_timezone)\n",
    "print(\"loaded timezone\", config_timezone, \"from config file\")\n",
    "\n",
    "# The date parser to convert the millisecond since the epoch timestamp in `strmMeta.timestamp\n",
    "# to a datetime instance. This is not strictly necessary but nice to have\n",
    "def date_parser(string_list):\n",
    "    # Java ms epoch to datetime\n",
    "    return [datetime.datetime.fromtimestamp(float(x)*0.001).astimezone(config_timezone) for x in string_list]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "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>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Country</th>\n",
       "      <th>strmMeta.eventContractRef</th>\n",
       "      <th>strmMeta.nonce</th>\n",
       "      <th>strmMeta.timestamp</th>\n",
       "      <th>strmMeta.keyLink</th>\n",
       "      <th>strmMeta.billingId</th>\n",
       "      <th>strmMeta.consentLevels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536365</td>\n",
       "      <td>85123A</td>\n",
       "      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00+01:00</td>\n",
       "      <td>2.55</td>\n",
       "      <td>ASqc1Q0QalDEN+LHeyZSfGHE+s9Lqu4o+jM=</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>strmprivacy/online-retail/1.0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-12-01 08:26:00+01:00</td>\n",
       "      <td>0fd20015-40e4-484d-ab1f-182acff382ac</td>\n",
       "      <td></td>\n",
       "      <td>[2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536365</td>\n",
       "      <td>71053</td>\n",
       "      <td>WHITE METAL LANTERN</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00+01:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>ASqc1Q0QalDEN+LHeyZSfGHE+s9Lqu4o+jM=</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>strmprivacy/online-retail/1.0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-12-01 08:26:00+01:00</td>\n",
       "      <td>0fd20015-40e4-484d-ab1f-182acff382ac</td>\n",
       "      <td></td>\n",
       "      <td>[2]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  InvoiceNo StockCode                         Description Quantity  \\\n",
       "0    536365    85123A  WHITE HANGING HEART T-LIGHT HOLDER        6   \n",
       "1    536365     71053                 WHITE METAL LANTERN        6   \n",
       "\n",
       "                InvoiceDate UnitPrice                            CustomerID  \\\n",
       "0 2010-12-01 08:26:00+01:00      2.55  ASqc1Q0QalDEN+LHeyZSfGHE+s9Lqu4o+jM=   \n",
       "1 2010-12-01 08:26:00+01:00      3.39  ASqc1Q0QalDEN+LHeyZSfGHE+s9Lqu4o+jM=   \n",
       "\n",
       "          Country        strmMeta.eventContractRef strmMeta.nonce  \\\n",
       "0  United Kingdom  strmprivacy/online-retail/1.0.0              0   \n",
       "1  United Kingdom  strmprivacy/online-retail/1.0.0              0   \n",
       "\n",
       "         strmMeta.timestamp                      strmMeta.keyLink  \\\n",
       "0 2010-12-01 08:26:00+01:00  0fd20015-40e4-484d-ab1f-182acff382ac   \n",
       "1 2010-12-01 08:26:00+01:00  0fd20015-40e4-484d-ab1f-182acff382ac   \n",
       "\n",
       "  strmMeta.billingId strmMeta.consentLevels  \n",
       "0                                       [2]  \n",
       "1                                       [2]  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "encrypted = pd.read_csv(\"s3/uci_online_retail/encrypted.csv\", dtype=str,\n",
    "                        keep_default_na=False, parse_dates=['strmMeta.timestamp'], date_parser=date_parser)\n",
    "encrypted['InvoiceDate'] = pd.to_datetime(encrypted['InvoiceDate']).dt.tz_localize(config_timezone)\n",
    "encrypted.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "note the encrypted `CustomerID` and the `strmMeta...` columns.\n",
    "* `strmMeta.billingId` is no longer used, and not filled in.\n",
    "* `strmMeta.consentLevels` is the consent for this particular record.\n",
    "  In this case all the records have the same consent but this is not necessarily the case.\n",
    "* `strmMeta.keyLink` is the uuid that allows us to find the encryption key that was used to encrypt the\n",
    "  PII columns in this event."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Encryption keys\n",
    "The exported encryption keys were read from the keys export file in the bucket. These are all the keys that were created or re-used for processing this job"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>keyLink</th>\n",
       "      <th>encryptionKey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0fd20015-40e4-484d-ab1f-182acff382ac</td>\n",
       "      <td>{\"primaryKeyId\":714921229,\"key\":[{\"keyData\":{\"...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1de9b1fc-9ad5-43a2-b501-af6933624f67</td>\n",
       "      <td>{\"primaryKeyId\":1369926497,\"key\":[{\"keyData\":{...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                keyLink  \\\n",
       "0  0fd20015-40e4-484d-ab1f-182acff382ac   \n",
       "1  1de9b1fc-9ad5-43a2-b501-af6933624f67   \n",
       "\n",
       "                                       encryptionKey  \n",
       "0  {\"primaryKeyId\":714921229,\"key\":[{\"keyData\":{\"...  \n",
       "1  {\"primaryKeyId\":1369926497,\"key\":[{\"keyData\":{...  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "keys = pd.read_csv(\"s3/uci_online_retail/keys.csv\")\n",
    "keys.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `encryptionKey` column is a [Google Tink](https://developers.google.com/tink) format encryption key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'primaryKeyId': 714921229,\n",
       " 'key': [{'keyData': {'typeUrl': 'type.googleapis.com/google.crypto.tink.AesSivKey',\n",
       "    'value': 'EkBKGzBuy9C3UUmWaOzpe7NBEg6QK21FRhZ9MjuD5hpa0+hPJy0kn1HngA9QUT5aGbTNQQyow0V6qJCFoFRQNNTH',\n",
       "    'keyMaterialType': 'SYMMETRIC'},\n",
       "   'status': 'ENABLED',\n",
       "   'keyId': 714921229,\n",
       "   'outputPrefixType': 'TINK'}]}"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json.loads(keys.iloc[0]['encryptionKey'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Decrypting records\n",
    "In this part of the notebook I'll decrypt the encrypted dataset within this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: tink in /home/bvdeenen/anaconda3/lib/python3.9/site-packages (1.6.1)\r\n",
      "Requirement already satisfied: absl-py in /home/bvdeenen/anaconda3/lib/python3.9/site-packages (from tink) (1.2.0)\r\n",
      "Requirement already satisfied: protobuf in /home/bvdeenen/anaconda3/lib/python3.9/site-packages (from tink) (3.20.1)\r\n",
      "Requirement already satisfied: six in /home/bvdeenen/anaconda3/lib/python3.9/site-packages (from tink) (1.16.0)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install tink\n",
    "import tink\n",
    "from tink import daead, cleartext_keyset_handle\n",
    "daead.register()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import base64\n",
    "class Decrypter():\n",
    "    def __init__(self, encryptionKey):\n",
    "        # create a primitive from a json cleartext encryption key\n",
    "        reader = tink.JsonKeysetReader(encryptionKey)\n",
    "        self.prim = cleartext_keyset_handle.read(reader).primitive(daead.DeterministicAead)\n",
    "    def decrypt(self, cipher_text):\n",
    "        return self.prim.decrypt_deterministically(base64.b64decode(cipher_text),b'').decode('utf-8')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "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>encryptionKey</th>\n",
       "      <th>decrypter</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>keyLink</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0fd20015-40e4-484d-ab1f-182acff382ac</th>\n",
       "      <td>{\"primaryKeyId\":714921229,\"key\":[{\"keyData\":{\"...</td>\n",
       "      <td>&lt;__main__.Decrypter object at 0x7fd51dcbf280&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1de9b1fc-9ad5-43a2-b501-af6933624f67</th>\n",
       "      <td>{\"primaryKeyId\":1369926497,\"key\":[{\"keyData\":{...</td>\n",
       "      <td>&lt;__main__.Decrypter object at 0x7fd55c54f190&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          encryptionKey  \\\n",
       "keyLink                                                                                   \n",
       "0fd20015-40e4-484d-ab1f-182acff382ac  {\"primaryKeyId\":714921229,\"key\":[{\"keyData\":{\"...   \n",
       "1de9b1fc-9ad5-43a2-b501-af6933624f67  {\"primaryKeyId\":1369926497,\"key\":[{\"keyData\":{...   \n",
       "\n",
       "                                                                          decrypter  \n",
       "keyLink                                                                              \n",
       "0fd20015-40e4-484d-ab1f-182acff382ac  <__main__.Decrypter object at 0x7fd51dcbf280>  \n",
       "1de9b1fc-9ad5-43a2-b501-af6933624f67  <__main__.Decrypter object at 0x7fd55c54f190>  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create the decrypters table\n",
    "decrypters=keys.set_index('keyLink')\n",
    "decrypters['decrypter'] = decrypters['encryptionKey'].apply(lambda e: Decrypter(e))\n",
    "decrypters.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "original: 17850 decrypted: 17850\n"
     ]
    }
   ],
   "source": [
    "# Test a decrypter instance\n",
    "customerId = df.iloc[0].CustomerID\n",
    "decrypter = decrypters.loc[encrypted.iloc[0]['strmMeta.keyLink']].decrypter\n",
    "print(\"original:\", customerId, \"decrypted:\", decrypter.decrypt(encrypted.iloc[0].CustomerID))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a merged table of the encrypted values and the encryption keys\n",
    "encrypted_and_keys = encrypted.merge(keys, how='left', left_on = 'strmMeta.keyLink', right_on='keyLink')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# decrypt every 'CustomerID' to 'decryptedCustomerId'\n",
    "def decrypt_row(row):\n",
    "    return decrypters.loc[row['strmMeta.keyLink']]['decrypter'].decrypt(row['CustomerID'])\n",
    "encrypted_and_keys['decryptedCustomerId'] = encrypted_and_keys.apply( decrypt_row, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    17850\n",
       "1    17850\n",
       "2    17850\n",
       "3    17850\n",
       "4    17850\n",
       "Name: decryptedCustomerId, dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sanity check\n",
    "encrypted_and_keys['decryptedCustomerId'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Data subject service"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DSS is a service that provides links to all the different encryption keys that were generated for a certain _data-subject_ (like a customer id). Encryption keys last 24 hours, so if a certain customer has shopped on multiple days in the dataset, we should find multiple key links"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "!strm list data-subjects --page-size 3 \n",
    "CgUxMjM0OQ==\n",
    "\n",
    "12346\n",
    "12349\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first line is the `next-page-token` that you can use for the next _page_ of data-subjects. There can be _many data subjects_, say as many as you have customers. When this token is empty, you've reached the last page of data subjects.\n",
    "Also interesting is the empty line, which is actually all the _unknown_ customers, that had an empty string as `CustomerID`. All these were seen as the _same entity_ and encrypted with the same key."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "!strm list data-subjects --page-token CgUxMjM0Nw== --page-size 3\n",
    "CgUxMjM1MA==\n",
    "12348\n",
    "12349\n",
    "12350\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assume you want to know what key links were ever generated for a certain data-subject, you execute a `list data-subject-keylinks` request for one or more data subjects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "!strm list data-subject-keylinks 12346 12348\n",
    "DATASUBJECT   KEYLINK                                EXPIRY                          \n",
    "                                                                                \n",
    " 12346         863cd2bf-60f7-4047-84b6-4d9f08e9868c   2011-01-19T01:00:00.000000+0100 \n",
    " 12348         925a0397-628d-4495-9e60-c9d772677417   2010-12-17T01:00:00.000000+0100 \n",
    " 12348         3ef801c7-8aec-49a5-89c4-f2a1543a9953   2011-01-26T01:00:00.000000+0100 \n",
    " 12348         d96de0e7-36d0-4530-996a-a49ee553e5da   2011-04-06T02:00:00.000000+0200 \n",
    " 12348         0039a177-37a9-4447-81da-dc3d1d36fcdc   2011-09-26T02:00:00.000000+0200 \n",
    " ```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we see that datasubject 12346 has been active on one day, and 12347 on four different days in this dataset. In case of a Right To Be Forgotten request, the organization would have to\n",
    "1. call the DSS to get all the keylinks associated with this datasubject\n",
    "2. remove encryption keys from a keys database in the organization. Hopefully, the keys export file has long ago\n",
    "   been deleted! If the organization has never stored any derived datasets with decrypted data then it has complied\n",
    "   with the RTBF request. This mechanism is called _crypto-shredding_.\n",
    "3. it would be useful to delete the datasubject from the DSS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "!strm delete data-subjects 12348\n",
    "4\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "!strm list data-subject-keylinks 12346 12348\n",
    " DATASUBJECT   KEYLINK                                EXPIRY                          \n",
    "                                                                                \n",
    " 12346         863cd2bf-60f7-4047-84b6-4d9f08e9868c   2011-01-19T01:00:00.000000+0100 \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that deleting a datasubject from the DSS _does not delete it from your organization_. How could it? It doesn't even have access to where you would store the encryption keys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}