{
    "cells": [
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "SAM003 - Data Pool Example\n",
                "==========================\n",
                "\n",
                "Description\n",
                "-----------\n",
                "\n",
                "In this tutorial, you learn how to:\n",
                "\n",
                "-   **Create a data pool source.**\n",
                "-   **Create an external table in the data pool.**\n",
                "-   **Insert data in data pool tables.**\n",
                "-   **Loading data from one data pool table to another.**\n",
                "-   **Join data in the data pool table with other data pool tables.**\n",
                "-   **Truncating tables.**\n",
                "-   **Cleanup.**\n",
                "\n",
                "### Create a database and an external data source"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "USE MASTER\n",
                "GO\n",
                "\n",
                "DROP DATABASE IF EXISTS datapoolNoteBookTutorialDB\n",
                "GO\n",
                "\n",
                "CREATE DATABASE datapoolNoteBookTutorialDB\n",
                "GO\n",
                "\n",
                "USE datapoolNoteBookTutorialDB\n",
                "GO\n",
                "\n",
                "CREATE EXTERNAL DATA SOURCE datapoolsrc\n",
                "WITH\n",
                "(\n",
                "    LOCATION = 'sqldatapool://controller-svc/default'\n",
                ")\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Create an external table in the data pool with Round Robin distribution"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "CREATE EXTERNAL TABLE [CustomersRR]\n",
                "(\n",
                "       [CustomerId] [int] NOT NULL,\n",
                "       [Name] [nvarchar](256) NOT NULL,\n",
                "       [RegionId] [int] NOT NULL\n",
                ") WITH\n",
                "(\n",
                "       DATA_SOURCE = datapoolsrc,\n",
                "       DISTRIBUTION = ROUND_ROBIN\n",
                ")\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Create an external table in the data pool with Replicated distribution"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "CREATE EXTERNAL TABLE [CustomersRep]\n",
                "(\n",
                "       [CustomerId] [int] NOT NULL,\n",
                "       [Name] [nvarchar](256) NOT NULL,\n",
                "       [RegionId] [int] NOT NULL\n",
                ") WITH\n",
                "(\n",
                "       DATA_SOURCE = datapoolsrc,\n",
                "       DISTRIBUTION = REPLICATED\n",
                ")\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Load data into tables"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "INSERT INTO CustomersRR VALUES (1, 'customer1', 1)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRR VALUES (2, 'customer2', 1)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRR VALUES (3, 'customer3', 2)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRR VALUES (4, 'customer4', 2)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRR VALUES (5, 'customer5', 3)\n",
                "GO"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "INSERT INTO CustomersRep VALUES (1, 'customerRep1', 1)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRep VALUES (2, 'customerRep2', 1)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRep VALUES (3, 'customerRep3', 2)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRep VALUES (4, 'customerRep4', 2)\n",
                "GO\n",
                "\n",
                "INSERT INTO CustomersRep VALUES (5, 'customerRep5', 3)\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Loading data from one data pool table to another"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "INSERT INTO CustomersRep\n",
                "SELECT * FROM CustomersRR\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Querying data"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "SELECT * FROM CustomersRR;\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Join between different data pool tables"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "SELECT CustomersRR.CustomerId, CustomersRR.Name, CustomersRR.RegionId \n",
                "FROM CustomersRR \n",
                "INNER JOIN CustomersRep ON CustomersRR.CustomerId = CustomersRep.CustomerId\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Truncate Tables"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "TRUNCATE TABLE CustomersRep\n",
                "GO"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "TRUNCATE TABLE CustomersRR\n",
                "GO"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Cleanup"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "DROP EXTERNAL TABLE CustomersRep\n",
                "GO\n",
                "\n",
                "DROP EXTERNAL TABLE CustomersRR\n",
                "GO\n",
                "\n",
                "DROP EXTERNAL DATA SOURCE datapoolsrc\n",
                "GO\n",
                "\n",
                "USE master\n",
                "GO\n",
                "\n",
                "DROP DATABASE datapoolNoteBookTutorialDB\n",
                "GO"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "print('Notebook execution complete.')"
            ]
        }
    ],
    "nbformat": 4,
    "nbformat_minor": 5,
    "metadata": {
        "kernelspec": {
            "name": "sql",
            "display_name": "SQL"
        },
        "azdata": {
            "side_effects": true
        }
    }
}