abstractive-qa.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f8c0ba02",
   "metadata": {},
   "source": [
    "# Abstractive QA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd49e569",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/myscale/examples/blob/main/abstractive-qa.ipynb\" style=\"padding-left: 0.5rem;\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg?style=plastic)](https://colab.research.google.com/github/myscale/examples/blob/main/abstractive-qa.ipynb)\"></a><a href=\"https://github.com/myscale/examples/blob/main/abstractive-qa.ipynb\" style=\"padding-left: 0.5rem;\"><img src=\"https://img.shields.io/badge/Open-Github-blue.svg?logo=github&style=plastic)](https://github.com/myscale/examples/blob/main/abstractive-qa.ipynb)\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15b1a936",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "Abstractive QA (Question Answering) is a type of natural language processing (NLP) technique that involves generating an answer to a given question in natural language by summarizing and synthesizing information from various sources, rather than just selecting an answer from pre-existing text.\n",
    "\n",
    "Unlike extractive QA, which relies on identifying and extracting relevant passages of text from a corpus of documents to answer a question, abstractive QA systems are capable of generating new, original sentences that capture the key information and meaning required to answer the question.\n",
    "\n",
    "In this notebook, you will learn how MyScale can assist you in creating a abstractive QA application with openai api. There are three primary components required to construct a question-answering system:\n",
    "1. A vector index for semantic search storage and execution.\n",
    "2. A retriever model to embed contextual passages.\n",
    "3. OpenAI API for answer extraction.\n",
    "\n",
    "We will use [bitcoin_articles dataset](https://www.kaggle.com/datasets/balabaskar/bitcoin-news-articles-text-corpora), which contains a collection of news articles on Bitcoin that have been obtained through web scraping from different sources on the Internet using the Newscatcher API. We'll use the retriever to create embeddings for the context passages, index them in the vector database, and execute a semantic search to retrieve the top k most relevant contexts with potential answers to our question. OpenAI API will then be used to generate answers based on the returned contexts.\n",
    "\n",
    "If you're more interested in exploring capabilities of MyScale, feel free to skip the [Building dataset](#building-dataset) section and dive right into the [Populating data to MyScale](#populate-data-to-myscale) section."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3df4667",
   "metadata": {},
   "source": [
    "## Prerequisites\n",
    "Before we get started, we need to install tools such as [clickhouse python client](https://clickhouse.com/docs/en/integrations/language-clients/python/intro/), openai, sentence-transformer, and other dependencies."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "606ead67",
   "metadata": {},
   "source": [
    "### Install dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b9ff719f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: clickhouse-connect in /opt/anaconda3/lib/python3.8/site-packages (0.5.17)\n",
      "Requirement already satisfied: openai in /opt/anaconda3/lib/python3.8/site-packages (0.26.5)\n",
      "Requirement already satisfied: sentence-transformers in /opt/anaconda3/lib/python3.8/site-packages (2.2.2)\n",
      "Requirement already satisfied: torch in /opt/anaconda3/lib/python3.8/site-packages (1.13.1)\n",
      "Requirement already satisfied: requests in /opt/anaconda3/lib/python3.8/site-packages (2.26.0)\n",
      "Requirement already satisfied: pandas in /opt/anaconda3/lib/python3.8/site-packages (1.3.5)\n",
      "Requirement already satisfied: tqdm in /opt/anaconda3/lib/python3.8/site-packages (4.64.1)\n",
      "Requirement already satisfied: certifi in /opt/anaconda3/lib/python3.8/site-packages (from clickhouse-connect) (2021.10.8)\n",
      "Requirement already satisfied: lz4 in /opt/anaconda3/lib/python3.8/site-packages (from clickhouse-connect) (4.3.2)\n",
      "Requirement already satisfied: pytz in /opt/anaconda3/lib/python3.8/site-packages (from clickhouse-connect) (2021.3)\n",
      "Requirement already satisfied: zstandard in /opt/anaconda3/lib/python3.8/site-packages (from clickhouse-connect) (0.20.0)\n",
      "Requirement already satisfied: urllib3>=1.26 in /opt/anaconda3/lib/python3.8/site-packages (from clickhouse-connect) (1.26.7)\n",
      "Requirement already satisfied: aiohttp in /opt/anaconda3/lib/python3.8/site-packages (from openai) (3.8.4)\n",
      "Requirement already satisfied: scipy in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (1.7.3)\n",
      "Requirement already satisfied: transformers<5.0.0,>=4.6.0 in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (4.26.1)\n",
      "Requirement already satisfied: sentencepiece in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (0.1.97)\n",
      "Requirement already satisfied: numpy in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (1.21.2)\n",
      "Requirement already satisfied: nltk in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (3.6.5)\n",
      "Requirement already satisfied: torchvision in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (0.14.1)\n",
      "Requirement already satisfied: huggingface-hub>=0.4.0 in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (0.12.0)\n",
      "Requirement already satisfied: scikit-learn in /opt/anaconda3/lib/python3.8/site-packages (from sentence-transformers) (1.2.2)\n",
      "Requirement already satisfied: typing-extensions in /opt/anaconda3/lib/python3.8/site-packages (from torch) (4.4.0)\n",
      "Requirement already satisfied: charset-normalizer~=2.0.0 in /opt/anaconda3/lib/python3.8/site-packages (from requests) (2.0.9)\n",
      "Requirement already satisfied: idna<4,>=2.5 in /opt/anaconda3/lib/python3.8/site-packages (from requests) (3.1)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /opt/anaconda3/lib/python3.8/site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: filelock in /opt/anaconda3/lib/python3.8/site-packages (from huggingface-hub>=0.4.0->sentence-transformers) (3.4.0)\n",
      "Requirement already satisfied: packaging>=20.9 in /Users/guanxiaoyao/.local/lib/python3.8/site-packages (from huggingface-hub>=0.4.0->sentence-transformers) (23.0)\n",
      "Requirement already satisfied: pyyaml>=5.1 in /opt/anaconda3/lib/python3.8/site-packages (from huggingface-hub>=0.4.0->sentence-transformers) (6.0)\n",
      "Requirement already satisfied: six>=1.5 in /opt/anaconda3/lib/python3.8/site-packages (from python-dateutil>=2.7.3->pandas) (1.16.0)\n",
      "Requirement already satisfied: regex!=2019.12.17 in /opt/anaconda3/lib/python3.8/site-packages (from transformers<5.0.0,>=4.6.0->sentence-transformers) (2021.11.10)\n",
      "Requirement already satisfied: tokenizers!=0.11.3,<0.14,>=0.11.1 in /opt/anaconda3/lib/python3.8/site-packages (from transformers<5.0.0,>=4.6.0->sentence-transformers) (0.13.2)\n",
      "Requirement already satisfied: yarl<2.0,>=1.0 in /opt/anaconda3/lib/python3.8/site-packages (from aiohttp->openai) (1.8.2)\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in /opt/anaconda3/lib/python3.8/site-packages (from aiohttp->openai) (1.3.3)\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in /opt/anaconda3/lib/python3.8/site-packages (from aiohttp->openai) (1.3.1)\n",
      "Requirement already satisfied: async-timeout<5.0,>=4.0.0a3 in /opt/anaconda3/lib/python3.8/site-packages (from aiohttp->openai) (4.0.2)\n",
      "Requirement already satisfied: multidict<7.0,>=4.5 in /opt/anaconda3/lib/python3.8/site-packages (from aiohttp->openai) (6.0.4)\n",
      "Requirement already satisfied: attrs>=17.3.0 in /opt/anaconda3/lib/python3.8/site-packages (from aiohttp->openai) (21.2.0)\n",
      "Requirement already satisfied: joblib in /opt/anaconda3/lib/python3.8/site-packages (from nltk->sentence-transformers) (1.2.0)\n",
      "Requirement already satisfied: click in /opt/anaconda3/lib/python3.8/site-packages (from nltk->sentence-transformers) (8.0.3)\n",
      "Requirement already satisfied: threadpoolctl>=2.0.0 in /opt/anaconda3/lib/python3.8/site-packages (from scikit-learn->sentence-transformers) (3.0.0)\n",
      "Requirement already satisfied: pillow!=8.3.*,>=5.3.0 in /opt/anaconda3/lib/python3.8/site-packages (from torchvision->sentence-transformers) (8.4.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install clickhouse-connect openai sentence-transformers torch requests pandas tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a4e74b4",
   "metadata": {},
   "source": [
    "### Setup openai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8fc49972",
   "metadata": {},
   "outputs": [],
   "source": [
    "import openai\n",
    "openai.api_key = \"YOUR_OPENAI_API_KEY\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce906961",
   "metadata": {},
   "source": [
    "### Setup retriever\n",
    "We will have to initiate our retriever, which will primarily perform two tasks, the first is optional:\n",
    "\n",
    "1. Produce embeddings for each context passage (context vectors/embeddings)\n",
    "2. Produce an embedding for our queries (query vector/embedding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f43f611b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SentenceTransformer(\n",
       "  (0): Transformer({'max_seq_length': 512, 'do_lower_case': False}) with Transformer model: BertModel \n",
       "  (1): Pooling({'word_embedding_dimension': 384, 'pooling_mode_cls_token': False, 'pooling_mode_mean_tokens': True, 'pooling_mode_max_tokens': False, 'pooling_mode_mean_sqrt_len_tokens': False})\n",
       "  (2): Normalize()\n",
       ")"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import torch\n",
    "from sentence_transformers import SentenceTransformer\n",
    "# set device to GPU if available\n",
    "device = 'cuda' if torch.cuda.is_available() else 'cpu'\n",
    "# load the retriever model from huggingface model hub\n",
    "retriever = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1', device=device)\n",
    "retriever"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c796ece",
   "metadata": {},
   "source": [
    "## Building dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3dc0cc6",
   "metadata": {},
   "source": [
    "### Downloading and processing data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f1d0487",
   "metadata": {},
   "source": [
    "The dataset contains news articles about Bitcoin that were web scraped from various sources on the internet using Newscatcher API.\n",
    "\n",
    "The information is given in CSV files and includes details such as article ID, title, author, published date, link, summary, topic, country, language, and more.Initially, we create a compact database for retrieving data. \n",
    "\n",
    "To make this notebook easier, we keep a full copy of the Kaggle dataset [bitcoin-news-articles-text-corpora](https://www.kaggle.com/datasets/balabaskar/bitcoin-news-articles-text-corpora) on S3 to save time configuring [Kaggle's Public API](https://github.com/Kaggle/kaggle-api) credentials.\n",
    "\n",
    "So, we can download the dataset by the command below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4c452bdb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100  837k  100  837k    0     0   196k      0  0:00:04  0:00:04 --:--:--  206k\n",
      "Archive:  bitcoin-news-articles-text-corpora.zip\n",
      "  inflating: bitcoin_articles.csv    \n"
     ]
    }
   ],
   "source": [
    "!wget https://myscale-saas-assets.s3.ap-southeast-1.amazonaws.com/testcases/clickhouse/bitcoin-news-articles-text-corpora.zip\n",
    "\n",
    "# unzip the downloaded file\n",
    "!unzip -o bitcoin-news-articles-text-corpora.zip "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62b9e455",
   "metadata": {},
   "source": [
    "After loading data we perform basic data manipulation tasks such as eliminating duplicate entries and removing empty cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "61ecfb61",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "data_raw = pd.read_csv('bitcoin_articles.csv')\n",
    "\n",
    "data_raw.drop_duplicates(subset=['summary'], keep='first', inplace=True)\n",
    "data_raw.dropna(subset=['summary'], inplace=True)\n",
    "data_raw.dropna(subset=['author'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "01cc7339",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>article_id</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>published_date</th>\n",
       "      <th>link</th>\n",
       "      <th>clean_url</th>\n",
       "      <th>excerpt</th>\n",
       "      <th>summary</th>\n",
       "      <th>rights</th>\n",
       "      <th>article_rank</th>\n",
       "      <th>topic</th>\n",
       "      <th>country</th>\n",
       "      <th>language</th>\n",
       "      <th>authors</th>\n",
       "      <th>media</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>article_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>57a00c1140cbd3af79e77bf0e4e6af48</td>\n",
       "      <td>62% of Bitcoin Has Not Moved in a Year as Long...</td>\n",
       "      <td>Jamie McNeill</td>\n",
       "      <td>04-10-2022 17:15</td>\n",
       "      <td>https://www.business2community.com/crypto-news...</td>\n",
       "      <td>business2community.com</td>\n",
       "      <td>Over the course of the last few years, there h...</td>\n",
       "      <td>Over the course of the last few years, there h...</td>\n",
       "      <td>business2community.com</td>\n",
       "      <td>1595</td>\n",
       "      <td>finance</td>\n",
       "      <td>US</td>\n",
       "      <td>en</td>\n",
       "      <td>Jamie McNeill</td>\n",
       "      <td>https://www.business2community.com/wp-content/...</td>\n",
       "      <td>@Jamie_DeFi</td>\n",
       "      <td>8.556426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21b48b3731c03466be3fac4be6c7dc67</td>\n",
       "      <td>The Orange Party Issue Playlist</td>\n",
       "      <td>Bitcoin Magazine</td>\n",
       "      <td>05-10-2022 21:17</td>\n",
       "      <td>https://bitcoinmagazine.com/culture/orange-par...</td>\n",
       "      <td>bitcoinmagazine.com</td>\n",
       "      <td>News Links: Russia Legalizing Bitcoin And Cryp...</td>\n",
       "      <td>Russia Legalizing Bitcoin And Crypto Is A Matt...</td>\n",
       "      <td>bitcoinmagazine.com</td>\n",
       "      <td>6284</td>\n",
       "      <td>news</td>\n",
       "      <td>US</td>\n",
       "      <td>en</td>\n",
       "      <td>Bitcoin Magazine</td>\n",
       "      <td>https://bitcoinmagazine.com/.image/t_share/MTk...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.507881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77030740ee160ad68c25e4e63515dd77</td>\n",
       "      <td>How Many Bitcoins Are There?</td>\n",
       "      <td>AOL Staff</td>\n",
       "      <td>04-10-2022 21:44</td>\n",
       "      <td>https://www.gobankingrates.com/investing/crypt...</td>\n",
       "      <td>gobankingrates.com</td>\n",
       "      <td>Bitcoin has a maximum supply of 21 million. Ho...</td>\n",
       "      <td>Bitcoin has a maximum supply of 21 million. Ho...</td>\n",
       "      <td>aol.com</td>\n",
       "      <td>5044</td>\n",
       "      <td>news</td>\n",
       "      <td>US</td>\n",
       "      <td>en</td>\n",
       "      <td>AOL Staff,David Granahan</td>\n",
       "      <td>https://s.yimg.com/ny/api/res/1.2/wPK4V8gjwjrD...</td>\n",
       "      <td>@AOL</td>\n",
       "      <td>8.483973</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         article_id  \\\n",
       "0  57a00c1140cbd3af79e77bf0e4e6af48   \n",
       "1  21b48b3731c03466be3fac4be6c7dc67   \n",
       "2  77030740ee160ad68c25e4e63515dd77   \n",
       "\n",
       "                                               title            author  \\\n",
       "0  62% of Bitcoin Has Not Moved in a Year as Long...     Jamie McNeill   \n",
       "1                    The Orange Party Issue Playlist  Bitcoin Magazine   \n",
       "2                       How Many Bitcoins Are There?         AOL Staff   \n",
       "\n",
       "     published_date                                               link  \\\n",
       "0  04-10-2022 17:15  https://www.business2community.com/crypto-news...   \n",
       "1  05-10-2022 21:17  https://bitcoinmagazine.com/culture/orange-par...   \n",
       "2  04-10-2022 21:44  https://www.gobankingrates.com/investing/crypt...   \n",
       "\n",
       "                clean_url                                            excerpt  \\\n",
       "0  business2community.com  Over the course of the last few years, there h...   \n",
       "1     bitcoinmagazine.com  News Links: Russia Legalizing Bitcoin And Cryp...   \n",
       "2      gobankingrates.com  Bitcoin has a maximum supply of 21 million. Ho...   \n",
       "\n",
       "                                             summary                  rights  \\\n",
       "0  Over the course of the last few years, there h...  business2community.com   \n",
       "1  Russia Legalizing Bitcoin And Crypto Is A Matt...     bitcoinmagazine.com   \n",
       "2  Bitcoin has a maximum supply of 21 million. Ho...                 aol.com   \n",
       "\n",
       "   article_rank    topic country language                   authors  \\\n",
       "0          1595  finance      US       en             Jamie McNeill   \n",
       "1          6284     news      US       en          Bitcoin Magazine   \n",
       "2          5044     news      US       en  AOL Staff,David Granahan   \n",
       "\n",
       "                                               media twitter_account  \\\n",
       "0  https://www.business2community.com/wp-content/...     @Jamie_DeFi   \n",
       "1  https://bitcoinmagazine.com/.image/t_share/MTk...             NaN   \n",
       "2  https://s.yimg.com/ny/api/res/1.2/wPK4V8gjwjrD...            @AOL   \n",
       "\n",
       "   article_score  \n",
       "0       8.556426  \n",
       "1       8.507881  \n",
       "2       8.483973  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_raw.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ab099c1",
   "metadata": {},
   "source": [
    "### Generating article summary embeddings\n",
    "After processing the data, we use the previously defined retriever to generate embeddings for article summaries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e4128d5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7d2ae06fa67b496fa8f05f4303773a36",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/1731 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from tqdm.auto import tqdm\n",
    "\n",
    "summary_raw = data_raw['summary'].values.tolist()\n",
    "summary_feature = []\n",
    "\n",
    "for i in tqdm(range(0, len(summary_raw), 1)):\n",
    "    i_end = min(i+1, len(summary_raw))\n",
    "    # generate embeddings for summary\n",
    "    emb = retriever.encode(summary_raw[i:i_end]).tolist()[0]\n",
    "    summary_feature.append(emb)\n",
    "    \n",
    "data_raw['summary_feature'] = summary_feature"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a223ca87",
   "metadata": {},
   "source": [
    "### Creating dataset\n",
    "Finally, we convert the dataframes into csv file and compress it into a zip, and we will upload to s3 for later use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bc4bef48",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data_raw[['article_id', 'title', 'author', 'link', 'summary', 'article_rank', 'summary_feature']]\n",
    "data = data.reset_index().rename(columns={'index': 'id'})\n",
    "data.to_csv('bitcoin_articles_embd.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "02c66064",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "huggingface/tokenizers: The current process just got forked, after parallelism has already been used. Disabling parallelism to avoid deadlocks...\n",
      "To disable this warning, you can either:\n",
      "\t- Avoid using `tokenizers` before the fork if possible\n",
      "\t- Explicitly set the environment variable TOKENIZERS_PARALLELISM=(true | false)\n",
      "  adding: bitcoin_articles_embd.csv (deflated 55%)\n"
     ]
    }
   ],
   "source": [
    "!zip abstractive-qa-examples.zip bitcoin_articles_embd.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ee6651e",
   "metadata": {},
   "source": [
    "## Populate data to MyScale"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d145ed54",
   "metadata": {},
   "source": [
    "### Loading data\n",
    "To populate data to MyScale, first, we download dataset which created in the previous section. The following code snippet shows how to download the data and transform them into panda DataFrames.\n",
    "\n",
    "Note: `summary_feature` is a 384-dimensional floating-point vector that represents the text features extracted from an article summary using the `multi-qa-MiniLM-L6-cos-v1` model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4c589256",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "huggingface/tokenizers: The current process just got forked, after parallelism has already been used. Disabling parallelism to avoid deadlocks...\n",
      "To disable this warning, you can either:\n",
      "\t- Avoid using `tokenizers` before the fork if possible\n",
      "\t- Explicitly set the environment variable TOKENIZERS_PARALLELISM=(true | false)\n",
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100 6941k  100 6941k    0     0  1082k      0  0:00:06  0:00:06 --:--:-- 1647k\n",
      "huggingface/tokenizers: The current process just got forked, after parallelism has already been used. Disabling parallelism to avoid deadlocks...\n",
      "To disable this warning, you can either:\n",
      "\t- Avoid using `tokenizers` before the fork if possible\n",
      "\t- Explicitly set the environment variable TOKENIZERS_PARALLELISM=(true | false)\n",
      "Archive:  abstractive-qa-examples.zip\n",
      "  inflating: bitcoin_articles_embd.csv  \n"
     ]
    }
   ],
   "source": [
    "!wget https://myscale-saas-assets.s3.ap-southeast-1.amazonaws.com/testcases/clickhouse/abstractive-qa-examples.zip\n",
    "\n",
    "!unzip -o abstractive-qa-examples.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "586c8f8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import ast\n",
    "\n",
    "data = pd.read_csv('bitcoin_articles_embd.csv')\n",
    "data['summary_feature'] = data['summary_feature'].apply(ast.literal_eval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2ea06678",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>article_id</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>link</th>\n",
       "      <th>summary</th>\n",
       "      <th>article_rank</th>\n",
       "      <th>summary_feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>57a00c1140cbd3af79e77bf0e4e6af48</td>\n",
       "      <td>62% of Bitcoin Has Not Moved in a Year as Long...</td>\n",
       "      <td>Jamie McNeill</td>\n",
       "      <td>https://www.business2community.com/crypto-news...</td>\n",
       "      <td>Over the course of the last few years, there h...</td>\n",
       "      <td>1595</td>\n",
       "      <td>[0.054771628230810165, -0.05538482591509819, -...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>21b48b3731c03466be3fac4be6c7dc67</td>\n",
       "      <td>The Orange Party Issue Playlist</td>\n",
       "      <td>Bitcoin Magazine</td>\n",
       "      <td>https://bitcoinmagazine.com/culture/orange-par...</td>\n",
       "      <td>Russia Legalizing Bitcoin And Crypto Is A Matt...</td>\n",
       "      <td>6284</td>\n",
       "      <td>[-0.02826531231403351, -0.018267612904310226, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>77030740ee160ad68c25e4e63515dd77</td>\n",
       "      <td>How Many Bitcoins Are There?</td>\n",
       "      <td>AOL Staff</td>\n",
       "      <td>https://www.gobankingrates.com/investing/crypt...</td>\n",
       "      <td>Bitcoin has a maximum supply of 21 million. Ho...</td>\n",
       "      <td>5044</td>\n",
       "      <td>[0.028079882264137268, -0.02520909532904625, 0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                        article_id  \\\n",
       "0   0  57a00c1140cbd3af79e77bf0e4e6af48   \n",
       "1   1  21b48b3731c03466be3fac4be6c7dc67   \n",
       "2   2  77030740ee160ad68c25e4e63515dd77   \n",
       "\n",
       "                                               title            author  \\\n",
       "0  62% of Bitcoin Has Not Moved in a Year as Long...     Jamie McNeill   \n",
       "1                    The Orange Party Issue Playlist  Bitcoin Magazine   \n",
       "2                       How Many Bitcoins Are There?         AOL Staff   \n",
       "\n",
       "                                                link  \\\n",
       "0  https://www.business2community.com/crypto-news...   \n",
       "1  https://bitcoinmagazine.com/culture/orange-par...   \n",
       "2  https://www.gobankingrates.com/investing/crypt...   \n",
       "\n",
       "                                             summary  article_rank  \\\n",
       "0  Over the course of the last few years, there h...          1595   \n",
       "1  Russia Legalizing Bitcoin And Crypto Is A Matt...          6284   \n",
       "2  Bitcoin has a maximum supply of 21 million. Ho...          5044   \n",
       "\n",
       "                                     summary_feature  \n",
       "0  [0.054771628230810165, -0.05538482591509819, -...  \n",
       "1  [-0.02826531231403351, -0.018267612904310226, ...  \n",
       "2  [0.028079882264137268, -0.02520909532904625, 0...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "684c11fc",
   "metadata": {},
   "source": [
    "### Creating table\n",
    "Next, we create tables in MyScale. Before you begin, you will need to retrieve your cluster host, username, and password information from the MyScale console.\n",
    "\n",
    "The following code snippet creates the bitcoin article information table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "62389dbd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import clickhouse_connect\n",
    "\n",
    "client = clickhouse_connect.get_client(\n",
    "    host='YOUR_CLUSTER_HOST',\n",
    "    port=8443,\n",
    "    username='YOUR_USERNAME',\n",
    "    password='YOUR_CLUSTER_PASSWORD'\n",
    ")\n",
    "\n",
    "# create table for bitcoin texts\n",
    "client.command(\"DROP TABLE IF EXISTS default.myscale_llm_bitcoin_qa\")\n",
    "\n",
    "client.command(\"\"\"\n",
    "CREATE TABLE default.myscale_llm_bitcoin_qa\n",
    "(\n",
    "    id UInt64,\n",
    "    article_id String,\n",
    "    title String,\n",
    "    author String,\n",
    "    link String,\n",
    "    summary String,\n",
    "    article_rank UInt64,\n",
    "    summary_feature Array(Float32),\n",
    "    CONSTRAINT vector_len CHECK length(summary_feature) = 384\n",
    ")\n",
    "ORDER BY id\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "926379cb",
   "metadata": {},
   "source": [
    "### Uploading data\n",
    "After creating the table, we insert data loaded from the datasets into tables and create a vector index to accelerate later vector search queries. The following code snippet shows how to insert data into table and create a vector index with cosine distance metric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5e59a5ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "article count: 1731\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# upload data from datasets\n",
    "client.insert(\"default.myscale_llm_bitcoin_qa\", \n",
    "              data.to_records(index=False).tolist(), \n",
    "              column_names=data.columns.tolist())\n",
    "\n",
    "# check count of inserted data\n",
    "print(f\"article count: {client.command('SELECT count(*) FROM default.myscale_llm_bitcoin_qa')}\")\n",
    "\n",
    "# create vector index with cosine\n",
    "client.command(\"\"\"\n",
    "ALTER TABLE default.myscale_llm_bitcoin_qa \n",
    "ADD VECTOR INDEX summary_feature_index summary_feature\n",
    "TYPE MSTG\n",
    "('metric_type=Cosine')\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8242d08d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "index build status: Built\n"
     ]
    }
   ],
   "source": [
    "# check the status of the vector index, make sure vector index is ready with 'Built' status\n",
    "get_index_status=\"SELECT status FROM system.vector_indices WHERE name='summary_feature_index'\"\n",
    "print(f\"index build status: {client.command(get_index_status)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cf86ee3",
   "metadata": {},
   "source": [
    "## Query MyScale"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b119ede5",
   "metadata": {},
   "source": [
    "### Search and filter\n",
    "Use retriever to generate query question embedding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e4edca65",
   "metadata": {},
   "outputs": [],
   "source": [
    "question = 'what is the difference between bitcoin and traditional money?'\n",
    "emb_query = retriever.encode(question).tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e205a23",
   "metadata": {},
   "source": [
    "Then, use vector search to identify the top K candidates that are most similar to the question, filter the result with article_rank < 500."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "24608f8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "top_k = 10\n",
    "results = client.query(f\"\"\"\n",
    "SELECT summary, distance(summary_feature, {emb_query}) as dist\n",
    "FROM default.myscale_llm_bitcoin_qa\n",
    "WHERE article_rank < 500\n",
    "ORDER BY dist ASC\n",
    "LIMIT {top_k}\n",
    "\"\"\")\n",
    "\n",
    "summaries = []\n",
    "for res in results.named_results():\n",
    "    summaries.append(res[\"summary\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "485cbdb3",
   "metadata": {},
   "source": [
    "### Get CoT for GPT-3.5\n",
    "Combine summaries searched from MyScale into a valid prompt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "691bef9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Some even see a digital payment revolution unfolding on the horizon. Despite rising inflation, the interest in crypto is still growing, and adoption continues to expand. One of the industries that are bridging the gap between crypto and ordinary people is retail Forex trading. In the midst of global economic and political uncertainties and disturbances, people increasingly seek out the cryptocurrency market to probe its inner workings, principles and financial potential. Investors use crypto to diversify their portfolios, whereas the mother of all cryptocurrencies—bitcoin—even established itself as a ‘store of value'.Bitcoin prices have stayed relatively stable lately amid contractionary Fed policies. getty\n",
      "Bitcoin prices have continued to trade within a relatively tight range recently, retaining their value even as Federal Reserve policies threaten the values of risk assets. The world's best-known digital currency, which has a total market value of close to $375 billion at the time of this writing, has been trading reasonably close to the $20,000 level since last month, CoinDesk data shows. The cryptocurrency has experienced some price fluctuations lately, but these movements have been modest.: Representations of Bitcoin and pound banknotes - Dado Ruvic/ REUTERS\n",
      "\n",
      "Bitcoin is the 'child of the great quantitative easing' by the likes of the Bank of England, the former Conservative Party Treasurer has claimed.\n",
      "\n",
      "Lord Michael Spence blamed the vast programme of bond buying carried out by central banks for creating a price bubble for cryptocurrencies such as bitcoin, saying the Bank of England 'printed too much money' and caused a 'very rapid growth in the money supply'.\n",
      "\n",
      "Cheap money inflated the cryptocurrency market into the 'modern day equivalent of the Dutch tulip bubble', said Lord Spencer, the founder of trading firm ICAP.Analysts speak to key considerations as we start a new month. getty\n",
      "As the new month begins, investors have been closely watching macroeconomic developments and central bank policy decisions at a time when bitcoin continues to trade within a relatively modest range. The world's most well-known digital currency has been fluctuating between roughly $18,950 and $19,650.00 since the start of October, TradingView figures reveal. Around 3:00 p.m. ET today, it reached the upper end of this range, additional TradingView data shows.Some luxury hotels are now offering a new perk: the ability to pay in cryptocurrencies. From Dubai to the Swiss Alps, several high-end hotels enable guests to swap their credit cards for their digital assets.\n",
      "\n",
      "The Future of Finances: Gen Z & How They Relate to Money\n",
      "\n",
      "Looking To Diversify in a Bear Market? Consider These 6 Alternative Investments\n",
      "\n",
      "The Chedi Andermatt, a 5-star hotel in Andermatt, Switzerland, is one of them.\n",
      "\n",
      "General Manager Jean-Yves Blatt said the hotel, which started offering such payments in August 2021, is currently accepting Bitcoin and ETH, an option that is a continuation of the personalized services it offers its guests.Bitcoin BTC is not just a decentralized peer-to-peer electronic cash system. There's more. It is a new way of thinking about economics, philosophy, politics, human rights, and society.\n",
      "Hungarian sculptors and creators Reka Gergely (L) and Tamas Gilly (R) pose next to the statue of ... [+] Satoshi Nakamoto, the mysterious inventor of the virtual currency bitcoin, after its unveiling at the Graphisoft Park in Budapest, on September 16, 2021. - Hungarian bitcoin enthusiasts unveiled a statue on September 16 in Budapest that they say is the first in the world to honour Satoshi Nakamoto, the mysterious inventor of the virtual currency.The invention of cryptocurrency is attributed to Satoshi Nakamoto , the pseudonym for the creator or group of creators of Bitcoin. The exact identity of Satoshi Nakamoto remains unknown.\n",
      "\n",
      "Cryptocurrency can be stored in online exchanges, such as Coinbase and PayPal , or cryptocurrency owners can store their crypto cash on hardware wallets. Trezor and Ledger are examples of companies that sell these small devices to securely store crypto tokens. These wallets can be 'hot,' meaning users are connected to the Internet and have easier access to their crypto tokens, or 'cold,' meaning that the crypto tokens are encrypted in wallets with private keys whose passwords are not stored on Internet-connected computers.A strong dollar and rising Treasury yields have given bitcoin and gold something in common price-wise: both assets have tumbled this year. Gold GC00, +2.23%, traditionally seen as a safe haven asset, has lost almost 7% year-to-date, according to Dow Jones Market Data. Bitcoin BTCUSD, +1.59% declined almost 60% year-to-date, according to CoinDesk data.  Though some bitcoin supporters have touted the cryptocurrency as a hedge against inflation and as 'digital gold,' the two assets have been largely uncorrelated, with their correlation mostly swinging between negative 0.Bitcoin, Ethereum and other cryptocurrencies, have been described as offering a store of value, but ... [+] that hasn't happened yet in 2022 (Photo illustration by Jakub Porzycki/NurPhoto via Getty Images)NurPhoto via Getty Images\n",
      "In 2022, Bitcoin BTC and Ethereum ETH have both lost around two thirds of their value for the year so far. That's at a time when U.S. inflation is running at around 8% and market risk is elevated. What happened to Bitcoin and Ethereum as a store of value? It's worth noting that this level of volatility is nothing new.A strong dollar and rising Treasury yields have given bitcoin and gold something in common price-wise: both assets have tumbled this year.Gold GC00, +2.30%, traditionally seen as a safe haven asset, has lost almost 7% year-to-date, according to Dow Jones Market Data. Bitcoin BTCUSD, +1.47% declined almost 60% year-to-date, according to CoinDesk data.\n",
      "Based on the context above \n",
      " Q: what is the difference between bitcoin and traditional money?\n",
      " A: The answer is\n"
     ]
    }
   ],
   "source": [
    "CoT = ''\n",
    "for summary in summaries:\n",
    "    CoT += summary\n",
    "CoT += '\\n' +'Based on the context above '+'\\n' +' Q: '+ question + '\\n' +' A: The answer is'\n",
    "print(CoT)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b51768e9",
   "metadata": {},
   "source": [
    "### Get result from GPT-3.5\n",
    "Then, use the generated CoT to query `gpt-3.5-turbo`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "278372ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "response = openai.ChatCompletion.create(\n",
    "    model=\"gpt-3.5-turbo\",\n",
    "    messages=[\n",
    "        {\"role\": \"user\", \"content\": CoT}\n",
    "    ],\n",
    "    temperature=0,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "17e64e76",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Example: Retrieval with MyScale\n",
      "Q:  what is the difference between bitcoin and traditional money?\n",
      "A:  Bitcoin is a decentralized digital currency that operates independently of traditional banking systems and is not backed by any government. It is based on blockchain technology and allows for peer-to-peer transactions without the need for intermediaries. Traditional money, on the other hand, is issued and regulated by central banks and governments, and its value is backed by the trust and stability of those institutions.\n"
     ]
    }
   ],
   "source": [
    "print(\"Example: Retrieval with MyScale\")\n",
    "print('Q: ', question)\n",
    "\n",
    "print('A: ', response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "280648e4",
   "metadata": {},
   "source": [
    "We return a complete and detailed answer. We have recieved great results."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}