<?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 Sudharma Mokashi on Medium]]></title>
        <description><![CDATA[Stories by Sudharma Mokashi on Medium]]></description>
        <link>https://medium.com/@sudharmamokashi18?source=rss-faca7cf54e0------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*b2z-s924ij_zdWsSjR7XBQ.jpeg</url>
            <title>Stories by Sudharma Mokashi on Medium</title>
            <link>https://medium.com/@sudharmamokashi18?source=rss-faca7cf54e0------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 16:57:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sudharmamokashi18/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[From Messy Data Models to Agentic Data Catalogs: Building an AI Data Architect with OKF and Gemini]]></title>
            <link>https://medium.com/google-cloud/from-messy-data-models-to-agentic-data-catalogs-building-an-ai-data-architect-with-okf-and-gemini-75a20caee9ed?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/75a20caee9ed</guid>
            <category><![CDATA[agentic-ai]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[google-okf]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Fri, 04 Sep 2026 02:58:33 GMT</pubDate>
            <atom:updated>2026-09-04T06:33:24.801Z</atom:updated>
            <content:encoded><![CDATA[<blockquote><strong>A quick reality check before we dive in:</strong> Adopting OKF does <em>not</em> mean you should throw away your enterprise data modeling tools (like dbt, erwin, or any other tool). You still need those to actually build and manage your pipelines. Instead, think of OKF as the connective tissue — a lightweight, machine-readable translation layer designed specifically to feed your existing data models into an Agentic AI.</blockquote><p>It’s 9:00 AM on a Tuesday, and a critical executive dashboard just flatlined because someone dropped a column in a Silver table, silently breaking a downstream Gold aggregation. You rush to your enterprise data catalog hoping for a quick lineage trace, only to hit a wall of stale, year-old documentation.</p><p>We’ve all been there. For data engineers, traditional catalogs are basically graveyards for metadata. They’re static, disconnected from the actual codebase, and rely on humans remembering to update wiki pages. <br>Spoiler: they never do :D.</p><p>But what if your documentation actually lived right next to your pipelines? That’s where the Open Knowledge Format (OKF) comes in. Instead of trapping our metadata in a proprietary database, we treat it strictly as code. We represent every table — Bronze, Silver, and Gold — as a simple Markdown file. Table relationships and join keys aren’t hidden in some clunky UI; they’re just standard hyperlinks.</p><p>That structure alone is a huge quality-of-life upgrade, but here’s where it gets actually fun. We’re going to hand this structured markdown over to an AI. In this post, I’ll walk you through how to turn a static repository into an “agentic” knowledge graph using the Google GenAI SDK. We’re going to build a custom AI architect that can automatically read your OKF docs, click through hyperlinks to trace complex lineage, and answer your most tangled pipeline questions in seconds.</p><p>Let’s stop writing docs that nobody reads, and build an agent to do the heavy lifting for us.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yDk6RUbCL5RXM1itv-7Nuw.png" /><figcaption>OKF</figcaption></figure><h3><strong>Step 1: Treating Metadata as Code</strong></h3><p>The first step to building an agentic catalog is getting your metadata out of walled gardens and into version control. We use the <a href="https://cloud.google.com/blog/products/data-analytics/how-the-open-knowledge-format-can-improve-data-sharing"><strong>Open Knowledge Format (OKF)</strong> </a>to define our data model.</p><p>In OKF, every table, metric, or concept is a Markdown file. The schema lives in the YAML frontmatter and standard markdown tables, while relationships are just hyperlinks.</p><p>Take a look at how we define our bronze_sales.md landing table:</p><pre>---<br>type: BigQuery Table<br>title: Bronze Raw Sales<br>resource_id: bronze_sales.data_source_bnz<br>description: Raw, unvalidated point-of-sale transaction logs landed via batch ingestion.<br>tags: [bronze, raw, ingestion, pos]<br>timestamp: 2026-06-01T00:00:00Z<br>---<br><br># Schema<br><br>| Column | Type | Description |<br>|---|---|---|<br>| `tx_id` | STRING | Raw transaction ID from POS terminal. |<br>| `raw_cust_ref` | STRING | Unstandardized customer string / card hash. |<br>| `raw_sku` | STRING | Barcode or product SKU. |<br>| `tx_amount` | NUMERIC | Total transaction cost. |<br>| `ingest_timestamp` | TIMESTAMP | Wall-clock time data landed in warehouse. |<br><br># Notes &amp; Known Quirks<br><br>* Contains duplicate records when terminals retry failed network requests.<br>* Downstream deduplication happens during transformation into [Gold Sales Facts](/gold/gold_sales_facts.md).</pre><p>silver_customers.md</p><pre>---<br>type: BigQuery Table<br>title: Silver Customers<br>resource_id: silver_ds.d_cust<br>description: Cleansed, conformed customer dimension with deduplicated master records.<br>tags: [silver, dimension, customer]<br>timestamp: 2026-06-01T00:00:00Z<br>---<br><br># Schema<br><br>| Column | Type | Description |<br>|---|---|---|<br>| `customer_id` | STRING | Primary Key. Standardized UUID. |<br>| `loyalty_tier` | STRING | Customer tier: &#39;Gold&#39;, &#39;Silver&#39;, &#39;Bronze&#39;, or &#39;Standard&#39;. |<br>| `region` | STRING | Standardized 2-character country/region code. |<br><br># Referenced by<br><br>- [Gold Sales Facts](/gold/gold_sales_facts.md) via `customer_id`</pre><p>silver_products.md</p><pre>---<br>type: BigQuery Table<br>title: Silver Products<br>resource_id: silver_ds.d_prod<br>description: Conformed product dimension table mapped to standard category hierarchies.<br>tags: [silver, dimension, product, master-data]<br>timestamp: 2026-06-01T00:00:00Z<br>---<br><br># Schema<br><br>| Column | Type | Description |<br>|---|---|---|<br>| `sku_id` | STRING | Primary Key. Standardized Global Trade Item Number (GTIN). |<br>| `category` | STRING | Product category (e.g., Beverages, Packaged Foods). |<br>| `unit_cost` | NUMERIC | Base production cost per unit in USD. |<br><br># Referenced by<br><br>- [Gold Sales Facts](/gold/gold_sales_facts.md) via `sku_id`<br>- [Gold Inventory Summary](/gold/gold_inventory_summary.md) via `sku_id`</pre><p>Notice those links at the bottom? That is our lineage. By organising our files into a simple directory structure (/bronze, /silver, /gold), we create a lightweight knowledge graph that lives right in our Git repository.</p><p>gold_inventory_summary.md</p><pre>---<br>type: BigQuery Table<br>title: Gold Inventory Summary<br>description: Daily snapshot of warehouse stock levels per product.<br>tags: [gold, aggregate, inventory, supply-chain]<br>timestamp: 2026-06-01T00:00:00Z<br>---<br><br># Schema<br><br>| Column | Type | Description |<br>|---|---|---|<br>| `snapshot_date` | DATE | Date of the snapshot. |<br>| `sku_id` | STRING | Foreign Key to [Silver Products](/silver/silver_products.md). |<br>| `stock_on_hand` | INT64 | Total physical units in fulfillment centers. |<br><br># Joins &amp; Traversal<br><br>* Join with [Silver Products](/silver/silver_products.md) on `sku_id`.<br><br># Notes<br><br>This is a separate lineage branch from [Gold Sales Facts](/gold/gold_sales_facts.md) —<br>both consume [Silver Products](/silver/silver_products.md) but track different metrics<br>(inventory levels vs. revenue).</pre><p>Note the Joins and Traversal section, where we provide the details w.r.t. joining conditions.</p><h3>Step 2: Giving the AI a “Read” Tool</h3><p>Large Language Models are incredibly smart, but they don’t inherently know your company’s proprietary database schemas. To bridge this gap, we need to give the LLM “eyes.”</p><p>In our agent.py file, we define a standard Python function that allows the LLM to fetch and read any markdown file in our bundle:</p><pre><br>import os<br>from pathlib import Path<br><br>from google import genai<br>from google.genai import types<br><br><br>BUNDLE_ROOT = Path(__file__).parent / &quot;lineage-bundle&quot;<br><br><br>def read_okf_doc(path: str) -&gt; str:<br>    &quot;&quot;&quot;Reads a single OKF (Open Knowledge Format) concept document.<br><br>    Use this to fetch table schemas, metric definitions, or any concept<br>    referenced by a markdown link inside another OKF document (e.g. a<br>    link like &#39;/gold/gold_sales_facts.md&#39;). Every link in this bundle is<br>    root-relative — always pass the path exactly as it appears in the<br>    link, leading slash included. Follow every relevant markdown link in<br>    the returned content by calling this tool again until you have<br>    traced the full chain.<br><br>    Args:<br>        path: The root-relative OKF path to the concept doc, e.g.<br>            &quot;/gold/gold_sales_facts.md&quot; or &quot;/bronze/bronze_sales.md&quot;.<br><br>    Returns:<br>        The full raw markdown content of the concept document (frontmatter<br>        + body), or an error message if the file does not exist.<br>    &quot;&quot;&quot;<br>    relative = path.lstrip(&quot;/&quot;)<br>    target = (BUNDLE_ROOT / relative).resolve()<br><br>    if BUNDLE_ROOT not in target.parents and target != BUNDLE_ROOT:<br>        return f&quot;Error: &#39;{path}&#39; resolves outside the OKF bundle.&quot;<br><br>    if not target.exists():<br>        return (<br>            f&quot;Error: no concept doc found at &#39;{path}&#39; (resolved to {target}). &quot;<br>            f&quot;Check for filename typos or mismatched links.&quot;<br>        )<br><br>    return target.read_text(encoding=&quot;utf-8&quot;)<br><br><br>client = genai.Client(api_key=os.environ[&quot;GEMINI_API_KEY&quot;])<br><br>config = types.GenerateContentConfig(<br>    system_instruction=(<br>        &quot;You are an expert Data Platform Architect. You have direct access &quot;<br>        &quot;to our data catalog via the Open Knowledge Format. Whenever a user &quot;<br>        &quot;asks about SQL queries, schemas, or lineage, use the `read_okf_doc` &quot;<br>        &quot;tool to fetch concept documents. Always follow markdown hyperlinks &quot;<br>        &quot;inside a doc&#39;s body to traverse table relationships before &quot;<br>        &quot;answering, rather than guessing. If you don&#39;t know where to start, &quot;<br>        &quot;begin by looking at &#39;/index.md&#39; for the bundle overview, or &quot;<br>        &quot;&#39;/gold/gold_sales_facts.md&#39; and &#39;/bronze/bronze_sales.md&#39; directly.&quot;<br>    ),<br>    tools=[read_okf_doc],<br>    automatic_function_calling=types.AutomaticFunctionCallingConfig(disable=False),<br>    temperature=0.2,<br>)<br><br><br>def ask(prompt: str) -&gt; None:<br>    print(f&quot;\n{&#39;=&#39; * 70}\nQ: {prompt}\n{&#39;=&#39; * 70}&quot;)<br>    response = client.models.generate_content(<br>        model=&quot;gemini-2.5-flash&quot;,<br>        contents=prompt,<br>        config=config,<br>    )<br>    print(response.text)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    # --- 3. Example prompts for the blog / demo screenshots ------------<br>    ask(<br>        &quot;Trace the full lineage from bronze_sales to gold_sales_facts. &quot;<br>        &quot;List every hop and what happens at each stage.&quot;<br>    )</pre><h3>Step 3: The Agentic Loop</h3><p>Here is where we ditch the complex LangChain orchestrations. Using the Google GenAI SDK, we can empower the model to figure out the routing itself using <strong>Automatic Function Calling</strong>.</p><pre>from google import genai<br>from google.genai import types<br><br>client = genai.Client(api_key=os.environ[&quot;GEMINI_API_KEY&quot;])<br><br>config = types.GenerateContentConfig(<br>    system_instruction=(<br>        &quot;You are an expert Data Platform Architect. You have direct access &quot;<br>        &quot;to our data catalog via the Open Knowledge Format. Whenever a user &quot;<br>        &quot;asks about SQL queries, schemas, or lineage, use the `read_okf_doc` &quot;<br>        &quot;tool to fetch concept documents. Always follow markdown hyperlinks &quot;<br>        &quot;inside a doc&#39;s body to traverse table relationships...&quot;<br>    ),<br>    tools=[read_okf_doc],<br>    automatic_function_calling=types.AutomaticFunctionCallingConfig(disable=False),<br>    temperature=0.2,<br>)</pre><p>By setting disable=False in the automatic function calling config, Gemini handles the reasoning loop natively.</p><p>If I ask the agent: <em>“Trace the full lineage from bronze_sales to gold_sales_facts”</em>, here is what happens under the hood:</p><ol><li>The model calls read_okf_doc(&quot;/bronze/bronze_sales.md&quot;).</li><li>It reads the file, parses the schema, and notices a hyperlink pointing to /gold/gold_sales_facts.md.</li><li>It decides, entirely on its own, to call the tool <em>again</em> for the Gold table.</li><li>It reads the Gold table, sees the joins to the Silver dimension tables, and grabs those too.</li><li>Once it has the full picture, it synthesises a complete, accurate response.</li></ol><h3>The Payoff</h3><p>Instead of spending hours manually auditing SQL scripts to figure out which downstream tables will break if you modify a Silver column, you just ask the agent:</p><blockquote>“If we add a new column to silver_products, which gold tables need to be revalidated? Use the catalog to check, don’t guess.”</blockquote><p>Because the agent crawls the actual, validated OKF bundle on your local machine, it doesn’t hallucinate. It traverses the graph, checks the dependencies, and gives you an exact answer based on the reality of your codebase.</p><p>By shifting to docs-as-code and layering an AI agent on top, you stop treating data catalogs as an administrative chore. You turn them into an active, intelligent teammate that knows your pipeline inside and out.</p><h3>Screen Captures</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1CuLGFnru65QnT1Z17odrg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*his9uFOR0E2cbaUwSUd35A.png" /></figure><h3>Final Thoughts</h3><blockquote>To be clear, building an agentic catalog isn’t an excuse to abandon traditional data modeling. Your core modeling tools are still the source of truth for engineering. OKF simply acts as the bridge that takes those hard-earned models and serves them up in a format that AI agents can actually read, understand, and act upon. It’s not about replacing your stack; it’s about giving your stack a brain.</blockquote><p>Thanks for reading! If you enjoyed this, hit that <strong>follow</strong> button for more.</p><p><strong>You can connect with me on:</strong></p><p><strong>LinkedIn</strong>: <a href="http://www.linkedin.com/in/sudharma-mokashi">www.linkedin.com/in/sudharma-mokashi</a></p><p>I’d love to hear your feedback on this project and see how you’re using it!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=75a20caee9ed" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/from-messy-data-models-to-agentic-data-catalogs-building-an-ai-data-architect-with-okf-and-gemini-75a20caee9ed">From Messy Data Models to Agentic Data Catalogs: Building an AI Data Architect with OKF and Gemini</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build Your Own AI-Powered Stock Analyzer (for Free!)]]></title>
            <link>https://medium.com/google-cloud/build-your-own-ai-powered-stock-analyzer-for-free-5d26248c5173?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/5d26248c5173</guid>
            <category><![CDATA[gcp-app-dev]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[stock-market]]></category>
            <category><![CDATA[gemini]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Sat, 08 Feb 2025 08:59:10 GMT</pubDate>
            <atom:updated>2025-02-10T03:57:49.010Z</atom:updated>
            <content:encoded><![CDATA[<p>Ever felt overwhelmed by the sheer volume of stock market data? Wish you had a personal assistant to sift through the noise and give you clear, actionable insights? Well, you can build one yourself, and it’s surprisingly easy (<strong>and free!</strong>) thanks to the power of Python, readily available APIs, and a touch of AI.</p><p><strong>Important Disclaimer:</strong></p><p>This project is intended for <strong>educational and informational purposes only</strong>. It is designed to demonstrate how to integrate various APIs and AI models. The stock market is <em>inherently complex and risky</em>. <strong>Do not use the “Buy” or “Sell” signals generated by this application for actual trading decisions.</strong> Thorough understanding of financial markets, risk management, and independent research are essential before making any investment decisions.</p><blockquote>This project can serve as a foundation for those interested in exploring the intersection of AI and finance, but it is <em>not</em> a substitute for professional financial advice.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*krl1DKyx8m8vie6CltgpdQ.jpeg" /></figure><h4>In this tutorial, we’ll create a simple yet effective stock analyzer that:</h4><ol><li><strong>Fetches historical stock data:</strong> Using the free Alpha Vantage API.</li><li><strong>Visualizes the data:</strong> Creates interactive charts with Matplotlib.</li><li><strong>Provides AI-powered analysis:</strong> Leverages Google’s Gemini Flash model for insights.</li><li><strong>Presents everything in a user-friendly web app:</strong> Built with Streamlit.</li></ol><blockquote>This project is perfect for beginners who want to learn about API integration, data visualization, and the practical application of AI. No prior experience with finance or AI is required!</blockquote><p><strong>Step 1: Gathering Your Tools (and API Keys)</strong></p><p>Before we dive into the code, let’s make sure you have the necessary tools:</p><p><strong>Python:</strong> Make sure you have Python 3.7 or later installed.</p><p><strong>Libraries:</strong> We’ll be using the following Python libraries. You can install them using pip:</p><pre>pip install pandas matplotlib requests pytz google-generativeai streamlit</pre><p><strong>Streamlit:</strong> Use command pip install streamlit to install</p><p><strong>API Keys:</strong> This is where the “free” magic happens!</p><ul><li><strong>Alpha Vantage:</strong> Head over to <a href="https://www.alphavantage.co/">https://www.alphavantage.co/</a> and sign up for a free API key. This key allows us to access their stock data. Keep this key safe and private!</li><li><strong>Google AI Studio API Key:</strong> Go to <a href="https://www.google.com/url?sa=E&amp;source=gmail&amp;q=https://ai.google.dev/">Google AI Studio</a> . Click “Get an API Key”. This will allow you to use the Gemini Flash model. Keep this key secret, too!</li></ul><blockquote><strong>Important Note:</strong> Free API keys often have usage limits (e.g., a certain number of requests per minute or day). Be mindful of these limits to avoid interruptions. Alpha Vantage’s free tier is usually sufficient for personal projects.</blockquote><h3>Let’s Get Coding!</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qIbHU-fb7jDDxdIejZoQ2A.jpeg" /></figure><p><strong>Step 2: Fetching Stock Data (stock_utility_handler.py)</strong></p><p>Let’s start by creating a file named stock_utility_handler.py. This file will handle fetching and processing the stock data.</p><pre>import pandas as pd<br>import json<br>from datetime import datetime<br>import pytz<br>import matplotlib.pyplot as plt<br>import matplotlib.dates as mdates<br>import matplotlib.widgets as widgets<br>import requests<br><br><br>class StockAPI:<br>    def __init__(self,api_key):<br>        self.api_key = api_key<br>        <br>    def get_stock_info(self,stock,market):<br>        if market == &#39;NASDAQ&#39;:<br>            url = f&#39;https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&amp;symbol={stock}&amp;outputsize=compact&amp;apikey={self.api_key}&#39;<br>        else:<br>            url = f&#39;https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&amp;symbol={stock}.{market}&amp;outputsize=compact&amp;apikey={self.api_key}&#39;<br>        r = requests.get(url)<br>        data = r.json()<br>        return data <br>    <br>class StockAnalyzer:<br>    def __init__(self):<br>        pass<br><br>    def json_to_dataframe(self,json_data,stock_symbol,market):<br>        print(json_data)<br>        time_series_data = json_data[&#39;Time Series (Daily)&#39;]<br>        df_data = []<br><br>        for date_str, values in time_series_data.items():<br>            data_row = {&#39;date&#39;: date_str}<br>            for key, value in values.items():<br>                new_key = key.split(&#39;. &#39;)[1]  # Correctly extract the key<br>                data_row[new_key] = float(value)<br>            df_data.append(data_row)<br><br>        df = pd.DataFrame(df_data)<br>        df[&#39;date&#39;] = pd.to_datetime(df[&#39;date&#39;])<br><br>        eastern = pytz.timezone(&#39;US/Eastern&#39;)<br>        ist = pytz.timezone(&#39;Asia/Kolkata&#39;)<br><br>        df[&#39;date&#39;] = df[&#39;date&#39;].dt.tz_localize(eastern).dt.tz_convert(ist)<br>        df[&#39;date&#39;] = df[&#39;date&#39;].dt.strftime(&#39;%Y-%m-%d %H:%M:%S&#39;)<br>        df[&#39;stock&#39;]=stock_symbol<br>        df[&#39;market&#39;]=market<br><br>        df = df.set_index(&#39;date&#39;)<br>        return df<br><br>    def plot_stock_data(self,df, stock_symbol, market,image_path):<br>        plt.figure(figsize=(16, 10))<br><br>        # Plotting Closing Price<br>        plt.subplot(3, 1, 1)<br>        plt.plot(pd.to_datetime(df.index), df[&#39;close&#39;], label=f&#39;{stock_symbol} Closing Price ({market})&#39;, color=&#39;blue&#39;)<br>        plt.title(f&#39;{stock_symbol} Stock Performance ({market})&#39;)<br>        plt.xlabel(&#39;Date (IST)&#39;)<br>        plt.ylabel(&#39;Price&#39;)<br>        plt.legend()<br>        plt.grid(True)<br><br>        # Plotting Volume<br>        plt.subplot(3, 1, 2)<br>        plt.bar(pd.to_datetime(df.index), df[&#39;volume&#39;], label=f&#39;{stock_symbol} Volume ({market})&#39;, color=&#39;green&#39;, width=2)<br>        plt.xlabel(&#39;Date (IST)&#39;)<br>        plt.ylabel(&#39;Volume&#39;)<br>        plt.legend()<br>        plt.grid(True)<br><br>        # Plotting Moving Averages<br>        plt.subplot(3, 1, 3)<br>        df[&#39;MA_7&#39;] = df[&#39;close&#39;].rolling(window=7).mean()<br>        df[&#39;MA_20&#39;] = df[&#39;close&#39;].rolling(window=20).mean()<br>        plt.plot(pd.to_datetime(df.index), df[&#39;close&#39;], label=f&#39;{stock_symbol} Closing Price ({market})&#39;, color=&#39;blue&#39;, alpha=0.7)<br>        plt.plot(pd.to_datetime(df.index), df[&#39;MA_7&#39;], label=&#39;7-Day MA&#39;, color=&#39;orange&#39;)<br>        plt.plot(pd.to_datetime(df.index), df[&#39;MA_20&#39;], label=&#39;20-Day MA&#39;, color=&#39;red&#39;)<br>        plt.xlabel(&#39;Date Month(IST)&#39;)<br>        plt.ylabel(&#39;Price&#39;)<br>        plt.legend()<br>        plt.grid(True)<br><br>        # Enhanced Date Formatting for All Subplots<br>        for ax in plt.gcf().axes:<br>            # Major ticks every month, minor ticks every week<br>            ax.xaxis.set_major_locator(mdates.MonthLocator())<br>            ax.xaxis.set_minor_locator(mdates.WeekdayLocator(byweekday=[0]))  # Monday<br><br>            # Formatter for major ticks<br>            ax.xaxis.set_major_formatter(mdates.DateFormatter(&#39;%Y-%m&#39;))<br><br>            # Formatter for minor ticks (hover tooltip will provide more detail)<br>            #ax.xaxis.set_minor_formatter(mdates.DateFormatter(&#39;%Y-%m-%d&#39;))<br><br>            # Auto-rotate labels if needed<br>            plt.gcf().autofmt_xdate()<br><br>        # Add hover tooltip<br>        cursor = widgets.Cursor(plt.gca(), color=&#39;red&#39;, linewidth=1)<br><br>        plt.tight_layout()<br>        plt.savefig(image_path) <br>        plt.show()</pre><p><strong>Code Explanation</strong></p><p><strong>get_stock_info():</strong> This function constructs the API request URL based on the stock ticker symbol and market. It handles both BSE and NASDAQ markets (you can add more as part of your project :) ). It uses the requests library to make the API call and returns the JSON response.</p><p><strong>json_to_dataframe():</strong> This function takes the raw JSON data from Alpha Vantage and converts it into a Pandas DataFrame. This is crucial for data analysis and visualization. It handles:</p><ul><li>Extracting the “Time Series (Daily)” data.</li><li>Cleaning up the keys (removing the “1. “ prefix).</li><li>Converting the date strings to datetime objects.</li><li>Converting the dates from US/Eastern to Asia/Kolkata (IST) timezone, and formats the date.</li><li>Setting the ‘date’ column as the index.</li></ul><p><strong>plot_stock_data():</strong> This function generates a comprehensive plot with three subplots:</p><ul><li><strong>Closing Price:</strong> Shows the daily closing price of the stock.</li><li><strong>Volume:</strong> Displays the trading volume as a bar chart.</li><li><strong>Moving Averages:</strong> Calculates and plots the 7-day and 20-day moving averages, which are common technical indicators.</li></ul><p>AND saves the figure to the specified image_path.</p><p><strong>Step 3: Adding AI Insights (ai_insights_handler.py)</strong></p><p>Now, let’s create ai_insights_handler.py to integrate the Gemini Flash model for AI-powered analysis.</p><pre>import google.generativeai as genai<br>import PIL.Image<br><br>class AIInsights:<br>    def __init__(self,api_key):<br>        self.api_key=api_key<br>        genai.configure(api_key=self.api_key)<br>        self.model = genai.GenerativeModel(model_name = &quot;gemini-2.0-flash&quot;)<br>        <br>    def get_ai_insights(self,image_path,stock,market):<br>        image = PIL.Image.open(image_path)<br>        prompt = f&quot;This is an image of stock performance of stock : &#39;{stock}&#39; for the last 100 days on market : &#39;{market}&#39;, on the basis of volume traded, closing prices and 7,20 day moving averages provide some analysis and suggestion abot this stock. This Stock should be purchased or not.&quot;<br>        response = self.model.generate_content([prompt, image])<br>        return response</pre><p><strong>Code Explanation</strong></p><p><strong>get_ai_insights():</strong></p><ul><li>Opens the saved image using PIL.Image.open().</li><li>Constructs a prompt for the Gemini model. The prompt clearly describes the image and the desired analysis (buy/sell suggestion based on volume, closing prices, and moving averages). A clear prompt is <em>essential</em> for good results.</li><li>Calls model.generate_content(), passing in both the prompt and the image. This is the core of the multimodal interaction.</li><li>Returns the response from the model.</li></ul><p><strong>Step 4: Building the Streamlit Web App (marketapp.py)</strong></p><p>Collect the API keys generated in the initial steps for Google AI Studio and the Alpha Vantage API.</p><p>Finally, let’s create marketapp.py to build a user-friendly interface using Streamlit.</p><pre>from stock_utility_handler import StockAPI, StockAnalyzer<br>from ai_insights_handler import AIInsights<br><br>import streamlit as st<br>    <br>if &#39;page&#39; not in st.session_state:<br>    st.session_state.page = &quot;page1&quot;  <br>    st.session_state.ticker = &quot;RELIANCE&quot;  <br>    st.session_state.market = &quot;BSE&quot; <br>    st.session_state.image_path = &quot;&quot;<br>    st.session_state.ai_insights = &quot;&quot;<br>    st.session_state.internal_results_available = False<br><br><br><br>def page1():<br>    st.title(&#39;Stock AI Agent&#39;)<br><br>    <br>    col1, col2 = st.columns(2)<br>    with col1:<br>        st.session_state.ticker = st.text_input(&quot;Enter Stock Ticker Symbol&quot;, value=st.session_state.ticker, key=&quot;ticker_input&quot;)  #Use key for unique input.<br>    with col2:<br>        st.session_state.market = st.selectbox(&quot;Select Market&quot;, [&quot;BSE&quot;, &quot;NASDAQ&quot;], index=[&quot;BSE&quot;, &quot;NASDAQ&quot;].index(st.session_state.market), key=&quot;market_input&quot;) #Use key, corrected index.<br><br>    <br>    st.sidebar.header(&quot;About&quot;)<br>    st.sidebar.write(&quot;This is a stock analysis platform.&quot;)<br><br>    <br>    st.markdown(&quot;---&quot;)<br><br>    if st.button(&#39;Submit&#39;):<br>        st.session_state.page = &quot;page2&quot;  # Go to the next page<br>        st.session_state.internal_results_available = False<br>        st.rerun() <br><br>def page2():<br><br><br>    st.title(f&quot;Analysis for {st.session_state.ticker} ({st.session_state.market})&quot;)  # Use stored inputs<br>    stock=st.session_state.ticker<br>    market=st.session_state.market<br>    if not st.session_state.internal_results_available:<br>        with st.spinner(&#39;Analyzing... Please wait...&#39;):<br>            image_path=f&quot;&lt;your_image_path&gt;/{market}_{stock}.png&quot;<br>            st.session_state.image_path=image_path<br>            <br>            stock_api_obj = StockAPI(&quot;&lt;your-alpha-vantage-api-key&gt;&gt;&quot;)<br><br>            market_data=stock_api_obj.get_stock_info(stock,market)<br><br>            stock_analyzer_obj=StockAnalyzer()<br><br>            df=stock_analyzer_obj.json_to_dataframe(market_data,stock, market)<br><br>            stock_analyzer_obj.plot_stock_data(df,stock, market,image_path)<br><br>            ai_insights_obj=AIInsights(&quot;&lt;&lt;your-google-api-key&gt;&gt;&quot;)<br><br>            response=ai_insights_obj.get_ai_insights(image_path,stock,market)<br><br>            candidates = response.candidates  <br>            for candidate in candidates:<br>                text_parts = candidate.content.parts<br>                for part in text_parts:<br>                    print(part.text)<br>                    st.session_state.ai_insights += part.text   <br>            st.session_state.internal_results_available = True<br>        <br>    if  st.session_state.internal_results_available:<br>        st.subheader(&quot;Chart Analysis&quot;)<br>        st.image(st.session_state.image_path, caption=f&quot;{st.session_state.ticker} Chart&quot;,use_column_width=True)  # Example image<br><br>        st.subheader(&quot;Analysis Results&quot;)<br>        st.write(st.session_state.ai_insights)<br>        <br>        if st.button(&quot;Back&quot;): #Back button<br>            st.session_state.page = &quot;page1&quot;<br>            st.session_state.internal_results_available = False<br>            st.rerun()<br><br><br>if st.session_state.page == &quot;page1&quot;:<br>    page1()<br>elif st.session_state.page == &quot;page2&quot;:<br>    page2()</pre><h3>At last</h3><p>Run the marketapp.py using streamlit run</p><pre>streamlit run marketapp.py</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b_LPSKZ_J0hq8TmJKrkYRA.jpeg" /></figure><p>Here are few screenshots of how the UI looks like</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HlhG4c_j-tAOPttzGkVN8g.png" /></figure><p>Enter the stock ticker symbol and select the market. (Here I have selected HDFCBANK just as an example)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mSMRjLcvhxFkr1lEBAAHhw.png" /></figure><p>You should see a screen displaying the API calls and data analysis in progress.</p><p>Here are the results the chart and the AI insights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Hiy4a0gDbBBTXJDweIG7WQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G29LrRTr03ZhCIx9y6m2bA.png" /></figure><p>Thanks for reading! If you enjoyed this, hit that <strong>subscribe</strong> button for more.</p><p><strong>You can connect with me on:</strong></p><p><strong>LinkedIn</strong>: <a href="http://www.linkedin.com/in/sudharma-mokashi">www.linkedin.com/in/sudharma-mokashi</a></p><p><strong>Youtube</strong>:<a href="https://www.youtube.com/@techwithsud4646">https://www.youtube.com/@techwithsud4646</a></p><p>I’d love to hear your feedback on this project and see how you’re using it!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5d26248c5173" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/build-your-own-ai-powered-stock-analyzer-for-free-5d26248c5173">Build Your Own AI-Powered Stock Analyzer (for Free!)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Powering Q&A Chatbots with AlloyDB and GenAI Models]]></title>
            <link>https://medium.com/google-cloud/powering-q-a-chatbots-with-alloydb-and-genai-models-e6332b62383c?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/e6332b62383c</guid>
            <category><![CDATA[generative-ai]]></category>
            <category><![CDATA[pgvector]]></category>
            <category><![CDATA[alloydb]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[chatbots]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Thu, 31 Aug 2023 10:46:25 GMT</pubDate>
            <atom:updated>2023-10-09T11:15:59.332Z</atom:updated>
            <content:encoded><![CDATA[<p>In recent times, there has been a growing interest in the use of <strong>embeddings</strong> and <strong>vector databases</strong> for a variety of applications, including natural language processing, machine learning, and recommendation engines. Embeddings are a way of representing data as vectors, which can then be used to calculate similarities and perform other operations. Vector databases are specialised databases that are designed to store and query embeddings efficiently.</p><p><strong>AlloyDB</strong> is a next-generation managed PostgreSQL database that offers a number of features that make it well-suited for storing and querying embeddings. For example, AlloyDB supports the pgvector extension, which provides a number of features for storing and indexing vectors.</p><p>In this blog post, we will show how to use AlloyDB’s pgvector extension to store vector embeddings of a PDF file into AlloyDB for PostgreSQL and also will create a small Q&amp;A chatbot on top of it.</p><p>Before we jump into the implementation, first lets understand some basic information about <strong>AlloyDB</strong>, <strong>Embeddings</strong> and <strong>Vector Databases</strong>.”</p><h4>What are Embeddings?</h4><p>Embeddings are a way of representing data as vectors. This can be useful for a variety of tasks, such as natural language processing, machine learning, and recommendation engines.</p><p>For example, we can represent a word as a vector of real numbers. The meaning of the word is then represented by the values of the vector. We can then use these vectors to calculate similarities between words, or to perform other operations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PqX7e75TdqRYx54Dbl09Ig.png" /><figcaption>Embeddings</figcaption></figure><h4>What are Vector Databases?</h4><p>Vector databases are specialised databases that are designed to store and query embeddings efficiently. Vector databases typically have features such as:</p><ul><li>Support for storing vectors of different sizes</li><li>Support for indexing vectors for efficient search</li><li>Support for vector operations such as addition, subtraction, and multiplication</li></ul><h4>Why Use AlloyDB for Storing Embeddings?</h4><p>AlloyDB is a next-generation managed PostgreSQL database that offers a number of features that make it well-suited for storing and querying embeddings. These features include:</p><ul><li>Support for the pgvector extension</li><li>High performance for storing and querying vectors</li><li>Scalability to handle large datasets</li><li>Security and compliance features</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V5P_I9WdL8vo3t9ouIKChw.png" /><figcaption>AlloyDB</figcaption></figure><h3>Pre-requisites</h3><p>Before we get into the actual implementation, please read through the following topics</p><ul><li>Embedding Model <a href="https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/text-embeddings">Gecko</a> — This will be used to generate embeddings of the source documents.</li><li>Text Generation Model <a href="https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/text">Bison</a> — This will be used to generate text while providing answers.</li><li><a href="https://www.langchain.com/">Langchain</a></li><li><a href="https://cloud.google.com/alloydb">AlloyDB</a> for PostgreSQL Cluster</li></ul><h3>High Level Architecture Diagram</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/873/1*sxj-qFnwUjLus5lj4NrgnA.png" /><figcaption>High Level Architecture | QA chatbot | AlloyDB | pgvectordb</figcaption></figure><h3>Implementation</h3><p>Step — 1 : Enable the Vertex AI API.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IhoERgAOFXuHMIUTc2YuBw.png" /><figcaption>Enable Vertex AI API</figcaption></figure><p>Step — 2: Create pgvector extension in AlloyDB using the following command</p><pre>CREATE EXTENSION IF NOT EXISTS vector;</pre><p>Step — 3: Create table to store the contents of the PDF and their respective embeddings</p><pre>CREATE TABLE pdf_content_embeddings(<br> content_id SERIAL PRIMARY KEY,<br>content TEXT,<br>embedding vector(768));</pre><p>Step — 3: Provide Vertex AI User/Admin access to the default compute engine service account.</p><p>Step — 4: Export the below mentioned environmental variables on your compute.</p><pre>export DATABASE_NAME=&lt;DATABASE_NAME&gt;<br>export DB_HOST=&lt;ALLOYDB_HOST_IP_ADDRESS&gt;<br>export DB_USER=&lt;DATABASE_USER_NAME&gt;<br>export DB_PASSWORD=&lt;DATABASE_PASSWORD&gt;</pre><p>Step — 5: We will be making use of 6 python scripts here, copy and paste it according to the names provided.</p><p><strong>alloydb_utility.py</strong> — This utility script will help in making database connection and calls with AlloyDB database.</p><pre>import psycopg2<br>import os<br><br>class AlloyDB:<br>    &quot;&quot;&quot;<br>    class AlloyDB will make the connection with the AlloyDB&#39;s columnar engine<br>    &quot;&quot;&quot;<br>    def __init__(self):<br>        <br>        config=self._read_config()<br>        <br>        self.database_nm=config[&#39;database_nm&#39;]<br>        self.user=config[&#39;user&#39;]<br>        self.password=config[&#39;password&#39;]<br>        self.host=config[&#39;host&#39;]<br>        self.port=&#39;5432&#39;<br>       <br>        self.cursor=self._connection()<br>    <br>    def _read_config(self):<br>        <br>        config={}<br>        config[&#39;database_nm&#39;]=os.environ[&#39;DATABASE_NAME&#39;]<br>        config[&#39;user&#39;] = os.environ[&#39;DB_USER&#39;]<br>        config[&#39;password&#39;]=os.environ[&#39;DB_PASSWORD&#39;]<br>        config[&#39;host&#39;]=os.environ[&#39;DB_HOST&#39;]<br>        <br>        return config<br>        <br>    def _connection(self):<br>        try:<br>            conn = psycopg2.connect(<br>                database=self.database_nm, user=self.user, password=self.password, host=self.host, port=self.port<br>                )<br>            conn.autocommit = True<br>            cursor = conn.cursor()<br>            return cursor<br>        except Exception as e:<br>            print(e)<br>    def select_query(self,query):<br>        <br>        self.cursor.execute(query)<br>        result_data=self.cursor.fetchall()<br>        return result_data<br><br>    def insert_embeddings(self,query):<br>        self.cursor.execute(query)</pre><p><strong>custom_vertex_ai_embeddings.py </strong>— This python script will be used to override the existing VertexAIEmbeddings class with embed_documents method. Using this class and it’s functions we will create vectors from text data.</p><pre>import time<br>from typing import Optional, Tuple, List<br>from pydantic import BaseModel<br>from langchain.embeddings import VertexAIEmbeddings<br><br><br><br>class CustomVertexAIEmbeddings(VertexAIEmbeddings, BaseModel):<br>  requests_per_minute: int<br>  num_instances_per_batch: int<br><br>  def rate_limit(self,max_per_minute):<br>    period = 60 / max_per_minute<br>    print(&#39;Waiting&#39;)<br>    while True:<br>      before = time.time()<br>      yield<br>      after = time.time()<br>      elapsed = after - before<br>      sleep_time = max(0, period - elapsed)<br>      if sleep_time &gt; 0:<br>        print(&#39;.&#39;, end=&#39;&#39;)<br>        time.sleep(sleep_time)<br>  <br>  # Overriding embed_documents method<br>  def embed_documents(self, texts: List[str]):<br>    limiter = self.rate_limit(self.requests_per_minute)<br>    results = []<br>    docs = list(texts)<br><br>    while docs:<br>      # Working in batches because the API accepts maximum 5<br>      # documents per request to get embeddings<br>      head, docs = docs[:self.num_instances_per_batch], docs[self.num_instances_per_batch:]<br>      chunk = self.client.get_embeddings(head)<br>      results.extend(chunk)<br>      next(limiter)<br><br>    return [r.values for r in results]</pre><p><strong>document_reader_utility.py</strong> — This utility will help in downloading and reading the document from Google Cloud Storage bucket.</p><pre>import os<br>from langchain.document_loaders import PyPDFLoader<br>import vertexai<br>from langchain.text_splitter import RecursiveCharacterTextSplitter<br>from google.cloud import storage<br>import json<br><br>class DocumentReader:<br>    def __init__(self) -&gt; None:<br>        config=self._read_config()<br>        self.gcs_folder_prefix=config[&#39;gcs_prefix&#39;]<br>        self.gcp_project_name=config[&#39;project_nm&#39;]<br>        self.bucket_name=config[&#39;gcs_bucket_nm&#39;]<br>        vertexai.init(project=self.gcp_project_name)<br><br>    def _read_config(self):<br>        config_file=open(&#39;config.json&#39;)<br>        config=json.load(config_file)<br>        config_file.close()<br>        return config<br><br>    def _document_loader(self):<br>        documents=[]<br>        for file in os.listdir(&#39;./Data/&#39;):<br>            if file.endswith(&#39;.pdf&#39;):<br>                pdf_data_path=&#39;./Data/&#39;+file<br>                loader=PyPDFLoader(pdf_data_path)<br>                documents.extend(loader.load())<br>        return documents<br><br>    def text_splitter(self):<br>        documents=self._document_loader()<br>        text_splitter=RecursiveCharacterTextSplitter(chunk_size=100, chunk_overlap=20)<br>        docs=text_splitter.split_documents(documents)<br>        return docs<br>    <br>    def download_file_from_GCS(self):<br>        dl_dir=&#39;Data/&#39;<br>        storage_client=storage.Client()<br>        bucket=storage_client.get_bucket(self.bucket_name)<br>        blobs=bucket.list_blobs(prefix=self.gcs_folder_prefix)<br>        for blob in blobs:<br>            filename=blob.name.replace(&#39;/&#39;,&#39;_&#39;)<br>            blob.download_to_filename(dl_dir+filename)<br><br>    def create_temp_data_dir(self):<br>        os.popen(&#39;mkdir Data&#39;)<br>    <br>    def remove_dir(self):<br>        os.popen(&#39;rm -rf ./Data&#39;)</pre><p><strong>embeddings_utility.py — </strong>This is a wrapper script which will be used to generate vectors from text data. This will also insert the text with its vector embeddings into the <strong><em>pdf_content_embeddings</em></strong> table in AlloyDB.</p><pre>from custom_vertex_ai_embeddings import CustomVertexAIEmbeddings<br>from alloydb_utility import AlloyDB<br>class EmbeddingsUtility:<br><br>    def __init__(self,docs):<br>        self.docs=docs<br>    def generate_embeddings_client(self):<br>        EMBEDDING_QPM=100<br>        EMBEDDING_NUM_BATCH=5<br>        embeddings= CustomVertexAIEmbeddings(requests_per_minute=EMBEDDING_QPM,<br>        num_instances_per_batch=EMBEDDING_NUM_BATCH)<br>        return embeddings<br>    <br>    def create_embeddings(self):<br>        insert_statement=&#39;INSERT INTO pdf_content_embeddings(content,embeddings) VALUES &#39;<br>        embeddings_client=self.generate_embeddings_client()<br>        for doc in self.docs:<br>            tuple1=(doc.page_content,embeddings_client.embed_documents(doc.page_content)[0])<br>            insert_statement = insert_statement + str(tuple1).replace(&quot;&#39;&quot;,&quot;&quot;) + &#39;;&#39;<br>            AlloyDB().insert_embeddings(insert_statement)<br>            insert_statement=&#39;INSERT INTO pdf_content_embeddings(content,embeddings) VALUES &#39;<br>    <br>    def get_embeddings(self):<br>        embeddings_client=self.generate_embeddings_client()<br>        return embeddings_client.embed_documents(self.docs)[0]</pre><p><strong>generate_answers.py</strong> — This utility will perform the similarity search operation on AlloyDB and provide us with the similar results to our questions and then will generate text from the relevant description.</p><pre>from langchain.chains.summarize import load_summarize_chain<br>from langchain.llms import VertexAI<br>from langchain import PromptTemplate, LLMChain<br>from alloydb_utility import AlloyDB<br>from langchain.docstore.document import Document<br><br>class fetchResults:<br>    def __init__(self,question,question_embeddings) :<br>        self.matches=[]<br>        self.question=question<br>        self.question_embeddings=question_embeddings<br>        self.similarity_threshold = 0.7<br>        self.num_matches = 5<br>        <br>        self.llm= VertexAI(<br>        model_name=&#39;text-bison@001&#39;,<br>        max_output_tokens=256,<br>        temperature=0.1,<br>        top_p=0.8,<br>        top_k=40,<br>        verbose=True,<br>        )<br><br>        self.map_prompt_template = &quot;&quot;&quot;<br>              You will be given a detailed description of Bigquery feature information.<br>              This description is enclosed in triple backticks (```).<br>              Using this description only, extract the summary of the description.<br><br>              ```{text}```<br>              SUMMARY:<br>              &quot;&quot;&quot;<br>        self.combine_prompt_template = &quot;&quot;&quot;<br>                You will be given a detailed description of Bigquery feature information.<br>                This description is enclosed in triple backticks (```). and a question enclosed in<br>                double backticks(``).<br>                Select the description which is most relevant to answer the question.<br>                Using that selected toy description, answer the following<br>                question in as much detail as possible.<br>                You should only use the information in the description.<br>                Your answer should be less than 200 words.<br>                Your answer should be in a bulleted list format wherever required.<br><br><br>                Description:<br>                ```{text}```<br><br><br>                Question:<br>                ``{user_query}``<br><br><br>                Answer:<br>                &quot;&quot;&quot;<br>        <br>    def similarity_search(self):    <br>        self.get_matching_contents_qry = f&quot;&quot;&quot;<br>                            WITH vector_matches AS (<br>                              SELECT content_id, 1 - (embedding &lt;=&gt; &#39;{self.question_embeddings}&#39;) AS similarity<br>                              FROM pdf_content_embeddings;<br>                              WHERE 1 - (embedding &lt;=&gt; {self.question_embeddings}) &gt; {self.similarity_threshold}<br>                              ORDER BY similarity DESC<br>                              LIMIT {self.num_matches}<br>                            )<br>                            SELECT content FROM pdf_content_embeddings<br>                            WHERE content_id IN (SELECT content_id FROM vector_matches)<br>                            &quot;&quot;&quot;<br>    <br>        results=AlloyDB().select_query(self.get_matching_contents_qry)<br>        if len(results) == 0:<br>            raise Exception(&quot;Did not find any results. Adjust the query parameters.&quot;)<br>        for r in results:<br>            # Collect the text content for all the matches to the question.<br>            self.matches.append(r[0])<br>        return self.matches<br><br>    def form_answer(self):<br>        matches=self.similarity_search()<br>       <br>        map_prompt = PromptTemplate(template=self.map_prompt_template, input_variables=[&quot;text&quot;])<br>        combine_prompt = PromptTemplate(template=self.combine_prompt_template, input_variables=[&quot;text&quot;, &quot;user_query&quot;])<br>        docs = [Document(page_content=t) for t in matches]<br>        chain = load_summarize_chain(<br>            self.llm, chain_type=&quot;map_reduce&quot;, map_prompt=map_prompt, combine_prompt=combine_prompt<br>            )   <br>        answer = chain.run(<br>            {<br>                &quot;input_documents&quot;: docs,<br>                &quot;user_query&quot;: self.question,<br>            }<br>            )<br>        return answer</pre><p><strong>main.py — </strong>Driver program for the utility</p><pre>from pydoc import doc<br>from document_reader_utility import DocumentReader<br>from embeddings_utility import EmbeddingsUtility<br><br>from alloydb_utility import AlloyDB<br>from generate_answers import fetchResults<br>import sys<br><br><br><br>if __name__ == &#39;__main__&#39;:<br>    #Define Database Connections and Retriever Below<br>    db=AlloyDB()._connection()<br>    <br>    if sys.argv[1] == &#39;build&#39;:<br>        #Source Document Reader<br>        document_reader_obj=DocumentReader()<br>        document_reader_obj.create_temp_data_dir()<br>        document_reader_obj.download_file_from_GCS()<br>        docs=document_reader_obj.text_splitter()<br><br>        #Generate Embeddings for the text data<br>        embeddings=EmbeddingsUtility(docs)<br>        embeddings.create_embeddings()<br><br>        #Remove Temp Data folder<br>        document_reader_obj.create_temp_data_dir()<br>    <br>    elif sys.argv[1] == &#39;run&#39;:<br>        question_counter=0<br>        while True:<br>            if question_counter ==0:<br>                question=input(&quot;Hello How can I help you ? \n\n You can ask me questions related to Bigquery Optimization. \n\n Anytime you want to stop, simply say Thankyou or Goodbye :)&quot;)<br>            else:<br>                question=input(&quot;Anything else where I can help ?&quot;)<br>            if &#39;goodbye&#39; not in question.lower() or &#39;thankyou&#39; not in question.lower():<br>                embeddings_obj=EmbeddingsUtility(question)<br>                ques_embeddings=embeddings_obj.get_embeddings()<br>                fetchResults_obj=fetchResults(question,ques_embeddings)<br>                print(fetchResults_obj.form_answer())<br>                question_counter +=1<br>            else:<br>                print(&#39;Thank you! Hope you had a great experience.&#39;)<br>                break<br><br>    else:<br>        print(&#39;Please enter valid input, either run/build as a command line argument&#39;)</pre><p><strong>requirements.txt — </strong>Dependencies for the utility</p><pre>google-cloud-aiplatform==1.32<br>shapely==1.8.2<br>pypdf==3.10.0<br>google-cloud-storage==2.10.0<br>langchain==0.0.222<br>transformers==4.30.1<br>torch==2.0.1</pre><p><strong>config.json — </strong>GCS Bucket, File prefix and Project configurations should be stored in config.json</p><pre>{<br>    &quot;gcs_prefix&quot;:&quot;&quot;,<br>    &quot;gcs_bucket_nm&quot;:&quot;&quot;,<br>    &quot;project_nm&quot;:&quot;&quot;<br>}</pre><p>To Generate Embeddings and Store into AlloyDB use :</p><pre>python3 main.py build</pre><p>To start the chat application use :</p><pre>python3 main.py run</pre><p>This chatbot is created using the BigQuery optimization PDF document, but it can use any document stored in Google Cloud Storage. We have used the following technologies to create the chatbot:</p><ul><li><strong>textembedding-gecko:</strong> This creates embeddings for the content stored in PDF documents.</li><li><strong>pgvector extension: </strong>This extension for PostgreSQL allows us to store and query embeddings efficiently.</li><li><strong>AlloyDB for Postgres: </strong>This fully-managed, PostgreSQL-compatible database service from Google Cloud Platform provides high performance and scalability for our chatbot.</li><li><strong>text-bison:</strong> This library generates text and summaries from text embeddings.</li></ul><p>We successfully created a Q&amp;A chatbot using AlloyDB and GCP’s GenAI models. I hope you learnt something new from this blog.</p><p>Thanks for reading !</p><p><strong>Additional References :</strong></p><ul><li><a href="https://cloud.google.com/alloydb/docs">AlloyDB Documentation</a></li><li><a href="https://cloud.google.com/vertex-ai/docs/generative-ai/learn/models">GCP’s GenAI Models</a></li><li><a href="https://cloud.google.com/blog/products/databases/using-pgvector-llms-and-langchain-with-google-cloud-databases">More about GCP’s databases and pgvector integration</a></li></ul><p>If you want to learn more around Database or Data&amp;Analytics, do checkout my other <a href="https://medium.com/me/stories/public">blogs</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e6332b62383c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/powering-q-a-chatbots-with-alloydb-and-genai-models-e6332b62383c">Powering Q&amp;A Chatbots with AlloyDB and GenAI Models</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Connect to AlloyDB for PostgreSQL using Cloud Functions]]></title>
            <link>https://medium.com/google-cloud/connect-to-alloydb-for-postgresql-using-cloud-functions-9baef13ac5ca?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/9baef13ac5ca</guid>
            <category><![CDATA[cloud-functions]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[alloydb]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[serverless-vpc-access]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Wed, 05 Apr 2023 07:45:43 GMT</pubDate>
            <atom:updated>2023-04-10T03:01:42.970Z</atom:updated>
            <content:encoded><![CDATA[<p>In this blog we will see how we can connect to AlloyDB using Cloud Functions. As we know that Cloud Function is a serverless compute service, it requires some additional configuration to access private VPC resources such as an AlloyDB instance or a private Cloud SQL instance. We will use Serverless VPC Access Connector to establish a connection between AlloyDB instance and the Cloud Function.</p><p>Before we jump into the implementation, first lets understand some basic information about AlloyDB, Cloud Function and VPC Serverless Access Connectors.</p><h3>What is AlloyDB ?</h3><p><a href="https://cloud.google.com/alloydb">AlloyDB</a> is a fully managed, PostgreSQL-compatible database service that offers enterprise-grade performance, availability, and scale.</p><p>AlloyDB offers a number of features that make it a good choice for demanding transactional and analytical workloads, including:</p><ul><li><strong>High performance</strong>: AlloyDB is designed to be highly performant for both transactional and analytical workloads. It uses a number of techniques to achieve this, including a shared-nothing architecture, columnar storage, and vectorized execution.</li><li><strong>High availability</strong>: AlloyDB offers a 99.99% uptime SLA, inclusive of maintenance. It uses a number of techniques to achieve this, including synchronous replication, automatic failover, and hot backups.</li><li><strong>Scale</strong>: AlloyDB can scale to meet the needs of even the most demanding applications. It can be scaled up to support up to 128 vCPUs and 1 TB of memory per instance, and it can be scaled out to support up to 100 instances per cluster.</li><li><strong>Security</strong>: AlloyDB is designed with security in mind. It uses a number of features to protect your data, including encryption, auditing, and access control.</li><li><strong>Compliance</strong>: AlloyDB is designed to meet the needs of organisations with demanding compliance requirements. It supports a number of compliance standards, including HIPAA, PCI DSS, and SOC 2.</li></ul><h3>What is Cloud Function ?</h3><p><a href="https://cloud.google.com/functions">Cloud Function</a> is a lightweight compute solution for developers to create single-purpose, stand-alone functions that respond to Cloud events without needing to manage a server or runtime environment.</p><h3>What are Serverless VPC Connectors ?</h3><p>A <a href="https://cloud.google.com/vpc/docs/configure-serverless-vpc-access">Serverless VPC Access connector</a> is a tool that enables you to connect your serverless environment to your Virtual Private Cloud (VPC) network. This allows you to access Compute Engine virtual machine (VM) instances, Memorystore instances, and any other resources with an internal IP address.</p><p>To use a Serverless VPC Access connector, you first need to create a connector in your Google Cloud project. You then need to attach the connector to a specific VPC network and region. Finally, you need to configure your serverless environment to use the connector.</p><h3>What you’ll learn ?</h3><ul><li>How to access the Cloud Functions web UI in the Google Cloud Console.</li><li>How to create a Cloud Function.</li><li>How to access database instances created in a VPC from Cloud Functions.</li><li>How to create Serverless VPC Connectors.</li><li>How to test a Cloud Function.</li><li>How to connect to an AlloyDB database instance (either primary or read pool instance) using Python.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/748/1*yM6ArDQdEXFVwOt2P3ckTA.png" /><figcaption>Process</figcaption></figure><h3>Requirements</h3><ol><li>A browser, such as Chrome or Firefox.</li><li>A Google Cloud Platform project that contains your AlloyDB cluster and a primary / read pool instance.</li><li>Your instance contains a database with a table.</li><li>Your instance’s private IP address, the database and table names, the database user name and the user’s password.</li><li>A Service account with the AlloyDB Client role.</li></ol><ul><li>In the Console UI, navigate to the IAM &amp; Admins &gt; IAM page.</li><li>You can use the default Compute Engine service account. It has the suffix <a href="/service/mailto:compute@developer.gserviceaccount.com">compute@developer.gserviceaccount.com</a>.</li><li>Select Edit using the pencil icon.</li><li>Click ADD ANOTHER ROLE and add AlloyDB &gt; Client.</li><li>Click ADD ANOTHER ROLE and add Secret Manager &gt; Secret Accessor.</li><li>Click SAVE.</li></ul><p>6. A VPC Serverless Connector which is connected to the VPC where AlloyDB instance is present.</p><ul><li>Search for “Serverless VPC access” in the Google Cloud Console.</li><li>Click on CREATE CONNECTOR</li><li>Provide a name to the connector (and take a note), it will be required while configuring the Cloud Function.</li><li>Select Region.</li><li>Select the VPC network.</li><li>Provide a range of IP addresses which will be used by the connectors. For example, 10.0.0.0/28</li><li>Click on Create and wait till it appears in the dashboard with the green tick.</li></ul><p>7. Create secrets mentioned in the next section using following process</p><ul><li>Search for “Secret Manger” in the Google Cloud Console.</li><li>Click on CREATE Secret</li><li>Add Secret Name</li><li>Add Secret Value</li><li>Click on Create and wait till it appears in the dashboard with the green tick.</li></ul><h3>Create the function by using below mentioned steps</h3><h4>Create secrets for storing project and database credentials with the following names</h4><ul><li>alloydb-secrets-dev-database : Store database name into this secret</li><li>alloydb-secrets-dev-host : Store private IP address of the instance into this secret</li><li>alloydb-secrets-dev-username : Store username of the database user into this secret</li><li>alloydb-secrets-dev-password : Store password of the database user into this secret</li></ul><h3>Create the function</h3><ul><li>In a browser, go to the Google Cloud Platform Console UI.</li><li>Select Cloud Functions from the Navigation menu.</li><li>Click CREATE FUNCTION on the button bar.</li><li>Enter a name for the function.</li><li>Select the HTTP trigger. (Make a note of the URL displayed beneath the trigger item. It will be in this format: <a href="https://region-project_id.cloudfunctions.net/FUNCTION_NAME">https://REGION-PROJECT_ID.cloudfunctions.net/FUNCTION_NAME</a>)</li><li>Under Authentication, select Allow unauthenticated invocations to make the function public.</li><li>Expand the Runtime, Build and Connections Settings In Runtime service account, select a service account that has the AlloyDB Client role.</li><li>Under Connections, select the VPC connector created earlier.</li><li>Click the NEXT button.</li><li>Select Python 3.8 for the runtime option.</li><li>Select Inline editor for the source code option.</li><li>SelIn the source code editor windows, delete the existing content for both requirements.txt and main.py, and replace them with your edited versions of the code above.</li><li>Enter main as the name of the Entry point.</li></ul><h4>Create the following Cloud Function Environment Variables</h4><ul><li>user-secret : alloydb-secrets-dev-username</li><li>pass-secret : alloydb-secrets-dev-password</li><li>project-id : &lt;project-id&gt;</li><li>version : 1</li><li>database-nm : alloydb-secrets-dev-database</li><li>database-host : alloydb-secrets-dev-host</li></ul><h3>Prepare the code</h3><p>The Cloud Function code for connecting to an AlloyDB database is right here.</p><p>The Cloud Functions UI in the Cloud Console includes a text editor. You can copy/paste and edit the code there, or edit the code locally first, and then copy/paste it into the UI.</p><p>requirements.txt</p><pre># This file tells Python which modules it needs to import<br>psycopg2==2.9.5<br>google-cloud-secret-manager==2.16.1</pre><p>main.py</p><pre># This file will get executed when the function is executed<br>from alloydb_utility import AlloyDB<br><br><br>def main(request):<br>    request_json = request.get_json()<br>    query_type = request_json[&quot;query_type&quot;]<br>    try:<br>        alloydb_obj = AlloyDB()<br>        if query_type == &quot;select&quot;:<br>            result = alloydb_obj.select_query(<br>                &quot;&quot;&quot;SELECT * FROM pg_catalog.pg_tables WHERE schemaname != &#39;pg_catalog&#39; AND schemaname != &#39;information_schema&#39;;&quot;&quot;&quot;<br>            )<br>            print(result)<br>        return &quot;Task Completed Successfully&quot;<br>    except Exception as e:<br>        print(e)<br>        return e</pre><p>alloydb_utility.py</p><pre>import psycopg2<br>import json<br>import os<br>from google.cloud import secretmanager<br><br><br>class AlloyDB:<br>    def __init__(self):<br><br>        self.user = self.get_secret_data(<br>            os.environ[&quot;project-id&quot;], os.environ[&quot;user-secret&quot;], os.environ[&quot;version&quot;]<br>        )<br>        self.password = self.get_secret_data(<br>            os.environ[&quot;project-id&quot;], os.environ[&quot;pass-secret&quot;], os.environ[&quot;version&quot;]<br>        )<br>        self.host = self.get_secret_data(<br>            os.environ[&quot;project-id&quot;], os.environ[&quot;database-host&quot;], os.environ[&quot;version&quot;]<br>        )<br>        self.database_nm = self.get_secret_data(<br>            os.environ[&quot;project-id&quot;], os.environ[&quot;database-nm&quot;], os.environ[&quot;version&quot;]<br>        )<br>        self.port = &quot;5432&quot;<br>        self.cursor = self._connection()<br><br>    def get_secret_data(self, project_id, secret_id, version_id):<br><br>        client = secretmanager.SecretManagerServiceClient()<br>        secret_detail = (<br>            f&quot;projects/{project_id}/secrets/{secret_id}/versions/{version_id}&quot;<br>        )<br>        response = client.access_secret_version(request={&quot;name&quot;: secret_detail})<br>        data = response.payload.data.decode(&quot;UTF-8&quot;)<br>        return data<br><br>    def _connection(self):<br><br>        try:<br>            conn = psycopg2.connect(<br>                database=self.database_nm,<br>                user=self.user,<br>                password=self.password,<br>                host=self.host,<br>                port=self.port,<br>            )<br>            cursor = conn.cursor()<br>            print(f&quot;Connection Established with {self.host}&quot;)<br>            return cursor<br>        except Exception as e:<br>            print(e)<br>            print(f&quot;Exception {e} occured while connecting to {self.host}&quot;)<br><br>    def select_query(self, query):<br><br>        self.cursor.execute(query)<br>        result_data = self.cursor.fetchall()<br>        return result_data</pre><ul><li>Click Deploy and wait while the function is created. The spinner stops spinning and a green check appears on the subsequent page when the function is ready to use.</li></ul><h3>Test the function</h3><ul><li>In a browser, go to the Google Cloud Platform Console UI.</li><li>Select Cloud Functions from the Navigation menu.</li><li>Click on the name of the function you created earlier.</li><li>Select the TESTING option which is the last option present in the options.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-Rd9IUGt_kNbauQLk_PstQ.png" /></figure><ul><li>Pass {“query_type”:”select”} .</li><li>Select TEST THE FUNCTION.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HNq_NwCbDg_qmE9dUZ2CtA.png" /></figure><ul><li>The result should appear: “Task Completed Successfully” (If the test fails, you’ll see a stack trace to help with debugging.)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WGAWhC4YR7RZsEq7cO2sDw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*eraMIdXA_rT4Zbz9Wey0ZQ.gif" /><figcaption>DONE !</figcaption></figure><p>We successfully made a connection from Cloud Function to AlloyDB. I hope you learnt how we can access VPC resources from serverless compute service like Cloud Function.</p><p>Thanks for reading !</p><p><strong>Additional References :</strong></p><ul><li><a href="https://cloud.google.com/functions/docs/?utm_source=codelabs&amp;utm_medium=et&amp;utm_campaign=CDR_jkl_databases_dbcodelabs_&amp;utm_content=-">Cloud Functions documentation</a></li><li><a href="https://cloud.google.com/alloydb/docs">AlloyDB Documentation</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9baef13ac5ca" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/connect-to-alloydb-for-postgresql-using-cloud-functions-9baef13ac5ca">Connect to AlloyDB for PostgreSQL using Cloud Functions</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Query Multiple Google Cloud Databases using Federated Queries (Part -2 : Cloud Spanner)]]></title>
            <link>https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-2-cloud-spanner-6e2b9f7d6ec8?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/6e2b9f7d6ec8</guid>
            <category><![CDATA[cloud-spanner]]></category>
            <category><![CDATA[federated-queries]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Fri, 17 Mar 2023 09:49:08 GMT</pubDate>
            <atom:updated>2023-03-17T13:37:02.512Z</atom:updated>
            <content:encoded><![CDATA[<h3>Query Multiple Google Cloud Databases using Federated Queries (Part -2 : Cloud Spanner)</h3><p>In this blog we will see how we can query multiple Google Cloud Databases using Federated Queries.</p><p>This is the second part of the blog where we will look into how we can run federated queries using BigQuery and Cloud Spanner.</p><p>If you are not aware about federated queries or checking federated queries with Cloud SQL, I will highly recommend you to go through the <a href="https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-1-cloud-sql-a9610b576ac9">first part</a> of this blog.</p><p><a href="https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-1-cloud-sql-a9610b576ac9">Part 1 : Google Cloud Federated Queries with Cloud SQL</a></p><p><strong>Part 2 : Google Cloud Federated Queries with Cloud Spanner</strong></p><p>Let’s look into some basic steps which are performed when we query from BigQuery to Cloud Spanner.</p><h3>Steps</h3><ul><li>Send a query statement using <strong>EXTERNAL_QUERY </strong>function to Cloud Spanner.</li><li>The query statement is performed using <strong>BigQuery Connection API.</strong></li><li>Cloud Spanner returns result back as a temporary table.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/653/1*18Efn2avpRb5lkzUACxcLg.png" /><figcaption>BigQuery to Cloud Spanner Federated Query</figcaption></figure><h3>Pre-requisites to execute Federated Queries in GCP</h3><ol><li><strong>GCP CloudSpanner instance — </strong>Cloud Spanner instance would be required to fetch data from one of the table which exists on the instance.</li><li><strong>Cloud Spanner Database Admin</strong> — Cloud Spanner Database Admin access would be required on your project.</li><li><strong>GCP BigQuery Admin User Access — </strong>bigquery.admin access would be required to create a connection resource in BigQuery.</li><li><strong>GCP BigQuery additional User (optional)</strong> — Admin user will need to provide access of the connection resource to additional user/s who might be using the connection resource.</li></ol><p>And That’s it, These are the accesses and resources which are required to perform federated query.</p><blockquote>BigQuery Spanner federation enables BigQuery to query data residing in Spanner in real-time, without copying or moving data.</blockquote><p>Before we jump into the demo, please check the list of supported regions for performing the federated query operation.</p><ul><li><a href="https://cloud.google.com/spanner/docs/instance-configurations">Spanner regional and multi-regional configurations</a></li><li><a href="https://cloud.google.com/bigquery/docs/locations">BigQuery dataset locations</a></li></ul><blockquote>Federated queries are only supported in regions that support both the external data source and BigQuery.</blockquote><h3>Let’s look into the DEMO now,</h3><ul><li>Step — 1 : Let’s check the data which is available in Cloud Spanner instance by querying the the passenger_dtl table. Currently we have 10 rows available in the passenger_dtl table and we will try to query this data from BigQuery.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IDALB4m3jgo6uBjDuCOhgg.png" /><figcaption>Querying Spanner Data</figcaption></figure><ul><li>Step — 2 : Enable the BigQuery Connection API, click here to enable.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*HZSa_F-_olsSHgCg.png" /><figcaption>Enable BigQuery API</figcaption></figure><ul><li>Step 3 : — Go to BigQuery and click on Add data to add the external data source.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TLm-EEgqoIW-hUEw.png" /><figcaption>Click on ADD DATA</figcaption></figure><p>Step 4 : — Copy the project id, instance id and the database name from Cloud Spanner console and create a string in the following format.</p><pre>projects/PROJECT_ID/instances/INSTANCE/databases/DATABASE</pre><p>add the details of external data source(Cloud Spanner).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0qwmyTeatqIuRNkwiF9TIg.png" /><figcaption>Add Details</figcaption></figure><p>Step 5 : — Once the external source starts appearing in the datasets click on query.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gx6JK2Q0dy6e9DckaMdl8g.png" /><figcaption>Query External Data Source</figcaption></figure><ul><li>Step 6: — Let’s query our Cloud Spanner data from passenger detail table and see the results in BigQuery.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ifRXxaEMGOlrChfkCYhG7A.png" /><figcaption>Querying Spanner Data from BigQuery</figcaption></figure><p>and here we got all the records from our table.</p><p>And that’s it. We successfully executed federated queries on Cloud Spanner Postgres environment from BigQuery.</p><p>Thanks for reading !</p><p><strong>Additional References :</strong></p><ul><li><a href="https://cloud.google.com/bigquery/docs/federated-queries-intro">Federated Query on Google Cloud Platform</a></li><li><a href="https://cloud.google.com/bigquery/docs/federated-queries-intro#limitations">Limitations of Federated Query on GCP</a></li><li><a href="https://cloud.google.com/bigquery/docs/federated-queries-intro#pricing">Pricing of Federated Query on GCP</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6e2b9f7d6ec8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-2-cloud-spanner-6e2b9f7d6ec8">Query Multiple Google Cloud Databases using Federated Queries (Part -2 : Cloud Spanner)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Query Multiple Google Cloud Databases using Federated Queries (Part -1 : Cloud SQL)]]></title>
            <link>https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-1-cloud-sql-a9610b576ac9?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/a9610b576ac9</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[federated-queries]]></category>
            <category><![CDATA[cloud-sql]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Fri, 24 Feb 2023 12:55:39 GMT</pubDate>
            <atom:updated>2023-03-20T10:03:01.001Z</atom:updated>
            <content:encoded><![CDATA[<p>In this blog we will see how we can query multiple Google Cloud Databases using Federated Queries. This is the first part of the blog where we will look into basics of Federated Queries and then how we can run these queries using BigQuery and Cloud SQL.</p><p>Part 1 : Google Cloud Federated Queries with Cloud SQL</p><p><a href="https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-2-cloud-spanner-6e2b9f7d6ec8">Part 2 : Google Cloud Federated Queries with Cloud Spanner</a></p><p>Before we begin, let’s understand what is Federated Queries and where it can be useful.</p><h3>What are Federated Queries ?</h3><ul><li>Federated means — to form or be formed into a single centralised unit, within which each state or organisation keeps some internal autonomy.</li><li>Federated queries are useful when you need to join data from multiple sources that are not physically located in the same place or within the same database.</li></ul><p>Now we know the meaning and use of federated queries, let’s quickly see how Google Cloud Supports this federated query option and what are the pre-requisites to perform federated queries on GCP.</p><h3>Federated Query from BigQuery to CloudSQL</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/653/1*3zov5YanKqelKt9fdG4CKw.png" /><figcaption>BigQuery to CloudSQL Federated Query</figcaption></figure><h3>Steps</h3><ul><li>Send a query statement using <strong>EXTERNAL_QUERY </strong>function to Cloud SQL.</li><li>The query statement is performed using <strong>BigQuery Connection API.</strong></li><li>Cloud SQL returns result back as a temporary table.</li></ul><h3>Pre-requisites to execute Federated Queries in GCP</h3><ol><li><strong>GCP CloudSQL instance — </strong>Cloud SQL instance would be required to fetch data from one of the table which exists on the instance.</li><li><strong>GCP BigQuery Admin User Access — </strong>bigquery.admin access would be required to create a connection resource in BigQuery.</li><li><strong>GCP BigQuery additional User (optional)</strong> — Admin user will need to provide access of the connection resource to additional user/s who might be using the connection resource.</li></ol><p>And That’s it, These are the accesses and resources which are required to perform federated query.</p><p>Before we jump into the demo, please check the list of supported regions for performing the federated query operation.</p><ul><li><a href="https://cloud.google.com/sql/docs/mysql/locations">Cloud SQL instance location</a></li><li><a href="https://cloud.google.com/bigquery/docs/locations">BigQuery dataset locations</a></li></ul><blockquote>Federated queries are only supported in regions that support both the external data source and BigQuery.</blockquote><h3>Now, It’s DEMO time</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*7dyZQK3aOhHPNyp5MgFzIg.gif" /><figcaption>YAY !</figcaption></figure><ul><li>Step — 1 : Let’s check the data which is available in Cloud SQL instance by querying the the passenger table. Currently we have 10 rows available in the passenger table and we try to query this data from BigQuery.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hAIEoCLGt21otXdbQf-43Q.png" /><figcaption>Data From CloudSQL</figcaption></figure><ul><li>Step — 2: Enable the BigQuery Connection API, click here to enable.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z6h3Qif6q8TTu-ro5RNP5g.png" /><figcaption>Enable BigQuery Connection API</figcaption></figure><ul><li>Step 3 :— Go to BigQuery and click on Add data to add the external data source.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7D82gmo7RhbSVoloijEzxg.png" /><figcaption>Click on ADD DATA</figcaption></figure><ul><li>Step 4 : — Copy the Cloud SQL connection name, database name, database region, username and password from the Cloud SQL overview page.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vQXuMd7PDOFiXUPsdFgICw.png" /></figure><ul><li>Step 5 : — Fill in all the details for external data sources, for this demo we are using Cloud SQL’s postgres flavour. Click on create connection.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lQqXjpWhWb7LE9vmdlRXVQ.png" /><figcaption>Add External Data Source</figcaption></figure><ul><li>Step 6 : — Once the external source starts appearing in the datasets click on query.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7duSiFyhpRwKUD-9KF1pyQ.png" /><figcaption>Click on Query to query Cloud SQL database</figcaption></figure><ul><li>Step 7 : — Let’s query our Cloud SQL data from passengers table and see the results in BigQuery.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5xTpeIGtF_te1u1erRStoA.png" /><figcaption>Federated Query execution on Cloud SQL</figcaption></figure><p>and here we got all the records from our table.</p><ul><li>Step 8 : — We can also check the number of tables which are available in the particular database by executing below shown query.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WZsXBIgR0Qq3vAVHBP3xVw.png" /><figcaption>Query to check available tables from Postgres</figcaption></figure><p>And that’s it. We successfully executed federated queries on Cloud SQL Postgres environment from BigQuery. Stay tuned for the upcoming blog where we will see how the federated queries can be executed on Spanner. Thanks for reading !</p><p><strong>Additional References :</strong></p><ul><li><a href="https://cloud.google.com/bigquery/docs/federated-queries-intro">Federated Query on Google Cloud Platform</a></li><li><a href="https://cloud.google.com/bigquery/docs/federated-queries-intro#limitations">Limitations of Federated Query on GCP</a></li><li><a href="https://cloud.google.com/bigquery/docs/federated-queries-intro#pricing">Pricing of Federated Query on GCP</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a9610b576ac9" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/query-multiple-google-cloud-databases-using-federated-queries-part-1-cloud-sql-a9610b576ac9">Query Multiple Google Cloud Databases using Federated Queries (Part -1 : Cloud SQL)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[DICOM data ingestion into GCP using Cloud Healthcare API (Part-3)]]></title>
            <link>https://medium.com/google-cloud/dicom-data-ingestion-into-gcp-using-cloud-healthcare-api-part-3-c425b2e61cad?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/c425b2e61cad</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[healthcare]]></category>
            <category><![CDATA[dicom]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[healthcare-api]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Tue, 24 Jan 2023 08:07:08 GMT</pubDate>
            <atom:updated>2023-01-24T08:35:36.933Z</atom:updated>
            <content:encoded><![CDATA[<p>In this blog, we would be discussing about what are DICOM datasets and how we can ingest DICOM instance into GCP using Cloud Healthcare API.</p><p>If you haven’t seen the previous Healthcare API blogs, please click <a href="https://medium.com/google-cloud/fhir-data-ingestion-using-gcps-cloud-healthcare-api-part-1-8f961714ff9e">here</a> to go through it.</p><h4><strong>What is DICOM ?</strong></h4><p>DICOM stands for <strong>Digital Imaging and Communications in Medicine</strong>, DICOM is a data interchange protocol, digital image format and file structure <strong>for biomedical images and images related information</strong>. DICOM is the standard for the communication and management of medical imaging information and related data.</p><p>DICOM is used worldwide to store, exchange and transmit medical images. It incorporates standards for imaging modalities such as radiography, ultrasonography, computed tomography (CT), <a href="https://en.wikipedia.org/wiki/Magnetic_resonance_imaging">magnetic resonance imaging</a> (MRI), and radiation therapy. It includes protocols for image exchange (e.g., via portable media such as DVDs), image compression, 3-D visualisation, image presentation, and results reporting. For more information about DICOM, click <a href="https://www.dicomstandard.org/about/">here</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pNqwDTg2KEqYw0p7uiGnIw.jpeg" /><figcaption>Radiography Sample</figcaption></figure><h4>How GCP supports DICOM instance ingestion ?</h4><p>The Cloud Healthcare API provides operations for reading DICOM instances, studies, and series that are consistent with the DICOMweb standard. GCP also provides a DICOM store which is a data store in the Cloud Healthcare API that holds all of your DICOM instances.</p><p>We would be ingesting the DICOM dataset into GCP, extracting and exporting the dataset metadata into BigQuery, which can be further used for analysis.</p><h3>Let’s jump into a short experiment to ingest DICOM data into GCP</h3><h4>Data Collection</h4><p><strong>Step 1: </strong>We need the DICOM datasets for ingestion, we can get the sample data from this <a href="https://cloud.google.com/healthcare-api/docs/resources/public-datasets/nih-chest#gcp_data_access">link</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9dz2MBRz_mUfa3OvJuy4SQ.png" /><figcaption>DICOM data collection</figcaption></figure><p>For this experiment we would be using sample dataset of NIH Chest X-rays, to download sample instances of DICOM, click on <a href="https://console.cloud.google.com/storage/browser/gcs-public-data--healthcare-nih-chest-xray?_ga=2.250101314.511096200.1674448786-1816682141.1674208751">Go to the NIH chest x-ray dataset in Cloud Storage</a>.</p><p><strong>Step 2</strong>: On Google Cloud Console, select appropriate billing project as download or data transfer operations require a billing project from requester.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zsg9F8hYtOCGA6plP9nXMg.png" /><figcaption>Select a Billing Project</figcaption></figure><p><strong>Step 3: </strong>Select the dicom folder and transfer/ download DICOM instances/ files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O5mtD2jLv5OAgum6gSsr_Q.png" /><figcaption>select dicom</figcaption></figure><p>you will see all the sample DICOM instances available. Upload them to your cloud GCS location.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dQQR-Uj4b7a0mfo_zj4fNw.png" /><figcaption>transfer/ download DICOM files</figcaption></figure><p><strong>Step 4: </strong>From Google Cloud Console, goto Healthcare API.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MKhiv7ZzgeugXn0BonJ5bA.png" /><figcaption>visit Healthcare API</figcaption></figure><p><strong>Step 5: </strong>In the Healthcare API browser click on Create Dataset to create a DICOM dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*84nxA-X_5mEfzpU0iCwK0w.png" /><figcaption>create dataset</figcaption></figure><p>fill in the appropriate parameter values and click on Create.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Gpn5H_Ew4yDfEBZzlsIMYA.png" /><figcaption>fill in the Dataset properties</figcaption></figure><p><strong>Step 6: </strong>Once the dataset is created, it will appear in the Healthcare browser.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ps83ek1l6SZ17FMfoAXh9Q.png" /><figcaption>newly created dataset</figcaption></figure><p><strong>Step 7: </strong>Let’s create a DICOM data store inside the DICOM dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uPV7x6cnAxHhzp8RE4GYaw.png" /><figcaption>create data store</figcaption></figure><p>Fill in the properties of the data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cuizM0tiHUos3aB9xu2dpA.png" /><figcaption>Data Store Type and ID</figcaption></figure><p>keep the “Stream resource changes to BigQuery” as is and move to the next section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zubZEcVQaPu5pA7B_Lf4nA.png" /><figcaption>select Pub/Sub topic</figcaption></figure><p>The newly created data store will appear under the dataset as shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eJxBCXs5zmG9si3f8TPEbQ.png" /><figcaption>Newly created DICOM data store</figcaption></figure><p><strong>Step 8: </strong>From action’s section click on import, to import DICOM datasets to DICOM Healthcare data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HPWX1ulGxEvutAS9B6NhDQ.png" /><figcaption>import DICOM dataset</figcaption></figure><h4>Data Ingestion</h4><p><strong>Step 9: </strong>Select the appropriate project and Google Cloud Storage location.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XspEienphP0YUtDojXGSHg.png" /><figcaption>Appropriate project and google cloud storage location selection</figcaption></figure><p>Click on import to start importing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1SJB4FhrJ9AEGKeTQabf2g.png" /><figcaption>import</figcaption></figure><p><strong>Step 10: </strong>Check the logs of this operation( in the operation’s section).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pvyhGhyJgZDwpLNRIbRWxQ.png" /><figcaption>Import Logs</figcaption></figure><h4>Export Data</h4><p><strong>Step 11: </strong>Let’s create a BigQuery table where we can ingest the metadata of the DICOM datasets.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gBFXz5FvkJmpHhBs1sslsw.png" /><figcaption>create a table in BigQuery</figcaption></figure><p><strong>Step 12: </strong>As a next part of our experiment, we will export the metadata like (patient ids, patient gender, dataset disease information, etc.).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_n36ZnTtwaAw5GV4LdhlMg.png" /><figcaption>Click on export</figcaption></figure><p><strong>Step 13: </strong>Fill in export parameters and click on export to export the metadata of the ingested 13 DICOM resources to BigQuery.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PMDdKd9pNJtlyKX5Z27q0Q.png" /><figcaption>Export</figcaption></figure><p><strong>Step 14: </strong>Check for the logs of export operation in the Operation’s section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TZTesxhF6yEAZODiIys1Aw.png" /><figcaption>Export Logs</figcaption></figure><p><strong>Step 15: </strong>Query the BigQuery table created to check the data, we can see there are fields such as the DICOM Implementation version, PatientID, Findings, PatientAge, etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1zbPSOZ9-N0FgcbxETQQwQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yjBd1ZBy2a23gekXNpM2gg.png" /></figure><p>That’ it! Today we learn how we can ingest, store and perform analysis on Healthcare imaging data using the Healthcare API and DICOM datasets. Thanks for reading! See you next time.</p><p><strong>References:</strong></p><ul><li><a href="https://cloud.google.com/healthcare-api">Cloud Healthcare API</a></li><li><a href="https://cloud.google.com/healthcare-api/docs/concepts/dicom">DICOM | Cloud Healthcare API</a></li><li><a href="http://dicomstandard.org/">DICOM</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c425b2e61cad" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/dicom-data-ingestion-into-gcp-using-cloud-healthcare-api-part-3-c425b2e61cad">DICOM data ingestion into GCP using Cloud Healthcare API (Part-3)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[FHIR Data De-identification using Cloud Healthcare API(Part -2)]]></title>
            <link>https://medium.com/google-cloud/fhir-data-de-identification-using-cloud-healthcare-api-part-2-6af3fa34cc01?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/6af3fa34cc01</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[de-identification]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[fhir]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Tue, 03 Jan 2023 04:28:11 GMT</pubDate>
            <atom:updated>2023-01-03T07:25:44.969Z</atom:updated>
            <content:encoded><![CDATA[<p>Welcome Back! In this blog we will continue to learn more about FHIR data transformation, especially how data de-identification can be done on GCP using Cloud Healthcare API. We would be using the FHIR data store which we created in the previous blog. If you haven’t followed the previous blog, click <a href="https://medium.com/google-cloud/fhir-data-ingestion-using-gcps-cloud-healthcare-api-part-1-8f961714ff9e">here</a>.</p><p>As I mentioned this blog is going to be very specific around FHIR data de-identification and insertion of de-identified FHIR data into BigQuery.</p><p>You might be thinking about what is data de-identification and why it is needed ?</p><p><strong>What is data de-identification ?</strong></p><p>Data de-identification is the process of <strong>removing or obfuscating</strong> Personally Identifiable Data (PII data) from any documents or other information that also contains that person’s Protected Health Information (PHI). To know more about the data de-identification standard provided by HIPAA click <a href="https://www.hhs.gov/hipaa/for-professionals/privacy/special-topics/de-identification/index.html#:~:text=The%20De%2Didentification%20Standard,-Section%20164.514(a&amp;text=Under%20this%20standard%2C%20health%20information,used%20to%20identify%20an%20individual.">here</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/486/1*4vHccTOLWIBIHkY-YK4h2A.png" /><figcaption>Data de-identification</figcaption></figure><p><strong>What is the use of data de-identification in Healthcare ?</strong></p><p>De-identified data can be used for analysis, training, and evaluating machine learning models and for sharing with non-privileged parties, while protecting patient privacy.</p><p>As we know what is data de-identification and why it is important, let’s do quick demo to understand how the data de-identification of FHIR data can be performed using Google Cloud Platform’s Cloud Healthcare API.</p><h3>Steps to de-identify the FHIR data :</h3><p><strong>Step-1 : </strong>If you have not created FHIR data store, please follow the steps provided in <a href="https://medium.com/google-cloud/fhir-data-ingestion-using-gcps-cloud-healthcare-api-part-1-8f961714ff9e">part-1</a> of this series.</p><p><strong>Step-2 : </strong>Let’s create one more Healthcare dataset and FHIR data store to save the de-identified data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0k7pqVj1RPZb8rymnrqpdQ.png" /><figcaption>Creation of de-identified dataset</figcaption></figure><p>The created dataset will be shown on the Healthcare browser screen</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lWIjRaCcbSAFkgKnArmLsg.png" /><figcaption>Healthcare Browser</figcaption></figure><p>Now, lets create a FHIR data store within de-identified-dataset</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UClM5llDoS6ns3MUL9a0dw.png" /><figcaption>Data Store Type and ID</figcaption></figure><p>Configure the data store</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_KDFeQro1xDRqwDkujYXEA.png" /><figcaption>Data Store Configuration</figcaption></figure><p>Keep stream resource changes to BigQuery as is, select/ create a pub/sub topic for notifications and Click on create.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RCa8IWQE_4l_6tF81xOzNg.png" /><figcaption>Create Data Store</figcaption></figure><p>You would be able to see the newly created data store under de-identified-dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6rtf-yZ_mYWHNZG9LBqvMw.png" /><figcaption>Newly Created Dataset</figcaption></figure><p><strong>Step-3</strong> : Let’s create one more BigQuery dataset to store de-identified data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ojvxy6k_L8oJMWI9XiMVoQ.png" /><figcaption>Create BigQuery Dataset to store de-identified data</figcaption></figure><p><strong>Step-4 : </strong>Let’s go back to the Healthcare browser, select the ‘healthcare-fhir-test’ dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JZ0BwrxI3ciEdFFjoS67-g.png" /><figcaption>select healthcare-api-fhir-test dataset</figcaption></figure><p><strong>Step-5 : </strong>Select De-identify from the actions of data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L_i8JXCo84K6QTTZ1TVidg.png" /><figcaption>select De-identify from the Actions</figcaption></figure><p><strong>Step-6 : </strong>Select appropriate dataset and data store values where the de-identified data should get stored.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UWxqMYKosNobJ7cbRwrstA.png" /><figcaption>De-identify FHIR store</figcaption></figure><p><strong>Step-7 : </strong>On the pop-up screen, click on Append to continue.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XvRD1OA9_2DeiMvW_BqHdQ.png" /><figcaption>Append to continue</figcaption></figure><p>Click on de-identify and it will start de-identifying the data.</p><p><strong>Step-8 : </strong>Let’s check if the de-identification is complete in operations section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_tMwYPqsNK2H8ILi6tSqSQ.png" /><figcaption>Logs of data de-identification operation</figcaption></figure><p><strong>Step-9 : </strong>Let’s check the de-identified data by opening the FHIR viewer from the actions of de-identified data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YczepCevlvsIaabRKT1jRQ.png" /><figcaption>Open FHIR viewer of de-identified data store</figcaption></figure><p><strong>Step-10 : </strong>Search for patient resource as we only have patient resource in our main data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PviUNO2KPIhfGAfAwAvgIg.png" /><figcaption>select Patient resource</figcaption></figure><p>here is the de-identified data, where we can clearly see that Identifiers,Given Name, Family Name is de-identified.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*w-JgpFbzadUUq_j6xWExog.png" /><figcaption>De-identified patient resource</figcaption></figure><p><strong>Note : </strong>To check how Cloud Healthcare API de-identifies the data click <a href="https://cloud.google.com/healthcare-api/docs/how-tos/fhir-deidentify?_ga=2.106348675.-1866878781.1654688785#default_fhir_data_de-identification">here</a>.</p><p><strong>Step-11 : </strong>Let’s export the de-identified data into BigQuery using the same export option which we have used in the previous part.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W_mZVCs4w3zzwAH8Ago4nw.png" /><figcaption>Export de-identified data to BigQuery</figcaption></figure><p><strong>Step-12 : </strong>Let’s explore the de-identified dataset and patient table in BigQuery.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IdEO2naL-jZ0WkodNYa1gQ.png" /><figcaption>BigQuery de-identified dataset</figcaption></figure><p><strong>Done! We have successfully de-identified FHIR patient resource and exported it to BigQuery dataset.</strong></p><p>In the upcoming blogs, we will learn more about HL7V2 messages and DICOM datasets. So stay tuned! Thank you!</p><p><strong>References</strong></p><ul><li><a href="https://cloud.google.com/healthcare-api/docs/how-tos/fhir-deidentify?_ga=2.106348675.-1866878781.1654688785#default_fhir_data_de-identification">Data de-identification Healthcare API Documentation</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6af3fa34cc01" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/fhir-data-de-identification-using-cloud-healthcare-api-part-2-6af3fa34cc01">FHIR Data De-identification using Cloud Healthcare API(Part -2)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[FHIR Data Ingestion using GCP’s Cloud Healthcare API(Part -1)]]></title>
            <link>https://medium.com/google-cloud/fhir-data-ingestion-using-gcps-cloud-healthcare-api-part-1-8f961714ff9e?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/8f961714ff9e</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[fhir]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[healthcare-api]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Mon, 26 Dec 2022 04:50:08 GMT</pubDate>
            <atom:updated>2023-02-01T16:16:16.259Z</atom:updated>
            <content:encoded><![CDATA[<p>In this article we would be exploring how FHIR data ingestion and analytics can be performed on GCP using <strong>Healthcare API and BigQuery.</strong></p><p><a href="https://medium.com/google-cloud/fhir-data-ingestion-using-gcps-cloud-healthcare-api-part-1-8f961714ff9e"><strong>Part 1</strong></a> will cover how FHIR data can be ingested into FHIR store on GCP and analysed using BigQuery’s analytics Capabilities.</p><p><a href="https://medium.com/@sudharmamokashi18/fhir-data-de-identification-using-cloud-healthcare-api-part-2-6af3fa34cc01"><strong>Part 2</strong></a><strong> </strong>will cover how we can perform data de-identification operation on FHIR.</p><p><a href="https://medium.com/google-cloud/dicom-data-ingestion-into-gcp-using-cloud-healthcare-api-part-3-c425b2e61cad"><strong>Part 3</strong></a><strong> </strong>will cover how DICOM datasets can be ingested into GCP and de-identified later.</p><p>For those who are new to <strong>FHIR</strong>(Fast Healthcare Interoperability Resources), it is a data standard which is followed by Healthcare industries to ease the interoperability of healthcare data. Learn more about <a href="https://www.hl7.org/fhir/overview.html">FHIR</a>.</p><p><strong>[Looking for more information about FHIR? Read </strong><a href="http://bit.ly/FHIRCareGaps"><strong>Care Gaps on FHIR to improve quality of patient care</strong></a><strong>.]</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/551/1*3rR6iU3jryrqMIk86uK2Bw.png" /></figure><p>Topics which would be covered in this article are as follows :</p><ul><li>Cloud Healthcare API</li><li>Ingestion of FHIR data into GCPs FHIR store</li><li>Export FHIR data to BigQuery</li></ul><h3>What is Cloud Healthcare API ?</h3><p>Healthcare API is a mediator between the healthcare systems and applications built on Google Cloud Platform. Using healthcare API one can connect their data to advanced Google Cloud Capabilities, including streaming data processing with Cloud Dataflow, scalable analytics with BigQuery, and machine learning with Cloud Machine Learning Engine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*c4vafSm-IIsTOuLKn0557w.png" /><figcaption>FHIR Data ingestion into GCP using Healthcare API</figcaption></figure><blockquote>Google Cloud provides detailed guidance regarding how it supports compliance with <strong>HIPAA</strong> in the US, the PIPEDA in Canada, and other global privacy standards at <a href="http://cloud.google.com/security/compliance">cloud.google.com/security/compliance</a>.</blockquote><blockquote>The Cloud Healthcare API treats data location as a core component of the API. You have the option to select the storage location for each dataset from a list of currently available locations which correspond to distinct geographic areas aligned with Google Cloud’s regional structure.</blockquote><p>Let’s deep dive into a quick demo which will cover FHIR data ingestion into Google Cloud Platform using <strong>Cloud Healthcare API</strong>. For this demo we are using FHIR data which is stored on GCS bucket in .ndjson(newline — delimited) format.</p><h3>Steps to ingest FHIR data into GCP</h3><ol><li>Enable the Healthcare API — To Enable the Healthcare API, follow the <a href="https://console.cloud.google.com/apis/library/healthcare.googleapis.com?project=&lt;project_nm&gt;">link</a> and click Enable.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qf_xUEIs2S01diSrqZCFKg.png" /></figure><p>2. Create a BigQuery dataset to store the exported data for analysis. Go to the BigQuery Console and create the dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*875gkXFdfLcisIiSzOieGQ.png" /><figcaption>BigQuery dataset creation</figcaption></figure><p>3. Execute the below mentioned commands on cloud shell to attach the right IAM roles to healthcare api’s service account.</p><pre>export PROJECT_ID=$(gcloud config list --format &#39;value(core.project)&#39;)<br>export PROJECT_NUMBER=$(gcloud projects list --filter=projectId:$PROJECT_ID \<br>  --format=&quot;value(projectNumber)&quot;)<br>gcloud projects add-iam-policy-binding $PROJECT_ID \<br>--member=serviceAccount:service-$PROJECT_NUMBER@gcp-sa-healthcare.iam.gserviceaccount.com \<br>--role=roles/bigquery.dataEditor<br>gcloud projects add-iam-policy-binding $PROJECT_ID \<br>--member=serviceAccount:service-$PROJECT_NUMBER@gcp-sa-healthcare.iam.gserviceaccount.com \<br>--role=roles/bigquery.jobUser</pre><p>4. Create healthcare dataset by searching Healthcare in the navigation menu and click on Create Dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2cu7AycRkY7ibvQ0OIdEfA.png" /><figcaption>Navigate to Healthcare section</figcaption></figure><p>set the below mentioned properties for healthcare dataset</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E4D25DagP31xlyhJojY2tg.png" /><figcaption>set the healthcare dataset properties</figcaption></figure><p>5. It will take sometime for the dataset creation, once it is created you would be able to see the dataset in the healthcare browser as shown below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*msox5hNxmVWIL7yDpyLZXA.png" /><figcaption>Healthcare Browser</figcaption></figure><p>6. Click on the recently created dataset to create a FHIR store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zeTuNfykDaPZ1dBrvAYJLw.png" /><figcaption>Create Data Store</figcaption></figure><p>set the below mentioned properties for data store</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*paO_DFQuqfxwLd0AQj5Vrg.png" /><figcaption>Data Store Type and ID</figcaption></figure><p>select the FHIR store version and click next</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YGaOaaXd89_Jw7_IvJSr3g.png" /><figcaption>select FHIR store version</figcaption></figure><p>make <strong>no changes</strong> in BigQuery stream section and move forward to Receive Cloud Pub/Sub notifications section, select Create A Topic option.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DGYGHlUzub_Udz6k6BmcKA.png" /><figcaption>Create pub/sub topic for receiving notifications</figcaption></figure><p>provide topic id, encryption mechanism and click on create topic</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uaClyWWQR8EXe1dwpJhj8w.png" /><figcaption>Create the topic required</figcaption></figure><p>Click on create to create a FHIR data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HW8QI2hctj8Zdgjeruuh0Q.png" /><figcaption>Create FHIR data store</figcaption></figure><p>7. Import FHIR data from Google Cloud Storage bucket to the recently created FHIR data store.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c6BGaQLjJ5THpFg0bUDpOA.png" /><figcaption>Import data</figcaption></figure><p>8. Before importing the dataset, provide object viewer permission to healthcare service account by running the following command in cloud shell</p><pre>gcloud projects add-iam-policy-binding $PROJECT_ID --member=serviceAccount:service-$PROJECT_NUMBER@gcp-sa-healthcare.iam.gserviceaccount.com --role=roles/storage.objectViewer</pre><p>9. Now select the appropriate project,GCS location and Content Structure and create import.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8r7P6uc4uhjF7fLJ_h_flA.png" /><figcaption>Import FHIR data</figcaption></figure><p>10. Go to operations to check the logs and process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sHv1FLfyBgEL2Qr2pEN5cw.png" /><figcaption>Check the logs in operations</figcaption></figure><p>here you can see all the resources were successfully imported</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*93lbUuwldtKv1DRox0GTvg.png" /><figcaption>Import Logs</figcaption></figure><p>11. Select Open in FHIR viewer from the data store window to check the recently imported data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RUgCTAqWa8hklTwue775EQ.png" /><figcaption>Open in FHIR Viewer</figcaption></figure><p>12. As part of this demo, we have inserted sample patient resource data, hence search for the patient resource.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2sengSVCjG3Y4eb87CIvXA.png" /><figcaption>search for patient resource</figcaption></figure><p>13. Patient FHIR data would be available on the FHIR store explorer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K7lUKe7c2zIcWF7sHKVsIQ.png" /><figcaption>FHIR store explorer</figcaption></figure><p>14. Apart from this you can select individual record and see its data in form of elements as well as raw json on the right hand side (inside FHIR viewer as shown in above mentioned step).</p><ul><li>Elements</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6QilVYkG4HtCUFubBoMgxg.png" /><figcaption>Elements of resource</figcaption></figure><ul><li>Json</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QQi_taZxkdATLGIK20wUPA.png" /><figcaption>Raw Json</figcaption></figure><p>15. Export the data to BigQuery for analysis using export option on Data Store page</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mH_3KiPY6CPq2k3psi2xPg.png" /><figcaption>Select Export Option</figcaption></figure><p>provide export options and click export.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*puYct7VmXJrXZ9sSKScD5g.png" /><figcaption>Export FHIR resource data to BigQuery</figcaption></figure><p>16. Similar to the import operation, if we want to check the logs of this operation we can check the operations window and select the required operation(in this case export one).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JeMzoVhXkqPJ-2zJdKaRZw.png" /><figcaption>Dataset Export operation successfully completed</figcaption></figure><p>As we have exported the Patient resource data, it will create a table with name Patient in specified BigQuery dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1v3d_9_PSqXLiwaeITHQXA.png" /><figcaption>Data Exported to BigQuery</figcaption></figure><p>That’s it! You’re ready to perform the analysis in BigQuery. Thanks for reading the blog, see you next time!</p><p><strong>References</strong></p><ul><li><a href="https://cloud.google.com/healthcare-api">Cloud Healthcare API</a></li><li><a href="https://cloud.google.com/healthcare-api/docs">Cloud Healthcare API Documentation</a></li><li><a href="https://www.youtube.com/watch?v=_aZZpAYzoHU">Healthcare API</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8f961714ff9e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/fhir-data-ingestion-using-gcps-cloud-healthcare-api-part-1-8f961714ff9e">FHIR Data Ingestion using GCP’s Cloud Healthcare API(Part -1)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analytics Hub - GCP’s Data Exchange Solution]]></title>
            <link>https://medium.com/google-cloud/analytics-hub-gcps-data-exchange-solution-2e01aca6a071?source=rss-faca7cf54e0------2</link>
            <guid isPermaLink="false">https://medium.com/p/2e01aca6a071</guid>
            <category><![CDATA[data-exchange-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[analytics-hub]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Sudharma Mokashi]]></dc:creator>
            <pubDate>Wed, 21 Dec 2022 13:08:56 GMT</pubDate>
            <atom:updated>2022-12-22T03:58:39.188Z</atom:updated>
            <content:encoded><![CDATA[<p>In this article, we will explore basics of Analytics Hub and also share how to use it to exchange BigQuery datasets within or outside of an organisation — with ease.</p><p>We all know that the existing options for data sharing are really expensive. Moreover, it requires some data sharing setup to be in place and still often end up violating security or governance protocols due to lack of proper auditing capabilities. In addition to these challenges, there are limited options for data sharing, collaboration and scaling. This is where Analytics Hub comes to our rescue!</p><h4><strong>What is Analytics Hub ?</strong></h4><p>Analytics Hub is a data sharing platform created to ease the task of sharing larger data assets within organizations to address the challenge of data reliability and cost at scale. The data you need is rarely stored in one place, often data is scattered across multiple sources and in various formats.</p><p>Analytics Hub not only allows for the curation and sharing of a large selection of analytics-ready datasets globally, it also enables teams to tap into the unique datasets only Google provides, such as Google Search Trends or the Data Commons knowledge graph.</p><p>Analytics Hub allows you to share the analytics-ready datasets in three different options (private exchanges are default setting)</p><ol><li><strong>Public </strong>— Listing shared publicly to all Google Cloud Users</li><li><strong>Private </strong>— Listing shared directly with you or a group you belong to</li><li><strong>Within Organization</strong> — Listing shared with your organization</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/561/1*V-poI0USE5vqjnTzY0nkHw.png" /><figcaption>Analytics Hub | Data Exchange and Data Sharing</figcaption></figure><p><strong>Key Components of Analytics Hub</strong></p><ol><li><strong>Data Exchanges</strong> — Data exchanges bring together the data publisher and data consumer. Inside a data exchange there can be many listings which will allow the user to uniquely identify the data.</li><li><strong>Listings</strong> — Listings are mainly used to used to uniquely identify the data. Listing is nothing but a reference to the dataset which the user has published inside a data exchange.</li><li><strong>Data Publisher</strong> — Data publisher is the one who publishes datasets into Analytics Hub as a listing within an exchange, which can be searched on the listings page of Analytics Hub(if shared publicly).</li><li><strong>Data Subscriber </strong>— Data subscriber is the one who wants to use the shared dataset in their own BigQuery project. Data Subscriber can search for the required dataset by using various filters like category, location, provider and listing type.</li></ol><p>Next up, let’s do a quick hands-on.</p><h3><strong>a. Prerequisites of using Analytics Hub</strong></h3><p>To use Analytics Hub one must enable the Analytics Hub API, but before that they must have following permissions in place</p><ol><li>View APIs in Cloud console — serviceUsage.services.get/list</li><li>Enable APIs— serviceUsage.services.enable</li></ol><p>After adding above mentioned permissions one can enable the Analytics Hub API by following the below mentioned link(please modify the <em>project name</em>).</p><p><a href="https://console.cloud.google.com/marketplace/product/google/analyticshub.googleapis.com?q=search&amp;referrer=search&amp;project=test-project-1-351206">https://console.cloud.google.com/marketplace/product/google/analyticshub.googleapis.com?q=search&amp;referrer=search&amp;project=</a>&lt;<strong>project-name-here</strong>&gt;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_rWUnyXrGuQyTEvJcYTiPQ.png" /><figcaption>Enable Analytics Hub API</figcaption></figure><p>Click on enable to enable the Analytics Hub API.</p><p>Hooray!! You are all set to use Analytics Hub.</p><h3><strong>b. Create Data Exchange and publish a listing</strong></h3><p><strong>Step-1</strong> : You can find Analytics Hub option on the left hand side of BigQuery page</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rQyLwCJTF_nw_OcqcvHkLA.png" /></figure><p><strong>Step-2.a</strong> : Click on create exchange option</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FaV2BUS_s2IbAVKAbNQlIA.png" /><figcaption>Create Exchange</figcaption></figure><p><strong>Step-2.b</strong>: Fill in the exchange related details</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8LCA-KgJBBCaYvgoUvpIEQ.png" /></figure><p><strong>Step-3(<em>Optional</em>)</strong> : Set up permissions for the exchange. More details related to exchange permissions are available below</p><ul><li><strong>Administrators</strong>: these users are assigned the <a href="https://cloud.google.com/bigquery/docs/analytics-hub-grant-roles#ah-admin-role">Analytics Hub Admin role</a>.</li><li><strong>Publishers</strong>: these users are assigned the <a href="https://cloud.google.com/bigquery/docs/analytics-hub-grant-roles#ah-publisher-role">Analytics Hub Publisher role</a>. For information about the tasks that publishers can perform, see <a href="https://cloud.google.com/bigquery/docs/analytics-hub-manage-listings">Manage listings</a>.</li><li><strong>Subscribers</strong>: these users are assigned the <a href="https://cloud.google.com/bigquery/docs/analytics-hub-grant-roles#ah-subscriber-role">Analytics Hub Subscriber role</a>. For information about the tasks that subscribers can perform, see <a href="https://cloud.google.com/bigquery/docs/analytics-hub-view-subscribe-listings">View and subscribe to listings</a>.</li><li><strong>Viewers</strong>: these users are assigned the <a href="https://cloud.google.com/bigquery/docs/analytics-hub-grant-roles#ah-subscriber-role">Analytics Hub Viewer role</a>. Viewers can <a href="https://cloud.google.com/bigquery/docs/analytics-hub-view-subscribe-listings#view_listings">view listings</a>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6W-jWXvcP6o5Idk4vqdX9Q.png" /></figure><p>Once the exchange is created you would be able to see the exchange on the page of Analytics Hub</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DZGdacMrfgxyRCDoxNjSdg.png" /><figcaption>Data Exchange List</figcaption></figure><p><strong>Step-4</strong>: Click on the exchange listed on the Analytics Hub page and create listing</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ehua2zCx2K6BGn6Xo58O1w.png" /></figure><p><strong>Step-5.a</strong> : Fill in the details related to publishing a listing</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5FSxDH5iZ41vjvgO147dwA.png" /></figure><p><strong>Step-5.b</strong> : Create documentation about the listing using markdown language.For a quick reference to the Markdown syntax <a href="https://cloud.google.com/apigee/docs/api-platform/publish/portal/markdown-reference">Click Here</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aiegZE3UB3nCtrKeco_kkA.png" /></figure><p><strong>Step-6 : </strong>Hit save and you are done. Your listing is created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*H7IqzAY_Y7Ma0lpwHm34kg.png" /></figure><h3><strong>c. Use Existing Listing in your project</strong></h3><p><strong>Step-1</strong>: Go to the Analytics Hub page and click on search listing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PWt8E6Kt1-ZEbKg8oGPn2g.png" /></figure><p><strong>Step-2</strong>: Search for a listing which you want to use in your project using search options and filters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q8oTxpwEAbfKwr-dtvPImA.png" /></figure><p><strong>Step-3</strong>: Select the searched listing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0EN3mtF2NmMXQ7UOnsLS8A.png" /></figure><p><strong>Step-4</strong>: Click on add dataset to your project to add the dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*77DNfqEFmQJYeUajbXTriA.png" /></figure><p><strong>Step-5</strong>: Select the project and provide a name to this linked dataset. Click save.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mN2aMsODsOc_pmyjIYD-Tw.png" /></figure><p>You’re done. You would be able to see the linked dataset under your project which you mentioned in the previous step.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pMvp2Udb26DT2RNzN7qQKg.png" /></figure><p>That’s it! Thanks for going through the blog, see you next time!</p><p><strong>Additional References :</strong></p><ul><li><a href="https://cloud.google.com/analytics-hub">Analytics Hub Official Documentation</a></li><li><a href="https://cloud.google.com/analytics-hub#section-6">Analytics Hub Pricing</a></li><li><a href="https://www.youtube.com/watch?v=h8_xCbTYtTU">Analytics Hub in a minute</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2e01aca6a071" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/analytics-hub-gcps-data-exchange-solution-2e01aca6a071">Analytics Hub - GCP’s Data Exchange Solution</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>