03 - BigQuery ML (BQML)/03b - BQML Random Forest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "423ed02a-f6fd-4a0c-b251-eeae4ed3dda9",
   "metadata": {},
   "source": [
    "# 03c - BigQuery ML (BQML) - Випадковий ліс для класифікації\n",
    "\n",
    "У цьому зошиті BigQuery ML (BQML) використовується для створення моделі випадкового лісу (за допомогою XGBoost).\n",
    "\n",
    "**Випадковий ліс**\n",
    "\n",
    "Модель випадкового лісу - це ансамбль дерев рішень з використанням бєгінгу:\n",
    "- Дерево рішень: Підхід до навчання з вчителем, який знаходить розщеплення в ознаках, що представляють рішення щодо прогнозованого рівня цільової змінної.\n",
    "- Ансамбль дерев: Об'єднання дерев для формування суми прогнозів з декількох дерев \n",
    "- Бєгінг: Паралельне навчання на випадковій підмножині даних (спостережень і ознак) із заміною (кожна підмножина випадково вибирається з повного набору)\n",
    "\n",
    "\n",
    "**BigQuery ML (BQML)**\n",
    "\n",
    "BigQuery має ряд алгоритмів машинного навчання, які можна викликати безпосередньо з SQL.  Це дає зручність використання загальноприйнятої мови `SQL` з операторами типу `CREATE MODEL ...`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25e47252-397c-4c3a-a614-620af1e2c4a2",
   "metadata": {},
   "source": [
    "---\n",
    "## Налаштування"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01f73f31-8117-4032-8fe9-52e7c2e976dd",
   "metadata": {},
   "source": [
    "вхідні дані:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "385ee1c0-c802-48e6-afd7-9858ff737d99",
   "metadata": {},
   "outputs": [],
   "source": [
    "project = !gcloud config get-value project\n",
    "PROJECT_ID = project[0]\n",
    "PROJECT_ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0a63657-961f-4d6f-90b2-617154abd515",
   "metadata": {},
   "outputs": [],
   "source": [
    "REGION = 'us-central1'\n",
    "EXPERIMENT = 'random-forest'\n",
    "SERIES = '03'\n",
    "\n",
    "# source data\n",
    "BQ_PROJECT = PROJECT_ID\n",
    "BQ_DATASET = 'fraud'\n",
    "BQ_TABLE = 'fraud_prepped'\n",
    "\n",
    "# Resources for serving BigQuery Model Exports\n",
    "TF_DEPLOY_IMAGE = 'us-docker.pkg.dev/vertex-ai/prediction/tf2-cpu.2-3:latest'\n",
    "XGB_DEPLOY_IMAGE = 'us-docker.pkg.dev/vertex-ai/prediction/xgboost-cpu.0-82:latest'\n",
    "\n",
    "# Resources for serving BigQuery Model Exports\n",
    "DEPLOY_COMPUTE = 'n1-standard-4'\n",
    "\n",
    "# Model Training\n",
    "VAR_TARGET = 'Class'\n",
    "VAR_OMIT = 'transaction_id' # add more variables to the string with space delimiters"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c82de065-bfd9-4107-a71a-731727129a3f",
   "metadata": {},
   "source": [
    "пакети:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6431936-35e5-49d9-a905-ec76ac36c6d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "from google.cloud import aiplatform\n",
    "from datetime import datetime\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn import metrics\n",
    "import numpy as np\n",
    "\n",
    "from google.api import httpbody_pb2\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d733d37c-2801-4bf5-a266-c4a7fe5357ed",
   "metadata": {},
   "source": [
    "клієнти:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffc52eaf-fd70-4e76-a08f-af32dfe8bed6",
   "metadata": {},
   "outputs": [],
   "source": [
    "bq = bigquery.Client(project = PROJECT_ID)\n",
    "aiplatform.init(project = PROJECT_ID, location = REGION)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9514028-bb15-4412-920e-9e813e98afc3",
   "metadata": {},
   "source": [
    "параметри:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb4d3bff-9bf9-43e1-ac3b-c4b511b427dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "TIMESTAMP = datetime.now().strftime(\"%Y%m%d%H%M%S\")\n",
    "BUCKET = PROJECT_ID\n",
    "URI = f\"gs://{BUCKET}/{SERIES}/{EXPERIMENT}\"\n",
    "RUN_NAME = f'run-{TIMESTAMP}'\n",
    "\n",
    "BQ_MODEL = f'{SERIES}_{EXPERIMENT}_{TIMESTAMP}'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4045e724-e46c-47b6-847a-935d0e039f6b",
   "metadata": {},
   "source": [
    "оточення:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85512469-b11f-465c-b237-3427ba6a6f7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "DIR = f\"temp/{EXPERIMENT}\"\n",
    "\n",
    "!rm -rf {DIR}\n",
    "!mkdir -p {DIR}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6f67d8a-b2f1-4a1d-95ff-da78d24de584",
   "metadata": {},
   "source": [
    "---\n",
    "## Дані"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "477616f4-83f9-4a98-8916-f283e33ccde6",
   "metadata": {},
   "source": [
    "Перегляньте кількість записів для кожного рівня класу (`VAR_TARGET`) для кожного з розбиття даних:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1850803a-7de6-4a95-8f82-a64593210beb",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT splits, {VAR_TARGET}, count(*) as n\n",
    "FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "GROUP BY splits, {VAR_TARGET}\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b18b1556-be4e-440d-b839-4bb160d450a1",
   "metadata": {},
   "source": [
    "Далі перегляньте баланс цільової змінної (VAR_TARGET) для кожного поділу у відсотках від поділу:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea363ce8-c793-4233-9727-96e6573d99e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "WITH\n",
    "    COUNTS as (SELECT splits, {VAR_TARGET}, count(*) as n FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}` GROUP BY splits, {VAR_TARGET})\n",
    "    \n",
    "SELECT *,\n",
    "    SUM(n) OVER() as total,\n",
    "    SAFE_DIVIDE(n, SUM(n) OVER(PARTITION BY {VAR_TARGET})) as n_pct_class,\n",
    "    SAFE_DIVIDE(n, SUM(n) OVER(PARTITION BY splits)) as n_pct_split,\n",
    "    SAFE_DIVIDE(SUM(n) OVER(PARTITION BY {VAR_TARGET}), SUM(n) OVER()) as class_pct_total\n",
    "FROM COUNTS\n",
    "\"\"\"\n",
    "review = bq.query(query = query).to_dataframe()\n",
    "review"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4e476e3-124c-4539-b9d3-5570f737bfcf",
   "metadata": {},
   "source": [
    "---\n",
    "## У цьому розділі"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "851a0776-ba51-484f-80ed-4f7b42778510",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'Цей прогін створить BQML-модель: {BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}')\n",
    "print(f'Цей прогін створить Vertex AI модель: {BQ_PROJECT}.{BQ_DATASET}.bqml_{SERIES}_{EXPERIMENT}')\n",
    "print(f'Мітка часу: {TIMESTAMP}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "632a0d6f-e4fa-40e8-a4ed-813404c3f026",
   "metadata": {},
   "source": [
    "---\n",
    "## Тренуємо модель\n",
    "\n",
    "Використовуйте BigQuery ML для навчання моделі багатокласового випадкового лісу за допомогою XGBoost:\n",
    "- [Random Forest](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-random-forest) з BigQuery ML (BQML)\n",
    "- Тут використовується стовпець `split`, створений у блокноті `01`\n",
    "- `data_split_method = CUSTOM` використовує стовпець у `data_split_col` для призначення навчальних даних для значень `FALSE` та оціночних даних для значень `TRUE`.\n",
    "    - У цьому прикладі інструкція `WHERE splits != 'TEST'` відфільтровує тестові дані\n",
    "    - оператор `CASE` зіставляє навчальні дані зі значенням FALSE, а дані перевірки зі значенням TRUE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c73ea6dd-7ec8-46dd-aca2-699f6c72ef8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "CREATE OR REPLACE MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`\n",
    "OPTIONS\n",
    "    (model_type = 'RANDOM_FOREST_CLASSIFIER',\n",
    "        auto_class_weights = TRUE,\n",
    "        input_label_cols = ['{VAR_TARGET}'],\n",
    "        enable_global_explain = TRUE,\n",
    "        data_split_col = 'custom_splits',\n",
    "        data_split_method = 'CUSTOM',\n",
    "        num_parallel_tree = 200,\n",
    "        #early_stop = TRUE,\n",
    "        #min_rel_progress = 0.01,\n",
    "        tree_method = 'HIST',\n",
    "        subsample = 0.85,\n",
    "        colsample_bytree = 0.9,\n",
    "        MODEL_REGISTRY = 'VERTEX_AI',\n",
    "        VERTEX_AI_MODEL_ID = 'bqml_{SERIES}_{EXPERIMENT}',\n",
    "        VERTEX_AI_MODEL_VERSION_ALIASES = ['{RUN_NAME}']\n",
    "    ) AS\n",
    "SELECT * EXCEPT({','.join(VAR_OMIT.split())}, splits),\n",
    "    CASE\n",
    "        WHEN splits = 'TRAIN' THEN FALSE\n",
    "        ELSE TRUE\n",
    "    END AS custom_splits\n",
    "FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "WHERE splits != 'TEST'\n",
    "\"\"\"\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "118cf076-90f1-403c-9d8e-1ae6b1907426",
   "metadata": {},
   "outputs": [],
   "source": [
    "job = bq.query(query = query)\n",
    "job.result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3a6b5f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "(job.ended-job.started).total_seconds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de17d955-6c31-46e6-8fb5-f9fc44be2257",
   "metadata": {},
   "outputs": [],
   "source": [
    "job.total_bytes_processed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9803c8a1-441c-48d3-b01d-126e8d93d6bf",
   "metadata": {},
   "source": [
    "Додайте мітки до моделі у BigQuery:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13d696f2-a277-422c-9cb0-f2ea8b5b242f",
   "metadata": {},
   "outputs": [],
   "source": [
    "model = bq.get_model(f'{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}')\n",
    "model.labels = {'series' : f'{SERIES}', 'experiment' : f'{EXPERIMENT}'}\n",
    "model = bq.update_model(model, ['labels'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "295f79dc-efe3-4c1f-a2a5-8493f42f060f",
   "metadata": {},
   "source": [
    "### Перевірте цю модель у BigQuery Console:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e612ea8-ff94-4100-8293-5e2d691cfc69",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'BigQuery Console для проєкту:\\nhttps://console.cloud.google.com/bigquery?project={PROJECT_ID}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a70136c5-da05-4083-b45e-1c19d4a32c3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'Пряме посилання на модель у BigQuery:\\nhttps://console.cloud.google.com/bigquery?project={PROJECT_ID}&ws=!1m5!1m4!5m3!1s{PROJECT_ID}!2s{BQ_DATASET}!3s{BQ_MODEL}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "986583a9-ba11-4631-81e4-fe00e599df34",
   "metadata": {},
   "source": [
    "### Ознайомтеся з цією моделлю в Реєстрі Vertex AI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3a7c3c4-b473-4dcb-8e00-b399982a0673",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'Ознайомтеся з моделлю в Реєстрі Vertex AI:\\nhttps://console.cloud.google.com/vertex-ai/locations/{REGION}/models/bqml_{BQ_MODEL}?project={PROJECT_ID}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63b9817b-76ae-4689-84ac-d1d027f82462",
   "metadata": {},
   "source": [
    "### Огляд фічей та навчання моделі"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1973a992-b888-48b5-9f5d-9947a1ab89bc",
   "metadata": {},
   "source": [
    "Перегляньте вхідні дані моделі [ML.FEATURE_INFO](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-feature):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36427328-d9da-48ef-9747-daa093bd7793",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_info = bq.query(\n",
    "    query = f\"\"\"\n",
    "        SELECT *\n",
    "        FROM ML.FEATURE_INFO(MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`)\n",
    "        \"\"\"\n",
    ").to_dataframe()\n",
    "feature_info"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5dae2f8-5cd7-42d2-a10d-46cf22eb8974",
   "metadata": {},
   "source": [
    "Перегляньте ітерації навчання [ML.TRAINING_INFO](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-train):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7626f61a-feae-45d6-a166-4209105875aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "history = bq.query(\n",
    "    query = f\"\"\"\n",
    "        SELECT *\n",
    "        FROM ML.TRAINING_INFO(MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`)\n",
    "        ORDER BY iteration\"\"\"\n",
    ").to_dataframe()\n",
    "history"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce0360da-8191-4a63-8bf8-f85444931cd7",
   "metadata": {},
   "source": [
    "---\n",
    "## Оцінювання моделі"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d57880e-58a4-4540-89a2-feb29601b3a3",
   "metadata": {},
   "source": [
    "### Метрики"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e57755ea-9bd2-4ae4-a466-edd5d8f0eb45",
   "metadata": {},
   "source": [
    "Перегляньте статистику оцінювання моделі на сплітах за допомогою [ML.EVALUATE](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-evaluate):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe52da8e-2cdb-4292-9698-1c5b74522508",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT 'TEST' as SPLIT, * FROM ML.EVALUATE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,\n",
    "    (SELECT * FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}` WHERE SPLITS='TEST'))\n",
    "UNION ALL\n",
    "SELECT 'VALIDATE' as SPLIT, * FROM ML.EVALUATE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,\n",
    "    (SELECT * FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}` WHERE SPLITS='VALIDATE'))\n",
    "UNION ALL\n",
    "SELECT 'TRAIN' as SPLIT, * FROM ML.EVALUATE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,\n",
    "    (SELECT * FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}` WHERE SPLITS='TRAIN'))\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e179723a-dce6-444f-9f19-bb1fcee1efe3",
   "metadata": {},
   "source": [
    "### Confusion Matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8f0c1ff-7c7f-4722-b325-168ac9362e23",
   "metadata": {},
   "source": [
    "Перегляд матриці [ML.CONFUSION_MATRIX](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-confusion):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ee513cd-3205-4419-a380-f687d8504cc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.CONFUSION_MATRIX (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'TRAIN')\n",
    "  )\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df9f248a-e612-4f21-b261-89a13cd30309",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.CONFUSION_MATRIX (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'VALIDATE')\n",
    "  )\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6b9c391-9e6b-434b-9eb0-b22e967d90ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.CONFUSION_MATRIX (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'TEST')\n",
    "  )\n",
    "\"\"\"\n",
    "bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a67e9c22-06fb-40ec-891b-0d06e520007e",
   "metadata": {},
   "source": [
    "### ROC крива\n",
    "\n",
    "Перегляд даних ROC-кривої [ML.ROC_CURVE](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-roc):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49d1bf40-95b0-4905-9cca-4bfd0bf262cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.ROC_CURVE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'TRAIN')\n",
    "  )\n",
    "ORDER BY false_positive_rate\n",
    "\"\"\"\n",
    "train_roc = bq.query(query = query).to_dataframe()\n",
    "train_roc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "526e8047-1204-468d-8ab9-d73e2c3e9230",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.ROC_CURVE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'VALIDATE')\n",
    "  )\n",
    "ORDER BY false_positive_rate\n",
    "\"\"\"\n",
    "val_roc = bq.query(query = query).to_dataframe()\n",
    "val_roc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f11e5ff-6427-4a24-bd9d-d14959090a83",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.ROC_CURVE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'TEST')\n",
    "  )\n",
    "ORDER BY false_positive_rate\n",
    "\"\"\"\n",
    "test_roc = bq.query(query = query).to_dataframe()\n",
    "test_roc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cad414b4-cbc5-4698-a986-77965aac942b",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(15,5))\n",
    "plt.plot(train_roc['false_positive_rate'], train_roc['recall'], label='TRAIN')\n",
    "plt.plot(val_roc['false_positive_rate'], val_roc['recall'], label='VALIDATE')\n",
    "plt.plot(test_roc['false_positive_rate'], test_roc['recall'], label='TEST')\n",
    "plt.xlabel('False Positive Rate')\n",
    "plt.ylabel('True Positive Rate\\nRecall')\n",
    "plt.title('ROC Curves')\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf3e47a4-8263-418a-94a2-4281eeb75474",
   "metadata": {},
   "source": [
    "---\n",
    "## Прогнозування з BigQuery ML (BQML)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c67fd17-b3e3-4bbd-9dbf-adb5065b3022",
   "metadata": {},
   "source": [
    "Створення pandas-датафрейм для отримання прогнозів [ML.PREDICT](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-predict):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ef98b8e-35b9-4a51-966f-992d681991b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.PREDICT (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'TEST')\n",
    "  )\n",
    "\"\"\"\n",
    "pred = bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "375a6918-e62e-41c2-871d-b1745a6d19aa",
   "metadata": {},
   "source": [
    "Перегляд стовпчика прогнозів - стовпчик з префіксом `predicted_`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0696049a-f85c-4417-888b-edc75a460164",
   "metadata": {},
   "outputs": [],
   "source": [
    "pred.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab0d0f78-811e-49cd-bfb6-56ba8d6f560b",
   "metadata": {},
   "source": [
    "Виведіть перші кілька рядків для стовпців, що відносяться до фактичних та прогнозованих значень:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5d027fd-e036-4774-8712-059ed91272eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "pred[[VAR_TARGET, f'predicted_{VAR_TARGET}', f'predicted_{VAR_TARGET}_probs', 'splits']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85cd8a9d-a526-4b96-8d42-a8cc6ad83abc",
   "metadata": {},
   "source": [
    "Зверніть увагу на вкладений словник для прогнозованих ймовірностей.  У BigQuery це структура типу Record з вкладеними полями для `label` і `prob`.  Він повертається до фрейму даних pandas як вкладений словник.\n",
    "\n",
    "Наступний код сортує словник для першого запису за `prob`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20ffaa54-cb02-46b7-b53d-e87dfeddeca9",
   "metadata": {},
   "outputs": [],
   "source": [
    "exec('temp = pred.predicted_'+VAR_TARGET+'_probs[0]')\n",
    "[sorted(x, key = lambda x: x['label']) for x in [temp]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6393e79-cd78-427e-a9a7-45fa43658921",
   "metadata": {},
   "source": [
    "---\n",
    "## Кастомні метрики"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2646ff28-4991-4d82-b32b-9f25f311edae",
   "metadata": {},
   "outputs": [],
   "source": [
    "metrics.confusion_matrix(list(pred[f'{VAR_TARGET}']), list(pred[f'predicted_{VAR_TARGET}']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9648d9fa-7739-4e10-90eb-5956ee5dbb0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "disp = metrics.ConfusionMatrixDisplay(\n",
    "    confusion_matrix = metrics.confusion_matrix(list(pred[f'{VAR_TARGET}']), list(pred[f'predicted_{VAR_TARGET}']))\n",
    ")\n",
    "disp.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7594f48a-4d3c-4e99-8c18-be0b402a36b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "metrics.accuracy_score(list(pred[f'{VAR_TARGET}']), list(pred[f'predicted_{VAR_TARGET}']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75d2c4d7-6622-4629-bc8b-8c1bb4632e27",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(metrics.classification_report(list(pred[f'{VAR_TARGET}']), list(pred[f'predicted_{VAR_TARGET}'])))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "099691b5-a7b9-4b9e-be4b-ec00b751c142",
   "metadata": {},
   "source": [
    "---\n",
    "## Інтерпретація\n",
    "\n",
    "[Вбудовані](https://cloud.google.com/bigquery/docs/xai-overview) можливості для розуміння того, як особливості сприяли прогнозуванню як на індивідуальному (локальному), так і на загальному (глобальному) рівнях."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f97d19f-163d-4121-9c77-39fceafab2c1",
   "metadata": {},
   "source": [
    "### Локальна пояснюваність\n",
    "\n",
    "Отримати значення атрибуції ознак для інтерпретації внеску кожної ознаки у прогноз відносно базового прогнозу за допомогою [ML.EXPLAIN_PREDICT](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-explain-predict)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5fd7e17a-d5db-47b0-8371-1681b42be3f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.EXPLAIN_PREDICT (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`,(\n",
    "    SELECT *\n",
    "    FROM `{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}`\n",
    "    WHERE splits = 'TEST'), STRUCT(10 as top_k_features)\n",
    "  )\n",
    "\"\"\"\n",
    "explain = bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ae65dbb-7059-4c4b-ae48-7bc7b3c53ba1",
   "metadata": {},
   "outputs": [],
   "source": [
    "explain.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c75e7fa0-af70-4371-bd7b-2bbd65dcacb9",
   "metadata": {},
   "source": [
    "Графік атрибуцій ознак для одного прогнозу:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09ff4fc8-a20f-429f-bfd2-2c0dbaeea325",
   "metadata": {},
   "outputs": [],
   "source": [
    "explanation = explain.iloc[0]['top_feature_attributions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ff1a952-885b-48e4-83fb-7586d41a8c21",
   "metadata": {},
   "outputs": [],
   "source": [
    "features = []\n",
    "scores = []\n",
    "for k in explanation:\n",
    "    features.append(k['feature'])\n",
    "    scores.append(k['attribution'])\n",
    "features = [x for _, x in sorted(zip(scores, features))]\n",
    "scores = sorted(scores)\n",
    "\n",
    "plt.figure(figsize=(15,5))\n",
    "plt.barh(features, scores)\n",
    "plt.title('Feature Attribution for Prediction')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "030b067b-33b2-427d-a509-a094a5d56e64",
   "metadata": {},
   "source": [
    "### Глобальна інтепретація\n",
    "\n",
    "Отримайте вплив кожної ознаки на модель за допомогою [ML.GLOBAL_EXPLAIN](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-global-explain).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ef61160-5723-421e-bd81-1bbc388a8562",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.GLOBAL_EXPLAIN (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`)\n",
    "\"\"\"\n",
    "gexplain = bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e576d895-17a5-4103-be98-414eb40f2f40",
   "metadata": {},
   "outputs": [],
   "source": [
    "gexplain.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1724a6f0-e15c-4f91-96ec-a0472689c608",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(15,8))\n",
    "plt.barh(gexplain['feature'], gexplain['attribution'])\n",
    "plt.title('Global Influence')\n",
    "plt.xlabel('Attribution')\n",
    "#plt.ylabel('Feature')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94e0b28f-268b-4dce-83ad-be02229f002b",
   "metadata": {},
   "source": [
    "### Впливовість змінних\n",
    "\n",
    "Отримайте значення [XGBoost feature importances](https://xgboost.readthedocs.io/en/latest/python/python_api.html#xgboost.XGBRegressor.feature_importances_) для моделі за допомогою [ML.FEATURE_IMPORTANCE](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-importance)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea3fbd20-aace-463c-a581-5c9eb82d18f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM ML.FEATURE_IMPORTANCE (MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`)\n",
    "\"\"\"\n",
    "feature_importance = bq.query(query = query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04e8a31f-5e9f-41b1-9987-ea8537976f34",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_importance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1c5b147-a9ec-4251-9155-512a38aff7f7",
   "metadata": {},
   "source": [
    "---\n",
    "## Обслуговування моделі у Vertex AI\n",
    "\n",
    "Реєстрація моделі у Vertex AI.  Це спрощує розгортання моделі на [Vertex AI Endpoint для онлайн-сервісу](https://cloud.google.com/vertex-ai/docs/general/deployment)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af3469ad-e3b9-4f88-b26c-5576ac235953",
   "metadata": {},
   "source": [
    "### Vertex AI Model Registry - Отримати модель\n",
    "\n",
    "Посилання:\n",
    "- [aiplatform.Model()](https://cloud.google.com/python/docs/reference/aiplatform/latest/google.cloud.aiplatform.Model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67ef0ccf-5c06-405d-93c0-88bcb6b7af78",
   "metadata": {},
   "outputs": [],
   "source": [
    "vertex_model = aiplatform.Model(model_name = f'bqml_{SERIES}_{EXPERIMENT}')\n",
    "vertex_model.resource_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e33688f-6921-4967-9697-e6f2636502a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "aiplatform.ModelEvaluation(\n",
    "    evaluation_name = vertex_model.get_model_evaluation().resource_name\n",
    ").to_dict()['metrics']['confusionMatrix']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ffdc177-f523-45f7-848b-76a6049cfff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# примітка: це поле порожнє, оскільки моделлю керує BQML, а контейнер попередньо визначено\n",
    "vertex_model.uri "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef9c10ad-171c-4544-9304-cd3f02470a2d",
   "metadata": {},
   "source": [
    "### Vertex AI Endpoint - Створення\n",
    "\n",
    "Посилання:\n",
    "- [aiplatform.Endpoint()](https://cloud.google.com/python/docs/reference/aiplatform/latest/google.cloud.aiplatform.Endpoint)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cde7f56-e07f-42fd-bec1-4e1d12bdd591",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not aiplatform.Endpoint.list(filter = f\"display_name={SERIES}_{EXPERIMENT}\"):\n",
    "    endpoint = aiplatform.Endpoint.create(\n",
    "        display_name = f\"{SERIES}_{EXPERIMENT}\",\n",
    "        labels = {'series' : f\"{SERIES}_{EXPERIMENT}\"}\n",
    "    )\n",
    "    print(f\"Endpoint Created: {endpoint.display_name}\")\n",
    "else:\n",
    "    endpoint = aiplatform.Endpoint.list(filter = f\"display_name={SERIES}_{EXPERIMENT}\")[0]\n",
    "    print(f\"Endpoint Found: {endpoint.display_name}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26272c00-32a4-47e9-89e4-356eca1b77c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint.display_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c8f410e-ce0f-4fd3-9706-f48e98ad310e",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint.traffic_split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d707ba4-8c1c-41b0-8cf9-5bf5eb3cf806",
   "metadata": {},
   "outputs": [],
   "source": [
    "deployed_models = endpoint.list_models()\n",
    "deployed_models"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f318443-3888-454e-9f13-b4f7d2cedc56",
   "metadata": {},
   "source": [
    "### Розгортання моделі у Vertex AI Endpoint\n",
    "\n",
    "**15+ хвилин**\n",
    "\n",
    "ПОсилання:\n",
    "- [aiplatform.Endpoint.deploy()](https://cloud.google.com/python/docs/reference/aiplatform/latest/google.cloud.aiplatform.Endpoint#google_cloud_aiplatform_Endpoint_deploy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3307b83b-f81c-40bd-8eca-6985fdc8f85c",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint.deploy(\n",
    "    model = vertex_model,\n",
    "    deployed_model_display_name = vertex_model.display_name,\n",
    "    traffic_percentage = 100,\n",
    "    machine_type = 'n1-standard-2',\n",
    "    min_replica_count = 1,\n",
    "    max_replica_count = 1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "145c0fb5-5fd0-485b-a514-faf5142a1036",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint.list_models()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "616986c7-1dd2-4b0a-a036-9b36e1ff660b",
   "metadata": {},
   "source": [
    "---\n",
    "## Прогнозування з Vertex AI Endpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93a94fff-4288-469b-bb45-9dde97111341",
   "metadata": {},
   "source": [
    "### Записи для прогнозування"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fbcb2a8-0152-43ac-bae3-35c7d9be6789",
   "metadata": {},
   "outputs": [],
   "source": [
    "n = 10\n",
    "pred = bq.query(\n",
    "    query = f\"\"\"\n",
    "        SELECT * EXCEPT({VAR_TARGET}, {VAR_OMIT}, splits)\n",
    "        FROM {BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}\n",
    "        WHERE splits='TEST'\n",
    "        LIMIT {n}\n",
    "        \"\"\"\n",
    ").to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e186988-69d3-406a-8805-b108385d1570",
   "metadata": {},
   "source": [
    "Форматування"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4719317b-20ad-4d0c-867e-067996d1ca4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "newobs = pred.to_dict(orient = 'records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c21f8f72-e067-452e-8bff-310bce28c0f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "newobs[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1ad617e-bf78-4973-806c-ac85e5d92c49",
   "metadata": {},
   "source": [
    "### Отримання прогнозів: Python Client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5598fda3-dda5-42c2-b8af-0679902d74b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Можуть бути проблеми через версію Python\n",
    "prediction = endpoint.predict(instances = newobs[0:1])\n",
    "prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fb73d55-1a57-47c0-b4d1-9db9201e2fb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "prediction.predictions[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a12c7e41-b703-4c0b-9480-696b19e34669",
   "metadata": {},
   "outputs": [],
   "source": [
    "np.argmax(prediction.predictions[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "000c02f2-3cfe-440f-a522-5034f4c7a9d0",
   "metadata": {},
   "source": [
    "### Отримання прогнозів: REST\n",
    "Посилання:\n",
    "- [POST https://{REGION}-aiplatform.googleapis.com/v1/{endpoint}:predict](https://cloud.google.com/python/docs/reference/aiplatform/latest/google.cloud.aiplatform_v1.services.prediction_service.PredictionServiceClient#google_cloud_aiplatform_v1_services_prediction_service_PredictionServiceClient_predict)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f8a6097-4455-4cae-8279-56a8a5ea246f",
   "metadata": {},
   "source": [
    "Запит:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b645ce8-fd24-4265-b4c7-c5decf00d6c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(f'{DIR}/request.json','w') as file:\n",
    "    file.write(json.dumps({\"instances\": newobs[0:1]}))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf2f2839-664c-4aa4-89a9-3fc6c023b7a0",
   "metadata": {},
   "source": [
    "Прогноз:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4e18288-f8e8-4b12-8af6-47f15db7fa16",
   "metadata": {},
   "outputs": [],
   "source": [
    "prediction = !curl -X POST \\\n",
    "-H \"Authorization: Bearer \"$(gcloud auth application-default print-access-token) \\\n",
    "-H \"Content-Type: application/json; charset=utf-8\" \\\n",
    "-d @{DIR}/request.json \\\n",
    "https://{REGION}-aiplatform.googleapis.com/v1/{endpoint.resource_name}:predict\n",
    "\n",
    "prediction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b975e20e-8fcb-47ca-8432-3d555ef6ab74",
   "metadata": {},
   "source": [
    "### Отримання прогнозів: gcloud (CLI)\n",
    "Посилання:\n",
    "- [gcloud ai endpoints predict](https://cloud.google.com/sdk/gcloud/reference/ai/endpoints/predict)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bff18a5d-da5f-403c-8a02-b94f023eba46",
   "metadata": {},
   "source": [
    "Запит"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc1730e5-afcc-44ed-97cf-743d59833f43",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(f'{DIR}/request.json','w') as file:\n",
    "    file.write(json.dumps({\"instances\": newobs[0:1]}))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac196dd9-8e63-47d3-96e1-91feeaab2c0f",
   "metadata": {},
   "source": [
    "Прогноз:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fb16975-a3e8-4506-8e24-9de87bb17af3",
   "metadata": {},
   "outputs": [],
   "source": [
    "prediction = !gcloud beta ai endpoints predict {endpoint.name.rsplit('/',1)[-1]} --region={REGION} --json-request={DIR}/request.json\n",
    "prediction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e40dae42-5776-4326-acb9-6d20ffe145e3",
   "metadata": {},
   "source": [
    "---\n",
    "## Експорт моделі"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90d49cc6-6f97-44eb-9692-2e147206a60c",
   "metadata": {},
   "source": [
    "### Експорт моделі BigQuery\n",
    "\n",
    "\n",
    "Моделі, навчені на BQML, можна експортувати з BigQuery до GCS:\n",
    "\n",
    "Посилання:\n",
    "- [Exporting Models](https://cloud.google.com/bigquery/docs/exporting-models)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "726c3efd-689b-4613-8169-a4e22f6b7ae7",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "EXPORT MODEL `{BQ_PROJECT}.{BQ_DATASET}.{BQ_MODEL}`\n",
    "    OPTIONS (URI = 'gs://{BUCKET}/{SERIES}/{EXPERIMENT}/models/{TIMESTAMP}/model')\n",
    "\"\"\"\n",
    "export = bq.query(query = query)\n",
    "export.result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38a2fc5a-0ef2-480b-a641-0602079eab32",
   "metadata": {},
   "outputs": [],
   "source": [
    "(export.ended - export.started).total_seconds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "990090fa-0727-4e99-a22f-dcf6efbe94b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "listing = !gsutil ls gs://{BUCKET}/{SERIES}/{EXPERIMENT}/models/{TIMESTAMP}/model\n",
    "listing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95b2a2bc-5cce-4c1d-a5f5-61d2911906c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"Переглянути:\\nhttps://console.cloud.google.com/storage/browser/{BUCKET}/{SERIES}/{EXPERIMENT}/models/{TIMESTAMP}/model?project={PROJECT_ID}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "539e0868-46bc-4fbd-9912-1a5eeeebf7da",
   "metadata": {},
   "source": [
    "### Визначте тип фреймворку моделей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c285a97-8507-471d-8348-72c5454e1b0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "filetype = {'pb': 'TensorFlow', 'bst': 'XGBoost'}\n",
    "\n",
    "filetypes = [filetype[f.split('.')[-1]] for f in listing if f.split('.')[-1] in ['pb','bst']]\n",
    "if len(list(set(filetypes))) == 1:\n",
    "    framework = filetypes[0]\n",
    "    print(f'framework = {framework}')\n",
    "else:\n",
    "    raise TypeError(f\"Тип моделі не визначено!!! Переглянути вміст 'listing =' {listing}'.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37b8fe09-64b4-451e-bbbf-569368eee09c",
   "metadata": {},
   "outputs": [],
   "source": [
    "list(set(filetypes))"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-11.m104",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-11:m104"
  },
  "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}