<?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 Puneet Kumar on Medium]]></title>
        <description><![CDATA[Stories by Puneet Kumar on Medium]]></description>
        <link>https://medium.com/@puneetkumar_2605?source=rss-214a9bad3cea------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*YHb6_qKd5qA1aA-L.</url>
            <title>Stories by Puneet Kumar on Medium</title>
            <link>https://medium.com/@puneetkumar_2605?source=rss-214a9bad3cea------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 20:05:42 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@puneetkumar_2605/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[Superfast Spark ETL with YAML Constructs: SOPE]]></title>
            <link>https://medium.com/@puneetkumar_2605/superfast-spark-etl-with-yaml-constructs-sope-3a85e4f1436?source=rss-214a9bad3cea------2</link>
            <guid isPermaLink="false">https://medium.com/p/3a85e4f1436</guid>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[yaml]]></category>
            <category><![CDATA[sope]]></category>
            <dc:creator><![CDATA[Puneet Kumar]]></dc:creator>
            <pubDate>Mon, 11 Feb 2019 11:56:42 GMT</pubDate>
            <atom:updated>2019-02-11T11:56:42.555Z</atom:updated>
            <content:encoded><![CDATA[<p>Sope is set of utilities and library functions that helps with ETL development using Apache Spark. The project contains following sub-modules</p><ul><li><em>sope-spark</em></li><li><em>sope-etl</em></li></ul><ol><li>sope-spark: This module contains library functions and a Scala internal dsl library that assists with writing Spark SQL ETL transformations in concise manner</li></ol><h3>sope-spark</h3><p>This module contains library functions and a Scala internal dsl library that helps with writing Spark SQL ETL transformations in concise manner. It will reduce the boiler-plate code for complex transformations and will assist in code readability and review.</p><ol><li>Using Library functions:</li><li>Just use following import statement and you are all set.</li></ol><pre>import com.sope.spark.sql._<br>This will make available useful implicit methods on the DataFrame object. Following is the list of some useful methods available:</pre><pre>df // a dataframe object reference <br><br>// Check if Dataframe is not empty<br>df.isNotEmpty  <br><br>// Check if Dataframe is empty<br>df.isEmpty    <br><br>// Rename columns<br>df.renameColumns(Map(&quot;nme&quot; -&gt; &quot;name&quot;, &quot;address&quot; -&gt; &quot;address_line_1&quot;))<br><br>// Apply string expressions to provided columns<br>df.applyStringExpressions(Map(&quot;name&quot; -&gt; &quot;upper(name)&quot;, &quot;valid_name&quot; -&gt; &quot;&#39;YES&#39;&quot;))    <br><br>// Apply column expressions to provided columns<br>df.applyColumnExpressions(Map(&quot;name&quot; -&gt; upper($&quot;name&quot;), &quot;valid_name&quot; -&gt; lit(&quot;YES&quot;))     <br><br>// Cast columns, converts all columns from one type to other castable data type<br>df.castColumns(TimestampType, DateType)  /* converts all timestamp columns to date columns */<br><br>// Partition Dataframe<br>val (indiaDF, otherDF) = df.partition(&quot;country = &#39;India&#39;&quot;)<br><br>// Get max value for a surrogate key column, empty set returns 0.<br>val maxMemberId = df.maxKeyValue(&quot;member_id&quot;)<br><br>// Generate Sequence Number from the provided start index<br>df.generateSequence(maxMemberId, Some(&quot;member_id&quot;))  // will create new column &#39;member_id&#39; as sequence column<br>df.generateSequence(maxMemberId)  // If already has first column as member_id<br><br>// Flattens the columns in a struct type column to normal columns<br>df.unstruct(&quot;column&quot;)<br><br>//Update/Add Surrogate key columns by joining on the &#39;Key&#39; dimension table.<br>val dimDateDF = sqlContext.table(&quot;dim_date&quot;)<br>val dateColumns = Seq(&quot;sale_date&quot;, &quot;create_date&quot;)<br>df.updateKeys(dateColumns, dimDateDF, &quot;date&quot; /*join column*/, &quot;date_key&quot; /*key column*/) // new columns - sale_date_</pre><p><strong>Scala DSL</strong>:</p><p>A simple DSL for Transformations like : t1 + t2 + t3 …. + tn → df is provided. The DSL can be used to reduce the intermediate dataframe object references and create transformation references instead. These transformation references are reusable and concise in representation. You can also use the implicit functions and DSL together.</p><p>Following is a sample DSL usage :</p><pre>// Use Following import to use the DSL constructs<br>import com.sope.spark.sql.dsl._<br><br>val studentDF =  ...<br>val classDF = ...<br>// Rename column transformation<br>val rename = Rename(&quot;first_name&quot; -&gt; &quot;firstname&quot;, &quot;last_name&quot; -&gt; &quot;lastname&quot;)<br>// Column transform transformation<br>val transform = Transform(&quot;firstname&quot; -&gt; upper($&quot;firstname&quot;), &quot;lastname&quot; -&gt; upper($&quot;lastname&quot;))<br>// Join transformation<br>val join = Join(&quot;cls&quot;) &lt;&lt; classDF // left join on class DF<br>// chain all transformations and finally apply on dataframe<br>val transformed = rename + transform + join + Sequence(0l, &quot;id&quot;) --&gt; studentDF</pre><p>Following DSL constructs are supported. Please refer <a href="https://mayur2810.github.io/sope/sope-spark">scala doc</a> for more details:</p><ul><li>Select</li><li>SelectNot</li><li>Filter</li><li>Rename</li><li>Drop</li><li>Transform</li><li>Sequence</li><li>Join</li><li>Group</li><li>Cube</li><li>Union</li><li>Intersect</li><li>Except</li><li>OrderBy</li><li>Distinct</li><li>Limit</li><li>DropDuplicates</li><li>Unstruct</li><li>UpdateKeys</li></ul><h3>sope-etl</h3><h3>YAML Transformer:</h3><p>The YAML Transformer reads a yaml file and executes the transformations defined in the file. You will find the transformer useful for creating simple or medium complexity transformation pipelines by using a external YAML file, instead of writing scala/java files. The transformer supports following useful features:</p><ul><li>Simple: Simple and easy to use constructs with support for SQL.</li><li>Optimizations: Auto persists and pre-partitions persisted data for join optimization.</li><li>Template support: Create reusable yaml templates (Call yaml within yaml with support for substitutions).</li><li>Custom Transformations/UDF support: Call custom transformations/udfs written in Scala/Java.</li><li>Testing mode: Enable testing mode to test transformations on sample set of data.</li></ul><p>The details about the Transformer constructs are provided in following section:</p><h3>YAML Transformer Constructs</h3><p>A Yaml Transformer definition can have following constructs at root:</p><ul><li>inputs : Inputs sources to be transformed.</li><li>transformations: Transformations definitions.</li><li>outputs: Output targets (only defined for end-to-end mode).</li></ul><h4>1. inputs</h4><ul><li>End-to-End Transformation file: The inputs sources for transformation can be provided as follows:</li></ul><pre>inputs:<br>      - {type: hive, alias: product, db: dim, table: product}<br>      - {type: hive, alias: date, db: dim, table: date}</pre><p>Following input/source types are supported:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PJQcY9JUhXhbgg6fxH0Lew.png" /></figure><h4>Transformer Modes:</h4><p>The transformer supports both end-to-end and intermediate mode.</p><ul><li>End-to-End mode: The YAML files has input sources and output targets information along with the transformation that will happen on the inputs. Supported input/output formats: hive, orc, parquet, text, csv, json Following is a sample yaml file which reads and writes to hive:</li></ul><pre>inputs:<br>    - {type: hive, alias: product, db: dim, table: product}<br>    - {type: hive, alias: date, db: dim, table: date}<br>    - {type: hive, alias: transactions, db: stage, table: transactions}<br>transformations :<br>    - description: &quot;SQL can be used for simple transformations&quot;<br>      input: product<br>      alias: product_filtered<br>      persist: memory_only<br>      sql: &quot;select product_id, product_desc as product from product where product_desc != &#39;N.A.&#39;&quot;<br>    - description: &quot;Actions can contain multiple action (transformation) steps&quot;<br>      input: transactions<br>      alias: transactions_transformed	  <br>      actions:<br>        - {type: rename, list: {id: trxn_id , loc: location}}<br>        - {type: transform, list: {location: &quot;lower(location)&quot;, trxn_id: &quot;concat(trxn_id, location)&quot;, rank: &quot;RANK() OVER (PARTITION BY location order by date desc)&quot;}}<br>        - {type: join, columns: [product] , join_type: inner, with: product_filtered}<br>        - {type: join, columns: [date], join_type: inner, with: date}<br>        - {type: sequence, sk_source: transactions, sk_column: id}<br>outputs:<br>    - {type: hive, input: product_filtered, mode: append, db: stage, table: temp_product}<br>    - {type: hive, input: transactions_transformed, mode: append, db: stage, table: transformed_transaction}</pre><p>The yaml file can be submitted for execution using spark-submit command as follows:</p><ul><li>Cluster mode:</li></ul><pre>spark-submit  --master yarn  --deploy-mode cluster  --class  com.sope.etl.YamlRunner  --files=&quot;entry.yaml, scd.yaml&quot; sope-etl-x.x.jar --main_yaml_file entry.yaml</pre><ul><li>Client mode with substitutions:</li></ul><pre>spark-submit  --master yarn  --deploy-mode client  --class  com.sope.etl.YamlRunner  --driver-class-path=&quot;/yaml-folder/&quot; sope-etl-x.x.jar --main_yaml_file entry.yaml --substitutions &quot;[sub1, {k1: v1, k2: v2}]&quot;</pre><p><em>NOTE</em>: It is mandatory that all the yaml files are in the spark driver classpath. The main_yaml_file argument only specifies the enrty point yaml file.</p><ul><li>Intermediate mode: This mode allows to use yaml file in Scala/Java code. Multiple dataframes can be provided as input and the final output will give references to transformed dataframes for all transformation aliases.</li><li>Following is a sample yaml file for intermediate mode use:</li></ul><pre>inputs: [transactions, product, date, dim_product]<br>transformations :<br>    - input: product<br>      alias: product_filtered<br>      sql: &quot;select product_id, product_desc as product from product where product_desc != &#39;N.A.&#39;&quot;<br>    - input: transactions<br>      alias: trxn_transformed<br>      actions:<br>        - {type: rename, list: {id: trxn_id , loc: location}}<br>        - {type: transform, list: {location: lower(location), trxn_id: &quot;concat(trxn_id, location)&quot;, rank: &quot;RANK() OVER (PARTITION BY location order by date desc)&quot;}}<br>        - {type: join, with: product_filtered,  columns: [product] , join_type: inner}<br>        - {type: join, with: date, columns: [date], join_type: inner}<br>        - {type: select_not, columns: [location]}<br>    - input: transactions<br>      alias: grp_by<br>      actions:<br>        - {type: group_by, columns: [product], expr: &quot;count(product) as p_cnt&quot;}<br>        - {type: order_by, columns: [p_cnt:desc]}<br>    - input : product<br>      alias: scd_product<br>      persist: true<br>      actions:<br>        - {type: scd, dim_table: dim_product, sk_column: product_key, natural_keys: [product_id], derived_columns: [derived_attr], meta_columns: [last_updated_date]}<br>    - input: scd_product<br>      alias: insert_update<br>      actions:<br>        - {type: filter, condition: &quot;scd_status = &#39;INSERT&#39; OR scd_status = &#39;UPDATE&#39;&quot;}<br>        - {type: transform, list: {last_updated_date: current_date}}<br>        - {type: sequence, sk_source: dim_product, sk_column: product_key}<br>    - input: scd_product<br>      alias: final_dim_out<br>      actions:<br>        - {type: filter, condition: &quot;scd_status = &#39;NCD&#39;&quot;}<br>        - {type: union, with: [insert_update]}</pre><p>The above yaml file can be called in code as follows:</p><pre>import com.sope.etl.yaml.YamlFile.IntermediateYaml<br>val transactionsDF = ...<br>val productDF = ...<br>val dateDF = ...<br>// The YAML files should be added in spark&#39;s driver classpath, returns list of Tuple of alias and transformed dataframes<br>val transformationResult = IntermediateYaml(&quot;withoutSourceInfo.yaml&quot;).getTransformedDFs(transactionsDF, productDF, dateDF)<br>// Get last transformed dataframe<br>val transformedDF = transformationResult.last._2</pre><h4>Optimizations:</h4><p>The Yaml Transformer will try to figure out if there are any transformations that are being reused and persist them using MEMORY_ONLY mode. This may be useful if you do not want to explicitly tag the transformation for persistence and let the transformer decide on it.</p><p>Also, if the transformation to be persisted is being used for multiple joins, it will be pre-partitioned on the join columns that are involved in most joins. This feature is enabled by default. To deactivate auto-persist set <em>sope.auto.persist.enabled=false</em>using — driver-java-options.</p><h4>Templates:</h4><p>The Transformer supports a ‘yaml’ action construct which can be used to call another yaml. It also supports substitution mode to drive the templates using dynamic values. There are some etl specific templates that are provided for reference:</p><ul><li>YAML SCD Template</li></ul><p><em># SCD Template<br># =================<br># Performs scd comparison for provided Dimension table</em></p><p><em># Placeholders:<br># 1 -&gt; Surrogate Key Column<br># 2 -&gt; Primary key Columns<br># 3 -&gt; Derived columns (Will be skipped for SCD comparisons, but values will stay intact for Update &amp; NCD records<br># 4 -&gt; Meta Columns (e.g. date columns identifying updates. This will be skipped for SCD comparison. Make sure to<br># Update this template to set new values for Meta columns in case for Insert/Update records.<br># The Output from this template can then be used to perform Type 1/Type 2 SCD as desired.<br>#<br># *Updates Needed to Template*<br># — — — — — — — — — — — — — -<br># 1. update the required date meta column. Currently defaults tp ‘last_update_date’. line no 31<br>#<br># Output alias to be used:<br># scd_result -&gt; Union of insert_update &amp; ncd<br>inputs: [“scd_input”, “dimension”]<br>transformations :<br> — input : “scd_input”<br> alias: “scd_product”<br> persist: “memory_only”<br> actions:<br> — {type: “scd”, dim_table: “dimension”, sk_column: $1 , natural_keys: $2 , derived_columns: $3 , meta_columns: $4}<br> #Filter insert/updates and adds ‘last_updated_date’ column, Note: Update the name of the column is required<br> — input: “scd_product”<br> alias: “insert_update”<br> coalesce: 200<br> actions:<br> — {type: filter, condition: “scd_status = ‘INSERT’ OR scd_status = ‘UPDATE’”}<br> — {type: transform, list: {last_updated_date: “current_date”}}<br> — {type: sequence, sk_source: “dimension”, sk_column: $1}<br> — input: “scd_product”<br> alias: “ncd”<br> coalesce: 200<br> actions:<br> — {type: filter, condition: “scd_status = ‘NCD’”}<br> — input: “ncd”<br> alias: “scd_result”<br> actions:<br> — {type: union, with: [“insert_update”]}</em></p><ul><li><strong>YAML DQ Template</strong></li></ul><p><em>inputs: [“dq_input”]<br>transformations :<br> — alias: “dq_output”<br> input : “dq_input”<br> actions:<br> — {type: “dq_check”, dq_function: “isnull” , id: “is_null” , columns: $1 }<br> — {type: “dq_check”, dq_function: “check_empty” , id: “is_empty” , columns: $2 }<br> — {type: “dq_check”, dq_function: “date_format_check” , id: “date_check” , options:[“yyyy-mm-dd”] , columns: $3 }</em></p><h4>Custom Transformation:</h4><p>If there is a need to call some complex/pre-built logic developed in using Scala/Java SparkSQl API’s, they can be integrated by calling the ‘named_transform’ action construct. For integration, you need to implement the ‘com.sope.etl.register.TransformationRegistration’ trait and define the ‘registerTransformations’ which return a Map of transformation name and transformation function (DataFrame*) =&gt; DataFrame.</p><p>An interface ‘com.sope.etl.register.UDFRegistration’ is also provided for registering custom UDF’s for use in transformer.</p><p>You can create a jar for custom transformations/udfs and register with Transformer using system properties:</p><ul><li>sope.etl.transformation.class</li><li>sope.etl.udf.class</li></ul><p>example:</p><pre>spark-submit  --master yarn  --deploy-mode cluster  --class  com.sope.etl.YamlRunner  --driver-java-options &quot;-Dsope.etl.transformation.class=com.custom.CustomTranformations -Dsope.etl.udf.class=com.custom.CustomUDFs&quot;  --files=&quot;entry.yaml, scd.yaml&quot; --jars &quot;custom.jar&quot; sope-etl-x.x.jar --main_yaml_file entry.yaml</pre><h4>Testing mode:</h4><p>The testing mode allows to sample data for all sources at once, without the need to edit the Yaml transformation file. This is helpful if you need to test transformation pipeline on small subset of data. To enable testing mode, set following system properties using <em>driver-java-options</em> property.</p><ol><li>sope.testing.mode.enabled=true</li><li>sope.testing.data.fraction=0.5 (Optional, if not provided defaults to 0.1)</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3a85e4f1436" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>