{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "T5-wikiSQL-with-HF-transformers.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyOKESPh7i0YCiRexy88VPG1",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "accelerator": "GPU",
    "widgets": {
      "application/vnd.jupyter.widget-state+json": {
        "dab3b41563e2413493b796a22776a6d4": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HBoxModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "HBoxModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "HBoxView",
            "box_style": "",
            "children": [
              "IPY_MODEL_a821ed3f960d44b99aefa77814fa23f1",
              "IPY_MODEL_49e47d1b79cf46a3881843ff210016bb",
              "IPY_MODEL_c740bd8b679448d9ae4badc67c36400f"
            ],
            "layout": "IPY_MODEL_cc7ec75bd9d14f4caa3ce9f87ff7bfbb"
          }
        },
        "a821ed3f960d44b99aefa77814fa23f1": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HTMLModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "HTMLModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "HTMLView",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_cb4cf1ebfc514891a253d9cd6a874ffc",
            "placeholder": "​",
            "style": "IPY_MODEL_03772b2541dd43c5a614829c7155cdc3",
            "value": "100%"
          }
        },
        "49e47d1b79cf46a3881843ff210016bb": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "FloatProgressModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "FloatProgressModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "ProgressView",
            "bar_style": "success",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_a2d910b8525c40879a4a31d805e384ab",
            "max": 1,
            "min": 0,
            "orientation": "horizontal",
            "style": "IPY_MODEL_72ce6fb8cb244ced88abc340f5bf1258",
            "value": 1
          }
        },
        "c740bd8b679448d9ae4badc67c36400f": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HTMLModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "HTMLModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "HTMLView",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_96f366309e644e0cbe7411f4c49f2c10",
            "placeholder": "​",
            "style": "IPY_MODEL_8a378b1afb7a407f93dacd7b6f10a7a7",
            "value": " 1/1 [00:00&lt;00:00, 11.34ba/s]"
          }
        },
        "cc7ec75bd9d14f4caa3ce9f87ff7bfbb": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "cb4cf1ebfc514891a253d9cd6a874ffc": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "03772b2541dd43c5a614829c7155cdc3": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "DescriptionStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "DescriptionStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "description_width": ""
          }
        },
        "a2d910b8525c40879a4a31d805e384ab": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "72ce6fb8cb244ced88abc340f5bf1258": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "ProgressStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "ProgressStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "bar_color": null,
            "description_width": ""
          }
        },
        "96f366309e644e0cbe7411f4c49f2c10": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "8a378b1afb7a407f93dacd7b6f10a7a7": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "DescriptionStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "DescriptionStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "description_width": ""
          }
        }
      }
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/mrm8488/shared_colab_notebooks/blob/master/T5_wikiSQL_with_HF_transformers.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Tutorial to fine-tune Google's text2text/seq2seq T5 model on WikiSQL dataset to translate from SQL to Natural Language and vice versa (multitask).\n",
        "\n",
        "Author: **Manuel Romero** [mrm8488](https://twitter.com/mrm8488). NLP/NLG Senior Engineer at [Narrativa](https://narrativa.com/) and Head collaborator at [HuggingFace](https://huggingface.co/mrm8488)\n",
        "\n",
        "> Note: This is still WIP so you may find errors"
      ],
      "metadata": {
        "id": "RXr_ss_50qPf"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## PART 1\n",
        "## Fine-tune T5 (small) to translate form Natural Language to SQL"
      ],
      "metadata": {
        "id": "HfPmlkfMSsSu"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## The model\n",
        "## [Exploring Transfer Learning with **T5**: the Text-To-Text Transfer Transformer](https://ai.googleblog.com/2020/02/exploring-transfer-learning-with-t5.html)"
      ],
      "metadata": {
        "id": "GdiqFsx0VLBY"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "![T5.png](data:image/png;base64,[encoded data, 219980 characters])\n",
        "\n"
      ],
      "metadata": {
        "id": "omPVRYUmUxup"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## The dataset\n",
        "## [WikiSQL](https://github.com/salesforce/WikiSQL)\n",
        "### A large crowd-sourced dataset for developing natural language interfaces for relational databases. WikiSQL is the dataset released along with Seq2SQL: Generating Structured Queries from Natural Language using Reinforcement Learning."
      ],
      "metadata": {
        "id": "ae9yi7fiVl6b"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Training with 🤗 ecosystem"
      ],
      "metadata": {
        "id": "4EDo89yfWVEn"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "21hGe7Mr0gos",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "314c5079-a527-458c-fb20-f274ec69bb80"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Sun Apr  3 20:59:28 2022       \n",
            "+-----------------------------------------------------------------------------+\n",
            "| NVIDIA-SMI 460.32.03    Driver Version: 460.32.03    CUDA Version: 11.2     |\n",
            "|-------------------------------+----------------------+----------------------+\n",
            "| GPU  Name        Persistence-M| Bus-Id        Disp.A | Volatile Uncorr. ECC |\n",
            "| Fan  Temp  Perf  Pwr:Usage/Cap|         Memory-Usage | GPU-Util  Compute M. |\n",
            "|                               |                      |               MIG M. |\n",
            "|===============================+======================+======================|\n",
            "|   0  Tesla V100-SXM2...  Off  | 00000000:00:04.0 Off |                    0 |\n",
            "| N/A   43C    P0    25W / 300W |      0MiB / 16160MiB |      0%      Default |\n",
            "|                               |                      |                  N/A |\n",
            "+-------------------------------+----------------------+----------------------+\n",
            "                                                                               \n",
            "+-----------------------------------------------------------------------------+\n",
            "| Processes:                                                                  |\n",
            "|  GPU   GI   CI        PID   Type   Process name                  GPU Memory |\n",
            "|        ID   ID                                                   Usage      |\n",
            "|=============================================================================|\n",
            "|  No running processes found                                                 |\n",
            "+-----------------------------------------------------------------------------+\n"
          ]
        }
      ],
      "source": [
        "! nvidia-smi"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install -q transformers datasets"
      ],
      "metadata": {
        "id": "28K5CDBE08jh"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## HF auth"
      ],
      "metadata": {
        "id": "agUs01Ms12b_"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KE_RTo78sqb-",
        "outputId": "8e5bc8f1-87d7-4ddf-d5eb-5bcae3db0f90"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "        _|    _|  _|    _|    _|_|_|    _|_|_|  _|_|_|  _|      _|    _|_|_|      _|_|_|_|    _|_|      _|_|_|  _|_|_|_|\n",
            "        _|    _|  _|    _|  _|        _|          _|    _|_|    _|  _|            _|        _|    _|  _|        _|\n",
            "        _|_|_|_|  _|    _|  _|  _|_|  _|  _|_|    _|    _|  _|  _|  _|  _|_|      _|_|_|    _|_|_|_|  _|        _|_|_|\n",
            "        _|    _|  _|    _|  _|    _|  _|    _|    _|    _|    _|_|  _|    _|      _|        _|    _|  _|        _|\n",
            "        _|    _|    _|_|      _|_|_|    _|_|_|  _|_|_|  _|      _|    _|_|_|      _|        _|    _|    _|_|_|  _|_|_|_|\n",
            "\n",
            "        To login, `huggingface_hub` now requires a token generated from https://huggingface.co/settings/token.\n",
            "        (Deprecated, will be removed in v0.3.0) To login with username and password instead, interrupt with Ctrl+C.\n",
            "        \n",
            "Token: \n",
            "Login successful\n",
            "Your token has been saved to /root/.huggingface/token\n"
          ]
        }
      ],
      "source": [
        "!huggingface-cli login"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "IADibkaEs79C"
      },
      "outputs": [],
      "source": [
        "! git config --global credential.helper store"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "Rv5ejYCus9TD"
      },
      "outputs": [],
      "source": [
        "%%capture\n",
        "! sudo apt-get install git-lfs"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Model and tokenizer"
      ],
      "metadata": {
        "id": "qZQ0lpecFdiF"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "CKPT = 't5-small'\n",
        "from transformers import AutoTokenizer, T5ForConditionalGeneration\n",
        "tokenizer = AutoTokenizer.from_pretrained(CKPT)\n",
        "model = T5ForConditionalGeneration.from_pretrained(CKPT)"
      ],
      "metadata": {
        "id": "XBTV-c1nFiAX"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Dataset preparation"
      ],
      "metadata": {
        "id": "OcC0F2ED2CKJ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's load the dataset"
      ],
      "metadata": {
        "id": "1UxMVyz5294I"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from datasets import load_dataset\n",
        "\n",
        "train_data = load_dataset('wikisql', split='train+validation')\n",
        "test_data = load_dataset('wikisql', split='test')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hM3s5q981Ga6",
        "outputId": "1299c801-ba7f-46ba-a422-e052272ed9bc"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Using custom data configuration default\n",
            "Reusing dataset wiki_sql (/root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d)\n",
            "Using custom data configuration default\n",
            "Reusing dataset wiki_sql (/root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's explore the dataset"
      ],
      "metadata": {
        "id": "ZewvnLHJ3CQX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "train_data"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4b8roCmG1tTA",
        "outputId": "005aae69-e4bb-4e43-e59d-48e42200b60e"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Dataset({\n",
              "    features: ['phase', 'question', 'table', 'sql'],\n",
              "    num_rows: 64776\n",
              "})"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "train_data[0]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_5V6i4yG2omO",
        "outputId": "90297a95-3942-4d14-d201-75105a41ae3c"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'phase': 1,\n",
              " 'question': 'Tell me what the notes are for South Australia ',\n",
              " 'sql': {'agg': 0,\n",
              "  'conds': {'column_index': [3],\n",
              "   'condition': ['SOUTH AUSTRALIA'],\n",
              "   'operator_index': [0]},\n",
              "  'human_readable': 'SELECT Notes FROM table WHERE Current slogan = SOUTH AUSTRALIA',\n",
              "  'sel': 5},\n",
              " 'table': {'caption': '',\n",
              "  'header': ['State/territory',\n",
              "   'Text/background colour',\n",
              "   'Format',\n",
              "   'Current slogan',\n",
              "   'Current series',\n",
              "   'Notes'],\n",
              "  'id': '1-1000181-1',\n",
              "  'name': 'table_1000181_1',\n",
              "  'page_id': '',\n",
              "  'page_title': '',\n",
              "  'rows': [['Australian Capital Territory',\n",
              "    'blue/white',\n",
              "    'Yaa·nna',\n",
              "    'ACT · CELEBRATION OF A CENTURY 2013',\n",
              "    'YIL·00A',\n",
              "    'Slogan screenprinted on plate'],\n",
              "   ['New South Wales',\n",
              "    'black/yellow',\n",
              "    'aa·nn·aa',\n",
              "    'NEW SOUTH WALES',\n",
              "    'BX·99·HI',\n",
              "    'No slogan on current series'],\n",
              "   ['New South Wales',\n",
              "    'black/white',\n",
              "    'aaa·nna',\n",
              "    'NSW',\n",
              "    'CPX·12A',\n",
              "    'Optional white slimline series'],\n",
              "   ['Northern Territory',\n",
              "    'ochre/white',\n",
              "    'Ca·nn·aa',\n",
              "    'NT · OUTBACK AUSTRALIA',\n",
              "    'CB·06·ZZ',\n",
              "    'New series began in June 2011'],\n",
              "   ['Queensland',\n",
              "    'maroon/white',\n",
              "    'nnn·aaa',\n",
              "    'QUEENSLAND · SUNSHINE STATE',\n",
              "    '999·TLG',\n",
              "    'Slogan embossed on plate'],\n",
              "   ['South Australia',\n",
              "    'black/white',\n",
              "    'Snnn·aaa',\n",
              "    'SOUTH AUSTRALIA',\n",
              "    'S000·AZD',\n",
              "    'No slogan on current series'],\n",
              "   ['Victoria',\n",
              "    'blue/white',\n",
              "    'aaa·nnn',\n",
              "    'VICTORIA - THE PLACE TO BE',\n",
              "    'ZZZ·562',\n",
              "    'Current series will be exhausted this year']],\n",
              "  'section_title': '',\n",
              "  'types': ['text', 'text', 'text', 'text', 'text', 'text']}}"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's format the dataset\n"
      ],
      "metadata": {
        "id": "x7L8T_VN3LYg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def format_dataset(example):\n",
        " return {'input': 'translate to SQL: ' + example['question'], 'target': example['sql']['human_readable']}"
      ],
      "metadata": {
        "id": "gaTsiEd0D-hU"
      },
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "train_data = train_data.map(format_dataset, remove_columns=train_data.column_names)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mt-c0E0GEPI7",
        "outputId": "082775a9-734f-4813-f4b2-3f2ccafb80fb"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-f6eaa945d21cf0a1.arrow\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "test_data = test_data.map(format_dataset, remove_columns=test_data.column_names)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Sg0pAzLQE68T",
        "outputId": "78cceed5-4aaf-4a4e-c63a-1ed70ebf743d"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-e2cff5b3a1b5b6d3.arrow\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "train_data"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bgWNGEJPFIdA",
        "outputId": "c3af8cfe-d4a8-463c-be25-26e35f4a0dec"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Dataset({\n",
              "    features: ['input', 'target'],\n",
              "    num_rows: 64776\n",
              "})"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's calculate the inputs and targets lengths"
      ],
      "metadata": {
        "id": "Apw09O1-M07K"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# map article and summary len to dict as well as if sample is longer than 512 tokens\n",
        "def map_to_length(x):\n",
        "  x[\"input_len\"] = len(tokenizer(x[\"input\"]).input_ids)\n",
        "  x[\"input_longer_256\"] = int(x[\"input_len\"] > 256)\n",
        "  x[\"input_longer_128\"] = int(x[\"input_len\"] > 128)\n",
        "  x[\"input_longer_64\"] = int(x[\"input_len\"] > 64)\n",
        "  x[\"out_len\"] = len(tokenizer(x[\"target\"]).input_ids)\n",
        "  x[\"out_longer_256\"] = int(x[\"out_len\"] > 256)\n",
        "  x[\"out_longer_128\"] = int(x[\"out_len\"] > 128)\n",
        "  x[\"out_longer_64\"] = int(x[\"out_len\"] > 64)\n",
        "  return x\n",
        "\n",
        "sample_size = 10000\n",
        "data_stats = train_data.select(range(sample_size)).map(map_to_length, num_proc=4)\n",
        "\n",
        "def compute_and_print_stats(x):\n",
        "  if len(x[\"input_len\"]) == sample_size:\n",
        "    print(\n",
        "        \"Input Mean: {}, %-Input > 256:{},  %-Input > 128:{}, %-Input > 64:{} Output Mean:{}, %-Output > 256:{}, %-Output > 128:{}, %-Output > 64:{}\".format(\n",
        "            sum(x[\"input_len\"]) / sample_size,\n",
        "            sum(x[\"input_longer_256\"]) / sample_size,\n",
        "            sum(x[\"input_longer_128\"]) / sample_size,\n",
        "            sum(x[\"input_longer_64\"]) / sample_size,   \n",
        "            sum(x[\"out_len\"]) / sample_size,\n",
        "            sum(x[\"out_longer_256\"]) / sample_size,\n",
        "            sum(x[\"out_longer_128\"]) / sample_size,\n",
        "            sum(x[\"out_longer_64\"]) / sample_size,\n",
        "        )\n",
        "    )\n",
        "\n",
        "output = data_stats.map(\n",
        "  compute_and_print_stats, \n",
        "  batched=True,\n",
        "  batch_size=-1,\n",
        ")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 156,
          "referenced_widgets": [
            "dab3b41563e2413493b796a22776a6d4",
            "a821ed3f960d44b99aefa77814fa23f1",
            "49e47d1b79cf46a3881843ff210016bb",
            "c740bd8b679448d9ae4badc67c36400f",
            "cc7ec75bd9d14f4caa3ce9f87ff7bfbb",
            "cb4cf1ebfc514891a253d9cd6a874ffc",
            "03772b2541dd43c5a614829c7155cdc3",
            "a2d910b8525c40879a4a31d805e384ab",
            "72ce6fb8cb244ced88abc340f5bf1258",
            "96f366309e644e0cbe7411f4c49f2c10",
            "8a378b1afb7a407f93dacd7b6f10a7a7"
          ]
        },
        "id": "LT7ovuoJFLFC",
        "outputId": "6c0f72bd-4e10-46d2-be64-e02826a2591e"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " "
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-1863bb36f979efb5.arrow\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " "
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-5950cb3095aabe7d.arrow\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " "
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-91b402e4a19939bd.arrow\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " "
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-83eac86c86834f97.arrow\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  0%|          | 0/1 [00:00<?, ?ba/s]"
            ],
            "application/vnd.jupyter.widget-view+json": {
              "version_major": 2,
              "version_minor": 0,
              "model_id": "dab3b41563e2413493b796a22776a6d4"
            }
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Input Mean: 19.8508, %-Input > 256:0.0,  %-Input > 128:0.0, %-Input > 64:0.0002 Output Mean:20.0403, %-Output > 256:0.0, %-Output > 128:0.0002, %-Output > 64:0.0005\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "As we can see input and output mean are around 20 tokens. So we should not use less than 20 token in both econder and decoder to keep all the info there. For this example we will use 64, but we could use 32."
      ],
      "metadata": {
        "id": "48n7qnn9NAES"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's tokenize our data"
      ],
      "metadata": {
        "id": "ruygyHEAR-BL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# tokenize the examples\n",
        "def convert_to_features(example_batch):\n",
        "    input_encodings = tokenizer.batch_encode_plus(example_batch['input'], pad_to_max_length=True, max_length=64)\n",
        "    target_encodings = tokenizer.batch_encode_plus(example_batch['target'], pad_to_max_length=True, max_length=64)\n",
        "\n",
        "    encodings = {\n",
        "        'input_ids': input_encodings['input_ids'], \n",
        "        'attention_mask': input_encodings['attention_mask'],\n",
        "        'labels': target_encodings['input_ids'],\n",
        "        'decoder_attention_mask': target_encodings['attention_mask']\n",
        "    }\n",
        "\n",
        "    return encodings"
      ],
      "metadata": {
        "id": "ppkB_UwQH7a6"
      },
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "train_data = train_data.map(convert_to_features, batched=True, remove_columns=train_data.column_names)\n",
        "test_data = test_data.map(convert_to_features, batched=True, emove_columns=test_data.column_names)\n",
        "\n",
        "columns = ['input_ids', 'attention_mask', 'labels', 'decoder_attention_mask']\n",
        "\n",
        "train_data.set_format(type='torch', columns=columns)\n",
        "test_data.set_format(type='torch', columns=columns)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JxPcQH1aIEj_",
        "outputId": "a808392d-d5be-4ee4-b2d3-cfc997f07e74"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-650d53a36abe5b77.arrow\n",
            "Loading cached processed dataset at /root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d/cache-74e255cfb147a24f.arrow\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from transformers import Seq2SeqTrainer\n",
        "from transformers import Seq2SeqTrainingArguments"
      ],
      "metadata": {
        "id": "wRNxG52qIYMf"
      },
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Set the training arguments"
      ],
      "metadata": {
        "id": "A2cbQnUJSG4i"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# set training arguments - Feel free to adapt it\n",
        "training_args = Seq2SeqTrainingArguments(\n",
        "    output_dir=\"/content/t5-small-finetuned-wikisql\",\n",
        "    per_device_train_batch_size=16,\n",
        "    num_train_epochs=5,\n",
        "    per_device_eval_batch_size=16,\n",
        "    predict_with_generate=True,\n",
        "    evaluation_strategy=\"epoch\",\n",
        "    do_train=True,\n",
        "    do_eval=True,\n",
        "    logging_steps=500,\n",
        "    save_strategy=\"epoch\",\n",
        "    #save_steps=1000,\n",
        "    #eval_steps=1000,\n",
        "    overwrite_output_dir=True,\n",
        "    save_total_limit=3,\n",
        "    load_best_model_at_end=True,\n",
        "    push_to_hub=True\n",
        "    #fp16=True, \n",
        ")"
      ],
      "metadata": {
        "id": "ZH-HHDI0IWrX"
      },
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's define a metric to track our training progress"
      ],
      "metadata": {
        "id": "0FrvqN66N20E"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install -q rouge_score"
      ],
      "metadata": {
        "id": "r5_gHE2wJeE0"
      },
      "execution_count": 21,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from datasets import load_metric\n",
        "rouge = load_metric(\"rouge\")\n",
        "\n",
        "def compute_metrics(pred):\n",
        "    labels_ids = pred.label_ids\n",
        "    pred_ids = pred.predictions\n",
        "\n",
        "    # all unnecessary tokens are removed\n",
        "    pred_str = tokenizer.batch_decode(pred_ids, skip_special_tokens=True)\n",
        "    labels_ids[labels_ids == -100] = tokenizer.pad_token_id\n",
        "    label_str = tokenizer.batch_decode(labels_ids, skip_special_tokens=True)\n",
        "\n",
        "    rouge_output = rouge.compute(predictions=pred_str, references=label_str, rouge_types=[\"rouge2\"])[\"rouge2\"].mid\n",
        "\n",
        "    return {\n",
        "        \"rouge2_precision\": round(rouge_output.precision, 4),\n",
        "        \"rouge2_recall\": round(rouge_output.recall, 4),\n",
        "        \"rouge2_fmeasure\": round(rouge_output.fmeasure, 4),\n",
        "    }"
      ],
      "metadata": {
        "id": "SqWbevh5Kelo"
      },
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Let's create a `Trainer` instance"
      ],
      "metadata": {
        "id": "y-Q4lxu8SMGo"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# instantiate trainer\n",
        "trainer = Seq2SeqTrainer(\n",
        "    model=model,\n",
        "    args=training_args,\n",
        "    compute_metrics=compute_metrics,\n",
        "    train_dataset=train_data,\n",
        "    eval_dataset=test_data,\n",
        ")"
      ],
      "metadata": {
        "id": "IAbtaix-KnXC"
      },
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's run a sanity check"
      ],
      "metadata": {
        "id": "UjNABeOSN-dT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "trainer.evaluate()"
      ],
      "metadata": {
        "id": "c-MSUMJqKrqb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 248
        },
        "outputId": "3cf2a23e-af5b-45c1-9f74-8b8814dd13b7"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "The following columns in the evaluation set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "***** Running Evaluation *****\n",
            "  Num examples = 15878\n",
            "  Batch size = 16\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "    <div>\n",
              "      \n",
              "      <progress value='993' max='993' style='width:300px; height:20px; vertical-align: middle;'></progress>\n",
              "      [993/993 03:49]\n",
              "    </div>\n",
              "    "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'eval_loss': 8.005945205688477,\n",
              " 'eval_rouge2_fmeasure': 0.0778,\n",
              " 'eval_rouge2_precision': 0.0785,\n",
              " 'eval_rouge2_recall': 0.0796,\n",
              " 'eval_runtime': 232.7062,\n",
              " 'eval_samples_per_second': 68.232,\n",
              " 'eval_steps_per_second': 4.267}"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Track the training in real time using `tensorboard`"
      ],
      "metadata": {
        "id": "z1pKhH-YRQQV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%load_ext tensorboard\n",
        "%tensorboard --logdir /content/t5-small-finetuned-wikisql/runs"
      ],
      "metadata": {
        "id": "2uiF7tA8KvxQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "trainer.train()"
      ],
      "metadata": {
        "id": "qZR_ASsEK5Lp",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "7d3b37cc-29a5-4bfe-ee49-38bdad08f864"
      },
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "The following columns in the training set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "/usr/local/lib/python3.7/dist-packages/transformers/optimization.py:309: FutureWarning: This implementation of AdamW is deprecated and will be removed in a future version. Use the PyTorch implementation torch.optim.AdamW instead, or set `no_deprecation_warning=True` to disable this warning\n",
            "  FutureWarning,\n",
            "***** Running training *****\n",
            "  Num examples = 64776\n",
            "  Num Epochs = 5\n",
            "  Instantaneous batch size per device = 16\n",
            "  Total train batch size (w. parallel, distributed & accumulation) = 16\n",
            "  Gradient Accumulation steps = 1\n",
            "  Total optimization steps = 20245\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "    <div>\n",
              "      \n",
              "      <progress value='20245' max='20245' style='width:300px; height:20px; vertical-align: middle;'></progress>\n",
              "      [20245/20245 47:11, Epoch 5/5]\n",
              "    </div>\n",
              "    <table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              " <tr style=\"text-align: left;\">\n",
              "      <th>Epoch</th>\n",
              "      <th>Training Loss</th>\n",
              "      <th>Validation Loss</th>\n",
              "      <th>Rouge2 Precision</th>\n",
              "      <th>Rouge2 Recall</th>\n",
              "      <th>Rouge2 Fmeasure</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>0.191800</td>\n",
              "      <td>0.156427</td>\n",
              "      <td>0.792600</td>\n",
              "      <td>0.702900</td>\n",
              "      <td>0.738100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>0.165500</td>\n",
              "      <td>0.138190</td>\n",
              "      <td>0.809200</td>\n",
              "      <td>0.717100</td>\n",
              "      <td>0.753400</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>0.152500</td>\n",
              "      <td>0.129753</td>\n",
              "      <td>0.815400</td>\n",
              "      <td>0.723700</td>\n",
              "      <td>0.759700</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>0.144300</td>\n",
              "      <td>0.126204</td>\n",
              "      <td>0.816300</td>\n",
              "      <td>0.724100</td>\n",
              "      <td>0.760300</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>5</td>\n",
              "      <td>0.141400</td>\n",
              "      <td>0.124436</td>\n",
              "      <td>0.817500</td>\n",
              "      <td>0.725800</td>\n",
              "      <td>0.761800</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table><p>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "The following columns in the evaluation set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "***** Running Evaluation *****\n",
            "  Num examples = 15878\n",
            "  Batch size = 16\n",
            "Saving model checkpoint to /content/t5-small-finetuned-wikisql/checkpoint-4049\n",
            "Configuration saved in /content/t5-small-finetuned-wikisql/checkpoint-4049/config.json\n",
            "Model weights saved in /content/t5-small-finetuned-wikisql/checkpoint-4049/pytorch_model.bin\n",
            "The following columns in the evaluation set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "***** Running Evaluation *****\n",
            "  Num examples = 15878\n",
            "  Batch size = 16\n",
            "Saving model checkpoint to /content/t5-small-finetuned-wikisql/checkpoint-8098\n",
            "Configuration saved in /content/t5-small-finetuned-wikisql/checkpoint-8098/config.json\n",
            "Model weights saved in /content/t5-small-finetuned-wikisql/checkpoint-8098/pytorch_model.bin\n",
            "The following columns in the evaluation set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "***** Running Evaluation *****\n",
            "  Num examples = 15878\n",
            "  Batch size = 16\n",
            "Saving model checkpoint to /content/t5-small-finetuned-wikisql/checkpoint-12147\n",
            "Configuration saved in /content/t5-small-finetuned-wikisql/checkpoint-12147/config.json\n",
            "Model weights saved in /content/t5-small-finetuned-wikisql/checkpoint-12147/pytorch_model.bin\n",
            "The following columns in the evaluation set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "***** Running Evaluation *****\n",
            "  Num examples = 15878\n",
            "  Batch size = 16\n",
            "Saving model checkpoint to /content/t5-small-finetuned-wikisql/checkpoint-16196\n",
            "Configuration saved in /content/t5-small-finetuned-wikisql/checkpoint-16196/config.json\n",
            "Model weights saved in /content/t5-small-finetuned-wikisql/checkpoint-16196/pytorch_model.bin\n",
            "Deleting older checkpoint [/content/t5-small-finetuned-wikisql/checkpoint-4049] due to args.save_total_limit\n",
            "The following columns in the evaluation set  don't have a corresponding argument in `T5ForConditionalGeneration.forward` and have been ignored: input, target. If input, target are not expected by `T5ForConditionalGeneration.forward`,  you can safely ignore this message.\n",
            "***** Running Evaluation *****\n",
            "  Num examples = 15878\n",
            "  Batch size = 16\n",
            "Saving model checkpoint to /content/t5-small-finetuned-wikisql/checkpoint-20245\n",
            "Configuration saved in /content/t5-small-finetuned-wikisql/checkpoint-20245/config.json\n",
            "Model weights saved in /content/t5-small-finetuned-wikisql/checkpoint-20245/pytorch_model.bin\n",
            "Deleting older checkpoint [/content/t5-small-finetuned-wikisql/checkpoint-8098] due to args.save_total_limit\n",
            "\n",
            "\n",
            "Training completed. Do not forget to share your model on huggingface.co/models =)\n",
            "\n",
            "\n",
            "Loading best model from /content/t5-small-finetuned-wikisql/checkpoint-20245 (score: 0.12443569302558899).\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "TrainOutput(global_step=20245, training_loss=0.17887339593157647, metrics={'train_runtime': 2831.3401, 'train_samples_per_second': 114.391, 'train_steps_per_second': 7.15, 'total_flos': 5479312832593920.0, 'train_loss': 0.17887339593157647, 'epoch': 5.0})"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "trainer.save_model()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M4f_eBTCeTHA",
        "outputId": "6c0ea618-bb69-43b2-a999-24f0f941b30e"
      },
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Saving model checkpoint to /content/t5-small-finetuned-wikisql\n",
            "Configuration saved in /content/t5-small-finetuned-wikisql/config.json\n",
            "Model weights saved in /content/t5-small-finetuned-wikisql/pytorch_model.bin\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "tokenizer.save_pretrained('/content/t5-small-finetuned-wikisql')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5d-QcwKnebYY",
        "outputId": "059c235f-8bc0-43ac-96de-4d60d865a3a9"
      },
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "tokenizer config file saved in /content/t5-small-finetuned-wikisql/tokenizer_config.json\n",
            "Special tokens file saved in /content/t5-small-finetuned-wikisql/special_tokens_map.json\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "('/content/t5-small-finetuned-wikisql/tokenizer_config.json',\n",
              " '/content/t5-small-finetuned-wikisql/special_tokens_map.json',\n",
              " '/content/t5-small-finetuned-wikisql/tokenizer.json')"
            ]
          },
          "metadata": {},
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "trainer.create_model_card()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JrBFmJbdW9XV",
        "outputId": "7a1d0db3-2913-4d5c-94a4-491266246e57"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Dropping the following result as it does not have all the necessary fields:\n",
            "{'task': {'name': 'Sequence-to-sequence Language Modeling', 'type': 'text2text-generation'}, 'dataset': {'name': 'wiki_sql', 'type': 'wiki_sql', 'args': 'default'}}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "trainer.push_to_hub()"
      ],
      "metadata": {
        "id": "nTn9FjGKo0S7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Test the model"
      ],
      "metadata": {
        "id": "wsJum5nEcHWg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "CKPT = '/content/t5-small-finetuned-wikisql'\n",
        "from transformers import AutoTokenizer, T5ForConditionalGeneration\n",
        "tokenizer = AutoTokenizer.from_pretrained(CKPT)\n",
        "model = T5ForConditionalGeneration.from_pretrained(CKPT)"
      ],
      "metadata": {
        "id": "HeaD6sejb7hg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "test_data = load_dataset('wikisql', split='test')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fGCO9LvFdAG6",
        "outputId": "9b6607ce-a283-4c56-9c5c-900e7b5c8ea6"
      },
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Using custom data configuration default\n",
            "Reusing dataset wiki_sql (/root/.cache/huggingface/datasets/wiki_sql/default/0.1.0/7037bfe6a42b1ca2b6ac3ccacba5253b1825d31379e9cc626fc79a620977252d)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def translate_to_sql(text):\n",
        "    inputs = tokenizer(text, padding='longest', max_length=64, return_tensors='pt')\n",
        "    input_ids = inputs.input_ids\n",
        "    attention_mask = inputs.attention_mask\n",
        "    output = model.generate(input_ids, attention_mask=attention_mask, max_length=64)\n",
        "\n",
        "    return tokenizer.decode(output[0], skip_special_tokens=True)"
      ],
      "metadata": {
        "id": "mAMMJJJvcQ38"
      },
      "execution_count": 66,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for i in range(0,100,10):\n",
        "  print('translate to SQL: ' + test_data[i]['question'])\n",
        "  print('Predict. :' + translate_to_sql('translate to SQL: ' + test_data[i]['question']))\n",
        "  print('Expected: ' + test_data[i]['sql']['human_readable'])\n",
        "  print('=================================\\n')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tbuoLD-hcpPF",
        "outputId": "d90012df-9258-4a35-e356-91e6ed499f6b"
      },
      "execution_count": 67,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "translate to SQL: What is terrence ross' nationality\n",
            "Predict. :SELECT Nationality FROM table WHERE Player = Terrence Ross\n",
            "Expected: SELECT Nationality FROM table WHERE Player = Terrence Ross\n",
            "=================================\n",
            "\n",
            "translate to SQL: How many different nationalities do the players of New Jersey Devils come from?\n",
            "Predict. :SELECT COUNT Nationality FROM table WHERE Team = New Jersey Devils\n",
            "Expected: SELECT COUNT Nationality FROM table WHERE NHL team = New Jersey Devils\n",
            "=================================\n",
            "\n",
            "translate to SQL: If you are a pilot officer in the commonwealth then what will you called as in the US air force?\n",
            "Predict. :SELECT US air force FROM table WHERE Position = pilot officer AND Country = commonwealth\n",
            "Expected: SELECT US Air Force equivalent FROM table WHERE Commonwealth equivalent = Pilot Officer\n",
            "=================================\n",
            "\n",
            "translate to SQL: what is the total number of total w–l where doubles w–l is 11–11\n",
            "Predict. :SELECT COUNT Total W–L FROM table WHERE Doubles W–L = 11–11\n",
            "Expected: SELECT COUNT Total W–L FROM table WHERE Doubles W–L = 11–11\n",
            "=================================\n",
            "\n",
            "translate to SQL: Which Frequency is used for WEGP calls?\n",
            "Predict. :SELECT Frequency FROM table WHERE Calls = WEGP\n",
            "Expected: SELECT Frequency FROM table WHERE Calls = WEGP\n",
            "=================================\n",
            "\n",
            "translate to SQL: If a radius is 10, what  is the lowest possible mass?\n",
            "Predict. :SELECT MIN Mass FROM table WHERE Radius = 10\n",
            "Expected: SELECT MIN Mass (M ☉ ) FROM table WHERE Radius (R ☉ ) = 10\n",
            "=================================\n",
            "\n",
            "translate to SQL: what's the new south wales with crop (kilotonnes) being canola\n",
            "Predict. :SELECT New South Wales FROM table WHERE Crop (Kilotonnes) = Canola\n",
            "Expected: SELECT New South Wales FROM table WHERE Crop (kilotonnes) = Canola\n",
            "=================================\n",
            "\n",
            "translate to SQL: If % lunsford is 51.82% what is the % mcconnell in Letcher?\n",
            "Predict. :SELECT % McConnell FROM table WHERE % Lunsford = 51.82%\n",
            "Expected: SELECT % McConnell FROM table WHERE % Lunsford = 51.82%\n",
            "=================================\n",
            "\n",
            "translate to SQL: What is the percentage of the Shivalik Zone where the percentage of the Mid-Hill Zone is 10%?\n",
            "Predict. :SELECT Shivalik Zone FROM table WHERE Mid-Hill Zone = 10%\n",
            "Expected: SELECT Shivalik Zone FROM table WHERE Mid-Hill Zone = 10%\n",
            "=================================\n",
            "\n",
            "translate to SQL: How many episodes in season 6 titles \"Poppin' Tags\"?\n",
            "Predict. :SELECT COUNT Season 6 FROM table WHERE Title = \"Poppin' Tags\"\n",
            "Expected: SELECT COUNT No. in season FROM table WHERE Title = \"Poppin' Tags\"\n",
            "=================================\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Custom example"
      ],
      "metadata": {
        "id": "7iZtACF4g1Hq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "text = \"translate to SQL: How many model with BERT architecture are in the HuggingFace Hub?\"\n",
        "translate_to_sql(text)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "PO1OvqSedZhI",
        "outputId": "1cea30ac-e49d-4fc1-8332-338053bc6646"
      },
      "execution_count": 70,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'SELECT COUNT Model FROM table WHERE Architecture = BERT AND Hub = HuggingFace'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 70
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "-V7bmzeQgqtA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Acknowledgments\n",
        "\n",
        "- [Narrativa](https://narrativa.com)\n",
        "- [HuggingFace](https://huggingface.co/mrm8488)\n",
        "- [QBLOCKS](https://www.qblocks.cloud/)"
      ],
      "metadata": {
        "id": "MOx78lCspgyI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "61dslGdDqHfA"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}