bdc-03-sqlserver-master.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",
                "## **03 - SQL Server Master Instance Queries**\r\n",
                "\r\n",
                "In this tutorial you will learn how to run standard SQL Server Queries against the Master Instance (MI) in a SQL Server big data cluster. \r\n",
                "\r\n",
                "You'll start with a simple set of queries to explore the Instance: \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "bca9df1d-585b-40c9-b61f-0f1d5bff4d74"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "/* Instance Version */\r\n",
                "SELECT @@VERSION; \r\n",
                "GO\r\n",
                "\r\n",
                "/* General Configuration */\r\n",
                "USE master;  \r\n",
                "GO  \r\n",
                "EXEC sp_configure;\r\n",
                "GO\r\n",
                "\r\n",
                "/* Databases on this Instance */\r\n",
                "SELECT db.name AS 'Database Name'\r\n",
                ", Physical_Name AS 'Location on Disk'\r\n",
                ", Cast(Cast(Round(cast(mf.size as decimal) * 8.0/1024000.0,2) as decimal(18,2)) as nvarchar) 'Size (GB)'\r\n",
                "FROM sys.master_files mf\r\n",
                "INNER JOIN \r\n",
                "    sys.databases db ON db.database_id = mf.database_id\r\n",
                "WHERE mf.type_desc = 'ROWS';\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT * from sys.master_files\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "245818aa-6bbd-44eb-832e-8c57f1dbe564"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Ingest data into the SQL Server Databases**\r\n",
                "\r\n",
                "Before you start working with data, you need to briloadng it in to the system. \r\n",
                "\r\n",
                "You have several options to do that, from the `bcp` utility to SQL Server Integration Services, the Azure Data Factory and more. \r\n",
                "\r\n",
                "For the structured data, you'll use the SQL Server `RESTORE` command to bring in two databases from the location we specified earlier with the `kubectl` command.\r\n",
                "\r\n",
                "The Code below shows all of that: "
            ],
            "metadata": {
                "azdata_cell_guid": "0af26209-79fc-45ba-9c65-fd5bca373823"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "/* Add the Customer Databases to the cluster */\r\n",
                "USE [master]\r\n",
                "RESTORE DATABASE [analysis] FROM  DISK = N'/var/opt/mssql/data/analysis.bak' \r\n",
                "WITH  FILE = 1\r\n",
                ",  MOVE N'tpcxbb_1gb' TO N'/var/opt/mssql/data/analysis.mdf'\r\n",
                ",  MOVE N'tpcxbb_1gb_log' TO N'/var/opt/mssql/data/analysis.ldf'\r\n",
                ",  NOUNLOAD\r\n",
                ",  REPLACE\r\n",
                ",  STATS = 5\r\n",
                "GO\r\n",
                "\r\n",
                "USE [master]\r\n",
                "RESTORE DATABASE [sales] FROM  DISK = N'/var/opt/mssql/data/sales.bak' \r\n",
                "WITH  FILE = 1\r\n",
                ",  MOVE N'tpcxbb_1gb' TO N'/var/opt/mssql/data/sales.mdf'\r\n",
                ",  MOVE N'tpcxbb_1gb_log' TO N'/var/opt/mssql/data/sales.ldf'\r\n",
                ",  NOUNLOAD\r\n",
                ",  REPLACE\r\n",
                ",  STATS = 5\r\n",
                "GO\r\n",
                "\r\n",
                "USE [master]\r\n",
                "RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'/var/opt/mssql/data/WWI.bak' WITH  FILE = 1\r\n",
                ",  MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImporters.mdf'\r\n",
                ",  MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImporters_UserData.ndf'\r\n",
                ",  MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImporters.ldf'\r\n",
                ",  MOVE N'WWI_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1'\r\n",
                ",  NOUNLOAD\r\n",
                ",  REPLACE\r\n",
                ",  STATS = 5\r\n",
                "GO\r\n",
                "\r\n",
                "USE [master]\r\n",
                "RESTORE DATABASE [AdventureWorks] FROM  DISK = N'/var/opt/mssql/data/AdventureWorks.bak' \r\n",
                "WITH  FILE = 1\r\n",
                ",  MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks2017.mdf'\r\n",
                ",  MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks2017_log.ldf'\r\n",
                ",  NOUNLOAD\r\n",
                ",  REPLACE\r\n",
                ",  STATS = 5\r\n",
                "GO\r\n",
                "\r\n",
                "USE [master]\r\n",
                "RESTORE DATABASE [AdventureWorksDW] FROM  DISK = N'/var/opt/mssql/data/AdventureWorksDW.bak' \r\n",
                "WITH  FILE = 1,  MOVE N'AdventureWorksDW2017' TO N'/var/opt/mssql/data/AdventureWorksDW.mdf'\r\n",
                ",  MOVE N'AdventureWorksDW2017_log' TO N'/var/opt/mssql/data/AdventureWorksDW_log.ldf'\r\n",
                ",  NOUNLOAD\r\n",
                ",  REPLACE\r\n",
                ",  STATS = 5\r\n",
                "GO\r\n",
                "\r\n",
                "USE [master]\r\n",
                "RESTORE DATABASE [NYC] FROM  DISK = N'/var/opt/mssql/data/NYC.bak' \r\n",
                "WITH  FILE = 1\r\n",
                ",  MOVE N'NYCTaxi_Sample' TO N'/var/opt/mssql/data/NYC.mdf'\r\n",
                ",  MOVE N'NYCTaxi_Sample_log' TO N'/var/opt/mssql/data/NYC_0.ldf'\r\n",
                ",  NOUNLOAD\r\n",
                ",  REPLACE\r\n",
                ",  STATS = 5\r\n",
                "GO\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "2f79c85c-9896-4522-836e-016f94939d09",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Query Data**\r\n",
                "\r\n",
                "The SQL Server 2019 big data cluster Master Instance is a SQL Server Instance - and as such has most all of the query facilities and capabilities of Microsoft SQL Server running on Linux.\r\n",
                "\r\n",
                "In these queries you'll explore some customer information, in preparation for combining that information with external sources. You can add cells to this workbook to explore more. "
            ],
            "metadata": {
                "azdata_cell_guid": "7db932ab-1da7-41e4-9db3-aa869a81d59b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE WideWorldImporters;\r\n",
                "SELECT * FROM INFORMATION_SCHEMA.tables ORDER BY TABLE_SCHEMA"
            ],
            "metadata": {
                "azdata_cell_guid": "d19b079d-36b6-4203-a69a-c1dc78a092ee"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "/* Get some general information about the data in the WWI OLTP system */\r\n",
                "\r\n",
                "/* Show the Populations. \r\n",
                "Where do we have the most people?\r\n",
                " */\r\n",
                "SELECT TOP 10 CityName as 'City Name'\r\n",
                ", StateProvinceName as 'State or Province'\r\n",
                ", sp.LatestRecordedPopulation as 'Population'\r\n",
                ", CountryName\r\n",
                "FROM Application.Cities AS city\r\n",
                "JOIN Application.StateProvinces AS sp ON\r\n",
                "    city.StateProvinceID = sp.StateProvinceID\r\n",
                "JOIN Application.Countries AS ctry ON \r\n",
                "    sp.CountryID=ctry.CountryID\r\n",
                "ORDER BY Population, CityName;\r\n",
                "GO\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "25130f1b-8b75-41e5-894b-ffb863f9b9b4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "/* Show Customer Sales in WWI OLTP */\r\n",
                "USE WideWorldImporters;\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT TOP 10 s.CustomerID\r\n",
                ", s.CustomerName\r\n",
                ", sc.CustomerCategoryName\r\n",
                ",  pp.FullName AS PrimaryContact\r\n",
                ",  ap.FullName AS AlternateContact\r\n",
                ",  s.PhoneNumber\r\n",
                ",  s.FaxNumber\r\n",
                ",  bg.BuyingGroupName\r\n",
                ",  s.WebsiteURL\r\n",
                ",  dm.DeliveryMethodName AS DeliveryMethod\r\n",
                ",  c.CityName AS CityName\r\n",
                ",  s.DeliveryLocation AS DeliveryLocation\r\n",
                ",  s.DeliveryRun\r\n",
                ",  s.RunPosition\r\n",
                "FROM Sales.Customers AS s\r\n",
                "    LEFT OUTER JOIN Sales.CustomerCategories AS sc\r\n",
                "    ON s.CustomerCategoryID = sc.CustomerCategoryID\r\n",
                "    LEFT OUTER JOIN [Application].People AS pp\r\n",
                "    ON s.PrimaryContactPersonID = pp.PersonID\r\n",
                "    LEFT OUTER JOIN [Application].People AS ap\r\n",
                "    ON s.AlternateContactPersonID = ap.PersonID\r\n",
                "    LEFT OUTER JOIN Sales.BuyingGroups AS bg\r\n",
                "    ON s.BuyingGroupID = bg.BuyingGroupID\r\n",
                "    LEFT OUTER JOIN [Application].DeliveryMethods AS dm\r\n",
                "    ON s.DeliveryMethodID = dm.DeliveryMethodID\r\n",
                "    LEFT OUTER JOIN [Application].Cities AS c\r\n",
                "    ON s.DeliveryCityID = c.CityID\r\n",
                "ORDER BY c.CityName"
            ],
            "metadata": {
                "azdata_cell_guid": "14036e3c-e53f-4fef-b21c-5198ea53f463"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## **Next Step: Data Virtualization**\r\n",
                "\r\n",
                "Now you're ready to open the next Jupyter Notebook - [bdc-04-data-virtualization.ipynb](bdc-04-data-virtualization.ipynb) - to learn how to work with Data Virtualization."
            ],
            "metadata": {
                "azdata_cell_guid": "7c70d778-6f92-4d90-a41d-1966647410d5"
            }
        }
    ]
}