explore-assistant-training/looker_explore_assistant_training.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "# MIT License\n",
        "\n",
        "# Copyright (c) 2023 Looker Data Sciences, Inc.\n",
        "\n",
        "# Permission is hereby granted, free of charge, to any person obtaining a copy\n",
        "# of this software and associated documentation files (the \"Software\"), to deal\n",
        "# in the Software without restriction, including without limitation the rights\n",
        "# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell\n",
        "# copies of the Software, and to permit persons to whom the Software is\n",
        "# furnished to do so, subject to the following conditions:\n",
        "\n",
        "# The above copyright notice and this permission notice shall be included in all\n",
        "# copies or substantial portions of the Software.\n",
        "\n",
        "# THE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\n",
        "# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\n",
        "# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\n",
        "# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\n",
        "# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\n",
        "# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\n",
        "# SOFTWARE."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7JbdO0xRwjrV"
      },
      "source": [
        "# Explore Assistant Examples Generation\n",
        "\n",
        "This notebook is a companion to the [Explore Assistant Looker + GenAI Solution](https://github.com/LukaFontanilla/looker-explore-assistant) and will take you through some example code for:\n",
        "\n",
        "\n",
        "*   Formatting Looker Explore Metadata for Prompt Stuffing\n",
        "*   Generating NLQ to Explore URL examples from your data and Looker Explore usage\n",
        "*   Categorizing those examples by different Looker query types\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B8GHaG2hz4Dr"
      },
      "source": [
        "## Install Dependencies"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7rcdnts_TqJy",
        "outputId": "fc4b4d55-cd08-48af-b646-e4725a00884f"
      },
      "outputs": [],
      "source": [
        "%pip install looker-sdk\n",
        "%pip install --upgrade google-cloud-aiplatform"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dDTunjYj0D21"
      },
      "source": [
        "## Import Required Packages"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "UnOd9duf6tjZ"
      },
      "outputs": [],
      "source": [
        "import looker_sdk\n",
        "import vertexai\n",
        "from vertexai.preview.generative_models import GenerativeModel, GenerationConfig\n",
        "from looker_sdk import models40 as models, error\n",
        "import configparser\n",
        "import json\n",
        "import urllib.parse\n",
        "import re"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "H8hSwYQ00HIU"
      },
      "source": [
        "## Configure Application Default Credentials with GCloud\n",
        "\n",
        "This [Exports ADC credentials](https://cloud.google.com/docs/authentication/application-default-credentials) to your environment. ***Make sure to set the quota project to a GCP project that has the Vertex AI API enabled.***"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uVADeAsor7FR",
        "outputId": "c6602f75-1c6b-4ad0-c913-45873b59acc2"
      },
      "outputs": [],
      "source": [
        "!gcloud auth application-default login"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YUGBr03ZsEi4",
        "outputId": "48a2455b-e81c-4b4a-e176-424b308aac72"
      },
      "outputs": [],
      "source": [
        "!gcloud config set project looker-private-demo\n",
        "!gcloud auth application-default set-quota-project looker-private-demo"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "10e3sC8y0gGT"
      },
      "source": [
        "## Setup Looker SDK\n",
        "\n",
        "Steps for configuring the Looker Python SDK:\n",
        "\n",
        "\n",
        "1.   Create a file named `looker.ini`\n",
        "2.   Using the example below, fill in the variables as they are for your environment. ***You will need Looker API credentials for a user that has at least `explore` level permissions.***\n",
        "2.   Upload that file into your Colab Notebook\n",
        "\n",
        "\n",
        "`looker_example.ini`\n",
        "```\n",
        "[Looker]\n",
        "base_url=\n",
        "client_id=\n",
        "client_secret=\n",
        "verify_ssl=true\n",
        "```\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "z77fb26l7WU8"
      },
      "outputs": [],
      "source": [
        "sdk = looker_sdk.init40('looker.ini')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_LwTOEam3Fht"
      },
      "source": [
        "## Setup Vertex Python SDK with Gemini Model\n",
        "\n",
        "Please set the following variables prior to running the cell:\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "cLkJluszslz3"
      },
      "outputs": [],
      "source": [
        "project_id = 'looker-private-demo' # @param {type:\"string\"}\n",
        "location = 'us-central1' # @param {type:\"string\"}\n",
        "\n",
        "prompt = '''You are a specialized assistant that translates Looker Explore query URL's into natural language questions. By reading the different parameters of the url (like the fields used, filters, etc.) you are able to generate a natural language question.\n",
        "Please keep these responses short and concise using 1-2 sentences max in your repsonse. Make sure to generate a response that sounds like it's coming from an average person and not a data analyst who is very familiar with the data. Each request will contain an \"input\" and an \"output\" field. The \"output\" field will be the Looker Explore query url. The \"input\" field will be the natural language question that you will fill in/generate. Here is an example of a properly formatted response:\n",
        "Example:\n",
        "{\"input\": \"customer with lifetime revenue > 100\", \"output\": \"fields=user_order_facts.lifetime_revenue&f[user_order_facts.lifetime_revenue]=>100&sorts=user_order_facts.lifetime_revenue desc 0&limit=500\"}\n",
        "'''\n",
        "\n",
        "parameters = {\"max_output_tokens\": 2500, \"temperature\": 0.2, \"candidate_count\": 1}\n",
        "vertexai.init(project=project_id, location=location)\n",
        "\n",
        "def generate_input(request):\n",
        "    model = GenerativeModel(\"gemini-pro\")\n",
        "    # make prediction to generate Looker Explore URL\n",
        "    response =  model.generate_content(\n",
        "        contents=prompt + request,\n",
        "        generation_config=GenerationConfig(\n",
        "            temperature=0.2,\n",
        "            top_p=0.8,\n",
        "            top_k=40,\n",
        "            max_output_tokens=1000,\n",
        "            candidate_count=1\n",
        "        )\n",
        "    )\n",
        "\n",
        "    return response.text"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jzYmIxOa5Ghm"
      },
      "source": [
        "## Format Looker Explore Metadata for Prompt Stuffing\n",
        "\n",
        "The next two cells provide the Looker Explore Metadata the LLM needs to be able to generate Looker Explore URL's from natural language. This is all done through Prompt Stuffing.\n",
        "\n",
        "\n",
        "*   Fetches all the field metadata from the LookML model for a given Explore\n",
        "*   Generates two arrays containing all the measures and dimensions with the name, type, description and any other relevant attribute you'd like to include\n",
        "*   Formats that into a structured text variable and writes it to a txt file in the format of `model::explore.txt`\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "E0ycyL-Z63Id"
      },
      "outputs": [],
      "source": [
        "def fetchExploreMetadata(model,explore,fields):\n",
        "  data = sdk.lookml_model_explore(model,explore,fields)\n",
        "\n",
        "  # Dimensions\n",
        "  dimensions = []\n",
        "  for field in data.fields.dimensions:\n",
        "    dimensions.append(f\"name: {field.name}, type: {field.type}, description: {field.description} \\n\")\n",
        "\n",
        "  # # Measures\n",
        "  measures = []\n",
        "  for field in data.fields.measures:\n",
        "    measures.append(f\"name: {field.name}, type: {field.type}, description: {field.description} \\n\")\n",
        "\n",
        "  return {\n",
        "      \"dimensions\": dimensions,\n",
        "      \"measures\": measures\n",
        "  }\n",
        "\n",
        "\n",
        "def formatExploreMetadata(data):\n",
        "  return f\"\"\"\n",
        "  Dimensions Used to group by information:\\n {''.join(data['dimensions'])}\n",
        "  Measures are used to perform calculations/aggregations (if top, bottom, total, sum, etc. are used include a measure):\\n {''.join(data['measures'])}\n",
        "  \"\"\"\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "uwewxfku66i4"
      },
      "outputs": [],
      "source": [
        "model = 'thelook' # @param {type:\"string\"}\n",
        "explore = 'order_items' # @param {type:\"string\"}\n",
        "\n",
        "data = fetchExploreMetadata(model, explore, 'fields')\n",
        "\n",
        "with open(f\"./{model}::{explore}.txt\", \"w\") as f:\n",
        "  f.write(formatExploreMetadata(data))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aNUphl0i6WtF"
      },
      "source": [
        "## Setup Explore URL Parser & Categorizer\n",
        "\n",
        "The following cells setup the functions used to generate commmon and representative Looker Explore query URL's that are labeled via Gen AI for the LLM to use in it's reasoning.\n",
        "\n",
        "*   CONSTANTS: Regex patterns for Looker Query filter string parsing\n",
        "*   LOOKER QUERY METHODS: The functions for fetching historical queries and parsing their metadata for an expanded URL\n",
        "*   LOOKER URL PARSER FUNCTIONS: Functions used to parse and categorize example URL's into specific categories of queries\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "id": "r0bmWXonxxM1"
      },
      "outputs": [],
      "source": [
        "### CONSTANTS\n",
        "\n",
        "# Time / Date Regex Patterns\n",
        "time_relative_pattern = r\"(\\d+)\\s+(month|week|day|year)?(?:\\s+ago)?\"\n",
        "time_range_pattern = r\"\\b(\\d+)\\s+(month|week|day|year)s?\\s+ago\\s+for\\s+\\1\\s+\\2\\b\"\n",
        "\n",
        "# Numerical Patterns\n",
        "numerical_comparison_pattern = r\"^(>|>=|<|<=|<>)?(\\d+)$\"\n",
        "numerical_range_pattern = r\"\\b(>|>=|<|<=|<>)?(\\d+)?\\s+(AND|OR)?\\s+(>|>=|<|<=|<>)?(\\d+)\"\n",
        "\n",
        "# String Patterns\n",
        "string_catch_all_pattern = r\"\\w\"\n",
        "string_multiple_pattern = r\"\\w,+\\w\"\n",
        "categorized_queries = {}\n",
        "categorized_queries_filters = {}\n",
        "\n",
        "### END\n",
        "\n",
        "\n",
        "### LOOKER QUERY METHODS\n",
        "\n",
        "\n",
        "def fetchQueryUrlMetadata(explore: str):\n",
        "  try:\n",
        "    response = sdk.run_inline_query(\n",
        "        result_format='json',\n",
        "        cache=True,\n",
        "        body=models.WriteQuery(\n",
        "            model=\"system__activity\",\n",
        "            view=\"history\",\n",
        "            fields=[\n",
        "              \"query.slug\",\n",
        "              \"query.view\",\n",
        "              \"query.dynamic_fields\",\n",
        "              \"query.formatted_fields\",\n",
        "              \"query.filters\",\n",
        "              \"query.filter_expression\",\n",
        "              \"query.formatted_pivots\",\n",
        "              \"query.sorts\",\n",
        "              \"query.limit\",\n",
        "              \"query.column_limit\",\n",
        "              \"query.count\"\n",
        "            ],\n",
        "            pivots=None,\n",
        "            fill_fields=None,\n",
        "            filters={\n",
        "              \"query.view\": explore,\n",
        "              \"history.status\": \"complete\",\n",
        "              # \"user.email\":\"\"\n",
        "            },\n",
        "            filter_expression=None,\n",
        "            sorts=[\n",
        "              \"history.completed_time desc\"\n",
        "              \"query.view\"\n",
        "            ],\n",
        "            limit=\"10\",\n",
        "        )\n",
        "    )\n",
        "\n",
        "    return json.loads(response)[0:10]\n",
        "  except error.SDKError as e:\n",
        "    print(e.message)\n",
        "\n",
        "\n",
        "\n",
        "def fetchQueryUrl(slug: str):\n",
        "  query_url = sdk.query_for_slug(slug=slug)\n",
        "  return query_url\n",
        "\n",
        "### END\n",
        "\n",
        "\n",
        "### LOOKER URL PARSER FUNCTIONS\n",
        "\n",
        "# limit categorization\n",
        "def limit_categorization(query,url):\n",
        "  if \"query.limit\" in query and query['query.limit'] != None:\n",
        "      categorized_queries.setdefault('limit',[])\n",
        "      categorized_queries['limit'].append(url)\n",
        "\n",
        "# dynamic fields categorization\n",
        "def dynamic_fields_categorization(query,url):\n",
        "  if \"query.dynamic_fields\" in query and query['query.dynamic_fields'] != None:\n",
        "      categorized_queries.setdefault('dynamic_fields',[])\n",
        "      categorized_queries['dynamic_fields'].append(url)\n",
        "\n",
        "# sorts categorization\n",
        "def sorts_categorization(query,url):\n",
        "  if \"query.sorts\" in query and query['query.sorts'] != None:\n",
        "      categorized_queries.setdefault('sorts',[])\n",
        "      categorized_queries['sorts'].append(url)\n",
        "\n",
        "# filter expression categorization\n",
        "def filter_expression_categorization(query,url):\n",
        "  if \"query.filter_expression\" in query and query['query.filter_expression'] != None:\n",
        "      categorized_queries.setdefault('filter_expression',[])\n",
        "      categorized_queries['filter_expression'].append(url)\n",
        "\n",
        "# pivots categorization\n",
        "def pivots_categorization(query,url):\n",
        "  if \"query.formatted_pivots\" in query and query['query.formatted_pivots'] != None:\n",
        "      categorized_queries.setdefault('pivots',[])\n",
        "      categorized_queries['pivots'].append(url)\n",
        "\n",
        "# filters categorization\n",
        "def filters_categorization(query,url):\n",
        "  parsed_filters = json.loads(query['query.filters'])\n",
        "  keys_copy = tuple(parsed_filters.keys())\n",
        "  for key in keys_copy:\n",
        "    if parsed_filters[key] != \"\":\n",
        "      if re.findall(time_range_pattern, parsed_filters[key]):\n",
        "        categorized_queries_filters.setdefault('time_range',[])\n",
        "        categorized_queries_filters['time_range'].append(url)\n",
        "        continue\n",
        "      if re.findall(time_relative_pattern, parsed_filters[key]):\n",
        "        categorized_queries_filters.setdefault('time_relative',[])\n",
        "        categorized_queries_filters['time_relative'].append(url)\n",
        "        continue\n",
        "      elif re.findall(numerical_comparison_pattern, parsed_filters[key]):\n",
        "        categorized_queries_filters.setdefault('numerical_comparison',[])\n",
        "        categorized_queries_filters['numerical_comparison'].append(url)\n",
        "        continue\n",
        "      elif re.findall(numerical_range_pattern, parsed_filters[key]):\n",
        "        categorized_queries_filters.setdefault('numerical_range',[])\n",
        "        categorized_queries_filters['numerical_range'].append(url)\n",
        "        continue\n",
        "      elif re.findall(string_multiple_pattern, parsed_filters[key]):\n",
        "        categorized_queries_filters.setdefault('string_multiple',[])\n",
        "        categorized_queries_filters['string_multiple'].append(url)\n",
        "        continue\n",
        "      elif re.findall(r\"\\w\",parsed_filters[key]):\n",
        "        categorized_queries_filters.setdefault('string_standard',[])\n",
        "        categorized_queries_filters['string_standard'].append(url)\n",
        "        continue\n",
        "\n",
        "### END\n",
        "\n",
        "def explore_url_categorization(data):\n",
        "  for query in data:\n",
        "    query_data = fetchQueryUrl(str(query['query.slug']))\n",
        "    decoded_url = urllib.parse.unquote(query_data.url)\n",
        "\n",
        "    # return url parameters as a string\n",
        "    url_parameters = decoded_url.split(\"?\",1)[1].replace(\"+\", \" \")\n",
        "    # remove timezone parameter\n",
        "    decoded_url_notimezone = re.sub(r\"&query_timezone=(.)*&\", \"&\", url_parameters,count=1)\n",
        "    # remove filter config parameter\n",
        "    decoded_url_nofilterconfig = re.sub(r\"&filter_config=(.)*(?=&|$)\", \"&\", decoded_url_notimezone)[0:-1] if re.sub(r\"&filter_config=(.)*(?=&|$)\", \"&\", decoded_url_notimezone)[-1] == \"&\" else re.sub(r\"&filter_config=(.)*(?=&|$)\", \"&\", decoded_url_notimezone)\n",
        "    # parse vis config parameter only maintain vis type\n",
        "    vis_config = re.search(r\"(&vis=(.)*(?=&|$))\", decoded_url_nofilterconfig)\n",
        "\n",
        "    decoded_url_modifiedvisjson = ''\n",
        "    if vis_config:\n",
        "      vis_json_str = vis_config.group(1)\n",
        "      # regex to search for the vis type (ie. \"type\":\"looker_bar\")\n",
        "      vis_type = re.search(r'(\"type\":\\s*\"([^,}]+))', vis_json_str)\n",
        "      # replace the vis config in original url parameter string with the modified vis type\n",
        "      decoded_url_modifiedvisjson = re.sub(r\"(&vis=(.)*(?=&|$))\",\"&vis={\" + (vis_type.group(1) if vis_type else '') + \"}\",decoded_url_nofilterconfig)\n",
        "    else:\n",
        "      decoded_url_modifiedvisjson = decoded_url_nofilterconfig\n",
        "\n",
        "    # run categorization functions to construct object with categorized urls\n",
        "    limit_categorization(query,decoded_url_modifiedvisjson)\n",
        "    dynamic_fields_categorization(query,decoded_url_modifiedvisjson)\n",
        "    sorts_categorization(query,decoded_url_modifiedvisjson)\n",
        "    filter_expression_categorization(query,decoded_url_modifiedvisjson)\n",
        "    pivots_categorization(query,decoded_url_modifiedvisjson)\n",
        "    filters_categorization(query,decoded_url_modifiedvisjson)\n",
        "\n",
        "  categorized_queries.setdefault('filters',{})\n",
        "  categorized_queries['filters'] = categorized_queries_filters\n",
        "  return categorized_queries\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "id": "bD_pNQm6rXce"
      },
      "outputs": [],
      "source": [
        "data = fetchQueryUrlMetadata('order_items')\n",
        "# categorization =\n",
        "categorized_queries = explore_url_categorization(data)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "id": "-oZF3NKCyJPZ"
      },
      "outputs": [],
      "source": [
        "url_prompts = []\n",
        "\n",
        "for key in categorized_queries.keys():\n",
        "  if type(categorized_queries[key]) == list:\n",
        "    for url in categorized_queries[key][0:3]:\n",
        "      url_prompts.append(generate_input(json.dumps({\"input\": \"\", \"output\": url})) + '\\n')\n",
        "\n",
        "  else:\n",
        "    for key2 in categorized_queries[key].keys():\n",
        "      for url in categorized_queries[key][key2][0:3]:\n",
        "        url_prompts.append(generate_input(json.dumps({\"input\": \"\", \"output\": url})) + '\\n')\n",
        "\n",
        "\n",
        "with open(\"./examples.jsonl\", \"a\") as f:\n",
        "  f.writelines(url_prompts)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "B8GHaG2hz4Dr",
        "H8hSwYQ00HIU",
        "_LwTOEam3Fht",
        "jzYmIxOa5Ghm"
      ],
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}