Cortex_LLM/Cortex_LLM_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's show how to use Cortex in Python\n",
    "\n",
    "How to get started with Cortex in Python both individual queries and working with tables in Snowflake.\n",
    "\n",
    "Related Docs:\n",
    "https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Snowpark for Python\n",
    "from snowflake.snowpark.session import Session\n",
    "from snowflake.snowpark.types import Variant\n",
    "from snowflake.snowpark.version import VERSION\n",
    "\n",
    "# Snowpark ML\n",
    "# Misc\n",
    "import pandas as pd\n",
    "import json\n",
    "import logging \n",
    "logger = logging.getLogger(\"snowflake.snowpark.session\")\n",
    "logger.setLevel(logging.ERROR)\n",
    "\n",
    "from snowflake import connector\n",
    "from snowflake.ml.utils import connection_params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('../../creds.json') as f:\n",
    "    data = json.load(f)\n",
    "    USERNAME = data['user']\n",
    "    PASSWORD = data['password']\n",
    "    SF_ACCOUNT = data['account']\n",
    "    SF_WH = data['warehouse']\n",
    "\n",
    "CONNECTION_PARAMETERS = {\n",
    "   \"account\": SF_ACCOUNT,\n",
    "   \"user\": USERNAME,\n",
    "   \"password\": PASSWORD,\n",
    "}\n",
    "\n",
    "session = Session.builder.configs(CONNECTION_PARAMETERS).create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User                        : RSHAH\n",
      "Role                        : \"RAJIV\"\n",
      "Database                    : \"RAJIV\"\n",
      "Schema                      : \"PUBLIC\"\n",
      "Warehouse                   : \"RAJIV\"\n",
      "Snowflake version           : 8.9.2\n",
      "Snowpark for Python version : 1.11.1\n",
      "Snowflake ML version        : 1.2.2\n"
     ]
    }
   ],
   "source": [
    "snowflake_environment = session.sql('select current_user(), current_version()').collect()\n",
    "snowpark_version = VERSION\n",
    "\n",
    "from snowflake.ml import version\n",
    "mlversion = version.VERSION\n",
    "\n",
    "\n",
    "# Current Environment Details\n",
    "print('User                        : {}'.format(snowflake_environment[0][0]))\n",
    "print('Role                        : {}'.format(session.get_current_role()))\n",
    "print('Database                    : {}'.format(session.get_current_database()))\n",
    "print('Schema                      : {}'.format(session.get_current_schema()))\n",
    "print('Warehouse                   : {}'.format(session.get_current_warehouse()))\n",
    "print('Snowflake version           : {}'.format(snowflake_environment[0][1]))\n",
    "print('Snowpark for Python version : {}.{}.{}'.format(snowpark_version[0],snowpark_version[1],snowpark_version[2]))\n",
    "print('Snowflake ML version        : {}.{}.{}'.format(mlversion[0],mlversion[2],mlversion[4]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run all the cortex functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Complete() is experimental since 1.0.12. Do not use it in production. \n",
      "ExtractAnswer() is experimental since 1.0.12. Do not use it in production. \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Snowflakes get their unique patterns through a process called crystallization, which occurs when water vapor in the air freezes into ice crystals. As the water vapor freezes, it forms a nucleus, which is a small cluster of water molecules that acts as a center around which the crystal will grow. The nucleus is typically made up of a few hundred water molecules, and it can be formed in various ways, such as through the condensation of water vapor onto a dust particle or the collision of two water molecules.\n",
      "\n",
      "Once the nucleus has formed, it begins to grow as water vapor in the air condenses onto it, forming a crystal lattice structure. The lattice structure is made up of a repeating pattern of water molecules that are arranged in a specific way, with each molecule bonded to its neighbors through hydrogen bonds. The unique pattern of the snowflake is determined by the way the water molecules arrange themselves in the lattice structure, and this is influenced by a number of factors, including the temperature, humidity, and wind conditions in the air.\n",
      "\n",
      "One of the key factors that influences the formation of snowflake patterns is the temperature at which the crystal lattice forms. At warmer temperatures, the water molecules have more energy and are able to move more freely, which allows them to form more complex and intricate patterns. At colder temperatures, the water molecules have less energy and are more rigid, resulting in simpler patterns.\n",
      "\n",
      "Another factor that affects the formation of snowflake patterns is the presence of impurities in the air. Impurities, such as dust particles or pollen, can act as nucleators, which means that they provide a surface for the water vapor to condense onto and form a nucleus. The presence of impurities can also affect the growth of the crystal lattice, leading to the formation of more complex patterns.\n",
      "\n",
      "Finally, the wind conditions in the air can also influence the formation of snowflake patterns. Wind can cause the snowflakes to collide with other particles in the air, which can alter their shape and size. Additionally, wind can cause the snowflakes to fall through different layers of air with different temperatures and humidity levels, which can also affect their formation.\n",
      "\n",
      "Overall, the unique patterns of snowflakes are the result of a complex interplay between temperature, humidity, impurities, and wind conditions in the air. While we can't predict exactly what pattern a snowflake will have, we can understand the factors that influence its formation and appreciate the beauty and uniqueness of each individual snowflake.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Sentiment() is experimental since 1.0.12. Do not use it in production. \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "  {\n",
      "    \"answer\": \"2012\",\n",
      "    \"score\": 0.9998274\n",
      "  }\n",
      "]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Summarize() is experimental since 1.0.12. Do not use it in production. \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.8329001\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Translate() is experimental since 1.0.12. Do not use it in production. \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Snowflake company was founded by Thierry Cruanes, Marcin Zukowski, Bob Muglia, and Benoit Dageville in 2012 and is based in Bozeman, Montana.\n",
      "La société Snowflake a été fondée en 2012 par Thierry Cruanes, Marcin Zukowski, Bob Muglia et Benoit Dageville et a son siège social à Bozeman, au Montana.\n"
     ]
    }
   ],
   "source": [
    "from snowflake.cortex import Complete, ExtractAnswer, Sentiment, Summarize, Translate\n",
    "\n",
    "text = \"\"\"\n",
    "    The Snowflake company was co-founded by Thierry Cruanes, Marcin Zukowski,\n",
    "    Bob Muglia, and Benoit Dageville in 2012 and is headquartered in Bozeman,\n",
    "    Montana.\n",
    "\"\"\"\n",
    "\n",
    "print(Complete(\"llama2-70b-chat\", \"how do snowflakes get their unique patterns?\"))\n",
    "print(ExtractAnswer(text, \"When was snowflake founded?\"))\n",
    "print(Sentiment(\"I really enjoyed this restaurant. Fantastic service!\"))\n",
    "print(Summarize(text))\n",
    "print(Translate(text, \"en\", \"fr\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Snowflakes get their unique patterns through a process called crystallization, which occurs when water vapor in the air freezes into ice crystals. As the water vapor freezes, it forms a nucleus, which is a small cluster of water molecules that acts as a center around which the crystal will grow. The nucleus is typically made up of a few hundred water molecules, and it can be formed in various ways, such as through the condensation of water vapor onto a dust particle or the collision of two water molecules.\n",
      "\n",
      "Once the nucleus has formed, it begins to grow as water vapor in the air condenses onto it, forming a crystal lattice structure. The lattice structure is made up of a repeating pattern of water molecules that are arranged in a specific way, with each molecule bonded to its neighbors through hydrogen bonds. The unique pattern of the snowflake is determined by the way the water molecules arrange themselves in the lattice structure, and this is influenced by a number of factors, including the temperature, humidity, and wind conditions in the air.\n",
      "\n",
      "One of the key factors that influences the formation of snowflake patterns is the temperature at which the crystal lattice forms. At warmer temperatures, the water molecules have more energy and are able to move more freely, which allows them to form more complex and intricate patterns. At colder temperatures, the water molecules have less energy and are more rigid, resulting in simpler patterns.\n",
      "\n",
      "Another factor that affects the formation of snowflake patterns is the presence of impurities in the air. Impurities, such as dust particles or pollen, can act as nucleators, which means that they provide a surface for the water vapor to condense onto and form a nucleus. The presence of impurities can also affect the growth of the crystal lattice, leading to the formation of more complex patterns.\n",
      "\n",
      "Finally, the wind conditions in the air can also influence the formation of snowflake patterns. Wind can cause the snowflakes to collide with other particles in the air, which can alter their shape and size. Additionally, wind can cause the snowflakes to fall through different layers of air with different temperatures and humidity levels, which can also affect their formation.\n",
      "\n",
      "Overall, the unique patterns of snowflakes are the result of a complex interplay between temperature, humidity, impurities, and wind conditions in the air. While we can't predict exactly what pattern a snowflake will have, we can understand the factors that influence its formation and appreciate the beauty and uniqueness of each individual snowflake.\n"
     ]
    }
   ],
   "source": [
    "print(Complete(\"llama2-70b-chat\", \"how do snowflakes get their unique patterns?\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use it on a column in a datset "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the outputs, showing how you can use a snowpark dataframe or bring it back locally in a pandas dataframe.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(TEXT='Great entertainment from start to the end. Wonderful performances by Belushi, Beach, Dalton & Railsback. Some twists and many action scenes. The movie was made for me! Funny lines in the screenplay, good music. Dalton as the tough sheriff and Railsback as \"redneck-villain\". I must recommend this film to every action-adventure fan! 10/10', LABEL=1, TEXTSUMMARY='The text expresses high praise for the movie, featuring great entertainment, wonderful performances by Belushi, Beach, Dalton, and Railsback, and a 10/10 rating.'),\n",
       " Row(TEXT=\"i was hoping this was going to be good as a fan of timothy dalton's james bond and although it wasn't his deserved '3rd bond outing' it was a laugh. Belushi brought some good humour to his part and dalton hammed it up nicely, but was probably underused. his part was liked a camped up version of jack nicholson in a few good men. the other brit in it was a bit shocking, but overal it was a laugh.\", LABEL=1, TEXTSUMMARY='The viewer enjoyed \"Timothy Dalton\\'s James Bond\" movie despite it not being his third outing as expected. Belushi added humor with his performance, while Dalton hammed it up. The other British actor was surprising, but overall, it was a comedic film.'),\n",
       " Row(TEXT='I bought this movie a few days ago, and thought that it would be a pretty shitty film. But when i popped it into the DVD-player, it surprised me in a very good way. James Belushi plays very well as Bill \"The Mouth\" Manuccie. But especially Timothy Dalton plays a very good roll as the Sheriff. The \\'end\\' scene, in the house of Bill is very excellent, good camera-work, nice dialogues and very good acting. Bill \"The Mouth\" Manuccie has stolen 12 Million Dollars from the Mafia. Together with his wife he lives in South-Carolina in a witness protection program. But the Mafia tracks him down, and wants the 12 Million Dollar. Bill can only trust the only person he knows inside out, himself.', LABEL=1, TEXTSUMMARY='The person bought a movie they expected to dislike but found enjoyable. James Belushi and Timothy Dalton gave standout performances. The film\\'s ending, featuring Bill \"The Mouth\" Manucci\\'s theft of 12 Million Dollars from the Mafia and subsequent hiding in a witness protection program, was well-executed with good camera work, dialogues, and acting.'),\n",
       " Row(TEXT=\"This movie surprised me in a good way. From the box I got the impression that it was an action thriller but it was too funny to be a thriller, even though it was somewhat exciting.<br /><br />There's a lot of nice one-liners and funny situations in this movie and James Belushi was born to do Bill Manucci, he does a great job. The rest of the cast ain't half-bad either and especially Timothy Dalton is a treat.<br /><br />The story can get pretty confusing at times as new characters shows up during the film. Things get more complicated as nobody seldom tells the truth about things. If you don't pay attention things might get a bit messy in the end but I really liked it.<br /><br />Louis Morneau isn't all that well known but he has done a perfectly OK job with this one and I never really grew impatient while watching the movie.<br /><br />Made men is well worth checking out.\", LABEL=1, TEXTSUMMARY='The movie \"Made Men\" surprised the viewer with its unexpected comedic elements instead of the anticipated action thriller genre. James Belushi shone in his role, and the cast delivered humorous one-liners and situations. The story was confusing at times due to new characters and deceit, but overall, the viewer enjoyed it. Louis Morneau\\'s direction kept the viewer engaged. The movie is recommended.'),\n",
       " Row(TEXT='What a good film! Made Men is a great action movie with lots of twists and turns. James Belushi is very good as an ex hood who has stolen 12 million from the boss who has to fend of the gangsters , hillbillies his wife and the local sheriff( Timothy Dalton).you wont be disappointed, jump on board and enjoy the ride. 8 out of 10', LABEL=1, TEXTSUMMARY='\"Made Men\" is a great action movie with many twists and turns. James Belushi delivers a strong performance as an ex-hood who stole 12 million and faces challenges from gangsters, hillbillies, his wife, and the local sheriff (Timothy Dalton). The film receives an 8 out of 10 rating.'),\n",
       " Row(TEXT='This movie has everything you want from an action movie. Explosions,shootouts,bad guys and worse guys. It is fun to see James Belushi using his humor to get out of the trouble he has gotten himself in to since he stole 12 million dollars from the ultimate big boss \"The Skipper. Does this sounds cheesy. Of course it is. But boy,did I have fun watching this movie. It is a whole lot better than all the direct to DVD garbage that is made nowadays. If you can get over the silly plot than you will find out that this movie has quite a few surprises in store. You could argue about the twists being predictable. But the fast pace of this movie doesn\\'t give you time too think too much of them,which is a blessing since this movie is not about revealing the ultimate twist. But more about the journey to that moment. Only the title is a bit misleading and that could be the reason why so many people hated this movie. They probably expected a movie about mobsters in stead of some crooks double crossing each other. Pure fun!', LABEL=1, TEXTSUMMARY=\"This movie features explosions, shootouts, and James Belushi's humor as he navigates through a crooked situation involving stolen money. Despite a predictable plot and misleading title, the fast-paced action provides enjoyment.\"),\n",
       " Row(TEXT='This movie surprised me, it had good one-liners and laughs, + a nonstop action-packed storyline with tons of gun action and explosions. This movie surprisingly had a lot of good twists and turns. The plot is solid despite what others may think, it kept my interest the whole time right up till the very end. In conclusion; this is a great way for an action movie buff to spend time on.', LABEL=1, TEXTSUMMARY='This movie had surprising good one-liners, laughs, and a nonstop action-packed storyline with numerous gun action and explosions. The plot was solid and kept the interest throughout.'),\n",
       " Row(TEXT='Saw this in the theater in \\'86 and fell out of my chair laughing more than once. \"Beirut\"...\"What do you know about Beirut?\"...\"Beirut...he\\'s the best damn baseball player who ever lived.\"<br /><br />You know how it\\'s going to end but it has a great time getting there. The training scenes are very funny but the best scene may be the one when Jack and Reno are attempting to watch the Falcons v. Vikings Monday Night Football game while attempting a make-up dinner with their wives.<br /><br />Williams and Russell seem to have a lot of fun with this one and it\\'s too bad that it\\'s overlooked as a top notch comedy.', LABEL=1, TEXTSUMMARY='The text is a review of the movie \"Beirut\" (1986), expressing that the reviewer found it hilarious despite knowing the outcome. The training scenes and the scene with Jack and Reno attempting to watch Monday Night Football while having dinner with their wives were particularly amusing. The reviewers, Williams and Russell, enjoyed the film and felt it was underrated as a comedy.'),\n",
       " Row(TEXT='I guess that everyone has to make a comeback at some point. And that\\'s exactly what embarrassed Taft resident Jack Dundee (Robin Williams) intends to do in \"The Best of Times\". Yep, the man who went all crazy with the radio in \"Good Morning, Vietnam\" is playing football. In this case, he seeks to replay a game that cost his high school a prestigious title. But ex-teammate Reno Hightower (Kurt Russell) isn\\'t just going to go along with it so easily.<br /><br />Granted, it\\'s not the best movie for either man. But Williams and Russell are actually a pretty good comedy team. And some of the names in this movie are likely to give you the giggles (to say the least). Check it out.', LABEL=1, TEXTSUMMARY='Jack Dundee, played by Robin Williams, intends to make a comeback by replaying a high school football game in \"The Best of Times,\" despite resistance from his former teammate Reno Hightower (Kurt Russell). The movie features Williams and Russell as a comedy team, with some amusing character names.'),\n",
       " Row(TEXT='Have you ever in your life, gone out for a sport\\'s activity, tried your best, and then found yourself in an important segment of it, where for a brief moment, you were given a chance to be a hero and a champion and . . . failed? I believe many of us have had that moment in our lives. This is the premise of the movie, \"The Best of Times.\" In this story a middle age banker, named Jack Dundee (Robin Williams) suffers from the deep melancholy of a football mistake, which happened years ago, is inspired to re-play the game . . again. In order to accomplish this he must convince the once great football quarterback, Reno Hightower (Kurt Russell) to make a comeback. For Reno, who is satisfied with his present lot in life, see\\'s no need to change the past record, which get\\'s better as he ages. Added to both their problem is the fact years have passed and in addition, both their marriages are floundering and in need of re-vamping. Not easy when his Father-in-law (Donald Moffat) habitually reminds him of the biggest drop. Nevertheless, Dundee is persistent and will do anything to try and correct the greatest blunder of his life. Great fun for anyone wishing to enjoy their youth again. ***', LABEL=1, TEXTSUMMARY='The movie \"The Best of Times\" is about a middle-aged banker, Jack Dundee, played by Robin Williams, who is haunted by a past football mistake and tries to re-live the game with the help of former quarterback Reno Hightower, played by Kurt Russell. Both men are dealing with personal issues and struggling marriages, making their attempt to change the past record challenging.')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from snowflake.cortex import Summarize, Complete, ExtractAnswer, Sentiment, Summarize, Translate\n",
    "from snowflake.snowpark.functions import col\n",
    "\n",
    "article_df = session.table(\"IMDB_SAMPLE\")\n",
    "article_df = article_df.withColumn(\n",
    "    \"textsummary\",\n",
    "    Summarize(col(\"TEXT\"))\n",
    ")\n",
    "article_df.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>LABEL</th>\n",
       "      <th>TEXT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>The text expresses high praise for the movie, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>The viewer enjoyed \"Timothy Dalton's James Bon...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>The person bought a movie they expected to dis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>The movie \"Made Men\" surprised the viewer with...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>\"Made Men\" is a great action movie with many t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>This movie features explosions, shootouts, and...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>This movie had surprising good one-liners, lau...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>The text is a review of the movie \"Beirut\" (19...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Jack Dundee, played by Robin Williams, intends...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>The movie \"The Best of Times\" is about a middl...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LABEL                                               TEXT\n",
       "0      1  The text expresses high praise for the movie, ...\n",
       "1      1  The viewer enjoyed \"Timothy Dalton's James Bon...\n",
       "2      1  The person bought a movie they expected to dis...\n",
       "3      1  The movie \"Made Men\" surprised the viewer with...\n",
       "4      1  \"Made Men\" is a great action movie with many t...\n",
       "5      1  This movie features explosions, shootouts, and...\n",
       "6      1  This movie had surprising good one-liners, lau...\n",
       "7      1  The text is a review of the movie \"Beirut\" (19...\n",
       "8      1  Jack Dundee, played by Robin Williams, intends...\n",
       "9      1  The movie \"The Best of Times\" is about a middl..."
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=article_df.toPandas()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.snowpark.functions as f\n",
    "\n",
    "article_df = session.table(\"IMDB_SAMPLE\")\n",
    "outdf = article_df.withColumn(\n",
    "    \"extracted\",\n",
    "    Complete(\n",
    "        model='mistral-7b',prompt = f.concat(f.lit(\"Extract the actor and move names from each review: \"),\n",
    "            f.col(\"TEXT\")),\n",
    "            )\n",
    ")\n",
    "\n",
    "output = outdf.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>TEXT</th>\n",
       "      <th>LABEL</th>\n",
       "      <th>EXTRACTED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Great entertainment from start to the end. Won...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actors: Belushi (John Belushi), Beach (Karen ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>i was hoping this was going to be good as a fa...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actors: Timothy Dalton, Dan Aykroyd (Belushi ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I bought this movie a few days ago, and though...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actors: James Belushi (as Bill \"The Mouth\" Ma...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>This movie surprised me in a good way. From th...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actor 1: James Belushi (plays Bill Manucci)\\n...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>What a good film! Made Men is a great action m...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actors: James Belushi, Timothy Dalton\\n\\nMovi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>This movie has everything you want from an act...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actor: James Belushi\\n\\nMovie: (The title is ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>This movie surprised me, it had good one-liner...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actor: N/A (The review does not mention any s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Saw this in the theater in '86 and fell out of...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actor 1: Michael Caine (mentioned in the firs...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>I guess that everyone has to make a comeback a...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actor 1: Robin Williams (playing the role of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Have you ever in your life, gone out for a spo...</td>\n",
       "      <td>1</td>\n",
       "      <td>Actors: Robin Williams (as Jack Dundee), Kurt...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                TEXT  LABEL  \\\n",
       "0  Great entertainment from start to the end. Won...      1   \n",
       "1  i was hoping this was going to be good as a fa...      1   \n",
       "2  I bought this movie a few days ago, and though...      1   \n",
       "3  This movie surprised me in a good way. From th...      1   \n",
       "4  What a good film! Made Men is a great action m...      1   \n",
       "5  This movie has everything you want from an act...      1   \n",
       "6  This movie surprised me, it had good one-liner...      1   \n",
       "7  Saw this in the theater in '86 and fell out of...      1   \n",
       "8  I guess that everyone has to make a comeback a...      1   \n",
       "9  Have you ever in your life, gone out for a spo...      1   \n",
       "\n",
       "                                           EXTRACTED  \n",
       "0   Actors: Belushi (John Belushi), Beach (Karen ...  \n",
       "1   Actors: Timothy Dalton, Dan Aykroyd (Belushi ...  \n",
       "2   Actors: James Belushi (as Bill \"The Mouth\" Ma...  \n",
       "3   Actor 1: James Belushi (plays Bill Manucci)\\n...  \n",
       "4   Actors: James Belushi, Timothy Dalton\\n\\nMovi...  \n",
       "5   Actor: James Belushi\\n\\nMovie: (The title is ...  \n",
       "6   Actor: N/A (The review does not mention any s...  \n",
       "7   Actor 1: Michael Caine (mentioned in the firs...  \n",
       "8   Actor 1: Robin Williams (playing the role of ...  \n",
       "9   Actors: Robin Williams (as Jack Dundee), Kurt...  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting Cortext SQL to Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Simple example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(SNOWFLAKE.CORTEX.COMPLETE('MIXTRAL-8X7B', 'WHAT ARE LARGE LANGUAGE MODELS?')=' Large language models are artificial intelligence models that have been trained on a vast amount of text data to generate human-like text. They are called \"large\" because they typically have a large number of parameters (billions) and require a significant amount of computational resources to train. These models can be used for a variety of natural language processing tasks, such as text generation, translation, summarization, question answering, and more. They work by predicting the likelihood of a word given the previous words in a sentence, allowing them to generate coherent and contextually appropriate text. Some examples of large language models include OpenAI\\'s GPT-3, Google\\'s BERT, and Facebook\\'s RoBERTa.')]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = session.sql(\"\"\"SELECT SNOWFLAKE.CORTEX.COMPLETE('mixtral-8x7b', 'What are large language models?')\"\"\")\n",
    "df.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Messing with the hyperparameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = session.sql(\"\"\"SELECT SNOWFLAKE.CORTEX.COMPLETE(\n",
    "    'llama2-70b-chat',\n",
    "    [\n",
    "        {'role': 'user',\n",
    "         'content': 'how does a snowflake get its unique pattern?'}\n",
    "    ],\n",
    "    {\n",
    "        'temperature': 0.7,\n",
    "        'max_tokens': 30\n",
    "    }\n",
    ");\"\"\")\n",
    "data_string = df.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " The unique pattern on a snowflake is formed by a combination of factors, including the temperature and humidity in the air, the shape\n"
     ]
    }
   ],
   "source": [
    "# Accessing the JSON string\n",
    "json_string = data_string[0][0]\n",
    "\n",
    "# Parse the JSON string\n",
    "try:\n",
    "    json_data = json.loads(json_string)\n",
    "    # Extract the message\n",
    "    message = json_data[\"choices\"][0][\"messages\"] if \"choices\" in json_data and json_data[\"choices\"] else \"Message not found.\"\n",
    "except json.JSONDecodeError:\n",
    "    message = \"Invalid JSON format.\"\n",
    "\n",
    "# Print the extracted message\n",
    "print(message)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's run it across a column in a table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = session.sql(\"\"\"SELECT SNOWFLAKE.CORTEX.COMPLETE(\n",
    "    'mistral-7b',\n",
    "        CONCAT('Critique this review in bullet points: <review>', text, '</review>')\n",
    ") FROM IMDB_SAMPLE LIMIT 10;\"\"\")\n",
    "data_string = df.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(' * The reviewer expresses initial skepticism about the movie but was '\n",
      " 'pleasantly surprised.\\n'\n",
      " '\\n'\n",
      " '* James Belushi\\'s performance as Bill \"The Mouth\" Manucci is commended.\\n'\n",
      " '\\n'\n",
      " \"* Timothy Dalton's performance as the Sheriff is also praised.\\n\"\n",
      " '\\n'\n",
      " \"* The 'end' scene in Bill's house is highlighted as excellent, with good \"\n",
      " 'camera work, nice dialogues, and good acting.\\n'\n",
      " '\\n'\n",
      " '* The plot involves Bill Manucci, who has stolen 12 Million Dollars from the '\n",
      " 'Mafia, living in South-Carolina under witness protection with his wife.\\n'\n",
      " '\\n'\n",
      " '* The Mafia tracks him down and wants the money back, leaving Bill to trust '\n",
      " 'only himself.\\n'\n",
      " '\\n'\n",
      " '* No specific criticisms or negative comments are mentioned in the review.\\n'\n",
      " '\\n'\n",
      " \"* The reviewer's language is informal and conversational, making it easy to \"\n",
      " 'read and understand.\\n'\n",
      " '\\n'\n",
      " '* The review does not provide enough context to determine the title or genre '\n",
      " 'of the movie.\\n'\n",
      " '\\n'\n",
      " '* The reviewer does not mention the quality of the DVD or any technical '\n",
      " 'issues they experienced.\\n'\n",
      " '\\n'\n",
      " '* The review is brief and focuses mainly on the performances and the final '\n",
      " 'scene.')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import ast\n",
    "\n",
    "# Initialize a list to hold parsed data\n",
    "parsed_data = []\n",
    "\n",
    "# Iterate over each Row object\n",
    "for row in data_string:\n",
    "    parsed_data.append(row[0])\n",
    "\n",
    "# Convert the list of dictionaries to a DataFrame\n",
    "df = pd.DataFrame(parsed_data)\n",
    "# Now df is a pandas DataFrame with your data\n",
    "df.columns = ['text']\n",
    "print (df['text'][2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Use the Cortex LLM Rest API: \n",
    "https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-llm-rest-api\n",
    "\n",
    "1. Get a JWT token\n",
    "2. Use the token to make a POST request to the LLM API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import timedelta, timezone, datetime\n",
    "from cryptography.hazmat.primitives.serialization import load_pem_private_key\n",
    "from cryptography.hazmat.primitives.serialization import Encoding\n",
    "from cryptography.hazmat.primitives.serialization import PublicFormat\n",
    "from cryptography.hazmat.backends import default_backend\n",
    "import jwt\n",
    "\n",
    "import base64\n",
    "from getpass import getpass\n",
    "import hashlib\n",
    "\n",
    "# Get the raw bytes of the public key.\n",
    "public_key_raw = private_key_obj.public_key().public_bytes(Encoding.DER, PublicFormat.SubjectPublicKeyInfo)\n",
    "\n",
    "# Get the sha256 hash of the raw bytes.\n",
    "sha256hash = hashlib.sha256()\n",
    "sha256hash.update(public_key_raw)\n",
    "\n",
    "# Base64-encode the value and prepend the prefix 'SHA256:'.\n",
    "public_key_fp = 'SHA256:' + base64.b64encode(sha256hash.digest()).decode('utf-8')\n",
    "\n",
    "\n",
    "# Use uppercase for the account identifier and user name.\n",
    "account = SF_ACCOUNT\n",
    "user = USERNAME\n",
    "account = account.upper()\n",
    "user = user.upper()\n",
    "qualified_username = account + \".\" + user\n",
    "\n",
    "# Get the current time in order to specify the time when the JWT was issued and the expiration time of the JWT.\n",
    "now = datetime.now(timezone.utc)\n",
    "\n",
    "# Specify the length of time during which the JWT will be valid. You can specify at most 1 hour.\n",
    "lifetime = timedelta(minutes=59)\n",
    "\n",
    "# Create the payload for the token.\n",
    "payload = {\n",
    "\n",
    "    # Set the issuer to the fully qualified username concatenated with the public key fingerprint (calculated in the  previous step).\n",
    "    \"iss\": qualified_username + '.' + public_key_fp,\n",
    "\n",
    "    # Set the subject to the fully qualified username.\n",
    "    \"sub\": qualified_username,\n",
    "\n",
    "    # Set the issue time to now.\n",
    "    \"iat\": now,\n",
    "\n",
    "    # Set the expiration time, based on the lifetime specified for this object.\n",
    "    \"exp\": now + lifetime\n",
    "}\n",
    "\n",
    "# Generate the JWT. private_key is the private key that you read from the private key file in the previous step when you generated the public key fingerprint.\n",
    "encoding_algorithm=\"RS256\"\n",
    "token = jwt.encode(payload, key=private_key_obj, algorithm=encoding_algorithm)\n",
    "\n",
    "# If you are using a version of PyJWT prior to 2.0, jwt.encode returns a byte string, rather than a string.\n",
    "# If the token is a byte string, convert it to a string.\n",
    "if isinstance(token, bytes):\n",
    "  token = token.decode('utf-8')\n",
    "decoded_token = jwt.decode(token, key=private_key_obj.public_key(), algorithms=[encoding_algorithm])\n",
    "print(\"Generated a JWT with the following payload:\\n{}\".format(decoded_token))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "\n",
    "# JWT and other values (ensure these are set properly)\n",
    "jwt_token = decoded_token\n",
    "jwt_token = token\n",
    "\n",
    "account = SF_ACCOUNT\n",
    "account = account.upper()\n",
    "account_identifier = account  # Replace with your Snowflake account identifier\n",
    "model_name = \"mistral-large\"\n",
    "prompt = \"eexplain semantic search\"\n",
    "\n",
    "# Endpoint URL\n",
    "url = f\"https://{account_identifier}.snowflakecomputing.com/api/v2/cortex/inference:complete\"\n",
    "\n",
    "# Headers\n",
    "headers = {\n",
    "    'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',\n",
    "    'Authorization': f'Bearer {jwt_token}',\n",
    "    'Content-Type': 'application/json',\n",
    "    'Accept': 'application/json, text/event-stream'\n",
    "}\n",
    "\n",
    "# Data payload (adjust the 'messages' content accordingly)\n",
    "data = {\n",
    "    \"model\": model_name,\n",
    "    \"messages\": [\n",
    "        {\n",
    "            \"content\": prompt\n",
    "        }\n",
    "    ],\n",
    "    \"top_p\": 0,\n",
    "    \"temperature\": 0\n",
    "}\n",
    "\n",
    "# Make the POST request\n",
    "response = requests.post(url, headers=headers, data=json.dumps(data))\n",
    "\n",
    "response.text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Or use the current session\n",
    "\n",
    "from snowflake.cortex import Complete\n",
    "stream = Complete(\n",
    "  \"mistral-7b\",\n",
    "  \"What are unique features of the Snowflake SQL dialect?\",\n",
    "  session=session,\n",
    "  stream=True)\n",
    "\n",
    "for update in stream:\n",
    "  print(update)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "hf_public",
   "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.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}