bdc-05-data-mart.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "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",
                "## **05 - Creating and Querying a Data Mart**\r\n",
                "\r\n",
                "In this tutorial you will learn how to create and query a Data Mart using Virtualized Data in a SQL Server big data cluster. \r\n",
                "\r\n",
                "Wide World Importers is interested in ingesting the data from web logs from an HDFS source where they have been streamed. They want to be able to analyze the traffic to see if there is a pattern in time, products or locations. \r\n",
                "\r\n",
                "The web logs, however, are refreshed periodically. WWI would like to keep the logs in local storage to do deeper analysis. \r\n",
                "\r\n",
                "In this Jupyter Notebook you'll create a location to store the log files as a SQL Server Table in the SQL Data Pool, and then fill it by creating an External Table that reads HDFS."
            ],
            "metadata": {
                "azdata_cell_guid": "4be7adee-14ac-4497-9cd7-dc38b664f485"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE WideWorldImporters;\r\n",
                "GO\r\n",
                "\r\n",
                "IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlDataPool')\r\n",
                "BEGIN\r\n",
                "    CREATE EXTERNAL DATA SOURCE SqlDataPool\r\n",
                "    WITH (LOCATION = 'sqldatapool://controller-svc/default');\r\n",
                "END\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "6912cda3-abe5-4241-a602-3c366a7c4ce2"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE EXTERNAL TABLE [web_clickstream_clicks_data_pool]\r\n",
                "    (\"wcs_click_date_sk\" BIGINT \r\n",
                "    , \"wcs_click_time_sk\" BIGINT \r\n",
                "    , \"wcs_sales_sk\" BIGINT \r\n",
                "    , \"wcs_item_sk\" BIGINT\r\n",
                "    , \"wcs_web_page_sk\" BIGINT \r\n",
                "    , \"wcs_user_sk\" BIGINT)\r\n",
                "    WITH\r\n",
                "    (\r\n",
                "    DATA_SOURCE = SqlDataPool,\r\n",
                "    DISTRIBUTION = ROUND_ROBIN\r\n",
                "    );\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "49c5954b-e0e5-4824-958c-da5ccf56ee95"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "/* Create an External Table that can read from the Storage Pool File Location */\r\n",
                "IF NOT EXISTS(SELECT * FROM sys.external_tables WHERE name = 'web_clickstreams_hdfs')\r\n",
                "BEGIN\r\n",
                "    CREATE EXTERNAL TABLE [web_clickstreams_hdfs]\r\n",
                "    (\"wcs_click_date_sk\" BIGINT \r\n",
                "    , \"wcs_click_time_sk\" BIGINT \r\n",
                "    , \"wcs_sales_sk\" BIGINT \r\n",
                "    , \"wcs_item_sk\" BIGINT\r\n",
                "    , \"wcs_web_page_sk\" BIGINT \r\n",
                "    , \"wcs_user_sk\" BIGINT)\r\n",
                "    WITH\r\n",
                "    (\r\n",
                "        DATA_SOURCE = SqlStoragePool,\r\n",
                "        LOCATION = '/web_logs',\r\n",
                "        FILE_FORMAT = csv_file\r\n",
                "    );\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "202463cd-45fb-48ca-b7e5-06ccc94f4de1"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "/* Insert data into data pool from storage pool*/\r\n",
                "BEGIN\r\n",
                "   INSERT INTO web_clickstream_clicks_data_pool\r\n",
                "   SELECT wcs_click_date_sk\r\n",
                "    , wcs_click_time_sk \r\n",
                "    , wcs_sales_sk \r\n",
                "    , wcs_item_sk \r\n",
                "    , wcs_web_page_sk  \r\n",
                "    , wcs_user_sk \r\n",
                "     FROM web_clickstreams_hdfs\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "8625c184-167c-4a65-8538-6ccc4ace2368"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can visualize the sales data inside azure data studio.  \r\n",
                "- Execute the below cell and click on the second dataframe. \r\n",
                "\r\n",
                "![plot](images/plot.png)\r\n",
                "\r\n",
                "---\r\n",
                "- Change the configuration of the plot, and you'll see the sales trend plot.\r\n",
                "\r\n",
                "![plot-config](images/plot-config.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "2e831a29-ef10-43e7-a0e0-aac3a5ccf9ff"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "/* SELECT clickstream data so that you can visualize it*/\r\n",
                "SELECT count(*) FROM [dbo].[web_clickstream_clicks_data_pool];\r\n",
                "SELECT TOP 1000 wcs_click_date_sk, wcs_sales_sk FROM [dbo].[web_clickstream_clicks_data_pool]\r\n",
                "WHERE wcs_sales_sk IS NOT NULL"
            ],
            "metadata": {
                "azdata_cell_guid": "254e598e-d8a2-47dd-8c98-4f9cb3904191"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Clean up only - run this cell only if you are repeating the demo!"
            ],
            "metadata": {
                "azdata_cell_guid": "bfb6ef2a-7897-4a6d-847a-274d15a18fed"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE WideWorldImporters;\r\n",
                "\r\n",
                "IF EXISTS(SELECT * FROM sys.external_tables WHERE name = 'web_clickstream_clicks_data_pool')\r\n",
                "BEGIN\r\n",
                "    DROP EXTERNAL TABLE web_clickstream_clicks_data_pool\r\n",
                "END\r\n",
                "\r\n",
                "IF EXISTS(SELECT * FROM sys.external_tables WHERE name = 'web_clickstreams_hdfs')\r\n",
                "BEGIN\r\n",
                "    DROP EXTERNAL TABLE web_clickstreams_hdfs\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "98f72726-bd0c-423b-92de-578c484f841b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Next Steps: Continue on to Working with Spark and ETL**\r\n",
                "\r\n",
                "Now you're ready to open the next Jupyter Notebook - [bdc-06-spark-etl.ipynb](bdc-06-spark-etl.ipynb) - to learn how to create and work with Spark and Extracting, Transforming and Loading data."
            ],
            "metadata": {
                "azdata_cell_guid": "a69641a1-2948-4f9d-924b-ba059215e87b"
            }
        }
    ]
}