python/python-sdk-tutorial.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uVFbvrN2lnPU"
      },
      "source": [
        "# Looker Python SDK Tutorial\n",
        "\n",
        "> Looking for a simple Quick Start? Check out the instructions in the [README](https://github.com/looker-open-source/sdk-codegen/tree/main/python).\n",
        "\n",
        "\n",
        "In this 15-20 minute tutorial, we'll build a real-world tool using the Looker Python SDK— albeit a basic one. We'll start from absolute scratch, and while you can follow along in the IDE of your choice, I recommend you follow along in this notebook. \n",
        "\n",
        "You can follow along right in this version, but any changes you make won't be saved if you refresh. To save your own version, \"Copy to Drive\" using the button just above this text block.\n",
        "\n",
        "\n",
        "**In this tutorial you will:**\n",
        "\n",
        "*   Install and configure the Looker Python SDK.\n",
        "*   Learn to read & write data using the SDK.\n",
        "*   Understand the Methods and Models made available by the SDK and how they work.\n",
        "*   Chain simple API calls together to create more complex logic.\n",
        "*   **Build a simple micro-application that lets you easily use the results of one Look as dynamic filter values for another Look.**\n",
        "\n",
        "**Prerequisites:**\n",
        "*    Access to a Looker instance. We don't yet offer an open sandbox (soon!).\n",
        "*    API Keys for that instance (Accessible from Admin > Users. If you are not an admin, you'll need to ask your admin).\n",
        "*    Some Python experience. There aren't any complex concepts here but it's assumed you understand basic Python. \n",
        "\n",
        "_If you've never used Jupyter Notebooks or Colaboratory before, it's pretty simple. This notebook just lets you intersperse text and Python code blocks and show the output nicely. Clone this notebook, and you'll have your own editable copy of it all to yourself. You can double-click into each of these \"blocks\" to edit them._ \n",
        "\n",
        "_Some blocks are markdown text (like this one) and others are `code`. The Python code blocks can be **Run** by hitting CMD+Enter or clicking the Play button on the left side of each block. **The execution flow is linear.** If you define a variable in block #1 and reference it in block #2, you must run block #1 before block #2 or the variable won't be available. If you're getting errors that seem odd, make sure you've run any necessary earlier blocks._\n",
        "\n",
        "<font size=6>👀</font> **Hot tip! Throughout the tutorial, you may see the <font size=6>👀</font> emoji denoting an aside or a handy tip. Keep your eyes peeled!**\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IYXK_QYAlpE-"
      },
      "source": [
        "# Installing requirements\n",
        "We'll install the latest looker_sdk from [pypi](https://pypi.org/project/looker-sdk/). \n",
        "\n",
        "_If you're doing this on your local machine (ie: not in a jupyter notebook) we recommend you use pyenv to manage your environment. See the instructions [here](https://pypi.org/project/looker-sdk/)._\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2MVc2mTwltae",
        "outputId": "129269e2-e4fd-4aca-caad-9ca9b1c9f8f5"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: looker_sdk in /usr/local/lib/python3.6/dist-packages (0.1.3b20)\n",
            "Requirement already satisfied: cattrs<1.1.0; python_version < \"3.7\" in /usr/local/lib/python3.6/dist-packages (from looker_sdk) (1.0.0)\n",
            "Requirement already satisfied: typing-extensions; python_version < \"3.8\" in /usr/local/lib/python3.6/dist-packages (from looker_sdk) (3.7.4.3)\n",
            "Requirement already satisfied: python-dateutil; python_version < \"3.7\" in /usr/local/lib/python3.6/dist-packages (from looker_sdk) (2.8.1)\n",
            "Requirement already satisfied: requests>=2.22 in /usr/local/lib/python3.6/dist-packages (from looker_sdk) (2.23.0)\n",
            "Requirement already satisfied: attrs>=18.2.0; python_version < \"3.7\" in /usr/local/lib/python3.6/dist-packages (from looker_sdk) (20.3.0)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.6/dist-packages (from python-dateutil; python_version < \"3.7\"->looker_sdk) (1.15.0)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.6/dist-packages (from requests>=2.22->looker_sdk) (3.0.4)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.6/dist-packages (from requests>=2.22->looker_sdk) (2.10)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.6/dist-packages (from requests>=2.22->looker_sdk) (1.24.3)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.6/dist-packages (from requests>=2.22->looker_sdk) (2020.12.5)\n"
          ]
        }
      ],
      "source": [
        "pip install looker_sdk"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "x9aokkzwogFe"
      },
      "outputs": [],
      "source": [
        "import looker_sdk #Note that the pip install required a hyphen but the import is an underscore.\n",
        "\n",
        "import os #We import os here in order to manage environment variables for the tutorial. You don't need to do this on a local system or anywhere you can more conveniently set environment variables.\n",
        "\n",
        "import json #This is a handy library for doing JSON work."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "25gT1y7Ml4vH"
      },
      "source": [
        "# Configuring & Initializing the SDK\n",
        "In this notebook, we'll use environment variables to configure the SDK. On your local machine or environment, you can choose to use either environment variables or a looker.ini file you create. Either is perfectly fine to do.\n",
        "\n",
        "I actually prefer to use environment variables, as I often find myself deploying cloud functions or other lightweight apps where it's inconvenient to add a .ini file. If you do want to use a looker.ini, example looker.ini contents are below. If a file named looker.ini is in your working directory and contains the following code, the Looker SDK will automatically use it:\n",
        "\n",
        "\n",
        "```\n",
        "[Looker]\n",
        "# Base URL for API. Do not include /api/* in the url\n",
        "base_url=https://self-signed.looker.com:19999\n",
        "# API client id\n",
        "client_id=YourClientID\n",
        "# API client secret\n",
        "client_secret=YourClientSecret\n",
        "# Set to false if testing locally against self-signed certs. Otherwise leave True\n",
        "verify_ssl=True\n",
        "```\n",
        "Make sure to ***never*** commit this .ini file to source control (ie: github) as it contains your API secret. You should immediately add looker.ini to your .gitignore file if working in a git repository.\n",
        "\n",
        "<font size=6>👀</font> _If you have a looker.ini file *AND* set environment variables, the environment variables will override the contents of the .ini file._\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fl5ub2qLnL68"
      },
      "source": [
        "In this notebook, for convenience we'll use the ```os``` package to set environment variables on the fly. Edit the placeholders below with the information for your Looker instance and run the block.\n",
        "\n",
        "**Be careful not to share this notebook if you leave your API credentials in it. Also, just don't leave your API credentials in it!! Please.**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "SOp9JZxPm4pM"
      },
      "outputs": [],
      "source": [
        "os.environ[\"LOOKERSDK_BASE_URL\"] = \"https://your.looker.com:19999\" #If your looker URL has .cloud in it (hosted on GCP), do not include :19999 (ie: https://your.cloud.looker.com).\n",
        "os.environ[\"LOOKERSDK_API_VERSION\"] = \"4.0\" #As of Looker v23.18+, the 3.0 and 3.1 versions of the API are removed. Use \"4.0\" here.\n",
        "os.environ[\"LOOKERSDK_VERIFY_SSL\"] = \"true\" #Defaults to true if not set. SSL verification should generally be on unless you have a real good reason not to use it. Valid options: true, y, t, yes, 1.\n",
        "os.environ[\"LOOKERSDK_TIMEOUT\"] = \"120\" #Seconds till request timeout. Standard default is 120.\n",
        "\n",
        "#Get the following values from your Users page in the Admin panel of your Looker instance > Users > Your user > Edit API keys. If you know your user id, you can visit https://your.looker.com/admin/users/<your_user_id>/edit.\n",
        "os.environ[\"LOOKERSDK_CLIENT_ID\"] =  \"clientid\" #No defaults.\n",
        "os.environ[\"LOOKERSDK_CLIENT_SECRET\"] = \"clientsecret\" #No defaults. This should be protected at all costs. Please do not leave it sitting here, even if you don't share this document.\n",
        "\n",
        "print(\"All environment variables set.\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PUaK0b39oofw"
      },
      "source": [
        "Now that we've set all the necessary environment variables, we should be able to initialize the Looker SDK. Please note that the 3.1 and 3.0  versions of the API have been removed in Looker v23.18+, so we'll need to initialize using the 4.0 SDK as shown below.\n",
        "\n",
        "Having set environment variables, we don't need to pass any args to the ```init40()``` function— It automatically checks for environment variables. If you're using a specific .ini file, you might want to say ```sdk = looker_sdk.init40(~/path/to/looker.ini)```."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3fql8YPho2Y1"
      },
      "outputs": [],
      "source": [
        "sdk = looker_sdk.init40()\n",
        "print('Looker SDK 4.0 initialized successfully.')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p8A-xXhEpG8C"
      },
      "source": [
        "# Making API Calls\n",
        "We've now initialized an instance of the SDK using the Looker 4.0 API. We can reference it via ```sdk``` and use any of the SDK functions available.\n",
        "\n",
        "**Hello World (aka Reading Data)**\n",
        "\n",
        "The standard Looker \"Hello World\" equivalent is to get your own user object and print it. We'll do this first, before diving into some real scripting."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a3tpOkHbpvy7"
      },
      "outputs": [],
      "source": [
        "my_user = sdk.me()\n",
        "\n",
        "#Output is an instance of the User model, but can also be read like a python dict. This applies to all Looker API calls that return Models.\n",
        "#Example: The following commands return identical output. Feel free to use whichever style is more comfortable for you.\n",
        "\n",
        "print(my_user.first_name) #Model dot notation\n",
        "print(my_user[\"first_name\"]) #Dictionary"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iuvlVgXMqs7D"
      },
      "source": [
        "Huzzah! You've just made your first Looker API calls, and should see your first name printed out twice after running the cell above. If you're getting errors, triple and quadruple check the environment variable settings and your API keys. \n",
        "\n",
        "If you are getting an error here, stop and resolve it before continuing. If this doesn't work, nothing will.\n",
        "\n",
        "<font size=6>👀</font> **Troubleshooting tip: remember that if you're using an AWS-hosted Looker, you'll need to include :19999 at the end of your `BASE_URL` (https://your.looker.com:19999), but if you're on a GCP instance at a [your.cloud.looker.com](your.cloud.looker.com) style URL, you should remove it.**\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gM860a4xrFz8"
      },
      "source": [
        "**Writing Data**\n",
        "\n",
        "Requesting information with API calls is pretty easy— You make a call like `sdk.me()`, it returns the right information using the right model, you print it out, pretty simple.\n",
        "\n",
        "Writing information with API calls is slightly more complicated, as you need to provide the right information _in the right format_ so Looker can understand it. This can be confusing, but the SDK has helpful [model instances](https://github.com/looker-open-source/sdk-codegen/blob/main/python/looker_sdk/sdk/api40/models.py) to make things less likely to error. Models are classes pre-built for all the kinds of objects you need to interact with in the Looker API— Users, Queries, Dashboards, etc. \n",
        "\n",
        "This can take some getting used to though, and you're welcome to use normal python dictionaries instead if you'd like. Check out the examples below. We use the WriteUser model to make the call simpler, but replicate the same call using a simple dict as well. \n",
        "\n",
        "You can see the WriteUser model [here](https://github.com/looker-open-source/sdk-codegen/blob/main/python/looker_sdk/sdk/api40/models.py#L13826), for reference."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0zTsa_0UrEDx"
      },
      "outputs": [],
      "source": [
        "#Example of creating a user using a Model instance (WriteUser)\n",
        "user = sdk.create_user(\n",
        "  body = looker_sdk.models.WriteUser(first_name=\"Jane\", last_name=\"Doe\")\n",
        ")\n",
        "print(user.id, user.first_name)\n",
        "\n",
        "#Example of creating a user using a simple python dictionary\n",
        "user = sdk.create_user(\n",
        "    body={\"first_name\": \"Jane\", \"last_name\": \"Doe\"}\n",
        ")\n",
        "print(user.id, user.first_name)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "18LWQO8ss2EG"
      },
      "source": [
        "Running the cell above should create two identical users and return two lines, each with a different User ID but the same name. You're welcome to use either method you prefer going forward, but we'll show examples using models as it's the most foolproof and Lookery way of doing things.\n",
        "\n",
        "<font size=6>👀</font> _We assigned the `sdk.create_user()` call directly to a user variable, which we could then immediately reference. You don't need to assign calls to variables, but it's generally handy so that you can immediately reference the user you've created. If you wanted to create the user and just move on, you could have written:_\n",
        "```\n",
        "sdk.create_user(\n",
        "  body = looker_sdk.models.WriteUser(first_name=\"Jane\", last_name=\"Doe\")\n",
        ")\n",
        "```\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D4iTt1aXtRWv"
      },
      "source": [
        "# Building application logic\n",
        "It usually helps to mock up the flow of an App/script before diving into code. In this case, our mission is to take the output of one Look and pipe it into the filters of a second Look. That means it'll look something like this:\n",
        "\n",
        "1. Get Query #1 from Look #1\n",
        "2. Run Query #1 and save results (Our eventual filter input)\n",
        "3. If needed, transform results to use as a filter\n",
        "4. Get Query #2 from Look #2\n",
        "5. Update Query #2 to include our new filter values\n",
        "6. Run Query #2\n",
        "\n",
        "Let's take it item by item."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ymgP3A-zwWEj"
      },
      "source": [
        "**1. Get query #1 from Look #1 (input query)**\n",
        "\n",
        "Our input query needs to be simple. It should return just one column, which will contain the list of values to be passed into Look #2's filters. You can add whatever filters you'd like. Mine is a list of clothing brands. Here's what it looks like: ![Screen Shot 2021-01-14 at 3.52.25 PM.png](https://i.imgur.com/mGoMepU.png)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "U3gIfdhwxjLM"
      },
      "outputs": [],
      "source": [
        "#Enter your Look ID. If your URL is https://your.cloud.looker.com/looks/25, your Look ID is 25.\n",
        "look_id = \"25\"\n",
        "look = sdk.look(look_id=look_id) \n",
        "# This gives us a Look object. We'll print the ID of it to verify everything's working.\n",
        "\n",
        "print(look.id)\n",
        "\n",
        "#You actually don't need to do anything further for this case, using a Look. \n",
        "#If you wanted to use an Explore instead, you'd have to get the underlying query first, which might look like this:\n",
        "\n",
        "#explore_id = \"Q4pXny1FEtuxMuj9Atf0Gg\" \n",
        "#If your URL looks like https://your.cloud.looker.com/explore/ecommerce_data/order_items?qid=Q4pXny1FEtuxMuj9Atf0Gg&origin_space=15&toggle=vis, your explore_id/QID is Q4pXny1FEtuxMuj9Atf0Gg.\n",
        "#explore_query = sdk.query_for_slug(slug=explore_id)\n",
        "\n",
        "#This would return a Query object that we could then run to get results in step 2 using the run_query endpoints."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DiboaPjoyhH2"
      },
      "source": [
        "**2. Run query #1 and save results**\n",
        "\n",
        "Now we need to actually run this Look, to get our filter input values. This is also quite simple using the SDK!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0IpDI4b8z4Mq"
      },
      "outputs": [],
      "source": [
        "#We'll use a try/except block here, to make debugging easier. \n",
        "#In general, this kind of thing isn't really necessary in notebooks as each cell is already isolated from the rest,\n",
        "#but it's a good practice in larger scripts and certainly in applications where fatal errors can break the entire app.\n",
        "#You should get into the habit of using them.\n",
        "\n",
        "try:\n",
        "  response = sdk.run_look(\n",
        "    look_id=look.id,\n",
        "    result_format= \"json\" # Options here are csv, json, json_detail, txt, html, md, xlsx, sql (returns the raw query), png, jpg. JSON is the easiest to work with in python, so we return it.\n",
        "  )\n",
        "  data = json.loads(response) #The response is just a string, so we have to use the json library to load it as a json dict.\n",
        "  print(data) #If our query was successful we should see an array of rows.\n",
        "except:\n",
        "  raise Exception(f'Error running look {look.id}')\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OF7m-roOeky2"
      },
      "source": [
        "This might be a good time to mention the ```methods.py``` file. You may be wondering \"how did you know what arguments to pass to `sdk.run_look()`? \n",
        "\n",
        "Most IDEs (Colaboratory included) will let you inspect any imported functions. Try mousing over run_look in the code block above, and you'll see it provides you with the definition of the function and all of its arguments. This is probably the best way to understand what you need to pass to a method.\n",
        "\n",
        "If you don't have that capability, you can review the SDK's [methods.py](https://![image.png](https://github.com/looker-open-source/sdk-codegen/blob/main/python/looker_sdk/sdk/api40/methods.py)) file, which lists every SDK method. `run_look` is [here](https://github.com/looker-open-source/sdk-codegen/blob/main/python/looker_sdk/sdk/api40/methods.py#L7095), on line 4427 (at time of writing).\n",
        "\n",
        "This is **super** helpful to review. It tells us what arguments functions take, what types they accept, and has handy descriptions. Use it when you're confused!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_KrIz5Ht2Prc"
      },
      "source": [
        "**3. If needed, transform results to use as a filter**\n",
        "\n",
        "We don't need to do too much to the data when running a look with `result_format: \"json\"`. Our `data` object now contains an array of \"rows\", each represented as a dict, and we can iterate through that array to get each row's values."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "unihO9Xj2iQc"
      },
      "outputs": [],
      "source": [
        "#Before we move on, here's a simple example of that. Let's print the first 10 rows.\n",
        "#This script is set up to always only look at the first column, assuming our Look returns 1 column.\n",
        "first_field = list(\n",
        "    data[0].keys()\n",
        "    )[0] #This looks at the first row of the data and returns the first field name. keys() returns a set, so we wrap it in list() to return an array.\n",
        "    \n",
        "for i in range(0,10):\n",
        "  print(i,data[i][first_field])\n",
        "\n",
        "#If we _know_ the name of the first field, why did we go to all this list(data[0].keys()[0]) trouble? Well, we know the name of the first field for ONE look. \n",
        "#This little trickery above makes it so that our script will always work for any Look, no matter what the name is, without having to edit the code."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bKosvIbR34ww"
      },
      "source": [
        "Before moving on, let's package our work up so far into a function to make things easier later on.\n",
        "\n",
        "In this function, we'll parse the data just a tiny bit to turn it into a filter expression. Looker filters take \"Looker Filter Expressions\", documented [here](https://docs.looker.com/reference/filter-expressions), which are strings. In this case, we'll just generate a comma separated list of values."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "L0XE0jkr38A8"
      },
      "outputs": [],
      "source": [
        "def get_data_for_look(look_id):\n",
        "  try:\n",
        "    look = sdk.look(look_id=look_id)\n",
        "  except:\n",
        "    raise Exception(f'Look {look_id} not found.')\n",
        "  print(f'Successfully got Look {look.id}')\n",
        "\n",
        "  try:\n",
        "    response = sdk.run_look(\n",
        "        look_id=look.id,\n",
        "        result_format = \"json\"\n",
        "    )\n",
        "    data = json.loads(response)\n",
        "    first_field = list(\n",
        "      data[0].keys()\n",
        "    )[0]\n",
        "    list_of_values = []\n",
        "    for i in data:\n",
        "      list_of_values.append(i[first_field])\n",
        "    #Ultimately, we're going to want to pass Looker a filter expression that's a comma-separated-list of values.\n",
        "    #Here, we use .join on the array of values to generate that filter expression. \n",
        "    string_list = \",\".join(list_of_values)\n",
        "    return({\"filter_values\": string_list, \"first_field\": first_field}) \n",
        "  except:\n",
        "    raise Exception('Error running Look.')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kXSdnobw5Rnw"
      },
      "source": [
        "Now we can just call this function whenever we want to grab data from a look in filter expression format."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7Ji8aYZ55WeI"
      },
      "outputs": [],
      "source": [
        "test = get_data_for_look(25)\n",
        "#This should return successful."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GRbt1FJB3v_Y"
      },
      "source": [
        "**4. Create or get query #2**\n",
        "\n",
        "Awesome! So now we can easily get the data for our first query. The second half of this isn't as simple, since we actually need to take a query apart and edit it. Let's again assume that we're using a Look.\n",
        "\n",
        "My first Look was a list of all the brands in the inventory table. My second look will be a list of Users and their age, filtered on users that have made more than 1 order. I want to add a filter to this Look of the selected brands generated by Look #1.\n",
        "\n",
        "![Screen Shot 2021-01-13 at 2.49.32 PM.png](https://i.imgur.com/AZPoa9P.png)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jEm7b4kL3vXE"
      },
      "outputs": [],
      "source": [
        "#Start off the same as before, by getting the Look. \n",
        "\n",
        "second_look_id = \"26\"\n",
        "second_look = sdk.look(look_id=second_look_id)\n",
        "print(second_look.id) #just verifying we obtained the Look properly.\n",
        "\n",
        "#Now we can extract the underlying query from the second Look, in order to modify it. We'll print it to see what it looks like.\n",
        "second_query = second_look.query\n",
        "print(second_query)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6kyXsHZt9b0d"
      },
      "outputs": [],
      "source": [
        "#We want to edit the filters, so let's start by inspecting the query and see how filters are set.\n",
        "#We can see all the available keys in the query object by running the command below.\n",
        "#👀 What are keys? Objects are key: value pairs. ex: {\"name\": \"Izzy\"}. The key is name, the value for that key is Izzy.\n",
        "\n",
        "print(second_query.keys())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VJ6g79ljCGN4"
      },
      "outputs": [],
      "source": [
        "\n",
        "#Looks like there's a filters key, so we can run the following to see the filters:\n",
        "print(\"Filters: \", second_query.filters)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UJXoOqCA90iN"
      },
      "source": [
        "For my example query, that returns `{'order_items.order_count': '>1'}`. Which matches up perfectly to the UI filter I'd already set on the Look. Awesome. ![Screen Shot 2021-01-13 at 2.57.18 PM.png](https://i.imgur.com/SzNz7sI.png)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9-36Rrx0-JJR"
      },
      "source": [
        "**5. Update query #2 to include our new filter values**\n",
        "\n",
        "We've successfully obtained and inspected the query that we want to alter. All that's left is to change the filters on it, and then run the final query.\n",
        "\n",
        "If we wanted to run this query as is, without modifying it, we'd probably just call `run_query`, and pass in the `query_id`.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KKM9pKqr-Xrn"
      },
      "outputs": [],
      "source": [
        "response = sdk.run_query(query_id = second_query.id, result_format=\"json\")\n",
        "print(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v46vTSXh-6b3"
      },
      "source": [
        "That's pretty simple. You just reference the existing query ID we got from the Look, and pass it to the run_query function. But that query doesn't include the new filters we want to add yet, and to do that we actually need to create a new query. \n",
        "\n",
        "<font size=6>👀</font> **For clarity's sake: You cannot alter existing Looker queries— You have to get a query, edit the query object returned, and then pass that query object back to create a brand new query with a new ID.**\n",
        "\n",
        "You have two options when creating new queries:\n",
        "\n",
        "a. Create a brand new query object on the Looker instance using create_query, and then run that query using `run_query`, or `create_query_task` for async execution.\n",
        "\n",
        "_or_\n",
        "\n",
        "b. Use `run_inline_query` to run a query immediately from raw input.\n",
        "\n",
        "Either way, the editing of the query object is the same. We'll show both methods."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "muAFN-LA-05l"
      },
      "outputs": [],
      "source": [
        "#Let's create a new variable, altered_query, and assign it to be identical to second_query to begin.\n",
        "altered_query = second_query \n",
        "\n",
        "#Then, let's set a new key on the filters dict, which adds a new filter to the query. We'll name it the name of the field we want to filter on.\n",
        "#This must exactly match the **fully scoped** field name in Looker (ie: view_name.field_name).\n",
        "#We will then set that key's value equal to our data. In this case, we'll set it equal to that comma-separated string we generated earlier.\n",
        "\n",
        "filter_data = get_data_for_look(25) # This is that function we built earlier.\n",
        "field_name = filter_data['first_field']\n",
        "filter_values = filter_data['filter_values']\n",
        "\n",
        "altered_query.filters[field_name] = filter_values\n",
        "\n",
        "#This should now print an object with the filters you've just added. Nice!\n",
        "print(altered_query.filters)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oa143Kxxjl5a"
      },
      "outputs": [],
      "source": [
        "#Before we can run this query, we need to do a little bit of pruning to it, since we copied it directly from an existing query.\n",
        "#If you do not remove the ID and client_id, you'll get an error that this query already exists.\n",
        "altered_query.client_id = None\n",
        "altered_query.id = None"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fa_uHPd1EHcd"
      },
      "source": [
        "**6. Run the new query**\n",
        "\n",
        "Now it's time to run this new query! Remember there were two options... We'll do them both below in separate blocks.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KvxH6CBlCfPu"
      },
      "outputs": [],
      "source": [
        "#Option a. Run inline query. This is the simplest option for just getting the data now.\n",
        "#This should return your newly filtered data from Look #2\n",
        "response = sdk.run_inline_query(body=altered_query, result_format=\"json\")\n",
        "print(response)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "o2gtWEv1DjkA"
      },
      "outputs": [],
      "source": [
        "#Option b. Creating a brand new query object, then running that query from the ID.\n",
        "#You might want to do this if you're planning to run the query asynchronously using create_query_task.\n",
        "new_query = sdk.create_query(body=altered_query)\n",
        "response = sdk.run_query(query_id=new_query.id, result_format=\"json\")\n",
        "print(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pQdweqa0Du0o"
      },
      "source": [
        "These methods have the same outcome. Both blocks should have returned identical output. You may want to `create_query` rather than running inline if you want to create and run asynchronous queries and poll for their results using the `create_query_task` and `query_task_results` endpoints.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4oe5RJt6DIlc"
      },
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ErxMtbN3EprF"
      },
      "source": [
        "# Congratulations! \n",
        "You've successfully altered the filters of one Look based on the results of another Look, all using the Looker API, 100% dynamically. That means you could do this programmatically for as many Looks as you like, as many times as you like, and it wouldn't have to be done manually. Any time the data returned by Look #1 changes, your output from running this script will change accordingly. You can even change the Look ID for Look #1 to a brand new Look and it will adapt accordingly. \n",
        "\n",
        "Pretty cool!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XFp_GgkyEcBm"
      },
      "source": [
        "**7 // Extra Credit. Package it up**\n",
        "\n",
        "One awesome bonus step is to create a little application to make this more easily repeatable. Check this out:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pe7_WzZVFENp"
      },
      "outputs": [],
      "source": [
        "#@title Look Filterer\n",
        "#@markdown Enter a look ID (`first_look_id`) that returns one column of values you want to apply as filters to another Look (`second_look_id`).\n",
        "#@markdown The first Look must return one column only. Once you've entered the look IDs, run this block.\n",
        "\n",
        "#@markdown _👀  If you get an error, make sure you've run the earlier code blocks that initialize the Looker API and create the get_data_for_look() function._\n",
        "first_look_id = \"25\" #@param {type:\"string\"}\n",
        "second_look_id = \"26\" #@param {type:\"string\"}\n",
        "final_result_format = \"json\" #@param [\"json\", \"json_detail\", \"csv\", \"png\", \"jpg\", \"txt\", \"html\", \"md\"]\n",
        "\n",
        "\n",
        "first_look = get_data_for_look(first_look_id)\n",
        "filter_field = first_look['first_field']\n",
        "filter_values = first_look['filter_values']\n",
        "second_look = sdk.look(look_id=second_look_id)\n",
        "second_query = second_look.query\n",
        "altered_query = second_query\n",
        " \n",
        "altered_query.filters[filter_field] = filter_values\n",
        "\n",
        "altered_query.client_id = None\n",
        "altered_query.id = None\n",
        "\n",
        "response = sdk.run_inline_query(body=altered_query, result_format=final_result_format)\n",
        "\n",
        "print(f\"Results of look {second_look_id}  filtered with values from {first_look_id}:\", response)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U1Bp3GVklHay"
      },
      "source": [
        "# What's next?\n",
        "\n",
        "You've gone all the way from installation of the Looker SDK to creating a micro-application that streamlines what would otherwise be a very manual workflow. \n",
        "\n",
        "The goal of this tutorial was to familiarize you with all the basic components of the Looker API: Installing, using the provided Models and Methods to read & write data, and building scripts that chain together API calls. You should now be able to write Python scripts that leverage the Looker API, or perhaps build a Python-based application on top of Looker data.\n",
        "\n",
        "A note: This tutorial emphasizes clarity & simplicity over elegance of code. For a very elegant and robust example of a similar function, with typing and better error handling, check out the [run_look_with_filters example](https://github.com/looker-open-source/sdk-codegen/blob/main/examples/python/run_look_with_filters.py).\n",
        "\n",
        "Go forth and create great things! If you've got questions, ask them on the [Looker Community Forums](https://community.looker.com/looker-api-77).\n",
        "\n",
        "**💥 Oh yeah. Go back up to the top and delete your API keys from that code block.**"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "Looker Python SDK Tutorial",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}