01_Lab_Setup.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "<div style=\"width:100%; background-color: #121017;\"><a target=\"_blank\" href=\"http://university.yugabyte.com?utm_source=gitpod&utm_medium=notebook\"><img src=\"assets/YBU_Logo.png\" /></a></div><br>\n",
    "\n",
    "> **Migrate and Modernize with YugabyteDB Voyager**\n",
    ">\n",
    "> Enroll for free at  [Yugabyte University](https://university.yugabyte.com/courses/YugabyteDB-Voyager?utm_source=gitpod&utm_medium=notebook).\n",
    ">\n",
    "\n",
    "<br>\n",
    "This notebook file is:\n",
    "\n",
    "`01_Lab_Setup.ipynb`"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# About this notebook environment\n",
    "The labs for this course consists of several Jupyter notebooks that run in Gitpod. [Gitpod](https://www.gitpod.io/) facilitates runtime environments using Ubuntu and VS Code in a Chromium (browser) environment. Gitpod is free. All you need is a [GitHub](https://github.com/join) account.\n",
    "\n",
    "A Gitpod instance belongs to your Gitpod workspace. The instance automatically runs a three node, YugabyteDB database cluster. YugabyteDB is the world's #1 open source, distributed SQL database. \n",
    "\n",
    "> Although a three node cluster is up and running, Gitpod does not support visiting loopback addresses over a web ui, even if exposed on a different port.\n",
    "> 127.0.0.1 is the only web user interface that is exposed. To see all available ports in Gitpod, in the Terminal tab, you can run `gp ports list`.\n",
    "> "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 🛠️ Requirements\n",
    "Here are the requirements for this lab:\n",
    "- ✅ Launch using this Gitpod workspace, which you are already doing\n",
    "- ✅ A running, standalone MySQL database, which is already running\n",
    "- ✅ A running, 3 node YugabyteDB cluster, which is already running\n",
    "- ☑️ A Python 3.12 or higher kernel for the notebook, *which you need to select right now!!!*\n",
    "- ☑️ Fix Pop-ups Blocked, *which you need to select right now!!!*\n",
    "- ☑️ Connect to YugabyteDB with `ysqlsh`, YSQL shell, and create the database, *which you need to do next*\n",
    "\n",
    "### Select your notebook kernel\n",
    "- In the Notebook toolbar, click **Select Kernel**.\n",
    "<br>\n",
    "<img width=50% src=\"assets/01_01_Select_Kernel_Toolbar.png\" />\n",
    "\n",
    "- Next, in the dropdown, select **Python 3.12** or higher.\n",
    "<br>\n",
    "<img width=50% src=\"assets/01_02_Select_Kernel_Dropdown.png\" />\n",
    "\n",
    "### Fix Pop-ups Blocked\n",
    "- In your Chromium browser, select the Pop-ups Blocked icon in the browser address bar.\n",
    "- In the dialog, select **Always allow**. This lets you view the YugabyteD web user interface on port `15433`.\n",
    "  <br>\n",
    "  <img width=25% src=\"assets/01_03_Popups_Blocked.png\" />\n",
    "\n",
    "<br>\n",
    "That's it!"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>About Jupyter notebooks</summary>\n",
    "\n",
    "## About Jupyter notebooks\n",
    "\n",
    "A Juypter notebook is a runtime environment for executing code. A notebook consists of one or more cells. There are three types of cells in a notebook: Markdown, Code, and Output. This is a Markdown cell.\n",
    "\n",
    "A cell has two modes: read-mode and edit-mode. By default, the cells in a notebook are in read-mode. You can run a read-mode cell that is code. To execute a code cell, select the play icon in the cell's left gutter. \n",
    "\n",
    "The execution of a code cell often results in Output. An Output cell shows the `stdout` of the previous executed code cell. You can delete an Output cell. You can also collapse the cell. In addition, you can also select `Clear Outputs of All Cells`.\n",
    "\n",
    "To modify a cell, select `Enter` or `Return`. In edit-mode, you can modify the contents of a cell. Certain labs contain challenges or experiments that require you to do just that: modify a code cell and re-run it! To switch from edit-mode to read-mode, use the `Shift+Enter` or `Shift+Return` keystroke.\n",
    "\n",
    "### Notebook keyboard shortcuts\n",
    "The Jupyter extension for Gitpod supports the following keyboard shortcuts:\n",
    "\n",
    "| Keystroke | Description |\n",
    "|--|--|\n",
    "| ESC | Change the cell mode |\n",
    "| A | Add a cell above |\n",
    "| B | Add a cell below |\n",
    "| J or down arrow key |  Change a cell to below | \n",
    "| K or up arrow key | Change a cell to above | \n",
    "| Ctrl+Enter | Run the currently selected cell |\n",
    "| Shift+Enter | Run the currently selected cell and insert a new cell immediately below (focus moves to new cell) |\n",
    "| Alt+Enter | Run the currently selected cell and insert a new cell immediately below (focus remains on current cell) |\n",
    "| dd | Delete a selected cell |\n",
    "| z | Undo the last change | \n",
    "| M | switch the cell type to Markdown | \n",
    "| Y | switch the cell type to code |\n",
    "| L | Enable/Disable line numbers |\n",
    "\n",
    "\n",
    "  </details>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ⛑️ Getting help\n",
    "The best way to get help us from the Yugabyte University team. You can post your question in YugabyteDB Community Slack in the #training or #yb-university channels. Join our [community](https://communityinviter.com/apps/yugabyte-db/register)."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "## 👣 Set up steps for this notebook\n",
    "Here are the steps to setup this lab:\n",
    "- Create the notebook styles \n",
    "- Create the notebook variables\n",
    "- Create the `db_ybu` database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 👇 Create the notebook styles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import HTML\n",
    "def css_styling():\n",
    "    styles = open(\"./styles/custom.css\", \"r\").read()\n",
    "    return HTML(styles)\n",
    "css_styling()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "### 👇 Create the notebook variables \n",
    "\n",
    "> 👉 IMPORTANT! 👈\n",
    "> \n",
    "> Do **NOT** skip running this cell. \n",
    "> \n",
    "\n",
    "The following Python cell creates and stores variables that all the notebooks in this lab will use. You can view these variables in the Jupyter tab.\n",
    "\n",
    "- To run the script, select Execute Cell (Play Arrow) in the left gutter of the cell.\n",
    "- Verify the accuracy of the output values\n",
    "\n",
    "👇 👇 👇 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [],
   "source": [
    "# Env variables for Notebook\n",
    "import os\n",
    "\n",
    "# read env_vars.env\n",
    "env_vars = !cat env_vars.env\n",
    "for var in env_vars:\n",
    "    key, value = var.split('=')\n",
    "    os.environ[key] = value\n",
    "\n",
    "### Begin: Gitpod or local\n",
    "#\n",
    "### Uncomment Gitpod specific\n",
    "#\n",
    "NB_YB_PATH_BIN=os.environ.get('ENV_YB_PATH_BIN_GITPOD')\n",
    "NB_YB_PATH_DATA=os.environ.get('ENV_YB_PATH_DATA_GITPOD')\n",
    "NB_GITPOD_WORKSPACE_URL=os.environ.get('GITPOD_WORKSPACE_URL')\n",
    "#\n",
    "\n",
    "\n",
    "# env_vars defines the following\n",
    "NB_DB_NAME=os.environ.get('ENV_DB_NAME')\n",
    "NB_DB_PORT=os.environ.get('ENV_DB_PORT')\n",
    "NB_MYSQL_DB_NAME=os.environ.get('ENV_MYSQL_DB_NAME')\n",
    "\n",
    "\n",
    "NB_HOST_IPv4_01=os.environ.get('ENV_HOST_IPv4_01')\n",
    "NB_HOST_IPv4_02=os.environ.get('ENV_HOST_IPv4_02')\n",
    "NB_HOST_IPv4_03=os.environ.get('ENV_HOST_IPv4_03')\n",
    "\n",
    "NB_MYSQL_PORT=os.environ.get('ENV_MYSQL_PORT')\n",
    "\n",
    "\n",
    "NB_MASTER_WEB_PORT=os.environ.get('ENV_MASTER_WEB_PORT')\n",
    "NB_TSERVER_WEBSERVER_PORT=os.environ.get('ENV_TSERVER_WEBSERVER_PORT')\n",
    "NB_YUGABYTED_WEB_UI_PORT=os.environ.get('ENV_YUGABYTED_WEB_UI_PORT')\n",
    "\n",
    "NB_YB_MASTER_HOST_GITPOD_URL = NB_GITPOD_WORKSPACE_URL.replace('https://','https://'+NB_MASTER_WEB_PORT+'-')\n",
    "NB_YB_TSERVER_HOST_GITPOD_URL = NB_GITPOD_WORKSPACE_URL.replace('https://','https://'+NB_TSERVER_WEBSERVER_PORT+'-')\n",
    "NB_YUGABYTED_UI_HOST_GITPOD_URL = NB_GITPOD_WORKSPACE_URL.replace('https://','https://'+NB_YUGABYTED_WEB_UI_PORT+'-')\n",
    "\n",
    "# Current directory of project and related child folders\n",
    "NB_NOTEBOOK_DIR=os.getcwd()\n",
    "NB_NOTEBOOK_DATA_FOLDER=NB_NOTEBOOK_DIR +'/data' \n",
    "NB_NOTEBOOK_UTILS_FOLDER=NB_NOTEBOOK_DIR + '/utils'\n",
    "\n",
    "# Util files\n",
    "NB_UTIL_FUNCTIONS_FILE=os.environ.get(\"ENV_UTIL_FUNCTIONS_FILE\")\n",
    "NB_UTIL_YBTSERVER_METRICS_FILE=os.environ.get(\"ENV_UTIL_YBTSERVER_METRICS_FILE\")\n",
    "\n",
    "# For Voyager files so that we can edit\n",
    "NB_NOTEBOOK_VOYAGER_TMP=NB_NOTEBOOK_DIR +'/tmp'\n",
    "\n",
    "# BETA_FAST_DATA_EXPORT=1\n",
    "BETA_FAST_DATA_EXPORT=os.environ.get('BETA_FAST_DATA_EXPORT')\n",
    "\n",
    "# For end migration\n",
    "TARGET_DB_PASSWORD=os.environ.get('TARGET_DB_PASSWORD')\n",
    "\n",
    "# Store the note book values for other notebooks to use\n",
    "%store NB_YB_PATH_BIN\n",
    "%store NB_YB_PATH_DATA\n",
    "\n",
    "%store NB_GITPOD_WORKSPACE_URL\n",
    "\n",
    "%store NB_DB_NAME\n",
    "%store NB_DB_PORT\n",
    "\n",
    "%store NB_HOST_IPv4_01\n",
    "%store NB_HOST_IPv4_02\n",
    "%store NB_HOST_IPv4_03\n",
    "\n",
    "%store NB_MYSQL_DB_NAME\n",
    "%store NB_MYSQL_PORT\n",
    "\n",
    "%store NB_MASTER_WEB_PORT\n",
    "%store NB_TSERVER_WEBSERVER_PORT\n",
    "%store NB_YUGABYTED_WEB_UI_PORT\n",
    "%store NB_YB_MASTER_HOST_GITPOD_URL\n",
    "%store NB_YB_TSERVER_HOST_GITPOD_URL\n",
    "%store NB_YUGABYTED_UI_HOST_GITPOD_URL\n",
    "\n",
    "%store NB_NOTEBOOK_DIR\n",
    "%store NB_NOTEBOOK_DATA_FOLDER\n",
    "%store NB_NOTEBOOK_UTILS_FOLDER\n",
    "%store NB_UTIL_FUNCTIONS_FILE\n",
    "%store NB_UTIL_YBTSERVER_METRICS_FILE\n",
    "\n",
    "%store NB_NOTEBOOK_VOYAGER_TMP\n",
    "\n",
    "%store BETA_FAST_DATA_EXPORT\n",
    "%store TARGET_DB_PASSWORD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verify the Voyager Installation\n",
    "\n",
    "With **YugabyteDB Voyager**, you can simplify the migration from legacy databases to highly available PostgreSQL using YugabyteDB. \n",
    "\n",
    "Voyager safely migrates data from PostgreSQL, MySQL, and Oracle databases to YugabyteDB Managed, YugabyteDB Anywhere, and the core open source database, YugabyteDB.\n",
    "\n",
    "> Learn more!\n",
    ">\n",
    "> To learn more, visit [YugabyteDB Voyager](https://docs.yugabyte.com/preview/yugabyte-voyager/).\n",
    "\n",
    "\n",
    "Run the cell below to confirm that YugabyteDB Voyager is already installed in this environment. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "yb-voyager version"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "# Connect to YugabyteDB with `ysqlsh`, YSQL shell, and create the database\n",
    "The following commands are YSQL shell commands. Using `ysqlsh`, you can execute YSQL statements.\n",
    "\n",
    "Run the following cell to connect to the local host using `ysqlsh`, create the `db_ybu` database, and then list the databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%%bash -s \"$NB_YB_PATH_BIN\" \"$NB_DB_NAME\"  # create database\n",
    "YB_PATH_BIN=${1}\n",
    "DB_NAME=${2}\n",
    "\n",
    "cd $YB_PATH_BIN\n",
    "\n",
    "# terminate connections, drop, and create\n",
    "./ysqlsh -d yugabyte -c \"select pg_terminate_backend(pid) from pg_stat_activity where datname = '\"${DB_NAME}\"';\"  \n",
    "./ysqlsh -d yugabyte -c \"drop database if exists \"${DB_NAME}\";\"  \n",
    "./ysqlsh -d yugabyte -c \"create database \"${DB_NAME}\";\" \n",
    "\n",
    "# list dbs\n",
    "./ysqlsh -d yugabyte -c \"\\l\"\n",
    "\n",
    "# list nodes\n",
    "./ysqlsh -d yugabyte -c \"select * from yb_servers()\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Util Functions\n",
    "Run the following cell to create the utility functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%%bash -s \"$NB_YB_PATH_BIN\" \"$NB_DB_NAME\" \"$NB_NOTEBOOK_UTILS_FOLDER\" \"$NB_UTIL_FUNCTIONS_FILE\"  # util functions\n",
    "YB_PATH_BIN=${1}\n",
    "DB_NAME=${2}\n",
    "UTILS_FOLDER=${3}\n",
    "UTIL_FUNCTIONS_FILE=${4}\n",
    "\n",
    "\n",
    "#ls $UTIL_FOLDER\n",
    "UTIL_FUNCTIONS_FILE_PATH=${UTILS_FOLDER}/${UTIL_FUNCTIONS_FILE}\n",
    "\n",
    "cd $YB_PATH_BIN\n",
    "\n",
    "# Functions file\n",
    "./ysqlsh -q -d ${DB_NAME} -f ${UTIL_FUNCTIONS_FILE_PATH} \n",
    "sleep 1;\n",
    "\n",
    "# Describe functions\n",
    "./ysqlsh -d ${DB_NAME} -c \"\\df public.fn_*\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Util Metrics\n",
    "Run the following cell to create the utility functions for the metrics report."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%%bash -s \"$NB_YB_PATH_BIN\" \"$NB_DB_NAME\" \"$NB_NOTEBOOK_UTILS_FOLDER\" \"$NB_UTIL_YBTSERVER_METRICS_FILE\"  # utl metrics\n",
    "YB_PATH_BIN=${1}\n",
    "DB_NAME=${2}\n",
    "UTILS_FOLDER=${3}\n",
    "UTIL_YBTSERVER_METRICS_FILE=${4}\n",
    "\n",
    "#ls $UTIL_FOLDER\n",
    "\n",
    "UTIL_YBTSERVER_METRICS_FILE=${UTILS_FOLDER}/${UTIL_YBTSERVER_METRICS_FILE}\n",
    "\n",
    "cd $YB_PATH_BIN\n",
    "\n",
    "# Metrics file\n",
    "./ysqlsh -d ${DB_NAME} -f ${UTIL_YBTSERVER_METRICS_FILE} >&/dev/null\n",
    "\n",
    "# Describe relations\n",
    "./ysqlsh -d ${DB_NAME} -c \"\\df public.fn_yb*\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to YugabyteDB using the PostgreSQL Driver for Python\n",
    "The following cells requires:\n",
    "- Python 3.8+ and psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "# Connect to db_ybu\n",
    "# Inspiration from https://medium.com/analytics-vidhya/postgresql-integration-with-jupyter-notebook-deb97579a38d\n",
    "import psycopg2\n",
    "import sqlalchemy as alc\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# env_var.env\n",
    "db_host=NB_HOST_IPv4_01\n",
    "db_name=NB_DB_NAME\n",
    "\n",
    "connection_str='postgresql://yugabyte@'+db_host+':5433/'+db_name\n",
    "print(connection_str)\n",
    "engine = create_engine(connection_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load SQL magic extension\n",
    "> 👉 IMPORTANT! 👈\n",
    ">\n",
    "> To use SQL magic, you must run the following cell that loads the notebook extension."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%reload_ext sql \n",
    "# creates connection for sql magic\n",
    "%sql {connection_str}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can optionally review the configuration for Sql Magic. It's okay to skip the following cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    " %config SqlMagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "select version();"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "---\n",
    "\n",
    "## 😊 Next up!\n",
    "Continue your learning by opening the next notebook, `02_Migration_Workflow_Prepare.ipynb`. \n",
    "\n",
    "Or, if using GitPod, run the following cell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "gp open '02_Migration_Workflow_Prepare.ipynb'"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.9 64-bit",
   "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.11.2"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}