language/orchestration/langchain/langchain_bigquery_data_loader.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_1uzXWPtI1b_"
      },
      "outputs": [],
      "source": [
        "# Copyright 2023 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "359697d5"
      },
      "source": [
        "# How to use the LangChain 🦜️🔗 BigQuery Data Loader\n",
        "\n",
        "> **NOTE:** This notebook uses the PaLM generative model, which will reach its [discontinuation date in October 2024](https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/text#model_versions). \n",
        "\n",
        "<table align=\"left\">\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://www.gstatic.com/pantheon/images/bigquery/welcome_page/colab-logo.svg\" alt=\"Google Colaboratory logo\"><br> Open in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/colab/import/https:%2F%2Fraw.githubusercontent.com%2FGoogleCloudPlatform%2Fgenerative-ai%2Fmain%2Flanguage%2Forchestration%2Flangchain%2Flangchain_bigquery_data_loader.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://lh3.googleusercontent.com/JmcxdQi-qOpctIvWKgPtrzZdJJK-J3sWE1RsfjZNwshCFgE_9fULcNpuXYTilIR2hjwN\" alt=\"Google Cloud Colab Enterprise logo\"><br> Open in Colab Enterprise\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/GoogleCloudPlatform/generative-ai/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\">\n",
        "      <img src=\"https://www.gstatic.com/images/branding/gcpiconscolors/vertexai/v1/32px.svg\" alt=\"Vertex AI logo\"><br> Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/bigquery/import?url=https://github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\">\n",
        "      <img src=\"https://www.gstatic.com/images/branding/gcpiconscolors/bigquery/v1/32px.svg\" alt=\"BigQuery Studio logo\"><br> Open in BigQuery Studio\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://upload.wikimedia.org/wikipedia/commons/9/91/Octicons-mark-github.svg\" alt=\"GitHub logo\"><br> View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "</table>\n",
        "\n",
        "<div style=\"clear: both;\"></div>\n",
        "\n",
        "<b>Share to:</b>\n",
        "\n",
        "<a href=\"https://www.linkedin.com/sharing/share-offsite/?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/8/81/LinkedIn_icon.svg\" alt=\"LinkedIn logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://bsky.app/intent/compose?text=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/7/7a/Bluesky_Logo.svg\" alt=\"Bluesky logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://twitter.com/intent/tweet?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/53/X_logo_2023_original.svg\" alt=\"X logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://reddit.com/submit?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://redditinc.com/hubfs/Reddit%20Inc/Brand/Reddit_Logo.png\" alt=\"Reddit logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://www.facebook.com/sharer/sharer.php?u=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/language/orchestration/langchain/langchain_bigquery_data_loader.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/51/Facebook_f_logo_%282019%29.svg\" alt=\"Facebook logo\">\n",
        "</a>            "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8ce85c71ef92"
      },
      "source": [
        "| | |\n",
        "|-|-|\n",
        "|Author(s) | [Karl Weinmeister](https://github.com/kweinmeister) |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "50b19d12"
      },
      "source": [
        "## Objectives\n",
        "\n",
        "This notebook provides an introductory understanding of how to use [LangChain](https://langchain.com/) and LangChain's [BigQuery Data Loader](https://python.langchain.com/docs/integrations/document_loaders/google_bigquery). The notebook covers 3 steps:\n",
        "\n",
        "1. Querying the Vertex AI LLM with LangChain\n",
        "1. Using the LangChain BigQuery Data Loader\n",
        "1. Developing a chain that uses the data loader"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e985f332"
      },
      "source": [
        "### Costs\n",
        "\n",
        "This tutorial uses billable components of Google Cloud:\n",
        "\n",
        "- Vertex AI\n",
        "- BigQuery\n",
        "\n",
        "Learn about [Vertex AI pricing](https://cloud.google.com/vertex-ai/pricing), [BigQuery pricing](https://cloud.google.com/bigquery/pricing),\n",
        "and use the [Pricing Calculator](https://cloud.google.com/products/calculator/)\n",
        "to generate a cost estimate based on your projected usage.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ohPUPez8imvE"
      },
      "outputs": [],
      "source": [
        "# Install Vertex AI LLM SDK, BigQuery library, and langchain\n",
        "%pip install google-cloud-aiplatform google-cloud-bigquery langchain==0.0.332 --upgrade --user"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d3fd2805"
      },
      "source": [
        "**Colab only:** Uncomment the following cell to restart the kernel or use the button to restart the kernel. For Vertex AI Workbench you can restart the terminal using the button on top.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "c49ae51e03fe"
      },
      "outputs": [],
      "source": [
        "# # Automatically restart kernel after installs so that your environment can access the new packages\n",
        "# import IPython\n",
        "\n",
        "# app = IPython.Application.instance()\n",
        "# app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "034fe628"
      },
      "source": [
        "### Authenticating your notebook environment\n",
        "\n",
        "- If you are using **Colab** to run this notebook, uncomment the cell below and continue.\n",
        "- If you are using **Vertex AI Workbench**, check out the setup instructions [here](https://github.com/GoogleCloudPlatform/generative-ai/tree/main/setup-env).\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6d83f50a3d3f"
      },
      "outputs": [],
      "source": [
        "# from google.colab import auth as google_auth\n",
        "# google_auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a92ac8ea"
      },
      "source": [
        "### Import libraries\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "89d2e5b70afd"
      },
      "source": [
        "**Colab only:** Uncomment the following cell to initialize the Vertex AI SDK. For Vertex AI Workbench, you don't need to run this.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "e62a5503"
      },
      "outputs": [],
      "source": [
        "# import vertexai\n",
        "\n",
        "# PROJECT_ID = \"[your-project-id]\"  # @param {type:\"string\"}\n",
        "# vertexai.init(project=PROJECT_ID, location=\"us-central1\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Smo-7TpE4B22"
      },
      "outputs": [],
      "source": [
        "from google.cloud import aiplatform\n",
        "import google.cloud.bigquery as bq\n",
        "import langchain\n",
        "from langchain.document_loaders import BigQueryLoader\n",
        "from langchain.llms import VertexAI\n",
        "from langchain.prompts import PromptTemplate\n",
        "from langchain.schema import format_document\n",
        "\n",
        "# Print LangChain and Vertex AI versions\n",
        "print(f\"LangChain version: {langchain.__version__}\")\n",
        "print(f\"Vertex AI SDK version: {aiplatform.__version__}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7ed789fe"
      },
      "source": [
        "## Using Vertex AI foundation models with LangChain"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3dd3a375d3b6"
      },
      "source": [
        "Let's start from the beginning, and learn a bit about BigQuery along the way. We'll define a LangChain LLM model. We'll use the [text foundation model](https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/text) and use a `temperature` setting of 0 for consistent results."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "eVpPcvsrkzCk"
      },
      "outputs": [],
      "source": [
        "llm = VertexAI(model_name=\"text-bison\", temperature=0)\n",
        "\n",
        "llm(\"What's BigQuery?\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "05bb564d"
      },
      "source": [
        "## Using the Data Loader\n",
        "\n",
        "Let's now learn how to use the document loader. We'll use data from a fictional eCommerce clothing site called [TheLook](https://console.cloud.google.com/marketplace/product/bigquery-public-data/thelook-ecommerce), available as a BigQuery public dataset.\n",
        "\n",
        "Our first goal is to understand the tables in a dataset. Let's query the schema from this dataset to extract the data definition language (DDL). DDL is used to create and modify tables, and can tell us about each column and its type.\n",
        "\n",
        "Our query is extracting the table name and DDL for each of the tables. We then create a [data loader](https://api.python.langchain.com/en/latest/document_loaders/langchain.document_loaders.bigquery.BigQueryLoader.html), specifying that the table name is a metadata column and the DDL is the content. \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4ddfee90d6a1"
      },
      "outputs": [],
      "source": [
        "# Define our query\n",
        "query = \"\"\"\n",
        "SELECT table_name, ddl\n",
        "FROM `bigquery-public-data.thelook_ecommerce.INFORMATION_SCHEMA.TABLES`\n",
        "WHERE table_type = 'BASE TABLE'\n",
        "ORDER BY table_name;\n",
        "\"\"\"\n",
        "\n",
        "# Load the data\n",
        "loader = BigQueryLoader(\n",
        "    query, metadata_columns=[\"table_name\"], page_content_columns=[\"ddl\"]\n",
        ")\n",
        "data = loader.load()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EZfIhJSMjDLV"
      },
      "source": [
        "## Writing our first chain\n",
        "\n",
        "Now that we've loaded the documents, let's put them to work.\n",
        "\n",
        "Our goal is to understand which customers we want to target for an upcoming marketing campaign in Japan. We'll use the [code generation model](https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/code-generation) to help us create a query.\n",
        "\n",
        "We will create a basic chain that \"[stuffs](https://python.langchain.com/docs/use_cases/summarization#option-1-stuff)\" together all of the table metadata into one prompt. For larger datasets with many more tables, a more sophisticated chaining approach will be needed. That's because there's a limited length to each prompt, i.e. a context window.\n",
        "\n",
        "For example, you could compress highlights from each individual table's content into smaller documents, and then summarize those using a [map-reduce](https://python.langchain.com/docs/use_cases/summarization#option-2-map-reduce) method. Or, you could iterate over each table, [refining](https://python.langchain.com/docs/use_cases/summarization#option-3-refine) your query as you go.\n",
        "\n",
        "Here's how to do it. We'll use the [LangChain Expression Language](https://python.langchain.com/docs/expression_language/) (LCEL) to define the chain with 3 steps:\n",
        "\n",
        "1. We'll combine the page_content from each document (remember, that's the DDL of each table) into a string called content.\n",
        "1. Create a prompt to find our most valuable customers, passing in content, the combined set of table metadata .\n",
        "1. Pass the prompt to the LLM.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8e0dc06c"
      },
      "outputs": [],
      "source": [
        "# Use code generation model\n",
        "llm = VertexAI(model_name=\"code-bison@latest\", max_output_tokens=2048)\n",
        "\n",
        "# Define the chain\n",
        "chain = (\n",
        "    {\n",
        "        \"content\": lambda docs: \"\\n\\n\".join(\n",
        "            format_document(doc, PromptTemplate.from_template(\"{page_content}\"))\n",
        "            for doc in docs\n",
        "        )\n",
        "    }\n",
        "    | PromptTemplate.from_template(\n",
        "        \"Suggest a GoogleSQL query that will help me identify my most valuable customers located in Japan:\\n\\n{content}\"\n",
        "    )\n",
        "    | llm\n",
        ")\n",
        "\n",
        "# Invoke the chain with the documents, and remove code backticks\n",
        "result = chain.invoke(data).strip(\"```\")\n",
        "print(result)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "31f18baed279"
      },
      "source": [
        "Let's now try out our query, and see what it returns!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "97737a23787d"
      },
      "outputs": [],
      "source": [
        "client = bq.Client()\n",
        "client.query(result).result().to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f65764a2d0cd"
      },
      "source": [
        "Congratulations, you've now seen how to integrate your BigQuery data into LLM solutions! 🎉"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "langchain_bigquery_data_loader.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}