00_prep_bq_procedures.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TFX -  Create BigQuery Stored Procedures\n",
    "\n",
    "This multi-part tutorial shows how to use Matrix Factorization algorithm in BigQuery ML to generate embeddings for items based on their co-occurrence statistics. The generated item embeddings can be then used to find similar items.\n",
    "\n",
    "The is notebook covers creating the BigQuery stored procedures executed by the TFX pipeline that automates the running the solution:\n",
    "1. [sp_ComputePMI](sql_scripts/sp_ComputePMI.sql) -  This computes the pointwise mutual information and store the results in the `item_cooc` table.\n",
    "2. [sp_TrainItemMatchingModel](sql_scripts/sp_TrainItemMatchingModel.sql) - This creates the `item_embedding_model` Matrix Factorization model using the data in the `item_cooc` table.\n",
    "3. [sp_ExractEmbeddings](sql_scripts/sp_ExractEmbeddings.sql) - This extracts the item embedding values from the `item_embedding_model`, aggregate the two embedding vectors produced for each item, and store them in the `item_embeddings` table.\n",
    "\n",
    "The notebook assumes that the `vw_item_groups`, which is created in [00_prep_bq_and_datastore.ipynb](00_import_bq_to_datastore.ipynb) notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q -U google-cloud-bigquery pyarrow"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from google.cloud import bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Configure GCP environment settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PROJECT_ID = 'chernikov-playground'\n",
    "BUCKET = 'cooltacular-gcp1'\n",
    "SQL_SCRIPTS_DIR = 'sql_scripts'\n",
    "BQ_DATASET_NAME = 'recommendations'\n",
    "\n",
    "!gcloud config set project $PROJECT_ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Authenticate your GCP account\n",
    "This is required if you run the notebook in Colab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "  from google.colab import auth\n",
    "  auth.authenticate_user()\n",
    "  print(\"Colab user is authenticated.\")\n",
    "except: pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Stored Procedures dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS recommendations.item_cooc\n",
    "AS SELECT 0 AS item1_Id, 0 AS item2_Id, 0 AS cooc, 0 AS pmi;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE MODEL IF NOT EXISTS recommendations.item_matching_model\n",
    "OPTIONS(\n",
    "    MODEL_TYPE='matrix_factorization', \n",
    "    USER_COL='item1_Id', \n",
    "    ITEM_COL='item2_Id',\n",
    "    RATING_COL='score'\n",
    ")\n",
    "AS\n",
    "SELECT 0 AS item1_Id, 0 AS item2_Id, 0 AS score;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute the BigQuery Scripts to Create the Procedures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = bigquery.Client(project=PROJECT_ID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_scripts = dict()\n",
    "\n",
    "for script_file in [file for file in os.listdir(SQL_SCRIPTS_DIR) if '.sql' in file]:\n",
    "  script_file_path = os.path.join(SQL_SCRIPTS_DIR, script_file)\n",
    "  sql_script = open(script_file_path, 'r').read()\n",
    "  sql_script = sql_script.replace('@DATASET_NAME', BQ_DATASET_NAME)\n",
    "  sql_scripts[script_file] = sql_script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for script_file in sql_scripts:\n",
    "  print(f'Executing {script_file} script...')\n",
    "  query = sql_scripts[script_file]\n",
    "  query_job = client.query(query)\n",
    "  result = query_job.result()\n",
    "\n",
    "print('Done.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## List the Created Procedures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = f'SELECT * FROM {BQ_DATASET_NAME}.INFORMATION_SCHEMA.ROUTINES;'\n",
    "query_job = client.query(query)\n",
    "query_job.result().to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## License\n",
    "\n",
    "Copyright 2020 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. You may obtain a copy of the License at: http://www.apache.org/licenses/LICENSE-2.0\n",
    "\n",
    "Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. \n",
    "\n",
    "See the License for the specific language governing permissions and limitations under the License.\n",
    "\n",
    "**This is not an official Google product but sample code provided for an educational purpose**"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "tf2-gpu.2-3.m61",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-3:m61"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}