{"cells":[{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["RUN000 - Setup Master Pool runner infrastructure\n","================================================\n","\n","Description\n","-----------\n","\n","Create the Big Data Cluster SQL Master Pool objects to enable saving\n","results of notebook executions.\n","\n","NOTE: This notebook uses the Python kernel, not the SQL kernel, so that\n","it can be run in a ‘single click’ on all machines. (The SQL kernel\n","requires a PowerShell module to be installed, that can’t be guaranteed\n","to be present on all machines).\n","\n","### Parameters\n","\n","Passwords are generated for the SQL logins to enable metrics to be read\n","by the Grafana dashboard, and to be saved (insert\\_metrics) by the\n","notebook:\n","\n","-   [RUN002 - Save result in Big Data\n","    Cluster](../notebook-runner/run002-save-result-in-bdc.ipynb)\n","\n","These Passwords are only used if the SQL logins do not already exist."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["parameters"]},"outputs":[],"source":["import os\n","import secrets\n","import string\n","\n","azdata_login_secret_name = \"azdata-login-notebook-run-secret\"\n","\n","# Generate passwords (to enable metrics to be saved, and read using Grafana)\n","#\n","def generate_alphanumeric_pwd():\n","    alphabet = string.ascii_letters + string.digits\n","    while True:\n","        password = ''.join(secrets.choice(alphabet) for i in range(20)) # for a 20-character password\n","        if (any(c.islower() for c in password)\n","                and any(c.isupper() for c in password)\n","                and any(c.isdigit() for c in password)):\n","            return password\n","\n","os.environ[\"runner_insert_metric_password\"] = generate_alphanumeric_pwd()\n","os.environ[\"runner_db_reader_password\"] = generate_alphanumeric_pwd()\n","\n","print(\"Passwords generated\")"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Common functions\n","\n","Define helper functions used in this notebook."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["hide_input"]},"outputs":[],"source":["# Define `run` function for transient fault handling, suggestions on error, and scrolling updates on Windows\n","import sys\n","import os\n","import re\n","import platform\n","import shlex\n","import shutil\n","import datetime\n","\n","from subprocess import Popen, PIPE\n","from IPython.display import Markdown\n","\n","retry_hints = {} # Output in stderr known to be transient, therefore automatically retry\n","error_hints = {} # Output in stderr where a known SOP/TSG exists which will be HINTed for further help\n","install_hint = {} # The SOP to help install the executable if it cannot be found\n","\n","def run(cmd, return_output=False, no_output=False, retry_count=0, base64_decode=False, return_as_json=False, regex_mask=None):\n","    \"\"\"Run shell command, stream stdout, print stderr and optionally return output\n","\n","    NOTES:\n","\n","    1.  Commands that need this kind of ' quoting on Windows e.g.:\n","\n","            kubectl get nodes -o jsonpath={.items[?(@.metadata.annotations.pv-candidate=='data-pool')].metadata.name}\n","\n","        Need to actually pass in as '\"':\n","\n","            kubectl get nodes -o jsonpath={.items[?(@.metadata.annotations.pv-candidate=='\"'data-pool'\"')].metadata.name}\n","\n","        The ' quote approach, although correct when pasting into Windows cmd, will hang at the line:\n","        \n","            `iter(p.stdout.readline, b'')`\n","\n","        The shlex.split call does the right thing for each platform, just use the '\"' pattern for a '\n","    \"\"\"\n","    MAX_RETRIES = 5\n","    output = \"\"\n","    retry = False\n","\n","    # When running `azdata sql query` on Windows, replace any \\n in \"\"\" strings, with \" \", otherwise we see:\n","    #\n","    #    ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')\n","    #\n","    if platform.system() == \"Windows\" and cmd.startswith(\"azdata sql query\"):\n","        cmd = cmd.replace(\"\\n\", \" \")\n","\n","    # shlex.split is required on bash and for Windows paths with spaces\n","    #\n","    cmd_actual = shlex.split(cmd)\n","\n","    # Store this (i.e. kubectl, python etc.) to support binary context aware error_hints and retries\n","    #\n","    user_provided_exe_name = cmd_actual[0].lower()\n","\n","    # When running python, use the python in the ADS sandbox ({sys.executable})\n","    #\n","    if cmd.startswith(\"python \"):\n","        cmd_actual[0] = cmd_actual[0].replace(\"python\", sys.executable)\n","\n","        # On Mac, when ADS is not launched from terminal, LC_ALL may not be set, which causes pip installs to fail\n","        # with:\n","        #\n","        #    UnicodeDecodeError: 'ascii' codec can't decode byte 0xc5 in position 4969: ordinal not in range(128)\n","        #\n","        # Setting it to a default value of \"en_US.UTF-8\" enables pip install to complete\n","        #\n","        if platform.system() == \"Darwin\" and \"LC_ALL\" not in os.environ:\n","            os.environ[\"LC_ALL\"] = \"en_US.UTF-8\"\n","\n","    # When running `kubectl`, if AZDATA_OPENSHIFT is set, use `oc`\n","    #\n","    if cmd.startswith(\"kubectl \") and \"AZDATA_OPENSHIFT\" in os.environ:\n","        cmd_actual[0] = cmd_actual[0].replace(\"kubectl\", \"oc\")\n","\n","    # To aid supportability, determine which binary file will actually be executed on the machine\n","    #\n","    which_binary = None\n","\n","    # Special case for CURL on Windows.  The version of CURL in Windows System32 does not work to\n","    # get JWT tokens, it returns \"(56) Failure when receiving data from the peer\".  If another instance\n","    # of CURL exists on the machine use that one.  (Unfortunately the curl.exe in System32 is almost\n","    # always the first curl.exe in the path, and it can't be uninstalled from System32, so here we\n","    # look for the 2nd installation of CURL in the path)\n","    if platform.system() == \"Windows\" and cmd.startswith(\"curl \"):\n","        path = os.getenv('PATH')\n","        for p in path.split(os.path.pathsep):\n","            p = os.path.join(p, \"curl.exe\")\n","            if os.path.exists(p) and os.access(p, os.X_OK):\n","                if p.lower().find(\"system32\") == -1:\n","                    cmd_actual[0] = p\n","                    which_binary = p\n","                    break\n","\n","    # Find the path based location (shutil.which) of the executable that will be run (and display it to aid supportability), this\n","    # seems to be required for .msi installs of azdata.cmd/az.cmd.  (otherwise Popen returns FileNotFound) \n","    #\n","    # NOTE: Bash needs cmd to be the list of the space separated values hence shlex.split.\n","    #\n","    if which_binary == None:\n","        which_binary = shutil.which(cmd_actual[0])\n","\n","    # Display an install HINT, so the user can click on a SOP to install the missing binary\n","    #\n","    if which_binary == None:\n","        print(f\"The path used to search for '{cmd_actual[0]}' was:\")\n","        print(sys.path)\n","\n","        if user_provided_exe_name in install_hint and install_hint[user_provided_exe_name] is not None:\n","            display(Markdown(f'HINT: Use [{install_hint[user_provided_exe_name][0]}]({install_hint[user_provided_exe_name][1]}) to resolve this issue.'))\n","\n","        raise FileNotFoundError(f\"Executable '{cmd_actual[0]}' not found in path (where/which)\")\n","    else:   \n","        cmd_actual[0] = which_binary\n","\n","    start_time = datetime.datetime.now().replace(microsecond=0)\n","\n","    cmd_display = cmd\n","    if regex_mask is not None:\n","        regex = re.compile(regex_mask)\n","        cmd_display = re.sub(regex, '******', cmd)\n","        \n","    print(f\"START: {cmd_display} @ {start_time} ({datetime.datetime.utcnow().replace(microsecond=0)} UTC)\")\n","    print(f\"       using: {which_binary} ({platform.system()} {platform.release()} on {platform.machine()})\")\n","    print(f\"       cwd: {os.getcwd()}\")\n","\n","    # Command-line tools such as CURL and AZDATA HDFS commands output\n","    # scrolling progress bars, which causes Jupyter to hang forever, to\n","    # workaround this, use no_output=True\n","    #\n","\n","    # Work around a infinite hang when a notebook generates a non-zero return code, break out, and do not wait\n","    #\n","    wait = True \n","\n","    try:\n","        if no_output:\n","            p = Popen(cmd_actual)\n","        else:\n","            p = Popen(cmd_actual, stdout=PIPE, stderr=PIPE, bufsize=1)\n","            with p.stdout:\n","                for line in iter(p.stdout.readline, b''):\n","                    line = line.decode()\n","                    if return_output:\n","                        output = output + line\n","                    else:\n","                        if cmd.startswith(\"azdata notebook run\"): # Hyperlink the .ipynb file\n","                            regex = re.compile('  \"(.*)\"\\: \"(.*)\"') \n","                            match = regex.match(line)\n","                            if match:\n","                                if match.group(1).find(\"HTML\") != -1:\n","                                    display(Markdown(f' - \"{match.group(1)}\": \"{match.group(2)}\"'))\n","                                else:\n","                                    display(Markdown(f' - \"{match.group(1)}\": \"[{match.group(2)}]({match.group(2)})\"'))\n","\n","                                    wait = False\n","                                    break # otherwise infinite hang, have not worked out why yet.\n","                        else:\n","                            print(line, end='')\n","\n","        if wait:\n","            p.wait()\n","    except FileNotFoundError as e:\n","        if install_hint is not None:\n","            display(Markdown(f'HINT: Use {install_hint} to resolve this issue.'))\n","\n","        raise FileNotFoundError(f\"Executable '{cmd_actual[0]}' not found in path (where/which)\") from e\n","\n","    exit_code_workaround = 0 # WORKAROUND: azdata hangs on exception from notebook on p.wait()\n","\n","    if not no_output:\n","        for line in iter(p.stderr.readline, b''):\n","            try:\n","                line_decoded = line.decode()\n","            except UnicodeDecodeError:\n","                # NOTE: Sometimes we get characters back that cannot be decoded(), e.g.\n","                #\n","                #   \\xa0\n","                #\n","                # For example see this in the response from `az group create`:\n","                #\n","                # ERROR: Get Token request returned http error: 400 and server \n","                # response: {\"error\":\"invalid_grant\",# \"error_description\":\"AADSTS700082: \n","                # The refresh token has expired due to inactivity.\\xa0The token was \n","                # issued on 2018-10-25T23:35:11.9832872Z\n","                #\n","                # which generates the exception:\n","                #\n","                # UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa0 in position 179: invalid start byte\n","                #\n","                print(\"WARNING: Unable to decode stderr line, printing raw bytes:\")\n","                print(line)\n","                line_decoded = \"\"\n","                pass\n","            else:\n","\n","                # azdata emits a single empty line to stderr when doing an hdfs cp, don't\n","                # print this empty \"ERR:\" as it confuses.\n","                #\n","                if line_decoded == \"\":\n","                    continue\n","                \n","                print(f\"STDERR: {line_decoded}\", end='')\n","\n","                if line_decoded.startswith(\"An exception has occurred\") or line_decoded.startswith(\"ERROR: An error occurred while executing the following cell\"):\n","                    exit_code_workaround = 1\n","\n","                # inject HINTs to next TSG/SOP based on output in stderr\n","                #\n","                if user_provided_exe_name in error_hints:\n","                    for error_hint in error_hints[user_provided_exe_name]:\n","                        if line_decoded.find(error_hint[0]) != -1:\n","                            display(Markdown(f'HINT: Use [{error_hint[1]}]({error_hint[2]}) to resolve this issue.'))\n","\n","                # Verify if a transient error, if so automatically retry (recursive)\n","                #\n","                if user_provided_exe_name in retry_hints:\n","                    for retry_hint in retry_hints[user_provided_exe_name]:\n","                        if line_decoded.find(retry_hint) != -1:\n","                            if retry_count \u003c MAX_RETRIES:\n","                                print(f\"RETRY: {retry_count} (due to: {retry_hint})\")\n","                                retry_count = retry_count + 1\n","                                output = run(cmd, return_output=return_output, retry_count=retry_count)\n","\n","                                if return_output:\n","                                    if base64_decode:\n","                                        import base64\n","                                        return base64.b64decode(output).decode('utf-8')\n","                                    else:\n","                                        return output\n","\n","    elapsed = datetime.datetime.now().replace(microsecond=0) - start_time\n","\n","    # WORKAROUND: We avoid infinite hang above in the `azdata notebook run` failure case, by inferring success (from stdout output), so\n","    # don't wait here, if success known above\n","    #\n","    if wait: \n","        if p.returncode != 0:\n","            raise SystemExit(f'Shell command:\\n\\n\\t{cmd_display} ({elapsed}s elapsed)\\n\\nreturned non-zero exit code: {str(p.returncode)}.\\n')\n","    else:\n","        if exit_code_workaround !=0 :\n","            raise SystemExit(f'Shell command:\\n\\n\\t{cmd_display} ({elapsed}s elapsed)\\n\\nreturned non-zero exit code: {str(exit_code_workaround)}.\\n')\n","\n","    print(f'\\nSUCCESS: {elapsed}s elapsed.\\n')\n","\n","    if return_output:\n","        if base64_decode:\n","            import base64\n","            return base64.b64decode(output).decode('utf-8')\n","        else:\n","            return output\n","\n","\n","\n","# Hints for tool retry (on transient fault), known errors and install guide\n","#\n","retry_hints = {'azdata': ['Endpoint sql-server-master does not exist', 'Endpoint livy does not exist', 'Failed to get state for cluster', 'Endpoint webhdfs does not exist', 'Adaptive Server is unavailable or does not exist', 'Error: Address already in use', 'Login timeout expired (0) (SQLDriverConnect)', 'SSPI Provider: No Kerberos credentials available',  ], 'kubectl': ['A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond',  ], 'python': [ ], }\n","error_hints = {'azdata': [['Please run \\'azdata login\\' to first authenticate', 'SOP028 - azdata login', '../common/sop028-azdata-login.ipynb'], ['The token is expired', 'SOP028 - azdata login', '../common/sop028-azdata-login.ipynb'], ['Reason: Unauthorized', 'SOP028 - azdata login', '../common/sop028-azdata-login.ipynb'], ['Max retries exceeded with url: /api/v1/bdc/endpoints', 'SOP028 - azdata login', '../common/sop028-azdata-login.ipynb'], ['Look at the controller logs for more details', 'TSG027 - Observe cluster deployment', '../diagnose/tsg027-observe-bdc-create.ipynb'], ['provided port is already allocated', 'TSG062 - Get tail of all previous container logs for pods in BDC namespace', '../log-files/tsg062-tail-bdc-previous-container-logs.ipynb'], ['Create cluster failed since the existing namespace', 'SOP061 - Delete a big data cluster', '../install/sop061-delete-bdc.ipynb'], ['Failed to complete kube config setup', 'TSG067 - Failed to complete kube config setup', '../repair/tsg067-failed-to-complete-kube-config-setup.ipynb'], ['Data source name not found and no default driver specified', 'SOP069 - Install ODBC for SQL Server', '../install/sop069-install-odbc-driver-for-sql-server.ipynb'], ['Can\\'t open lib \\'ODBC Driver 17 for SQL Server', 'SOP069 - Install ODBC for SQL Server', '../install/sop069-install-odbc-driver-for-sql-server.ipynb'], ['Control plane upgrade failed. Failed to upgrade controller.', 'TSG108 - View the controller upgrade config map', '../diagnose/tsg108-controller-failed-to-upgrade.ipynb'], ['NameError: name \\'azdata_login_secret_name\\' is not defined', 'SOP013 - Create secret for azdata login (inside cluster)', '../common/sop013-create-secret-for-azdata-login.ipynb'], ['ERROR: No credentials were supplied, or the credentials were unavailable or inaccessible.', 'TSG124 - \\'No credentials were supplied\\' error from azdata login', '../repair/tsg124-no-credentials-were-supplied.ipynb'], ['Please accept the license terms to use this product through', 'TSG126 - azdata fails with \\'accept the license terms to use this product\\'', '../repair/tsg126-accept-license-terms.ipynb'],  ], 'kubectl': [['no such host', 'TSG010 - Get configuration contexts', '../monitor-k8s/tsg010-get-kubernetes-contexts.ipynb'], ['No connection could be made because the target machine actively refused it', 'TSG056 - Kubectl fails with No connection could be made because the target machine actively refused it', '../repair/tsg056-kubectl-no-connection-could-be-made.ipynb'],  ], 'python': [['Library not loaded: /usr/local/opt/unixodbc', 'SOP012 - Install unixodbc for Mac', '../install/sop012-brew-install-odbc-for-sql-server.ipynb'], ['WARNING: You are using pip version', 'SOP040 - Upgrade pip in ADS Python sandbox', '../install/sop040-upgrade-pip.ipynb'],  ], }\n","install_hint = {'azdata': [ 'SOP063 - Install azdata CLI (using package manager)', '../install/sop063-packman-install-azdata.ipynb' ],  'kubectl': [ 'SOP036 - Install kubectl command line interface', '../install/sop036-install-kubectl.ipynb' ],  }\n","\n","\n","print('Common functions defined successfully.')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Get the Kubernetes namespace for the big data cluster\n","\n","Get the namespace of the Big Data Cluster use the kubectl command line\n","interface .\n","\n","**NOTE:**\n","\n","If there is more than one Big Data Cluster in the target Kubernetes\n","cluster, then either:\n","\n","-   set \\[0\\] to the correct value for the big data cluster.\n","-   set the environment variable AZDATA\\_NAMESPACE, before starting\n","    Azure Data Studio."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["hide_input"]},"outputs":[],"source":["# Place Kubernetes namespace name for BDC into 'namespace' variable\n","\n","if \"AZDATA_NAMESPACE\" in os.environ:\n","    namespace = os.environ[\"AZDATA_NAMESPACE\"]\n","else:\n","    try:\n","        namespace = run(f'kubectl get namespace --selector=MSSQL_CLUSTER -o jsonpath={{.items[0].metadata.name}}', return_output=True)\n","    except:\n","        from IPython.display import Markdown\n","        print(f\"ERROR: Unable to find a Kubernetes namespace with label 'MSSQL_CLUSTER'.  SQL Server Big Data Cluster Kubernetes namespaces contain the label 'MSSQL_CLUSTER'.\")\n","        display(Markdown(f'HINT: Use [TSG081 - Get namespaces (Kubernetes)](../monitor-k8s/tsg081-get-kubernetes-namespaces.ipynb) to resolve this issue.'))\n","        display(Markdown(f'HINT: Use [TSG010 - Get configuration contexts](../monitor-k8s/tsg010-get-kubernetes-contexts.ipynb) to resolve this issue.'))\n","        display(Markdown(f'HINT: Use [SOP011 - Set kubernetes configuration context](../common/sop011-set-kubernetes-context.ipynb) to resolve this issue.'))\n","        raise\n","\n","print(f'The SQL Server Big Data Cluster Kubernetes namespace is: {namespace}')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Get the controller username and password\n","\n","Get the controller username and password from the Kubernetes Secret\n","Store and place in the required AZDATA\\_USERNAME and AZDATA\\_PASSWORD\n","environment variables."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["hide_input"]},"outputs":[],"source":["# Place controller secret in AZDATA_USERNAME/AZDATA_PASSWORD environment variables\n","\n","import os, base64\n","\n","os.environ[\"AZDATA_USERNAME\"] = run(f'kubectl get secret/controller-login-secret -n {namespace} -o jsonpath={{.data.username}}', return_output=True, base64_decode=True)\n","os.environ[\"AZDATA_PASSWORD\"] = run(f'kubectl get secret/controller-login-secret -n {namespace} -o jsonpath={{.data.password}}', return_output=True, base64_decode=True)\n","\n","print(f\"Controller username '{os.environ['AZDATA_USERNAME']}' and password stored in environment variables\")"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Check the azdata login secret has been previously created"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["try:\n","    run(f'kubectl get secret/{azdata_login_secret_name} -n {namespace} -o jsonpath={{.data.azdata_login_username}}', no_output=True)\n","except:\n","    from IPython.display import Markdown\n","    print('PREREQUISITE: Please create the `azdata login` secret, to enable notebooks to be run cluster side in an app-deploy container')\n","    display(Markdown(f'HINT: Use [SOP013 - Create secret for azdata login (inside cluster)](../common/sop013-create-secret-for-azdata-login.ipynb) to resolve this issue.'))\n","    raise"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create Kubernetes secrets and SQL logins\n","\n","Create a T/SQL Login to insert metrics (done in the run002 notebook),\n","and read metrics from Grafana."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["import json\n","\n","def create_tsql_login_if_not_exists(name, password_env_var):\n","    query = f\"\"\"SELECT COUNT(*) as login_exists FROM sys.syslogins where name = '##{name}##'\"\"\"\n","    run(f'azdata sql query -q \"SELECT @@version\"')\n","    output = run(f'azdata sql query -q \"{query}\"', return_output=True)\n","    run(f'azdata sql query -q \"SELECT @@version\"')\n","\n","    print(f\"Results from: {query}\")\n","    print(output)\n","\n","    login_exists = bool(json.loads(output)[0][\"login_exists\"])\n","\n","    print(f\"Login exists: {login_exists}\")\n","\n","    if not login_exists:\n","\n","        # Delete K8s secret if previously created\n","        # \n","        secret = run(f\"kubectl get secrets --field-selector metadata.name={name.replace('_', '-')}-secret -n {namespace} --no-headers -o jsonpath={{.items}}\", return_output=True)\n","\n","        if secret != \"[]\":\n","            print(\"Deleting existing secret, before creating\")\n","            run(f\"kubectl delete secret {name.replace('_', '-')}-secret -n {namespace}\")\n","        else:\n","            print(f\"Secret does not already exist.  Creating it...\")\n","\n","        run(f\"\"\"kubectl create secret generic {name.replace('_', '-')}-secret -n {namespace} --from-literal=username=##{name}## --from-literal=password={os.environ[password_env_var]}\"\"\")\n","\n","        sql = f\"\"\"CREATE LOGIN ##{name}## WITH PASSWORD = '{os.environ[password_env_var]}'\"\"\"\n","\n","        run(f'azdata sql query --database master -q \"{sql}\"')\n","\n","        # Clear out the password\n","        #\n","        os.environ[password_env_var] = \"\"\n","\n","create_tsql_login_if_not_exists(\"runner_insert_metric_login\", \"runner_insert_metric_password\")\n","create_tsql_login_if_not_exists(\"runner_db_reader_login\", \"runner_db_reader_password\")"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create the `runner` database\n","\n","The metrics will be held in a database named `runner`"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","IF DB_ID('runner') IS NULL\n","BEGIN\n","  CREATE DATABASE [runner]\n","END\n","\"\"\"\n","\n","run(f'azdata sql query --database master -q \"{sql}\"')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create the SQL tables\n","\n","Create the required T/SQL tables to hold the metrics. The schema is\n","normalized to optimize performance (dense records), and views are\n","provided to consume the data in dashboards.\n","\n","-   `tbl_notebook` – generates an internal `id` for each notebook file\n","    name received\n","-   `tbl_app` – generates an internal `id` for each ‘app-deploy’ app\n","    name/version received. It also notes if the app-deploy is used as a\n","    canary (to enable dashboards to optimize displays)\n","-   `tbl_session` – generate an `id` for each session. A session is a\n","    single invocation of all the notebooks in an app-deploy.\n","-   `tbl_metrics` – the fully normalized table containing all the\n","    metrics of notebook execution"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","IF NOT EXISTS (SELECT *\n","              FROM INFORMATION_SCHEMA.TABLES\n","              WHERE TABLE_SCHEMA = 'dbo'\n","              AND TABLE_NAME = 'tbl_notebook')\n","BEGIN\n","    CREATE TABLE [dbo].[tbl_notebook](\n","        [id] [int] NOT NULL IDENTITY,\n","        [name] [sysname] NOT NULL,\n","        [created] [datetime2](7) NOT NULL DEFAULT GETDATE()\n","        CONSTRAINT [PK__notebook__id] PRIMARY KEY CLUSTERED \n","        (\n","            [id] ASC\n","        )\n","    )\n","END\n","\n","IF NOT EXISTS (SELECT *\n","              FROM INFORMATION_SCHEMA.TABLES\n","              WHERE TABLE_SCHEMA = 'dbo'\n","              AND TABLE_NAME = 'tbl_app')\n","BEGIN\n","    CREATE TABLE [dbo].[tbl_app](\n","        [id] [int] NOT NULL IDENTITY,\n","        [name] [sysname] NOT NULL,\n","        [version] [sysname] NOT NULL,\n","        [is_canary] [bit] NOT NULL,\n","        [created] [datetime2](7) NOT NULL DEFAULT GETDATE()\n","        CONSTRAINT [PK__app__id] PRIMARY KEY CLUSTERED \n","        (\n","            [id] ASC\n","        )\n","    )\n","END\n","\n","IF NOT EXISTS (SELECT *\n","              FROM INFORMATION_SCHEMA.TABLES\n","              WHERE TABLE_SCHEMA = 'dbo'\n","              AND TABLE_NAME = 'tbl_session')\n","BEGIN\n","    CREATE TABLE [dbo].[tbl_session](\n","        [id] [int] NOT NULL IDENTITY,\n","        [start] [datetime2](7) NOT NULL,\n","        [app_id] [int] NOT NULL CONSTRAINT FK__session__app_id FOREIGN KEY REFERENCES [dbo].[tbl_app](id) ON DELETE CASCADE\n","        CONSTRAINT [PK__session__id] PRIMARY KEY CLUSTERED \n","        (\n","            [id] ASC\n","        )\n","    )\n","END\n","\n","IF NOT EXISTS (SELECT *\n","              FROM INFORMATION_SCHEMA.TABLES\n","              WHERE TABLE_SCHEMA = 'dbo'\n","              AND TABLE_NAME = 'tbl_metrics')\n","BEGIN\n","    CREATE TABLE [dbo].[tbl_metrics](\n","        [session_id] [int] NOT NULL CONSTRAINT FK__metrics__session_id FOREIGN KEY REFERENCES [dbo].[tbl_session](id) ON DELETE CASCADE,\n","        [notebook_id] [int] NOT NULL CONSTRAINT FK__metrics__notebook_id FOREIGN KEY REFERENCES [dbo].[tbl_notebook](id) ON DELETE CASCADE,\n","        [error_level] [tinyint] NOT NULL,\n","        [html_notebook_included] [bit] NOT NULL,\n","        [start] [datetime2](7) NOT NULL,\n","        [end] [datetime2](7) NOT NULL\n","        CONSTRAINT [PK__metrics__session_id__start] PRIMARY KEY CLUSTERED \n","        (\n","            [session_id] ASC,\n","            [start] ASC\n","        )\n","    )\n","END\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create `insert_metric` procedure\n","\n","The `insert metric` procedure is called from the run002 notebook."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","CREATE OR ALTER PROCEDURE [dbo].[insert_metric]\n","    @session_start DATETIME2, \n","    @app_name SYSNAME,\n","    @app_version SYSNAME,\n","    @notebook_name NVARCHAR(max),\n","    @error_level TINYINT,\n","    @html_notebook_included BIT,\n","    @start DATETIME2,\n","    @end DATETIME2\n","AS\n","BEGIN\n","    SET NOCOUNT ON\n","    SET XACT_ABORT ON\n","    BEGIN TRAN\n","\n","        /* Get the app_id from the app_name/app_version */\n","        DECLARE @app_id INT = (SELECT id FROM [dbo].[tbl_app] WHERE [name] = @app_name AND [version] = @app_version)\n","        IF @app_id IS NULL\n","        BEGIN\n","            INSERT INTO [dbo].[tbl_app] VALUES (@app_name, @app_version, 0, DEFAULT)\n","            SET @app_id = SCOPE_IDENTITY()\n","        END\n","\n","        /* Get the session_id from the session_start/app_name/app_version */\n","        DECLARE @session_id INT = (SELECT id FROM [dbo].[tbl_session] WHERE [start] = @session_start and [app_id] = @app_id)\n","        IF @session_id IS NULL\n","        BEGIN\n","            INSERT INTO [dbo].[tbl_session] VALUES (@session_start, @app_id)\n","            SET @session_id = SCOPE_IDENTITY()\n","        END\n","\n","        /* Get the notebook_id for the notebook_name */\n","        DECLARE @notebook_id INT = (SELECT id FROM [dbo].[tbl_notebook] WHERE [name] = @notebook_name)\n","        IF @notebook_id IS NULL\n","        BEGIN\n","            INSERT INTO [dbo].[tbl_notebook] VALUES (@notebook_name, DEFAULT)\n","            SET @notebook_id = SCOPE_IDENTITY()\n","        END\n","\n","\n","        INSERT [dbo].[tbl_metrics] VALUES(@session_id, @notebook_id, @error_level, @html_notebook_included, @start, @end)\n","\n","    COMMIT\n","END\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create views\n","\n","These views are used to power the Grafana dashboards"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","CREATE OR ALTER VIEW [dbo].[sessions]\n","AS\n","    SELECT s.id, s.start as session_start, a.name as app_name, a.version as app_version, a.is_canary\n","    FROM [dbo].[tbl_session] s\n","    JOIN [dbo].[tbl_app] a ON s.app_id = a.id\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","CREATE OR ALTER VIEW [dbo].[metrics]\n","AS\n","    SELECT \n","        s.[session_start],\n","        s.[app_name], \n","        s.[app_version],\n","        n.[name],\n","        m.[error_level],\n","        m.[start],\n","        m.[end],\n","        'app-deploy/' + /* Build up the URL to this notebook in HDFS */\n","            s.[app_name] + '-' + s.[app_version] +\n","            '/logs/' +\n","            FORMAT(DATEPART(yy, s.[session_start]),'D4') + '/' +\n","            FORMAT(DATEPART(mm, s.[session_start]),'D2') + '/' +\n","            FORMAT(DATEPART(dd, s.[session_start]),'D2') + '/' +\n","            FORMAT(DATEPART(hh, s.[session_start]),'D2') + '-' +\n","            FORMAT(DATEPART(mi, s.[session_start]),'D2') + '-' +\n","            CASE m.[error_level] WHEN 0 THEN 'SUCCESS-' ELSE 'FAILURE-' END + \n","            n.[name] +\n","            CASE m.html_notebook_included WHEN 1 THEN '.html' ELSE '.ipynb' END as [notebook]\n","    FROM dbo.tbl_metrics m\n","    JOIN dbo.sessions s on m.session_id = s.id\n","    JOIN dbo.tbl_notebook n on m.notebook_id = n.id\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create canary\\_stats function\n","\n","Grafana dashboard don’t work with CTEs (Common Table Expressions),\n","therefore creating Functions here."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","CREATE OR ALTER FUNCTION canary_stats (@hours INT)\n","RETURNS TABLE\n","AS\n","RETURN\n","    WITH last_hours([app_name], [passed], [failed])\n","    AS\n","    (\n","        SELECT  [app_name],\n","            (SELECT count(*) FROM [metrics] r2 WHERE r1.app_name = r2.app_name AND r2.error_level = 0 AND app_name LIKE 'canary-%' AND start \u003e DATEADD(hour, @hours, GETDATE())) AS [passed],\n","            (SELECT count(*) FROM [metrics] r2 WHERE r1.app_name = r2.app_name AND r2.error_level \u003c\u003e 0 AND app_name LIKE 'canary-%' AND start \u003e DATEADD(hour, @hours, GETDATE())) AS [failed]\n","        FROM [metrics] r1\n","        WHERE app_name LIKE 'canary-%' AND start \u003e DATEADD(hour, @hours, GETDATE())\n","        GROUP BY [app_name]\n","    )\n","    SELECT [app_name] as Canary, [Passed], [Failed], [passed] + [failed] as Total, CONVERT(DECIMAL(5, 2), ([passed] * 1.0) / ([passed] + [failed]) * 100.0) as [Percent]\n","    FROM last_hours\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create maintenance procs\n","\n","Use these procs to clear up the runner database:\n","\n","-   `delete_app_metrics` – if there is an app you no longer want to\n","    appear in dashboards."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","CREATE OR ALTER PROCEDURE [dbo].[delete_app_metrics]\n","    @app_name SYSNAME,\n","    @app_version SYSNAME\n","AS\n","BEGIN\n","    SET NOCOUNT ON\n","    SET XACT_ABORT ON\n","    BEGIN TRAN\n","        DELETE [dbo].[tbl_app] WHERE name = @app_name and version = @app_version\n","    COMMIT\n","END\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')\n","\n","sql = f\"\"\"\n","CREATE OR ALTER PROCEDURE [dbo].[delete_notebook_metrics] @name SYSNAME AS\n","BEGIN\n","    SET NOCOUNT ON\n","    SET XACT_ABORT ON \n","    \n","    BEGIN TRAN\n","        DECLARE @notebook_id INT = (SELECT id FROM tbl_notebook WHERE name = @name)\n","\n","        DELETE [dbo].[tbl_notebook] WHERE name = @name     \n","    COMMIT\n","END\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Set up permissions\n","\n","Set up the minimum permissions required for the account to read the\n","metrics (used by Grafana), and the account to insert the metrics."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["sql = f\"\"\"\n","IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = '##runner_db_reader_login##')\n","BEGIN\n","    CREATE USER ##runner_db_reader_login## FOR LOGIN ##runner_db_reader_login##\n","    ALTER ROLE db_datareader ADD MEMBER ##runner_db_reader_login##\n","END\n","\n","IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = '##runner_insert_metric_login##')\n","BEGIN\n","    CREATE USER ##runner_insert_metric_login## FOR LOGIN ##runner_insert_metric_login##\n","    GRANT CONNECT TO [##runner_insert_metric_login##]\n","    GRANT EXECUTE ON [dbo].[insert_metric] TO [##runner_insert_metric_login##]\n","END\n","\"\"\"\n","\n","run(f'azdata sql query --database runner -q \"{sql}\"')"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["print(\"Notebook execution is complete.\")"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["Related\n","-------\n","\n","- [RUN003 - Run expert rules](../notebook-runner/run003-run-expert-rules.ipynb)\n","- [RUN999 - Cleanup Master Pool runner infrastructure](../notebook-runner/run999-cleanup-infrastructure.ipynb)\n"]}],"nbformat":4,"nbformat_minor":5,"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3"},"pansop":{"related":"RUN003, RUN999","test":{"strategy":"","types":null,"disable":{"reason":"","workitems":null,"types":null}},"target":{"current":"","final":""},"internal":{"parameters":null,"symlink":false},"timeout":"0"},"language_info":{"codemirror_mode":"{ Name: \"\", Version: \"\"}","file_extension":"","mimetype":"","name":"","nbconvert_exporter":"","pygments_lexer":"","version":""},"widgets":[]}}