<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Luka Fontanilla on Medium]]></title>
        <description><![CDATA[Stories by Luka Fontanilla on Medium]]></description>
        <link>https://medium.com/@lukafontanilla?source=rss-fd1a6ceb3708------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*t2ec6RVkvW7obEgm.jpg</url>
            <title>Stories by Luka Fontanilla on Medium</title>
            <link>https://medium.com/@lukafontanilla?source=rss-fd1a6ceb3708------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 03:46:29 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@lukafontanilla/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Looker MCP in Gemini Enterprise]]></title>
            <link>https://lukafontanilla.medium.com/looker-mcp-in-gemini-enterprise-f45e94cfd224?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/f45e94cfd224</guid>
            <category><![CDATA[google-gemini]]></category>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[gemini-enterprise]]></category>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Wed, 10 Jun 2026 17:47:50 GMT</pubDate>
            <atom:updated>2026-06-10T17:47:50.153Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tb82wJdvnzkd7N_NAENWOQ.jpeg" /></figure><p>For engineering teams looking to unify LLM capabilities with live business intelligence, integrating Looker via the Model Context Protocol (MCP) is the bridge you need. I’ve found this setup is key to turning Gemini Enterprise (GE) into a functional agent that can fetch real-time metrics and audit instance health directly from Looker.</p><p><strong><em>Note: </em></strong><em>At the time of writing both the Custom MCP Data Store in GE &amp; Looker MCP are in Preview.</em></p><h3>Why this matters for Engineering</h3><p>In my experience, moving to a live data model solves the biggest headache with LLMs: stale data. By deploying the Looker MCP server, you get:</p><ul><li><strong>Live Data Access</strong>: Gemini pulls metrics from Explores, Looks, or Dashboards instead of relying on training data.</li><li><strong>Health Monitoring</strong>: You can automate auditing for connection status and performance.</li><li><strong>Unstructured + Structured Data Insights</strong>: Looker’s MCP server will live alongside your other connectors like Google Drive and Gemini can orchestrate and synthesize information from both in a single prompt.</li></ul><h3>Phase 1: The OAuth Piece</h3><p>The integration utilizes PKCE (Proof Key for Code Exchange), so you don’t need a client secret. This keeps things secure without the extra overhead.</p><ol><li>Go to the Looker API Explorer -&gt; OAuth Registration endpoint for your instance.</li><li>Set the client_guid to <strong>looker-mcp-ge</strong>.</li><li>Apply the following configuration body:</li></ol><pre>{<br>&quot;redirect_uri&quot;: &quot;https://vertexaisearch.cloud.google.com/oauth-redirect&quot;,<br>&quot;display_name&quot;: &quot;GE &amp; Looker MCP&quot;,<br>&quot;description&quot;: &quot;OAuth Client for use in GE&quot;,<br>&quot;enabled&quot;: true<br>}</pre><p>4.<strong> Optional</strong>: Pre-activate the OAuth App User by providing your Looker ID at the Activation URL to ensure the client_guid is correctly associated. This will pre-approve the consent flow for the registered user, however this is not required.</p><h3>Phase 2: Data Store Config</h3><p>Once OAuth is set, you configure the data store in GE. You’ll need your instance URI and the specific auth parameters for PKCE support. I’ve found that even though GE asks for a client secret, you can just set it to ‘none’ since PKCE doesn’t use it.</p><h3>Configuration Variables</h3><p>Use the following parameters, replacing &lt;looker-instance&gt; with your specific domain:</p><ul><li><strong>Instance URI</strong>: https://&lt;looker-instance&gt;/mcp</li><li><strong>Auth URI</strong>: https://&lt;looker-instance&gt;/auth</li><li><strong>Auth Params</strong>: &amp;response_type=code&amp;code_challenge_method=S256</li><li><strong>Token URI</strong>: https://&lt;looker-instance&gt;/api/token</li><li><strong>Client ID</strong>: looker-mcp-ge</li><li><strong>Client Secret</strong>: Set to ‘none’ (Required field by GE, though unused in PKCE)</li><li><strong>Scopes</strong>: cors_api</li><li><strong>PKCE Support</strong>: true</li></ul><h3>Phase 3: Agent Tooling</h3><p>This is where the real utility happens. You need to define the operational boundaries.</p><h3>Agent Instructions</h3><p>I’ve included sample description and instructions below, all you need to know is that these help Gemini understand what your server does and when and how to use it.</p><h3>MCP Server Description</h3><pre>Looker Agent with API access to various aspects of Looker. Primarily used to fetch data and run dashboards from Looker.</pre><h3>MCP Server Agent Instructions</h3><pre>Use this server exclusively to interact with the Looker instance for monitoring health, retrieving data &amp; lookml, and listing dashboards &amp; looks.<br>Invoke the Looker MCP server for the following request categories:<br>- Instance Health Checks: Auditing connection status, uptime, performance metrics, or system errors.<br>- Analytics Queries &amp; Data Retrieval: Fetching live business metrics from Explores, Looks, or Dashboards.<br>Strict Constraints:<br>- No Local Estimation: Do not guess. Always fetch real-time data via MCP tools.<br>- Fallback Behavior: Inform the user of connectivity failures immediately.</pre><h3>Enabling Actions</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/914/1*wGDd_qH9gmEUc9GN8CuL4A.png" /></figure><p>After defining instructions, navigate to the Actions tab in the Looker Custom MCP Data Store. You must manually toggle the specific tools (e.g., get_dashboards, run_inline_query, get_lookml_tests) you want Gemini to access. This step is required for GE to discover the tools as executable functions rather than standard indexed data.</p><h3>Finalization</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LwaIzqv32GZsAbFEAegJ5Q.png" /></figure><p>Authorize the connector in the Gemini Enterprise console. This binds the OAuth flow and you’re good to go!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f45e94cfd224" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Interact with Looker Conversational Analytics Agents via A2A]]></title>
            <link>https://lukafontanilla.medium.com/interact-with-looker-conversational-analytics-agents-via-a2a-21e8aa71ba9d?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/21e8aa71ba9d</guid>
            <category><![CDATA[google-adk]]></category>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[a2a-protocol]]></category>
            <category><![CDATA[conversational-ai]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Fri, 05 Jun 2026 23:06:58 GMT</pubDate>
            <atom:updated>2026-06-05T23:06:58.468Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9oI1MT5HPSkdoKlU-Hn92A.jpeg" /></figure><p>We recently launched an integration for publishing Looker CA Agents to Gemini Enterprise, and behind the scenes, these run as A2A agents pointing directly to a native Looker API endpoint.</p><p>If your organization uses a homegrown solution but you still need access to Looker’s Semantic Layer, you can completely skip building custom wrappers from scratch. You can easily reuse the CA Agents already sitting in your Looker instance and talk to them from any client using the A2A protocol and the Google Python ADK.</p><p>To make this easier, I’ve put together <a href="https://github.com/lkrdev/looker-ca-a2a-client-sample">a sample A2A Client </a>exposed as a CLI for local development. Here’s a quick look at how the underlying architecture breaks down and how you can get it spinning locally.</p><h3>1. Anatomy of an Agent Card</h3><p>We use ADK’s Python SDK to connect to a remote Looker A2A Agent. The SDK relies on a structured configuration file called an <strong>Agent Card</strong>. This JSON file acts as a descriptive profile for the remote agent, mapping out its location, capabilities, and required security layout. Looker’s A2A Agent Endpoint doesn’t natively serve this Agent Card at <strong>/.well-known/agent.json </strong>so we need to define it in the A2A client.</p><p>A standard Agent Card comprises four core structural areas:</p><ul><li><strong>Identity &amp; Endpoint (</strong>name<strong>, </strong>url<strong>)</strong>: Uniquely identifies the agent and points directly to the Looker instance API path containing your specific Agent UUID.</li><li><strong>Capabilities &amp; Modes</strong>: Explicitly declares features like streaming: true and defines the supported MIME types (such as text/plain) for input and output.</li><li><strong>Skills</strong>: A descriptive array outlining what the agent can do (e.g., conversational_analytics), providing downstream orchestration layers with context about the agent’s utility.</li><li><strong>Security Schemes</strong>: Outlines how the client must prove its identity. For rapid prototyping or backend scripting, this is typically configured to expect a standard HTTP Bearer token.</li></ul><h3>2. Decoupling the ADK Client Logic</h3><p>The execution pipeline inside the application code leverages ADK constructs to handle the translation between raw user text and streamed Looker responses. Instead of wrestling with low-level JSON-RPC protocol details, the application establishes a clean multi-step pipeline:</p><ul><li><strong>Scoped Transport Layer</strong>: An asynchronous HTTPX client is initialized and pre-loaded with the necessary Looker authorization headers, ensuring every subsequent network call is securely signed.</li><li><strong>Agent Initialization</strong>: The ADK’s RemoteA2aAgent class consumes the Agent Card profile and standardizes the communication interfaces.</li><li><strong>Session &amp; Context Isolation</strong>: An ADK in-memory session manager instantiates unique data wrappers (InvocationContext) to cleanly isolate different user conversations and track conversational history. <em>Note: we use the in-memory session manager for simplicity but in production it is highly recommended to use Gemini Enterprise Agent Platform’s managed service for this.</em></li><li><strong>Stream Execution</strong>: The client triggers the remote execution engine, returning an asynchronous generator that yields events as Looker processes the query in real time.</li></ul><h3>3. Testing the Integration Locally</h3><p>The fastest way to validate your A2A setup is by spinning up the provided command-line interface tool. This allows you to verify token authorization and ensure Looker can correctly parse your natural language prompts.</p><h3>Prep Your Environment</h3><p>Before running the client, duplicate the .env_example file into a local .env file and insert a valid, short-lived Looker API access token:</p><pre>LOOKER_A2A_TOKEN=&quot;your-fetched-bearer-token-here&quot;</pre><p>Additionally, ensure you have opened your local agent_card.json and swapped out the template placeholders (&lt;LOOKER_URL&gt; and &lt;AGENT_ID&gt;) with your active Looker instance details.</p><h3>Execute Local Test Queries</h3><p>With your environment variables locked in, you can interact with the CLI directly. The project natively supports uv for seamless execution without manual dependency management, but standard virtual environments work just as well.</p><pre># Option A: Run instantly using uv (Recommended); use --help flag to understand cli command and additional flags<br>uv run a2a-client chat -- prompt &quot;Show me total sales for last week broken down by geography&quot;<br># Option B: Run via a standard Python virtual environment<br>python3 -m venv .venv<br>source .venv/bin/activate<br>pip install -e .<br>a2a-client chat -- prompt &quot;Show me total sales for last week broken down by geography&quot;</pre><p>When triggered, the client sets up the secure connection tunnel, sends the prompt, and pipes the streaming text chunks right into your terminal output.</p><h3>⚠️ Production Security Callout</h3><p>While using a static developer token via a .env file is perfectly fine for quick local testing, <strong>production environments must switch to an OAuth 2.0 flow with PKCE</strong> to pass end-user credentials.</p><p>When switching to a live deployment, updating your configuration to use agent_card_oauth.json serves strictly for <strong>OAuth metadata discovery</strong> — it allows the ADK to discover the proper authorization endpoints and required scopes (like cors-api). However, the card itself does not execute the authentication. <strong>Your client application is entirely responsible for handling the actual OAuth 2.0 flow</strong>, including managing user browser redirects, verifying the PKCE challenges, and executing the backend token exchange.</p><p>Once your application successfully retrieves the dynamic user token, it is injected into the ADK runtime. This architecture ensures that end-user credentials are handled securely and that Looker strictly enforces user-specific data access controls and row-level security for every conversational query.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=21e8aa71ba9d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ADK 2.0 is ]]></title>
            <link>https://lukafontanilla.medium.com/adk-2-0-is-d7ea27bcf4ee?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/d7ea27bcf4ee</guid>
            <category><![CDATA[google-adk]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[agents]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Fri, 01 May 2026 19:31:54 GMT</pubDate>
            <atom:updated>2026-05-01T19:31:54.843Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*A4-k_sI5kmrphjS4tJ_rpA.png" /></figure><p>Took some time to deep dive into the ADK 2.0 Beta Release and here are a few of my favorites.</p><h3>Graph-based Workflows</h3><p>In 2.0, the big move is to a <strong>Graph-based workflow</strong>. I personally find it both more intuitive and powerful because it lets you mix AI reasoning with deterministic code logic in a way that actually scales.</p><p>Logically, you organize the system using a Workflow graph. The edges array determines the execution path — it’s essentially a roadmap for your data. A simple sequential example:</p><pre>Workflow(<br>  name=&quot;sequential_workflow&quot;,<br>  edges=[(&quot;START&quot;, task_A_node, task_B_node)],<br>)</pre><p>In my experience, the benefits really show up when the system gets “messy.” For a recent client project, I built an <strong>autonomous data research agent</strong>. A user asks to investigate a KPI (like CAC), and the graph handles the “dirty work”:</p><ol><li>Identifies the Looker data models.</li><li>Generates a dynamic list of research questions.</li><li><strong>Fans out</strong>: Spawns an agent for <em>each</em> question to query and analyze in parallel.</li><li><strong>Fans in</strong>: Synthesizes everything into a cloud-stored executive summary.</li></ol><p>With the graph-based workflow, this was straightforward and — crucially — <strong>readable</strong>.</p><pre>Workflow(<br>  name=&quot;data_research_pipeline&quot;,<br>  edges=[<br>    (&quot;start-research&quot;, generate_questions_node),<br>    (generate_questions_node, execute_workers_node),<br>    (execute_workers_node, synthesize_results_node),<br>  ]<br>)</pre><h3>Nodes: Stop burning tokens</h3><p>Nodes in ADK 2.0 can be Agents, Tools, or just pure Python functions. In the past, you often ended up wrapping simple code in an Agent just to get it into the flow, creating a custom Agent runner, or hijacking callbacks.</p><p>Now, if you want a code-only step, you just use the @node decorator:</p><pre>@node(name=&quot;hello_node&quot;)<br>def my_function_node(node_input: Any):<br>  # No LLM, no tokens, just code.<br>  return &quot;Hello world&quot;</pre><h3>Dynamic Workflows (The real “Pro” feature)</h3><p>If a static graph is too rigid for you, <strong>Dynamic Workflows</strong> give you full programmatic control. You can use loops, if statements, and async/await just like standard Python.</p><p>The secret sauce here is the <strong>ctx</strong> (Context) object. You use <strong>ctx.run_node()</strong> to trigger other nodes. What’s cool is that ADK handles the checkpointing for you — if your loop fails halfway through, it knows where to pick back up.</p><p>Here’s how I handled that “parallel fan-out” for the research questions:</p><pre>@node(rerun_on_resume=True)<br>async def execute_workers_node(ctx: Context, node_input: dict):<br>  questions = node_input[&quot;questions&quot;]<br>  # We create a list of tasks using run_node<br>  tasks = [ctx.run_node(run_worker, node_input={&quot;q&quot;: q}) for q in questions]<br>  # Standard asyncio to run them all at once<br>  results = await asyncio.gather(*tasks)<br>  return {&quot;results&quot;: results}</pre><p>I’ve found this pattern — dynamic fan-out to <strong>N</strong> workers and a clean fan-in — to be incredibly robust in 2.0. It feels very ergonomic.</p><p>Check out the<a href="https://adk.dev/2.0/"> 2.0 docs</a> release— it’s well worth the read. Happy dev’ing!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d7ea27bcf4ee" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Gemini Computer Use & Playwright]]></title>
            <link>https://lukafontanilla.medium.com/gemini-computer-use-playwright-dc1c41418dbe?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/dc1c41418dbe</guid>
            <category><![CDATA[playwright-automation]]></category>
            <category><![CDATA[gemini]]></category>
            <category><![CDATA[computer-use-agent]]></category>
            <category><![CDATA[google-adk]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Fri, 27 Feb 2026 19:41:27 GMT</pubDate>
            <atom:updated>2026-02-27T19:41:27.152Z</atom:updated>
            <content:encoded><![CDATA[<p>Wrote this document to outline the use of Playwright (<em>browser testing library</em>) with the Gemini Computer Use model. Specifically explaining the structure and behavior of the Playwright tool calls enacted by the Computer Use Agent (<em>gemini-2.5-computer-use-preview-10–2025</em>).</p><p>I’m following the sample demo the ADK team has provided here -&gt; <a href="https://github.com/google/adk-python/tree/main/contributing/samples/computer_use">https://github.com/google/adk-python/tree/main/contributing/samples/computer_use</a></p><h3><strong>Agent Interaction Loop</strong></h3><p>When the agent operates the browser to complete a user task, it follows a standard Observation-Thought-Action loop:</p><p>1. <strong>LLM <em>generateContent</em> Call</strong>: The agent sends the current state to the LLM. The LLM processes the prompt, its own internal thoughts, and previous tool outputs, reasoning about the next logical computer action.</p><p>2. <strong>Tool Invocation</strong>: The LLM issues a function call matching one of the Playwright tools (e.g., <em>click_at</em>, <em>type_text_at</em>).</p><p>3. <strong>Playwright Execution</strong>: The <em>PlaywrightComputer</em> underlying the <em>ComputerUseToolset</em> performs the action natively via Playwright APIs (mouse clicks, keyboard sequences, JavaScript execution).</p><p>4. <strong>Tool Response</strong>: After the action finishes and the page’s load state is settled, the tool grabs the overall context by calling <em>current_state()</em>. This returns a <em>ComputerState</em> object to the LLM containing:</p><ul><li><em>screenshot</em>: A Base64-encoded PNG representation of the screen’s updated viewport.</li><li><em>url</em>: The browser’s active URL string.</li></ul><p>The agent uses this <em>screenshot</em> and <em>url</em> to verify whether its action succeeded before proposing the next step.</p><h3><strong>Playwright Tool Operations</strong></h3><p>In this demo, the <em>PlaywrightComputer</em> class exposes a variety of methods for navigating and manipulating the DOM. Virtually all actions automatically return the newest <em>ComputerState</em>.</p><h4><strong>Navigation Tools</strong></h4><ul><li><strong><em>open_web_browser()</em></strong>: Initializes or resets the environment, returning the base computer state.</li><li><strong><em>navigate(url: str)</em></strong>: Commands Playwright’s <em>page.goto(url)</em> to open a specific webpage.</li><li><strong><em>search()</em></strong>: Navigates to the session-configured default search engine.</li><li><strong><em>go_back()</em> / <em>go_forward()</em></strong>: Triggers browser history traversal.</li></ul><h4><strong>Interaction Tools</strong></h4><ul><li><strong><em>click_at(x: int, y: int)</em></strong>: Places a visual highlight at the target coordinates, fires <em>page.mouse.click(x, y)</em>, and awaits document load.</li><li><strong><em>hover_at(x: int, y: int)</em></strong>: Triggers <em>page.mouse.move(x, y)</em> to activate CSS hover states or Javascript tooltips.</li><li><strong><em>type_text_at(x: int, y: int, text: str)</em></strong>: Combines clicking the target input, optionally clearing the input via <em>Control+A</em> + <em>Delete</em>, and using <em>page.keyboard.type(text)</em> to submit strings. Pressing Enter at the end can be toggled via arguments.</li><li><strong><em>key_combination(keys: list[str])</em></strong>: Presses a combination of raw inputs mapped directly to canonical Playwright keys (e.g., <em>[‘Shift’, ‘Tab’]</em> or <em>[‘Control’, ‘C’]</em>).</li><li><strong><em>drag_and_drop(x: int, y: int, destination_x: int, destination_y: int)</em></strong>: Mouses to the origin, fires <em>mouse.down()</em>, moves to the destination, and fires <em>mouse.up()</em>.</li></ul><h4><strong>Scrolling Tools</strong></h4><ul><li><strong><em>scroll_document(direction)</em></strong>: Broadest scroll implementation, mimicking <em>PageUp</em>/<em>PageDown</em> keyboard patterns for vertical scrolls, or executing internal JavaScript <em>window.scrollBy</em> for horizontal shifts.</li><li><strong><em>scroll_at(x: int, y: int, direction, magnitude: int)</em></strong>: Mouses to a precise coordinate and fires <em>mouse.wheel(dx, dy)</em> to manage targeted scroll containers (like a modal or sidebar).</li></ul><h4><strong>Verification</strong></h4><ul><li><strong><em>wait(seconds: int)</em></strong>: Triggers an <em>asyncio.sleep</em> to handle dynamically rendering/AJAX-heavy sites, returning the updated state afterwards.</li><li><strong><em>current_state()</em></strong>: At any time, takes a <em>png</em> representation of the page, grabbing both the screenshot chunk and the URL.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dc1c41418dbe" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Looker x BQML: Building a Semantic Dashboard Recommendation Engine]]></title>
            <link>https://lukafontanilla.medium.com/looker-x-bqml-building-a-semantic-dashboard-recommendation-engine-5566ff60206e?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/5566ff60206e</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[recommendation-system]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Mon, 01 Dec 2025 19:39:05 GMT</pubDate>
            <atom:updated>2025-12-01T19:39:05.663Z</atom:updated>
            <content:encoded><![CDATA[<h3>Overview</h3><p>Do your Looker users struggle to find the right dashboards? In large organizations with hundreds of dashboards, traditional keyword searches often fall short, leaving users frustrated and wasting time.</p><p>This article presents a practical “zero-to-one” solution: a Looker dashboard recommendation engine powered by BigQuery ML (BQML) embeddings.</p><p>In this tutorial, we will implement a <strong>“Dense Search”</strong> strategy. We will take a “naive” approach by feeding the full dashboard JSON (titles, descriptions, and queries) into an embedding model. This is excellent for capturing the <em>semantic meaning</em> of a dashboard — allowing users to find “Sales” dashboards even if they search for “Revenue.”</p><p>However, we will also discuss why — for a production-grade system — you should eventually upgrade this to a <strong>Hybrid Search</strong> (combining this Dense model with Keyword search) to capture specific technical metadata like column names.</p><h3>Building Your Looker Dashboard Recommendation Engine: A High-Level Overview</h3><p>The Looker Dashboard Recommendation Engine provides a powerful way to improve content discovery. It’s based on a straightforward process involving Looker, BigQuery, and BQML. Here’s a summary of the key steps:</p><ol><li><strong>Gather Target Content:</strong> Identify the Looker folders containing the dashboards you want to make searchable.</li><li><strong>Retrieve Dashboard IDs:</strong> Use Looker’s search_dashboards() API endpoint to retrieve a list of Dashboard IDs.</li><li><strong>Extract and Store Metadata:</strong> For each dashboard, extract relevant metadata. <strong>Note:</strong> We will serialize the entire JSON object to generate a comprehensive “dense” embedding.</li><li><strong>Generate Embeddings with BQML:</strong> Use BQML to generate vector embeddings from the dashboard metadata.</li><li><strong>Implement Vector Search:</strong> Craft a simple SQL query to perform a vector search against the embeddings table.</li><li><strong>Integrate with Looker:</strong> Connect the SQL query to a LookML model or an LLM-powered chat interface. Check out our <a href="https://demogoogle.y3ti.com/demo/lookerapp">Embedded Analytics App Template demo</a> and the gif below to get a preview of this in action!</li></ol><figure><img alt="AI Agent Report Recommender visual" src="https://cdn-images-1.medium.com/max/999/1*fDFr2XKUV3VXrpwBAC_Enw.png" /></figure><p>With these steps, your users can search for dashboards using natural language, finding relevant content based on meaning, not just keywords.</p><p>[Continue to the tutorial for a detailed, step-by-step implementation guide.]</p><h3>1.Prerequisites &amp; Setup</h3><p>Now to get started with the tutorial, you’ll need a few things:</p><ul><li>A Colab notebook</li><li>A Looker instance</li><li>Access/credentials to a GCP Project with the BigQuery API turned on</li><li>GCP IAM for creating a dataset, tables and creating &amp; executing a BigQuery Remote Connection leverage resources on Vertex</li></ul><p>Once those are secured, start by filling in the required variables (you can change the defaults if you’d like):</p><pre>PROJECT_ID = &quot;&quot; #@param {type: &quot;string&quot;} Replace with your Google Cloud project ID<br>DATASET_ID = &quot;&quot; #@param {type: &quot;string&quot;} Replace with your BigQuery dataset ID<br>TABLE_ID = &quot;dashboard_metadata&quot; #@param {type: &quot;string&quot;} Replace with your BigQuery table ID that will store the dashboard metadata<br>EMBEDDINGS_TABLE_ID = &quot;dashboard_metadata_embeddings&quot; #@param {type: &quot;string&quot;} Replace with your BigQuery table ID that will store the dashboard metadata embeddings<br>REGION = &quot;us&quot; #@param {type: &quot;string&quot;} REgion for Remote Connection deployment<br>BQML_REMOTE_CONNECTION_NAME = &quot;dashboard_recommendation_system&quot; #@param {type: &quot;string&quot;} BQML Remote Connection<br>MODEL_NAME = &quot;dashboard_embeddings&quot; #@param {type: &quot;string&quot;} BQML embeddings remote model name</pre><p>Assuming you are following along in a Colab Environment, you’ll need to authenticate that notebook to your GCP project:</p><pre>from google.colab import auth<br>auth.authenticate_user()</pre><pre>!gcloud auth login</pre><p>Instantiate the BigQuery client</p><pre>import google.cloud.bigquery as bigquery<br>client = bigquery.Client(project=PROJECT_ID)</pre><p>Instantiate the Looker SDK</p><ul><li>You’ll need a `looker.ini` file mounted to your Colab Notebook. Please see <a href="https://github.com/looker-open-source/sdk-codegen/blob/main/looker-sample.ini">Looker’s API documentation</a> on the format required here.</li><li>You can use config headers to delineate between environments if you plan on running this for multiple Looker instances with different credentials.</li></ul><pre>import looker_sdk<br>sdk = looker_sdk.init40(&#39;./looker.ini&#39;)</pre><h3>2.Search Looker Folders &amp; Save Dashboard ID’s</h3><p>Now that the Looker SDK is instantiated, let’s find our content. Our first step is to identify where and what. <strong>Where</strong> do we want to search for content, in a specific folder, across all folders, across multiple instances. <strong>What</strong> do we want returned, only dashboards, only looks, both? In this tutorial we are focused on two search criteria: <em>folders that exist in the parent folder, and dashboards that live in that folder.</em></p><p><strong><em>To note</em></strong> you’ll want to adjust the search filters if the criteria doesn’t match your own. Please refer to Looker’s API documentation on this. Additionally, we are running these calls as a single user on the Looker instance(s). Users likely don’t have full access permissions to every single dashboard on an instance. You’ll need to modify this to incorporate additional workflows like fetching content metadata accesses if the recommendation engine needs user specific access controls.</p><pre>folders = sdk.search_folders(parent_id=1,fields=&quot;id,name,dashboards&quot;)</pre><p>This will return us a list of folders with the given fields specified. Next, to search for dashboard content in each:</p><pre>folder_ids = &quot;,&quot;.join(map(lambda folder: folder[&quot;id&quot;], folders))<br><br>dashboards = sdk.search_dashboards(<br>    folder_id=folder_ids,<br>)</pre><h3>3.Extract Dashboard Metadata for Embeddings</h3><p>To make our recommendation engine useful, we need to extract descriptive fields.</p><p><strong>The “Naive” Approach:</strong> In this step, we are taking a “kitchen sink” approach. We will extract titles, descriptions, query fields, and filters, and bundle them into a single JSON object. This is a <strong>dense embedding strategy</strong>. It relies on the AI model to understand the relationship between all these fields implicitly.</p><pre>import json<br>import concurrent.futures  # For parallel processing<br>from google.cloud import bigquery<br><br>def extract_relevant_metadata(dashboard_json):<br>    &quot;&quot;&quot;<br>    Extracts relevant metadata from a Looker dashboard JSON object for generating embeddings.<br><br>    Args:<br>        dashboard_json (dict): A dictionary representing the Looker dashboard JSON.<br><br>    Returns:<br>        dict: A dictionary containing the extracted metadata, suitable for embedding.<br>    &quot;&quot;&quot;<br><br>    extracted_data = {<br>        &quot;dashboard_title&quot;: dashboard_json.get(&quot;title&quot;, &quot;&quot;),<br>        &quot;dashboard_description&quot;: dashboard_json.get(&quot;description&quot;, &quot;&quot;),<br>        &quot;dashboard_id&quot;: dashboard_json.get(&quot;id&quot;, &quot;&quot;),<br>        &quot;elements&quot;: []<br>    }<br><br>    for element in dashboard_json.get(&quot;dashboard_elements&quot;, []):<br>        element_data = {<br>            &quot;element_title&quot;: element.get(&quot;title&quot;, &quot;&quot;),<br>            &quot;element_type&quot;: element.get(&quot;type&quot;, &quot;&quot;),<br>            &quot;body_text&quot;: element.get(&quot;body_text&quot;, &quot;&quot;),<br>            &quot;query_fields&quot;: [],<br>            &quot;query_filters&quot;: [],<br>            &quot;query_model&quot;: None,<br>            &quot;query_view&quot;: None,<br>            &quot;listen_filters&quot;: []<br>        }<br><br>        if element.get(&quot;query&quot;):<br>            query = element[&quot;query&quot;]<br>            element_data[&quot;query_fields&quot;] = query.get(&quot;fields&quot;, [])<br><br>            # Extract model and view information<br>            element_data[&quot;query_model&quot;] = query.get(&quot;model&quot;, None)<br>            element_data[&quot;query_view&quot;] = query.get(&quot;view&quot;, None)<br><br>            # Extract filter information<br>            filters = query.get(&quot;filters&quot;, {})<br>            element_data[&quot;query_filters&quot;] = [f&quot;{field}: {value}&quot; for field, value in filters.items()] if filters else []<br><br>        if element.get(&quot;result_maker&quot;):<br>            result_maker = element[&quot;result_maker&quot;]<br>            if result_maker.get(&quot;filterables&quot;):<br>                filterables = result_maker.get(&quot;filterables&quot;)<br>                for filterable in filterables:<br>                    if filterable.get(&quot;listen&quot;):<br>                        listens = filterable.get(&quot;listen&quot;)<br>                        for listen in listens:<br>                            element_data[&quot;listen_filters&quot;].append(listen.get(&quot;dashboard_filter_name&quot;, None))<br><br>        extracted_data[&quot;elements&quot;].append(element_data)<br><br>    return extracted_data<br><br><br>def process_dashboard(dashboard_json):<br>    &quot;&quot;&quot;Processes a single dashboard JSON and extracts metadata.<br><br>    This function is designed to be used as a worker in a parallel processing pool.<br>    &quot;&quot;&quot;<br>    try:<br>        return extract_relevant_metadata(dashboard_json)  # Return as a Python dictionary<br>    except Exception as e:<br>        print(f&quot;Error processing dashboard: {e}&quot;)<br>        return None  # Or handle the error as needed<br><br>def insert_into_bigquery(data_list, project_id, dataset_id, table_id):<br>    &quot;&quot;&quot;Inserts a list of extracted metadata dictionaries into a BigQuery table.&quot;&quot;&quot;<br><br>    try:<br><br>      client = bigquery.Client(project=project_id)<br>      table_ref = client.dataset(dataset_id).table(table_id)<br><br>      # Define the schema for the table (one column of type JSON)<br>      schema = [<br>          bigquery.SchemaField(&quot;metadata&quot;, bigquery.enums.StandardSqlTypeNames.JSON),<br>      ]<br><br>      # Prepare the rows to insert<br>      rows_to_insert = [{&quot;metadata&quot;: data} for data in data_list]  # Wrap in a dict with column name<br><br>      errors = client.insert_rows(table_ref, rows_to_insert, selected_fields=schema)  # Make an API request, pass the schema<br><br>      if errors:<br>          print(f&quot;Encountered errors while inserting rows: {errors}&quot;)<br>      else:<br>          print(f&quot;Successfully inserted {len(data_list)} rows into {project_id}.{dataset_id}.{table_id}&quot;)<br>    except Exception as e:<br>        print(f&quot;Error inserting into BigQuery: {e}&quot;)<br><br><br>def main():<br>    &quot;&quot;&quot;Main function to process multiple dashboards in parallel and insert into BigQuery.&quot;&quot;&quot;<br><br>    # --- Parallel Processing ---<br>    with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:  # Adjust max_workers as needed<br>        results = list(executor.map(process_dashboard, dashboards))<br><br>    # Filter out any None results (due to errors in processing)<br>    extracted_metadata_list = [r for r in results if r is not None]<br><br>    # --- Insert into BigQuery ---<br>    insert_into_bigquery(extracted_metadata_list, PROJECT_ID, DATASET_ID, TABLE_ID)The majority of this code is focused on parsing the dashboard metadata objects and extracting the information we want to be included in the dashboard embedding. We use some parallel processing techniques in case the volume of dashboards is very high and finally insert this data into the BigQuery table specified.</pre><p><em>To note, we only generate a single column in this table, “metadata” which is a json object. You may want to include things like instance name, content access user id’s, etc. if you want to support multiple instances or existing access controls.</em></p><pre>main()</pre><p>Here we run our function and should be able to preview our BigQuery table.</p><h3>4.Create BigQuery Remote Connection &amp; BQML Embeddings Model</h3><p>To create our embeddings from SQL in BigQuery, we need to first establish a BigQuery Remote Connection and generate a BQML Embeddings Model. If you aren’t familiar you can think of Remote Connections in BigQuery as a way for BigQuery, via SQL, to access and make requests to external services like Vertex OR the Google Maps Platform API’s for example.</p><pre>!bq mk - connection - location=REGION - project_id=PROJECT_ID \<br> - connection_type=BQML_REMOTE_CONNECTION_NAME</pre><p>Here we are using the BQ command line utility to make the request to create the connection. Now run the below command to return the Remote Connection Service Account.</p><pre>!bq show - connection PROJECT_ID.REGION.BQML_REMOTE_CONNECTION_NAME</pre><p>And grant that service account the “Vertex AI User” IAM role to your GCP Project.</p><p>Now once that is done. We can create the BQML Embeddings Model. Here we are using the “text-embedding-004” version. But this could be a custom Embeddings model you have deployed on Vertex in which case, please supply the resource path.</p><pre>bqml_model_creation_query = f&#39;&#39;&#39;<br>CREATE OR REPLACE MODEL `{PROJECT_ID}.{DATASET_ID}.{MODEL_NAME}`<br>REMOTE WITH CONNECTION `{REGION}.{BQML_REMOTE_CONNECTION_NAME}`<br>OPTIONS(ENDPOINT = &#39;text-embedding-004&#39;)<br>&#39;&#39;&#39;<br>model_creation_job = client.query(bqml_model_creation_query) # API request<br>model_creation_job.result() # Waits for query to finish</pre><h3>5.Create the Embeddings Table &amp; Optionally Deploy Vector Search Index</h3><p>We now generate embeddings for our dashboard metadata.</p><p>Because we are using the <strong>naive dense approach</strong>, we use TO_JSON_STRING(metadata) to turn the entire metadata object into a single string of text. The model will read this string and create a vector based on the overall context.</p><p>In this tutorial we will be doing this process one time, but you may need to schedule this query if there will be a constant stream of new reports getting extracted and updated in the underlying tables.</p><pre>embeddings_creation_query = f&#39;&#39;&#39;<br>CREATE OR REPLACE TABLE `{PROJECT_ID}.{DATASET_ID}.{EMBEDDINGS_TABLE_ID}` AS (<br>SELECT *<br>FROM<br>ML.GENERATE_EMBEDDING(<br>MODEL `{PROJECT_ID}.{DATASET_ID}.{MODEL_NAME}`,<br>(SELECT TO_JSON_STRING(metadata) as content FROM `{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}`),<br>STRUCT(TRUE AS flatten_json_output, &#39;SEMANTIC_SIMILARITY&#39; as task_type)<br>)<br>);<br>&#39;&#39;&#39;<br>embeddings_creation_job = client.query(embeddings_creation_query) # API request<br>embeddings_creation_job.result() # Waits for query to finish</pre><p>One thing you may note here is that the “task_type” we use is the “SEMANTIC_SIMILARITY” type. You can read more about each <a href="https://cloud.google.com/vertex-ai/generative-ai/docs/embeddings/task-types#supported_task_types">task type here</a> and evaluate which makes the most sense for your given use case.</p><p>Optionally, if the output embeddings table is greater than 5000 rows we may want to generate a Vector Index. I am going to assume that most of us Looker instance Admins don’t maintain instances with over 5000 production dashboards, but if you are one such rare instance owner, run the below and optimize your Vector Search queries with an index.</p><pre>vector_index_creation_query = f&#39;&#39;&#39;<br>CREATE VECTOR INDEX search_index ON `{PROJECT_ID}.{DATASET_ID}.{EMBEDDINGS_TABLE_ID}`(ml_generate_embedding_result)<br>OPTIONS(distance_type=&#39;COSINE&#39;, index_type=&#39;IVF&#39;, ivf_options=&#39;{&quot;num_lists&quot;: 1000}&#39;);<br>&#39;&#39;&#39;<br>vector_index_creation_job = client.query(vector_index_creation_query) # API request<br>vector_index_creation_job.result() # Waits for query to finish</pre><h3>Try an Example Query!</h3><p>Now that our Embeddings table is created, let’s leverage BQML’s <a href="https://cloud.google.com/bigquery/docs/vector-search-intro">Vector Search function</a> for a test search query.</p><pre>PROMPT = &#39;total sales by age tier&#39; # @param {type: &quot;string&quot;} user prompt<br>num_matches = 5 # @param {type: &quot;integer&quot;} number of matches to return<br><br>recommendation_query = f&quot;&quot;&quot;SELECT base.content<br>FROM VECTOR_SEARCH(<br>TABLE `{PROJECT_ID}.{DATASET_ID}.{EMBEDDINGS_TABLE_ID}`,<br>&#39;ml_generate_embedding_result&#39;,<br>(<br>SELECT ml_generate_embedding_result, content AS query<br>FROM ML.GENERATE_EMBEDDING(<br>MODEL `{PROJECT_ID}.{DATASET_ID}.{MODEL_NAME}`,<br>(SELECT &#39;{PROMPT}&#39; AS content)<br>)<br>),<br>top_k =&gt; {num_matches}, options =&gt; &#39;{&quot;fraction_lists_to_search&quot;: 1.0}&#39;<br>)&quot;&quot;&quot;<br><br>query_job = client.query(recommendation_query) # API request<br>rows = query_job.result() # Waits for query to finish<br><br>dashboards=[]<br>for row in rows:<br>  dashboards.append(row.get(&#39;content&#39;))<br>  print(&quot;Matched Dashboards: &quot;,dashboards)</pre><p>We have 2 variables here, the prompt for our search and the number of matches to return and that’s it. The rest is done in a few lines of SQL, directly in the database!</p><h3>Beyond the Tutorial: Going to Production with Hybrid Search</h3><p>The method outlined above uses <strong>Dense Search</strong>, which is fantastic for understanding intent (e.g., knowing that “Revenue” is similar to “Sales”). However, in a production environment with complex technical metadata, you may encounter limitations:</p><ol><li><strong>Technical Specificity:</strong> A dense model might struggle to distinguish between table_v1 and table_v2 if the rest of the dashboard context is identical.</li><li><strong>Exact Matching:</strong> Sometimes a user wants to find a dashboard that contains a specific field name exactly, regardless of semantic meaning.</li></ol><p><strong>The Solution: Hybrid Search</strong></p><p>For a robust production recommender, we recommend upgrading to a <strong>Hybrid Search</strong> architecture. This involves:</p><ol><li><strong>Dense Vector:</strong> The implementation we built in this tutorial (captures meaning).</li><li><strong>Sparse Index:</strong> Using BigQuery’s SEARCH index on your metadata (captures exact keywords).</li><li><strong>Reciprocal Rank Fusion (RRF):</strong> A SQL technique to combine results from both the Vector Search and the Keyword Search.</li></ol><p>By combining these two, you ensure that your users get the best of both worlds: the “smart” understanding of AI and the precision of keyword matching.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5566ff60206e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automate Database-Side Column Updates in LookML with ldeploy]]></title>
            <link>https://lukafontanilla.medium.com/automate-database-side-column-updates-in-lookml-with-ldeploy-23e5dbd73171?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/23e5dbd73171</guid>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[open-source]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Wed, 01 Feb 2023 05:16:47 GMT</pubDate>
            <atom:updated>2023-02-01T05:25:49.049Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6vOH1RmZpbVJn67EFcRmFg.jpeg" /></figure><h3>The Lay of the Land</h3><p>Sometimes your BI dashboards break. <em>Yes, a lot of us have been there and some of us more than others.</em> They run fine one second, then the next that beautiful waterfall chart right under the header that says <strong>Key Marketing Metrics</strong> throws an error. The office is in turmoil, it’s chaos and eventually a Slack comes in … “<em>Looks like there was a column rename on the database end.</em>” lol</p><p>If you are like the audience described, you know database changes happen and sometimes you fear them. If a data changelog isn’t maintained or a transparent line of communication down to the end data consumers is lacking, we as the analyst or the data consumer have to live with it and adjust, sometimes under the pressure of frustrated stakeholders, with them. Now one might ask, well how hard is it to track that change and update said column rename in the places that make sense. Short answer is, it’s more complicated then that. See lot’s of tools downstream from the database don’t have a two way sync, historically this has often been the case. In fact, one use case in the emerging field of data cataloging is set to cover this. For the analyst/data consumer, your column references are hardcoded in your BI tool. Either in some sort of proprietary modeling language OR directly in sql at the reporting layer. What happens next? Well you come to find out that engineering completed a whole initiative to introduce consistent camelcase across columns on the database end and now that specific hardcoded reference downstream is invalid.</p><p>This issue doesn’t escape analytics platforms like Looker.</p><h4>What happens in Looker?</h4><p>In Looker you define all of your business logic as repeatable governed metrics in our data model, LookML. LookML then translates that to sql through a GUI interface for query building. It’s all magic, until a database error is thrown. LookML doesn’t have a feedback loop with the database, once you hardcode that column name it’s up to you to make the necessary changes to keep your Looker model &amp; database in sync.</p><p>Fortunately, the process doesn’t have to be manual nor does it need to plague you for quarters on end until your only solace is to down boxes of Count Chocula in sorry. Thanks to some awesome folks at Looker, OSS tooling has been created to help automate this process. In specific a Python library called PyLookML.</p><p>PyLookML under the hood wraps the awesome LookML parser providing a myriad of ways to parse, write, and deploy LookML. Given our wonderful scenario of a database column name change breaking a Looker Explore lets walkthrough using PyLookML to automate our field updates.</p><h3>Programmatically Generating LookML</h3><p>PyLookML provides an effective interface for accessing LookML objects. Combined with the GitHub file system API we can very easily read our LookML project files and use object oriented programming to access each unique LookML attribute. For example, accessing the <em>sql</em> field for all dimensions in a given explore and swapping out their qualifying path.</p><p>Built in to PyLookML is a direct way to write file changes back to git and with Looker’s Advanced Deploy/webhook, bring those changes into local Looker production. <em>Ever found yourself needing to duplicate LookML project files and structure across different clients?</em> Well this is your tool for that.</p><h4>PyLookML Setup</h4><p>There are some lovely details on PyLookML in the <a href="https://pylookml.readthedocs.io/en/latest/">official docs</a>, however at a high level steps for configuration are as follows (this assumes you have some sort of Python environment already setup):</p><p>1. Install the pylookml package: <em>pip install lookml</em><br>2. Create a GitHub Access Token (to allow for read/write access to the repo).<br>3. Instantiate a <em>lookml</em> Project class.</p><pre>proj = lookml.Project(<br>   repo= process.env.PROJECT_NAME,<br>   access_token=process.env.ACCESS_TOKEN,<br>)</pre><p>Once the <em>Project</em> class is instantiated, we can begin to directly access our LookML programmatically and in this case start working towards the scenario discussed.</p><h3>Ze Code</h3><h4>Packages / Imports</h4><p>Here are the packages &amp; import am I using for this utility:</p><p><strong>packages:</strong></p><pre>lookml<br>github<br>pyyaml</pre><p><strong>imports:</strong></p><pre>import lookml<br>import re<br>from github import Github<br>import yaml<br>import argparse</pre><h4><strong>Main Execution</strong></h4><p>Before we define the meat of the utility, let’s walk through it’s execution.</p><p>We start with opening and reading a config.yml file in our main execution.</p><pre>if __name__ == &quot;__main__&quot;:<br>   with open(&#39;config.yml&#39;, &#39;r&#39;) as file:<br>   config = yaml.safe_load(file)</pre><p>All I am using the YAML file for is to store the repo name and access token for instantiating the <em>Project</em> class.</p><pre>config:<br>   repo: &#39;&lt;repo_name&gt;&#39;<br>   access_token: &#39;&lt;personal access token (PAT)&gt;&#39;</pre><p>Once we read in our <em>config.yaml</em> file, we instantiate the <em>Project</em> class and the GitHub API (for project traversal, mainly for ready access to file paths).</p><pre>repo_name = config[&#39;config&#39;][&#39;repo&#39;]<br>access_token = config[&#39;config&#39;][&#39;access_token&#39;]<br>proj = lookml.Project(<br>   repo=repo_name,<br>   access_token=access_token<br>)<br>git = Github(access_token)</pre><p>This portion of our utility leverages the <em>argparse</em> package allowing us to create a command line utility for our script. The relevant arguments include:</p><p>- <strong>views:</strong> Comma separate list of LookML views to audit (if none specified all views will be audited)<br>- <strong>old:</strong> Old SQL string to replace<br>- <strong>new:</strong> New SQL string to substitute</p><pre>repo = git.get_repo(repo_name)<br>contents = repo.get_contents(&quot;&quot;)<br>parser = argparse.ArgumentParser(description=&#39;Audit Fields for Specific View(s)&#39;)<br>parser.add_argument(&#39; - views&#39;, &#39;-v&#39;, type=str, required=False, help=&#39;Comma Separated list of views to audit&#39;)<br>parser.add_argument(&#39; - old&#39;, &#39;-o&#39;, type=str, required=True, help=&#39;Old SQL string to replace&#39;)<br>parser.add_argument(&#39; - new&#39;, &#39;-n&#39;, type=str, required=True, help=&#39;New SQL string to substitute&#39;)<br>args = parser.parse_args()</pre><p>The remaining code takes those arguments, passes them to a recursive function, that we are soon to create, then traverses the project directory making the relevant changes and finally deploys those changes.</p><pre>recurse(contents, args.views, args.old, args.new)<br># Hit Deploy URL to Update Looker<br>proj.deploy()</pre><h4>Traverse LookML Project</h4><p>The main usage of our recursive function is to loop through all the content in the returned directory (ie. LookML project), if it’s a file and it’s a view file then run a function to audit the dimensions replacing the old field name with the new. If the content is a directory then fetch the content in that directory and recurse.</p><pre>def recurse(directory, chosen_views, old, new):<br>   for content in directory:<br>     # make sure we are only checking files<br>     if content.type == &#39;file&#39;:<br>       # make sure the file checked has the .view.lkml file extension<br>       if &#39;view.lkml&#39; in content.path:<br>         audit_file_dimensions(content.path, chosen_views, old, new)<br>       else:<br>         pass<br>     else:<br>       recurse(repo.get_contents(f&#39;/{content.path}&#39;), chosen_views, old, new)</pre><h4>Audit LookML Fields</h4><p>The <em>audit_file_dimensions</em> function checks each view file, looping through each view in the view file if multiple exist (think refinements). If the utility was called with views specified, only those views will be audited. The <em>search</em> function checks the LookML attribute specified (ie. ‘sql’) and looks for any <em>sql</em> parameter that references the old column name. We then reassign those sql parameters’ values to the new column name replacement and with <em>put</em> write those files to the local Git Filesystem.</p><pre>def audit_file_dimensions(path, chosen_views, old, new):<br>  for view in proj.file(path).views:<br>    if chosen_views is not None:<br>      audit_views = chosen_views.split(&#39;,&#39;)<br>        if view.name in audit_views:<br>          for search in view.search(&#39;sql&#39;, re.escape(old)):<br>            search.sql.value = search.sql.value.replace(old, new)<br>          proj.put(proj.file(path))<br>        else:<br>          pass<br>    else:<br>      print(&#39;Auditing all views in proj.&#39;)<br>  for search in view.search(&#39;sql&#39;, re.escape(old)):<br>    search.sql.value = search.sql.value.replace(old, new)<br>  proj.put(proj.file(path))</pre><p>Once our command line utility is defined an ready, we can make simple calls out to it and the behind the scenes work of auditing your Looker project is handled for you.</p><pre># audit specific views<br>python3 audit.py -v orders,users,order_items -o salePrice -n sale_price<br># audit all views<br>python3 audit.py -o salePrice -n sales_price</pre><h3>Ending Notes</h3><p>This is only one use case that PyLookML can accomplish, things like automating LookML generation for EAV Schemas, autogenerating LookML dimensions for new columns added to the database.. PyLookML allows for LookML developers to supercharge development with an Object Oriented interface.</p><p>While this utility can prove very powerful, use it with caution. It makes the assumption that we want to replace any field with the old sql, but if you have alike named columns across multiple tables and schemas you may want to be very explicit around which LookML views are audited.</p><p>For reference, the source code is available <a href="https://github.com/LukaFontanilla/LookML-Field-Audit">here</a>! If you enjoyed or found this read helpful, feel free to check out my other postings or follow me on the <a href="https://lukafontanilla.medium.com/">Medium</a>. Until next time citizens of the earth.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=23e5dbd73171" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Looker Admin Automation Series: Bulk Disable Schedules with the API]]></title>
            <link>https://lukafontanilla.medium.com/looker-admin-automation-series-bulk-disable-schedules-with-the-api-100b43399042?source=rss-fd1a6ceb3708------2</link>
            <guid isPermaLink="false">https://medium.com/p/100b43399042</guid>
            <category><![CDATA[sdk]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[api]]></category>
            <dc:creator><![CDATA[Luka Fontanilla]]></dc:creator>
            <pubDate>Wed, 05 May 2021 23:16:24 GMT</pubDate>
            <atom:updated>2021-05-05T23:51:44.235Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1-m9xxW_N2guRGuYBlsR8w.jpeg" /></figure><p><strong>Hey Looker Community, what is going on?</strong></p><p>My name is Luka I work on the Looker Support Team (<em>aka. Department of Customer Love</em>). During my time on the support team I’ve come across a lot of scenarios where customers are looking for specific UI actions like (<em>bulk disable user/schedules, mass adding users to groups, etc.</em>) however because these are not built-in features we offer either the end result is a push to manually doing so (<em>imagine having to manually add 100’s of users to a given group</em>) or a link to our api docs with the relevant endpoints which for those not familiar with our api might seem daunting (<em>since we don’t have a whole lot of built-in examples for these</em>).</p><p>In this series, I want to address some of these limitations/common scenarios and leveraging our SDK provide examples as to how one might better automate these kinds of tasks. My hope is that this provides you, the Looker user/admin, with a nice little toolkit to save time and make certain Looker tasks more efficient.</p><p>There are a few assumptions this series makes, first is that you have access to a Looker instance (<strong>XD</strong>) and second, that the <a href="https://pypi.org/project/looker-sdk/">python sdk</a> is installed on your local machine and you have api credentials generated for your user. Given those are checked, the first workflow we will be addressing is:</p><h4><em>Bulk Disabling Schedules</em></h4><p>Bulk disabling schedules is a common task, and ask when managing a Looker application. Perhaps a recent change introduced to your LookML model is bringing through unexpected errors and these errors are being surfaced in scheduled delivery failures to users OR perhaps instance performance and slowness has been a concern and you want to bulk disable schedules to see if that will give the instance more breathing room.. however in searching around the instance and Looker documentation you find that there is no built in option for doing this besides the API.</p><p>If you haven’t used the Looker api before or are unfamiliar with the schedule related endpoints, don’t fret because this is actually a quite straightforward process. We will be using two endpoints for this method:</p><p><a href="https://docs.looker.com/reference/api-and-integration/api-reference/v3.1/scheduled-plan#get_all_scheduled_plans"><strong><em>all_scheduled_plans</em>()</strong></a><strong>:</strong></p><ul><li>all_scheduled_plans endpoint accepts a <strong>user_id</strong> (<em>defining which user schedules are to be fetched</em>), <strong>fields</strong> (<em>what fields are returned in the response body</em>), and <strong>all_users</strong> (<em>setting this to true will fetch all the schedules for all users, assuming the user has the </em><strong><em>see_schedules</em></strong><em> permission</em>) parameters</li><li>for a bulk disable of all schedules on the instance we will want to set <strong>all_users</strong> to true so that we are fetching the scheduled plan id’s of all schedules on the instance</li></ul><p><a href="https://docs.looker.com/reference/api-and-integration/api-reference/v3.1/scheduled-plan#update_scheduled_plan"><strong><em>update_scheduled_plan()</em></strong></a>:</p><ul><li>update_scheduled_plan accepts a <strong>schedule plan id</strong>, and a <strong>scheduled plan body </strong>(<em>the body is where we will be grabbing the </em><strong><em>enabled</em></strong><em> parameter and setting it to false later on in our script</em>)</li></ul><p>Alright, now let’s start coding!</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/68dbcb047a9d8a0f494ce1fe8bbb6c37/href">https://medium.com/media/68dbcb047a9d8a0f494ce1fe8bbb6c37/href</a></iframe><p>We are going to start by importing the looker_sdk module. From the looker_sdk module we are going to be importing models. Models are essentially classes for use by the sdk that define a writeable type to be consumed by the method (<em>exhaustive list of all the Python SDK models </em><a href="https://github.com/looker-open-source/sdk-codegen/blob/main/python/looker_sdk/sdk/api40/models.py"><em>here</em></a><em> in our open source repo</em>).</p><p>I am also importing the typing module for defining the types of our function arguments (<em>this is not a required step, I am solely doing so to confirm the types that are allowed for a given function input</em>). From there we are then instantiating our sdk (<em>using init40, if using v3.1 then we will want to use init31 instead</em>) by pointing that to our ini file with our <strong>client id</strong>, <strong>secret</strong>, and <strong>api host url</strong>.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f2e59864f55614130e5472bfad29a7f4/href">https://medium.com/media/f2e59864f55614130e5472bfad29a7f4/href</a></iframe><p>Here we are making our call to the all_scheduled_plans endpoint. Since all_users is set to true this will return all the scheduled plans defined on our instance. In our fields param we only really care about two things (<em>the id of the schedule, and whether it is enabled or not</em>), therefore we are narrowing down the response body by declaring that we only want those two parameters brought through.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a14adce70dd617dacd371db2ee0fe74f/href">https://medium.com/media/a14adce70dd617dacd371db2ee0fe74f/href</a></iframe><p>Since we could potentially be looping through many schedules with our automation AND given that we only care about schedules that are currently enabled, we can filter out any already disabled schedules. Here I am using a quick python list comprehension to do so.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/07bab0774a1ae7c61876c59abc8b2ac2/href">https://medium.com/media/07bab0774a1ae7c61876c59abc8b2ac2/href</a></iframe><p>Once we have our list of enabled schedule objects we can quickly loop through that list and run the update_scheduled_plan endpoint to disable each schedule. Here we are leveraging the <strong><em>WriteScheduledPlan</em></strong> model which allows us to make the types in the body writeable.</p><blockquote>Please use this approach with caution else a large amount of schedules could be unexpectedly disabled on your instance.</blockquote><p>To make this all useful we will want to incorporate this into a script which can be run as a single file. Below is my take on that:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9b387b99de5cecfaf84e13cb7421617a/href">https://medium.com/media/9b387b99de5cecfaf84e13cb7421617a/href</a></iframe><p>And that’s all there is to it! Hopefully you found this breakdown helpful and that it provides you with an admin automation you can keep in your back pocket to break out when your Looker instance needs it most. If you are interested in digging into more Admin automations, I will be following up in this series with another automation coming to you hot, <em>like a nice honey oat baked loaf of wheat bread fresh out the oven</em>, with bulk reassigning schedules.</p><p>Until Next Time.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=100b43399042" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>