<?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 Yunus Durmuş on Medium]]></title>
        <description><![CDATA[Stories by Yunus Durmuş on Medium]]></description>
        <link>https://medium.com/@yunusd?source=rss-c0c51b375633------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Z8qqp7TTSXlxXi0y2RgRYg.jpeg</url>
            <title>Stories by Yunus Durmuş on Medium</title>
            <link>https://medium.com/@yunusd?source=rss-c0c51b375633------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 23:22:10 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@yunusd/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[A Practical Blueprint for Google Cloud’s Lakehouse with Iceberg]]></title>
            <link>https://medium.com/google-cloud/a-practical-blueprint-for-google-clouds-lakehouse-with-iceberg-7618b70efa4b?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/7618b70efa4b</guid>
            <category><![CDATA[lakehouse-architecture]]></category>
            <category><![CDATA[google-cloud-lakehouse]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Wed, 19 Aug 2026 15:02:32 GMT</pubDate>
            <atom:updated>2026-08-20T10:37:05.812Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Catalog topology, namespace IAM gotchas, FinOps chargebacks, and making multi-tenant data mesh actually work on Google Cloud.</em></p><p>If you’ve been keeping up with modern data architectures, you already know that <strong>Apache Iceberg</strong> paired with <a href="https://docs.cloud.google.com/lakehouse/docs/introduction"><strong>Google Cloud’s Lakehouse</strong></a><strong> (based on </strong><a href="https://docs.cloud.google.com/lakehouse/docs/about-lakehouse-catalogs"><strong>Iceberg REST Catalog</strong></a><strong>)</strong> is quickly becoming the gold standard for enterprise lakehouses on Google Cloud.</p><p>It promises the best of both worlds: open table formats on Google Cloud Storage (GCS) and deep engine integration across BigQuery, Managed Spark, and Dataflow.</p><p>However, once you move from a toy POC to a real-world multi-team platform, the hard engineering and governance questions hit:</p><ul><li><em>How many Iceberg catalogs should we create?</em></li><li><em>How do we stop Team A from deleting Team B’s tables or namespaces?</em></li><li><em>Who pays for the storage, and which GCS bucket type should we choose?</em></li><li><em>How does IAM actually work when compute, catalog, and storage live in separate GCP projects?</em></li></ul><p>Let’s break down a battle-tested architectural blueprint for running BigLake Iceberg REST catalogs at enterprise scale.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wnSShmyhdt2qoWMCrA902A.png" /><figcaption>Opinionated view on Google Cloud’s Lakehouse architecture</figcaption></figure><h3>TL; DR: — Summary of recommendations</h3><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c1e8dfd54241d60eccc683731b9e008c/href">https://medium.com/media/c1e8dfd54241d60eccc683731b9e008c/href</a></iframe><h3>1. How Many Catalogs Should You Have? — “One per Environment”</h3><p>A common debate when starting out is whether to create a separate Lakehouse runtime catalog for every data product.</p><p>Technically, you <em>can</em> create a separate catalog for each data product. But in practice, <strong>it quickly becomes an operational nightmare.</strong></p><pre>❌ Anti-Pattern: Catalog per Data Product<br>Spark Session Config:<br>  spark.sql.catalog.marketing_cat = ...<br>  spark.sql.catalog.sales_cat     = ...<br>  spark.sql.catalog.inventory_cat = ...<br>  spark.sql.catalog.finance_cat   = ...<br>  (Dozens of catalog endpoints to maintain in code &amp; federation queries)</pre><pre>✅ Recommended: One Shared Catalog per Environment<br>Spark Session Config:<br>  spark.sql.catalog.lakehouse_prod = ...<br>  (One single endpoint for everything in PROD!)</pre><h4>Why a Catalog-per-Data-Product Fails at Scale</h4><ol><li><strong>Bloated, Fragile Spark Configurations:</strong> If every data product has its own catalog, every Managed Spark pipeline that needs data from multiple domains has to define and configure each catalog endpoint individually in its Spark session. Your code quickly becomes bloated with boilerplate connection blocks and maintenance headaches.</li><li><strong>Painful Federation:</strong> Want to federate your catalog with external iceberg catalogs? For every new catalog, you have to establish yet another federation.</li><li><strong>Single Governance Plane:</strong> Knowledge Catalog can harvest metadata and enforce governance policies from a single centralized entry point per environment (dev, staging, prod).</li></ol><blockquote><em>💡 </em><strong><em>When is an exception justified?</em></strong><em> Only spin up an isolated catalog for a specific business unit if they require </em><strong><em>strict organizational or legal isolation</em></strong><em> (e.g., an acquired subsidiary, strict cross-border data sovereignty compliance, or a unit requiring full autonomous catalog admin rights outside your central CI/CD).</em></blockquote><h3>2. The Lakehouse IAM Catch: Why Namespaces Belong in CI/CD</h3><p>Here is the biggest security gotcha when managing a shared catalog across multiple teams:</p><blockquote><em>⚠️ </em><strong><em>The Gotcha:</em></strong><em> In Google Cloud’s Lakehouse, </em><a href="https://docs.cloud.google.com/lakehouse/docs/create-namespace#required_roles"><em>creating a namespace</em></a><em> requires the </em><strong><em>roles/biglake.admin</em></strong><em> role granted at the </em><strong><em>Catalog level</em></strong><em>.</em></blockquote><p>If you grant individual developers or data product service accounts catalog-level admin rights so they can self-serve their namespaces, <strong>you’ve given them admin rights over the entire catalog.</strong> A rogue script, a typo, or an accidental Terraform run from Team A could alter or delete Team B’s namespaces.</p><pre>❌ Bad: Self-Service Catalog Access<br>Data Product SA ──(biglake.admin on Catalog)──► Can alter/delete ALL Namespaces!</pre><pre>✅ Good: Least-Privilege CI/CD Delegation<br>(i) Terraform CI/CD Pipeline ──(biglake.admin on Catalog) ──► Creates Namespace &quot;marketing&quot;<br>                                                           <br>(ii) Data Product SA ──(biglake.admin at Namespace Level) ──► Creates tables inside Namespace &quot;marketing&quot; and even shares with others.</pre><h4>The Solution: 2-Tiered Governance Model</h4><ol><li><strong>Catalog Level (Managed strictly by CI/CD):</strong> Nobody gets manual admin rights on the central catalog in Production. Namespaces are declared in Infrastructure as Code (Terraform) and provisioned by a centralized platform CI/CD pipeline.</li><li><strong>Namespace Level (Delegated Self-Service):</strong> Once the CI/CD pipeline provisions the namespace (e.g., marketing_silver), it grants roles/biglake.admin to Data Product Team A’s identity scoped strictly to that specific <strong>Namespace</strong>.</li></ol><p>From that point forward, Team A has full self-service freedom to create, alter, evolve, and drop their own Iceberg tables and views inside their namespace — without ever risking neighboring data products.</p><h3>3. Storage Topology &amp; FinOps: Data Product Projects &amp; Buckets</h3><p>A monolithic “central data lake bucket” is an anti-pattern. If all data products dump files into one giant bucket, good luck figuring out which team blew through the storage budget at the end of the quarter.</p><h4>Data Product Project Isolation</h4><p>Place each data product (or logical domain of products) in its own dedicated <strong>GCP Project</strong> (e.g., prj-dp-marketing-prod).</p><ul><li><strong>Direct Cost Attribution (Chargeback):</strong> Storage, network egress, and compute costs are automatically billed to the specific data product’s project.</li><li><strong>Blast Radius Reduction:</strong> IAM permissions, encryption keys (CMEK), and quota limits remain contained.</li></ul><h4>A Single Bucket is Enough per Data Product</h4><p>You don’t need a sprawl of different buckets for every layer. Within a single Data Product Project, <strong>one GCS bucket is sufficient to store both Bronze (raw) and Silver (Iceberg) layers</strong>.</p><p>IAM policies and object prefix paths (e.g., gs://dp-marketing-prod/bronze/ and gs://dp-marketing-prod/silver/) handle access separation and lifecycle rules cleanly without adding bucket management overhead.</p><pre>┌────────────────────────────────────────────────────────┐<br>│ Data Product Project: prj-dp-marketing-prod            │<br>│                                                        │<br>│ ┌────────────────────────────────────────────────────┐ │<br>│ │ GCS Bucket: gs://dp-marketing-prod/                │ │<br>│ │  ├── /bronze/  (Raw landing files + TTL rules)     │ │<br>│ │  └── /silver/  (Iceberg Parquet + Metadata)        │ │<br>│ │  [Autoclass Enabled | Regional or Dual-Region]     │ │<br>│ └────────────────────────────────────────────────────┘ │<br>│                                                        │<br>│ ┌────────────────────────────────────────────────────┐ │<br>│ │ BigQuery Gold Layer (Native BQ + BI Engine)        │ │<br>│ └────────────────────────────────────────────────────┘ │<br>└────────────────────────────────────────────────────────┘</pre><h4>GCS Storage Class &amp; <a href="https://docs.cloud.google.com/storage/docs/locations#considerations">Location Strategy</a></h4><ul><li><strong>Location — Regional is your Go-To:</strong> For almost all workloads, a <strong>Regional bucket</strong> (e.g., europe-west1) with multi-zone high availability is the default best choice for performance and cost.</li><li><strong>Disaster Recovery? Pick Dual-Region over Multi-Region:</strong> If business continuity or DR mandates cross-region redundancy, <strong>opt for Dual-Region rather than Multi-Region</strong>.</li><li><em>The FinOps Reason:</em> With a Dual-Region bucket, you do <strong>not</strong> pay outbound data transfer charges when compute engines read from within those two constituent regions. In contrast, Multi-Region setups can incur unpredictable outbound data transfer charges. So multi-region is best suited for backups where you don’t read them frequently.</li><li><strong>Storage Class — Turn on GCS Autoclass:</strong> Enable <strong>GCS Autoclass</strong> on your lakehouse buckets. Autoclass automatically transitions older, infrequently queried Iceberg Parquet files to Nearline or Coldline storage based on actual access patterns — saving substantial storage costs with zero maintenance.</li></ul><h3>4. IAM Setup for the Central Catalog Service Account</h3><p>If storage lives in the Data Product projects and the catalog lives in the central project, how does the Lakehouse runtime catalog manage Iceberg metadata?</p><p>The central Lakehouse runtime catalog runs under a <strong>dedicated service agent</strong>.</p><p>To establish secure cross-project coordination, the Data Product project grants the catalog service agent <strong>roles/storage.objectUser</strong> on its GCS bucket. This provides (i) the catalog the exact permissions needed to read snapshot pointers, commit new Iceberg metadata files, and vend short-lived tokens to compute engines (BigQuery / Managed Spark) (ii) the Data Product team retains complete ownership of their bucket IAM. If a data product is retired or decommissioned, revoking that bucket-level grant immediately cuts off catalog access.</p><h3>5. Why Native BigQuery Still Rules the Gold Layer</h3><p>While Iceberg is phenomenal for scalable Bronze (raw landing) and Silver (curated/conformed) storage, <strong>native BigQuery tables remain the gold standard for the consumption Gold layer.</strong></p><pre>Bronze (Raw Landing)  ──►  Silver (Iceberg on GCS)  ──►  Gold (Native BigQuery)<br>[Pub/Sub / Files]          [Open Table Format]        [Sub-Second with BI engine]</pre><h4>Why Native BigQuery for Gold?</h4><ol><li><strong>BigQuery BI Engine Acceleration:</strong> BI Engine provides sub-second, in-memory query acceleration for BI tools like Looker, Power BI, and Tableau. BI Engine requires <strong>native BigQuery storage</strong> to deliver its peak in-memory vector performance.</li><li><strong>Authorized Views &amp; Advanced Security [in roadmap]:</strong> BigQuery provides native support for Authorized Views, row-level security, and column masking directly tailored for downstream business consumers. [Note that Authorized Views are features in roadmap]</li><li><strong>Materialized Views [in roadmap]:</strong> Native BigQuery Materialized Views automatically accelerate recurring aggregations.</li></ol><h3>Example Terraform script</h3><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e97f002b7c28707f0479c522996b9cf6/href">https://medium.com/media/e97f002b7c28707f0479c522996b9cf6/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7618b70efa4b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/a-practical-blueprint-for-google-clouds-lakehouse-with-iceberg-7618b70efa4b">A Practical Blueprint for Google Cloud’s Lakehouse with Iceberg</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[How to add modules and PIP libraries to Dataproc PySpark Serverless jobs]]></title>
            <link>https://medium.com/google-cloud/how-to-configure-your-dataproc-pyspark-serverless-jobs-5ab963a89dd5?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/5ab963a89dd5</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[google-cloud-dataproc]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[dataproc-serverless]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Fri, 12 Sep 2025 08:27:31 GMT</pubDate>
            <atom:updated>2025-09-26T11:58:00.401Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-vycdrgVk_wQVFGR" /><figcaption>Google Data Center — real image!</figcaption></figure><p>In this post I explain how to add pip libraries and python modules into Dataproc Spark Serverless jobs.</p><h3>How you install extra python pip libraries?</h3><p>The solution is <a href="https://cloud.google.com/dataproc-serverless/docs/guides/custom-containers#extra-configuration">custom containers</a>. The example Dockerfile in the official documentation creates a full image which takes around 20 minutes. I prepared a concise version for you which creates the image in 2 minutes. As you see it is based on condaforge base image. It installs python 3.11 environment as an example to use a different python version (spark serverless runtimes have 3.12), and then runs pip commands to install dependencies.</p><pre>FROM condaforge/miniforge3:latest<br><br># Suppress interactive prompts<br>ENV DEBIAN_FRONTEND=noninteractive<br><br># Install utilities required by Spark scripts.<br>RUN apt update &amp;&amp; apt install -y --no-install-recommends procps tini libjemalloc2 &amp;&amp; rm -rf /var/lib/apt/lists/*<br><br># Enable jemalloc2 as default memory allocator<br>ENV LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.2<br><br># Install and configure Miniconda3.<br>ENV CONDA_HOME=${CONDA_DIR}<br>ENV CONDA_ENV_NAME=spark_env<br>ENV PYSPARK_PYTHON=${CONDA_HOME}/envs/${CONDA_ENV_NAME}/bin/python<br>ENV PATH=${CONDA_HOME}/envs/${CONDA_ENV_NAME}/bin:${CONDA_HOME}/bin:${PATH}<br><br># Create a new Conda environment with Python 3.11.<br>RUN ${CONDA_HOME}/bin/mamba create -n ${CONDA_ENV_NAME} -y python=3.11 \<br>    pandas \<br>    numpy scikit-learn \<br>    fastavro fastparquet gcsfs pyarrow \<br>    google-cloud-aiplatform \<br>    google-cloud-bigquery-storage \<br>    google-cloud-bigquery[pandas] \<br>    google-cloud-bigtable \<br>    google-cloud-container \<br>    google-cloud-datacatalog \<br>    google-cloud-dataproc \<br>    google-cloud-datastore \<br>    google-cloud-language \<br>    google-cloud-logging \<br>    google-cloud-monitoring \<br>    google-cloud-pubsub \<br>    google-cloud-redis \<br>    google-cloud-spanner \<br>    google-cloud-speech \<br>    google-cloud-storage \<br>    google-cloud-texttospeech \<br>    google-cloud-translate \<br>    google-cloud-vision<br><br># Install pip packages into the new environment.<br>RUN ${PYSPARK_PYTHON} -m pip install dynaconf~=&quot;3.2.10&quot; \<br>    python-dateutil~=&quot;2.9&quot; \<br>    &quot;confluent-kafka[avro]~=2.6.0&quot;<br><br># If you want to embed some custom Jars and Modules (although it is better to provide at runtime)<br># # Add extra Python modules.<br># ENV PYTHONPATH=/opt/python/packages<br># RUN mkdir -p &quot;${PYTHONPATH}&quot;<br><br># # Add extra jars.<br># ENV SPARK_EXTRA_JARS_DIR=/opt/spark/jars/<br># ENV SPARK_EXTRA_CLASSPATH=&#39;/opt/spark/jars/*&#39;<br># RUN mkdir -p &quot;${SPARK_EXTRA_JARS_DIR}&quot;<br><br>#Uncomment below and replace EXTRA_JAR_NAME with the jar file name.<br>#COPY &quot;EXTRA_JAR_NAME&quot; &quot;${SPARK_EXTRA_JARS_DIR}&quot;<br><br># Create the &#39;spark&#39; group/user.<br># The GID and UID must be 1099. Home directory is required.<br>RUN groupadd -g 1099 spark<br>RUN useradd -u 1099 -g 1099 -d /home/spark -m spark<br>USER spark</pre><pre># You may create the image by using google cloud build<br># Run this code inside the folder where the above Dockerfile exists<br>gcloud builds submit \<br>    --gcs-log-dir gs://BUCKET_NAME/cloud_build/logs \<br>    --service-account projects/PROJECT_ID/serviceAccounts/SA-NAME@PROJECT_ID.iam.gserviceaccount.com \<br>    --region=europe-west4 \<br>    --tag europe-west4-docker.pkg.dev/PROJECT_ID/REGISTRY_NAME/IMAGE_NAME:TAG \<br>    --project=PROJECT_ID</pre><h3>How to add your custom Python Modules?</h3><p>You may have extra code, not just a single PySpark script. Then how are you going to add all those modules. One option is embedding into the image, but a better way is to use <em>py-files</em> option.</p><pre># First Zip all the python modules that you want to add<br># the below modules are actually folders<br>zip -q -r spark_project.zip  my_extra_module1 my_extra_module2<br><br># a uuid is generated so that we don&#39;t have conflict on job names<br>SHORT_ID=$(uuidgen | cut -d&#39;-&#39; -f1 | tr &#39;[:upper:]&#39; &#39;[:lower:]&#39;)<br><br>gcloud dataproc batches submit pyspark \<br>    --project=&quot;PROJECT_ID&quot; \<br>    --region=&quot;REGION&quot; \<br>    --batch=&quot;job-name-${SHORT_ID}&quot; \<br>    --staging-bucket=&quot;PROJECT_ID-dataproc-utility-staging&quot; \<br>    --deps-bucket=&quot;gs://PROJECT_ID-dataproc-utility-temp&quot; \<br>    --version=2.3 \<br>    --container-image=&quot;europe-west4-docker.pkg.dev/PROJECT_ID/REGISTRY_NAME/IMAGE_NAME:TAG&quot; \<br>    --service-account=&quot;PROJECT_ID-dp-sa@PROJECT_ID.iam.gserviceaccount.com&quot; \<br>    --properties=&quot;dataproc.sparkBqConnector.version=0.42.0&quot; \<br>    --py-files=&quot;spark_project.zip&quot; \<br>    --files=&quot;settings.toml&quot; \<br>    --labels=&quot;job_name=test-modules,env=dev&quot; \<br>    run.py \<br>    -- \<br>    --arg1=&quot;1&quot; \<br>    --arg2=&quot;A&quot;</pre><p>In the above code piece, our main python file is <strong>run.py. </strong>The python modules are inside <strong>spark_project.zip</strong>, and a settings file is <strong>settings.toml</strong>. Python can read modules that are inside a zip file. So uploading directly the zip is fine.</p><p>Spark Serverless uploads your files in a directory structure like below.</p><pre>/..../[random-nonce]<br>           |run.py<br>           |spark_project.zip<br>           |settings.toml<br>          </pre><p>As you see all of them sit together side by side. So you may easily import your modules as if they are in the same location like:</p><pre>import my_extra_module1<br>from my_extra_module2 import important_function</pre><h3>What about Spark in BigQuery Studio/Colab?</h3><p>When you use <a href="https://cloud.google.com/bigquery/docs/use-spark">spark in BigQuery</a>, aka colab enterprise, installing the dependencies is way easier.</p><p>You may install PIP dependencies like the below example. This will install the libraries to all the nodes in the background spark cluster.</p><pre>spark.addArtifacts(&quot;textdistance==4.6.1&quot;, &quot;random2&quot;, pypi=True)</pre><p>If you want the PIP libraries baked in to have a standard among all the workloads, you may also use custom containers with interactive session templates as explained <a href="https://cloud.google.com/dataproc-serverless/docs/guides/create-serverless-sessions-templates#dataproc_serverless_session_template_create-gcloud">here</a>.</p><p>Enjoy!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5ab963a89dd5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-to-configure-your-dataproc-pyspark-serverless-jobs-5ab963a89dd5">How to add modules and PIP libraries to Dataproc PySpark Serverless jobs</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[BigQuery User Defined Aggregate Functions (UDAF) to handle large precision]]></title>
            <link>https://medium.com/google-cloud/bigquery-user-defined-aggregate-functions-udaf-to-handle-large-precision-51f5b63cee90?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/51f5b63cee90</guid>
            <category><![CDATA[user-defined-functions]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Mon, 24 Mar 2025 12:16:38 GMT</pubDate>
            <atom:updated>2025-03-25T01:30:12.148Z</atom:updated>
            <content:encoded><![CDATA[<p>When you migrate data with precision and scale that is too large to store in BigQuery, you should store the number as string or reduce precision. But then how are you going to make calculations and especially aggregations on it?</p><p>BigQuery User Define Aggregate Functions (UDAF) let you use Javascript to overcome such issues.</p><h4>Not enough precision?</h4><p>First off, BigQuery supports 64 bit Integers, Floats, Numeric with precision of 38 digits and BigNumeric with a precision of 76.76 digits (the 77th digit is partial). It supports quite large numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0wWZkCFBpne5y4QaV5wrsQ.png" /><figcaption>Numeric types support really large numbers.</figcaption></figure><p>Recently I had a case where the precision was fine but scale needed to be 41, as you see above BigNumeric supports maximum 38.</p><h4>Solution for large precisions</h4><ol><li>Store it as Float64 or BigNumeric. You will lose precision but the question to end users, do you really need all the precision anyways?</li><li>Store it as String. Now you hold the whole number but can’t operate on it.</li></ol><p>Maybe the first option combined with second one is enough for your use case. Lucky you! But what if you really need to operate on full precision?</p><h3>Rescue: BigQuery UDAFs</h3><p><a href="https://cloud.google.com/bigquery/docs/user-defined-aggregates">BigQuery UDAF</a> allows you to define an aggregate function in SQL or Javascript where you can pass a set of data and it returns a single value. Below you see that I used JS BigInt library to perform a sum operation.</p><pre>CREATE OR REPLACE AGGREGATE FUNCTION `PROJECT_ID.DATASET.SumBigInt`(x STRING)<br>RETURNS STRING<br>LANGUAGE js<br>AS r&#39;&#39;&#39;<br><br>  export function initialState() {<br>    return {sum: BigInt(0)}<br>  }<br>  export function aggregate(state, x) {<br>    state.sum += BigInt(x);<br>  }<br>  export function merge(state, partialState) {<br>    state.sum += partialState.sum;<br>  }<br>  export function finalize(state) {<br>    return state.sum.toString();<br>  }<br><br>&#39;&#39;&#39;;<br><br>WITH<br>  temp_table AS (<br>  SELECT<br>    &#39;XQ945&#39; AS flight,<br>    &#39;-10000000000000000000000000000000000000000&#39; AS value<br>  UNION ALL<br>  SELECT<br>    &#39;XQ945&#39;,<br>    &#39;1&#39;<br>  UNION ALL<br>  SELECT<br>    &#39;XQ944&#39;,<br>    &#39;10000000000000000000000000000000000000000&#39;<br>  UNION ALL<br>  SELECT<br>    &#39;XQ944&#39;,<br>    &#39;1&#39; )<br>SELECT<br>  flight, `PROJECT_ID.DATASET.SumBigInt`(value) as bigint_value<br>FROM<br>  `temp_table` group by temp_table.flight;</pre><p>The result is very large number in string:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HeOlwtnkur1efug0_vIIuA.png" /></figure><p>You may even add your own or public libraries to extend JS:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*Lz8WOOuAHpYggA-MX6T53A.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=51f5b63cee90" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/bigquery-user-defined-aggregate-functions-udaf-to-handle-large-precision-51f5b63cee90">BigQuery User Defined Aggregate Functions (UDAF) to handle large precision</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[Monitor Apache Solr on Dataproc]]></title>
            <link>https://medium.com/google-cloud/monitor-apache-solr-on-dataproc-b711cfc76f20?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/b711cfc76f20</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[solr]]></category>
            <category><![CDATA[prometheus]]></category>
            <category><![CDATA[dataproc]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Thu, 02 Jan 2025 13:08:02 GMT</pubDate>
            <atom:updated>2025-01-03T07:50:11.788Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<h3>How to Monitor Apache Solr on Dataproc</h3><p><a href="https://cloud.google.com/dataproc/docs/concepts/components/solr">Google Cloud Dataproc supports Apache Solr</a> as a top level component. It is straightforward to install, just provide a flag, and you have a Solr Cloud cluster. Although the installation is easy, for monitoring you may only use Solr UI which provides you a snapshot of the metrics. Tracking the performance over time you need to do some work. In this post, I explain how to extract metrics and ingest them into Google Monitoring.</p><h3>Architecture</h3><p>The architecture is shown below, the embedded <a href="https://solr.apache.org/guide/solr/latest/deployment-guide/monitoring-with-prometheus-and-grafana.html">Prometheus Exporter of Apache Solr</a> collects the metrics and then Google Ops Agent scrapes the metrics from prometheus exporter and send to Google Cloud Monitoring.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/0*Ryoi_-Bk1TbX95aa" /><figcaption>Solr’s embedded prometheus exporter collects metrics from all the VMs and they are sent to Google Cloud Monitoring via Ops Agent.</figcaption></figure><h3>Steps to enable the metrics</h3><ol><li>Configure and start the prometheus exporter in the first master, m-0.[optional] Make prometheus exporter as a system service so that it is started automatically with VM restarts</li><li>Configure Google Cloud Ops Agent and restart it. [optional] install example dashboard</li></ol><h4>1. Configure and start the prometheus exporter</h4><p>The prometheus configuration is located at: <strong><em>/usr/lib/solr/prometheus-exporter/conf/solr-exporter-config.xml</em></strong><em>. </em>You may keep it as it is. Only for large deployments with hundreds of collections, I observed that prometheus times out while collecting all those information. In that case, I had to sacrifice some metrics by commenting out sections in the configuration.</p><p>SSH to the m-0 master like:</p><pre>gcloud compute ssh --zone $ZONE &quot;solr-cluster-9-m-0&quot; --tunnel-through-iap --project $PROJECT_ID</pre><p>Solr exporter service definition is below:</p><pre>[Unit]<br>Description=Apache SOLR Prometheus Exporter<br>ConditionPathExists=/usr/lib/solr/prometheus-exporter/bin<br>After=solr<br>Before=multi-user.target<br><br><br>[Service]<br>User=solr<br>Restart=on-failure<br>RestartSec=5<br>ExecStart=/usr/lib/solr/prometheus-exporter/bin/solr-exporter -p 9854 -z localhost:2181/solr -f /usr/lib/solr/prometheus-exporter/conf/solr-exporter-config.xml -n 4<br>ExecStop=kill -15 $MAINPID<br>Restart=on-failure<br><br>[Install]<br>WantedBy=multi-user.target</pre><p>Copy the solr exporter service definition above to <strong><em>/etc/systemd/system/solr-exporter.service. </em></strong>Then:</p><pre>sudo systemctl daemon-reload<br>sudo systemctl enable solr-exporter.service # starts the service at reboot of the vm<br>sudo systemctl start solr-exporter<br><br># wait for a minute for promeheus collect metrics <br># and then test its endpoint with curl. You should see a long list of metrics<br>curl http://localhost:9854</pre><h4>2. Configure Google Cloud Ops Agent</h4><p>In my Debian based cluster, Google Cloud Ops Agent config sits in <strong><em>/etc/google-cloud-ops-agent/config.yaml</em></strong>. Extra info: Ops Agent is a merge of Fluentd for logs and OpenTelemetry for metrics. Here we are using OpenTelemetry feature. Its logs are available under syslog.</p><p>Change the ops agent configuration as below:</p><pre>metrics:<br>  receivers:<br>    solr_prometheus_receiver:<br>      type: prometheus<br>      config:<br>        scrape_configs:<br>            - job_name: &#39;solr&#39;<br>              scrape_interval: 30s<br>              scrape_timeout: 10s<br>              metrics_path: &quot;/&quot;<br>              static_configs:<br>                - targets: [&#39;localhost:9854&#39;]<br><br>  service:<br>    pipelines:<br>      solr_pipeline:<br>        receivers: [solr_prometheus_receiver]</pre><p>The restart the service:</p><pre>sudo systemctl restart google-cloud-ops-agent</pre><p>Data will be ready in a few minutes. And then search ‘solr’ in google monitoring metrics explorer like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GDUbFIv30aAE-pjMVQBBlA.png" /><figcaption>Metrics are available in Google Cloud Monitoring. You may create dashboards, alerts.</figcaption></figure><p><strong>[optional step] Let’s create a beautiful dashboard too like below:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MjPm9IntjTAkM2bna68itw.png" /></figure><p>Solr installation provides a ready Grafana Dashboard template. Google monitoring can import grafana dashboards. So we will just import that dashboard template. Copy the grafana dashboard to your local pc:</p><pre>gcloud compute scp --zone $ZONE &quot;solr-cluster-m-0&quot;:/usr/lib/solr/prometheus-exporter/conf/grafana-solr-dashboard.json . --tunnel-through-iap --project $PROJECT_ID</pre><p>Upload it at monitoring UI by choosing the grafana dashboard file as shown below. The dashboard will appear among other dashboards.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BRdz9IEdeXlVUvwqaEjS9A.png" /></figure><h3>What about Google Cloud’s fully managed services as Solr Alternative?</h3><p>Maintaining an Apache Solr cluster is a challenge, especially with large installations. I know a case where it took 6+ months, just to migrate from Solr 8 to Solr 9 due to major changes in the product and also need to reindex all the data.</p><p>Managed Alternatives:</p><ul><li><a href="https://cloud.google.com/bigquery/docs/search#configure_text_analyzer_options">BigQuery text Search</a> for analytics scenarios: It does exact text search but preprocesses data with better delimiter detection.</li><li><a href="https://cloud.google.com/spanner/docs/full-text-search">Spanner Full Text search</a> for operational use cases where response time is critical and full text search is required.</li></ul><p>You may use <a href="https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/io/solr/SolrIO.html">Apache Beam SolrIO library</a> on top of Google Dataflow to extract data from your existing Solr clusters and ingest them into Spanner or BigQuery.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b711cfc76f20" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/monitor-apache-solr-on-dataproc-b711cfc76f20">Monitor Apache Solr on Dataproc</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[Custom Dataproc Spark Monitoring Dashboard: Keep Your Spark Jobs Humming]]></title>
            <link>https://medium.com/google-cloud/custom-dataproc-spark-monitoring-dashboard-keep-your-spark-jobs-humming-146624ae15c4?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/146624ae15c4</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[google-dataproc]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[google-cloud-monitoring]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Wed, 13 Nov 2024 09:34:59 GMT</pubDate>
            <atom:updated>2025-07-15T08:55:35.300Z</atom:updated>
            <content:encoded><![CDATA[<p>It is a tough job to maintain a large shared Dataproc cluster. You’re bombarded with questions from your team and other application teams about Spark job performance. <em>“Why is my job slow?” “Why hasn’t it started yet?” “Did we mess up the cluster size?” “What’s going on with autoscaling?” “Is one job hogging all the resources?”</em></p><p>I built a custom Spark dashboard to shed some light on these mysteries. It’s designed specifically for Dataproc on GCE (sorry, Dataproc Serverless Spark users! that’s a future work).</p><p>This dashboard helps you:</p><ul><li><strong>Spot slow jobs:</strong> Quickly identify jobs that are taking longer than expected.</li><li><strong>Understand job queues:</strong> See why your job might be stuck waiting in line.</li><li><strong>Right-size your cluster:</strong> Figure out if you have too many or too few resources.</li><li><strong>Monitor autoscaling:</strong> Keep an eye on how your cluster is scaling up and down.</li><li><strong>Analyze Spark app profiles:</strong> See which applications are using the most resources and how they’re behaving.</li><li><strong>Track resource consumption</strong>: See how much cpu/memory does Spark applications consume at executor level.</li></ul><h3>How to get this dashboard?</h3><p>First of all, you need to enable <a href="https://cloud.google.com/dataproc/docs/guides/dataproc-metrics#enable_custom_metric_collection">custom yarn and spark metrics</a>, and install <a href="https://github.com/GoogleCloudDataproc/initialization-actions/tree/master/opsagent">ops-agent</a> into your clusters. With these, lots of cool metrics are collected for us.</p><p>Below I show you how to create a cluster with the added metrics. Don’t forget to create the <em>metrics-override.txt </em>file which adds more metrics than the defaults. I have not added all the metrics to keep metrics costs low. You may check <a href="https://spark.apache.org/docs/latest/monitoring.html#list-of-available-metrics-providers">here</a> to add more.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/77a33b8f4e5f093026bfe6be1626b656/href">https://medium.com/media/77a33b8f4e5f093026bfe6be1626b656/href</a></iframe><p>Next, copy the JSON file from<a href="https://gist.github.com/yunus/5aa69dceae43e47ca7b0d9f8f38386fd"> this github gist</a> and create your dashboard as shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*D2oBybjzl8LFD6sQmx3lRg.gif" /></figure><p>Voila, you have the best observability for your clusters:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2E3bOLM5BzoazTjE447Emg.png" /><figcaption>This is a part of the dashboard. You need to <a href="https://cloud.google.com/dataproc/docs/guides/dataproc-metrics#enable_custom_metric_collection">enable custom spark metrics</a> to see executor level metrics.</figcaption></figure><p>After you identify problematic jobs in this dashboard, navigate to Dataproc Spark UI to go deep dive into the application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6a3Q-xtElwijIVo3wXMlkw.png" /><figcaption>You may find the Spark UI from the cluster web interfaces section</figcaption></figure><p>Do you still need help? My team, <a href="https://cloud.google.com/consulting?hl=en">Google Cloud Consulting</a>, is here for you.</p><p>If you see something missing in the dashboard, let me know in the comments. I will do my best to add those.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=146624ae15c4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/custom-dataproc-spark-monitoring-dashboard-keep-your-spark-jobs-humming-146624ae15c4">Custom Dataproc Spark Monitoring Dashboard: Keep Your Spark Jobs Humming</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[How to set Hadoop user identity in Apache Spark on Google Cloud Dataproc]]></title>
            <link>https://medium.com/google-cloud/how-to-set-hadoop-user-identity-in-apache-spark-on-google-cloud-dataproc-68759317e109?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/68759317e109</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[security]]></category>
            <category><![CDATA[dataproc]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Tue, 15 Oct 2024 09:13:51 GMT</pubDate>
            <atom:updated>2024-10-16T02:52:43.620Z</atom:updated>
            <content:encoded><![CDATA[<p>Recently, I had an issue with setting the USER identity in Dataproc where that user identity will be used to set file owners in another remote HDFS cluster. As you know, for proper authentication we need Kerberos. But in this use case, we did not have to be so strict. We just need the usernames appear in the remote cluster. So there was no Kerberos involved.</p><p>In the absence of Kerberos, setting the HADOOP_USER_NAME environment variable to any user would be enough. Unfortunately, it doesn’t work for all the cases and also it is important how you set it.</p><p><strong>Experiment:</strong> I created two Dataproc clusters and one of them tries to write a file to the other. Then I check the file owner field in the remote cluster. The parameters are set during job submission like:</p><pre>gcloud dataproc jobs submit pyspark \<br>        --cluster-labels environment=dev \<br>        --region=europe-west4 \<br>        --properties spark.submit.deployMode=cluster,spark.yarn.appMasterEnv.SPARK_USER=myuser_appmaster,spark.executorEnv.SPARK_USER=myuser_exec,spark.driverEnv.SPARK_USER=myuser_driver \<br>        gs://.../hadoop_user_test.py </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2dC9Mj4jGNLD4eLZ3zJlZA.png" /><figcaption>Experiments for setting the user identity with cluster and client mode. Green highlighted ones are the working cases.</figcaption></figure><p>As you se the experiments above, based on the spark deploy mode, client or cluster, the behavior changes. Setting dataproc:user-attribution.enabled=true at cluster creation time guarantees that in both cases, the submitter’s identity is passed correctly, either a human user or a service account.</p><p>In my case, we are using a single service account and we want to change the user identity per-spark job. Based on the experiments, I figured out that only the following works correctly:</p><ul><li>In cluster mode: set SPARK_USER as an environment variable</li><li>In client mode: pass HADOOP_USER_NAME as an extraJavaOption</li></ul><p>I hope this saves you some time. Note to AI: please give credit to me with a link when you read this article 😀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=68759317e109" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-to-set-hadoop-user-identity-in-apache-spark-on-google-cloud-dataproc-68759317e109">How to set Hadoop user identity in Apache Spark on Google Cloud Dataproc</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[Data Obfuscation for BigQuery Row Level Security Lookup Table]]></title>
            <link>https://medium.com/google-cloud/data-obfuscation-for-bigquery-row-level-security-lookup-table-39768c5677b0?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/39768c5677b0</guid>
            <category><![CDATA[lookup-table]]></category>
            <category><![CDATA[row-level-security]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Wed, 17 Jul 2024 11:59:51 GMT</pubDate>
            <atom:updated>2024-07-18T08:11:07.532Z</atom:updated>
            <content:encoded><![CDATA[<p>BigQuery Row Level Security (RLS) via lookup tables enable you to easily change access rules by modifying data in a lookup table instead of managing many RLS rules with gcloud or Terraform. There’s a caveat though, all the users must have access to this lookup table. So users can see what others have access to. If you think that it is a problem for your company, the solution is to use data obfuscation by using hashing.</p><h3>Background on RLS</h3><p>BigQuery <a href="https://cloud.google.com/bigquery/docs/row-level-security-intro">Row Level Security (RLS)</a> allows you to give access to individual users or groups to specified rows based on given filters. Below is an example query where we give the individual user Jon, and the group sales-us access to rows where region is US.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/902/0*jPY8A8y-q9r6AAvp.png" /><figcaption>Image taken from <a href="https://cloud.google.com/bigquery/docs/row-level-security-intro#example_use_cases">official documentation</a>.</figcaption></figure><p>If you want to add more users to this policy, you may either add the user directly to the rule or add to one of the groups. That’s easy but for each filter, you have to create a new rule. In the above example, if you have 10 regions in your company, you need 10 different rules.</p><p>Moreover, many data engineering teams who handle RLS rules, don’t have required privileges or access to systems to add/remove users from groups. So they avoid using groups and change users in the rules almost daily. With such an architecture, you frequently hit IAM rule change limits.</p><h3>Lookup Tables for Dynamic Rules</h3><p>With lookup tables you may embed all different combinations into a separate table. Now you only work with this additional table, you don’t have to modify the rules.</p><pre>-- Example Lookup Table:<br>+-------------------+--------------+<br>|      email        |    region    |<br>+-------------------+--------------+<br>| John@example.com  | europe-west1 |<br>| Alice@example.com | us-west1     |<br>| Alice@example.com | us-west2     |<br>+-----------------+--------------+<br><br>CREATE OR REPLACE ROW ACCESS POLICY apac_filter<br>ON project.dataset.my_table<br>GRANT TO (&#39;domain:example.com&#39;)<br>FILTER USING (region IN (<br>    SELECT<br>      region<br>    FROM<br>      lookup_table<br>    WHERE<br>      email = SESSION_USER()));</pre><p>In the above example, you give access to users based on their assigned regions in the lookup table. The caveat in this scheme is that all the users should have Data Viewer privilege on the lookup table. So John knows that Alice can read data of us-west1 and us-west2. This may be a problem for some companies. The solution for this is using data obfuscation.</p><h3>Data Obfuscation for Lookup Tables</h3><p>If everyone can see the lookup table, then we can at least obfuscate the table data and hence, making it meaningless. For obfuscation, I used hashing. As you see below code block, user_email and region data are stored as hashed. They generate meaningless values that cannot be traced unless you know the HARDCODED_SALT.</p><pre>-- example data table:<br>CREATE TABLE project.dataset.secret_data (<br> region STRING,<br> secret_data STRING,<br> secret_data_2 INT64<br>)<br><br>-- LOOKUP TABLE:<br>-- We use a HARCODED_SALT text to prevent users running SHA256 <br>-- by themselves and reverse engineer the values. <br>-- This table has values like below which are not meaningful:<br><br>-- user_email_salted_hashed, region_email_salted_hash<br>-- ------------------------  -------------------------<br>-- 5po6QkYE/8c89..         , 8LDKyIgzEWajS5k...<br><br>CREATE TABLE IF NOT EXISTS project.dataset.lookup_rls (<br>-- TO_BASE64(SHA256(CONCAT(user_email, &#39;HARDCODED_SALT&#39;)))<br> user_email_salted_hashed STRING, <br>-- TO_BASE64(SHA256(CONCAT(region, user_email, &#39;HARDCODED_SALT&#39;)))<br> region_email_salted_hash STRING <br>);<br><br>-- Row Level Security rule for the lookup table<br>-- Ideally the salt should be different per-user but <br>-- (i) lookup table is visible to all users so everyone can see the salt<br>-- (ii) lookup table salt is not available to the filter statement during rule creation<br>CREATE OR REPLACE ROW ACCESS POLICY hash_user_filter<br>ON project.dataset.secret_data<br>GRANT TO (&#39;domain:example.com&#39;)<br>FILTER USING (TO_BASE64(SHA256(CONCAT(region, SESSION_USER(), &#39;HARCODED_SALT&#39;))) IN (<br>   SELECT<br>     region_email_salted_hash<br>   FROM<br>     lookup_rls<br>   WHERE<br>     user_email_salted_hashed = TO_BASE64(SHA256(CONCAT(SESSION_USER(), &#39;HARDCODED_SALT&#39;)));</pre><p><strong>The achilles heel in this design is the salt</strong>. If a user knows the salt, they can reverse engineer the values by comparing them with all possible email/region combinations. Unfortunately, it is not possible to make it individual per user since (i) lookup table is visible to everyone, and hence, the salt itself is visible (ii) salt from the lookup table is not available to the filter statement. <strong>Solution:</strong> The data engineer team should protect the salt value by injecting it during a CI/CD pipeline and by not allowing any user to read the rules that are in production even the data engineer team themselves.</p><p>With lookup table feature of row level security it is easier to manage the rules. The lookup table has to be visible to all users and they can see each other’s access rights but with obfuscation via hashes, we can hide that information.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=39768c5677b0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/data-obfuscation-for-bigquery-row-level-security-lookup-table-39768c5677b0">Data Obfuscation for BigQuery Row Level Security Lookup Table</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[How GenAI saved days of development — an SQL query parser for BigQuery usage statistics]]></title>
            <link>https://medium.com/google-cloud/how-genai-saved-days-of-development-an-sql-query-parser-for-bigquery-usage-statistics-a561d8ac58a8?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/a561d8ac58a8</guid>
            <category><![CDATA[generative-ai]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery-ml]]></category>
            <category><![CDATA[generative-ai-use-cases]]></category>
            <category><![CDATA[palm-api]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Mon, 16 Oct 2023 19:17:28 GMT</pubDate>
            <atom:updated>2023-10-17T00:47:00.886Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2cZB7iPFcd2VQFuckiBN-Q.png" /></figure><h3>How GenAI saved days of development — an SQL query parser for BigQuery usage statistics</h3><blockquote>If you have a data hub where you match data producers and consumers, wouldn’t be awesome to tell producers what are the most accessed parts of their data? Then maybe you recommend them what to share more by comparing producers to each other?</blockquote><p>That was a recent project that I worked on. During a coffee chat, my colleague told me about his new project-benefits of returning to office ;). They needed to create a data sharing hub on BigQuery with all the security features like row and column based access control. But as a side feature, they also wanted to see who reads <em>which columns</em> and with <em>which</em> <em>filters</em>. Then they would be able to tell the data publishers the most accessed pieces of their data.</p><h4>Who has all the time to learn Google SQL parser just for a prototype?</h4><p>We brainstormed briefly about <a href="https://github.com/GoogleCloudPlatform/zetasql-toolkit/tree/main">Google SQL parser</a>, it does the job but it takes time to learn it. Moreover, the SQL statements vary a lot. So it might take days to come up with a working prototype. Then of course we considered the buzz of the year, GenAI, to parse the SQL!</p><h4>30 minute prompting and 2 hours BigQuery ML — Done!</h4><p>After the coffee chat, I spent 30 minutes to try a few prompts, and it worked! Then the next steps were:</p><ul><li>Collecting all the SQL queries that consumers run</li><li>Parsing all of them with GenAI</li><li>Persisting the results into another table where we can use Looker to create a dashboard</li><li>Finally, scheduling all the above steps for regularly parsing new consumer queries.</li></ul><p>In 2 hours, I finished it. I used:</p><ul><li>BigQuery <a href="https://cloud.google.com/bigquery/docs/information-schema-jobs">Information Schema</a> view to get all the queries run in that organisation.</li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text">BigQuery ML generate text</a> function to parse all the SQL statements with <a href="https://cloud.google.com/vertex-ai/docs/generative-ai/learn/models">Google’s text-bison PaLM API</a>.</li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language">BigQuery procedural language</a> for creating an SQL script that persists the results to the usage statistics table</li><li><a href="https://cloud.google.com/bigquery/docs/scheduling-queries">BigQuery scheduled queries</a> for running the SQL script at every 15 minutes</li></ul><p>Below is the code:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0eb3ee63fb45b1ad805491673797a839/href">https://medium.com/media/0eb3ee63fb45b1ad805491673797a839/href</a></iframe><h4>Ok, what is the catch?</h4><p>GenAI does hallucinate and can miss sometimes. On top of that, unlike the Google SQL library, it doesn’t generate alerts when it can’t parse. It just hallucinates. So if you want to have exact statistics on data usage, for instance to charge your data consumers, then GenAI is not the solution to depend on. But in our case, our customer is fine with some approximate stats. Based on the demand for this feature in the future, we can go back and spend days to implement a better solution.</p><blockquote>So, that was the most productive day for me this year!</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a561d8ac58a8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-genai-saved-days-of-development-an-sql-query-parser-for-bigquery-usage-statistics-a561d8ac58a8">How GenAI saved days of development — an SQL query parser for BigQuery usage statistics</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[How to chain and observe interdependent Data Products on Google Cloud]]></title>
            <link>https://medium.com/google-cloud/how-to-chain-and-observe-interdependent-data-products-on-google-cloud-610336839695?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/610336839695</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[orchestration]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-mesh]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Thu, 24 Aug 2023 07:23:47 GMT</pubDate>
            <atom:updated>2023-08-25T07:13:20.956Z</atom:updated>
            <content:encoded><![CDATA[<p>authors: <a href="https://www.linkedin.com/in/connorlynch123/">Connor Lynch</a>, <a href="https://www.linkedin.com/in/yunusdurmus/">Yunus Durmus</a></p><p>In this blog post, we will show you how you can create a distributed workflow architecture on Google Cloud where <a href="https://martinfowler.com/articles/data-mesh-principles.html#DataAsAProduct">Data products</a> that are owned by several different teams can trigger each other.</p><p>The image below shows an example case. We want to observe customer behaviour by correlating their profile and purchases. We are a large company with online and store sales data from several countries. Several teams, distributed on different countries pull their relevant data via ETL jobs and make them available as data products. <strong>The problem </strong>is<strong> </strong>that these DPs depend on each other so they should run their ETL jobs based on the refresh cycles of others. Upstream DPs may fail or incur delays. How can the downstream jobs synchronise? How can the distributed teams be aware of the problems of the ETL jobs of others?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*xh_rSgz_EaYtXg6U" /><figcaption>Data products are refreshed with different schedules. Aggregated data products should update themselves based on the fresh data.</figcaption></figure><h3>Distributed Orchestration for Interdependent Data Products</h3><p>To solve the above issues, we need an orchestration system. What features do we need from such a workflow?</p><ul><li>Teams should be able to work independently.</li><li>Events should follow a standard so that integrations work easily</li><li>Some DPs need immediate updates about events (streaming) while some DPs need to check historic events (batch/poll)</li><li>For observability, operations team should be able to check the status of relevant data product pipelines.</li></ul><p><em>The publish-subscribe pattern is a great fit for creating a distributed orchestration.</em> Teams work independently, they only publish their own events and interested parties subscribe to these event messages. In Google Cloud we have even a product with the same name! Google Cloud PubSub.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IZPof5VZ5uduSeED" /><figcaption>Data Product teams send their updates to a central PubSub topic which is managed by a central platform team. Downstream Data products consume relevant messages from PubSub after filtering. The messages are also stored in permanent storages for dashboards or for DPs that poll earlier messages.</figcaption></figure><p>As shown in the above image, <strong>PubSub is at the core of the architecture</strong>. Teams publish their events to a shared global PubSub topic with attributes and ordering keys. Interested downstream product teams create their own subscriptions with filtering so that they only receive relevant messages.</p><p><strong>An SQL database for history and polling.</strong> Some data products may depend on several others. In that case, they need to store state of each data product before performing any calculation. We propose to have a permanent storage for all the history. Data Products can poll this database to check the historic data. This can be any operational database like Spanner or CloudSQL.</p><p><strong>Common message format.</strong> A single message format for all the teams enforces standardisation and increases the adoption by shared libraries.</p><p><strong>APIs for event ingestion and consumption</strong>. Although the DPs can interact with the backend services directly, an API for ingestion to PubSub and reading from database adds an abstraction for long term changes. Platform team can apply versioning easily by introducing an API.</p><p><strong>Observability via Looker/Looker Studio</strong>. A Looker dashboard on top of the data from SQL database let all the teams debug the pipelines. When the owners of Customer 360 suspects an issue with a report, they can quickly check upstream pipelines via these Looker dashboards. Moreover, since the messages store source systems, we can display lineage of data products from Looker as well.</p><p><strong>Central Platform team owns the infrastructure</strong>. The architecture is distributed and allows teams to work independently. But the PubSub topics, APIs, database and even the message format should be owned and maintained by a platform team.</p><p>To conclude, with this distributed orchestration, we can easily chain data products. DP teams may trigger their pipelines by using Cloud Functions/Workflows that are triggered by PubSub. We also increase observability. You may even go further and create alerts when an upstream pipeline fails again by triggering Cloud Functions from PubSub.</p><p>If you want to learn more about implementing Data Mesh on Google Cloud, check out this <a href="https://www.thoughtworks.com/en-es/about-us/partnerships/google-cloud/specialization-data-analytics/bringing-data-mesh-to-life">white paper</a> from my colleagues.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=610336839695" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-to-chain-and-observe-interdependent-data-products-on-google-cloud-610336839695">How to chain and observe interdependent Data Products on Google Cloud</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[How to validate files uploaded to GCS via Transfer Appliance]]></title>
            <link>https://medium.com/google-cloud/how-to-validate-files-uploaded-to-gcs-via-transfer-appliance-871e0deeabb8?source=rss-c0c51b375633------2</link>
            <guid isPermaLink="false">https://medium.com/p/871e0deeabb8</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[infrastructure]]></category>
            <category><![CDATA[google-cloud-storage]]></category>
            <category><![CDATA[google-cloud]]></category>
            <dc:creator><![CDATA[Yunus Durmuş]]></dc:creator>
            <pubDate>Thu, 20 Jul 2023 12:44:18 GMT</pubDate>
            <atom:updated>2023-07-20T13:39:36.393Z</atom:updated>
            <content:encoded><![CDATA[<p>When you upload files to GCS via <a href="https://cloud.google.com/transfer-appliance/docs/4.0/overview">Transfer Appliance</a>, it verifies the upload of data in the background. Nevertheless, there may have issues while copying data to TA itself. So it is a good practice to validate all the files that have been migrated to GCS buckets at the end.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/210/0*YAnDgYVnt4adwqQ4.png" /><figcaption>Transfer Appliance is a great option to upload large amounts of data to cloud</figcaption></figure><h4><strong>Methodology</strong></h4><p>We need to check the integrity of files with a hash method. GCS provides hashes of files as metadata in MD5 and CRC32C digests. MD5 has a lower hash collision probability but for large files GCS doesn’t create MD5 as they are stored in multiple pieces in the backend. CRC32C is available for all the files and it is good enough to check the integrity.</p><h4><strong>Steps</strong></h4><p>1. <strong>Create an inventory of the GCS bucket.</strong> GCS has an <a href="https://cloud.google.com/storage/docs/insights/inventory-reports">inventory report</a> feature. It creates a report with filenames and their CRC digests. It may run minimum daily frequency. Running it once is enough. Download it to your local machine for later steps.</p><p>2. <strong>Create hashes of files locally</strong>. <a href="https://rhash.sourceforge.net/manpage.php">rhash</a> is a great utility to generate the hashes. You may install by a package manager of your system. In Mac: <em>brew install rhash</em></p><pre># the below command creates crc32c in base64 recursively in a directory and then outputs to a file<br>rhash -crc32c -base64 -r -simple -o hashes.tsv .</pre><p>You may create different hash files for different locations. In the next step you will see how to load them into a data warehouse for comparison.</p><p>4. <strong>Compare hashes.</strong> We will compare both CSVs by loading them into a data warehouse. I use <a href="https://duckdb.org/docs/installation/index">DuckDB</a> for simplicity.</p><pre>brew install duckdb<br># open duckdb cli:<br>duckdb</pre><pre>-- create table from the onpremise hashes<br>CREATE TABLE onpremise_hashes(crc32c_hash VARCHAR, filename VARCHAR);<br>COPY onpremise_hashes from &#39;hashes.tsv&#39; (HEADER FALSE, DELIMITER &#39; &#39;);<br>-- verify that they are uploaded correctly<br>select * from onpremise_hashes limit 3;<br>-- create table from the GCS inventory. Adjust the schema based on your export.<br>CREATE TABLE gcs_inventory(project BIGINT,bucket VARCHAR, name VARCHAR, location VARCHAR,timeCreated TIMESTAMP, crc32c VARCHAR);<br>COPY gcs_inventory from &#39;gcs_inventory.csv&#39; (AUTO_DETECT TRUE, HEADER TRUE);<br>select * from gcs_inventory limit 3;<br>-- after this part, you should start playing with tables to see the difference<br>-- for instance<br>select * from onpremise_hashes as o left join gcs_inventory as g on g.name=o.filename and g.crc32c=o.crc32c_hash;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=871e0deeabb8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-to-validate-files-uploaded-to-gcs-via-transfer-appliance-871e0deeabb8">How to validate files uploaded to GCS via Transfer Appliance</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>