data-analytics-demos/bigquery-data-governance/colab-enterprise/07-Sensitive-Data-Protection-Scan.ipynb

{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "gXQ7R9BJ-L9v" }, "source": [ "### Overview" ] }, { "cell_type": "markdown", "metadata": { "id": "EfMzqWL3-L9w" }, "source": [ "**Overview**: Sensitive Data Protection (DLP) - Creates a sensitive data protection scan and identifies sensitive data. Places the results in a new dataset which you can then easily use for reporting.\n", "\n", "**Process Flow**:\n", "1. **Create a new dataset** to store the scan results.\n", "\n", "2. **Create a data profiling scan** for each of the 4 tables in the raw dataset:\n", " * customer\n", " * customer_transition\n", " * product\n", " * product_category\n", "\n", "3. **Wait for the scan to complete** (specifically, for the `customer` table scan).\n", "\n", "4. **Review the newly created BigQuery dataset** containing the scan results.\n", "\n", "5. **(Optional) Delete the scans.**\n", "\n", "Notes:\n", "* You can also schedule these scans. \n", "\n", "Cost:\n", "* Approximate cost: Less than a dollar\n", "\n", "Author:\n", "* Adam Paternostro" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "wUdnbAGL-L9w" }, "outputs": [], "source": [ "# Architecture Diagram\n", "from IPython.display import Image\n", "Image(url='https://storage.googleapis.com/data-analytics-golden-demo/colab-diagrams/BigQuery-Data-Governance-SDP-Scan.png', width=1200)" ] }, { "cell_type": "markdown", "metadata": { "id": "P6O1UluF-L9x" }, "source": [ "### Video Walkthrough" ] }, { "cell_type": "markdown", "metadata": { "id": "3yqa3U5A-L9x" }, "source": [ "[Video](https://storage.googleapis.com/data-analytics-golden-demo/colab-videos/Sensitive-Data-Protection-Scan.mp4)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "zO--hQKn-L9x" }, "outputs": [], "source": [ "from IPython.display import HTML\n", "\n", "HTML(\"\"\"\n", "<video width=\"800\" height=\"600\" controls>\n", " <source src=\"https://storage.googleapis.com/data-analytics-golden-demo/colab-videos/Sensitive-Data-Protection-Scan.mp4\" type=\"video/mp4\">\n", " Your browser does not support the video tag.\n", "</video>\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": { "id": "HMsUvoF4BP7Y" }, "source": [ "### License" ] }, { "cell_type": "markdown", "metadata": { "id": "jQgQkbOvj55d" }, "source": [ "```\n", "# Copyright 2024 Google LLC\n", "#\n", "# Licensed under the Apache License, Version 2.0 (the \"License\");\n", "# you may not use this file except in compliance with the License.\n", "# You may obtain a copy of the License at\n", "#\n", "# https://www.apache.org/licenses/LICENSE-2.0\n", "#\n", "# Unless required by applicable law or agreed to in writing, software\n", "# distributed under the License is distributed on an \"AS IS\" BASIS,\n", "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n", "# See the License for the specific language governing permissions and\n", "# limitations under the License.\n", "```" ] }, { "cell_type": "markdown", "metadata": { "id": "UmyL-Rg4Dr_f" }, "source": [ "### Initialize" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xOYsEVSXp6IP" }, "outputs": [], "source": [ "from PIL import Image\n", "from IPython.display import HTML\n", "import IPython.display\n", "import google.auth\n", "import requests\n", "import json\n", "import uuid\n", "import base64\n", "import os\n", "import cv2\n", "import random\n", "import time\n", "import datetime\n", "import base64\n", "import random\n", "import logging" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "wMlHl3bnkFPZ" }, "outputs": [], "source": [ "# Set these (run this cell to verify the output)\n", "\n", "bigquery_location = \"${bigquery_location}\"\n", "\n", "# Get the current date and time\n", "now = datetime.datetime.now()\n", "\n", "# Format the date and time as desired\n", "formatted_date = now.strftime(\"%Y-%m-%d-%H-%M\")\n", "\n", "# Get some values using gcloud\n", "project_id = !(gcloud config get-value project)\n", "user = !(gcloud auth list --filter=status:ACTIVE --format=\"value(account)\")\n", "\n", "if len(project_id) != 1:\n", " raise RuntimeError(f\"project_id is not set: {project_id}\")\n", "project_id = project_id[0]\n", "\n", "if len(user) != 1:\n", " raise RuntimeError(f\"user is not set: {user}\")\n", "user = user[0]\n", "\n", "print(f\"project_id = {project_id}\")\n", "print(f\"user = {user}\")" ] }, { "cell_type": "markdown", "metadata": { "id": "sZ6m_wGrK0YG" }, "source": [ "### Helper Methods" ] }, { "cell_type": "markdown", "metadata": { "id": "JbOjdSP1kN9T" }, "source": [ "#### restAPIHelper\n", "Calls the Google Cloud REST API using the current users credentials." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "40wlwnY4kM11" }, "outputs": [], "source": [ "def restAPIHelper(url: str, http_verb: str, request_body: str, project_id=None) -> str:\n", " \"\"\"Calls the Google Cloud REST API passing in the current users credentials\"\"\"\n", "\n", " import requests\n", " import google.auth\n", " import json\n", "\n", " # Get an access token based upon the current user\n", " creds, project = google.auth.default()\n", " auth_req = google.auth.transport.requests.Request()\n", " creds.refresh(auth_req)\n", " access_token=creds.token\n", "\n", " headers = {\n", " \"Content-Type\" : \"application/json\",\n", " \"Authorization\" : \"Bearer \" + access_token\n", " }\n", "\n", " # Required by some API calls\n", " if project_id != None:\n", " headers[\"x-goog-user-project\"] = project_id\n", "\n", " if http_verb == \"GET\":\n", " response = requests.get(url, headers=headers)\n", " elif http_verb == \"POST\":\n", " response = requests.post(url, json=request_body, headers=headers)\n", " elif http_verb == \"PUT\":\n", " response = requests.put(url, json=request_body, headers=headers)\n", " elif http_verb == \"PATCH\":\n", " response = requests.patch(url, json=request_body, headers=headers)\n", " elif http_verb == \"DELETE\":\n", " response = requests.delete(url, headers=headers)\n", " else:\n", " raise RuntimeError(f\"Unknown HTTP verb: {http_verb}\")\n", "\n", " if response.status_code == 200:\n", " return json.loads(response.content)\n", " #image_data = json.loads(response.content)[\"predictions\"][0][\"bytesBase64Encoded\"]\n", " else:\n", " error = f\"Error restAPIHelper -> ' Status: '{response.status_code}' Text: '{response.text}'\"\n", " raise RuntimeError(error)" ] }, { "cell_type": "markdown", "metadata": { "id": "bI-KJELZ1jgt" }, "source": [ "#### RunQuery\n", "Runs a SQL statement against BigQuery" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "pmnCwYvA1kZv" }, "outputs": [], "source": [ "def RunQuery(sql):\n", " import time\n", " from google.cloud import bigquery\n", " client = bigquery.Client()\n", "\n", " if (sql.startswith(\"SELECT\") or sql.startswith(\"WITH\")):\n", " df_result = client.query(sql).to_dataframe()\n", " return df_result\n", " else:\n", " job_config = bigquery.QueryJobConfig(priority=bigquery.QueryPriority.INTERACTIVE)\n", " query_job = client.query(sql, job_config=job_config)\n", "\n", " # Check on the progress by getting the job's updated state.\n", " query_job = client.get_job(\n", " query_job.job_id, location=query_job.location\n", " )\n", " print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n", "\n", " while query_job.state != \"DONE\":\n", " time.sleep(2)\n", " query_job = client.get_job(\n", " query_job.job_id, location=query_job.location\n", " )\n", " print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n", "\n", " if query_job.error_result == None:\n", " return True\n", " else:\n", " raise Exception(query_job.error_result)" ] }, { "cell_type": "markdown", "metadata": { "id": "PUPlSXZRFOlR" }, "source": [ "### Sensitive Data Protection - Helper Methods" ] }, { "cell_type": "markdown", "metadata": { "id": "8AjDmAN5EL76" }, "source": [ "#### existsSDPScan\n", "- Test to see if a scan exists\n", "- NOTE: It prefixes the scan name with an \"i-\" (\"i\" stands for an inspection scan)\n", "- If the scan exists, the does nothing" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ori7nzAQA4AZ" }, "outputs": [], "source": [ "def existsSDPScan(project_id, sdp_scan_name):\n", " \"\"\"Test to see if a scan exists.\"\"\"\n", "\n", " # Gather existing scans\n", " # https://cloud.google.com/sensitive-data-protection/docs/reference/rest/v2/projects.dlpJobs/list\n", "\n", " url = f\"https://dlp.googleapis.com/v2/projects/{project_id}/dlpJobs\"\n", "\n", " # Gather existing scans\n", " json_result = restAPIHelper(url, \"GET\", None, project_id=project_id)\n", " print(f\"existsSDPScan (GET) json_result: {json_result}\")\n", "\n", " # Test to see if data scan exists, if so return\n", " if \"jobs\" in json_result:\n", " for item in json_result[\"jobs\"]:\n", " print(f\"Scan names: {item['name']}\")\n", " if item[\"name\"] == f\"projects/{project_id}/dlpJobs/i-{sdp_scan_name}\":\n", " print(f\"SDP Scan {sdp_scan_name} exists\")\n", " return True\n", "\n", " return False" ] }, { "cell_type": "markdown", "metadata": { "id": "6w5BHsAjAWpy" }, "source": [ "#### createSDPScan\n", "- Creates a sensitive data proctection scan and starts to run it\n", "- If the scan exists, the does nothing\n", "- Returns the scan name" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "zcWAen9Z5sL_" }, "outputs": [], "source": [ "def createSDPScan(project_id, sdp_scan_name, source_dataset, source_table, primary_key_name, destination_dataset, destination_table):\n", " \"\"\"Creates a scan if it does not exist\"\"\"\n", "\n", " # publishFindingsToCloudDataCatalog\n", "\n", " if existsSDPScan(project_id,sdp_scan_name) == False:\n", " # https://cloud.google.com/sensitive-data-protection/docs/reference/rest/v2/projects.dlpJobs/create\n", "\n", " url = f\"https://dlp.googleapis.com/v2/projects/{project_id}/dlpJobs\"\n", "\n", " # Info Types: https://cloud.google.com/sensitive-data-protection/docs/infotypes-reference\n", "\n", " data = {\n", " \"jobId\": sdp_scan_name,\n", "\n", " \"inspectJob\":{\n", " \"storageConfig\":{\n", " \"bigQueryOptions\":{\n", " \"tableReference\":{\n", " \"projectId\": project_id,\n", " \"datasetId\": source_dataset,\n", " \"tableId\": source_table\n", " },\n", " \"identifyingFields\":[\n", " {\n", " \"name\": primary_key_name\n", " }\n", " ],\n", " \"rowsLimit\" : 0 # all rows\n", " }\n", " },\n", "\n", " \"inspectConfig\":{\n", " \"infoTypes\":[\n", " {\n", " \"name\":\"CREDIT_CARD_NUMBER\"\n", " },\n", " {\n", " \"name\":\"DATE_OF_BIRTH\"\n", " },\n", " {\n", " \"name\":\"EMAIL_ADDRESS\"\n", " },\n", " {\n", " \"name\":\"FIRST_NAME\"\n", " },\n", " {\n", " \"name\":\"LAST_NAME\"\n", " },\n", " {\n", " \"name\":\"GENDER\"\n", " },\n", " {\n", " \"name\":\"IP_ADDRESS\"\n", " },\n", " {\n", " \"name\":\"DATE\"\n", " }\n", " ],\n", " \"excludeInfoTypes\": False,\n", " \"includeQuote\": True,\n", " \"minLikelihood\": \"LIKELY\"\n", " },\n", "\n", " \"actions\":[\n", " {\n", " \"saveFindings\":{\n", " \"outputConfig\":{\n", " \"table\":{\n", " \"projectId\": project_id,\n", " \"datasetId\": destination_dataset,\n", " \"tableId\": destination_table\n", " },\n", " \"outputSchema\": \"BASIC_COLUMNS\"\n", " }\n", " }\n", " },\n", " {\n", " \"publishFindingsToCloudDataCatalog\" : {}\n", " }\n", " ]\n", " }\n", " }\n", "\n", " result = restAPIHelper(url, \"POST\", request_body=data, project_id=project_id)\n", " print(result)\n", " sdp_job_name = result[\"name\"]\n", " else:\n", " print(f\"SDP Scan Already Exists\")" ] }, { "cell_type": "markdown", "metadata": { "id": "zsrP4E1cEbha" }, "source": [ "#### getSDPScan\n", "- Test to see if a scan exists\n", "- If the scan exists, returns the scan data" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "1JHZEaEh5sDB" }, "outputs": [], "source": [ "def getSDPScan(project_id, sdp_scan_name):\n", " \"\"\"Gets a scan (in order to check its state)\"\"\"\n", "\n", " if existsSDPScan(project_id,sdp_scan_name) == True:\n", " # Get Scan\n", " # https://cloud.google.com/sensitive-data-protection/docs/reference/rest/v2/projects.dlpJobs/get\n", "\n", " url = f\"https://dlp.googleapis.com/v2/projects/{project_id}/dlpJobs/i-{sdp_scan_name}\"\n", "\n", " # Gather existing scans\n", " json_result = restAPIHelper(url, \"GET\", None, project_id=project_id)\n", " print(f\"getSDPScan (GET) json_result: {json_result}\")\n", " return json_result" ] }, { "cell_type": "markdown", "metadata": { "id": "HwUtdSteFXXF" }, "source": [ "#### deleteSDPScan\n", "- Test to see if a scan exists\n", "- If the scan exists, delete the scan" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "UVG58l0nFceX" }, "outputs": [], "source": [ "def deleteSDPScan(project_id, sdp_scan_name):\n", " \"\"\"Delete a scan\"\"\"\n", "\n", " if existsSDPScan(project_id,sdp_scan_name) == True:\n", " # Delete Scan\n", " # https://cloud.google.com/sensitive-data-protection/docs/reference/rest/v2/projects.dlpJobs/delete\n", "\n", " url = f\"https://dlp.googleapis.com/v2/projects/{project_id}/dlpJobs/i-{sdp_scan_name}\"\n", "\n", " # Gather existing scans\n", " json_result = restAPIHelper(url, \"DELETE\", None, project_id=project_id)\n", " print(f\"deleteSDPScan (DELETE) json_result: {json_result}\")" ] }, { "cell_type": "markdown", "metadata": { "id": "PwQHYZ-aFv7j" }, "source": [ "### Run Sensitive Data Protection Scan (for a BigQuery table)" ] }, { "cell_type": "markdown", "metadata": { "id": "MKIRbIHHt1Dm" }, "source": [ "- Creates a new dataset to store the results\n", "- Creates a new scan for the customer table\n", "- Starts the scan (after a delay)\n", "- Monitors the scans progress" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "MdYYj7cO9b4K" }, "outputs": [], "source": [ "# Create a new dataset to hold the SDP results (keep seperate from source tables)\n", "\n", "governed_data_sdp_scan_dataset_name = \"governed_data_sdp_scan\"\n", "\n", "sql = f\"\"\"CREATE SCHEMA IF NOT EXISTS `{project_id}.{governed_data_sdp_scan_dataset_name}` OPTIONS(location=\"{bigquery_location}\")\"\"\"\n", "\n", "RunQuery(sql)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "I5CHArHw5sGP" }, "outputs": [], "source": [ "# Create the SDP scan on the customer table\n", "\n", "customer_sdp_scan_name = \"sdp-governed-data-raw-customer\"\n", "source_dataset = \"${bigquery_governed_data_raw_dataset}\"\n", "source_table = \"customer\"\n", "primary_key_name = \"customer_id\"\n", "destination_dataset = governed_data_sdp_scan_dataset_name\n", "destination_table = \"customer\"\n", "\n", "createSDPScan(project_id, customer_sdp_scan_name, source_dataset, source_table, primary_key_name, destination_dataset, destination_table)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "7ESOd88C5sAB" }, "outputs": [], "source": [ "# Show the Customer scan\n", "\n", "getSDPScan(project_id, customer_sdp_scan_name)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "kyDBDIp2Ghpf" }, "outputs": [], "source": [ "# Create the SDP scan on the customer_transaction table\n", "\n", "customer_transaction_sdp_scan_name = \"sdp-governed-data-raw-customer-transaction\"\n", "source_dataset = \"${bigquery_governed_data_raw_dataset}\"\n", "source_table = \"customer_transaction\"\n", "primary_key_name = \"transaction_id\"\n", "destination_dataset = governed_data_sdp_scan_dataset_name\n", "destination_table = \"customer_transaction\"\n", "\n", "createSDPScan(project_id, customer_transaction_sdp_scan_name, source_dataset, source_table, primary_key_name, destination_dataset, destination_table)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "csWpyTlLHC6u" }, "outputs": [], "source": [ "# Create the SDP scan on the product table\n", "\n", "product_sdp_scan_name = \"sdp-governed-data-raw-project\"\n", "source_dataset = \"${bigquery_governed_data_raw_dataset}\"\n", "source_table = \"product\"\n", "primary_key_name = \"product\"\n", "destination_dataset = governed_data_sdp_scan_dataset_name\n", "destination_table = \"product\"\n", "\n", "createSDPScan(project_id, product_sdp_scan_name, source_dataset, source_table, primary_key_name, destination_dataset, destination_table)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "e5rAJguSG8wc" }, "outputs": [], "source": [ "# Create the SDP scan on the product_category table\n", "\n", "product_category_sdp_scan_name = \"sdp-governed-data-raw-product-category\"\n", "source_dataset = \"${bigquery_governed_data_raw_dataset}\"\n", "source_table = \"product_category\"\n", "primary_key_name = \"product_category\"\n", "destination_dataset = governed_data_sdp_scan_dataset_name\n", "destination_table = \"product_category\"\n", "\n", "createSDPScan(project_id, product_category_sdp_scan_name, source_dataset, source_table, primary_key_name, destination_dataset, destination_table)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ua8Eda6bDWos" }, "outputs": [], "source": [ "# Monitor the Customer scan (it automatically starts so we monitor its progress)\n", "print(f\"You can see your scan jobs here: https://console.cloud.google.com/security/sensitive-data-protection/landing/inspection/jobs?hl=en&inv=1&invt=Abn-xA&project={project_id}&supportedpurview=project\")\n", "print()\n", "\n", "sdp_scan_state = getSDPScan(project_id, customer_sdp_scan_name)\n", "print(f\"sdp_scan_state: {sdp_scan_state}\")\n", "\n", "while sdp_scan_state[\"state\"] == \"PENDING\" or \\\n", " sdp_scan_state[\"state\"] == \"JOB_STATE_UNSPECIFIED\" or \\\n", " sdp_scan_state[\"state\"] == \"RUNNING\" or \\\n", " sdp_scan_state[\"state\"] == \"ACTIVE\" or \\\n", " sdp_scan_state[\"state\"] == \"CANCELING\":\n", " time.sleep(10)\n", " sdp_scan_state = getSDPScan(project_id, customer_sdp_scan_name)\n", " print(f\"sdp_scan_state: {sdp_scan_state}\")\n", "\n", "print()\n", "print(\"Customer Table: SDP Scan complete. You should see a new BigQuery dataset.\")\n", "print(\"The other tables might still be in progress. The product and product_category do not have sensitive data and will be empty.\")\n", "print()\n", "print(\"\")\n", "print(f\"You can see your tag templates here: https://console.cloud.google.com/dataplex/templates?hl=en&inv=1&invt=Abn-xA&project={project_id}&supportedpurview=project\")" ] }, { "cell_type": "markdown", "metadata": { "id": "42IxhtRRrvR-" }, "source": [ "### Clean Up" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "6lF2Z7skFbvf" }, "outputs": [], "source": [ "# Delete the scan\n", "\n", "user_input = input(f\"Do you want to delete your SDP scans(Y/n)?\")\n", "if user_input == \"Y\":\n", " print(\"This will not delete the dataset created by the scan.\")\n", " deleteSDPScan(project_id,customer_sdp_scan_name)\n", " deleteSDPScan(project_id,customer_transaction_sdp_scan_name)\n", " deleteSDPScan(project_id,product_sdp_scan_name)\n", " deleteSDPScan(project_id,product_category_sdp_scan_name)" ] }, { "cell_type": "markdown", "metadata": { "id": "ASQ2BPisXDA0" }, "source": [ "### Reference Links\n" ] }, { "cell_type": "markdown", "metadata": { "id": "rTY6xJdZ3ul8" }, "source": [ "- [REPLACE-ME](https://REPLACE-ME)" ] } ], "metadata": { "colab": { "collapsed_sections": [ "gXQ7R9BJ-L9v", "P6O1UluF-L9x", "HMsUvoF4BP7Y", "m65vp54BUFRi", "sZ6m_wGrK0YG", "JbOjdSP1kN9T", "bI-KJELZ1jgt", "PUPlSXZRFOlR", "9dK1HHsmFKRw", "UjAdTIgGFEdv", "W7J_T8H3FVq2", "t4LEftacFizp", "42IxhtRRrvR-", "ASQ2BPisXDA0" ], "name": "07-Sensitive-Data-Protection-Scan", "private_outputs": true, "provenance": [] }, "kernelspec": { "display_name": "Python 3", "name": "python3" }, "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 0 }

data-analytics-demos/bigquery-data-governance/colab-enterprise/07-Sensitive-Data-Protection-Scan.ipynb (793 lines of code) (raw):