tutorials/mssql_translator.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YVZXVTAnpTbW"
      },
      "source": [
        "# Qrlew as a query Translator\n",
        "[![View On GitHub](https://img.shields.io/badge/View_in_Github-grey?logo=github)](https://github.com/Qrlew/docs/blob/main/tutorials/mssql_translator.ipynb)\n",
        "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/Qrlew/pyqrlew/blob/main/examples/mssql_translator.ipynb)\n",
        "\n",
        "In this notebook, we apply `qrlew` to translate SQL queries from one dialect e.g. PostgresSql to another such as MsSQL."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7z7PGALUpTbX"
      },
      "source": [
        "## Install and run the Microsoft SQL Server\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "GeXxtrsNEyTr"
      },
      "outputs": [],
      "source": [
        "%%capture\n",
        "# Install the mssql-server 2017 (Not working on colab)\n",
        "!curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc\n",
        "!curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list\n",
        "!sudo apt-get update\n",
        "!sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18\n",
        "!sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18\n",
        "!sudo apt-get install -y unixodbc-dev\n",
        "\n",
        "# installing mssql-server\n",
        "!curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg\n",
        "!curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc\n",
        "!curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | sudo tee /etc/apt/sources.list.d/mssql-server-2022.list\n",
        "!sudo apt-get update\n",
        "!sudo apt-get install -y mssql-server\n",
        "\n",
        "# Configure and run the server manually. This is a workaround to install the MSSQL server in a colab\n",
        "%env MSSQL_SA_PASSWORD=\"MyStrongPass$\"\n",
        "!sudo /opt/mssql/bin/mssql-conf -n setup accept-eula\n",
        "!nohup /opt/mssql/bin/sqlservr &"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iJwE8DCOnnkx",
        "outputId": "10ed8f7e-2ad6-4b8b-94f2-7d177e7e0366"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "           \n",
            "-----------\n",
            "          1\n",
            "\n",
            "(1 rows affected)\n"
          ]
        }
      ],
      "source": [
        "# give time to the server to start and check that it started correctly\n",
        "import time\n",
        "time.sleep(10)\n",
        "!/opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P $MSSQL_SA_PASSWORD -C -Q \"SELECT 1\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "OjZXeKMNMIdP"
      },
      "outputs": [],
      "source": [
        "%%capture\n",
        "!pip install -U pyqrlew matplotlib graphviz sqlalchemy pyodbc"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oznAAH-fpTbY"
      },
      "source": [
        "For the purpose of this demonstration, we focus on a single table.\n",
        "We create the `census_schema` and `census_table` and we fill it with values."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rnIPJKPXpTbZ",
        "outputId": "aafc5c0b-9da8-4ec6-ed42-c4729087bddc"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "(70 rows affected)\n"
          ]
        }
      ],
      "source": [
        "sql_script = \"\"\"\n",
        "CREATE SCHEMA census_schema;\n",
        "GO\n",
        "CREATE TABLE census_schema.census_table (\n",
        "    id INT IDENTITY(1,1)  PRIMARY KEY,\n",
        "    first_name VARCHAR(50),\n",
        "    last_name VARCHAR(50),\n",
        "    age INT,\n",
        "    gender VARCHAR(10),\n",
        "    occupation VARCHAR(50),\n",
        "    city VARCHAR(50),\n",
        "    state VARCHAR(50)\n",
        ");\n",
        "GO\n",
        "-- Inserting sample data\n",
        "INSERT INTO census_schema.census_table (first_name, last_name, age, gender, occupation, city, state)\n",
        "VALUES\n",
        "    ('John', 'Doe', 30, 'Male', 'Engineer', 'New York', 'NY'),\n",
        "    ('Jane', 'Doe', 28, 'Female', 'Engineer', 'Los Angeles', 'CA'),\n",
        "    ('Bob', 'Smith', 45, 'Male', 'Analyst', 'Chicago', 'IL'),\n",
        "    ('Alice', 'Johnson', 35, 'Female', 'Data Scientist', 'San Francisco', 'CA'),\n",
        "    ('Tom', 'Brown', 50, 'Male', 'Lawyer', 'Miami', 'FL'),\n",
        "    ('Emily', 'Davis', 32, 'Female', 'Software Developer', 'Seattle', 'WA'),\n",
        "    ('Charlie', 'Miller', 40, 'Male', 'Accountant', 'Boston', 'MA'),\n",
        "    ('Eva', 'Williams', 29, 'Female', 'Marketing Manager', 'Austin', 'TX'),\n",
        "    ('Mike', 'Jones', 38, 'Male', 'Analyst', 'Denver', 'CO'),\n",
        "    ('Sophia', 'Taylor', 42, 'Female', 'Analyst', 'Atlanta', 'GA'),\n",
        "    ('John', 'Doe', 30, 'Male', 'Engineer', 'New York', 'NY'),\n",
        "    ('Jane', 'Doe', 28, 'Female', 'Engineer', 'Los Angeles', 'CA'),\n",
        "    ('Bob', 'Smith', 45, 'Male', 'Analyst', 'Chicago', 'IL'),\n",
        "    ('Alice', 'Johnson', 35, 'Female', 'Data Scientist', 'San Francisco', 'CA'),\n",
        "    ('Tom', 'Brown', 50, 'Male', 'Lawyer', 'Miami', 'FL'),\n",
        "    ('Emily', 'Davis', 32, 'Female', 'Software Developer', 'Seattle', 'WA'),\n",
        "    ('Charlie', 'Miller', 40, 'Male', 'Accountant', 'Boston', 'MA'),\n",
        "    ('Eva', 'Williams', 29, 'Female', 'Marketing Manager', 'Austin', 'TX'),\n",
        "    ('Mike', 'Jones', 38, 'Male', 'Analyst', 'Denver', 'CO'),\n",
        "    ('Sophia', 'Taylor', 42, 'Female', 'Analyst', 'Atlanta', 'GA'),\n",
        "    ('Alex', 'Johnson', 33, 'Male', 'Analyst', 'Portland', 'OR'),\n",
        "    ('Megan', 'White', 27, 'Female', 'Data Scientist', 'San Diego', 'CA'),\n",
        "    ('Daniel', 'Clark', 36, 'Male', 'Analyst', 'Houston', 'TX'),\n",
        "    ('Olivia', 'Martinez', 31, 'Female', 'Data Scientist', 'Phoenix', 'AZ'),\n",
        "    ('William', 'Lee', 48, 'Male', 'Professor', 'Philadelphia', 'PA'),\n",
        "    ('Emma', 'Garcia', 29, 'Female', 'Data Scientist', 'Minneapolis', 'MN'),\n",
        "    ('Ryan', 'Harris', 41, 'Male', 'Data Scientist', 'Dallas', 'TX'),\n",
        "    ('Ava', 'Taylor', 26, 'Female', 'Product Manager', 'San Jose', 'CA'),\n",
        "    ('Christopher', 'Anderson', 37, 'Male', 'Engineer', 'Detroit', 'MI'),\n",
        "    ('Sophie', 'Wilson', 34, 'Female', 'HR Specialist', 'Raleigh', 'NC'),\n",
        "    ('David', 'Miller', 29, 'Male', 'Engineer', 'New York', 'NY'),\n",
        "    ('Sophia', 'Taylor', 34, 'Female', 'Engineer', 'Los Angeles', 'CA'),\n",
        "    ('Bob', 'Smith', 42, 'Male', 'Analyst', 'Chicago', 'IL'),\n",
        "    ('Alice', 'Johnson', 39, 'Female', 'Data Scientist', 'San Francisco', 'CA'),\n",
        "    ('Tom', 'Brown', 37, 'Male', 'Lawyer', 'Miami', 'FL'),\n",
        "    ('Emily', 'Davis', 28, 'Female', 'Software Developer', 'Seattle', 'WA'),\n",
        "    ('Charlie', 'Miller', 45, 'Male', 'Accountant', 'Boston', 'MA'),\n",
        "    ('Eva', 'Williams', 31, 'Female', 'Marketing Manager', 'Austin', 'TX'),\n",
        "    ('Mike', 'Jones', 36, 'Male', 'Analyst', 'Denver', 'CO'),\n",
        "    ('Sophia', 'Taylor', 49, 'Female', 'Analyst', 'Atlanta', 'GA'),\n",
        "    ('Alex', 'Johnson', 37, 'Male', 'Analyst', 'Portland', 'OR'),\n",
        "    ('Megan', 'White', 27, 'Female', 'Data Scientist', 'San Diego', 'CA'),\n",
        "    ('Daniel', 'Clark', 41, 'Male', 'Analyst', 'Houston', 'TX'),\n",
        "    ('Olivia', 'Martinez', 32, 'Female', 'Data Scientist', 'Phoenix', 'AZ'),\n",
        "    ('William', 'Lee', 47, 'Male', 'Professor', 'Philadelphia', 'PA'),\n",
        "    ('Emma', 'Garcia', 30, 'Female', 'Data Scientist', 'Minneapolis', 'MN'),\n",
        "    ('Ryan', 'Harris', 40, 'Male', 'Entrepreneur', 'Dallas', 'TX'),\n",
        "    ('Ava', 'Taylor', 26, 'Female', 'Product Manager', 'San Jose', 'CA'),\n",
        "    ('Christopher', 'Anderson', 35, 'Male', 'Engineer', 'Detroit', 'MI'),\n",
        "    ('Sophie', 'Wilson', 33, 'Female', 'HR Specialist', 'Raleigh', 'NC'),\n",
        "    ('Grace', 'Brown', 55, 'Female', 'Analyst', 'New York', 'NY'),\n",
        "    ('Brian', 'Taylor', 50, 'Male', 'Professor', 'Los Angeles', 'CA'),\n",
        "    ('Lily', 'Harrison', 42, 'Female', 'Engineer', 'Chicago', 'IL'),\n",
        "    ('Kevin', 'Garcia', 55, 'Male', 'Analyst', 'San Francisco', 'CA'),\n",
        "    ('Sophie', 'Smithson', 48, 'Female', 'Lawyer', 'Miami', 'FL'),\n",
        "    ('John', 'Clarkston', 38, 'Male', 'Software Developer', 'Seattle', 'WA'),\n",
        "    ('Emma', 'Millerson', 40, 'Female', 'Accountant', 'Boston', 'MA'),\n",
        "    ('David', 'Williamson', 45, 'Male', 'Marketing Manager', 'Austin', 'TX'),\n",
        "    ('Ava', 'Johnsonson', 38, 'Female', 'Analyst', 'Denver', 'CO'),\n",
        "    ('Daniel', 'Anderson', 42, 'Male', 'Data Scientist', 'Atlanta', 'GA'),\n",
        "    ('Michael', 'Martinson', 33, 'Male', 'Engineer', 'Portland', 'OR'),\n",
        "    ('Sophia', 'Whiterson', 27, 'Female', 'Data Scientist', 'San Diego', 'CA'),\n",
        "    ('Robert', 'Davidson', 41, 'Male', 'Analyst', 'Houston', 'TX'),\n",
        "    ('Grace', 'Wilson', 45, 'Female', 'Data Scientist', 'Phoenix', 'AZ'),\n",
        "    ('Andrew', 'Leeson', 48, 'Male', 'Professor', 'Philadelphia', 'PA'),\n",
        "    ('Emily', 'Garcia', 39, 'Female', 'Data Scientist', 'Minneapolis', 'MN'),\n",
        "    ('Christopher', 'Sanders', 41, 'Male', 'Entrepreneur', 'Dallas', 'TX'),\n",
        "    ('Olivia', 'Martinez', 36, 'Female', 'Product Manager', 'San Jose', 'CA'),\n",
        "    ('Sophie', 'Brownson', 43, 'Female', 'Engineer', 'Detroit', 'MI'),\n",
        "    ('Matthew', 'Taylors', 46, 'Male', 'HR Specialist', 'Raleigh', 'NC');\n",
        "GO\n",
        "\n",
        "\"\"\"\n",
        "with open('sql_script.sql', 'w') as f:\n",
        "  f.write(sql_script)\n",
        "\n",
        "!/opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P $MSSQL_SA_PASSWORD -C -i sql_script.sql"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p7wDj3bCpTbZ"
      },
      "source": [
        "# Use qrlew on the database"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Nm9m9CadpTbZ"
      },
      "source": [
        "At this stage, we have a mssql dataset that can be connected with `qrlew`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "GGSyaQ8h49LU"
      },
      "outputs": [],
      "source": [
        "from urllib.parse import quote_plus\n",
        "from sqlalchemy import create_engine\n",
        "import os"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kasCN3_i4mZv",
        "outputId": "7c1f47d5-4bb6-47a1-b6a4-ed2ab572729c"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "mssql+pyodbc:///?odbc_connect=Driver%3D%7BODBC+Driver+18+for+SQL+Server%7D%3BServer%3Dtcp%3Alocalhost%3BDatabase%3Dmaster%3BUid%3Dsa%3BPwd%3D%22MyStrongPass%24%22%3BEncrypt%3Dyes%3BTrustServerCertificate%3Dyes%3BConnection+Timeout%3D30%3B\n"
          ]
        }
      ],
      "source": [
        "# creating the sqlalchemy engine\n",
        "MSSQL_DRIVER = \"{ODBC Driver 18 for SQL Server}\"\n",
        "MSSQL_SERVER = \"localhost\"\n",
        "MSSQL_DATABASE = \"master\"\n",
        "MSSQL_USERNAME = \"sa\"\n",
        "MSSQL_PASSWORD = os.environ.get('MSSQL_SA_PASSWORD')\n",
        "ADVANCED_PARAMETERS = \"Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;\"\n",
        "MSSQL_URI = \"mssql+pyodbc:///?odbc_connect={}\".format(\n",
        "    quote_plus(\n",
        "        (\n",
        "            f\"Driver={MSSQL_DRIVER};Server=tcp:{MSSQL_SERVER};\"\n",
        "            f\"Database={MSSQL_DATABASE};Uid={MSSQL_USERNAME};\"\n",
        "            f\"Pwd={MSSQL_PASSWORD};{ADVANCED_PARAMETERS}\"\n",
        "        )\n",
        "    )\n",
        ")\n",
        "print(MSSQL_URI)\n",
        "engine = create_engine(MSSQL_URI)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bNyTQREeNhJv",
        "outputId": "caed9f16-bed2-41f9-9637-d67bd87cc873"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "[(1,)]\n"
          ]
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "<ipython-input-7-0ac7eb6b049f>:3: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
            "  res = conn.execute(\"SELECT 1\")\n"
          ]
        }
      ],
      "source": [
        "# another sanity check with sqlalchemy\n",
        "with engine.connect() as conn:\n",
        "  res = conn.execute(\"SELECT 1\")\n",
        "  print(res.fetchall())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rLDseL_FpTba"
      },
      "source": [
        "The `dataset_from_database` function, located in `pyqrlew.io.dataset`, enables the creation of a Dataset object from a SQLAlchemy engine.\n",
        "It takes the following parameters:\n",
        "- `name`: the dataset's name,\n",
        "- `engine`: the SQLAlchemy engine establishing a connection to the database,\n",
        "- `schema_name`: the optional name of the schema,\n",
        "- `ranges`: if set to True, we use the the minimum and maximum values of numeric and datetime columns for setting their bounds.\n",
        "    **This may violates differential privacy** and should be use if these values are public.\n",
        "    Otherwise, use `False`. In that case, the bounds have to be provided in the SQL query,\n",
        "- `possible_values_threshold`: an optional integer that serves as a threshold for identifying a column as categorical. If a column surpasses this threshold, its categories are published. Similar to the ranges parameter, exercising this option may compromise differential privacy."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "8mv1h-zHpTba"
      },
      "outputs": [],
      "source": [
        "from pyqrlew.io.dataset import dataset_from_database\n",
        "from pyqrlew import Dialect\n",
        "\n",
        "ds = dataset_from_database(\n",
        "    name=\"census_table\",\n",
        "    engine=engine,\n",
        "    schema_name=\"census_schema\",\n",
        "    ranges=True,\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EbybUFIHpTba"
      },
      "source": [
        "`qrlew` connects to the database via the SQLAlchemy engine and fetch the tables."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wKo_hLzHpTba"
      },
      "source": [
        "The table `\"census_schema\".\"census_table\"` is represented as a `Table` variant of a `Relation`.\n",
        "This specific representation encapsulates all columns, their respective types, and optionally includes information about bounds and possible values.\n",
        "\n",
        "In the provided example, we observe the bounds specification for the integer column `age` and the declaration of possible values for the string column `gender`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 201
        },
        "id": "j2aeA1iwpTbb",
        "outputId": "3c656b4a-0dab-48e6-ba4b-b245ca827c40"
      },
      "outputs": [
        {
          "data": {
            "image/svg+xml": [
              "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
              "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
              " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
              "<!-- Generated by graphviz version 2.43.0 (0)\n",
              " -->\n",
              "<!-- Title: graph_8ozp Pages: 1 -->\n",
              "<svg width=\"319pt\" height=\"135pt\"\n",
              " viewBox=\"0.00 0.00 319.00 135.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
              "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 131)\">\n",
              "<title>graph_8ozp</title>\n",
              "<polygon fill=\"transparent\" stroke=\"transparent\" points=\"-4,4 -4,-131 315,-131 315,4 -4,4\"/>\n",
              "<!-- graph_8ozp -->\n",
              "<g id=\"node1\" class=\"node\">\n",
              "<title>graph_8ozp</title>\n",
              "<path fill=\"#ff1744\" stroke=\"#000000\" stroke-opacity=\"0.333333\" d=\"M299,-127C299,-127 12,-127 12,-127 6,-127 0,-121 0,-115 0,-115 0,-12 0,-12 0,-6 6,0 12,0 12,0 299,0 299,0 305,0 311,-6 311,-12 311,-12 311,-115 311,-115 311,-121 305,-127 299,-127\"/>\n",
              "<text text-anchor=\"start\" x=\"14\" y=\"-105.2\" font-family=\"Red Hat Display,sans-serif\" font-weight=\"bold\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">CENSUS_SCHEMA_CENSUS_TABLE size ∈ int{70}</text>\n",
              "<text text-anchor=\"start\" x=\"88\" y=\"-94.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">id = id ∈ int[1 70]</text>\n",
              "<text text-anchor=\"start\" x=\"54.5\" y=\"-83.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">first_name = first_name ∈ str</text>\n",
              "<text text-anchor=\"start\" x=\"61\" y=\"-72.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">last_name = last_name ∈ str</text>\n",
              "<text text-anchor=\"start\" x=\"78\" y=\"-61.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">age = age ∈ int[26 55]</text>\n",
              "<text text-anchor=\"start\" x=\"81.5\" y=\"-50.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">gender = gender ∈ str</text>\n",
              "<text text-anchor=\"start\" x=\"54.5\" y=\"-39.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">occupation = occupation ∈ str</text>\n",
              "<text text-anchor=\"start\" x=\"95\" y=\"-28.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">city = city ∈ str</text>\n",
              "<text text-anchor=\"start\" x=\"88\" y=\"-17.2\" font-family=\"Red Hat Display,sans-serif\" font-size=\"11.00\" fill=\"#ffffff\" fill-opacity=\"0.733333\">state = state ∈ str</text>\n",
              "</g>\n",
              "</g>\n",
              "</svg>\n"
            ],
            "text/plain": [
              "<graphviz.sources.Source at 0x79e50f65ec80>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "import graphviz\n",
        "display(graphviz.Source(ds.census_schema.census_table.relation().dot()))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HjtfgHGXpTbb"
      },
      "source": [
        "Any SQL query can be transformed into a `Relation`, a `qrlew` object which allows easly for query manipulation such as query translation to another dialect."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 454
        },
        "id": "XFSjLgYYpTbb",
        "outputId": "4bd26cb3-c3eb-49da-dca5-8647a6d3fd99"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\u001b[34mWITH\u001b[0m\n",
            "  map_u3eo (id, first_name, last_name, age, gender, occupation, city, state) \u001b[35mAS\u001b[0m (\u001b[35mSELECT\u001b[0m \"id\" \u001b[35mAS\u001b[0m id, \"first_name\" \u001b[35mAS\u001b[0m first_name, \"last_name\" \u001b[35mAS\u001b[0m last_name, \"age\" \u001b[35mAS\u001b[0m age, \"gender\" \u001b[35mAS\u001b[0m gender, \"occupation\" \u001b[35mAS\u001b[0m occupation, \"city\" \u001b[35mAS\u001b[0m city, \"state\" \u001b[35mAS\u001b[0m state FROM \"census_schema\".\"census_table\"),\n",
            "  map_1_fm (id, first_name, last_name, age, gender, occupation, city, state) \u001b[35mAS\u001b[0m (\u001b[35mSELECT\u001b[0m TOP (10) \"id\" \u001b[35mAS\u001b[0m id, \"first_name\" \u001b[35mAS\u001b[0m first_name, \"last_name\" \u001b[35mAS\u001b[0m last_name, \"age\" \u001b[35mAS\u001b[0m age, \"gender\" \u001b[35mAS\u001b[0m gender, \"occupation\" \u001b[35mAS\u001b[0m occupation, \"city\" \u001b[35mAS\u001b[0m city, \"state\" \u001b[35mAS\u001b[0m state FROM \"map_u3eo\")\n",
            "\u001b[35mSELECT\u001b[0m TOP (10) * FROM \"map_1_fm\"\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-3a8242f9-03cf-4d73-8ae9-fd2829c3377a\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>first_name</th>\n",
              "      <th>last_name</th>\n",
              "      <th>age</th>\n",
              "      <th>gender</th>\n",
              "      <th>occupation</th>\n",
              "      <th>city</th>\n",
              "      <th>state</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>John</td>\n",
              "      <td>Doe</td>\n",
              "      <td>30</td>\n",
              "      <td>Male</td>\n",
              "      <td>Engineer</td>\n",
              "      <td>New York</td>\n",
              "      <td>NY</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>Jane</td>\n",
              "      <td>Doe</td>\n",
              "      <td>28</td>\n",
              "      <td>Female</td>\n",
              "      <td>Engineer</td>\n",
              "      <td>Los Angeles</td>\n",
              "      <td>CA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Bob</td>\n",
              "      <td>Smith</td>\n",
              "      <td>45</td>\n",
              "      <td>Male</td>\n",
              "      <td>Analyst</td>\n",
              "      <td>Chicago</td>\n",
              "      <td>IL</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Alice</td>\n",
              "      <td>Johnson</td>\n",
              "      <td>35</td>\n",
              "      <td>Female</td>\n",
              "      <td>Data Scientist</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>CA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Tom</td>\n",
              "      <td>Brown</td>\n",
              "      <td>50</td>\n",
              "      <td>Male</td>\n",
              "      <td>Lawyer</td>\n",
              "      <td>Miami</td>\n",
              "      <td>FL</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>6</td>\n",
              "      <td>Emily</td>\n",
              "      <td>Davis</td>\n",
              "      <td>32</td>\n",
              "      <td>Female</td>\n",
              "      <td>Software Developer</td>\n",
              "      <td>Seattle</td>\n",
              "      <td>WA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>7</td>\n",
              "      <td>Charlie</td>\n",
              "      <td>Miller</td>\n",
              "      <td>40</td>\n",
              "      <td>Male</td>\n",
              "      <td>Accountant</td>\n",
              "      <td>Boston</td>\n",
              "      <td>MA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>8</td>\n",
              "      <td>Eva</td>\n",
              "      <td>Williams</td>\n",
              "      <td>29</td>\n",
              "      <td>Female</td>\n",
              "      <td>Marketing Manager</td>\n",
              "      <td>Austin</td>\n",
              "      <td>TX</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>9</td>\n",
              "      <td>Mike</td>\n",
              "      <td>Jones</td>\n",
              "      <td>38</td>\n",
              "      <td>Male</td>\n",
              "      <td>Analyst</td>\n",
              "      <td>Denver</td>\n",
              "      <td>CO</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10</td>\n",
              "      <td>Sophia</td>\n",
              "      <td>Taylor</td>\n",
              "      <td>42</td>\n",
              "      <td>Female</td>\n",
              "      <td>Analyst</td>\n",
              "      <td>Atlanta</td>\n",
              "      <td>GA</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3a8242f9-03cf-4d73-8ae9-fd2829c3377a')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-3a8242f9-03cf-4d73-8ae9-fd2829c3377a button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-3a8242f9-03cf-4d73-8ae9-fd2829c3377a');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-ebd2ee4a-3288-46de-8e0b-5fd46b6157eb\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-ebd2ee4a-3288-46de-8e0b-5fd46b6157eb')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-ebd2ee4a-3288-46de-8e0b-5fd46b6157eb button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "   id first_name last_name  age  gender          occupation           city  \\\n",
              "0   1       John       Doe   30    Male            Engineer       New York   \n",
              "1   2       Jane       Doe   28  Female            Engineer    Los Angeles   \n",
              "2   3        Bob     Smith   45    Male             Analyst        Chicago   \n",
              "3   4      Alice   Johnson   35  Female      Data Scientist  San Francisco   \n",
              "4   5        Tom     Brown   50    Male              Lawyer          Miami   \n",
              "5   6      Emily     Davis   32  Female  Software Developer        Seattle   \n",
              "6   7    Charlie    Miller   40    Male          Accountant         Boston   \n",
              "7   8        Eva  Williams   29  Female   Marketing Manager         Austin   \n",
              "8   9       Mike     Jones   38    Male             Analyst         Denver   \n",
              "9  10     Sophia    Taylor   42  Female             Analyst        Atlanta   \n",
              "\n",
              "  state  \n",
              "0    NY  \n",
              "1    CA  \n",
              "2    IL  \n",
              "3    CA  \n",
              "4    FL  \n",
              "5    WA  \n",
              "6    MA  \n",
              "7    TX  \n",
              "8    CO  \n",
              "9    GA  "
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import pandas as pd\n",
        "from pyqrlew.utils import print_query\n",
        "# write a PostgreSql query: LIMIT is not allowed in MsSql but it becomes TOP\n",
        "psql_query = \"SELECT * FROM census_schema.census_table LIMIT 10\"\n",
        "relation = ds.relation(psql_query)\n",
        "# we need to pass the MsSql dialect to to_query relation method\n",
        "mssql_query = relation.to_query(Dialect.MsSql)\n",
        "print_query(mssql_query)\n",
        "# execute\n",
        "results = pd.read_sql(mssql_query, engine)\n",
        "results"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zkh9DmjSIC7B"
      },
      "source": [
        "There are serveral differences between PostgreSql and MsSql dialects: `LIMIT` becomes `TOP` in the `SELECT` quantifier, `LN` which is natural log in PostgreSql becomes `LOG` in MsSql, `MD5(col)` PostgreSql function is tranlated as `CONVERT(VARCHAR(MAX), HASHBYTES('MD5', col, 2))` to name a few."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 470
        },
        "id": "8_w3ISbcEm6g",
        "outputId": "eeed3d75-25f5-4069-867d-2b069968fb45"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\u001b[34mWITH\u001b[0m\n",
            "  map_3l7g (age, nat_log_age, hashed_name) \u001b[35mAS\u001b[0m (\u001b[35mSELECT\u001b[0m \"age\" \u001b[35mAS\u001b[0m age, LOG(\"age\") \u001b[35mAS\u001b[0m nat_log_age, CONVERT(VARCHAR(MAX),\n",
            "  H\u001b[35mAS\u001b[0mHBYTES('MD5', CONCAT(\"first_name\", \"last_name\")),\n",
            "  2) \u001b[35mAS\u001b[0m hashed_name FROM \"census_schema\".\"census_table\"),\n",
            "  map_3_1r (age, nat_log_age, hashed_name) \u001b[35mAS\u001b[0m (\u001b[35mSELECT\u001b[0m TOP (10) \"age\" \u001b[35mAS\u001b[0m age, \"nat_log_age\" \u001b[35mAS\u001b[0m nat_log_age, \"hashed_name\" \u001b[35mAS\u001b[0m hashed_name FROM \"map_3l7g\")\n",
            "\u001b[35mSELECT\u001b[0m TOP (10) * FROM \"map_3_1r\"\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-d5366f69-9a87-4b81-98fe-f27db69cb0e7\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>age</th>\n",
              "      <th>nat_log_age</th>\n",
              "      <th>hashed_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>30</td>\n",
              "      <td>3.401197</td>\n",
              "      <td>9FD9F63E0D6487537569075DA85A0C7F</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>28</td>\n",
              "      <td>3.332205</td>\n",
              "      <td>1E218214D6994742C9D543F8FBB5EE10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>45</td>\n",
              "      <td>3.806662</td>\n",
              "      <td>103891BACA2751A856B094DB796E3FEE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>35</td>\n",
              "      <td>3.555348</td>\n",
              "      <td>62360D09B6561E319B76DA8AE91DD526</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>50</td>\n",
              "      <td>3.912023</td>\n",
              "      <td>5306CF440C6634E13DBF38DE4DFDA04E</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>32</td>\n",
              "      <td>3.465736</td>\n",
              "      <td>D7E90AD028AFE50F5A0AEB8A7A6818A1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>40</td>\n",
              "      <td>3.688879</td>\n",
              "      <td>83D1145CCB93A34EBBDBD410C3F9A5CB</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>29</td>\n",
              "      <td>3.367296</td>\n",
              "      <td>5436CDEC603390DE2E6C097FA7EBD065</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>38</td>\n",
              "      <td>3.637586</td>\n",
              "      <td>FC2BA1CF41ADAA8605FA0362C2E60AF1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>42</td>\n",
              "      <td>3.737670</td>\n",
              "      <td>0E7479482648F5E1013A27599B3F9648</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d5366f69-9a87-4b81-98fe-f27db69cb0e7')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-d5366f69-9a87-4b81-98fe-f27db69cb0e7 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-d5366f69-9a87-4b81-98fe-f27db69cb0e7');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-494c11da-f0a7-443c-8311-bc2bc7042f92\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-494c11da-f0a7-443c-8311-bc2bc7042f92')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-494c11da-f0a7-443c-8311-bc2bc7042f92 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "   age  nat_log_age                       hashed_name\n",
              "0   30     3.401197  9FD9F63E0D6487537569075DA85A0C7F\n",
              "1   28     3.332205  1E218214D6994742C9D543F8FBB5EE10\n",
              "2   45     3.806662  103891BACA2751A856B094DB796E3FEE\n",
              "3   35     3.555348  62360D09B6561E319B76DA8AE91DD526\n",
              "4   50     3.912023  5306CF440C6634E13DBF38DE4DFDA04E\n",
              "5   32     3.465736  D7E90AD028AFE50F5A0AEB8A7A6818A1\n",
              "6   40     3.688879  83D1145CCB93A34EBBDBD410C3F9A5CB\n",
              "7   29     3.367296  5436CDEC603390DE2E6C097FA7EBD065\n",
              "8   38     3.637586  FC2BA1CF41ADAA8605FA0362C2E60AF1\n",
              "9   42     3.737670  0E7479482648F5E1013A27599B3F9648"
            ]
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "psql_query = \"SELECT age, LN(age) AS nat_log_age, MD5(CONCAT(first_name, last_name)) AS hashed_name FROM census_schema.census_table LIMIT 10\"\n",
        "relation = ds.relation(psql_query)\n",
        "\n",
        "mssql_query = relation.to_query(Dialect.MsSql)\n",
        "print_query(mssql_query)\n",
        "\n",
        "results = pd.read_sql(mssql_query, engine)\n",
        "results"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "id": "voEgsnbdLljf"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "myenv",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.9.0"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}