<?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 Muhammad Saipul Rohman on Medium]]></title>
        <description><![CDATA[Stories by Muhammad Saipul Rohman on Medium]]></description>
        <link>https://medium.com/@msaipulr?source=rss-645d0d2f0212------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*MlKXBfsrC25IMr-k47vdaw.png</url>
            <title>Stories by Muhammad Saipul Rohman on Medium</title>
            <link>https://medium.com/@msaipulr?source=rss-645d0d2f0212------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 08:27:10 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@msaipulr/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[Real-Time CDC to the Open Lakehouse: Streaming Cloud SQL (PostgreSQL) Changes into Apache Iceberg…]]></title>
            <link>https://medium.com/google-cloud/real-time-cdc-to-the-open-lakehouse-streaming-cloud-sql-postgresql-changes-into-apache-iceberg-6e114861469e?source=rss-645d0d2f0212------2</link>
            <guid isPermaLink="false">https://medium.com/p/6e114861469e</guid>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[cdc]]></category>
            <category><![CDATA[streaming-pipeline]]></category>
            <dc:creator><![CDATA[Muhammad Saipul Rohman]]></dc:creator>
            <pubDate>Tue, 13 Oct 2026 11:45:43 GMT</pubDate>
            <atom:updated>2026-10-13T11:45:43.814Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Real-Time CDC to the Open Lakehouse: Streaming Cloud SQL (PostgreSQL) Changes into Apache Iceberg on GCP</strong></h3><p><em>How to build a zero-lock-in Change Data Capture pipeline using Cloud SQL, Datastream, BigQuery Managed Iceberg Tables, and Cloud Storage — plus the real-world engineering patterns for turning Append-Only CDC logs into upserted Silver Iceberg tables.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xIk_3YvKJk5gRYhN7QZX-w.jpeg" /><figcaption><em>Figure 1: End-to-end serverless CDC architecture streaming Cloud SQL for PostgreSQL changes into BigQuery Managed Apache Iceberg tables on Google Cloud Storage via Datastream.</em></figcaption></figure><h4><strong>Introduction: The Tension Between Real-Time CDC and Open Table Formats</strong></h4><p>Modern data engineering teams usually want two things that historically required painful trade-offs:</p><p>1. <strong>Real-Time Operational Analytics (CDC)</strong>: Replicating INSERT, UPDATE, and DELETE transactions from operational PostgreSQL databases within seconds so business dashboards and AI agents never run on stale data.</p><p>2. <strong>An Open Data Lakehouse Architecture</strong>: Storing analytical tables in open formats — specifically <strong>Apache Iceberg</strong> backed by <strong>Parquet</strong> files in customer-owned <strong>Google Cloud Storage (GCS)</strong> buckets — so data is never locked into a single proprietary storage engine.</p><p>Traditionally, combining real-time PostgreSQL CDC with Apache Iceberg meant managing a complex, heavy stack: Debezium, Apache Kafka, Schema Registry, Spark Structured Streaming or Apache Flink, and custom compaction cron jobs to prevent the notorious Iceberg “small file problem.”</p><p>With <strong>Google Cloud Datastream</strong> and <strong>BigQuery Managed Tables for Apache Iceberg (BigLake)</strong>, you can now stream PostgreSQL Write-Ahead Log (WAL) changes directly into governed Apache Iceberg tables in your own GCS bucket — completely serverless.</p><p>In this hands-on guide, we will build this pipeline end-to-end on Google Cloud and solve three real-world engineering nuances you will encounter in production:</p><ul><li>Configuring Cloud SQL for PostgreSQL logical decoding and Datastream blmtConfig (BigLake Managed Tables) via the gcloud CLI.</li><li>Understanding why Datastream writes to Apache Iceberg in <strong>Append-Only mode</strong> (and how its datastream_metadata struct differs from native BigQuery Merge tables).</li><li>Implementing both a <strong>Zero-Latency Deduplicated View</strong> and an atomic <strong>ACID </strong><strong>MERGE INTO Silver Iceberg Table</strong> to handle row-level updates and deletes.</li></ul><h4><strong>Architecture Overview</strong></h4><p>Our pipeline consists of four layers:</p><p>1. <strong>Operational Source (OLTP): </strong>A <strong>Cloud SQL for PostgreSQL 15</strong> instance configured with cloudsql.logical_decoding=on, a PUBLICATION, and a pgoutput logical replication slot.</p><p>2. <strong>Serverless CDC Ingestion:</strong> <strong>Google Cloud Datastream</strong> reads the PostgreSQL WAL stream and writes via the BigQuery Storage Write API.</p><p>3. <strong>Bronze Open Lakehouse Layer (</strong><strong>lakehouse_bronze.public_orders):</strong> Configured via Datastream’s blmtConfig (tableFormat: ICEBERG, fileFormat: PARQUET), Datastream lands every change event (INSERT, UPDATE-INSERT, UPDATE-DELETE, DELETE) into an Apache Iceberg table stored in our GCS bucket via a <strong>BigQuery Cloud Resource Connection</strong>.</p><p>4. <strong>Silver Current-State Layer (</strong><strong>lakehouse_silver):</strong></p><ul><li><strong>Pattern A (</strong><strong>v_orders_current):</strong> A real-time SQL View using QUALIFY ROW_NUMBER() that resolves the latest state per primary key on the fly.</li><li><strong>Pattern B (</strong><strong>lakehouse_silver.orders):</strong> A materialized Apache Iceberg table updated via BigQuery’s native ACID MERGE INTO DML, with exported Iceberg V2 metadata (metadata.json &amp; Avro manifest lists) ready for Apache Spark, Trino, or PyIceberg.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GwixSanwOLUqjFn32TNW4g.jpeg" /><figcaption><em>End-to-end CDC transaction lifecycle from Cloud SQL PostgreSQL WAL to Bronze and Silver Apache Iceberg tables on Google Cloud Storage.</em></figcaption></figure><p><strong>Step 1: Provisioning the Open Lakehouse Foundation &amp; Cloud SQL</strong></p><p>First, let’s define our environment variables and enable the required Google Cloud APIs (sqladmin, datastream, bigquery, bigqueryconnection, biglake, and storage):</p><pre>export PROJECT_ID=&quot;your-gcp-project-id&quot;<br>export REGION=&quot;us-central1&quot;<br>export GCS_BUCKET=&quot;${PROJECT_ID}-iceberg-lakehouse&quot;<br>export BQ_CONNECTION_ID=&quot;iceberg-gcs-connection&quot;<br>export BQ_BRONZE_DATASET=&quot;lakehouse_bronze&quot;<br>export BQ_SILVER_DATASET=&quot;lakehouse_silver&quot;<br>export CLOUDSQL_INSTANCE=&quot;pg-cdc-source&quot;<br>export PG_DATABASE=&quot;ecommerce&quot;<br>export PG_USER=&quot;postgres&quot;<br>export PG_PASSWORD=&quot;YourStrongPassword2026!&quot;</pre><h4><strong>1.1 Create the GCS Bucket &amp; BigQuery Cloud Resource Connection</strong></h4><p>BigQuery Managed Iceberg tables require a **Cloud Resource Connection** whose service account has `roles/storage.admin` on your GCS bucket:</p><pre># Create the GCS bucket for Apache Iceberg data and metadata<br>gcloud storage buckets create &quot;gs://${GCS_BUCKET}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - location=&quot;${REGION}&quot; \<br> - uniform-bucket-level-access<br># Create the BigQuery Cloud Resource Connection<br>bq mk - connection \<br> - connection_type=CLOUD_RESOURCE \<br> - project_id=&quot;${PROJECT_ID}&quot; \<br> - location=&quot;${REGION}&quot; \<br>&quot;${BQ_CONNECTION_ID}&quot;<br># Retrieve the Connection&#39;s Service Account and grant Storage Admin on the bucket<br>BQ_CONN_SA=$(bq show - format=json - connection &quot;${PROJECT_ID}.${REGION}.${BQ_CONNECTION_ID}&quot; \<br>| python3 -c &quot;import sys, json; print(json.load(sys.stdin)[&#39;cloudResource&#39;][&#39;serviceAccountId&#39;])&quot;)<br>gcloud storage buckets add-iam-policy-binding &quot;gs://${GCS_BUCKET}&quot; \<br> - member=&quot;serviceAccount:${BQ_CONN_SA}&quot; \<br> - role=&quot;roles/storage.admin&quot; \<br> - project=&quot;${PROJECT_ID}&quot;<br># Create Bronze and Silver datasets in BigQuery<br>bq - location=&quot;${REGION}&quot; mk - dataset &quot;${PROJECT_ID}:${BQ_BRONZE_DATASET}&quot;<br>bq - location=&quot;${REGION}&quot; mk - dataset &quot;${PROJECT_ID}:${BQ_SILVER_DATASET}&quot;</pre><h4><strong>1.2 Create the Cloud SQL for PostgreSQL Instance with Logical Decoding</strong></h4><blockquote><strong><em>Engineering Gotcha #1:</em></strong><em> On Cloud SQL for PostgreSQL, the database flag that sets </em><em>wal_level = logical is named </em><strong><em>cloudsql.logical_decoding=on</em></strong><em> (not </em><em>cloudsql.logical_replication=on, which is used by AlloyDB).</em></blockquote><p>We also dynamically fetch Datastream’s regional static IPs so Datastream can connect to Cloud SQL:</p><pre>DS_IPS=$(gcloud datastream locations fetch-static-ips &quot;${REGION}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - format=&quot;value(staticIps)&quot; | tr &#39;;&#39; &#39;,&#39; | tr &#39;\n&#39; &#39;,&#39; | sed &#39;s/,$//&#39;)<br>gcloud sql instances create &quot;${CLOUDSQL_INSTANCE}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - database-version=POSTGRES_15 \<br> - edition=ENTERPRISE \<br> - tier=db-custom-2–7680 \<br> - region=&quot;${REGION}&quot; \<br> - root-password=&quot;${PG_PASSWORD}&quot; \<br> - database-flags=cloudsql.logical_decoding=on,max_replication_slots=10,max_wal_senders=10 \<br> - authorized-networks=&quot;${DS_IPS}&quot; \<br> - storage-type=SSD \<br> - storage-size=20GB<br>gcloud sql databases create &quot;${PG_DATABASE}&quot; \<br> - instance=&quot;${CLOUDSQL_INSTANCE}&quot; \<br> - project=&quot;${PROJECT_ID}&quot;</pre><h4><strong>Step 2: Preparing PostgreSQL Publication &amp; Logical Replication Slot</strong></h4><p>Next, connect to the ecommerce database on Cloud SQL and create an operational public.orders table, insert 5 initial seed rows, create a PostgreSQL publication, and initialize a pgoutput logical replication slot:</p><pre>- 1. Create the operational `orders` table<br>CREATE TABLE IF NOT EXISTS public.orders (<br>order_id VARCHAR(20) PRIMARY KEY,<br>customer_id VARCHAR(50) NOT NULL,<br>product_category VARCHAR(50) NOT NULL,<br>order_status VARCHAR(30) NOT NULL,<br>total_amount NUMERIC(12, 2) NOT NULL,<br>shipping_city VARCHAR(50) NOT NULL,<br>updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP<br>);<br> - 2. Configure REPLICA IDENTITY DEFAULT so primary keys are logged on UPDATE/DELETE<br>ALTER TABLE public.orders REPLICA IDENTITY DEFAULT;<br> - 3. Insert 5 seed orders (to test Datastream initial backfill into Iceberg)<br>INSERT INTO public.orders (order_id, customer_id, product_category, order_status, total_amount, shipping_city, updated_at)<br>VALUES<br>(&#39;ORD-1001&#39;, &#39;CUST-01&#39;, &#39;Electronics&#39;, &#39;PENDING&#39;, 450.00, &#39;Jakarta&#39;, NOW() - INTERVAL &#39;2 hours&#39;),<br>(&#39;ORD-1002&#39;, &#39;CUST-02&#39;, &#39;Home Office&#39;, &#39;PROCESSING&#39;, 180.50, &#39;Surabaya&#39;, NOW() - INTERVAL &#39;90 minutes&#39;),<br>(&#39;ORD-1003&#39;, &#39;CUST-03&#39;, &#39;Books&#39;, &#39;PENDING&#39;, 35.00, &#39;Bandung&#39;, NOW() - INTERVAL &#39;60 minutes&#39;),<br>(&#39;ORD-1004&#39;, &#39;CUST-04&#39;, &#39;Gaming&#39;, &#39;SHIPPED&#39;, 890.00, &#39;Singapore&#39;, NOW() - INTERVAL &#39;30 minutes&#39;),<br>(&#39;ORD-1005&#39;, &#39;CUST-05&#39;, &#39;Audio&#39;, &#39;DELIVERED&#39;, 220.00, &#39;Jakarta&#39;, NOW() - INTERVAL &#39;15 minutes&#39;)<br>ON CONFLICT (order_id) DO NOTHING;<br> - 4. Grant replication role and create Publication + Replication Slot<br>ALTER USER postgres WITH REPLICATION;<br>CREATE PUBLICATION datastream_iceberg_pub FOR TABLE public.orders;<br>SELECT pg_create_logical_replication_slot(&#39;datastream_iceberg_slot&#39;, &#39;pgoutput&#39;);</pre><h4><strong>Step 3: Configuring Datastream for BigQuery Apache Iceberg Tables (</strong><strong>blmtConfig)</strong></h4><p>Now we create the Datastream Source and Destination Connection Profiles:</p><pre>PG_PUBLIC_IP=$(gcloud sql instances describe &quot;${CLOUDSQL_INSTANCE}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - format=&quot;value(ipAddresses[0].ipAddress)&quot;)<br>gcloud datastream connection-profiles create pg-source-profile \<br> - location=&quot;${REGION}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - display-name=&quot;pg-source-profile&quot; \<br> - type=POSTGRESQL \<br> - postgresql-hostname=&quot;${PG_PUBLIC_IP}&quot; \<br> - postgresql-port=5432 \<br> - postgresql-username=&quot;${PG_USER}&quot; \<br> - postgresql-password=&quot;${PG_PASSWORD}&quot; \<br> - postgresql-database=&quot;${PG_DATABASE}&quot; \<br> - static-ip-connectivity<br>gcloud datastream connection-profiles create bq-iceberg-dest-profile \<br> - location=&quot;${REGION}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - display-name=&quot;bq-iceberg-dest-profile&quot; \<br> - type=BIGQUERY \<br> - static-ip-connectivity</pre><h4><strong>Configuring </strong><strong>blmtConfig (BigLake Managed Tables for Apache Iceberg)</strong></h4><p>To instruct Datastream to create and stream into <strong>Apache Iceberg tables</strong> on GCS rather than proprietary BigQuery native storage, we pass a blmtConfig block inside bigquery_iceberg_destination_config.json.</p><blockquote><strong><em>Engineering Gotcha #2:</em></strong><em> When configuring </em><em>blmtConfig via the Datastream API/CLI:</em></blockquote><blockquote><em>1. </em><em>connectionName must follow the compact </em><em>{project}.{location}.{name} format (not the full </em><em>projects/…/locations/…/connections/… resource URI).</em></blockquote><blockquote><em>2. </em><em>rootPath must begin with a leading slash </em><em>/ (e.g., </em><em>&quot;/bronze&quot;).</em></blockquote><blockquote><em>3. Streaming into Apache Iceberg tables requires </em><em>&quot;appendOnly&quot;: {} write mode.</em></blockquote><pre>{<br>  &quot;singleTargetDataset&quot;: {<br>    &quot;datasetId&quot;: &quot;YOUR_PROJECT_ID:lakehouse_bronze&quot;<br>  },<br>  &quot;appendOnly&quot;: {},<br>  &quot;blmtConfig&quot;: {<br>    &quot;bucket&quot;: &quot;YOUR_PROJECT_ID-iceberg-lakehouse&quot;,<br>    &quot;rootPath&quot;: &quot;/bronze&quot;,<br>    &quot;connectionName&quot;: &quot;YOUR_PROJECT_ID.us-central1.iceberg-gcs-connection&quot;,<br>    &quot;fileFormat&quot;: &quot;PARQUET&quot;,<br>    &quot;tableFormat&quot;: &quot;ICEBERG&quot;<br>  }<br>}</pre><p>Create and start the stream:</p><pre>gcloud datastream streams create pg-to-iceberg-cdc-stream \<br> - location=&quot;${REGION}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - display-name=&quot;pg-to-iceberg-cdc-stream&quot; \<br> - source=&quot;pg-source-profile&quot; \<br> - postgresql-source-config=&quot;configs/postgresql_source_config.json&quot; \<br> - destination=&quot;bq-iceberg-dest-profile&quot; \<br> - bigquery-destination-config=&quot;configs/bigquery_iceberg_destination_config.json&quot; \<br> - backfill-all<br># Once the stream reaches NOT_STARTED state, start it:<br>gcloud datastream streams update pg-to-iceberg-cdc-stream \<br> - location=&quot;${REGION}&quot; \<br> - project=&quot;${PROJECT_ID}&quot; \<br> - state=RUNNING \<br> - update-mask=state</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o_-y7cqxoaeuTQANJSh0gA.png" /><figcaption><em>Figure 2: Google Cloud Datastream configured to stream PostgreSQL WAL changes directly into BigQuery Apache Iceberg tables on Cloud Storage in Append-Only mode.</em></figcaption></figure><h4><strong>Step 4: Simulating Real-Time OLTP Changes (</strong><strong>INSERT, </strong><strong>UPDATE, </strong><strong>DELETE)</strong></h4><p>Once the initial 5 seed rows are backfilled into lakehouse_bronze.public_orders, let’s simulate real-time operational transactions in PostgreSQL:</p><ul><li><strong>INSERT</strong> 2 new orders (ORD-1006 and ORD-1007)</li><li><strong>UPDATE</strong> ORD-1001 (PENDING → SHIPPED) and ORD-1002 (PROCESSING → DELIVERED, amount updated to 195.00)</li><li><strong>DELETE</strong> cancelled order ORD-1003</li></ul><pre>INSERT INTO public.orders (order_id, customer_id, product_category, order_status, total_amount, shipping_city, updated_at)<br>VALUES<br>(&#39;ORD-1006&#39;, &#39;CUST-06&#39;, &#39;Smartphones&#39;, &#39;PENDING&#39;, 699.00, &#39;Jakarta&#39;, NOW()),<br>(&#39;ORD-1007&#39;, &#39;CUST-07&#39;, &#39;Wearables&#39;, &#39;PAID&#39;, 149.90, &#39;Medan&#39;, NOW());<br>UPDATE public.orders<br>SET order_status = &#39;SHIPPED&#39;, updated_at = NOW()<br>WHERE order_id = &#39;ORD-1001&#39;;<br>UPDATE public.orders<br>SET order_status = &#39;DELIVERED&#39;, total_amount = 195.00, updated_at = NOW()<br>WHERE order_id = &#39;ORD-1002&#39;;<br>DELETE FROM public.orders<br>WHERE order_id = &#39;ORD-1003&#39;;</pre><h4><strong>Inspecting the Bronze Apache Iceberg Change Log in BigQuery</strong></h4><blockquote><strong><em>Engineering Gotcha #3:</em></strong><em> Unlike Datastream’s </em><em>MERGE mode (which adds a boolean </em><em>datastream_metadata.is_deleted field), Datastream’s </em><strong><em>APPEND_ONLY mode for Apache Iceberg</em></strong><em> uses the schema:</em></blockquote><blockquote><em>STRUCT&lt;uuid STRING, source_timestamp INT64, change_sequence_number STRING, change_type STRING, sort_keys ARRAY&lt;STRING&gt;&gt;.</em></blockquote><blockquote><em>Every mutation is recorded via </em><strong><em>datastream_metadata.change_type</em></strong><em> (</em><em>INSERT, </em><em>UPDATE-INSERT, </em><em>UPDATE-DELETE, or </em><em>DELETE).</em></blockquote><p>Let’s query our Bronze Iceberg table:</p><pre>SELECT<br>order_id,<br>customer_id,<br>order_status,<br>total_amount,<br>datastream_metadata.change_type,<br>TIMESTAMP_MICROS(datastream_metadata.source_timestamp) AS source_ts,<br>datastream_metadata.change_sequence_number AS csn<br>FROM `YOUR_PROJECT_ID.lakehouse_bronze.public_orders`<br>ORDER BY order_id, datastream_metadata.source_timestamp DESC, csn DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bUlgC4KFVLFVbyG-XcpzCQ.png" /><figcaption><em>Figure 3: Querying the Bronze Apache Iceberg table (</em><em>lakehouse_bronze.public_orders) in BigQuery, showing the immutable CDC event log (INSERT, UPDATE-INSERT, UPDATE-DELETE, and DELETE) captured in datastream_metadata.</em></figcaption></figure><h4><strong>Step 5: Turning Append-Only Bronze into an Upserted Silver Iceberg Table</strong></h4><p>Because our Bronze Iceberg table is an immutable append-only change log, downstream BI tools and AI agents need a clean, deduplicated <strong>Current State (Silver)</strong> layer where updates overwrite older versions and DELETE events remove deleted records.</p><p>Here are the two production patterns you can use on Google Cloud:</p><h4><strong>Pattern A: Zero-Latency Real-Time Deduplicated View</strong></h4><p>If you want zero replication delay between Bronze and Silver without storing a second copy of the data, create a view using QUALIFY ROW_NUMBER():</p><pre>CREATE OR REPLACE VIEW `YOUR_PROJECT_ID.lakehouse_silver.v_orders_current` AS<br>SELECT<br>order_id,<br>customer_id,<br>product_category,<br>order_status,<br>total_amount,<br>shipping_city,<br>updated_at,<br>TIMESTAMP_MICROS(datastream_metadata.source_timestamp) AS last_cdc_timestamp,<br>datastream_metadata.change_type AS last_change_type<br>FROM `YOUR_PROJECT_ID.lakehouse_bronze.public_orders`<br>WHERE datastream_metadata.change_type != &#39;UPDATE-DELETE&#39;<br>QUALIFY ROW_NUMBER() OVER (<br>PARTITION BY order_id<br>ORDER BY<br>datastream_metadata.source_timestamp DESC,<br>datastream_metadata.change_sequence_number DESC<br>) = 1<br>AND datastream_metadata.change_type != &#39;DELETE&#39;;</pre><h4><strong>Pattern B: Materialized Silver Apache Iceberg Table via ACID </strong><strong>MERGE INTO</strong></h4><p>When external engines (like <strong>Apache Spark</strong>, <strong>Trino</strong>, or <strong>PyIceberg</strong>) need to read the curated Silver table directly from GCS without executing a BigQuery SQL view, or when your Bronze log grows to hundreds of millions of events, materialize a <strong>Silver Managed Iceberg Table</strong> and run an incremental MERGE INTO:</p><pre>-- 1. Create the Silver Managed Apache Iceberg Table on GCS<br>CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.lakehouse_silver.orders` (<br>order_id STRING,<br>customer_id STRING,<br>product_category STRING,<br>order_status STRING,<br>total_amount NUMERIC,<br>shipping_city STRING,<br>updated_at TIMESTAMP,<br>last_cdc_timestamp TIMESTAMP<br>)<br>CLUSTER BY order_id<br>WITH CONNECTION `YOUR_PROJECT_ID.us-central1.iceberg-gcs-connection`<br>OPTIONS (<br>file_format = &#39;PARQUET&#39;,<br>table_format = &#39;ICEBERG&#39;,<br>storage_uri = &#39;gs://YOUR_PROJECT_ID-iceberg-lakehouse/silver/orders&#39;<br>);<br> <br>-- 2. Execute Atomic ACID MERGE INTO (Upserts + Physical Deletes)<br>MERGE `YOUR_PROJECT_ID.lakehouse_silver.orders` AS T<br>USING (<br>SELECT<br>order_id,<br>customer_id,<br>product_category,<br>order_status,<br>total_amount,<br>shipping_city,<br>updated_at,<br>TIMESTAMP_MICROS(datastream_metadata.source_timestamp) AS source_ts,<br>datastream_metadata.change_type AS change_type<br>FROM `YOUR_PROJECT_ID.lakehouse_bronze.public_orders`<br>WHERE datastream_metadata.change_type != &#39;UPDATE-DELETE&#39;<br>QUALIFY ROW_NUMBER() OVER (<br>PARTITION BY order_id<br>ORDER BY<br>datastream_metadata.source_timestamp DESC,<br>datastream_metadata.change_sequence_number DESC<br>) = 1<br>) AS S<br>ON T.order_id = S.order_id<br>WHEN MATCHED AND S.change_type = &#39;DELETE&#39; THEN<br>DELETE<br>WHEN MATCHED THEN<br>UPDATE SET<br>customer_id = S.customer_id,<br>product_category = S.product_category,<br>order_status = S.order_status,<br>total_amount = S.total_amount,<br>shipping_city = S.shipping_city,<br>updated_at = S.updated_at,<br>last_cdc_timestamp = S.source_ts<br>WHEN NOT MATCHED AND S.change_type != &#39;DELETE&#39; THEN<br>INSERT (<br>order_id, customer_id, product_category, order_status,<br>total_amount, shipping_city, updated_at, last_cdc_timestamp<br>)<br>VALUES (<br>S.order_id, S.customer_id, S.product_category, S.order_status,<br>S.total_amount, S.shipping_city, S.updated_at, S.source_ts<br>);<br><br>-- 3. Export Iceberg Metadata Snapshot for Multi-Engine Readers (Spark / PyIceberg)<br>EXPORT TABLE METADATA FROM `YOUR_PROJECT_ID.lakehouse_silver.orders`;</pre><p>Querying lakehouse_silver.orders now returns the exact 6 active orders matching Cloud SQL (ORD-1001 as SHIPPED, ORD-1002 as DELIVERED at 195.00, ORD-1003 deleted, and ORD-1006/ORD-1007 inserted)!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zYHOT7ty70qxc4wC5KHdpA.png" /><figcaption><em>Figure 4: The curated Silver Apache Iceberg table (lakehouse_silver.orders) after atomic ACID MERGE INTO, reflecting updated order statuses (ORD-1001, ORD-1002), newly inserted orders (ORD-1006, ORD-1007), and physical removal of deleted order ORD-1003.</em></figcaption></figure><h4><strong>Verifying Physical Open Parquet &amp; Iceberg Metadata Files in Cloud Storage</strong></h4><p>Finally, let’s verify that both our Bronze and Silver tables are stored as genuine open-format <strong>Apache Iceberg</strong> tables inside our customer-owned GCS bucket (gs://${GCS_BUCKET}/):</p><pre>gcloud storage ls - recursive &quot;gs://${GCS_BUCKET}/&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lq58SQ1KqQOF2yDp60c5tA.png" /><figcaption><em>Figure 5: Inside our Google Cloud Storage bucket (`gs://…/bronze` and `gs://…/silver/orders`), verifying open Parquet data files and exported Apache Iceberg V2 metadata snapshots.</em></figcaption></figure><h4><strong>When to Use Pattern A (View) vs. Pattern B (Materialized Iceberg </strong><strong>MERGE)</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2OGfHH8e_GFrAjjCkB-KRw.jpeg" /></figure><h4><strong>Conclusion</strong></h4><p>By combining <strong>Cloud SQL for PostgreSQL</strong>, <strong>Google Cloud Datastream</strong>, and <strong>BigQuery Managed Tables for Apache Iceberg</strong>, you get the best of both worlds: a zero-maintenance, serverless CDC pipeline and an open Parquet/Iceberg lakehouse stored in your own Cloud Storage bucket.</p><p>You can find all the complete, reproducible bash and SQL scripts for this tutorial in my GitHub repository:</p><p>- <strong>GitHub Repository:</strong> <a href="https://github.com/saipulrx/cdc_lakehouse_gcp">https://github.com/saipulrx/cdc_lakehouse_gcp</a></p><p><em>Built for the Google Cloud Lakehouse/Iceberg Content Sprint 2026</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6e114861469e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/real-time-cdc-to-the-open-lakehouse-streaming-cloud-sql-postgresql-changes-into-apache-iceberg-6e114861469e">Real-Time CDC to the Open Lakehouse: Streaming Cloud SQL (PostgreSQL) Changes into Apache Iceberg…</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build an AI-Powered Agent Diet Planner with Streamlit, Gemini Pro, Google ADK, Vertex AI and…]]></title>
            <link>https://medium.com/google-cloud/build-an-ai-powered-agent-diet-planner-with-streamlit-gemini-pro-google-adk-vertex-ai-and-da543d955ff2?source=rss-645d0d2f0212------2</link>
            <guid isPermaLink="false">https://medium.com/p/da543d955ff2</guid>
            <category><![CDATA[streamlit]]></category>
            <category><![CDATA[vertex-ai]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[geminipro]]></category>
            <category><![CDATA[agent-development-kit]]></category>
            <dc:creator><![CDATA[Muhammad Saipul Rohman]]></dc:creator>
            <pubDate>Thu, 26 Jun 2025 05:05:07 GMT</pubDate>
            <atom:updated>2025-06-26T05:05:07.723Z</atom:updated>
            <content:encoded><![CDATA[<h3>Build an AI-Powered Agent Diet Planner with Streamlit, Gemini Pro, Google ADK, Vertex AI and BigQuery</h3><p>With rising health consciousness, personalized nutrition has become a priority. However, consulting dietitians regularly can be expensive and time-consuming. AI-powered Agent Diet planner solutions now offer an alternative by generating custom meal plans instantly.</p><p>This article demonstrates how modern Generative AI (Gemini Pro), cloud computing (Google Cloud), and low-code tools (Streamlit) can create an intelligent diet planner that:</p><ul><li>Adapts to individual health metrics</li><li>Provides scientifically-backed recommendations</li><li>Stores user history securely</li><li>Scales affordably</li></ul><p>The solution is particularly valuable for:</p><ul><li>Health enthusiasts tracking macros</li><li>Fitness beginners needing guidance</li><li>Clinicians supporting patients remotely</li></ul><p>The app will generate personalized meal plans based on user biometrics and store data for future reference.</p><h3><strong>Architecture Agent Diet Planner Apps</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/584/0*cBuYUyQ7rELufZ8e" /><figcaption>Architecture Agent Diet Planner Apps</figcaption></figure><p><strong>Solution Overview</strong></p><ul><li>Streamlit: Frontend interface for user inputs</li><li>Gemini Pro 2.5 LLM: Generate diet plans via Vertex AI</li><li>Google Agent Development Kit: Backend logic for plan generation</li><li>BigQuery: Secure user data storage</li></ul><h3>Key Features</h3><ol><li>User Inputs</li></ol><ul><li>Weight, height, age, and gender</li><li>Automatic BMI calculation with health status</li></ul><p>2. AI-Powered Diet Plans</p><ul><li>Generated by Gemini Pro 2.5 (latest LLM from Google)</li><li>Includes calorie targets, macros, and meal timing</li></ul><p>3. Data Storage</p><ul><li>User profiles and diet plans saved in BigQuery</li></ul><p>4. Export Options</p><ul><li>Download plans as Markdown</li></ul><h4>How It Works</h4><h4>1. User Input &amp; BMI Calculation</h4><p>The app collects:</p><ul><li>Weight (kg)</li><li>Height (cm)</li><li>Age</li><li>Gender</li></ul><p>Using this data, it calculates BMI and categorizes it:</p><ul><li>Underweight (&lt;18.5) → Suggests calorie surplus</li><li>Healthy (18.5–25) → Maintenance plan</li><li>Overweight (25–30) → Weight loss focus</li><li>Obese (30+) → Recommends medical consultation</li></ul><pre>def calculate_bmi(weight, height):<br>    height_m = height / 100  <br>    bmi = weight / (height_m ** 2)  <br>    return round(bmi, 1)  </pre><h4>2. AI Diet Plan Generation</h4><p>Using Gemini Pro, the app generates a 7-day meal plan with:<br>✅ Calorie targets<br>✅ Macronutrient breakdown<br>✅ Meal timing &amp; portion sizes</p><pre>prompt = f&quot;Generate a diet plan for a {gender}, {age}y, {weight}kg, {height}cm.&quot;  <br>response = Gemini.generate(prompt)  </pre><h4>3. Data Storage in BigQuery</h4><p>User profiles and diet plans are stored in Google BigQuery for future reference.</p><pre>from google.cloud import bigquery  <br>client = bigquery.Client()  <br>client.insert_rows(table, [user_data])  </pre><h4>4. Export as Markdown</h4><p>Users can download their diet plan in markdown format for offline use.</p><pre># Display generated plan<br>    if st.session_state.diet_plan:<br>        st.subheader(&quot;Your Personalized Diet Plan&quot;)<br>        st.markdown(&quot;---&quot;)<br>        st.markdown(st.session_state.diet_plan)<br>        <br>        # Download button<br>        st.download_button(<br>            label=&quot;Download Plan&quot;,<br>            data=st.session_state.diet_plan,<br>            file_name=&quot;my_diet_plan.md&quot;,<br>            mime=&quot;text/markdown&quot;<br>        )</pre><h4>Why This Stack?</h4><p>✔ Streamlit: Fast UI development<br>✔ Gemini Pro: High-quality AI responses<br>✔ BigQuery: Secure, scalable data storage</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Vwr-YRRJg-aVMvmStbxOUA.png" /><figcaption>Diet Planner Agent apps after Generate Diet Plan</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*e6kvbtAOG7EQYcC4nhe2Gw.png" /><figcaption>Daily Calories, Macronutrients, Meal Timing</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*T68gApV26CQXJwgDKcJ8QA.png" /><figcaption>Day 1 till Day 3 Detailed Meal Plan</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z5nHkHsRBA4HlU0Uxx6Wpw.png" /><figcaption>Day 4 till Day 7 Detailed Meal Plan</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P1zUhCtK4UxWNN52Szg0Bw.png" /><figcaption>Hydration Guidance and Plan History</figcaption></figure><h3>Enhancements and Future Possibilities</h3><p>This is a foundational agent, and there are many ways to enhance it:</p><ul><li><strong>Activity Level Input:</strong> Allow users to specify their activity level more precisely to get a more accurate TDEE.</li><li><strong>Dietary Preferences/Restrictions:</strong> Integrate inputs for vegetarian, vegan, gluten-free, allergies, etc., and instruct Gemini to incorporate these.</li><li><strong>Meal Logging &amp; Tracking:</strong> Add functionality for users to log their meals and track progress.</li><li><strong>Personalized Coaching:</strong> Leverage Gemini’s conversational abilities to provide ongoing support and answer diet-related questions.</li><li><strong>Cost and Availability:</strong> Ask Gemini to consider common ingredients or budget-friendly options.</li><li><strong>Interactive Feedback:</strong> Allow users to give feedback on meal suggestions, which can be used to refine future recommendations.</li><li><strong>User Authentication:</strong> For a more robust application, implement user login and profile management.</li><li>Deploy to Cloud Run or Google Kubernetes Engine : For more scalable application, this apps can deploy to Cloud Run or Google Kubernetes Engine</li></ul><h3>Conclusion</h3><p>Building an AI-powered diet planner with Streamlit, Gemini Pro, and Google Cloud demonstrates how modern technology can make personalized nutrition accessible to everyone. This project combines:</p><ul><li>AI intelligence (Gemini Pro for dynamic meal plans)</li><li>Cloud efficiency (BigQuery for storage)</li><li>User-friendly design (Streamlit for simple UI)</li></ul><p>Whether you’re a developer exploring AI applications or a health enthusiast seeking smarter meal planning, this solution offers a scalable, cost-effective approach.</p><p>Full Source Code : <a href="https://github.com/saipulrx/agent_diet_planner">https://github.com/saipulrx/agent_diet_planner</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=da543d955ff2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/build-an-ai-powered-agent-diet-planner-with-streamlit-gemini-pro-google-adk-vertex-ai-and-da543d955ff2">Build an AI-Powered Agent Diet Planner with Streamlit, Gemini Pro, Google ADK, Vertex AI and…</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL Batch Data Pipeline Menggunakan Apache Airflow, Docker, Google Cloud Storage dan BigQuery]]></title>
            <link>https://medium.com/google-cloud-indonesia/etl-batch-data-pipeline-menggunakan-apache-airflow-docker-google-cloud-storage-dan-bigquery-193fbe979a9a?source=rss-645d0d2f0212------2</link>
            <guid isPermaLink="false">https://medium.com/p/193fbe979a9a</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[google-cloud-storage]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Muhammad Saipul Rohman]]></dc:creator>
            <pubDate>Thu, 24 Aug 2023 04:42:36 GMT</pubDate>
            <atom:updated>2023-08-24T04:42:36.923Z</atom:updated>
            <content:encoded><![CDATA[<p>Di era Data is New Oil sekarang pada kebutuhan untuk mengolah data sehingga bisa menjadi insight sangat diperlukan di semua perusahaan untuk mengambil keputusan yang tepat agar bisa bersaing dengan para kompetitor. Untuk pengolahan data di dunia data engineering sendiri terdiri dari 3 jenis yaitu EL, ELT dan ETL. Yang sudah lama digunakan untuk pengolahan data yaitu ETL. Untuk menjalankan pengolahan data atau bisa disebut data pipeline itu sendiri terdiri dari 3 jenis yaitu Batch, Streaming/Real Time dan Hybrid.</p><p>Di artikel ini saya akan membahas bagaimana membuat ETL Batch Data Pipeline menggunakan Apache Airflow di docker, Google Cloud Storage dan BigQuery.</p><p>Untuk servis Google Cloud yang digunakan pada artikel ini yaitu :</p><ul><li>Google BigQuery untuk penyimpanan data setelah di lakukan proses ETL</li><li>Google Cloud Storage untuk menyimpan file parquet dari local ke Google Cloud</li><li>IAM Service Accounts untuk memberikan akses agar apache airflow bisa mengakses BigQuery dan cloud storage</li><li>VPC Firewalls untuk membuka port 8080 web server apache airflow</li></ul><h3><strong>Data Architecture</strong></h3><p>Untuk Data Architecture dari artikel ini yaitu :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/652/1*SKxqY4vOGN7esUahM4LFWg.jpeg" /><figcaption>Data Architecture Batch Data Pipeline Apache Airflow, GCS dan BigQuery</figcaption></figure><h3><strong>Prerequisite:</strong></h3><ul><li>Sudah membuat IAM service account dengan role at least BigQuery Admin, BigQuery Job User dan Storage Admin dan download service account key nya ke local komputer</li><li>Sudah setup firewall di VPC dengan membuka port 8080 untuk web server airflow dan port 5432 untuk port postgresql database</li><li>Sudah membuat dataset di BigQuery untuk menyimpan data hasil ETL ke BigQuery</li><li>Sudah membuat bucket di Cloud Storage untuk menyimpan raw data dan data hasil transformasi</li><li>Sudah terinstall docker dan docker compose di local komputer</li></ul><h3><strong>Setup Apache Airflow di Docker</strong></h3><p>Untuk Apache Airflow di artikel ini menggunakan docker compose default dari apache airflow dengan tambahan Dockerfile yang berisi instalasi Google Cloud SDK dan config yang lain. Untuk isi Dockerfile nya yaitu:</p><pre>FROM apache/airflow:2.6.3<br><br>ENV AIRFLOW_HOME=/opt/airflow<br><br>USER root<br>RUN apt-get update -qq &amp;&amp; apt-get install vim -qqq<br><br>SHELL [&quot;/bin/bash&quot;, &quot;-o&quot;, &quot;pipefail&quot;, &quot;-e&quot;, &quot;-u&quot;, &quot;-x&quot;, &quot;-c&quot;]<br><br>ARG CLOUD_SDK_VERSION=322.0.0<br>ENV GCLOUD_HOME=/home/google-cloud-sdk<br><br>ENV PATH=&quot;${GCLOUD_HOME}/bin/:${PATH}&quot;<br><br>RUN DOWNLOAD_URL=&quot;https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-sdk-${CLOUD_SDK_VERSION}-linux-x86_64.tar.gz&quot; \<br>    &amp;&amp; TMP_DIR=&quot;$(mktemp -d)&quot; \<br>    &amp;&amp; curl -fL &quot;${DOWNLOAD_URL}&quot; --output &quot;${TMP_DIR}/google-cloud-sdk.tar.gz&quot; \<br>    &amp;&amp; mkdir -p &quot;${GCLOUD_HOME}&quot; \<br>    &amp;&amp; tar xzf &quot;${TMP_DIR}/google-cloud-sdk.tar.gz&quot; -C &quot;${GCLOUD_HOME}&quot; --strip-components=1 \<br>    &amp;&amp; &quot;${GCLOUD_HOME}/install.sh&quot; \<br>       --bash-completion=false \<br>       --path-update=false \<br>       --usage-reporting=false \<br>       --quiet \<br>    &amp;&amp; rm -rf &quot;${TMP_DIR}&quot; \<br>    &amp;&amp; gcloud --version<br><br>WORKDIR $AIRFLOW_HOME<br><br>COPY scripts scripts<br>COPY dataset dataset<br><br>RUN chmod +x scripts<br><br>USER $AIRFLOW_UID</pre><p>Untuk isi docker compose nya yaitu :</p><pre>---<br>version: &#39;3&#39;<br>x-airflow-common:<br>  &amp;airflow-common<br>  # In order to add custom dependencies or upgrade provider packages you can use your extended image.<br>  # Comment the image line, place your Dockerfile in the directory where you placed the docker-compose.yaml<br>  # and uncomment the &quot;build&quot; line below, Then run `docker-compose build` to build the images.<br>  build:<br>    context: .<br>    dockerfile: ./Dockerfile<br>  environment:<br>    &amp;airflow-common-env<br>    AIRFLOW__CORE__EXECUTOR: CeleryExecutor<br>    AIRFLOW__CORE__SQL_ALCHEMY_CONN: postgresql+psycopg2://airflow:airflow@postgres/airflow<br>    AIRFLOW__CELERY__RESULT_BACKEND: db+postgresql://airflow:airflow@postgres/airflow<br>    AIRFLOW__CELERY__BROKER_URL: redis://:@redis:6379/0<br>    AIRFLOW__CORE__FERNET_KEY: &#39;&#39;<br>    AIRFLOW__CORE__DAGS_ARE_PAUSED_AT_CREATION: &#39;true&#39;<br>    AIRFLOW__CORE__LOAD_EXAMPLES: &#39;false&#39;<br>    AIRFLOW__API__AUTH_BACKEND: &#39;airflow.api.auth.backend.basic_auth&#39;<br>    _PIP_ADDITIONAL_REQUIREMENTS: ${_PIP_ADDITIONAL_REQUIREMENTS:-}<br>    GOOGLE_APPLICATION_CREDENTIALS: /.google/credentials/serious-music-345014-7adc51c1a770.json<br>    AIRFLOW_CONN_GOOGLE_CLOUD_DEFAULT: &#39;google-cloud-platform://?extra__google_cloud_platform__key_path=/.google/credentials/serious-music-345014-7adc51c1a770.json&#39;<br><br>    # TODO: Please change GCP_PROJECT_ID &amp; GCP_GCS_BUCKET, as per your config<br>    GCP_PROJECT_ID: &#39;serious-music-345014&#39;<br>    GCP_GCS_BUCKET: &#39;demo-de-class&#39;<br><br>  volumes:<br>    - ./dags:/opt/airflow/dags<br>    - ./logs:/opt/airflow/logs<br>    - ./plugins:/opt/airflow/plugins<br>    - ./credentials/serious-music-345014-7adc51c1a770.json:/.google/credentials/serious-music-345014-7adc51c1a770.json<br>    - ./dataset/raw:/opt/airflow/dataset/raw<br>    - ./dataset/transform:/opt/airflow/dataset/transform<br><br>  user: &quot;${AIRFLOW_UID:-50000}:0&quot;<br>  depends_on:<br>    &amp;airflow-common-depends-on<br>    redis:<br>      condition: service_healthy<br>    postgres:<br>      condition: service_healthy<br><br>services:<br>  postgres:<br>    image: postgres:13<br>    environment:<br>      POSTGRES_USER: airflow<br>      POSTGRES_PASSWORD: airflow<br>      POSTGRES_DB: airflow<br>    volumes:<br>      - postgres-db-volume:/var/lib/postgresql/data<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;pg_isready&quot;, &quot;-U&quot;, &quot;airflow&quot;]<br>      interval: 5s<br>      retries: 5<br>    restart: always<br><br>  redis:<br>    image: redis:latest<br>    expose:<br>      - 6379<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;redis-cli&quot;, &quot;ping&quot;]<br>      interval: 5s<br>      timeout: 30s<br>      retries: 50<br>    restart: always<br><br>  airflow-webserver:<br>    &lt;&lt;: *airflow-common<br>    command: webserver<br>    ports:<br>      - 8080:8080<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;curl&quot;, &quot;--fail&quot;, &quot;http://localhost:8080/health&quot;]<br>      interval: 10s<br>      timeout: 10s<br>      retries: 5<br>    restart: always<br>    depends_on:<br>      &lt;&lt;: *airflow-common-depends-on<br>      airflow-init:<br>        condition: service_completed_successfully<br><br>  airflow-scheduler:<br>    &lt;&lt;: *airflow-common<br>    command: scheduler<br>    healthcheck:<br>      test: [&quot;CMD-SHELL&quot;, &#39;airflow jobs check --job-type SchedulerJob --hostname &quot;$${HOSTNAME}&quot;&#39;]<br>      interval: 10s<br>      timeout: 10s<br>      retries: 5<br>    restart: always<br>    depends_on:<br>      &lt;&lt;: *airflow-common-depends-on<br>      airflow-init:<br>        condition: service_completed_successfully<br><br>  airflow-worker:<br>    &lt;&lt;: *airflow-common<br>    command: celery worker<br>    healthcheck:<br>      test:<br>        - &quot;CMD-SHELL&quot;<br>        - &#39;celery --app airflow.executors.celery_executor.app inspect ping -d &quot;celery@$${HOSTNAME}&quot;&#39;<br>      interval: 10s<br>      timeout: 10s<br>      retries: 5<br>    environment:<br>      &lt;&lt;: *airflow-common-env<br>      # Required to handle warm shutdown of the celery workers properly<br>      # See https://airflow.apache.org/docs/docker-stack/entrypoint.html#signal-propagation<br>      DUMB_INIT_SETSID: &quot;0&quot;<br>    restart: always<br>    depends_on:<br>      &lt;&lt;: *airflow-common-depends-on<br>      airflow-init:<br>        condition: service_completed_successfully<br><br>  airflow-triggerer:<br>    &lt;&lt;: *airflow-common<br>    command: triggerer<br>    healthcheck:<br>      test: [&quot;CMD-SHELL&quot;, &#39;airflow jobs check --job-type TriggererJob --hostname &quot;$${HOSTNAME}&quot;&#39;]<br>      interval: 10s<br>      timeout: 10s<br>      retries: 5<br>    restart: always<br>    depends_on:<br>      &lt;&lt;: *airflow-common-depends-on<br>      airflow-init:<br>        condition: service_completed_successfully<br><br>  airflow-init:<br>    &lt;&lt;: *airflow-common<br>    entrypoint: /bin/bash<br>    # yamllint disable rule:line-length<br>    command:<br>      - -c<br>      - |<br>        function ver() {<br>          printf &quot;%04d%04d%04d%04d&quot; $${1//./ }<br>        }<br>        airflow_version=$$(gosu airflow airflow version)<br>        airflow_version_comparable=$$(ver $${airflow_version})<br>        min_airflow_version=2.2.0<br>        min_airflow_version_comparable=$$(ver $${min_airflow_version})<br>        if (( airflow_version_comparable &lt; min_airflow_version_comparable )); then<br>          echo<br>          echo -e &quot;\033[1;31mERROR!!!: Too old Airflow version $${airflow_version}!\e[0m&quot;<br>          echo &quot;The minimum Airflow version supported: $${min_airflow_version}. Only use this or higher!&quot;<br>          echo<br>          exit 1<br>        fi<br>        if [[ -z &quot;${AIRFLOW_UID}&quot; ]]; then<br>          echo<br>          echo -e &quot;\033[1;33mWARNING!!!: AIRFLOW_UID not set!\e[0m&quot;<br>          echo &quot;If you are on Linux, you SHOULD follow the instructions below to set &quot;<br>          echo &quot;AIRFLOW_UID environment variable, otherwise files will be owned by root.&quot;<br>          echo &quot;For other operating systems you can get rid of the warning with manually created .env file:&quot;<br>          echo &quot;    See: https://airflow.apache.org/docs/apache-airflow/stable/start/docker.html#setting-the-right-airflow-user&quot;<br>          echo<br>        fi<br>        one_meg=1048576<br>        mem_available=$$(($$(getconf _PHYS_PAGES) * $$(getconf PAGE_SIZE) / one_meg))<br>        cpus_available=$$(grep -cE &#39;cpu[0-9]+&#39; /proc/stat)<br>        disk_available=$$(df / | tail -1 | awk &#39;{print $$4}&#39;)<br>        warning_resources=&quot;false&quot;<br>        if (( mem_available &lt; 4000 )) ; then<br>          echo<br>          echo -e &quot;\033[1;33mWARNING!!!: Not enough memory available for Docker.\e[0m&quot;<br>          echo &quot;At least 4GB of memory required. You have $$(numfmt --to iec $$((mem_available * one_meg)))&quot;<br>          echo<br>          warning_resources=&quot;true&quot;<br>        fi<br>        if (( cpus_available &lt; 2 )); then<br>          echo<br>          echo -e &quot;\033[1;33mWARNING!!!: Not enough CPUS available for Docker.\e[0m&quot;<br>          echo &quot;At least 2 CPUs recommended. You have $${cpus_available}&quot;<br>          echo<br>          warning_resources=&quot;true&quot;<br>        fi<br>        if (( disk_available &lt; one_meg * 10 )); then<br>          echo<br>          echo -e &quot;\033[1;33mWARNING!!!: Not enough Disk space available for Docker.\e[0m&quot;<br>          echo &quot;At least 10 GBs recommended. You have $$(numfmt --to iec $$((disk_available * 1024 )))&quot;<br>          echo<br>          warning_resources=&quot;true&quot;<br>        fi<br>        if [[ $${warning_resources} == &quot;true&quot; ]]; then<br>          echo<br>          echo -e &quot;\033[1;33mWARNING!!!: You have not enough resources to run Airflow (see above)!\e[0m&quot;<br>          echo &quot;Please follow the instructions to increase amount of resources available:&quot;<br>          echo &quot;   https://airflow.apache.org/docs/apache-airflow/stable/start/docker.html#before-you-begin&quot;<br>          echo<br>        fi<br>        mkdir -p /sources/logs /sources/dags /sources/plugins<br>        chown -R &quot;${AIRFLOW_UID}:0&quot; /sources/{logs,dags,plugins}<br>        exec /entrypoint airflow version<br>    # yamllint enable rule:line-length<br>    environment:<br>      &lt;&lt;: *airflow-common-env<br>      _AIRFLOW_DB_UPGRADE: &#39;true&#39;<br>      _AIRFLOW_WWW_USER_CREATE: &#39;true&#39;<br>      _AIRFLOW_WWW_USER_USERNAME: ${_AIRFLOW_WWW_USER_USERNAME:-airflow}<br>      _AIRFLOW_WWW_USER_PASSWORD: ${_AIRFLOW_WWW_USER_PASSWORD:-airflow}<br>    user: &quot;0:0&quot;<br>    volumes:<br>      - .:/sources<br><br>  airflow-cli:<br>    &lt;&lt;: *airflow-common<br>    profiles:<br>      - debug<br>    environment:<br>      &lt;&lt;: *airflow-common-env<br>      CONNECTION_CHECK_MAX_COUNT: &quot;0&quot;<br>    # Workaround for entrypoint issue. See: https://github.com/apache/airflow/issues/16252<br>    command:<br>      - bash<br>      - -c<br>      - airflow<br><br>  flower:<br>    &lt;&lt;: *airflow-common<br>    command: celery flower<br>    ports:<br>      - 5555:5555<br>    healthcheck:<br>      test: [&quot;CMD&quot;, &quot;curl&quot;, &quot;--fail&quot;, &quot;http://localhost:5555/&quot;]<br>      interval: 10s<br>      timeout: 10s<br>      retries: 5<br>    restart: always<br>    depends_on:<br>      &lt;&lt;: *airflow-common-depends-on<br>      airflow-init:<br>        condition: service_completed_successfully<br><br>volumes:<br>  postgres-db-volume:</pre><p>Untuk isi docker compose ada yang perlu yaitu di komentar #TO DO di sesuaikan dengan GCP project ID dan GCP GCS Bucket masing — masing. Dan juga untuk folder credentials yang berisi service account key json file disesuaikan folder nya. Jangan lupa untuk membuat folder dataset/raw dan dataset/transform serta folder scripts dan di dalam folder scripts buat file entrypoint.sh di satu folder dengan Dockerfile dan docker compose.</p><p>Untuk isi entrypoint.sh yaitu :</p><pre>#!/usr/bin/env bash<br>export GOOGLE_APPLICATION_CREDENTIALS=${GOOGLE_APPLICATION_CREDENTIALS}<br>export AIRFLOW_CONN_GOOGLE_CLOUD_DEFAULT=${AIRFLOW_CONN_GOOGLE_CLOUD_DEFAULT}<br><br>airflow db upgrade<br><br>airflow users create -r Admin -u admin -p admin -e admin@example.com -f admin -l airflow<br># &quot;$_AIRFLOW_WWW_USER_USERNAME&quot; -p &quot;$_AIRFLOW_WWW_USER_PASSWORD&quot;<br><br>airflow webserver</pre><p>Untuk struktur folder-nya yaitu :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/668/1*A6PM1ePnt_Y0Eaz_iQYdQw.png" /><figcaption>Struktur folder Apache Airflow</figcaption></figure><p>Untuk file gambar data architecture, explore_data.ipynb bisa di abaikan saja karena tidak mempengaruhi kerja Apache Airflow. Untuk folder dags, logs dan plugins akan terbentuk sendiri setelah menjalankan file docker-compose.yml.</p><h3><strong>Data Pipeline di Apache Airflow</strong></h3><p>Untuk flow data pipeline di Apache Airflow pada artikel ini ada 2 yaitu flow data pipeline menggunakan task group dan flow data pipeline menggunakan task group yang sudah didetailkan</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*Vnyfz6QSFS-Ujs5Dxe4sVg.png" /><figcaption>Flow data pipeline menggunakan task group</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*ArAePE6J6UOyo9wfvxcIpw.png" /><figcaption>Flow data pipeline menggunakan task group yang sudah didetailkan</figcaption></figure><p>Source code dags airflow yang ada di artikel ini menggunakan feature di airflow yaitu TaskGroup. Apa itu TaskGroup? Secara simple TaskGroup adalah membuat group untuk mengelompokan beberapa task untuk mempermudah dalam pembuatan workflow di data pipeline. Untuk detailnya bisa refer ke artikel <a href="https://marclamberti.com/blog/airflow-taskgroups-all-you-need-to-know/">ini</a></p><p>Untuk Data Pipeline di Apache Airflow ada 3 bagian yaitu:</p><ul><li><strong>Raw_data</strong> : TaskGroup yang berisi 4 task yaitu:</li></ul><p>— Download_dataset_task: task yang bertujuan untuk mendownload data berupa file parquet dari website NYC Taxi TLC di <a href="https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page">sini</a></p><p>— Upload_zone_map : Untuk data zone map sudah di download secara manual sehingga task ini bertujuan untuk upload data berupa csv file ini ke GCS bucket di folder raw</p><p>— Upload_yellow_taxi_trip : task ini bertujuan untuk upload data hasil dari task download_dataset_task ke GCS bucket di folder raw</p><p>— Load_zone_map_to_bigquery : task ini bertujuan untuk load data zone map ke BigQuery</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MVGTtKUyt4jJ9lOPYJM88g.png" /><figcaption>source code dags TaskGroup raw_data</figcaption></figure><p>· <strong>Transform_data</strong> : TaskGroup yang berisi 2 task yaitu :</p><p>— Transform_raw_data : task ini bertujuan untuk memfilter data dimana trip_distance tidak boleh memiliki nilai 0</p><p>— Upload_transform_data_to_gcs : task ini bertujuan untuk upload data hasil transformasi ke GCS bucket di folder transforms</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*axk2SyiqlIzL1hy_Swu5Ew.png" /><figcaption>source code fungsi transform_data</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1022/1*NhW5lgORAM3adJapXwgtdQ.png" /><figcaption>source code dags TaskGroup transform_data</figcaption></figure><p>· <strong>Load_transform_data_to_bigquery</strong></p><p>Task ini bertujuan untuk load hasil transform data ke BigQuery</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X5W1Ux_4tqV7SF5Ei1TsYA.png" /><figcaption>source code task load_transform_data_to_bigquery</figcaption></figure><p>Untuk full source code nya bisa di lihat di <a href="https://github.com/saipulrx/ETL_Airflow_BigQuery/blob/main/airflow_bigquery/dags/etl_gcs_to_bq.py">https://github.com/saipulrx/ETL_Airflow_BigQuery/blob/main/airflow_bigquery/dags/etl_gcs_to_bq.py</a></p><p>Setelah selesai membuat python dags script jangan lupa di simpan di folder dags agar bisa tampil di web server apache airflow</p><h3><strong>Run Apache Airflow di docker</strong></h3><p>Untuk menjalankan apache airflow di docker dengan langkah — langkah sebagai berikut :</p><ul><li>Build docker image terlebih dahulu(hanya pertama kali) menggunakan perintah</li></ul><pre>sudo docker compose build</pre><ul><li>Inisialisasi airflow scheduler, database dan config yang lain</li></ul><pre>docker compose up airflow-init</pre><ul><li>Menjalankan semua services yang ada di docker container</li></ul><pre>docker compose up</pre><ul><li>Cek status docker</li></ul><pre>docker ps</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AreDOz3mUcWltngzF6ECxA.png" /><figcaption>Status docker container</figcaption></figure><ul><li>Buka browser favorit kalian lalu ketik localhost:8080 untuk membuka web server apache airflow. Jika di minta login maka ketik username <em>airflow</em> dan password <em>airflow</em></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O3A9aTbOS3EA2uwBGSXWKw.png" /><figcaption>Web Server Apache Airflow</figcaption></figure><h3><strong>Hasil ETL batch data Pipeline</strong></h3><p>Di GCS Bucket sudah otomatis dibuat 2 folder raw dan transform setelah menjalankan data pipeline di airflow</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*zCgZknnQYLdFflIsUp_KhA.png" /><figcaption>GCS Bucket</figcaption></figure><p>Di GCS bucket folder raw sudah otomatis ada file hasil dari task download dari web data source di airflow</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*5HLICS3nfrS_vak_SmMjDQ.png" /><figcaption>GCS bucket folder raw</figcaption></figure><p>Di GCS bucket folder transform sudah ada file parquet hasil dari task transform data di airflow</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*RWIgarw0rsMNm9q61_KhkQ.png" /><figcaption>GCS bucket folder transform</figcaption></figure><p>Di BigQuery sudah ada 2 table yaitu green_trip_data dan taxi_zone_lookup</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*6UwXPFkuVPGmXwLkK93wsQ.png" /><figcaption>BigQuery table green_trip_data</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*cqA1Cwwfh7OmEiLLpb3wng.png" /><figcaption>BigQuery table taxi_zone_lookup</figcaption></figure><p><strong>What’s Next ?</strong></p><ul><li>Bisa di tambahkan dashboard untuk menampilkan data green_trip_data</li><li>Jika ingin lebih mudah di-<em>maintan</em> bisa menggunakan Google Cloud Managed Service Apache Airflow yaitu Google Cloud Composer</li><li>Bisa di tambahkan CI/CD agar sebelum di push ke git branch master, bisa di review terlebih dahulu oleh Data Engineer member yang lain</li></ul><p>Referensi :</p><ul><li><a href="https://airflow.apache.org/docs/">https://airflow.apache.org/docs/</a></li><li><a href="https://github.com/DataTalksClub/data-engineering-zoomcamp/tree/main/cohorts/2022/week_2_data_ingestion">https://github.com/DataTalksClub/data-engineering-zoomcamp/tree/main/cohorts/2022/week_2_data_ingestion</a></li><li><a href="https://marclamberti.com/blog/airflow-taskgroups-all-you-need-to-know/">https://marclamberti.com/blog/airflow-taskgroups-all-you-need-to-know/</a></li><li><a href="https://registry.astronomer.io/providers/apache-airflow-providers-google/versions/10.6.0">https://registry.astronomer.io/providers/apache-airflow-providers-google/versions/10.6.0</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=193fbe979a9a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud-indonesia/etl-batch-data-pipeline-menggunakan-apache-airflow-docker-google-cloud-storage-dan-bigquery-193fbe979a9a">ETL Batch Data Pipeline Menggunakan Apache Airflow, Docker, Google Cloud Storage dan BigQuery</a> was originally published in <a href="https://medium.com/google-cloud-indonesia">Google Cloud Indonesia</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build Batch Data Pipeline using Airflow MWAA, glue and AWS Redshift]]></title>
            <link>https://msaipulr.medium.com/build-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-9bdd87d2af6e?source=rss-645d0d2f0212------2</link>
            <guid isPermaLink="false">https://medium.com/p/9bdd87d2af6e</guid>
            <category><![CDATA[aws-mwaa]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[amazon-redshift]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <dc:creator><![CDATA[Muhammad Saipul Rohman]]></dc:creator>
            <pubDate>Fri, 13 Jan 2023 17:15:11 GMT</pubDate>
            <atom:updated>2023-01-13T17:30:54.369Z</atom:updated>
            <content:encoded><![CDATA[<p>Halo semua, pada artikel ini saya akan sharing mengenai How to Build End to End Batch Data Pipeline using Apache Airflow yang sudah di manage oleh AWS yaitu Managed Worflow Apache Airflow atau di singkat MWAA, Glue untuk crawling data dari S3 lalu di call ke MWAA dan Redshift sebagai Data Warehouse nya.</p><p>Untuk artikel ini saya akan pecah menjadi 3 artikel, artikel pertama adalah artikel ini dan 2 artikel lagi yaitu :</p><ul><li><a href="https://msaipulr.medium.com/setup-data-infrastructure-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-6332fbff2f4c">Setup Data Infrastructure for Batch Data Pipeline Using Airflow MWAA, glue and AWS Redshift</a></li><li><a href="https://msaipulr.medium.com/create-airflow-dag-script-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-ab5b4ced1ca6">Create Airflow dag script for Batch Data Pipeline Using Airflow MWAA, glue and AWS Redshift</a></li></ul><p>Jadi servis aws yang digunakan untuk artikel ini yaitu :</p><ul><li>MWAA</li><li>AWS Glue untuk crawling data from Aws S3</li><li>AWS S3 untuk penyimpanan raw csv file di data lakes nya</li><li>AWS IAM Roles untuk mengatur permission IAM roles services yang digunakan</li><li>AWS Redshift sebagai data warehouse</li></ul><h4>Data Architecture</h4><p>Untuk Data Architecture dari artikel ini yaitu :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*xTHguis8aJuYvThfUdZYKw.jpeg" /><figcaption>Data Architecture Batch Data Pipeline Using MWAA as Orchestrator</figcaption></figure><p>Penjelasan :</p><p>Data csv file di upload ke S3 bucket raw kemudian AWS Glue Crawler akan otomatis mengecek file tersebut kemudian menjalankan Glue ETL Job. Jika sudah selesai proses transformasi datanya akan di simpan di S3 bucket terpisah khusus transformasi data. Jika sudah selesai akan otomatis ke copy ke Redshift Datawarehouse.</p><h4>Prepare dataset</h4><p>Untuk dataset yang digunakan pada artikel ini menggunakan dataset NYC taxi ride. Detail dataset nya kalian bisa lihat di <a href="http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml">sini</a>.</p><h4>Sekilas Apache Airflow</h4><p>Apache Airflow adalah salah satu open source terkenal untuk melakukan orchestration dalam pengolahan data. Saya tidak akan membahas detail mengenai Apache Airflow. Kalian bisa baca mengenai airflow di website resmi <a href="https://airflow.apache.org/">airflow</a>.</p><h4>Sekilas Mengenai MWAA</h4><p>MWAA atau disingkat Managed Workflow Apache Airflow adalah Managed service Apache Airflow yang di tawarkan oleh AWS sehingga kalian tidak perlu install airflow dari awal lagi dan berjalan di Elastics Kubernetes Services jadi otomatis reliable dan High Availability karerna berjalan di kubernetes. Untuk detail nya bisa baca di <a href="https://aws.amazon.com/managed-workflows-for-apache-airflow/">sini</a>.</p><h4>Sekilas Mengenai AWS Redshift</h4><p>AWS Redshift adalah opsi datawarehouse yang disediakan oleh AWS. Disini saya masih menggunakan Redshift Cluster dan next nya akan mencoba menggunakan Redshift serverless. Untuk lebih detailnya bisa lihat ke docs resmi nya di <a href="https://aws.amazon.com/redshift/">sini</a></p><p>Ok itu saja untuk artikel intermezzo ini. Next nya mulai tahap setup Data Infrastrukture yang dibutuhkan di <a href="https://msaipulr.medium.com/setup-data-infrastructure-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-6332fbff2f4c">setup data infrastruktur for batch data pipeline</a>.</p><h4>Referensi :</h4><p><a href="https://catalog.us-east-1.prod.workshops.aws/workshops/795e88bb-17e2-498f-82d1-2104f4824168/en-US">https://catalog.us-east-1.prod.workshops.aws/workshops/795e88bb-17e2-498f-82d1-2104f4824168/en-US</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9bdd87d2af6e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Create Airflow dag script for Batch Data Pipeline Using Airflow MWAA, glue and AWS Redshift]]></title>
            <link>https://msaipulr.medium.com/create-airflow-dag-script-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-ab5b4ced1ca6?source=rss-645d0d2f0212------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab5b4ced1ca6</guid>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <category><![CDATA[aws-mwaa]]></category>
            <category><![CDATA[amazon-redshift]]></category>
            <dc:creator><![CDATA[Muhammad Saipul Rohman]]></dc:creator>
            <pubDate>Fri, 13 Jan 2023 17:08:12 GMT</pubDate>
            <atom:updated>2023-01-13T17:18:49.274Z</atom:updated>
            <content:encoded><![CDATA[<p>Di artikel ketiga ini akan membahas pembuatan airflow dag script melanjutkan dari artikel bagian kedua di <a href="https://msaipulr.medium.com/setup-data-infrastructure-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-6332fbff2f4c">https://msaipulr.medium.com/setup-data-infrastructure-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-6332fbff2f4c</a>.</p><p>Artikel ini adalah artikel bagian ketiga dan terakhir. Jika ingin mengetahui dari dari artikel pertama bisa di lihat di <a href="https://msaipulr.medium.com/build-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-9bdd87d2af6e">https://msaipulr.medium.com/build-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-9bdd87d2af6e</a>.</p><p>Sebelum setup DAG, saya akan setup Cloud9 terlebih dahulu. Apa itu Cloud9? Cloud9 adalah services IDE programming seperti VSCode, EClipse. Bedanya adalah Cloud9 berjalan di AWS EC2 sehingga bisa di gunakan dimanapun yang penting ada internet dan pastinya tidak perlu install ulang library yang di butuhkan berbeda dengan IDE yang di lokal :)</p><h4>Setup Cloud9</h4><p>Langkah-langkah membuat Workspace baru di Cloud9 yaitu :</p><ul><li>Pergi ke <a href="https://ap-southeast-1.console.aws.amazon.com/cloud9control/home?region=ap-southeast-1#/product">Cloud9</a></li><li>Klik Create Environment</li><li>Pilih region terdekat dengan lokasi kalian saat ini agar tidak low latency. Untuk memilih region ada di bagian atas kana sebelah tanda tanya seperti screenshot di bawah ini</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4nrlOd0MTdO3KmybNdzKGQ.png" /></figure><ul><li>Input nama environment nya. Isian yang lain di biarkan default kemudian klik Create</li><li>Tunggu sampai selesai. Jika sudah selesai buka tab baru dan close tab welcome</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7EHImQb_B2FhRHi6QYoClw.png" /></figure><ul><li>Workspace kalian akan seperti di bawah ini</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TpZS44dtWEZj7hMTq8qWlw.png" /></figure><ul><li>Jika kalian suka dengan thema ini bisa kalian pilih sendiri dengan cara klik View → Themes → Solarized → Solarized Dark di menu cloud9 workspace</li></ul><h4>Setup DAG</h4><p>Tahap pertama setup DAG yaitu import library python yang dibutuhkan untuk membuat airfow DAG beserta custom airflow operator yang sudah di sediakan di airflow plugin.</p><pre>from datetime import timedelta  <br>import airflow  <br>from airflow import DAG  <br>from airflow.providers.amazon.aws.sensors.s3_prefix import S3PrefixSensor<br>from airflow.providers.amazon.aws.operators.glue import AwsGlueJobOperator<br>from airflow.providers.amazon.aws.operators.glue_crawler import AwsGlueCrawlerOperator<br><br># Custom Operators deployed as Airflow plugins<br>from awsairflowlib.operators.aws_copy_s3_to_redshift import CopyS3ToRedshiftOperator</pre><p>Berikutnya saya akan mendefinisikan default set paramater di Airflow DAG. Saya juga akan mendefinisikan nama bucket S3 dimana data dan script akan disimpan</p><blockquote>Notes : Ganti value di variabel S3_BUCKET_NAME dengan S3 bucket masing-masing</blockquote><pre>S3_BUCKET_NAME = &quot;airflow-yourname-bucket&quot;  <br>  <br>default_args = {  <br>    &#39;owner&#39;: &#39;airflow&#39;,<br>    &#39;depends_on_past&#39;: False,<br>    &#39;start_date&#39;: airflow.utils.dates.days_ago(1),<br>    &#39;retries&#39;: 0,<br>    &#39;retry_delay&#39;: timedelta(minutes=2),<br>    &#39;provide_context&#39;: True,<br>    &#39;email&#39;: [&#39;airflow@example.com&#39;],<br>    &#39;email_on_failure&#39;: False,<br>    &#39;email_on_retry&#39;: False<br>}<br></pre><p>Berikutnya adalah membuat objek DAG menggunakan argument default yang sudah di buat sebelumnya dan menetapkan schedule yang akan di jalankan oleh DAG</p><pre>dag = DAG(  <br>    &#39;data_pipeline&#39;,<br>    default_args=default_args,<br>    dagrun_timeout=timedelta(hours=2),<br>    schedule_interval=&#39;0 3 * * *&#39;<br>)<br></pre><h4>S3 Sensor</h4><p>Selanjutnya menggunakan s3_prefix_sensor yang tersedia di Apache Airflow untuk menambahkan sebuah task di DAG yang akan menunggu objek tersedia di S3 sebelum di eksekusi.</p><pre>s3_sensor = S3PrefixSensor(  <br>  task_id=&#39;s3_sensor&#39;,  <br>  bucket_name=S3_BUCKET_NAME,  <br>  prefix=&#39;data/raw/green&#39;,  <br>  dag=dag  <br>)</pre><p>Maka Jika ada objek yang masuk di folder <strong>data/raw/green</strong> yang ada di S3 bucket maka otomatis akan menjalankan sensor ini</p><h4>Glue Crawler</h4><p>Untuk menjalankan Glue Crawler menggunakan operator yang ada di airflow, kalian bisa menggunakan package yang sudah di sediakan oleh Apache Airflow.</p><p>Sebelum menambahkan code untuk menjalankan Glue Crawler, buat Crawler nya menggunakan AWS CLI di Cloud9 Workspace. Detail langkah-langkah nya ada di bagian Setup Cloud9.</p><p>Pertama dapatkan ARN Glue IAM Service Role. Pergi ke <a href="https://console.aws.amazon.com/iamv2/home?#/roles">IAM Console — Roles</a>, cari <em>AWSGlueServiceRoleDefault</em>, klik role tersebut dan salin <strong>Role ARN</strong> nya. Kemudian ganti nomor ARN nya dan nama bucket S3 pada script di bawah ini</p><pre>aws glue create-crawler \<br>--name airflow-workshop-raw-green-crawler \<br>--role arn\:aws\:iam::1111111111111111\:role/AWSGlueServiceRoleDefault \<br>--database-name default \<br>--targets &quot;{\&quot;S3Targets\&quot;:[{\&quot;Path\&quot;:\&quot;s3://airflow-yourname-bucket/data/raw/green\&quot;}]}&quot;</pre><p>Jalankan script di atas pada terminal Cloud9 Workspace.</p><p>Berikutnya tambahkan langkah untuk menjalankan crawler di DAG saat ada objek masuk di S3.</p><pre>config = {&quot;Name&quot;: &quot;airflow-workshop-raw-green-crawler&quot;}<br><br>glue_crawler = AwsGlueCrawlerOperator(<br>    task_id=&quot;glue_crawler&quot;,<br>    config=config,<br>    dag=dag)</pre><h4>Glue ETL Job</h4><p>Tahap berikutnya kita akan import script job ke Glue di Console AWS. Langkah — langkahnya yaitu :</p><ul><li>Download job script package di <a href="https://static.us-east-1.prod.workshops.aws/public/540a27d2-ca08-4c01-b181-2e540a9bd1b3/static/packages/scripts.zip">sini</a> dan extract file zip nya</li><li>Modifikasi script job Glue <strong>nyc_raw_to_transform.py</strong>(di dalam folder glue) untuk mengganti nama S3 bucket di bagian data sink.</li></ul><pre>## @type: DataSink<br>## @args: [connection_type = &quot;s3&quot;, connection_options = {&quot;path&quot;: &quot;s3://airflow-yourname-bucket/data/transformed/green&quot;}, format = &quot;parquet&quot;, transformation_ctx = &quot;datasink4&quot;]<br>## @return: datasink4<br>## @inputs: [frame = dropnullfields3]<br>datasink4 = glueContext.write_dynamic_frame.from_options(frame = dropnullfields3, connection_type = &quot;s3&quot;, connection_options = {&quot;path&quot;: &quot;s3://mwaa-demo-buckets/data/transformed/green&quot;}, format = &quot;parquet&quot;, transformation_ctx = &quot;datasink4&quot;)</pre><ul><li>Copy script job Glue yang sudah di modifikasi sebelum nya ke S3 bucket <strong>s3://airflow-yourname-bucket/scripts/glue/</strong></li><li>Login ke <a href="https://console.aws.amazon.com/glue/home?#etl:tab=jobs">Console AWS Glue</a> kemudian klik Add Job</li><li>Input name, untuk IAM role pilih <strong>AWSGlueServiceRoleDefault</strong></li><li>Di bagian This job runs, pilih <strong>An existing script that you provide</strong></li><li>Pilih path S3 untuk penyimpanan script python untuk menjalankan job Glue dan folder temporary lalu klik Next</li><li>Klik Save job and edit script</li><li>Pastikan step datasink4 yang ada di script python sebelumnya sudah mengarah ke path S3 yang benar</li><li>Klik Save</li><li>Tutup editor dengan mengklik X mark</li></ul><p>Di bawah ini adalah script untuk menjalankan job Glue yang sudah ada menggunakan airflow operator AwsGlueJobOperator</p><pre>glue_task = AwsGlueJobOperator(  <br>    task_id=&quot;glue_task&quot;,  <br>    job_name=&#39;nyc_raw_to_transform&#39;,  <br>    iam_role_name=&#39;AWSGlueServiceRoleDefault&#39;,  <br>    dag=dag)</pre><h4>Redshift</h4><p>Ok kita masuk ke task terakhir yaitu mengcopy data hasil transformasi ke table yang ada di redshift cluster. Sebelum itu, lakukan setup koneksi redshift cluster di Apache Airflow agar Redshift bisa berkomunikasi dengan Airflow. Langkah — langkahnya yaitu :</p><ul><li>Pergi ke Airflow Web UI(Link nya ada di <a href="https://console.aws.amazon.com/mwaa/home?#environments">MWAA console</a>)</li><li>Klik menu Admin → Connections</li><li>Klik tanda + untuk membuat koneksi baru</li><li>Input conn id <em>redshift_default</em></li><li>Pilih <strong>Postgres</strong> di dropdown Conn Type</li><li>Input Redshift endpoint di Host</li></ul><blockquote>Untuk mengetahui Redshift endpoint, pergi ke <a href="https://console.aws.amazon.com/redshiftv2/home?#clusters">Redshift console</a>, klik cluster yang sebelumnya di buat dan salin nama endpoint nya. Perhatikan bahwa di pengaturan koneksi Airflow kalian harus tidak mencatumkan port/database dan hanya input endpoint yang akhiran nya adalah .com seperti gambar di bawah ini</blockquote><ul><li>Input Schema : dev</li><li>Input Login : admin dan Password input admin password</li><li>Input Port : 5439 kemudian Klik Save</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QCMoW8EdklYYumoodAAnWA.jpeg" /></figure><p>Berikutnya adalah tambahkan code di bawah ini untuk mencopy data hasil transformasi dari AWS Glue ke Redshift.</p><p>Pertama-tama dapatkan AWS Redshift IAM Service role ARN. Pergi ke <a href="https://console.aws.amazon.com/iamv2/home?#/roles">IAM Console — Roles</a>, cari role <em>AmazonMWAA-workshop-redshift-role</em> lalu klik role tersebut dan copy <strong>Role ARN</strong> nya.</p><blockquote>Ganti iam_role_arn dengan Redshift IAM Role yang sudah kalian copy <strong>Role ARN</strong> sebelumnya</blockquote><pre>copy_s3_to_redshift = CopyS3ToRedshiftOperator(<br>    task_id=&#39;copy_to_redshift&#39;,<br>    schema=&#39;nyc&#39;,<br>    table=&#39;green&#39;,<br>    s3_bucket=S3_BUCKET_NAME,<br>    s3_key=&#39;data/transformed&#39;,<br>    iam_role_arn=&#39;arn\:aws\:iam::111111111111\:role/AmazonMWAA-workshop-redshift-role&#39;,<br>    copy_options=[&quot;FORMAT AS PARQUET&quot;],<br>    dag=dag,<br>)</pre><h4>DAG Run</h4><p>Di tahap terakhir ini tambahkan script di bawah ini untuk menjalankan task yang ada di DAG airflow ini.</p><pre>s3_sensor &gt;&gt; glue_crawler &gt;&gt; glue_task &gt;&gt; copy_s3_to_redshift</pre><p>Jika sudah selesai, copy python script tersebut ke folder <strong>s3://airflow-yourname-bucket/dags.</strong></p><p>Setelah upload code dag ke S3, tunggu beberapa menit sebelum DAG tampil di UI Airflow. Kalian bisa akses UI Airflow dengan mengklik link di <a href="https://console.aws.amazon.com/mwaa/home?#environments">Console Airflow</a></p><h4>Run Airflow DAG</h4><p>Untuk menjalankan DAG kalian perlu meng-enable DAG nya dengan menggeser ke samping agar bisa enable. Jika sudah di enable DAG nya maka otomatis akan jalan schedulernya.</p><p>Jika scheduler nya tidak jalan setelah di enable, kalian bisa menjalankan manual dengan mengklik tombol play yang di tampilkan pada gambar di bawah.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oHxnC1VdrAnfWpHcqlkQqg.jpeg" /></figure><p>Setelah DAG airflow jalan, DAG Airflow akan menunggu file masuk di path S3 <strong>s3://airflow-yourname-bucket/data/raw/green/</strong>. Agar DAG airflow berjalan, copy file ke spesifik path tersebut menggunakan command AWS S3 cli di console Cloud9.</p><blockquote>Ganti nama bucket S3 di bawah ini sesuai dengan nama bucket S3 kalian</blockquote><pre>aws s3 cp s3://ee-assets-prod-us-east-1/modules/f8fe356a07604a12bec0b5582be38aed/v1/data/green_tripdata_2020-06.csv s3://airflow-yourname-bucket/data/raw/green/</pre><p>Ketika file sudah masuk ke path S3 tersebut, task s3_sensor akan berganti status menjadi complete dan eksekusi nya akan lanjut ke task berikutnya.</p><p>Jika semua task DAG sudah selesai, maka akan di tandai warna hijau seperti gambar di bawah ini</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*2gYwlKwJ9IHYellLgUCOPw.png" /></figure><p>Untuk data nya juga sudah masuk ke Redshift seperti pada gambar di bawah ini</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p1a1FRivamEIFW-TfM849g.png" /></figure><p>Untuk full source code nya yaitu bisa di lihat di <a href="https://github.com/saipulrx/airflow_in_aws/blob/main/demo_simple_mwaa_redshift.py">https://github.com/saipulrx/airflow_in_aws/blob/main/demo_simple_mwaa_redshift.py</a></p><h4>Referensi :</h4><p><a href="https://catalog.us-east-1.prod.workshops.aws/workshops/795e88bb-17e2-498f-82d1-2104f4824168/en-US">https://catalog.us-east-1.prod.workshops.aws/workshops/795e88bb-17e2-498f-82d1-2104f4824168/en-US</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab5b4ced1ca6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Setup Data Infrastructure for Batch Data Pipeline Using Airflow MWAA, glue and AWS Redshift]]></title>
            <link>https://msaipulr.medium.com/setup-data-infrastructure-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-6332fbff2f4c?source=rss-645d0d2f0212------2</link>
            <guid isPermaLink="false">https://medium.com/p/6332fbff2f4c</guid>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[amazon-redshift]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[aws-mwaa]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <dc:creator><![CDATA[Muhammad Saipul Rohman]]></dc:creator>
            <pubDate>Thu, 12 Jan 2023 16:46:17 GMT</pubDate>
            <atom:updated>2023-01-13T17:33:50.324Z</atom:updated>
            <content:encoded><![CDATA[<p>Pada artikel ini saya akan melanjutkan artikel sebelum nya di <a href="https://msaipulr.medium.com/build-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-9bdd87d2af6e">building batch data pipeline using airflow MWAA</a>.</p><p>Artikel ini adalah artikel bagian kedua. Untuk artikel ketiga yaitu <a href="https://msaipulr.medium.com/create-airflow-dag-script-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-ab5b4ced1ca6">Create Airflow DAG script</a></p><p>Di artikel ini akan fokus setup Data Infrastruktur nya yang terdiri dari beberapa tahap yaitu :</p><ul><li>Setup S3 Bucket</li><li>Setup MWAA</li><li>Setup IAM Roles</li><li>Setup Redshift</li></ul><h4>Setup S3 Bucket</h4><p>Untuk langkah — langkah setup S3 bucket yaitu :</p><ol><li>Ke AWS <a href="https://s3.console.aws.amazon.com/s3/home">S3 Console</a>.</li><li>Klik <strong>Create Bucket</strong>.</li><li>Input nama bucket yang diinginkan.</li><li>Untuk opsi lain tidak usah di ubah-ubah lalu klik <strong>Create Bucket</strong>.</li><li>Klik di bucket yang sudah di buat sebelumnya kemudian klik <strong>Create Folder</strong></li><li>Input nama folder menjadi <em>dags</em> kemudian klik <strong>Create Folder</strong></li><li>Berikutnya buat 4 folder lagi lalu diberi nama folder <em>data</em>, <em>plugins</em>, <em>requirements</em> dan <em>scripts.</em></li><li>Klik folder <strong>scripts</strong> lalu buat 2 folder lagi kemudian beri nama folder <em>glue</em> dan <em>emr</em>.</li><li>Download plugin yang akan digunakan saat membuat data pipeline nanti di <a href="https://static.us-east-1.prod.workshops.aws/public/540a27d2-ca08-4c01-b181-2e540a9bd1b3/static/packages/awsairflowlib_222.zip">sini</a>.</li><li>Upload hasil download zip file <strong>awsairflowlib_222.zip </strong>ke folder <strong>plugins</strong> yang sudah kalian buat sebelumnya.</li><li>Buat text file baru beri nama <em>requirements.txt</em> di lokal komputer kalian lalu copy paste code di bawah ini ke file requirements.txt kalian.</li></ol><pre>--constraint &quot;https://raw.githubusercontent.com/apache/airflow/constraints-2.2.2/constraints-3.7.txt&quot;<br>apache-airflow[package-extra]==2.2.2<br>apache-airflow-providers-postgres[amazon]<br>apache-airflow-providers-cncf-kubernetes<br>psycopg2<br>fsspec<br>s3fs<br>pandas<br>sagemaker==v1.72<br>dag-factory==0.7.2</pre><p>Kemudian langkah terakhir yaitu upload requirements.txt dari lokal komputer kalian ke folder <strong>requirements</strong>.</p><p>Jika sudah selesai di setup maka struktur folder di AWS S3 bucket kalian adalah seperti di bawah ini.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KCYv94YsowNTXY_cmXTt3A.jpeg" /></figure><p>Berikutnya adalah setup instance MWAA.</p><h4>Setup MWAA</h4><ul><li>Ke <a href="https://console.aws.amazon.com/mwaa/home?#create/environment">Managed Workflow Apache Airflow</a> console</li><li>Input nama instance airflow kalian</li><li>Pilih <strong>Airflow version 2.2</strong> dari drop down</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ur7ALGJSxr4MjOqfoOABkQ.jpeg" /></figure><ul><li>Input nama S3 bucket sudah kalian buat sebelumnya atau klik Browse S3 lalu pilih S3 bucket yang kalian buat sebelumnya. contoh <em>s3://nama_bucket</em></li><li>Untuk DAG folder, input <em>s3: //nama_bucket/dag</em></li><li>Untuk Plugin folder, input <em>s3://nama_bucket/plugins/</em><a href="https://awsairflowlib_222.zip/"><em>awsairflowlib_222.zip</em>/</a></li><li>Untuk file requirement, input s3://nama_bucket/requirements/requirements.txt</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*98eI4iOdY5Ta9cnzI62Y_w.jpeg" /></figure><ul><li>Klik <strong>Next</strong> kemudian klik <strong>Create MWAA VPC</strong></li><li>Setelah itu otomatis akan membuka tab baru dengan cloud formation. Scroll sampai paling bawah lalu klik <strong>Create Stack</strong></li><li>Tunggu sampai status nya menjadi <strong>CREATE_COMPLETE</strong> untuk stack MWAA- VPC</li><li>Ketika selesai, kembali ke Console Managed Apache Airflow dan pilih VPC yang sudah di buat sebelumnya dari dropdown</li><li>Di bagian <em>Web Server Access</em>, ganti ke <strong>Public network</strong></li><li>Di bagian <em>Security group(s)</em>, tidak usah di ganti ganti lagi dimana Create new security group di ceklist di checkbox</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vTSiss7fEIN41RODM5wK8g.jpeg" /></figure><ul><li>Di bagian Environment class, pilih <strong>mw1.small</strong> dan ganti <strong>Maximum worker count</strong> menjadi 4.</li><li>Di bagian monitoring, enable opsi <strong>Airflow DAG processing logs</strong> dan ganti <em>Log level</em> ke <strong>INFO</strong> untuk log pengolahan data.</li><li>Klik Next</li><li>Review semua konfigurasi, jika sudah ok klik tombol <strong>Create Environment</strong> di bagian bawah halaman</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WqPLa8ULmFSpOKiPIhO_cg.jpeg" /></figure><p>Berikutnya adalah setup IAM roles.</p><h4>Setup IAM Roles</h4><ul><li>Pergi ke console <a href="https://console.aws.amazon.com/iam/home?#/roles">IAM Roles</a></li><li>Cari role Airflow instance dimana nama role nya seperti <em>AmazonMWAA-airflow-xxxx-instance-xxxx</em></li><li>Berikutnya attach IAM role permission di bawah ini ke instance airflow role</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9x6diEvp4JpUjD-AprN2ww.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5VN5DLazptmTdthHKPMwdQ.jpeg" /></figure><p>Berikutnya adalah kita akan membuat Glue service role untuk menjalankan crawling dan job. Untuk membuat IAM role untuk AWS Glue yaitu :</p><ul><li>Pergi ke<a href="https://console.aws.amazon.com/iam/home?#/roles"> IAM roles Console</a></li><li>Klik <strong>Create Role</strong></li><li>Untuk tipe role, pilih AWS Service kemudian cari dan pilih Glue lalu Klik <strong>Next</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RptTNex_MiGpRX5hwHl2bQ.png" /></figure><ul><li>Di halaman attach permission policies, pilih AWS managed policy <strong>AWSGlueServiceRole</strong> untuk AWS Glue permission secara umum dan AWS managed policy <strong>AmazonS3FullAccess</strong> untuk akses Amazon S3 resource.</li><li>Untuk nama role, input <em>AWSGlueServiceRoleDefault</em> dan pilih <strong>Create Role</strong>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oWeSeJimEA8OXVHT2x8qJg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EADR01fJsiA6VIp5uQ8pkA.png" /></figure><p>Berikutnya adalah setup IAM role untuk redshift.</p><h4>Setup Redshift IAM Role</h4><p>Langkah — langkah untuk setup Redshift IAM role yaitu :</p><ul><li>Pergi ke <a href="https://console.aws.amazon.com/iam/home?#/roles">IAM roles Console</a></li><li>Klik <strong>Create Role</strong></li><li>Untuk tipe role, pilih AWS Service kemudian cari dan pilih <strong>Redshift</strong>.</li><li>Pilih <strong>Redshift-Customizable</strong> di bagian <strong>select your case</strong>.</li><li>Klik <strong>Next</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kOIG8ZaMHoLvzh6-wMzbnw.png" /></figure><ul><li>Di search box, cari dan cek 2 policy di bawah ini</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/319/1*EAqMzXeD1-LyJD3-fRj8qA.png" /></figure><ul><li>Klik <strong>Next</strong></li><li>Input role name <strong>AmazonMWAA-workshop-redshift-role</strong></li><li>Verifikasi 2 policy di bawah ini sudah di attach ke role</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/319/1*EAqMzXeD1-LyJD3-fRj8qA.png" /></figure><ul><li>Klik <strong>Create Role</strong></li></ul><p>Berikutnya adalah setup Subnet Group.</p><h4>Setup Subnet groups</h4><p>Kita akan setup Subnet Group berkaitan dengan VPC MWAA Airflow</p><ul><li>Pergi ke <a href="https://console.aws.amazon.com/redshiftv2/home?#subnet-groups">Redshift</a></li><li>Klik <strong>Create cluster subnet group</strong></li><li>Input nama untuk subnet group <em>-AmazonMWAA-workshop-subnet-group</em></li><li>Input Deskripsi <em>-Subnet group for the Amazon MWAA workshop</em></li><li>Di bagian <strong>Add Subnet</strong>, di dropdown pilih MWAA Environment dan klik <strong>Add all the subnets for this VPC</strong></li><li>Klik <strong>Create cluster subnet group</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u1k68TJbxussYuSqK5L5cg.png" /></figure><p>Berikutnya adalah setupRedshift cluster</p><h4>Setup Redshift Cluster</h4><p>Di bagian ini kita akan membuat 2 node Redshift Cluster untuk menyimpan data hasil transformasi.</p><ul><li>Pergi ke <a href="https://console.aws.amazon.com/redshiftv2/home">Redshift</a></li><li>Click Create Cluster</li><li>Untuk nama cluster identifier tidak usah di ubah biarkan nama defaultnya yaitu <strong>redshift-cluster-1</strong></li><li>Di bagian planning cluster pilih Production</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ePKZlkC75QFB3SC3fd5F_A.png" /></figure><ul><li>Pilih <strong>dc2.large</strong> sebagai tipe node</li><li>Input <em>Number of Nodes</em> menjadi 2</li><li>Ganti <strong>Master username</strong> menjadi <em>admin</em></li><li>Input <strong>Master user password</strong>. Pastikan input password sesuai dengan rule yang ada(harus ada kombinasi huruf besar, huruf kecil dan spesial karakter !@#$%^&amp;*()-+=).</li><li>Di bagian Cluster Permissions — Associated IAM roles, Klik Manage IAM roles lalu pilih Associate IAM roles</li><li>Di pop up window associate IAM role, pilih <strong>AmazonMWAA-workshop-redshift-role </strong>kemudian klik Associate IAM roles</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Sy16ANntHwwsTypOrAR6ew.png" /></figure><ul><li>Jika sudah akan tampil di bagian Associated IAM roles</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CXhY3OfxP2xggL69fDMiGQ.png" /></figure><ul><li>Di bagian Additional configurations, geser slider Use defaults agar tidak memilih default konfigurasi. Di bagian Network and Security, pilih <strong>MWAAEnvironment</strong> dari dropdown VPC. Untuk pengaturan yang lain biarkan default</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0WaxzK3oOpHszOWbaAUrmA.png" /></figure><ul><li>Klik <strong>Create Cluster</strong></li></ul><p>Pembuatan Cluster Redshift akan memakan waktu beberapa menit sampai statusnya menjadi <strong>available</strong></p><p>Jika cluster sudah selesai dibuat dan status nya menjadi <strong>available</strong> maka lanjut ke tahap berikutnya yaitu setup S3 gateway endpoint</p><h4>Setup S3 Gateway Endpoint</h4><p>Di bagian ini kita akan mensetup S3 Gateway Endpoint agar Cluster Redshift bisa terhubung dengan S3 menggunakan private IP.</p><blockquote>Lebih detail mengenai Amazon VPC bisa baca di <a href="https://docs.amazonaws.cn/en_us/vpc/latest/userguide/what-is-amazon-vpc.html">https://docs.amazonaws.cn/en_us/vpc/latest/userguide/what-is-amazon-vpc.html</a></blockquote><blockquote>Lebih detail mengenai S3 Gateway Endpoint bisa baca di <a href="https://docs.aws.amazon.com/vpc/latest/userguide/vpc-endpoints-s3.html">https://docs.aws.amazon.com/vpc/latest/userguide/vpc-endpoints-s3.html</a></blockquote><ul><li>Pergi ke <a href="https://console.aws.amazon.com/vpc/home?#Endpoints:sort=vpcEndpointId">Console AWS VPC</a></li><li>Klik Create Endpoint</li><li>Input name tag <em>RedshiftS3EP</em></li><li>Pilih AWS Services di bagian Service category</li><li>Di bagian pencarian service name cari <strong>s3</strong> lalu tekan enter</li><li>Harusnya muncul <strong>com.amazonaws.[region].s3</strong> di hasil pencarian. Pilih opsi dari list dimana di sebutkan <strong>Type</strong> sebagai <strong>Gateway</strong></li><li>Di bagian VPC, pilih VPC <strong>MWAAEnvironment</strong>. VPC ini akan digunakan juga di Redshift Cluster</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gG7gFraqBXkCrZe_W9Yh2w.png" /></figure><ul><li>Di bagian Configure route tables, centang dimana kolom <strong>Main</strong> nya <strong>Yes</strong></li><li>Di bagian Policy biarkan default(Full Access)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0MvoHmsKZeM3hwJmWptYDg.png" /></figure><ul><li>Klik tombol Create endpoint</li><li>Jika sudah maka tunggu sampai statusnya berubah menjadi available</li></ul><h4>Verifikasi dan menambahkan rule ke default security group</h4><p>Di langkah ini untuk melakukan verifikasi dan menambahkan rule ke redshift security group agar AWS Glue bisa berkomunikasi dengan Redshift.</p><ul><li>Pergi ke <a href="https://console.aws.amazon.com/vpc/home?#SecurityGroups:sort=tag:Name">VPC Security Groups</a></li><li>Pilih Redshift security groups. Jika ada lebih dari 1 security group, ikuti langkah di bawah ini :</li><li>Pergi ke <a href="https://console.aws.amazon.com/redshiftv2/home">Redshift console</a></li><li>Klik <strong>redshift-cluster-1</strong></li><li>Klik tab properties</li><li>Scroll ke bawah dan check bagian <strong>Network &amp; Security</strong> untuk security group</li><li>Setelah sudah memastikan security group, Klik security group tersebut.</li><li>Lalu klik Inbound Rules, klik Edit inbound rules</li><li>Klik Add rules, pilih type Redshift untuk memperbolehkan airflow untuk mengakses redshift cluster. Kemudian di bagian source pilih custom lalu pilih security group nya airflow</li><li>Kemudian Klik Add rules 1 lagi lalu pilih type HTTPS untuk akses Amazon S3 lalu klik save rules</li><li>Berikut nya di bagian Outbond Rules, klik Edit outbound rules</li><li>Pastikan sudah ada oubound rules All TCP. Jika belum maka dibuat terlebih dahulu</li></ul><h4>Membuat schema dan table di Redshift</h4><ul><li>Pergi ke <a href="https://console.aws.amazon.com/redshiftv2/home?#query-editor:">Redshift Query Editor</a></li><li>Klik <strong>Go to query editor v2</strong> di bagian paling atas sebelah kanan</li><li>Di sebelah kiri klik redshift-cluster-1 lalu klik Create connection</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s0db3C7vXzEExHh5OipWgw.png" /></figure><ul><li>Di bagian authentication pilih Database username dan password kemudian input database, username dan password kemudian create connection</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ps5HUv1yuVxxgYmxvnSG9g.png" /></figure><ul><li>Jalankan query di bawah ini untuk membuat schema dan table hasil transformasi data</li></ul><pre>--    Create nyc schema.<br>CREATE schema nyc;</pre><pre><br>CREATE TABLE IF not EXISTS nyc.green(vendorid bigint ,<br>                           lpep_pickup_datetime  character varying(256),<br>                           lpep_dropoff_datetime character varying(256),<br>                           store_and_fwd_flag    character varying(256),<br>                           ratecodeid            bigint,<br>                           pulocationid          bigint,<br>                           dolocationid          bigint,<br>                           passenger_count       bigint,<br>                           trip_distance         double precision,<br>                           fare_amount           double precision,<br>                           extra                 double precision,<br>                           mta_tax               double precision,<br>                           tip_amount            double precision,<br>                           tolls_amount          double precision,<br>                           ehail_fee             character varying(256),<br>                           improvement_surcharge double precision,<br>                           total_amount          double precision,<br>                           payment_type          bigint,<br>                           trip_type             bigint);</pre><p>Ok sudah selesai untuk artikel setup data infrastruktur nya. Di artikel berikut nya akan membahas <a href="https://msaipulr.medium.com/create-airflow-dag-script-for-batch-data-pipeline-using-airflow-mwaa-glue-and-aws-redshift-ab5b4ced1ca6">cara pembuatan airflow dag python script</a>.</p><h4>Referensi :</h4><p><a href="https://catalog.us-east-1.prod.workshops.aws/workshops/795e88bb-17e2-498f-82d1-2104f4824168/en-US">https://catalog.us-east-1.prod.workshops.aws/workshops/795e88bb-17e2-498f-82d1-2104f4824168/en-US</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6332fbff2f4c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>