bdc-00-overview.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "<img src=\"https://github.com/Microsoft/sqlworkshops/blob/master/graphics/solutions-microsoft-logo-small.png?raw=true\" alt=\"Microsoft\">\r\n",
                "<br>\r\n",
                "\r\n",
                "# **SQL Server 2019 big data cluster Tutorial**\r\n",
                "## **00 - Scenario Overview and System Setup**\r\n",
                "\r\n",
                "In this set of tutorials you'll work with an end-to-end scenario that uses SQL Server 2019's big data clusters to solve real-world problems. \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "d495f8be-74c3-4658-b897-ad69e6ed88ac"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Wide World Importers**\r\n",
                "\r\n",
                "Wide World Importers (WWI) is a traditional brick and mortar business that makes specialty items for other companies to use in their products. They design, sell and ship these products worldwide.\r\n",
                "\r\n",
                "WWI corporate has now added a new partnership with a company called \"AdventureWorks\", which sells bicycles both online and in-store. The AdventureWorks company has asked WWI to produce super-hero themed baskets, seats and other bicycle equipment for a new line of bicycles. WWI corporate has asked the IT department to develop a pilot program with these goals: \r\n",
                "\r\n",
                "- Integrate the large amounts of data from the AdventureWorks company including customers, products and sales\r\n",
                "- Allow a cross-selling strategy so that current WWI customers and AdventureWorks customers see their information without having to re-enter it\r\n",
                "- Incorporate their online sales information for deeper analysis\r\n",
                "- Provide a historical data set so that the partnership can be evaluated\r\n",
                "- Ensure this is a \"framework\" approach, so that it can be re-used with other partners\r\n",
                "\r\n",
                "WWI has a typical N-Tier application that provides a series of terminals, a Business Logic layer, and a Database back-end. They use on-premises systems, and are interested in linking these to the cloud. \r\n",
                "\r\n",
                "In this series of tutorials, you will build a solution using the scale-out features of SQL Server 2019, Data Virtualization, Data Marts, and the Data Lake features. "
            ],
            "metadata": {
                "azdata_cell_guid": "3815241f-e81e-4cf1-a48e-c6e67b0ccf7c"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Running these Tutorials**\r\n",
                "You can download these Jupyter Notebooks and run them in Azure Data Studio for a hands-on experience.\r\n",
                "\r\n",
                "### Prerequisites:  \r\n",
                "- If you would like to run the tutorials, you'll need a SQL Server 2019 big data cluster and the client tools installed. If you want to set up your own cluster, [click this reference and follow the steps to deploy a SQL Server 2019 big data cluster](bdc_deployment.md)\r\n",
                "\r\n",
                "- You will need to have the following: \r\n",
                "    - Your **Knox Password**\r\n",
                "    - The **Knox IP Address**\r\n",
                "    - The `sa` **Username** and **Password** to your Master Instance\r\n",
                "    - The **IP address** to the SQL Server big data cluster Master Instance \r\n",
                "    - The **name** of your big data cluster\r\n",
                "\r\n",
                "For a complete workshop on SQL Server 2019's big data clusters, <a href=\"https://github.com/Microsoft/sqlworkshops/tree/master/sqlserver2019bigdataclusters\" target=\"_blank\">check out this resource</a>."
            ],
            "metadata": {
                "azdata_cell_guid": "1c3e4b5e-fef4-43ef-a4e3-aa33fe99e25d"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Copy Database backups to the SQL Server 2019 big data cluster Master Instance**\r\n",
                "\r\n",
                "The first step for the solution is to copy the database backups from WWI from their location on the cloud and then up to your cluster. \r\n",
                "\r\n",
                "These commands use the `curl` program to pull the files down. [You can read more about curl here](https://curl.haxx.se/). \r\n",
                "\r\n",
                "The next set of commands use the `kubectl` command to copy the files from where you downloaded them to the data directory of the SQL Server 2019 bdc Master Instance. [You can read more about kubectl here](https://kubernetes.io/docs/reference/kubectl/overview/). \r\n",
                "\r\n",
                "Note that you will need to replace the section of the script marked with `<ReplaceWithClusterName>` with the name of your SQL Server 2019 bdc. (It does not need single or double quotes, just the letters of your cluster name.)\r\n",
                "\r\n",
                "Notice also that these commands assume a `c:\\temp` location, if you want to use another drive or directory, edit accordingly.\r\n",
                "\r\n",
                "Once you have edited these commands, you can open a Command Prompt *(not PowerShell)* on your system and copy and paste each block, one at a time and run them there, observing the output.\r\n",
                "\r\n",
                "In the next tutorial you will restore these databases on the Master Instance.\r\n",
                "\r\n",
                "> If you have trouble running commands in Azure Data Studio, please feel free to execute them in your terminal."
            ],
            "metadata": {
                "azdata_cell_guid": "5220c555-f819-409e-b206-de9a2dd6d434"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# REM Create a temporary directory for the files\r\n",
                "md c:\\temp\r\n",
                "cd c:\\temp\r\n",
                "\r\n",
                "# REM Get the database backups\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/WWI.bak\" -o c:\\temp\\WWI.bak\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/AdventureWorks.bak\" -o c:\\temp\\AdventureWorks.bak\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/AdventureWorksDW.bak\" -o c:\\temp\\AdventureWorksDW.bak\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/Analysis.bak\" -o c:\\temp\\Analysis.bak\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/sales.bak\" -o c:\\temp\\sales.bak\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/NYC.bak\" -o c:\\temp\\NYC.bak\r\n",
                "curl \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/WWIDW.bak\" -o c:\\temp\\WWIDW.bak\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "3e1f2304-cc0a-4e0e-96e2-333401b52036",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# REM Copy the backups to the data location on the SQL Server Master Instance\r\n",
                "cd c:\\temp\r\n",
                "kubectl cp WWI.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                "kubectl cp WWIDW.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                "kubectl cp sales.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                "kubectl cp analysis.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                "kubectl cp AdventureWorks.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                "kubectl cp AdventureWorksDW.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                "kubectl cp NYC.bak mssql-cluster/master-0:/var/opt/mssql/data -c mssql-server -n <cluster-name>\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "19106890-7c6c-4631-9acc-3dda4d2a50ab"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Check if the data is transfered successfully"
            ],
            "metadata": {
                "azdata_cell_guid": "407b8015-0820-4e3d-8607-96db43d9c64d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# List all the files downloaded in master instance\r\n",
                "kubectl exec master-0 -n mssql-cluster -- ls -la /var/opt/mssql/data"
            ],
            "metadata": {
                "azdata_cell_guid": "d914c707-5c2e-4c20-b600-b8c264779b61"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Copy Exported Data to Storage Pool**\r\n",
                "\r\n",
                "Next, you'll download a few text files that will form the external data to be ingested into the Storage Pool HDFS store. In production environments, you have multiple options for moving data into HDFS, such as Spark Streaming or the Azure Data Factory.\r\n",
                "\r\n",
                "The first code block creates directories in the HDFS store. The second block downloads the source data from a web location. And in the final block, you'll copy the data from your local system to the SQL Server 2019 big data cluster Storage Pool.\r\n",
                "\r\n",
                "You need to replace the `<ReplaceWithHDFSGatewayPassword>`, `<ReplaceWithHDFSGatewayEndpoint>`, and potentially the drive letter and directory values with the appropriate information on your system. \r\n",
                "> (You can use **CTL-H** to open the Find and Replace dialog in the cell)"
            ],
            "metadata": {
                "azdata_cell_guid": "2c426b35-dc57-4dc8-819d-6642deb69110"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# REM Make the Directories in HDFS\r\n",
                "# Run below commands in terminal or cmd, not powershell\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/product_review_data?op=MKDIRS\"\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/partner_customers?op=MKDIRS\"\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/partner_products?op=MKDIRS\"\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/web_logs?op=MKDIRS\"\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f2143d4e-6eb6-4bbc-864a-b417398adc21"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# REM Get the textfiles \r\n",
                "# Run below commands in terminal or cmd, not powershell\r\n",
                "curl -G \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/product_reviews_sample.csv\" -o product_reviews.csv\r\n",
                "curl -G \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/customers.csv\" -o customers.csv\r\n",
                "curl -G \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/stockitemholdings.csv\" -o products.csv\r\n",
                "curl -G \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/web_clickstreams.csv\" -o web_clickstreams.csv\r\n",
                "curl -G \"https://cs7a9736a9346a1x44c6xb00.blob.core.windows.net/backups/training-formatted.csv\" -o training-formatted.csv\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "c8a74514-2e0d-4f3c-99dd-4c541c11e15e"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "# REM Copy the text files to the HDFS directories\r\n",
                "# Run below commands in terminal or cmd, not powershell\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/product_review_data/product_reviews.csv?op=create&overwrite=true\" -H \"Content-Type: application/octet-stream\" -T \"product_reviews.csv\"\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/partner_customers/customers.csv?op=create&overwrite=true\" -H \"Content-Type: application/octet-stream\" -T \"customers.csv\"\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/partner_products/products.csv?op=create&overwrite=true\" -H \"Content-Type: application/octet-stream\" -T \"products.csv\"\r\n",
                "curl -i -L -k -u root:<password> -X PUT \"https://20.43.162.163:30443/gateway/default/webhdfs/v1/web_logs/web_clickstreams.csv?op=create&overwrite=true\" -H \"Content-Type: application/octet-stream\" -T \"web_clickstreams.csv\"\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "9c9e49ef-ef0d-47c4-92fd-b7e7bfa2d2f2"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Next Step: Working with the SQL Server 2019 big data cluster Master Instance**\r\n",
                "\r\n",
                "Now you're ready to open the next Jupyter Notebook - [bdc-01-k8s.ipynb](bdc-01-k8s.ipynb) - to learn how to monitor and troubleshoot SQL Server big data cluster."
            ],
            "metadata": {
                "azdata_cell_guid": "519aa112-47e0-443b-9b27-05fc02349b09"
            }
        }
    ]
}