notebooks/remote_functions/remote_function.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3613b1cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# BigQuery table data on which notebook should be run\n",
    "TABLE='bigquery-public-data.stackoverflow.comments'\n",
    "\n",
    "# Change this up to test the scale, down to run the notebook faster\n",
    "MAX_ROWS=100000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f1175247",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.34 s, sys: 307 ms, total: 2.65 s\n",
      "Wall time: 17.8 s\n"
     ]
    },
    {
     "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>text</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>It will help if you give some details of which...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25</td>\n",
       "      <td>infact it does. Look a the first lines of your...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>\"Currently + is implemented using StringBuffer...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41</td>\n",
       "      <td>I don't think that's the magic number he was r...</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>It's still very useful to know that magic numb...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>96</td>\n",
       "      <td>This implementation is also nice if you wish t...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>108</td>\n",
       "      <td>That's not full text searching, it's searching...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>109</td>\n",
       "      <td>That's not full text searching, it's searching...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>137</td>\n",
       "      <td>In vim you can open &gt; 1 buffer. :e filename. T...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>154</td>\n",
       "      <td>Sure, but what about a solution using O(1) mem...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                               text  score\n",
       "0   10  It will help if you give some details of which...      6\n",
       "1   25  infact it does. Look a the first lines of your...     10\n",
       "2   27  \"Currently + is implemented using StringBuffer...      7\n",
       "3   41  I don't think that's the magic number he was r...     18\n",
       "4   59  It's still very useful to know that magic numb...     12\n",
       "5   96  This implementation is also nice if you wish t...      9\n",
       "6  108  That's not full text searching, it's searching...      6\n",
       "7  109  That's not full text searching, it's searching...      6\n",
       "8  137  In vim you can open > 1 buffer. :e filename. T...      9\n",
       "9  154  Sure, but what about a solution using O(1) mem...      8"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "##############################\n",
    "#       Pandas World         #\n",
    "##############################\n",
    "\n",
    "import pandas as pd\n",
    "df = pd.read_gbq(TABLE, max_results=MAX_ROWS)[['id', 'text', 'score']]\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fd8a04a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# User defined function\n",
    "# https://www.codespeedy.com/find-nth-prime-number-in-python/\n",
    "def nth_prime(n):\n",
    "    prime_numbers = [2,3]\n",
    "    i=3\n",
    "    if(0<n<=2):\n",
    "        return prime_numbers[n-1]\n",
    "    elif(n>2):\n",
    "        while (True):\n",
    "            i+=1\n",
    "            status = True\n",
    "            for j in range(2,int(i/2)+1):\n",
    "                if(i%j==0):\n",
    "                    status = False\n",
    "                    break\n",
    "            if(status==True):\n",
    "                prime_numbers.append(i)\n",
    "            if(len(prime_numbers)==n):\n",
    "                break\n",
    "        return prime_numbers[n-1]\n",
    "    else:\n",
    "        return -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2b5e4568",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.32 s, sys: 0 ns, total: 3.32 s\n",
      "Wall time: 3.32 s\n"
     ]
    },
    {
     "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>text</th>\n",
       "      <th>score</th>\n",
       "      <th>n_prime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>It will help if you give some details of which...</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25</td>\n",
       "      <td>infact it does. Look a the first lines of your...</td>\n",
       "      <td>10</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>\"Currently + is implemented using StringBuffer...</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41</td>\n",
       "      <td>I don't think that's the magic number he was r...</td>\n",
       "      <td>18</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>It's still very useful to know that magic numb...</td>\n",
       "      <td>12</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>96</td>\n",
       "      <td>This implementation is also nice if you wish t...</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>108</td>\n",
       "      <td>That's not full text searching, it's searching...</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>109</td>\n",
       "      <td>That's not full text searching, it's searching...</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>137</td>\n",
       "      <td>In vim you can open &gt; 1 buffer. :e filename. T...</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>154</td>\n",
       "      <td>Sure, but what about a solution using O(1) mem...</td>\n",
       "      <td>8</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                               text  score  n_prime\n",
       "0   10  It will help if you give some details of which...      6       13\n",
       "1   25  infact it does. Look a the first lines of your...     10       29\n",
       "2   27  \"Currently + is implemented using StringBuffer...      7       17\n",
       "3   41  I don't think that's the magic number he was r...     18       61\n",
       "4   59  It's still very useful to know that magic numb...     12       37\n",
       "5   96  This implementation is also nice if you wish t...      9       23\n",
       "6  108  That's not full text searching, it's searching...      6       13\n",
       "7  109  That's not full text searching, it's searching...      6       13\n",
       "8  137  In vim you can open > 1 buffer. :e filename. T...      9       23\n",
       "9  154  Sure, but what about a solution using O(1) mem...      8       19"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df = df.assign(n_prime=df['score'].apply(nth_prime))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b81feaef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 71.2 ms, sys: 0 ns, total: 71.2 ms\n",
      "Wall time: 1.99 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Query job ca4f5d64-fcb3-4388-b9f1-924c55c1aaa5 is DONE. 17.2 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=shobs-test&j=bq:US:ca4f5d64-fcb3-4388-b9f1-924c55c1aaa5&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Query job a283cb39-41b1-44cd-a6c3-f2a2c6a55b25 is DONE. 17.2 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=shobs-test&j=bq:US:a283cb39-41b1-44cd-a6c3-f2a2c6a55b25&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>text</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11231597</td>\n",
       "      <td>In your update, why are some of the system fun...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49684807</td>\n",
       "      <td>what you have tried so far .  ??</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7623925</td>\n",
       "      <td>@Michael: It should work. Perhaps you looked i...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34046685</td>\n",
       "      <td>Will it work with SQL compact? Please excuse m...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6426146</td>\n",
       "      <td>do you know the equation to your pdf?</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>60686114</td>\n",
       "      <td>m sorry but at least you have to think about it.</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16631986</td>\n",
       "      <td>i think also making disable this by only jquer...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16498565</td>\n",
       "      <td>I am including these files on my header of the...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>26601001</td>\n",
       "      <td>wrong answer, you didn't understand the logic</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>73255842</td>\n",
       "      <td>Call the setOnClickListener before return row.</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 3 columns</p>\n",
       "</div>[10 rows x 3 columns in total]"
      ],
      "text/plain": [
       "         id                                               text  score\n",
       "0  11231597  In your update, why are some of the system fun...      0\n",
       "1  49684807                   what you have tried so far .  ??      1\n",
       "2   7623925  @Michael: It should work. Perhaps you looked i...      0\n",
       "3  34046685  Will it work with SQL compact? Please excuse m...      0\n",
       "4   6426146              do you know the equation to your pdf?      0\n",
       "5  60686114   m sorry but at least you have to think about it.      0\n",
       "6  16631986  i think also making disable this by only jquer...      0\n",
       "7  16498565  I am including these files on my header of the...      0\n",
       "8  26601001      wrong answer, you didn't understand the logic      0\n",
       "9  73255842     Call the setOnClickListener before return row.      0\n",
       "\n",
       "[10 rows x 3 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "##############################\n",
    "#     BigQuery DataFrames     #\n",
    "##############################\n",
    "\n",
    "import bigframes.pandas as pd\n",
    "\n",
    "df = pd.read_gbq(TABLE).head(MAX_ROWS)[['id', 'text', 'score']]\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "55ed241e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function remote_function in module bigframes.pandas:\n",
      "\n",
      "remote_function(input_types: 'List[type]', output_type: 'type', dataset: 'Optional[str]' = None, bigquery_connection: 'Optional[str]' = None, reuse: 'bool' = True, name: 'Optional[str]' = None, packages: 'Optional[Sequence[str]]' = None)\n",
      "    Decorator to turn a user defined function into a BigQuery remote function. Check out\n",
      "    the code samples at: https://cloud.google.com/bigquery/docs/remote-functions#bigquery-dataframes.\n",
      "    \n",
      "    .. note::\n",
      "        Please make sure following is setup before using this API:\n",
      "    \n",
      "    1. Have the below APIs enabled for your project:\n",
      "    \n",
      "        * BigQuery Connection API\n",
      "        * Cloud Functions API\n",
      "        * Cloud Run API\n",
      "        * Cloud Build API\n",
      "        * Artifact Registry API\n",
      "        * Cloud Resource Manager API\n",
      "    \n",
      "       This can be done from the cloud console (change `PROJECT_ID` to yours):\n",
      "       https://console.cloud.google.com/apis/enableflow?apiid=bigqueryconnection.googleapis.com,cloudfunctions.googleapis.com,run.googleapis.com,cloudbuild.googleapis.com,artifactregistry.googleapis.com,cloudresourcemanager.googleapis.com&project=PROJECT_ID\n",
      "    \n",
      "       Or from the gcloud CLI:\n",
      "    \n",
      "       `$ gcloud services enable bigqueryconnection.googleapis.com cloudfunctions.googleapis.com run.googleapis.com cloudbuild.googleapis.com artifactregistry.googleapis.com cloudresourcemanager.googleapis.com`\n",
      "    \n",
      "    2. Have following IAM roles enabled for you:\n",
      "    \n",
      "        * BigQuery Data Editor (roles/bigquery.dataEditor)\n",
      "        * BigQuery Connection Admin (roles/bigquery.connectionAdmin)\n",
      "        * Cloud Functions Developer (roles/cloudfunctions.developer)\n",
      "        * Service Account User (roles/iam.serviceAccountUser) on the service account `PROJECT_NUMBER-compute@developer.gserviceaccount.com`\n",
      "        * Storage Object Viewer (roles/storage.objectViewer)\n",
      "        * Project IAM Admin (roles/resourcemanager.projectIamAdmin) (Only required if the bigquery connection being used is not pre-created and is created dynamically with user credentials.)\n",
      "    \n",
      "    3. Either the user has setIamPolicy privilege on the project, or a BigQuery connection is pre-created with necessary IAM role set:\n",
      "    \n",
      "        1. To create a connection, follow https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions#create_a_connection\n",
      "        2. To set up IAM, follow https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions#grant_permission_on_function\n",
      "    \n",
      "           Alternatively, the IAM could also be setup via the gcloud CLI:\n",
      "    \n",
      "           `$ gcloud projects add-iam-policy-binding PROJECT_ID --member=\"serviceAccount:CONNECTION_SERVICE_ACCOUNT_ID\" --role=\"roles/run.invoker\"`.\n",
      "    \n",
      "    Args:\n",
      "        input_types (list(type)):\n",
      "            List of input data types in the user defined function.\n",
      "        output_type (type):\n",
      "            Data type of the output in the user defined function.\n",
      "        dataset (str, Optional):\n",
      "            Dataset in which to create a BigQuery remote function. It should be in\n",
      "            `<project_id>.<dataset_name>` or `<dataset_name>` format. If this\n",
      "            parameter is not provided then session dataset id is used.\n",
      "        bigquery_connection (str, Optional):\n",
      "            Name of the BigQuery connection. You should either have the\n",
      "            connection already created in the `location` you have chosen, or\n",
      "            you should have the Project IAM Admin role to enable the service\n",
      "            to create the connection for you if you need it. If this parameter is\n",
      "            not provided then the BigQuery connection from the session is used.\n",
      "        reuse (bool, Optional):\n",
      "            Reuse the remote function if already exists.\n",
      "            `True` by default, which will result in reusing an existing remote\n",
      "            function and corresponding cloud function (if any) that was\n",
      "            previously created for the same udf.\n",
      "            Setting it to `False` would force creating a unique remote function.\n",
      "            If the required remote function does not exist then it would be\n",
      "            created irrespective of this param.\n",
      "        name (str, Optional):\n",
      "            Explicit name of the persisted BigQuery remote function. Use it with\n",
      "            caution, because two users working in the same project and dataset\n",
      "            could overwrite each other's remote functions if they use the same\n",
      "            persistent name.\n",
      "        packages (str[], Optional):\n",
      "            Explicit name of the external package dependencies. Each dependency\n",
      "            is added to the `requirements.txt` as is, and can be of the form\n",
      "            supported in https://pip.pypa.io/en/stable/reference/requirements-file-format/.\n",
      "    Returns:\n",
      "        callable: A remote function object pointing to the cloud assets created\n",
      "        in the background to support the remote execution. The cloud assets can be\n",
      "        located through the following properties set in the object:\n",
      "    \n",
      "        `bigframes_cloud_function` - The google cloud function deployed for the user defined code.\n",
      "    \n",
      "        `bigframes_remote_function` - The bigquery remote function capable of calling into `bigframes_cloud_function`.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Tell the user what needs to be done offline before using BigQuery DataFrame\n",
    "# remote functions\n",
    "help(pd.remote_function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "fbc27f81",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Wall time: 76.2628 s\n"
     ]
    }
   ],
   "source": [
    "from time import perf_counter\n",
    "start = perf_counter()\n",
    "#-------------------------------------------------------------------------------------\n",
    "\n",
    "# User defined function\n",
    "# https://www.codespeedy.com/find-nth-prime-number-in-python/\n",
    "@pd.remote_function([int], int, reuse=False)\n",
    "def nth_prime(n):\n",
    "    prime_numbers = [2,3]\n",
    "    i=3\n",
    "    if(0<n<=2):\n",
    "        return prime_numbers[n-1]\n",
    "    elif(n>2):\n",
    "        while (True):\n",
    "            i+=1\n",
    "            status = True\n",
    "            for j in range(2,int(i/2)+1):\n",
    "                if(i%j==0):\n",
    "                    status = False\n",
    "                    break\n",
    "            if(status==True):\n",
    "                prime_numbers.append(i)\n",
    "            if(len(prime_numbers)==n):\n",
    "                break\n",
    "        return prime_numbers[n-1]\n",
    "    else:\n",
    "        return -1\n",
    "\n",
    "#-------------------------------------------------------------------------------------\n",
    "print(f\"\\nWall time: {(perf_counter()-start):.4f} s\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "c1c9355f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 55.8 ms, sys: 182 µs, total: 56 ms\n",
      "Wall time: 54.5 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Query job 762aa155-a3ed-4bab-aca2-a0380ed96ebf is DONE. 17.2 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=shobs-test&j=bq:US:762aa155-a3ed-4bab-aca2-a0380ed96ebf&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Query job c0a2c187-364d-4978-97bc-30352828f624 is DONE. 17.2 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=shobs-test&j=bq:US:c0a2c187-364d-4978-97bc-30352828f624&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>text</th>\n",
       "      <th>score</th>\n",
       "      <th>n_prime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11231597</td>\n",
       "      <td>In your update, why are some of the system fun...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49684807</td>\n",
       "      <td>what you have tried so far .  ??</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7623925</td>\n",
       "      <td>@Michael: It should work. Perhaps you looked i...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34046685</td>\n",
       "      <td>Will it work with SQL compact? Please excuse m...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6426146</td>\n",
       "      <td>do you know the equation to your pdf?</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>60686114</td>\n",
       "      <td>m sorry but at least you have to think about it.</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16631986</td>\n",
       "      <td>i think also making disable this by only jquer...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16498565</td>\n",
       "      <td>I am including these files on my header of the...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>26601001</td>\n",
       "      <td>wrong answer, you didn't understand the logic</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>73255842</td>\n",
       "      <td>Call the setOnClickListener before return row.</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 4 columns</p>\n",
       "</div>[10 rows x 4 columns in total]"
      ],
      "text/plain": [
       "         id                                               text  score  n_prime\n",
       "0  11231597  In your update, why are some of the system fun...      0       -1\n",
       "1  49684807                   what you have tried so far .  ??      1        2\n",
       "2   7623925  @Michael: It should work. Perhaps you looked i...      0       -1\n",
       "3  34046685  Will it work with SQL compact? Please excuse m...      0       -1\n",
       "4   6426146              do you know the equation to your pdf?      0       -1\n",
       "5  60686114   m sorry but at least you have to think about it.      0       -1\n",
       "6  16631986  i think also making disable this by only jquer...      0       -1\n",
       "7  16498565  I am including these files on my header of the...      0       -1\n",
       "8  26601001      wrong answer, you didn't understand the logic      0       -1\n",
       "9  73255842     Call the setOnClickListener before return row.      0       -1\n",
       "\n",
       "[10 rows x 4 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Let's apply the function to the dataframe\n",
    "df = df.assign(n_prime=df['score'].apply(nth_prime))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2701cb81",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shobs-test.bigframes_temp_us.bigframes_343b7b4bb93ca8747dae20c22bdaec8b_p27heyce\n",
      "projects/shobs-test/locations/us-central1/functions/bigframes-343b7b4bb93ca8747dae20c22bdaec8b-p27heyce\n"
     ]
    }
   ],
   "source": [
    "# We can see the path to the BQ remote function and the google cloud function\n",
    "# that was created under the hood\n",
    "print(nth_prime.bigframes_remote_function)\n",
    "print(nth_prime.bigframes_cloud_function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "920fa18e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function read_gbq_function in module bigframes.pandas:\n",
      "\n",
      "read_gbq_function(function_name: 'str')\n",
      "    Loads a BigQuery function from BigQuery.\n",
      "    \n",
      "    Then it can be applied to a DataFrame or Series.\n",
      "    \n",
      "    .. note::\n",
      "        The return type of the function must be explicitly specified in the\n",
      "        function's original definition even if not otherwise required.\n",
      "    \n",
      "    BigQuery Utils provides many public functions under the ``bqutil`` project on Google Cloud Platform project\n",
      "    (See: https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs#using-the-udfs).\n",
      "    You can checkout Community UDFs to use community-contributed functions.\n",
      "    (See: https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs/community#community-udfs).\n",
      "    \n",
      "    **Examples:**\n",
      "    \n",
      "    Use the ``cw_lower_case_ascii_only`` function from Community UDFs.\n",
      "    (https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/cw_lower_case_ascii_only.sqlx)\n",
      "    \n",
      "        >>> import bigframes.pandas as bpd\n",
      "        >>> bpd.options.display.progress_bar = None\n",
      "    \n",
      "        >>> df = bpd.DataFrame({'id': [1, 2, 3], 'name': ['AURÉLIE', 'CÉLESTINE', 'DAPHNÉ']})\n",
      "        >>> df\n",
      "           id       name\n",
      "        0   1    AURÉLIE\n",
      "        1   2  CÉLESTINE\n",
      "        2   3     DAPHNÉ\n",
      "        <BLANKLINE>\n",
      "        [3 rows x 2 columns]\n",
      "    \n",
      "        >>> func = bpd.read_gbq_function(\"bqutil.fn.cw_lower_case_ascii_only\")\n",
      "        >>> df1 = df.assign(new_name=df['name'].apply(func))\n",
      "        >>> df1\n",
      "           id       name   new_name\n",
      "        0   1    AURÉLIE    aurÉlie\n",
      "        1   2  CÉLESTINE  cÉlestine\n",
      "        2   3     DAPHNÉ     daphnÉ\n",
      "        <BLANKLINE>\n",
      "        [3 rows x 3 columns]\n",
      "    \n",
      "    Args:\n",
      "        function_name (str):\n",
      "            the function's name in BigQuery in the format\n",
      "            `project_id.dataset_id.function_name`, or\n",
      "            `dataset_id.function_name` to load from the default project, or\n",
      "            `function_name` to load from the default project and the dataset\n",
      "            associated with the current session.\n",
      "    \n",
      "    Returns:\n",
      "        callable: A function object pointing to the BigQuery function read\n",
      "        from BigQuery.\n",
      "    \n",
      "        The object is similar to the one created by the `remote_function`\n",
      "        decorator, including the `bigframes_remote_function` property, but\n",
      "        not including the `bigframes_cloud_function` property.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Let's try to simulate a scenario in which user shares this remote funciton to\n",
    "# their colleague who simply wants to reuse it. BigFrames provides an API to do\n",
    "# so via `read_gbq_function`. Usage details are available via `help` command.\n",
    "help(pd.read_gbq_function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "a6c9da0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "EXISTING_REMOTE_FUNCTION=nth_prime.bigframes_remote_function\n",
    "\n",
    "# Let's read the existing remote function in bigframes\n",
    "nth_prime_existing = pd.read_gbq_function(EXISTING_REMOTE_FUNCTION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d7e7de7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 70.8 ms, sys: 3.49 ms, total: 74.3 ms\n",
      "Wall time: 75.2 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "Query job f9a0e979-aeac-4ddd-a4c9-6720a5e91009 is DONE. 17.2 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=shobs-test&j=bq:US:f9a0e979-aeac-4ddd-a4c9-6720a5e91009&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Query job 4d3da7ed-42e6-4b2b-b656-ac9ef6d2e871 is DONE. 17.2 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=shobs-test&j=bq:US:4d3da7ed-42e6-4b2b-b656-ac9ef6d2e871&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>text</th>\n",
       "      <th>score</th>\n",
       "      <th>n_prime</th>\n",
       "      <th>n_prime_again</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11231597</td>\n",
       "      <td>In your update, why are some of the system fun...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49684807</td>\n",
       "      <td>what you have tried so far .  ??</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7623925</td>\n",
       "      <td>@Michael: It should work. Perhaps you looked i...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34046685</td>\n",
       "      <td>Will it work with SQL compact? Please excuse m...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6426146</td>\n",
       "      <td>do you know the equation to your pdf?</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>60686114</td>\n",
       "      <td>m sorry but at least you have to think about it.</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16631986</td>\n",
       "      <td>i think also making disable this by only jquer...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16498565</td>\n",
       "      <td>I am including these files on my header of the...</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>26601001</td>\n",
       "      <td>wrong answer, you didn't understand the logic</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>73255842</td>\n",
       "      <td>Call the setOnClickListener before return row.</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 5 columns</p>\n",
       "</div>[10 rows x 5 columns in total]"
      ],
      "text/plain": [
       "         id                                               text  score  \\\n",
       "0  11231597  In your update, why are some of the system fun...      0   \n",
       "1  49684807                   what you have tried so far .  ??      1   \n",
       "2   7623925  @Michael: It should work. Perhaps you looked i...      0   \n",
       "3  34046685  Will it work with SQL compact? Please excuse m...      0   \n",
       "4   6426146              do you know the equation to your pdf?      0   \n",
       "5  60686114   m sorry but at least you have to think about it.      0   \n",
       "6  16631986  i think also making disable this by only jquer...      0   \n",
       "7  16498565  I am including these files on my header of the...      0   \n",
       "8  26601001      wrong answer, you didn't understand the logic      0   \n",
       "9  73255842     Call the setOnClickListener before return row.      0   \n",
       "\n",
       "   n_prime  n_prime_again  \n",
       "0       -1             -1  \n",
       "1        2              2  \n",
       "2       -1             -1  \n",
       "3       -1             -1  \n",
       "4       -1             -1  \n",
       "5       -1             -1  \n",
       "6       -1             -1  \n",
       "7       -1             -1  \n",
       "8       -1             -1  \n",
       "9       -1             -1  \n",
       "\n",
       "[10 rows x 5 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Let's apply the existing function to the dataframe\n",
    "df = df.assign(n_prime_again=df['score'].apply(nth_prime_existing))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bafab950",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean up GCP assets created as part of bigframes remote_function\n",
    "def cleanup_remote_function_assets(remote_udf, ignore_failures=False):\n",
    "    \"\"\"Clean up the GCP assets behind a bigframes remote function.\"\"\"\n",
    "\n",
    "    session = pd.get_global_session()\n",
    "\n",
    "    # Clean up BQ remote function\n",
    "    try:\n",
    "        session.bqclient.delete_routine(remote_udf.bigframes_remote_function)\n",
    "    except Exception:\n",
    "        # By default don't raise exception in cleanup\n",
    "        if not ignore_failures:\n",
    "            raise\n",
    "\n",
    "    # Clean up cloud function\n",
    "    try:\n",
    "        session.cloudfunctionsclient.delete_function(name=remote_udf.bigframes_cloud_function)\n",
    "    except Exception:\n",
    "        # By default don't raise exception in cleanup\n",
    "        if not ignore_failures:\n",
    "            raise\n",
    "\n",
    "cleanup_remote_function_assets(nth_prime)"
   ]
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}