<?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 Keven Pinto on Medium]]></title>
        <description><![CDATA[Stories by Keven Pinto on Medium]]></description>
        <link>https://medium.com/@keven.pinto_10445?source=rss-5a17a7fa9fe0------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Mz_UF3jImYwa5FEhIiQOIg.jpeg</url>
            <title>Stories by Keven Pinto on Medium</title>
            <link>https://medium.com/@keven.pinto_10445?source=rss-5a17a7fa9fe0------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 06:01:40 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@keven.pinto_10445/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 review of the Google Datastream Service - Cloud SQL (PostgreSQL) to BigQuery]]></title>
            <link>https://medium.com/qodea/a-review-of-the-google-datastream-service-cloud-sql-postgresql-to-bigquery-e0298226c188?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/e0298226c188</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[datastream]]></category>
            <category><![CDATA[cdc]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Thu, 23 Mar 2023 15:47:10 GMT</pubDate>
            <atom:updated>2023-03-26T17:01:36.925Z</atom:updated>
            <content:encoded><![CDATA[<p><a href="https://cloud.google.com/datastream">Datastream</a> introduced the ability to seamlessly replicate data from Cloud SQL to BigQuery late in 2022. Given that (near) real time replication of operational databases is one of the most common uses cases we observe as part of our client requirement, we decided to review this service and put it through its paces. We were quite interested in observing how the stream would behave in the following scenarios:</p><ul><li>DMLs on Tables without Primary Keys</li><li>Zonal Failover</li><li>Regional Failovers</li><li>DMLs on Partition Tables</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/526/1*kO6sCM9izihIVSBqGZ4V1w.png" /><figcaption>Data Stream</figcaption></figure><p>For the purpose of this test, we are looking at the Cloud SQL (PostgreSQL 14) to BigQuery integration via Datastream.</p><p>Before we proceed further, we’d like to state that this service is a <a href="https://cloud.google.com/terms/service-terms#5">Pre-GA</a> offering — <em>Beta</em> in layperson speak. This means that any features or quirks we may point out in this article may or may not exist in the end product. We also strongly advise a review of the product documentation closer to the time of implementation as features may have changed, evolved or been dropped.</p><p><strong>Our Test Setup</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/840/1*UfM7Nz7OinnM4KYV_X8T_Q.png" /><figcaption>Architecture</figcaption></figure><p>Our setup composed of the following services:</p><ul><li>A HA (2 zones) Cloud SQL Instance using the PostgreSQL 14 db engine in Region 1</li><li>A read-replica Cloud SQL Instance in Region 2</li><li>A Datastream in Region 1 reading from Our Cloud SQL instance in Region 1 and writing to Single Region BigQuery Dataset in the same region, Staleness limit for this replication set to 0 seconds</li><li>A second BigQuery dataset in Region 2 being refreshed at regular intervals using the BigQuery dataset copy service… please refer to my <a href="https://medium.com/cts-technologies/bigquery-cross-region-replication-gotchas-4645c4ce0e6e">other blog</a> for some of my observations on this service before committing to it</li><li>A VM hosting our <a href="https://cloud.google.com/sql/docs/postgres/connect-auth-proxy">cloud sql auth proxy</a></li><li>Private Google access enabled on our VPC Subnet as our Cloud SQL instance was with a Private IP Only</li><li>A python function running from my desktop that writes to our db every n seconds via a tunnel through to our cloud sql auth proxy</li></ul><p>For details on how to setup your Datastream, kindly refer to my <a href="https://medium.com/p/a96848d3dbf7/">other blog</a>, <a href="https://github.com/kevenpinto/mr-datastream">here</a> is a link to that sample repo from the other blog.</p><p><strong>Test 1 — Write to a table with a Primary Key defined in PostgreSQL</strong></p><pre>create table data_schema.test_datastream(<br>    myid SERIAL PRIMARY KEY,<br>    somecol varchar(100),<br>    log_date  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP)</pre><p>We started with simply writing some records to Postgres and waited for these to appear in Bigquery.</p><p>This works a treat, our data is streaming to Bigquery!</p><p><strong>Test 2 — Simulate Failover of PostgreSQL in Region 1</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ngMyeH_7i3fecT_EsrlBNw.png" /></figure><p>For this test, we manually initiated a Failover on the Instance from the console, kindly refer <a href="https://cloud.google.com/sql/docs/postgres/configure-ha#test">here</a> for details on how to achieve this. Our Python routine is attempting to write to Cloud SQL throughout this failover.</p><p><strong>Observations:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cKl-smEyQ0DrapNmUkbSNA.png" /></figure><ol><li>Our instance <em>seamlessly</em> moves from europe-west2-c to europe-west2–a</li><li>Writes from the Python routine timeout during failover but then begin to get committed to the db once the failover completes</li><li>Datastream complains for a bit and then resets the connection once failover completes automatically</li><li>Records in BigQuery and Cloud SQL match</li></ol><p>We ran the failover a few times back and forth and each time, the datastream came back again by itself without the need for any manual intervention. Zonal failovers for Cloud SQL and Datastream are seamless. All going well so far!</p><p><strong>Test 3: Add a Table with no PK and NO Replica Identity</strong></p><pre>create table data_schema.test_datastream_nopk(<br>    myid SERIAL,<br>    somecol varchar(100),<br>    log_date  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);</pre><p><strong>Observations:</strong></p><ul><li>This table is not replicated to BigQuery, this is in line with the PostgreSQL requirements stated <a href="https://cloud.google.com/datastream/docs/sources-postgresql">here</a></li></ul><p><strong>Test 4: Alter table with No PK and add a Replica Identity to it</strong></p><pre>Alter table data_schema.test_datastream_nopk REPLICA IDENTITY FULL;</pre><p>The purpose of a Replica Identity as per the PostgreSQL docs…</p><p><em>“This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted. In most cases, the old value of each column is only logged if it differs from the new value; however, if the old value is stored externally, it is always logged regardless of whether it changed. This option has no effect except when logical replication is in use.”</em></p><p><strong>Observations:</strong></p><ul><li>This table is now replicated to BigQuery</li></ul><p><strong>Test 5: Test the delete feature</strong></p><pre>delete from data_schema.test_datastream where myid=3;<br>delete from data_schema.test_datastream_nopk where myid=3;</pre><p><strong>Observations:</strong></p><ul><li>These records are correctly dropped from BigQuery as well</li></ul><p><strong>Test 6: Test the Update feature</strong></p><pre><br>update data_schema.test_datastream set somecol=&#39;newval&#39; where myid=1;<br>update data_schema.test_datastream_nopk set somecol=&#39;newval&#39; where myid=1;</pre><p><strong>Observations:</strong></p><ul><li>PK Table in PostgreSQL and BigQuery in sync</li><li><strong>Non PK Table shows up in BigQuery as a new row — </strong>Please note setting a table with a REPLICA IDENTITY of FULL is discouraged… here is an excerpt from the postgres docs</li></ul><p><em>“If the table does not have any suitable key, then it can be set to replica identity “full”, which means the entire row becomes the key. This, however, is very inefficient and should only be used as a fallback if no other solution is possible”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*95TwV3-3CTBDgfpXTyMmAg.png" /><figcaption>New Row in BQ</figcaption></figure><p><strong>Test 7: DML on Partition Tables</strong></p><p>The purpose of database partitioning is to provide support for very large tables and indexes by splitting them into smaller pieces.</p><p>Partitioning in PostgreSQL is declarative, this means the partitioned table itself is virtual having no storage of its own. The data is stored in the Individual tables that store the data. This behavior is quite different to other popular databases like Oracle which support Horizontal partitioning wherein the management of individual partitions is managed by the table itself.</p><p>It is important to understand this difference in implementation because it has an impact on what you see at the other end of your stream.</p><p>Let’s take a look at the definitions of a partitioned table in Oracle V/S PostgreSQL to better understand this.</p><pre># ORACLE<br>CREATE TABLE data_schema.test_datastream_part<br>(<br>    my_id NUMBER GENERATED BY DEFAULT AS IDENTITY, <br>    somecol varchar(100),<br>    log_date TIMESTAMP (2) NOT NULL,<br>    CONSTRAINT pk_test_datastream_part  PRIMARY KEY(myid,log_date);<br>)<br>PARTITION BY RANGE(log_date)<br>(<br>PARTITION log_date VALUES LESS THAN (TO_DATE(&#39;01/01/2022&#39;,&#39;DD/MM/YYYY&#39;)) TABLESPACE TB1,<br>PARTITION log_date VALUES LESS THAN (TO_DATE(&#39;01/01/2023&#39;,&#39;DD/MM/YYYY&#39;)) TABLESPACE TB2,<br>PARTITION log_date VALUES LESS THAN (TO_DATE(&#39;01/01/2024&#39;,&#39;DD/MM/YYYY&#39;)) TABLESPACE TB3<br>);</pre><pre># POSTGRESQL<br><br>CREATE TABLE data_schema.test_datastream_part<br>(<br>    myid SERIAL,<br>    somecol varchar(100),<br>    log_date  timestamp(0) without time zone NULL<br>)<br>PARTITION BY RANGE(log_date);<br><br>ALTER TABLE data_schema.test_datastream_part ADD CONSTRAINT pk_test_datastream_part primary key(myid,log_date);<br><br>CREATE TABLE data_schema.test_datastream_part_2021 PARTITION OF data_schema.test_datastream_part FOR VALUES FROM (&#39;2021-01-01&#39;) TO (&#39;2022-01-01&#39;);<br>CREATE TABLE data_schema.test_datastream_part_2022 PARTITION OF data_schema.test_datastream_part FOR VALUES FROM (&#39;2022-01-01&#39;) TO (&#39;2023-01-01&#39;);<br>CREATE TABLE data_schema.test_datastream_part_2023 PARTITION OF data_schema.test_datastream_part FOR VALUES FROM (&#39;2023-01-01&#39;) TO (&#39;2024-01-01&#39;);</pre><p>In Oracle, the partition table holds the partition definition as well as the individual partitions. <em>There is no need to define individual tables separately and attach these to the main partition table.</em></p><p>In PostgreSQL, the partition table is <em>Virtual</em>, <em>data is held in the individual tables associated </em>to the Virtual partition table. Each of these tables need to be defined separately i.e. data_schema.test_datastream_part_2021, data_schema.test_datastream_part_2022, data_schema.test_datastream_part_2023</p><p>So, why am I telling you all this?</p><p>The reason is that this behavior of PostgreSQL has an impact on the datastream. If we consider the example from above where each postgreSQL partition table holds 10 records, <strong>One will see 4 tables in BigQuery rather than just 1. </strong>These<strong> </strong>4 tables will be as follows:</p><ul><li>test_datastream_part with 30 records</li><li>test_datastream_part_2021 with 10 records</li><li>test_datastream_part_2022 with 10 records</li><li>test_datastream_part_2023 with 10 records</li></ul><p><strong>In short, we now have data being duplicated in BigQuery.</strong></p><p>This situation can be remedied by setting the publish_via_partition_root=true on your postgreSQL database publication;</p><p>For more details on this subject, i’d like to signpost you’ll to <a href="https://medium.com/google-cloud/configure-streams-from-partition-table-in-postgresql-to-non-partition-in-bigquery-using-datastream-3238fe3321d9">this excellent blog.</a></p><p><strong>Test 8: Simulate a Regional Failover</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ob4ukZvP7o10V3oFFU06Q.png" /><figcaption>Promote Replica</figcaption></figure><p>This step involves the following :</p><ul><li>Promoting the Read Replica in Region 2 to Primary</li><li>Shutting down the Original Primary SQL Instance to simulate a service outage</li><li>Point the auth proxy container to the newly promoted replica</li></ul><pre>docker run -d -p 5432:5432 gcr.io/cloudsql-docker/gce-proxy:1.33.1 /cloud_sql_proxy -instances=cloudsqlpoc-demo:europe-west2:demo-europe-west1=tcp:0.0.0.0:5432 -ip_address_types=PRIVATE</pre><p>Kindly refer to <a href="https://cloud.google.com/sql/docs/postgres/intro-to-cloud-sql-disaster-recovery">this</a> document for more details on this process.</p><blockquote>Please note that once a read replica is promoted, it no more has any connection to the Original Instance. This is now a separate instance.</blockquote><p><strong>Observations:</strong></p><ul><li>The Promoted Replica does not contain a replication slot, this behaviour is explained in detail <a href="https://cloud.google.com/sql/docs/postgres/replication/configure-logical-replication#limitations-general">here</a></li><li>The Stream is broken and will not self restore as we observed in the Zonal failovers, the reason for this behaviour is because this is a completely different instance without a replication slot and the stream does not have a connection to it</li></ul><p><strong>Summary</strong></p><ul><li>Streams are a great was to implement change data capture between a transactional datastore (Postgres) and a warehouse without writing a line of code</li><li>Datastream accurately tracks insert, updates and deletes and replicates these across to BigQuery</li><li>Source tables that do not have a Primary Key or a Replica Identity are ignored</li><li>Tables with a Full Replica Identity have a limitation with tracking updates. update records manifest themselves as new records on BigQuery</li><li>Datastream works seamlessly in case of Zonal Failovers in a Region, it restarts itself as soon as the instance fails overs to a new zone</li><li>A Datastream must be paused in a Regional Failover. As the promoted read replica is a new instance and has no more ties to the old instance</li><li>In a Regional Failover, the stream being setup is a new stream and has no connection to the old stream. Make sure to set your backfill strategy accordingly, especially if data from BigQuery is being regularly synced to site B</li></ul><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e0298226c188" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/a-review-of-the-google-datastream-service-cloud-sql-postgresql-to-bigquery-e0298226c188">A review of the Google Datastream Service - Cloud SQL (PostgreSQL) to BigQuery</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using Datastream as a Data Sync and DR tool]]></title>
            <link>https://medium.com/qodea/using-datastream-as-a-data-sync-and-dr-tool-a96848d3dbf7?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/a96848d3dbf7</guid>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[disaster-recovery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[multi-region]]></category>
            <category><![CDATA[google-cloud-datastream]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Tue, 14 Mar 2023 09:23:04 GMT</pubDate>
            <atom:updated>2023-03-14T11:41:28.028Z</atom:updated>
            <content:encoded><![CDATA[<p>In my <a href="https://medium.com/cts-technologies/bigquery-cross-region-replication-gotchas-4645c4ce0e6e">last blog</a> we looked at how near real time BigQuery data sync across two regions could not be achieved using the <a href="https://cloud.google.com/bigquery/docs/copying-datasets">dataset copy service</a> provided by Google and how we had to abandon the approach. In this blog, we talk about the eventual solution we adopted to meet our objective.</p><p><strong>Initial Architecture</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/1*x-mGH6q6TklLxmGquWIScQ.png" /><figcaption>Initial Architecture</figcaption></figure><p>Before we explore the final solution, let’s take a quick journey through the evolution of our architecture.</p><p>Our objective was to have a Multi Region Active-Passive Setup. Region 2 being our DR failover site.</p><p><strong>Region 1</strong> <strong>Components</strong>:</p><ul><li>HA Cloud SQL PostgreSQL 14 Db that is CMEK encrypted</li><li>Logical replication enabled (cloudsql.logical_decoding=on) on the PostgreSQL db and complete with a replication slot and a publication created</li><li>BigQuery Dataset to hold data from our Primary Cloud SQL Db</li><li>Datastream Stream that hydrates the BigQuery dataset with all changes happening on the Cloud SQL database — <a href="https://en.wikipedia.org/wiki/Change_data_capture">CDC in short</a></li><li>A Cloud SQL HA proxy to bridge the connection from Datastream to our Cloud SQL instance, more details can be found <a href="https://cloud.google.com/datastream/docs/private-connectivity#reverse-csql-proxy">here</a></li><li><a href="https://cloud.google.com/bigquery/docs/copying-datasets">A BigQuery Dataset Copy service</a> that refreshes our dataset in Region 2</li></ul><p><strong>Region 2 Components</strong>:</p><ul><li>A Read Replica of our Primary Cloud SQL instance, the replica is encrypted using a different CMEK key to the primary as the replica is in a different region</li><li>A Replica BigQuery dataset that acts as failover to our Region 1 dataset</li></ul><p>As mentioned in my <a href="https://medium.com/cts-technologies/bigquery-cross-region-replication-gotchas-4645c4ce0e6e">last blog</a>, this approach was abandoned due to the limitations of this service. Please refer to the <a href="https://medium.com/cts-technologies/bigquery-cross-region-replication-gotchas-4645c4ce0e6e">blog</a> for more details</p><p><strong>Attempt 2</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/1*9MDd-Z3tYtuubmRjNGjSxA.png" /><figcaption>Attempt 2</figcaption></figure><p>As part of attempt 2 we did the following:</p><ul><li>We dropped the data set copy service</li><li>Attempted to setup a second Stream from the Read Replica in Region 2 to the Dataset in Region 2</li></ul><p>This attempt failed due to the limitation documented <a href="https://cloud.google.com/datastream/docs/faq#mysql_change_data_capture_cdc">here</a>. Now, if you are wondering, why didn’t we read the docs carefully? Well, we did. This update to the doc was kindly added on by Google as soon as we raised this concern with them.</p><p><strong>Final Architecture</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/1*BxlTAJW3JUXcTaMulXAFZQ.png" /></figure><p>I would love to state that the final solution appeared to me in a fever dream… a pure architectural epiphany. Sadly, such was not the case. Just pure perseverance and belief from the team and support from the client helped us arrive at the final solution.</p><p><strong>The Solution</strong></p><p>The solution was actually quite simple and as follows:</p><ul><li>Create 2 Replication slots on the database</li><li>Create 2 corresponding publications on the database</li><li>Setup a stream in Region 1 to look at the first Replication slot and push changes to the Dataset in Region 1</li><li>Setup a Stream in Region 2 to look at the second Replication slot and push changes to the dataset in Region 2</li></ul><p>Below is copy of our SQL Setup Script that shows the setup of the Replication slots and Publications. This script needs to be run by a user with Superuser privileges. For Cloud SQL this user is the postgres user that comes pre installed on the instance. We store all our tables in a schema called <strong>data_schema</strong> and usage on this schema needs to be granted to the user being used for replication (postgres).</p><pre>DO<br>\$do$<br>DECLARE<br> rs1 character varying(255);<br> rs2 character varying(255);<br>BEGIN<br>    ALTER USER ${user} with REPLICATION;<br><br>    IF NOT EXISTS (SELECT 1 FROM pg_publication WHERE pubname = &#39;pub1&#39;) THEN<br>        RAISE NOTICE &#39;Creating Pubication pub1&#39;;<br>        CREATE PUBLICATION pub1 FOR ALL TABLES  WITH (publish = &#39;insert,update,delete&#39;) ;<br>    END IF;<br><br>    IF NOT EXISTS (SELECT 1 FROM pg_publication WHERE pubname = &#39;pub2&#39;) THEN<br>        RAISE NOTICE &#39;Creating Pubication pub2&#39;;<br>        CREATE PUBLICATION pub2 FOR ALL TABLES  WITH (publish = &#39;insert,update,delete&#39;) ;<br>    END IF;<br><br>    COMMIT;<br>    IF NOT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = &#39;rs1&#39;) THEN<br>        RAISE NOTICE &#39;Creating Replication Slot rs1&#39;;<br>        rs1 := (SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT(&#39;rs1&#39;, &#39;pgoutput&#39;));<br>    END IF;<br>    COMMIT;<br><br>    IF NOT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = &#39;rs2&#39;) THEN<br>        RAISE NOTICE &#39;Creating Replication Slot rs2&#39;;<br>        rs2:= (SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT(&#39;rs2&#39;, &#39;pgoutput&#39;));<br>    END IF;<br><br>    CREATE SCHEMA IF NOT EXISTS ${schema};<br>    GRANT SELECT ON ALL TABLES IN SCHEMA ${schema} TO ${user};<br>    GRANT USAGE ON SCHEMA ${schema} TO ${user};<br>    ALTER DEFAULT PRIVILEGES IN SCHEMA ${schema} GRANT SELECT ON TABLES TO ${user};<br><br>END<br>\$do$;</pre><p><strong>Accompanying Code</strong></p><p><a href="https://github.com/kevenpinto/mr-datastream">Here</a> one can find a link to a sample repo that will help you setup all the assets shown in the final architecture, the code is written in terraform using <a href="https://github.com/GoogleCloudPlatform/cloud-foundation-fabric">Google Cloud Fabric Modules</a>. Please read the Readme in the repo for more more details on how to set this up.</p><p><strong>Start the Streams</strong></p><p>The streams in the accompanying code repo are deployed in the <strong>NOT_STARTED </strong>state. Start these <strong>manually</strong> once you have created tables (must have PK) and have added some data to these tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tS3VeksfsNpOatNCghfUvQ.png" /><figcaption>Streams Not Started</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XrZPWGsFBqY--H-lqnl5Qw.png" /><figcaption>Stream Start warning</figcaption></figure><p><strong>Final State</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pmeLttbv5Fe46ZGjtSKOGg.png" /><figcaption>Running Streams</figcaption></figure><p>Once the stream starts, it runs a back fill process and writes data to our BigQuery datasets in both Regions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B5FBx7ukBN9POU-e9ns1TA.png" /><figcaption>Regional Bigquery Datasets</figcaption></figure><p>That’s it… have a go!</p><p>Note: Given the business requirement outlined here, it might not be long before Google update their offerings around cross-region data set replication - until then, this approach works just as well.</p><p>Finally, thanks to my colleague @iamtheone.ankur for help with setting up the VPC private connectivity and acting as a great sounding board to all my ideas.</p><p><strong>References:</strong></p><ul><li><a href="https://github.com/kevenpinto/mr-datastream">Sample Code</a></li><li><a href="https://github.com/GoogleCloudPlatform/cloud-foundation-fabric">Cloud Fabric Terraform Modules</a></li><li><a href="https://cloud.google.com/datastream/docs/private-connectivity">Datastream Private Connectivity</a></li><li><a href="https://medium.com/cts-technologies/bigquery-cross-region-replication-gotchas-4645c4ce0e6e">BigQuery Gotchas</a></li></ul><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a96848d3dbf7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/using-datastream-as-a-data-sync-and-dr-tool-a96848d3dbf7">Using Datastream as a Data Sync and DR tool</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery Cross-Region Replication Gotchas]]></title>
            <link>https://medium.com/qodea/bigquery-cross-region-replication-gotchas-4645c4ce0e6e?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/4645c4ce0e6e</guid>
            <category><![CDATA[multi-region]]></category>
            <category><![CDATA[cross-region-dataset-copy]]></category>
            <category><![CDATA[disaster-recovery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Thu, 26 Jan 2023 07:50:02 GMT</pubDate>
            <atom:updated>2023-03-10T16:04:03.180Z</atom:updated>
            <content:encoded><![CDATA[<p>2022 was the fifth warmest year on record. The July heatwave pushing temperatures to 40°C in parts of the UK was so brutal, that it was a rare occasion that my camera was switched on during office meetings.</p><p>There were times, I felt that if I stayed out too long, I may melt. Thankfully that didn’t happen… to me at least! Sadly, we can’t say the same for certain <a href="https://www.datacenterdynamics.com/en/news/googles-london-data-center-outage-during-heatwave-caused-by-simultaneous-failure-of-multiple-redundant-cooling-systems/#:~:text=%E2%80%9COn%20Tuesday%2C%2019%20July%202022,impacted%20multiple%20Google%20Cloud%20services.">Google and Oracle services</a> located in a London data centre on the 19th July. The failures caused by the heatwave and subsequent disruption that resulted led to one of our clients facing a service outage that affected them for several hours. After restoring service, Google was quick to commission a project to ensure multi-regional resiliency for this client. So ironically, this extreme climate event has resulted in a technical opportunity to improve the client platform — and inspire this blog</p><p>We kicked of this project with a single goal in mind, that if this ever were to happen again, we would be ready(ish). I say that with some reduced conviction as regional failovers and fail-backs do incur some downtime, albeit minutes, <em>it’s not seamless!</em> Especially the database layer — more on that some other time.</p><blockquote>In this Blog, I’d like to talk about a small slice of the overall architecture — the Multi Region Setup for BigQuery, our approach, our initial success and why we felt we needed to rethink the approach.</blockquote><p>Before we go into details of our architecture, let’s clear some misconceptions and lay out some facts:</p><ul><li>Multi-region Datasets in Bigquery does not imply that a copy of the data is stored in a different geographical region. Currently, implementation means that data is located in a region within a large geographical area such as the US or EU</li><li>The term multi-region in BigQuery can be confusing as it is not the same as <a href="https://cloud.google.com/storage/docs/locations#location-mr">Cloud Storage’s definition of multi-region</a></li><li>Multi-region datasets cannot be used for data resiliency purposes</li></ul><h3>Architecture</h3><p>A quick scan of current Google offerings threw up <a href="https://cloud.google.com/bigquery/docs/copying-datasets"><strong>BigQuery’s cross-region dataset replication</strong> </a>as a good candidate of achieving resiliency and cross-region durability. The only small challenge was that this feature is still currently in Preview — I say small, as the client was happy to underwrite this risk. Onwards we went!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/984/1*K0mgU1RcaA9s64bB9-E27Q.png" /><figcaption>Database Cross Region View</figcaption></figure><p>Our Proposed setup involved the following components:</p><ul><li>A HA Cloud SQL (PostgreSQL 14) Instance (2 Zones) in Region 1 with a <em>read- replica</em> in Region 2</li><li>A Datastream in Region 1 to replicate data from PostgreSQL to a Bigquery dataset in Region 1</li><li>A Primary Bigquery dataset in Region 1</li><li>A Replica Bigquery dataset in Region 2 being kept in sync using the Bigquery dataset transfer service</li></ul><p>In case you wondering, <em>why didn’t we setup a Datastream in our Secondary region</em>?.. Good question! At the moment, <a href="https://cloud.google.com/datastream/docs/faq#mysql_change_data_capture_cdc">Datastream only supports read replicas from MySQL</a> and we are on PostgreSQL<a href="https://cloud.google.com/datastream/docs/faq#postgresql_change_data_capture_cdc"> 14</a>.</p><p><strong>Challenge 1 — Scheduling</strong></p><p>Our goal was to have consistency across our 2 datasets, however when setting the Transfer schedule we hit the first constraint. <em>You cannot increase the frequency of the copy job to be less than every 12 hours.</em></p><p>This was not ideal, we wanted consistency across regions and a latency of 12 hours was not going to cut it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*beQ56dlwIUfjFzIj4qTlIw.png" /><figcaption>12 hr Constraint</figcaption></figure><p><strong>Solution</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*PBrw5KE983YbJk96TOpRtA.png" /><figcaption>Low Latency Approach</figcaption></figure><p>The flow of the proposed Solution is as follows:</p><ul><li>Deploy the Data Transfer Service as an <strong>On-Demand Service</strong>. This simply means that it does not run on a schedule but will need to be called explicitly</li><li>Configure the Data Transfer Service to write to a Pub/Sub topic upon completion. This is an out of the box feature:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/531/1*Mmf5ceDQqb5N9isU01V-qg.png" /><figcaption>Data Transfer Notification</figcaption></figure><ul><li>Create an Event Based Trigger on this Pub Sub Q to call a Cloud Function</li><li>Code the triggered Cloud function to call the Data Transfer Service Job manually, thus creating a continuous loop</li></ul><p>Simple! Well, not quite. The job ran for a few hours and then started failing. It looked like we forgot to take some other dataset copy constraints in consideration. Enter Challenges 2 &amp; 3!</p><p><strong>Challenge 2 &amp; 3 — Daily Limits</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/892/1*hY9MMQvi145LnJd1EPPidQ.png" /><figcaption>Constraints</figcaption></figure><p>Before we look at the impact of these constraints, lets understand a few things:</p><ul><li>1 table Copy = 1 Job</li><li>If there is no new data since the last Job, a job slot is not taken up (nice!), see screenshot below where n tables have been skipped as there was no new data and the summary shows 1 jobs as only 1 table had data added to it. Data set copy does this even with the Overwrite Destination Tables option set as True</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jNFX57Qw9flbBbdDNpnZGQ.png" /></figure><h4>High Velocity tables</h4><p>To highlight challenge 2 let’s take a look at the following scenario:</p><p>Consider a group of tables that are high velocity and qualify for a data copy in each run. If each transfer job takes on average 3 minutes to run, we will hit our 100 jobs per destination table limit in the first 300 minutes of the day (continuous loop design). Given, 1440 minutes in a day, we won&#39;t be able to refresh that table for the next 19 hrs! At this point the 12 hour constraint seems like a much better solution.</p><p>In order to mitigate, Challenge 2, we decided to add some logic to our Cloud function to ensure that the job never runs more than 4 times per hour. We worked this out by dividing the minutes in a day (1440), by the number of job per table constraint, i.e. 1440/100. Ok! So we now do 96 jobs a day, that’s 4 jobs short of the constraint. Good work then?! Not quite! There was still Challenge 3 to consider.</p><p>Our Client has a large number of high velocity tables, and given that we wanted to keep latency low, we would have to pick only 20 high velocity tables in order to meet the 2000 jobs per day per project limit, i.e 20 * 100 runs = 2000 runs. <em>It was at this point, we decided that our approach needs a rethink!</em></p><p><strong>For details about our eventual Solution, look </strong><a href="https://medium.com/p/a96848d3dbf7/edit"><strong>here</strong></a><strong>.</strong></p><p><strong>Summary</strong></p><p>The BQ cross-region dataset transfer service is easy to setup and fast (very!). However, the scheduling limitations and daily limits do not make it an ideal candidate for high velocity and cross region consistency use cases.</p><p>Finally, I attach a very basic and early version of the cloud function we wrote in order to invoke the Transfer, enjoy!</p><pre># https://cloud.google.com/python/docs/reference/bigquerydatatransfer/latest/google.cloud.bigquery_datatransfer_v1.services.data_transfer_service.DataTransferServiceClient#google_cloud_bigquery_datatransfer_v1_services_data_transfer_service_DataTransferServiceClient_get_transfer_run<br># https://cloud.google.com/bigquery/docs/reference/datatransfer/rest/v1/TransferState<br>from pathlib import Path<br>import time<br>import functions_framework<br>from google.cloud import bigquery_datatransfer_v1<br>from google.protobuf.timestamp_pb2 import Timestamp<br>from google.cloud import pubsub_v1<br><br><br>def publish_message(project_id: str, topic_id: str, message: str):<br>    publisher = pubsub_v1.PublisherClient()<br>    topic_path = publisher.topic_path(project_id, topic_id)<br>    data = message.encode(&quot;utf-8&quot;)<br>    future = publisher.publish(topic_path, data)<br>    print(f&quot;Published messages to {topic_path}.-{future.result()}&quot;)<br><br><br>def get_transfer_id(<br>    config_name: str,<br>    parent: str,<br>    client,<br>):<br>    # Initialize request argument(s)<br>    request = bigquery_datatransfer_v1.ListTransferConfigsRequest(<br>        parent=parent,<br>    )<br><br>    # Make the request<br>    page_result = client.list_transfer_configs(request=request)<br>    config_id = &quot;&quot;<br><br>    # Handle the response<br>    for response in page_result:<br>        if response.display_name == config_name:<br>            config_id = Path(response.name).name<br><br>    return config_id<br><br><br>def run_manual_transfer(project_id: str, config_id: str, parent: str, client):<br>    parent = f&quot;{parent}/transferConfigs/{config_id}&quot;<br>    start_time = Timestamp(seconds=int(time.time()))<br>    request = bigquery_datatransfer_v1.StartManualTransferRunsRequest(<br>        {&quot;parent&quot;: parent, &quot;requested_run_time&quot;: start_time}<br>    )<br>    response = client.start_manual_transfer_runs(request, timeout=360)<br>    return response<br><br><br>def get_transfer_run(project_id: str, parent: str, config_id: str, run_id: str, client):<br><br>    transfer_running_states = [&quot;PENDING&quot;, &quot;RUNNING&quot;]<br>    name = f&quot;{parent}/transferConfigs/{config_id}/runs/{run_id}&quot;<br><br>    # Initialize request argument(s)<br>    request = bigquery_datatransfer_v1.GetTransferRunRequest(name=name)<br><br>    # Inner Recursive Function<br>    def monitor_run(pause_interval: int):<br>        # Make the request<br>        response = client.get_transfer_run(request=request)<br>        for line in str(response).splitlines():<br>            if line.strip().startswith(&quot;state&quot;):<br>                state = line.split(&quot;:&quot;)[1].strip()<br>                if state in transfer_running_states:<br>                    # Wait n seconds<br>                    print(f&quot;Run State:{state}...Waiting:{pause_interval} Seconds&quot;)<br>                    time.sleep(pause_interval)<br>                    return monitor_run(pause_interval + 1)<br>                else:<br>                    return state<br><br>    # Handle the response<br>    return monitor_run(0)<br><br><br>def get_run_id(response):<br>    run_id = &quot;&quot;<br>    for line in str(response).splitlines():<br>        if line.strip().startswith(&quot;name&quot;):<br>            run_id = Path(line.split(&quot;:&quot;)[1].replace(&#39;&quot;&#39;, &quot;&quot;)).name<br>    return run_id<br><br><br># Entry Point <br># This routine assumes that the tranfer job has already been created<br>@functions_framework.http<br>def main(request):<br>    project_id = &quot;PLEASEUPDATE&quot;<br>    source_region = &quot;PLEASEUPDATE&quot;<br>    target_region = &quot;PLEASEUPDATE&quot;<br>    config_name = &quot;PLEASEUPDATE&quot;<br>    topic_id = &quot;PLEASEUPDATE&quot;<br>    transfer_success_states = [&quot;SUCCEEDED&quot;]<br>    transfer_failed_states = [&quot;TRANSFER_STATE_UNSPECIFIED&quot;, &quot;FAILED&quot;, &quot;CANCELLED&quot;]<br><br>    # parent<br>    parent = f&quot;projects/{project_id}/locations/{target_region}&quot;<br><br>    # Create a client<br>    client = bigquery_datatransfer_v1.DataTransferServiceClient()<br><br>    # Get the Config id of the Transfer Job<br>    config_id = get_transfer_id(<br>        config_name=config_name,<br>        parent=parent,<br>        client=client,<br>    )<br>    <br>    # Trigger the Transfer Manually<br>    response = run_manual_transfer(<br>        project_id=project_id, config_id=config_id, parent=parent, client=client<br>    )<br><br>    # Extract Run Id of the Triggered Job<br>    run_id = get_run_id(response=response)<br><br>    if run_id != &quot;&quot;:<br>        print(f&quot;Cross Region DataSet Copy Initiated - Run Id:{run_id}&quot;)<br>        # Monitor the Job to completion<br>        # One can skip this and just parse the Completion message on the Q<br>        run_response = get_transfer_run(<br>            project_id=project_id,<br>            parent=parent,<br>            config_id=config_id,<br>            run_id=run_id,<br>            client=client,<br>        )<br><br>        if run_response in transfer_success_states:<br>            print(&quot;Transfer Run Succeeded... Proceeding to Schedule Next Run&quot;)<br>            publish_message(<br>                project_id=project_id, topic_id=topic_id, message=run_response<br>            )<br>        else:<br>            print(f&quot;Transfer Run Completed with Status:{run_response}&quot;)<br>    else:<br>        print(&quot;Unable to Start Job&quot;)<br><br><br><br>if __name__ == &quot;__main__&quot;:<br>    main(None)</pre><p>Thank you <a href="https://medium.com/u/4dddd636bb7f">Charlotte McKee</a> and <a href="https://medium.com/u/2aa42db9804f">Stuart Barnett</a> for editing this article and providing some great feedback.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4645c4ce0e6e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/bigquery-cross-region-replication-gotchas-4645c4ce0e6e">BigQuery Cross-Region Replication Gotchas</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Centralised Approach to CICD of DAGs on Google Cloud Composer with Google Cloud Build — Part 2]]></title>
            <link>https://medium.com/qodea/a-centralised-approach-to-cicd-of-dags-on-google-cloud-composer-with-google-cloud-build-part-2-2d0625ea44b0?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/2d0625ea44b0</guid>
            <category><![CDATA[google-cloud-composer]]></category>
            <category><![CDATA[github]]></category>
            <category><![CDATA[google-cloud-build]]></category>
            <category><![CDATA[directed-acyclic-graph]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Thu, 27 Oct 2022 08:57:30 GMT</pubDate>
            <atom:updated>2022-11-01T07:57:40.339Z</atom:updated>
            <content:encoded><![CDATA[<h3>A Centralised Approach to CICD of DAGs on Google Cloud Composer with Google Cloud Build — Part 2</h3><figure><img alt="CICD, DAGs, Cloud Composer, Cloud Build" src="https://cdn-images-1.medium.com/max/810/1*hUmx974DaG71qrG5SZk8Og.png" /><figcaption>CICD</figcaption></figure><p>In <a href="https://medium.com/p/1ab2aaf53f99/"><strong>Part 1</strong></a> of this blog we setup the infrastructure and other building blocks of our CICD pipeline — in this part we will show how we promote code within our managed environments via our CICD pipeline.</p><p>A managed environment is any <a href="https://cloud.google.com/composer">Cloud Composer</a> environment managed by a CICD service, Google Cloud Build, in this instance. For the purposes of this worked example, test and prod are our managed environments.</p><h4>CICD Workflow</h4><figure><img alt="CICD Workflow Google Cloud Build" src="https://cdn-images-1.medium.com/max/911/1*Jyux7v8EJzJGbWppW3kzkA.png" /><figcaption>CICD Workflow</figcaption></figure><p>Before we start implementation, let’s take a quick look at our CICD workflow:</p><ol><li>Devs have tested the DAG(s) in their own sandboxes and have committed changes to their dev branch</li><li>A PR is raised by our dev to merge code changes to a managed branch i.e. test or main in this example</li><li>The PR triggers a Cloud Build pipeline(<a href="https://github.com/kevenpinto/composer_cicd">/cloudbuild/pre-merge.yaml</a>) — This trigger was setup in <a href="https://medium.com/p/1ab2aaf53f99/">Part 1</a>. this pipeline performs linting checks, security checks and DAG tests</li><li>A member of the team reviews the code as well as checks the output of the pre-merge checks from our CICD pipeline</li><li>The Reviewer merges the PR if all checks have passed in the pipeline and the changes are in line with expectations</li><li>The merge triggers our second Cloud Build Pipeline (<a href="https://github.com/kevenpinto/composer_cicd">/cloudbuild/on-merge.yaml</a>), this pipeline deploys our code to a managed environment, the environment the code is deployed depends on our base branch and its associated environment (/config/env_mapper.txt)</li></ol><p><em>Please note that Steps 6.x will only run in case the reviewer Approves and Merges the code to the Base Branch</em></p><h4>Push Changes to the test environment</h4><p>In <a href="https://medium.com/p/1ab2aaf53f99/">Part 1</a>, we left off with a successful deployment of our DAG to the dev environment, In this part, we pick up from there and push our changes to the test environment. For this to happen, we first need to raise a PR in GitHub, the PR will involve merging changes from the cicdhead branch to the test base branch.</p><figure><img alt="Git Hub Pull Request trigger Google Cloud Build CICD" src="https://cdn-images-1.medium.com/max/1024/1*jd9Vhragt-NWH8cC6IWGow.png" /><figcaption>Pull Request</figcaption></figure><p>Once we click the ‘Create pull request’ button, we are presented with a new web page, Our pre-merge pipeline runs at the bottom of this page and looks like the screenshot below.</p><figure><img alt="CICD, Cloud Build, Pull Request, Git Hub PR" src="https://cdn-images-1.medium.com/max/1024/1*2a5b78cTWMSfmO7ibOK4Yg.png" /><figcaption>Cloud Build Trigger</figcaption></figure><p>Clicking on the Details link will present you with a web page showing a summary of the build, Clicking on the Build Reference number should take you to Google Cloud Build Screen in your Google Cloud Console.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1tR2aDPBJyeVO5Ac4tZrmA.png" /><figcaption>Cloud Build History</figcaption></figure><p>As we can see in the Screenshot above, Cloud Build shows us that all our pre-merge checks have been completed successfully.</p><p>Our Pre-Merge Tasks performs the following:</p><ul><li>Linting (Flake8)</li><li>Pre Commit Checks (see .pre-commit-config.yaml)</li><li>Dag Integrity Tests — Same as the one we ran for dev in Part 1</li><li>Testing dags using the composer env (?)</li></ul><p><strong>What is testing dags using the composer env?</strong></p><p>In this step we make use of the /data folder of Composer Dag Bucket to test our DAGs on the actual composer env. This is the best way to dress rehearse our DAG code on an environment it will be eventually deployed to without disrupting the environment.</p><p>We first create a sub folder using the<em> commit SHA</em> of the git branch and then copy our dags to this Sub-Folder. As a final step we run the list dags command. If the DAGs are error free one should see an output in Cloud Build similar to the Screen shot below. Kindly refer to <a href="https://cloud.google.com/composer/docs/how-to/using/testing-dags#airflow-2_3">this</a> article from Google for more info on this subject.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7AQLWk1kaR9hdHssOcmfiw.png" /><figcaption>Cloud Build Output</figcaption></figure><p>The steps for our pre-merge pipeline are stored in the file <a href="https://github.com/kevenpinto/composer_cicd/blob/main/cloudbuild/pre-merge.yaml">/cloudbuild/pre-merge.yaml</a>. I’d encourage you’ll to go through this code.</p><h4>Merge the PR</h4><figure><img alt="Git Hub Pull Request trigger Google Cloud Build CICDGit Hub Pull Request Checks" src="https://cdn-images-1.medium.com/max/1024/1*5cDKxNDnprV9KOsL_1n_oQ.png" /><figcaption>Merge PR</figcaption></figure><p>On clicking our ‘Squash and Merge’ or any of the Merge Options our merge pipeline<a href="https://github.com/kevenpinto/composer_cicd/blob/main/cloudbuild/on-merge.yaml">(</a><a href="https://github.com/kevenpinto/composer_cicd/blob/main/cloudbuild/on-merge.yaml">cloudbuild/on-merge.yaml)</a> will get triggered.</p><figure><img alt="Google Cloud Build Trigger" src="https://cdn-images-1.medium.com/max/1024/1*qVZGkK9XwiUeYRHCsC9M0w.png" /><figcaption>On-Merge Trigger Cloud Build</figcaption></figure><p>This pipeline will copy our DAGs to the test environment. You should now be able to see your DAGs in test composer environment</p><figure><img alt="Cloud Composer Test Environment" src="https://cdn-images-1.medium.com/max/1024/1*Vx2A8gcm1UMgQ_kqvx-kDA.png" /><figcaption>Test Composer DAGs</figcaption></figure><h4>Pushing Changes to the prod environment</h4><figure><img alt="Git Hub Pull Request trigger Google Cloud Build CICD" src="https://cdn-images-1.medium.com/max/1024/1*-QU1571oswOAhDkzMmL3iQ.png" /><figcaption>Git Hub PR</figcaption></figure><p>For pushing your changes to the prod env raise a PR in GitHub once again. this time keep the test branch as the head branch and the main branch as the base branch. I leave this final deployment cycle for the reader to try out for themself.</p><h4>Deployments to Prod</h4><p>This tutorial has shown how one can seamlessly deploy to prod, however, <em>we would like to </em><strong><em>discourage</em></strong><em> an automatic deployment to prod</em>. The only Cloud Build automation in prod we’d suggest is the pre-merge pipeline. If there is however a business need to automate deployment of DAGs to prod via Cloud Build, i’d suggest adding an approval stage to your CICD trigger. See <a href="https://cloud.google.com/build/docs/automating-builds/create-manage-triggers">this</a> article for more info on setting up the same.</p><h4>Clean Up</h4><p>Please run make cleanup , this will clean up the 4 projects that were created as part of this tutorial.</p><p>Thanks for persevering with this long blog and following along!</p><h4>References</h4><ul><li><a href="https://github.com/kevenpinto/composer_cicd">Demo Code Repo</a></li><li><a href="https://cloud.google.com/build/docs/configuring-builds/substitute-variable-values">Cloud Build</a></li><li><a href="https://cloud.google.com/composer/docs/dag-cicd-integration-guide">Cloud Build CICD</a></li><li><a href="https://cloud.google.com/build/docs/automating-builds/create-manage-triggers">Managed Triggers</a></li><li><a href="https://cloud.google.com/composer/docs/how-to/using/testing-dags#airflow-2_3">DAG testing</a></li></ul><h4>Makefile Help</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OeFeQ-7XuyiMWGJxUz7VYg.png" /><figcaption>Makefile Help</figcaption></figure><p><strong>About CTS</strong></p><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><p>Disclaimer: This is to inform readers that the views, thoughts, and opinions expressed in the text belong solely to the author.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2d0625ea44b0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/a-centralised-approach-to-cicd-of-dags-on-google-cloud-composer-with-google-cloud-build-part-2-2d0625ea44b0">A Centralised Approach to CICD of DAGs on Google Cloud Composer with Google Cloud Build — Part 2</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Centralised Approach to CICD of DAGs on Google Cloud Composer with Google Cloud Build — Part 1]]></title>
            <link>https://medium.com/qodea/a-centralised-approach-to-cicd-of-dags-on-google-cloud-composer-with-google-cloud-build-part-1-1ab2aaf53f99?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/1ab2aaf53f99</guid>
            <category><![CDATA[google-cloud]]></category>
            <category><![CDATA[directed-acyclic-graph]]></category>
            <category><![CDATA[cloud-build]]></category>
            <category><![CDATA[google-cloud-composer]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Thu, 20 Oct 2022 14:04:52 GMT</pubDate>
            <atom:updated>2022-11-01T07:58:32.366Z</atom:updated>
            <content:encoded><![CDATA[<h3>A Centralised Approach to CICD of DAGs on Google Cloud Composer with Google Cloud Build — Part 1</h3><p>In this 2-part Blog, we shall look at how we implemented CICD for Directed Acyclic Graphs (DAGs) on Google Cloud Composer using Google Cloud Build as our CICD platform.</p><ul><li>In Part 1 (this part), we set the basic building blocks in place to allow us to do a multi project CICD build. We end this part with a successful deployment of our DAG in dev</li><li>In <a href="https://medium.com/p/2d0625ea44b0/">Part 2</a>, we will move our DAG from dev to test and then to prod using a Cloud Build Pipeline</li></ul><figure><img alt="CICD using Cloud Build, Composer" src="https://cdn-images-1.medium.com/max/810/1*hUmx974DaG71qrG5SZk8Og.png" /><figcaption>CICD</figcaption></figure><p>CICD is a critical building block in promoting a GitOps culture within an Organisation/team. DAGs tracked by a Git repository and coupled with consistent processes and tooling; ensures that engineers focus on the design and development of the DAGs rather than delivering these changes across environments. Before we dive into CICD implementation, let’s take a look at some popular CICD patterns.</p><h3><strong>Pattern 1</strong></h3><p>A very common Pattern to <a href="https://www.redhat.com/en/topics/devops/what-is-ci-cd">CICD</a> is as shown in the Diagram below, in this pattern each Google Cloud Project has a dedicated <a href="https://cloud.google.com/build">Cloud Build</a> service. Each of these Cloud Build services in turn independently track changes to a git based repo and trigger one or more CICD pipelines within their project. This is a widely used pattern as each cloud build service manages a single dedicated project and failures to a Cloud Build service within a project do not impact any other environments.</p><figure><img alt="CICD Architecture — Pattern 1" src="https://cdn-images-1.medium.com/max/1024/1*UqNoPGJB-25arULYtr-LAw.png" /><figcaption>CICD Pattern1</figcaption></figure><h3><strong>Pattern 2</strong></h3><p>Pattern2 (diagram below) is a c<strong>entralised</strong> approach to CICD. In this Pattern, a dedicated project is created for the sole purpose of validating and distributing changes across all the other environments in the estate. This project runs the minimum number of cloud services necessary to support validation and deployment of build artefacts. This pattern usually divides opinion, so in order to be neutral, let’s state some pros and cons of this approach.</p><p>The biggest con to this approach is that this deployment project is a Single Point of Failure to the CICD pipeline.</p><p><em>If u can look beyond this con, read on. We will be walking you through the implementation of this pattern… Sorry!, should have said that earlier.</em></p><p>Some of the benefits of this approach are:</p><ul><li>Centrally located Cloud Build Logs without any need for additional effort/configuration.</li><li>Consolidation of Cloud Services to support validation and distribution of Artefacts, for example, Artefact Registry</li><li>Each new Repo needs to be Authenticated with only a Single Cloud Project via Cloud Build GitHub App</li><li>New Cloud Projects(e.g. Research/PoC) can be added or removed without the need to setup Cloud Build and Individual pipelines in them. A new deployment endpoint can be managed via config.</li></ul><figure><img alt="CICD Architecture" src="https://cdn-images-1.medium.com/max/1024/1*cL8q1dzx9-N1TKlMcMrmEg.png" /><figcaption>CICD Pattern 2</figcaption></figure><p>Now that we have introduced the two patterns, we’d like to demonstrate how we went about implementing Pattern2.</p><h3><strong>Resource Hierarchy</strong></h3><figure><img alt="Google Resource Heirarchy" src="https://cdn-images-1.medium.com/max/807/1*FZEJsPYoXiZI8xkN4FNKaQ.png" /><figcaption>Google Resource Hierarchy</figcaption></figure><p>All Google Cloud resources are organised hierarchically. An <em>Organisation</em> Resource is at the Top of this hierarchy, this is followed by <em>Folder</em> resources which are an additional, optional grouping mechanism between organisation resources and project resources, finally, a B<em>illing Account</em> is a cloud-level resource that defines who picks the tab for the cloud resources.</p><p>The reason we are talking about these resources is because our opinionated implementation is strictly based on this hierarchy. So if you want to follow along with this tutorial, please ensure you have the following two pieces of information:</p><ul><li>Folder Id</li><li>Billing Account Id</li></ul><p><em>Note: Free Tier users may not be able to follow along as they only have access to Project Resources.</em></p><h3><strong>Security</strong></h3><figure><img alt="Google IAM" src="https://cdn-images-1.medium.com/max/1002/1*gwbTrj_vRzC0hqpRyHgKTg.png" /><figcaption>CloudBuild IAM</figcaption></figure><p>As our Cloud build only runs in the Deployment Project, it needs access to the DAG buckets in the managed environments (test -&gt; prod). This is achieved by registering the Cloud Build service account of the deployment project as a <em>Member</em> in the other google projects.</p><p>When registering our Cloud Build SA in our other projects, we need to give it the following roles:</p><ul><li>roles/composer.environmentAndStorageObjectAdmin</li><li>roles/composer.worker</li></ul><p>For this tutorial, we don’t need to worry about this setup as it is automatically done for us by our terraform module.</p><p><em>For clarity, a managed environment is any Composer Environment managed by a CICD service — Google Cloud build in this instance. For the purposes of this worked example, test and prod are our managed environments.</em></p><p>Note: If you already have projects in place and are using a custom service account for Cloud Build, register the custom service account in your managed environments. You may need to either modify the Terraform code provided in this repo or do this manually.</p><p><strong>Prerequisites</strong></p><ul><li><a href="https://github.com/kevenpinto/composer_cicd">Fork the code from here</a> in your own Github Account and clone the code on your machine, <em>make all subsequent changes in your forked repo only.</em></li></ul><p>Ensure that you have the following setup on the machine that you will be executing the code:</p><ul><li>gcloud installed and authorised to your GCP Project</li><li>gsutil</li><li><a href="https://www.gnu.org/software/make/">make</a> build tool</li><li>Docker Desktop or equivalent container service like Rancher Desktop</li><li>git</li><li>Google user with rights to create Google projects under a specific folder and attach these projects to Billing Accounts</li></ul><p><a href="https://github.com/kevenpinto/composer_cicd"><strong>A bit about the sample code</strong></a></p><p>The <a href="https://github.com/kevenpinto/composer_cicd">sample code</a> will setup the following 4 GCP Projects for you under a specified folder:</p><ul><li>deployment</li><li>dev</li><li>test</li><li>prod</li></ul><p>The dev project has been added for completeness but will not benefit from a Cloud Build setup, this service is only for our managed environments i.e test and prod.</p><p>We then setup <em>build triggers</em> within the Cloud Build service in our newly created deployment project. These triggers track changes to our repo and deploy our DAGs to the appropriate environments.</p><p><strong><em>Why is dev not managed by Cloud Build, you ask ?</em></strong></p><ul><li>Need to deploy code without committing it to a branch (PoC/Learning Mode)</li><li>Faster feedback as opposed to a CICD build that could take 3–5 minutes</li><li>Learning Mode where one needs to check out a new feature of Airflow without the need for a full blown CICD pipeline.</li></ul><p>Later in this article, we do cover our dev deployment process via our Makefile, this method although manual, still enforces a small amount of rigour, you will find out more on this process once we reach that section.</p><h3>Project Layout</h3><pre>.<br>├── Makefile<br>├── Readme.md<br>├── cloudbuild<br>│   ├── Dockerfile<br>│   ├── on-merge.yaml<br>│   ├── pre-merge.yaml<br>│   ├── requirements.txt<br>│   └── run_integration_tests.sh<br>├── config<br>│   ├── env.config<br>│   ├── env_mapper.txt<br>│   ├── projects.yaml<br>│   └── repoconfig.yaml<br>├── dags<br>│   ├── __pycache__<br>│   │   ├── airflow_monitoring.cpython-39.pyc<br>│   │   └── good_dag_v1.cpython-39.pyc<br>│   ├── airflow_monitoring.py<br>│   ├── good_dag_v1.py<br>│   ├── scripts<br>│   ├── sql<br>│   └── utils<br>│       ├── __init__.py<br>│       ├── __pycache__<br>│       │   ├── __init__.cpython-39.pyc<br>│       │   └── cleanup.cpython-39.pyc<br>│       └── cleanup.py<br>├── info.txt<br>├── infra<br>│   ├── env_subst.sh<br>│   ├── modules<br>│   │   └── projects<br>│   │       ├── main.tf<br>│   │       └── variables.tf<br>│   ├── proj_subst.sh<br>│   ├── projects<br>│   │   ├── backend.tf<br>│   │   ├── main.tf<br>│   │   ├── provider.tf<br>│   │   ├── terraform.tfvars<br>│   │   └── variables.tf<br>│   ├── tf_utils.sh<br>│   └── triggers<br>│       ├── backend.tf<br>│       ├── main.tf<br>│       └── provider.tf<br>└── tests<br>    ├── __pycache__<br>    │   └── test_dags.cpython-39-pytest-7.1.2.pyc<br>    └── test_dags.py</pre><ul><li>Makefile : This file contains all the targets to automate build, test and run various tasks in this Tutorial. this file is our friend!</li><li>cloudbuild : This directory contains all our google cloud build files</li><li><strong>config</strong> : This directory contain config files that will need to be changed as per the readers environment</li><li>dags : This directory contain our DAGs, this is what gets deployed to Cloud Composer</li><li>infra : Terraform code to create projects, composer and cloud build triggers</li><li>tests : Our DAG integrity testing code lives here</li></ul><p><strong>Modify the Sample Code</strong></p><p>Start of first by creating a branch called cicd in your repo.</p><p>git checkout -b cicd</p><p>and then set the push to the correct upstream branch</p><pre>git push --set-upstream origin cicd</pre><p><em>Please make all changes in this branch, this branch will also be used to create a PR in later steps.</em></p><p>Next, run make init , After running this step, you will notice a <em>docker image called </em><strong><em>cicd</em></strong> being deployed to your machine, we will use this for airflow <em>DAG integrity testing(?!)</em> at a later stage and for our terraform deployments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sOCqgtXCG2vnr_KrgRWc1g.png" /></figure><p>Next, go to the <strong>config</strong> folder of the repo and modify file env.configto your own specific values, do not change the variable names, just the values please.</p><p>As mentioned before, we will be creating 4 new projects, so we need the names of these projects specified in here, in addition we also need the folder ID under which these projects need to be created and the billing account that these services will be billed to.</p><pre>TF_VAR_folder=&lt;CHANGEME&gt;, example 99999999999<br>TF_VAR_billing_account=&lt;CHANGEME&gt;, example XXXXXX-XXXXXX-XXXXXX<br>TF_VAR_location=&lt;CHANGEME&gt;, example, europe-west2<br>TF_VAR_deployment_project=&lt;CHANGEME&gt;, example, cicd-arequipa-deploy-<strong>917f1x</strong><br>TF_VAR_dev_project=&lt;CHANGEME&gt;, example, kevs-dev-project-<strong>917f1x</strong><br>TF_VAR_test_project=&lt;CHANGEME&gt;, example, kevs-test-project-<strong>917f1x</strong><br>TF_VAR_prod_project=&lt;CHANGEME&gt;, example, kevs-prod-project-<strong>917f1x</strong></pre><p><em>Note, Project Ids need to be unique and in lower case, be sure to add a short UUID as shown in bold in code snippet above to make sure you don’t hit an error.</em></p><p>While in this folder, please modify the file repoconfig.yaml change the name of the owner to your own github user. there are 2 instances of this entry in the file, make sure to change both.</p><pre>github:<br>  <strong>owner</strong>: kev-pinto-cts --&gt; Change this</pre><p><strong>Setup the deployment Project</strong></p><p>Pattern 2 requires a a dedicated deployment project, so that’s what we are going to do. Please go to the root folder of the repo and type the command make bootstrap . This will create a project with the name you have supplied in the config/env.config file. ensure this project exists before proceeding any further.</p><p>Note, You will be prompted by Google Cloud to select the correct account for Authorisation in the Cloud, Please select the correct account and then click <em>Allow</em> on the next Screen.</p><p><strong>Setup Artifact Registry in the deployment Project</strong></p><p>Type the command make repo in the root folder of the repo. This should create a docker repo called <strong>airflow-test-container </strong>in the deployment project<strong>.</strong></p><p>We will be unit testing our DAGs as part of our pre-merge process. In order to do the same we need a container with the Airflow Library installed. This container will reside in this new repo we have just created.</p><p><strong>Setup the dev, test and prod Projects</strong></p><p>Type the command make projects in the root folder of the repo. This will do the following:</p><ul><li>Create the dev, test and prod projects using the names provided in the config\env.config file</li><li>Enable the required set of APIs in these projects</li><li>Register the Deployment Projects Cloud Build SA as a principal in each of these projects with the <a href="https://cloud.google.com/composer/docs/how-to/access-control#composer.environmentAndStorageObjectAdmin"><em>cloud composer Role (Environment and Storage Object Administrator &amp; Composer Worker)</em></a>.</li></ul><p><strong>Install Cloud Composer</strong></p><p>Type the command make composer in the root folder of the repo. This will do the following:</p><ul><li>Install Google Cloud Composer in each of the Projects created above, the name of the Composer env will be the same as the project.</li><li>Generate a text file <strong>(<em>config/env_mapper.txt</em>)</strong> that needs to be <strong><em>modified</em></strong> in the next step.</li></ul><p>This step will take a while (50 mins) so i suggest catching up on all pending work/try origami/walk the dog… anything but stare at the Terraform progress bar.</p><p>Once terraform completes, please check for all of the steps and artefacts mentioned above proceeding.</p><p><em>Note: Cloud Composer 2 is not set up in all Regions yet, so if you get Terraform Timeouts, That could be the likely cause, please check </em><a href="https://cloud.google.com/about/locations#europe"><em>here</em></a><em> region support.</em></p><p><strong>Branch to Env Mapping</strong></p><p>The diagram below shows that we have mandated a Branch to Env mapping. This means that a Composer Env will receive code from a particular git branch only. As part of our SDLC document we have also mandated the order in which code is promoted from one branch to another. This prescribed path ensures that by the time the code reaches the main branch, it has been tested across the other env’s and has a higher level of stability (he hopes).</p><figure><img alt="Google Cloud Build CICD" src="https://cdn-images-1.medium.com/max/909/1*XaDmEFk1_31r63JLQEEefw.png" /><figcaption>Branch to Env Mapping</figcaption></figure><p><strong><em>So where is the Branch to Env mapping config Stored?</em></strong></p><p>In the last step we stated that terraform will generate a text file called <strong>config/env_mapper.txt</strong>. it’s now time to edit it.</p><pre>kevs-dev-project~gs://europe-west2-kevs-dev-project-07de6/dags<br>kevs-test-project<strong>~</strong>gs://europe-west2-kevs-test-project--07de67c8-bucket/dags<br>kevs-prod-project<strong>~</strong>gs://europe-west2-kevs-prod-project--29e6ee85-bucket/dags</pre><p>This file contains a mapping of the composer env to its corresponding DAG storage bucket separated by a <strong>~</strong>. It is here that we need to add our branch mapping. We will also be <strong>removing our dev</strong> env entry as we don’t need this to be done via cloud build. below is what your file should look like after modifying it. The project and bucket names will be the different, of course !</p><pre>kevs-test-project-917f1x~gs://europe-west2-kevs-test-project--07de67c8-bucket/dags~<strong>test</strong><br>kevs-prod-project-917f1x<strong>~</strong>gs://europe-west2-kevs-prod-project--29e6ee85-bucket/dags~<strong>main</strong></pre><p>The mapping above states that the test project will map to the test branch and prod will map to main. Please create a branch called testfrom the Github console/CLI based on main as we will need this later.</p><p>Although, the diagram shows a 1–1 mapping between a Google project and a git branch, there is no reason why a 1-n cannot be supported, just add more projects to the same branch, one per line. like shown below.</p><p>This is what we meant, when we said, <em>“New Cloud Projects(Research/PoC) can be added or removed…via config”</em></p><pre>kevs-test-project-917f1x<strong>~</strong>gs://europe-west2-kevs-test-project--07de67c8-bucket/dags<strong>~test</strong></pre><pre><strong>kevs-new-project-917f1x~gs://europe-west2-kevs-test-project--07de67c8-bucket/dags~test</strong></pre><pre>kevs-prod-project-917f1x<strong>~</strong>gs://europe-west2-kevs-prod-project--29e6ee85-bucket/dags<strong>~main</strong></pre><p><strong>Connect the Repo to the Deployment Project</strong></p><figure><img alt="Google Cloud Build Connect Repository" src="https://cdn-images-1.medium.com/max/1024/1*ldaZbK2Pdp16sNTymbAUCw.png" /><figcaption>Cloud Build Connect Repository</figcaption></figure><p>Log in to the Google cloud console and <a href="https://cloud.google.com/build/docs/automating-builds/github/connect-repo-github#connecting_a_github_repository">follow these steps</a> to connect to your repository. Please ensure sure that you establish this connection while you are in the Deployment Project.</p><p><em>Do not create any triggers from the console, we will do the same via Terraform in the next step.</em></p><p><strong>Setup the Triggers</strong></p><p>Assuming that you have already modified the file config/repoconfig.yaml , please type the command make triggers in the root folder of the repo. You should now see <strong>2 triggers in </strong>the Google Console as shown below. You will find this screen in your deployment project under Cloud Build-&gt;Triggers.</p><figure><img alt="Google Cloud Build Triggers" src="https://cdn-images-1.medium.com/max/1024/1*48n9SK55DNbVoS75AYdH0w.png" /><figcaption>Google Cloud Build Triggers</figcaption></figure><p><strong><em>Why do we have 2 CICD triggers?</em></strong></p><p>We may choose to <em>Cancel a Pull Request</em> after Raising a PR, in this case, the DAGs must not be deployed. so to deal with these scenarios we have 2 triggers:</p><ul><li>Pre-Merge trigger → This CICD pipeline executes on a Pull Request and runs checks for coding standards, security, DAG integrity and integration tests</li><li>On-Merge Trigger → This CICD pipeline executes when the PR Reviewer decides to merge the request, this pipeline is responsible for the actual deployment of the DAGs to Cloud Composer.</li></ul><h3><strong>CICD in action (dev first)</strong></h3><p>Let’s start by validating and deploying <a href="https://github.com/kevenpinto/composer_cicd">our code</a> to our <strong>dev</strong> environment first. we have a single DAG in our repo called <strong>good_dag_v1.py. </strong>Although this isn’t CICD via Cloud Build in action, this is still a crucial step of our the SDLC process.</p><p>Below is our DAG code, it’s a syntactically correct DAG, however, where’s the fun in that! …. so, let’s modify this DAG to <strong><em>inject an error</em></strong>, let’s introduce a <strong><em>cyclic error, </em></strong>this is achieved by adding the lines highlighted in <strong>bold</strong> below.</p><p>Type the command make deploy in the root folder of the repo and observe the results…..</p><pre>from pathlib import Path<br><br># Third Party Imports<br>from airflow.decorators import dag<br>from airflow.utils.dates import days_ago<br>from airflow.operators.empty import EmptyOperator<br>from airflow.operators.bash import BashOperator<br><br>default_args = {<br>    &#39;start_date&#39;: days_ago(1),<br>    &#39;catchup&#39;: False,<br>    &#39;max_active_runs&#39;: 1,<br>    &#39;tags&#39;: [&quot;BAU&quot;],<br>    &#39;retries&#39;: 0<br>}<br><br><br>@dag(dag_id=Path(__file__).stem,<br>     schedule_interval=&quot;@once&quot;,<br>     description=&quot;DAG CICD Demo&quot;,<br>     default_args=default_args)<br>def dag_main():<br>    start = EmptyOperator(task_id=&#39;start&#39;)<br><br>    print_var = BashOperator(task_id=&#39;print_var&#39;,<br>                             bash_command=&#39;echo &quot;Hello&quot;&#39;)<br><br>    end = EmptyOperator(task_id=&quot;end&quot;)<br><br>    start &gt;&gt; print_var &gt;&gt; end <strong>&gt;&gt; start</strong><br><br><br># Invoke DAG<br>dag = dag_main()</pre><p>As we can see from an excerpt of the error message below, the DAG failed an integrity test due to a cyclic check fail and as a result the deployment to the Dev env was abandoned. <em>So although we deploy manually to dev, we still ensure that we maintain some form of quality control by ensuring we run the DAG thru a generic test case</em>.</p><pre><br>======================================================================================================= short test summary info ========================================================================================================<br>FAILED tests/test_dags.py::test_dag_integrity[/workspace_stg/tests/../dags/<strong>good_dag_v1.py</strong>] - <strong>airflow.exceptions.AirflowDagCycleException: Cycle detected in DAG</strong>. Faulty task: end<br>================================================================================================ 1 failed, 1 passed, 1 warning in 0.26s ================================================================================================</pre><h4><strong><em>So what caused this error to be highlighted?</em></strong></h4><p><strong><em>… a bit of a diversion but this topic is worth a visit.</em></strong></p><p>The make deploytarget called a generic <strong><em>DAG Integrity Test Case</em></strong> (via pytest). This test case checks our DAGs for many common errors such as:</p><ul><li>imports</li><li>cyclic checks</li><li>common syntax errors</li></ul><p>I first came across DAG integrity tests in this excellent <a href="https://medium.com/wbaa/datas-inferno-7-circles-of-data-testing-hell-with-airflow-cef4adff58d8">blog</a>(See Circle 1) written by the engineers at ING . I have since modified the code very slightly to work with Airflow 2. The code basically looks at the /dag folder for DAGs and among other things, uses the Airflow Library routine check_cycle to catch any cyclic checks. The code runs within the container that we setup as part of our bootstrap process and makes use of the Airflow Library version 2.3.3 (configurable from within the Dockerfile).</p><p><em>Note: Please run </em><em>make initin case you decide to change the AIRFLOW_VERSION to a different version to that specified in the Dockerfile to rebuild the container</em>.</p><p>One can also run the target make tests to run this test explicitly and see the outputs. I’d encourage you to take a look at this code. it is pretty self explanatory and sits in the repo under tests/tests.dag.</p><p>Now, <em>please remove the injected error in the DAG</em> and run make deploy once more</p><pre>===================================================================================================== <strong>2 passed, 1 warning in 0.22s </strong>=====================================================================================================<br>Building synchronization state...<br>If you experience problems with multiprocessing on MacOS, they might be related to <a href="https://bugs.python.org/issue33725">https://bugs.python.org/issue33725</a>. You can disable multiprocessing by editing your .boto config or by adding the following flag to your command: `-o &quot;GSUtil:parallel_process_count=1&quot;`. Note that multithreading is still available even if you disable multiprocessing.</pre><pre>Starting synchronization...<br>If you experience problems with multiprocessing on MacOS, they might be related to <a href="https://bugs.python.org/issue33725">https://bugs.python.org/issue33725</a>. You can disable multiprocessing by editing your .boto config or by adding the following flag to your command: `-o &quot;GSUtil:parallel_process_count=1&quot;`. Note that multithreading is still available even if you disable multiprocessing.</pre><pre>Copying file://dags/__pycache__/airflow_monitoring.cpython-39.pyc [Content-Type=application/x-python-code]...<br>Copying file://dags/.airflowignore [Content-Type=application/octet-stream]...<br>Copying file://dags/__pycache__/good_dag_v1.cpython-39.pyc [Content-Type=application/x-python-code]...<br>Copying file://dags/good_dag_v1.py [Content-Type=text/x-python]...              <br>Copying file://dags/.DS_Store [Content-Type=application/octet-stream]...<br>Copying file://dags/utils/cleanup.py [Content-Type=text/x-python]...            <br>Copying file://dags/utils/__init__.py [Content-Type=text/x-python]...           <br>Copying file://dags/utils/__pycache__/__init__.cpython-39.pyc [Content-Type=application/x-python-code]...<br>Copying file://dags/utils/__pycache__/cleanup.cpython-39.pyc [Content-Type=application/x-python-code]...<br>/ [9/9 files][  9.6 KiB/  9.6 KiB] 100% Done                                    <br>Operation completed over 9 objects/9.6 KiB.</pre><p>That’s it for this week, we have successfully deployed a DAG to our dev env. To view this DAG open composer deployed in the Dev Project and view the DAG in the Airflow UI. You should see something resembling the Screenshot below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nCdmi8J73GhpYioFo-pQ8A.png" /><figcaption>Airflow UI</figcaption></figure><h3>Summary</h3><p>Congrats, we have deployed our DAG to the dev project.</p><p>Although we have still not played with Cloud Build and inspected our CICD pipeline, we have setup the following building blocks:</p><ul><li>deployment project</li><li>dev, test and prod projects</li><li>Cloud Composer Environments</li><li>IAM Setup</li><li>Cloud Build triggers</li></ul><p>In <a href="https://medium.com/p/2d0625ea44b0/">Part 2</a>, we will move the code from dev to test and then to prod using the Cloud Build CICD pipelines. Thanks for following along and see you soon!.</p><p>Not forgetting to state a Big Thanks(!) to <a href="https://medium.com/u/f5c7dd828b3b">Vitalii Karniushin</a> for helping test and debug the demo code!</p><p><strong>About CTS</strong></p><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><h4>Makefile Help</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OeFeQ-7XuyiMWGJxUz7VYg.png" /><figcaption>Makefile Help</figcaption></figure><p>Disclaimer: This is to inform readers that the views, thoughts, and opinions expressed in the text belong solely to the author.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1ab2aaf53f99" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/a-centralised-approach-to-cicd-of-dags-on-google-cloud-composer-with-google-cloud-build-part-1-1ab2aaf53f99">A Centralised Approach to CICD of DAGs on Google Cloud Composer with Google Cloud Build — Part 1</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build, Test and Deploy Dataflow Flex Templates in Google Cloud]]></title>
            <link>https://medium.com/qodea/building-and-testing-dataflow-flex-templates-80ef6d1887c6?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/80ef6d1887c6</guid>
            <category><![CDATA[apache-beam]]></category>
            <category><![CDATA[dataflow-flex-template]]></category>
            <category><![CDATA[ci-cd-pipeline]]></category>
            <category><![CDATA[dataflow]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Thu, 11 Aug 2022 21:51:07 GMT</pubDate>
            <atom:updated>2022-09-15T14:09:43.484Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/698/1*mt4T-oLewUl56HB6CtR3Gw.png" /><figcaption>Flex Template</figcaption></figure><p><a href="https://cloud.google.com/dataflow/docs/guides/templates/using-flex-templates">Dataflow Flex Templates</a> are a great way to package and distribute your DataFlow Pipeline. That being said, many engineers trying to implement flex templates for the first time, face quite a few challenges. This tutorial and accompanying code should (hopefully) provide you with a standard way to Deploy and Test your Pipelines.</p><p><strong>Caveat Lector !</strong>, this is not a blog on when to use a Flex Template or its advantages, for that, allow me to point you to <a href="https://cloud.google.com/blog/products/data-analytics/create-templates-from-any-dataflow-pipeline">this excellent blog written by 2 Google Engineers.</a></p><p>The main focus of this tutorial is the <em>Testing of the Pipeline Container</em> to ensure it is fit for Production, however explaining the testing approach without first building a template did not seem as an effective approach. So let’s begin !</p><h4>Project Layout</h4><p>Let us Start by <a href="https://github.com/kevenpinto/beam_flex_demo">downloading the Code from here</a>. I’d suggest you download and run this in your Cloud Shell (U will need a GCP Project) so that you do not face any OS challenges.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/524/1*jHvAcptZceXty98NqXyWnA.png" /><figcaption>Project Layout</figcaption></figure><ul><li>Dockerfile : This is the spec for the container in which our pipeline code get packaged, it uses <a href="https://cloud.google.com/dataflow/docs/reference/flex-templates-base-images">Google’s Data Flex Template Base Images.</a></li><li>.dockerignore : Controls which files and folders get copied the container</li><li>Makefile : This contains all the targets to automate build, test and run. this file is our friend!</li><li>main.py : This is your Driver Program the main entry point to your Pipeline</li><li>user_do_fns : This package contains all business logic for user defined DoFns</li><li>utils : A list of common routines invoked in the pipeline via beam.Map()</li><li>setup.py : A standard python setup file that allows for the deployment of this pipeline as a package on the container</li><li>requirements.txt : Add any additional packages required to be installed on the Container here or leave blank like we have</li><li>beam_flex_demo : This directory contains all the pipeline code</li><li>beam-flex-demo-metadata: This JSON file contains Metadata about the pipeline and validation rules for its parameters — <a href="https://cloud.google.com/dataflow/docs/guides/templates/using-flex-templates#metadataparameters">for more info refer to this</a>.</li></ul><h4>A bit about the Pipeline</h4><p>The pipeline self generates some data and writes it to a GCS Bucket. The name of the GCS bucket is provided by the user as a Runtime input param to the Pipeline.</p><p>The Code though basic, does provide an exemplar of how one goes about developing code in a modular manner with DoFns and functions in separate Packages &amp; Modules</p><h4>Pipeline Setup — Do this before anything else !</h4><p><strong>Edit the first 2 lines of the </strong><strong>Makefile</strong></p><p>GCP_PROJECT ?= &lt;CHANGEME to your Project ID&gt;<br> GCP_REGION ?= &lt;CHANGEME to your region for Ex: europe-west2&gt;</p><p>Once done, run the following make target from the root folder of the repo make init — <em>Don’t forget to respond with a ‘y’ to any prompts</em>.</p><p>This will do the following:</p><ul><li><a href="https://cloud.google.com/vpc/docs/configure-private-google-access"><em>Configure Private Google Access</em></a><em> </em>in your region.</li><li>Enable the Dataflow Service</li><li>Create a bucket that will store our Flex template Spec File — the name of the bucket will be &lt;GCP_PROJECT_ID&gt;-dataflow-&lt;PROJECT_NUMBER&gt;</li><li>Create a Docker Repo in Artifact Registry to house our dataflow flex template image — The name of the repo will be beam-flex-demo</li></ul><p>Kindly ensure that the assets(Bucket and Artifact Registry Repo) mentioned above exist in your project before proceeding.</p><h4>Build the Template</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/758/1*Il53h80fxSRxw_6T131H-w.png" /></figure><p>In the Root Folder of the Repo Type the Following Command<br>make template<br> <br> This will now do 2 things:</p><ul><li>Use the Dockerfile in this Repo to Build a Docker container that contains our code and Push it to the Artifact Registry Repo we created as part of Pipeline Setup</li><li>Deploy the Template Spec File to the GCS Bucket we created as part of Pipeline Setup</li></ul><p>Please check Artifact Registry for a Container and your GCS bucket for a template spec file before proceeding any further.</p><p><strong>Dockerfile</strong></p><p>Let&#39;s take a closer look at the Dockerfile spec as this is where quite a few of us trip up!.<br>There are a few things i would like to point out here:</p><ul><li>Use Google’s Flex Template as your base as far as possible, it keeps the setup simpler — This is not to say that one cannot use their own container, <a href="https://cloud.google.com/dataflow/docs/guides/using-custom-containers">more details here</a>.</li><li>Make sure you set the following two env vars correctly:<br> → FLEX_TEMPLATE_PYTHON_PY_FILE : Pipleine driver filemain.py in our case<br> → FLEX_TEMPLATE_PYTHON_SETUP_FILE : Location of our setup file</li><li>Always pin installs to a version — we are locking apache-beam to 2.40.0</li><li>Don’t forget to install the package on the Container (python setup.py install) —<a href="https://cloud.google.com/dataflow/docs/guides/templates/using-flex-templates#metadataparameters"> <em>Google quick start guide</em></a><em> does not mention this step but it kept failing without this step , Happy to be corrected on this.</em></li></ul><pre>ARG <em>TAG</em>=latest<br>FROM gcr.io/dataflow-templates-base/python39-template-launcher-base:${<em>TAG</em>}<br><br>ARG <em>WORKDIR</em>=/opt/dataflow<br>RUN mkdir -p ${<em>WORKDIR</em>}<br>WORKDIR ${<em>WORKDIR</em>}<br><br>ARG <em>TEMPLATE_NAME</em>=beam_flex_demo<br>COPY . ${<em>WORKDIR</em>}/<br><br><strong>ENV <em>FLEX_TEMPLATE_PYTHON_PY_FILE</em></strong>=${<em>WORKDIR</em>}/${<em>TEMPLATE_NAME</em>}/main.py<br><strong>ENV <em>FLEX_TEMPLATE_PYTHON_SETUP_FILE</em></strong>=${<em>WORKDIR</em>}/setup.py<br><br># Install apache-beam and other dependencies to launch the pipeline<br>RUN apt-get update \<br>    &amp;&amp; pip install --no-cache-dir --upgrade pip \<br>    &amp;&amp; pip install &#39;<strong>apache-beam[gcp]==2.40.0</strong>&#39; \<br>    &amp;&amp; pip install -U -r ${<em>WORKDIR</em>}/requirements.txt<br><br><strong>RUN python setup.py install</strong><br>ENV <em>PIP_NO_DEPS</em>=True</pre><p><strong>Build Speed</strong></p><p>We use <a href="https://cloud.google.com/blog/products/application-development/build-containers-faster-with-cloud-build-with-kaniko">Kaniko</a> to speed up our docker build-This means our first build will be a bit slow as the cache layers are being persisted to Artifact Registry but one should observe faster subsequent builds.</p><h4>Testing the Container</h4><p>The Challenge with deploying a container was ensuring that the container was valid. We wanted to add some checks to ensure we don’t catch these issues at runtime, we also wanted to ensure that these checks are part of our CICD pipeline going forward.</p><p>As a minimum we wanted checks for the following on the container:</p><ul><li>The 2 Env Vars FLEX_TEMPLATE_PYTHON_PY_FILE &amp; FLEX_TEMPLATE_PYTHON_SETUP_FILE exist</li><li>These above 2 variables reference actual files</li><li>The pipeline has been successfully deployed as a package and can be imported</li><li>A sub-package can be imported in python</li></ul><p>The following tests cover all of the checks mentioned above. These are pretty self explanatory, kindly go through the same in the <strong>Makefile (test-template).</strong></p><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &#39;env|grep -q &quot;FLEX_TEMPLATE_PYTHON_PY_FILE&quot; &amp;&amp; echo ✓&#39;</pre><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &#39;env|grep -q &quot;FLEX_TEMPLATE_PYTHON_PY_FILE&quot; &amp;&amp; echo ✓&#39;</pre><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &quot;/usr/bin/test -f ${FLEX_TEMPLATE_PYTHON_PY_FILE} &amp;&amp; echo ✓&quot;</pre><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &#39;test -f ${FLEX_TEMPLATE_PYTHON_SETUP_FILE} &amp;&amp; echo ✓&#39;</pre><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &#39;python -c &quot;import beam_flex_demo&quot; &amp;&amp; echo ✓&#39;</pre><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &#39;python -c &quot;from beam_flex_demo.utils.common_functions import split_element&quot; &amp;&amp; echo ✓&#39;</pre><pre>docker run --rm --entrypoint /bin/bash ${TEMPLATE_IMAGE} -c &quot;python ${FLEX_TEMPLATE_PYTHON_PY_FILE} <strong>--runner DirectRunner</strong> --output output.txt &amp;&amp; cat output.txt*&quot;</pre><p>In order to run this type make test-template at the root folder of your repo. The output should look like the image below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_htRfCuvA_nTsaf462vBxw.png" /></figure><p>Also, note that the last test actually runs the pipeline locally using the direct runner. This is a full E2E test before moving this to deployment phase.</p><h4>Run the Pipeline</h4><p><strong>CLI</strong></p><p>Type make run at the root folder of your repo.<br>Log in to Dataflow and view the Pipeline, Don’t Panic if your job stays at Queued State for 4–6 minutes, this is normal as Dataflow is provisioning resources and performing pre execution checks. You should see a Dataflow Job named beam-flex-demo-(date +%Y%m%dt%H%M%S)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W1hhiqARXuHYhbzYp9zzrg.png" /><figcaption>Dataflow Job Summary</figcaption></figure><p>Once the Job shows a Succeeded status, you will be able to locate your file under the following GCS location &lt;project_id&gt;-dataflow-&lt;project_id&gt;/out/beam-flex-demo-YYYYMMDDtHHmmSS/output-*</p><p><strong>Cloud Console</strong></p><p>From the Google Cloud console, select Dataflow →Jobs →Create Job From Template → under templates, select Custom → then Select the spec file from the GCS bucket(&lt;project_id&gt;-dataflow-&lt;project-number&gt;/templates/0.1.0/beam-flex-demo.json) , Select the bucket to store the output and then click on Run Job</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DGLIm1trvx7bo9PInijmMw.png" /><figcaption>Flex Template UI</figcaption></figure><h4>Troubleshooting</h4><p>Ideally, if you have followed all of the above steps, the need should not arise. But in the unfortunate event this does occur, kindly refer to <a href="https://cloud.google.com/dataflow/docs/guides/troubleshoot-templates">this</a> guide.</p><p>The most common reason I have found is that Private Google Access has not been configured, please ensure it is set or set it for the correct subnet and VPN if you use a specific VPN.</p><p>If you run into this error like one of my colleagues during make test-template</p><p><em>denied: Permission “artifactregistry.repositories.downloadArtifacts” denied on resource</em></p><p>Try adding yourself manually to the Repo with the command below:</p><pre>gcloud artifacts repositories add-iam-policy-binding beam-flex-demo --location=&lt;YOUR REGION&gt; --member=user:<a href="/service/mailto:kev.pinto@cts.co">&lt;</a>YOUR USER NAME&gt; --role=roles/artifactregistry.reader --project &lt;YOUR GCP PROJECT&gt;</pre><p>That’s it!, we have built, tested and executed a flex template. I do hope you try this tutorial. I’d also like to hear if you’ll have added new test cases (Hint! check for standard packages like apache-beam).</p><p>Finally, Big thanks to <a href="https://medium.com/u/9922c9c37989">Lee Doolan</a> for Testing the Repo, highlighting issues and suggesting improvements.</p><h4>References</h4><ul><li><a href="https://github.com/kevenpinto/beam_flex_demo">Code Repo</a></li><li><a href="https://www.youtube.com/watch?v=xaoruVUt0jk">Beam Summit Flex Template Demo</a></li><li><a href="https://cloud.google.com/blog/products/data-analytics/create-templates-from-any-dataflow-pipeline">Flex Template Blog by Google</a></li></ul><p><strong>About CTS</strong></p><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><p>Disclaimer: This is to inform readers that the views, thoughts, and opinions expressed in the text belong solely to the author.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=80ef6d1887c6" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/building-and-testing-dataflow-flex-templates-80ef6d1887c6">Build, Test and Deploy Dataflow Flex Templates in Google Cloud</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hive to BigQuery: Orchestrating the House Move of the Warehouse]]></title>
            <link>https://medium.com/qodea/hive-to-bigquery-orchestrating-the-house-move-of-the-warehouse-435be6f4a097?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/435be6f4a097</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[apache-hive]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Sun, 19 Jun 2022 12:28:14 GMT</pubDate>
            <atom:updated>2022-07-04T09:47:08.391Z</atom:updated>
            <cc:license>https://creativecommons.org/licenses/by-nc/4.0/</cc:license>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*jVFoM0BRvfRIY0lgAHiDMA.png" /></figure><p>Hive to BigQuery is a common data migration path and there are <a href="https://cloud.google.com/architecture/dw2bq/dw-bq-migration-overview">plenty of ways</a> to get there. This article describes the approach we took with one of our clients. The accompanying sample code is a scaled down version of our production code and is provided as an exemplar for anyone wanting to undertake a similar activity.</p><h3>The Ask</h3><p>A Discovery Workshop with the client revealed the following:</p><p>1) Table(s) on premise (Cloudera 6.x) are Hive managed</p><p>2) Underlying storage format for these tables is Parquet (SNAPPY compression)</p><p>3) These Parquet files will be uploaded by the client to a designated GCS Bucket i.e. Upload Bucket, each file size will be between 1–4 GB</p><p>4) The files will be encrypted using <a href="https://gnupg.org/index.html"><em>GPG</em></a></p><p>5) They will have an extension of <em>.gpg</em> when uploaded to the GCS Bucket</p><p>6) The root folder of the Upload Bucket will hold one or more manifest files (.manifest extension). These files will provide metadata on the data uploaded. below is an excerpt from a manifest file.</p><pre>{ &quot;files&quot;:[ <br>            {<br>              &quot;file&quot;:&quot;part-0000-8978787.snappy.parquet&quot;,<br>              &quot;md5sum&quot; :&quot;d4adfabba91bb2c2dbaa774ea09bf83d&quot;,<br>              &quot;cdhPath&quot;: &quot;/cdh/hive/table1&quot;<br>             },<br>             {<br>              .....<br>              }<br>            ]<br>}</pre><p>7) The “<em>cdhPath</em>” key in the Manifest file will allow us to identify the name of the target table. for example, “cdhPath”: “/cdh/hive/table1”</p><p>8) The Upload Bucket will follow the exact sub folders structure as specified in the “<em>cdhPath</em>” key of the Manifest File</p><p>9) The <em>“md5sum” </em>specified in the Manifest file will be pre GPG values</p><h3>Our Approach</h3><p>A review of these requirements meant we would need to deal with the following challenges:</p><p>1. Make the data available locally to the decryption service, i.e. Mounted to a Machine/Pod/Container (as we cannot decrypt using a <em>gs://</em> URI) or use a service that already has a bucket(s) mounted to worker nodes</p><p>2. Ensure that we are able to orchestrate the entire process and reduce any manual intervention</p><p>3. Design a workflow that allows us to decrypt files in parallel as we felt this would be a long running task given the size and number of the files</p><p>4. Have a secure way of storing GPG keys and making them available to compute resources</p><h3>Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0cQFCdEoczqAtcWU7_uAmA.png" /><figcaption>Hive to BigQuery Architecture</figcaption></figure><p>Based on the requirements &amp; challenges we felt Cloud Composer would be the best tool for the job as it would allow us the following:</p><p>1. Access to the <em>gs://DAG_BUCKET/data</em> subfolder on every node. This is mounted as <a href="https://cloud.google.com/composer/docs/composer-2/cloud-storage">/home/airflow/gcs/data by default by Cloud Composer.</a> This took care of challenge 1</p><p>2. Use TaskGroups in Airflow to decrypt in Parallel. This took care of challenge 2 &amp; 3</p><p>3. Use Airflows seamless integration with GCP Secrets manager to ensure keys are securely passed to nodes performing decryption. This took care of the challenge 4</p><h3>Composer Configuration</h3><p>The first thing we needed to do to our existing Composer 2 instance was to modify it to use GCP Secret Manager as its Secrets Backend. A secrets Backend is secure store from where Cloud Composer accesses secure information like passwords, connection details etc. Kindly refer to <a href="https://cloud.google.com/composer/docs/composer-2/configure-secret-manager">this</a> doc from Google for more information on how to set this up.</p><p>As you can see from the screenshot (<strong><em>backend_kwargs</em></strong><em>)</em> we have informed Cloud Composer that all our variables in Secrets Manager will start with the word “migration” and will be immediately followed by a Hyphen.</p><p>The DAG also looks for an Airflow Variable called <em>source_location. </em>This holds the name of the DAG Bucket. make sure to set this if you plan to download the code and use it.</p><figure><img alt="Cloud Composer 2, Cloud Composer Backend, Google Secret Manager" src="https://cdn-images-1.medium.com/max/1024/1*hfX91ltExTYT59T0qyWMzw.png" /></figure><h3>GCP Secret Manager Configuration</h3><p>We next had to setup our GPG Private Key, GPG Public Key and GPG Passphrase in Secrets manager. As mentioned in the section above these Variables needed to conform to the naming convention of <strong><em>migration-&lt;name&gt;</em></strong></p><figure><img alt="Google Secret Manager, Cloud Composer Backend" src="https://cdn-images-1.medium.com/max/1024/1*mqWtpNAnal3t8iZgOUULtg.png" /><figcaption>Secret Manager</figcaption></figure><p>As you can see above, we have created 3 secrets and the names of these secrets conform to the naming standard we configured in Cloud Composer. At this point we’d also like to point out that Airflow will mask information stored in any variables whose name contains the following words (<em>‘access_token’, ‘api_key’, ‘apikey’,’authorization’, ‘passphrase’, ‘passwd’, ‘password’, ‘private_key’, ‘secret’, ‘token’</em>). This means that the output from these variables always appear as <strong><em>‘***’</em></strong> in any Airflow logs and output messages (Security out of the Box!). This feature dictated what the names of our variables would be. Kindly refer to <a href="https://airflow.apache.org/docs/apache-airflow/2.2.5/security/secrets/mask-sensitive-values.html#sensitive-field-names">this</a> document for more details on this masking feature.</p><h3>DAG Design</h3><p>The following principles guided our work:</p><ul><li>Use gcp SDK tools wherever possible (<em>gsutil</em>, <em>gcloud</em> etc) via the BashOperator</li><li>Avoid writing custom ETL (if possible) — leverage the <em>bq</em> cli tool from BigQuery</li><li>Make sure the process is idempotent</li><li>Ensure individual Tasks are atomic in nature</li></ul><figure><img alt="Cloud Composer2, BQ Loader, BigQuery, GPG, TaskGroup" src="https://cdn-images-1.medium.com/max/1024/1*2orCoMO1c0rwWiL78hwWsw.png" /><figcaption>DAG — Image 1</figcaption></figure><figure><img alt="Cloud Composer2, BQ Loader, BigQuery, GPG, TaskGroup" src="https://cdn-images-1.medium.com/max/1024/1*Pmfhs-aZxG5vxqS8NgJ5bQ.png" /><figcaption>DAG Image 2</figcaption></figure><figure><img alt="Cloud Composer2, BQ Loader, BigQuery, GPG" src="https://cdn-images-1.medium.com/max/1024/1*64Fesyov5W1UuUlm13chZA.png" /><figcaption>DAG Image 3</figcaption></figure><h3>Overview of DAG Tasks</h3><h4>init (BashOperator)</h4><p>Create necessary Folders under <em>/home/airflow/gcs/data/</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u0HoHWGScWOZwbLfjDIbFA.png" /></figure><h4>attach (BashOperator)</h4><p>Copy data (<em>gsutil cp</em>) from the upload bucket to <em>/encrypted</em> folder. This now makes data available to all Worker Nodes in Airflow and allows us to gpg decrypt the files.</p><h4>decrypt_hive_files (TaskGroup of BashOperators)</h4><p>Decrypt the Files in Parallel and store this in the <em>/decrypted</em> folder.</p><p>Given that we are using Airflow 2.2.x, we are not able to take advantage of the <a href="https://airflow.apache.org/docs/apache-airflow/2.3.0/concepts/dynamic-task-mapping.html#dynamic-task-mapping">dynamic_tasks (Airflow 2.3 )</a> feature. (<em>n.b. I’m open to ideas if anyone has a better way to do this?)</em><br> <br>Note: We tried creating task dynamically based on the manifest but this process would create multiple tasks for the same table.</p><h4>calc_file_md5 (BashOperator)</h4><p>Calculates md5 sums on the file(s) after decrypting the file and store this in the <em>/scripts</em> folder</p><h4>validate_md5 (PythonOperator)</h4><p>Compare prem and cloud md5’s</p><h4>gen_load_script (PythonOperator)</h4><p>This steps creates 2 shell scripts (dynamically) based on the values in the manifest files.</p><ul><li><em>group_files_by_table.sh</em></li><li><em>load_parquets_to_bq.sh</em></li></ul><h4>group_files_by_table (BashOperator)</h4><p>This step uses the dynamic script created in the previous step to group all parquet files for a table under a single directory. The directory is the name of the table.</p><h4>run_bq_loader (BashOperator)</h4><p>Use the script created in task <em>gen_load_scripts</em> to load data in BigQuery using the “bq” command line utility in Bigquery</p><h3>Gotchas</h3><p>There were quite a few, but these bit us the most.</p><ul><li>Profile your data and identify any outliers. We observed timestamp columns with values ”0000–00–00 00:00:00”. This data will break your load. Fix at source or exclude at source if possible</li><li>Hive allows columns that start with a number, for example “<em>3_month_rolling_average”</em>, BigQuery does not allow columns that start with a number. Ideally, create a new staging table in prem with this column renamed, move data to this new staging table and then extract and ship parquets from this staging table to GCS.</li></ul><p><strong>The source code is available </strong><a href="https://github.com/kevenpinto/composer_migration"><strong>here</strong></a><strong>.</strong></p><h3>Conclusion</h3><p>We have a fully Orchestrated Process to move data from on-prem to BiqQuery without writing a single line of ETL code! We do hope you’ll will try and implement some of the ideas and concepts mentioned here.</p><p>Disclaimer: This is to inform readers that the views, thoughts, and opinions expressed in the text belong solely to the author.</p><p><strong>About CTS</strong></p><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=435be6f4a097" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/hive-to-bigquery-orchestrating-the-house-move-of-the-warehouse-435be6f4a097">Hive to BigQuery: Orchestrating the House Move of the Warehouse</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Dataproc Serverless & Airflow 2 Powered Event Driven Pipelines]]></title>
            <link>https://medium.com/qodea/dataproc-serverless-airflow-2-powered-event-driven-pipelines-badb572532f3?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/badb572532f3</guid>
            <category><![CDATA[pipelines-as-code]]></category>
            <category><![CDATA[dataproc-serverless]]></category>
            <category><![CDATA[airflow-2]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Tue, 12 Apr 2022 12:43:30 GMT</pubDate>
            <atom:updated>2022-05-02T11:53:59.300Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/843/1*e03_kQo2WVWYAndV5fq2fQ.png" /></figure><p>In my <a href="https://medium.com/cts-technologies/running-pyspark-jobs-on-google-cloud-using-serverless-dataproc-f16cef5ec6b9">previous</a> post, I demonstrated how one can get a Dataproc Serverless pipeline up and running from the CLI. In this post, we’ll look at how Dataproc Serverless integrates seamlessly with <a href="https://cloud.google.com/blog/topics/developers-practitioners/what-cloud-composer">Cloud Composer</a> and how one can combine the two to create a simple <em>event-driven </em>pipeline.</p><blockquote>For those new to GCP, <a href="https://cloud.google.com/blog/topics/developers-practitioners/what-cloud-composer#:~:text=Cloud%20Composer%20is%20a%20fully,hybrid%20and%20multi%2Dcloud%20environments."><em>Cloud Composer </em></a>is Googles managed version of Apache Airflow, In the rest of this article the terms Cloud Composer and Airflow will be used interchangeably.</blockquote><p><strong>A bit about the Pipeline …</strong></p><p>Pipeline makes use of a <em>Google</em> <a href="https://airflow.apache.org/docs/apache-airflow/1.10.12/_api/airflow/contrib/sensors/gcs_sensor/index.html"><em>File Sensor</em></a><em> to achieve an event-driven pattern. </em>The sensor polls a GCS bucket for files with a certain prefix(stocks*.csv) and triggers a Dataproc Serverless job if it finds any files matching this criteria. Dataproc in turn reads the file(s) and writes the same to BigQuery. If all works, you should see a table called <em>stock_prices</em> in a BigQuery dataset called <em>serverless_spark_airflow_demo </em>in your GCP Project.</p><p><strong>Prerequisites</strong></p><p>Prior to <a href="https://github.com/kevenpinto/dataproc_serverless_airflow">downloading the Code from Github</a>, ensure that you have the following setup on the machine that you will be executing the code.</p><ul><li><a href="https://python-poetry.org/docs/">Poetry</a> for dependency management and packaging.</li><li>gcloud installed and authorised to your GCP Project</li><li>gsutil</li><li>bq command line tool</li><li><a href="https://www.gnu.org/software/make/">make</a> build tool</li></ul><p>Please also ensure the following in the GCP Project you plan to execute the pyspark job:</p><ul><li><a href="https://console.cloud.google.com/flows/enableapi?apiid=dataproc&amp;_ga=2.172662435.1215563939.1647940091-2019008709.1647010149">Dataproc API</a> enabled</li><li>Ensure your subnet has <a href="https://cloud.google.com/vpc/docs/configure-private-google-access">Private Google Access</a> enabled</li><li>Ensure Billing is enabled</li><li>Bigquery API is enabled</li><li>Cloud Composer2 (Airflow) installed in your GCP Project</li></ul><p><em>Note: The DAG is written in an Airflow 2.x style and I’m not sure if it will work on any Airflow 1 environments. Let me know in the comments below if it works on Composer 1.x.</em></p><p><strong>Cloud Composer 2 dags folder</strong></p><p>Cloud Composer schedules only the DAGs that are located in the /dags folder. This is a subfolder in a GCS bucket that automatically gets created while setting up Cloud Composer. We need the name of this bucket as we need to copy our DAG to this bucket. In order to get the name of this bucket, follow these steps:</p><ul><li>Log into the GCP Console</li><li>Type <a href="https://console.cloud.google.com/composer/environments?_ga=2.96052188.1703807200.1649604243-944945359.1649603311"><em>Environments</em></a> in the search Box</li><li>This should take you to the following screen</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OMuTiHU9-ymuGkHTPgj_QQ.png" /></figure><ul><li>Click on the DAGs link highlighted above, this will take you to your DAG Bucket, make a note of this bucket as we need this in our next step.</li></ul><p><strong>Setting up the Pipeline for your GCP Project</strong></p><ul><li>Download the code from <a href="https://github.com/kevenpinto/dataproc_serverless_airflow">here</a></li><li>Edit the <em>Makefile</em> in the Repo and update the PROJECT_ID, REGION &amp; DAG_BUCKET variables.</li></ul><p>DAG_BUCKET variable is the Cloud Composer bucket that you made a note of in the last step.</p><pre># Please add values without quotes<br>PROJECT_ID ?= &lt;CHANGE ME&gt;<br>REGION  ?= &lt;CHANGE ME&gt;<br>DAG_BUCKET ?= &lt;CHANGE ME&gt;</pre><pre>for example:<br>PROJECT_ID ?= my-gcp-project-292607<br>REGION ?= europe-west2<br>DAG_BUCKET ?= europe-west2-composer2-15e6754fg-bucket</pre><ul><li>Run the following command from the <em>root folder</em> of the repo to setup the required buckets and dataset in your GCP Project.</li></ul><pre>make setup</pre><p>After this command executes, you should have the following assets in your GCP Project:</p><ol><li>GCS Bucket serverless-spark-airfow-code-repo-&lt;project_number&gt; — our Pyspark Code gets uploaded here</li><li>GCS Bucket serverless-spark-airflow-staging-&lt;project_number&gt; — Used for BQ operations</li><li>GCS Bucket serverless-spark-airflow-data-&lt;project_number — Our source csv file(s) are in here</li><li>Bigquery Dataset called serverless_spark_airflow_demoin BigQuery</li></ol><p><strong>Packaging Your Code</strong></p><ul><li>To Package the code and move the binary to GCS execute the following command</li></ul><pre>make build</pre><p>Refer to my <a href="https://medium.com/cts-technologies/running-pyspark-jobs-on-google-cloud-using-serverless-dataproc-f16cef5ec6b9">previous article</a> if you would like to know a bit more on the packaging process</p><p><strong>Modify the DAG</strong></p><p>Open file event_based_dataproc_serverless_pipeline.py under the /dags folder of the code repo. Change the following 2 lines</p><pre>PROJECT_NUMBER = &lt;CHANGEME&gt;<br>REGION =  &lt;CHANGEME&gt;</pre><pre>for example:<br>PROJECT_NUMBER = &#39;9999999&#39;<br>REGION = &#39;europe-west2&#39;</pre><p>The <em>Project Number</em> can be found in the Home Page of the Console as shown below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jJIiej2ur-sQtE2SViFcVw.png" /></figure><p>Once the changes have been made; run the following command to push your dag to Cloud Composers /<em>dags</em> folder</p><pre>make dags</pre><p><strong>Open the Airflow UI</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*63BJ8hZU-qkZ23Yi88AZng.png" /><figcaption>event_based_dataproc_serverless_pipeline</figcaption></figure><p>You should now, see the DAG event_based_dataproc_serverless_pipeline as shown above. Click on the DAG to bring up the Screen below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eG3xrKp1X3nfppdZ5f7Aqw.png" /></figure><p>Click on the Task called <em>Start, </em>then click <em>Clear</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VVMjEFMxoNt8Lcz9YtYLuQ.png" /></figure><p>Click Ok on the following screen, this will <em>re-trigger</em> the DAG if this has not already been triggered.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*irk5T9xq1lpPAv-88vXk0g.png" /></figure><p><strong>So what&#39;s happening within the DAG?</strong></p><ul><li>The task <em>file_sensor </em>is monitoring the data bucket for files with a Prefix of stocks*.csv. This bucket was created by us as part of the Setup (<em>make setup</em>) . if files are found by the sensor, the task creates an <a href="https://airflow.apache.org/docs/apache-airflow/stable/concepts/xcoms.html">XCOM</a> (Row 1 in image below) returning a <em>list</em> of all the files that match the prefix in the bucket.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9UPuEBPV72Mf_gsJLmSRPg.png" /></figure><p><em>Note: We have already pushed 2 files to this bucket as part of setup</em></p><ul><li>In the next task (<em>format_file_names)</em> we convert the List of files to a single string. This string contains all the file names separated by a comma. See Row 2 in the image above</li><li>Finally, we pass this String of file names to the task <em>run_dataproc_serverless_batch. </em>this task is<em> </em>Initiates a <em>Dataproc Serverless Batch Job</em> in the Background. this can be verified by checking the Batches under Dataproc from the console as shown in the screenshot below.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yUUKsZhMmKGOc7Xs8f5AYw.png" /></figure><ul><li>If all the tasks in the dag have a <em>Green Border; </em>this means your DAG has been completed and you should see the following table in BigQuery.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i9Se3wBS_UzLfHilLXoSeA.png" /></figure><ul><li>That’s it! We have successfully executed our Pipeline!</li></ul><p><strong>Next Steps</strong></p><p>I’d encourage you to delete the files from the <em>data bucket</em>, run the dag and once the dag has started, upload files to the data bucket.</p><p><em>Observe the behaviour, see if this triggers the dataproc job !</em></p><p>Note: The file sensor is on a <em>reschedule</em> mode, this means it will reschedule itself if it does not find any files when it first polls the bucket. If files are uploaded after the dag has kicked off, it could be a minute before the dag picks up the files. So, be patient!.</p><p>Finally, as the eagle eyed among you may have noticed, the DAG is incomplete from an end to end perspective. Once the files have been processed by Dataproc, they should ideally be moved to an archive location. I’ll leave that bit for you’ll to implement :-) Good Luck!</p><p><em>Hint! GCSToGCSOperator.</em></p><p><strong>Links and References</strong></p><ul><li><a href="https://github.com/kevenpinto/dataproc_serverless_airflow">Github Repo</a></li><li><a href="https://cloud.google.com/dataproc-serverless/docs">Dataproc Serverless docs</a></li><li><a href="https://cloud.google.com/composer/docs/composer-2/composer-versioning-overview">Cloud Composer 2</a></li></ul><p>To know a bit more about Dataproc Serverless kindly refer to <a href="https://medium.com/cts-technologies/dataproc-serverless-for-spark-on-gcp-a4b74b2c0b8">this</a> excellent article written by my colleague Ash.</p><p>I am hopeful you have found this useful and will try this out in your GCP projects. if you like what has been showcased, feel free to <a href="/service/mailto:kev.pinto@cts.co">get in touch</a> and we shall be happy to help you/your company on your Cloud journey with GCP.</p><p><strong>About CTS</strong></p><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><p>Disclaimer: This is to inform readers that the views, thoughts, and opinions expressed in the text belong solely to the author.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=badb572532f3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/dataproc-serverless-airflow-2-powered-event-driven-pipelines-badb572532f3">Dataproc Serverless &amp; Airflow 2 Powered Event Driven Pipelines</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Running pyspark jobs on Google Cloud using Serverless Dataproc]]></title>
            <link>https://medium.com/qodea/running-pyspark-jobs-on-google-cloud-using-serverless-dataproc-f16cef5ec6b9?source=rss-5a17a7fa9fe0------2</link>
            <guid isPermaLink="false">https://medium.com/p/f16cef5ec6b9</guid>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[dataproc]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[serverless-architecture]]></category>
            <dc:creator><![CDATA[Keven Pinto]]></dc:creator>
            <pubDate>Thu, 24 Mar 2022 13:32:21 GMT</pubDate>
            <atom:updated>2022-05-02T11:58:47.168Z</atom:updated>
            <cc:license>https://creativecommons.org/publicdomain/mark/1.0/</cc:license>
            <content:encoded><![CDATA[<p><em>Run Spark batch workloads without having to bother with the provisioning and management of clusters!.</em></p><blockquote>If you are interested in running a simple pyspark pipeline in Serverless mode on the Google Cloud Platform then read on..</blockquote><p>The ability to run spark jobs in serverless mode is a great idea, however, the loss of control over the Cluster demands that we structure and package our code in a slightly different manner. This article provides an explanation of the method that I have employed to get my pipeline running in serverless mode.</p><p>The approach taken in this article is one of many that one can employ while working with Dataproc Serverless; the other approach that comes to mind is that of using <a href="https://cloud.google.com/dataproc-serverless/docs/guides/custom-containers">custom containers</a> …. having started on that path, I eventually abandoned it due to the following reasons:</p><ul><li>One more configurable item to maintain</li><li>A constant need to modify the container if new pipelines utilise new python libraries</li><li>Pipelines with dependencies on different versions of the same package.</li></ul><p><em>Note: I would like to state that custom containers are not a bad feature, it just didn&#39;t fit my use case at this time.</em></p><p><strong>Prerequisites</strong></p><p>Prior to <a href="https://github.com/kevenpinto/spark-serverless-repo-example">downloading the Code from Github</a>, ensure that you have the following setup on the machine that you will be executing the code.</p><ul><li><a href="https://python-poetry.org/docs/">Poetry</a> for dependency management and packaging.</li><li>gcloud installed and authorised to your GCP Project</li><li>gsutil</li><li>bq command line tool</li><li><a href="https://www.gnu.org/software/make/">make</a> build tool</li></ul><p>Please also ensure the following in the GCP Project you plan to execute the pyspark job:</p><ul><li><a href="https://console.cloud.google.com/flows/enableapi?apiid=dataproc&amp;_ga=2.172662435.1215563939.1647940091-2019008709.1647010149">Dataproc API</a> enabled</li><li>Ensure your subnet has <a href="https://cloud.google.com/vpc/docs/configure-private-google-access">Private Google Access</a> enabled</li><li>Ensure Billing is enabled</li><li>Bigquery API is enabled</li></ul><p><strong>A bit about the Pipeline …</strong></p><p>The Pipeline is a simple pyspark job that reads a file from a GCS bucket and saves the result to a table in Bigquery. If all works, you should see a table called stock_prices in a bigquery dataset called serverless_spark_demo in your GCP Project.</p><p><strong>Setting up the Pipeline for your GCP Project</strong></p><ul><li>Download the code from <a href="https://github.com/kevenpinto/spark-serverless-repo-example">here</a></li><li>Edit the <em>Makefile</em> in the Repo and update the PROJECT &amp; REGION variables</li></ul><pre># Please add values without quotes<br>PROJECT_ID ?= &lt;CHANGE ME&gt;<br>REGION  ?= &lt;CHANGE ME&gt;</pre><pre>for example:<br>PROJECT_ID ?= my-gcp-project-292607<br>REGION ?= europe-west2</pre><ul><li>Run the following command from the root folder of the repo and ensure that you are in the correct GCP Project</li></ul><pre>gcloud config list</pre><ul><li>Run the following command from the <em>root folder</em> of the repo to setup the required buckets and dataset in your GCP Project.</li></ul><pre>make setup</pre><p>After this command executes, you should have the following assets in your GCP Project:</p><ol><li>GCS Bucket serverless-spark-code-repo-&lt;project_number&gt; — our Pyspark Code gets uploaded here</li><li>GCS Bucket serverless-spark-staging-&lt;project_number&gt; — Used for BQ operations</li><li>GCS Bucket serverless-spark-data-&lt;project_number — Our source csv file is in here</li><li>Bigquery Dataset called serverless_spark_demoin BigQuery</li></ol><p><strong>Packaging Your Code</strong></p><p>As this is a serverless setup, we will be packaging our python code along with all its 3rd party python dependencies and submit this as a single packaged file to the service. Dataproc Serverless supports .py, .egg and .zip file types, we have chosen to go down the zip file route.</p><ul><li>To Package the code, run the following command from the root folder of the repo</li></ul><pre>make build</pre><p>The above command does the following:</p><ul><li>Creates a build package in a folder called /dist in your repo root folder</li><li>Uploads this folder to the GCP Bucket(serverless-spark-code-repo-&lt;project_number&gt;)</li></ul><p>Let us take a look at what&#39;s happening under the hood! Below is the build target from the Makefile.</p><pre>build: clean <em>## Build Python Package with Dependencies</em><br>   @mkdir -p ./dist<br>   @poetry update<br>   @poetry export -f requirements.txt --without-hashes -o requirements.txt<br>   @poetry run pip install . -r requirements.txt -t ${SRC_WITH_DEPS}<br>   @cd ./${SRC_WITH_DEPS}<br>   @find . -name &quot;*.pyc&quot; -delete<br>   @cd ./${SRC_WITH_DEPS} &amp;&amp; zip -x &quot;*.git*&quot; -x &quot;*.DS_Store&quot; -x &quot;*.pyc&quot; -x &quot;*/*__pycache__*/&quot; -x &quot;.idea*&quot; -r ../dist/${SRC_WITH_DEPS}.zip .<br>   @rm -Rf ./${SRC_WITH_DEPS}<br>   @rm -f requirements.txt<br>   @cp ./src/main.py ./dist<br>   @mv ./dist/${SRC_WITH_DEPS}.zip ./dist/${APP_NAME}_${VERSION_NO}.zip<br>   @gsutil cp -r ./dist gs://${CODE_BUCKET}</pre><p>Poetry (<em>poetry update</em>) first looks at all the dependencies (3rd party packages) required by the pipeline. it does this by interrogating the pyproject.toml file and updating thepoetry.lock file (if required). I’d encourage you to look at the [tool.poetry.dependencies] section of the pyproject.toml to get a view of the 3rd party packages used in the project.</p><p>This .lock file then forms the basis to setup all code and dependencies in a temp folder before zipping it all up in a single .zip file. This file is renamed to APP_NAME_VERSION_NO.zip in a later step - the values of APP_NAMEand VERSION_NO are extracted from pyproject.toml.</p><p><em>One may notice that I have not added pyspark in the </em><em>[tool.poetry.dependencies] section… that&#39;s deliberate - pyspark comes pre installed on </em><a href="https://cloud.google.com/dataproc-serverless/docs/concepts/versions/spark-runtime-versions"><em>Googles standard spark container.</em></a></p><p>The final bit I want to discuss around the build is the following line.</p><pre>@cp ./src/main.py ./dist</pre><p><em>Why are we moving this file again to </em>./dist<em>, isn’t this already part of the zip?</em></p><p>No!, is the short answer.</p><p>The reason we need to do this step is because <a href="https://cloud.google.com/sdk/gcloud/reference/dataproc/batches/submit/pyspark">dataproc serverless </a>needs a python file as the main entry point, this cannot be inside a .zip or .egg file. So we need keep this as an independent file under the ./dist folder. The dist folder now looks like this.</p><pre>├── dist<br>│   ├── main.py<br>│   └── spark_serverless_repo_exemplar_0.1.0.zip</pre><p>Another important point to note is the config settings inpyproject.toml - we <em>exclude</em> the main.pyfile that sits directly under /src from being packaged. Below is an excerpt from the .toml file that shows the setting.</p><pre>exclude = [&quot;src/main*.py&quot;]<br>packages = [<br>    { include = &quot;src/**/*.py&quot; },<br>]</pre><p><strong>Execute the Pipeline</strong></p><p>The pipeline can be executed by running the following command from the root folder of the Repo</p><pre>make run</pre><p>The code below is an excerpt from the Makefile and shows the gcloud command we have used to run spark in serverless mode.</p><pre>run: <em>## Run the dataproc serverless job<br>   </em>gcloud beta dataproc batches submit --project ${PROJECT_ID} --region ${REGION} pyspark \<br>   gs://${CODE_BUCKET}/dist/main.py --py-files=gs://${CODE_BUCKET}/dist/${APP_NAME}_${VERSION_NO}.zip \<br>   --subnet default --properties spark.executor.instances=2,spark.driver.cores=4,spark.executor.cores=4,spark.app.name=spark_serverless_repo_exemplar \<br>   --jars gs://spark-lib/bigquery/spark-bigquery-with-dependencies_2.12-0.23.2.jar \<br>   -- --project=${PROJECT_ID} --file-uri=gs://${DATA_BUCKET}/stocks.csv --temp-bq-bucket=${TEMP_BUCKET}</pre><p>Note: Spark communicates to BigQuery via a Connector, this connector needs to be passed to the dataproc job via the — jars flag</p><pre>--jars gs://spark-lib/bigquery/spark-bigquery-with-dependencies_2.12-0.23.2.jar</pre><p>Once this pipeline completes <em>Successfully</em>; one should see a table called stock_prices under the serverless_spark_demo dataset in bigquery.</p><p><strong>View Pipeline Execution</strong></p><ul><li>Log on to your GCP Console</li><li>Type dataprocin the Search bar and select Dataproc</li><li>Click on the <em>Batches</em> Menu option on the Left</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IQByDk4voE5-kZDNNQOBAg.png" /><figcaption>Dataproc Batches — Console View</figcaption></figure><p>This Screen should give you a list of Dataproc Serverless Batch jobs you have executed, you should see the job you just submitted either in the Pending or the Succeeded state.</p><p>Click on the Batch ID of the job we just executed, this opens up the detailed view for the job. Click on the Clone Menu Option and then click Submit</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V73mrGqrAPWjHbabzlZLsw.png" /></figure><p>You will observe a new Batch Job in the dataproc batches list, however if you click on the Clusters menu option on the left, you will not see any clusters being instantiated…. Magic!</p><p><strong>Summing it all up</strong></p><p>Congrats- you have run just your first Dataproc Serverless job. As we have seen, there is absolutely no cluster to manage or provision.</p><p>I am hopeful you have found this useful and will try this out in your GCP projects. if you like what has been showcased, feel free to <a href="/service/mailto:kev.pinto@cts.co">get in touch</a> and we shall be happy to help you/your company on your Cloud journey with GCP.</p><p>To know a bit more about Dataproc Serverless kindly refer to <a href="https://medium.com/cts-technologies/dataproc-serverless-for-spark-on-gcp-a4b74b2c0b8">this</a> excellent article written by my colleague Ash.</p><p><strong>Links and References</strong></p><ul><li><a href="https://github.com/kevenpinto/spark-serverless-repo-example">Github Repo</a></li><li><a href="https://cloud.google.com/dataproc-serverless/docs">Dataproc Serverless docs</a></li></ul><h3><strong>About CTS:</strong></h3><p>CTS is the largest dedicated Google Cloud practice in Europe and one of the world’s leading Google Cloud experts, winning 2020 Google Partner of the Year Awards for both Workspace and GCP.</p><p>We offer a unique full stack Google Cloud solution for businesses, encompassing cloud migration and infrastructure modernisation. Our data practice focuses on analysis and visualisation, providing industry specific solutions for; Retail, Financial Services, Media and Entertainment.</p><p>We’re building talented teams ready to change the world using Google technologies. So if you’re passionate, curious and keen to get stuck in — take a look at our <a href="http://careers.cts.co/">Careers Page</a> and join us for the ride!</p><p>Disclaimer: This is to inform readers that the views, thoughts, and opinions expressed in the text belong solely to the author.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f16cef5ec6b9" width="1" height="1" alt=""><hr><p><a href="https://medium.com/qodea/running-pyspark-jobs-on-google-cloud-using-serverless-dataproc-f16cef5ec6b9">Running pyspark jobs on Google Cloud using Serverless Dataproc</a> was originally published in <a href="https://medium.com/qodea">Beyond Engineering.</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>