<?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 Junglee Games on Medium]]></title>
        <description><![CDATA[Stories by Junglee Games on Medium]]></description>
        <link>https://medium.com/@jungleegames?source=rss-35891010b23e------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*w8sNfHubv6GwwqDmzv7W0A.png</url>
            <title>Stories by Junglee Games on Medium</title>
            <link>https://medium.com/@jungleegames?source=rss-35891010b23e------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 07:39:35 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jungleegames/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[PII-SMT: Client-Side Field-Level Encryption/Decryption for Apache Kafka — Part 2]]></title>
            <link>https://jungleegames.medium.com/pii-smt-client-side-field-level-encryption-decryption-for-apache-kafka-part-2-6829afef2cec?source=rss-35891010b23e------2</link>
            <guid isPermaLink="false">https://medium.com/p/6829afef2cec</guid>
            <dc:creator><![CDATA[Junglee Games]]></dc:creator>
            <pubDate>Thu, 05 Jun 2025 07:14:16 GMT</pubDate>
            <atom:updated>2025-06-05T07:16:52.173Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>PII-SMT: Client-Side Field-Level Encryption/Decryption for Apache Kafka — Part 2</strong></h3><h3>Prerequisites</h3><p>This guide assumes that you have a basic understanding of Apache Kafka, Kafka Connect, Docker, relational databases (MySQL/PostgreSQL), and are comfortable using command-line tools. If you’re new to any of these technologies, we recommend reviewing introductory resources before diving in.</p><h3>System Requirements</h3><p>To follow along with the demo and run all components (Kafka, Kafka Connect, MySQL, PostgreSQL, Docker containers), your machine should meet at least the following specifications:</p><ul><li>CPU: 4 cores (minimum); 8+ cores recommended</li><li>Memory: 8 GB RAM (minimum); 16 GB recommended</li><li>Disk Space: 10 GB free</li><li>Docker: Installed and running (Docker Desktop or similar)</li><li>Operating System: Linux/macOS/Windows with WSL2</li></ul><p>This article is a continuation of <a href="https://jungleegames.medium.com/pii-smt-client-side-field-level-encryption-decryption-for-apache-kafka-part-1-9ade835518a2"><strong>Part 1</strong></a>, where we introduced PII-SMT, a library designed for field-level encryption and decryption of records in Apache Kafka. In this follow-up, we dive deeper into its practical implementation with an end-to-end demo covering.</p><h4>Key Topics Covered</h4><ol><li>Building and Installing the PII-SMT</li><li>Setting Up the Environment:</li></ol><ul><li>Docker</li><li>MySQL</li><li>PostgreSQL</li><li>Kafka</li><li>Kafka Connect</li></ul><p>3. Configuring Source connector With SMT:</p><ul><li>Create table and insert record</li><li>Create Source connector</li></ul><p>4. Configuring Sink Connectors with SMT:</p><ul><li>Create Postgres Table</li><li>Postgres Sink Connector Setup</li></ul><h3>End-to-End Demo</h3><h3>1. Building and Installing the PII-SMT</h3><p>To build Kafka SMT plugins</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5dnNqAQiJ7EnQcw5lITzJQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-j-BmhCArxhuUrbO" /></figure><p>Once you run the mvn package command, the final JAR file will be created under the target folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ngYz6AXdUYf-aKPm" /></figure><p>Create a folder debezium_plugins/smt and copy the JAR file into the smt folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/0*AV95ekee5bd_KzlX" /></figure><h3>2. Setting Up the Environment</h3><p>Running docker compose up starts the following services:</p><ol><li>Zookeeper</li><li>Kafka</li><li>Kafka Connect</li><li>MySQL</li><li>Postgres</li></ol><p>Create a docker-compose file and add the following services one by one:</p><p>$ cat &gt; docker-compose.yaml</p><p><strong>1.1 Zookeeper</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RfqV2xBdLd_z-tSgTIXNmg.png" /></figure><p><strong>1.2 Kafka</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OOXv0rjUoL1Q4i0itr9aXg.png" /></figure><p><strong>1.3 Kafka Connect</strong></p><p>Note: Please ensure that the JAR file is located in the debezium_plugins/smt folder, as this is the location used by Kafka Connect.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ayHa2xVWkGntRX-0r1ijVA.png" /></figure><p><strong>1.3 mysql</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BaZJuXhfjQ74PHHXl2-fOw.png" /></figure><p><strong>1.4 Postgres</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qo_ZsbITdV1KfuqmZlH7Ow.png" /></figure><p>Build and run the docker container with the following command:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5ML_JLPv5J_CIqA-oI783w.png" /></figure><p>Please make sure all the services are up and running.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*b31hiO4ynbDLRrXWEFF4aQ.png" /></figure><h3>3. Configure Connector with SMT</h3><p>This example illustrates field-level data protection during data transmission from the source to Kafka. The process utilizes the open-source Debezium platform for log-based <a href="https://www.confluent.io/learn/change-data-capture/">Change Data Capture</a> (CDC). The PII-SMT (Single Message Transform) for Kafka enhances security by post-processing Debezium’s MSSQL CDC event payloads, encrypting specific fields before the data leaves Kafka Connect on the client side and reaches the Kafka brokers. This ensures sensitive information is securely transmitted and protected throughout the process.</p><p>For example, consider an MSSQL users table containing several rows, each representing a different type of fictional users PII Data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Ree3G3_wV8bYtYvC" /></figure><p><strong>1. Create table and insert records</strong></p><p>Execute the MySQL Docker container and enter it using the following Docker command:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VRYZQlevKuTod1RthRtPmw.png" /></figure><p>To enter into mysql database CLI run the following command</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bCeFNn65NRuupgIMkn41Fg.png" /></figure><p>Insert some records into users table</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FvroMzJEXCR7g0JolWUOyQ.png" /></figure><p><strong>2. Create source connector</strong></p><p>Run the following command in your host terminal to start the Debezium tools container and enter an interactive bash session.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mHKAZV5KyJ8Ld4KsKnkHRg.png" /></figure><p><a href="https://github.com/kcctl/kcctl">kcctl</a> — a CLI for Apache Kafka Connect — is used to perform any Kafka Connect-related operations. First the connect cluster address is set and used as the CLI tool’s context. Then the MySQL source connector is created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XfFJdxK900YqQqhPPzvg_A.png" /></figure><p>Create <a href="https://docs.confluent.io/cloud/current/connectors/cc-mysql-source.html">Source connector</a> (Multiple Field Encryption with Same Encryption Data Key)</p><p>Now, let’s assume the task is to ensure that values from the email, dob, mobile, address columns in the users table are not stored in Kafka topics as plaintext. Using the field-level encryption capabilities of the custom CipherField SMT, this protection can be applied seamlessly without additional code. Simply add the following settings to the Debezium MSSQL source connector configuration:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*le1vuVmCPmrZbxTyUu05Dw.png" /></figure><p>In the CipherField SMT configuration, the transform.cipher.* properties have the following meanings:</p><ul><li><strong>Operate on the records’ values</strong>: Specifies that the transformation should apply to the record values. The type used is “Cryptography$Value”.</li><li><strong>Load the secret key material stored in AWS Secrets Manager</strong>: Specifies the path where the secret key material is stored, such as aws/sm/xyz.</li><li><strong>Use a specific secret key for a particular column</strong>: This configuration defines encryption details per column. For example:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*LjpHpm3JurwcfCCVYTgS0w.png" /></figure><p>Here, “encryption_type” is set to “deterministic” for consistent encryption across identical values, “pii_field_name” specifies the field name (e.g., mobile), and “aws_secretsmanager_key” provides the AWS SM key which holds the base64 encrypted KMS data key.</p><ul><li><strong>Process only specified fields:</strong> Using the field_config setting, you can specify which fields to encrypt, such as email,dob,mobile and address.</li><li><strong>Ignore tombstone records</strong>: The configuration can be set to skip processing tombstone records, often managed through predicate definitions to ensure the transform applies only to active records.</li></ul><p>Install connector using kafkacat command</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*ett0fk2qfa0NWcJB_0Rz9w.png" /></figure><p>The resulting source connector configuration successfully enables partial encryption of all Debezium CDC event payloads before they are transmitted to and stored by the Kafka brokers. As a result, the encrypted record values in the mssqlhost.inventory.users topic will appear with protected fields, such as the email,dob,mobile and address. column, reflecting the applied encryption.</p><p>To verify the records are encrypted, use the following command in the `kcctl` container. The output should show the data in an encrypted format.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*Ch3mlbXp-4461Ti6Jqn2qw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*b31hiO4ynbDLRrXWEFF4aQ.png" /></figure><h3>4. Configure Sink Connector with SMT</h3><p><strong>1. Create Postgres table</strong></p><p>Execute the Postgres Docker container and enter it using the following Docker command:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*hnhGUgVFT69SoltPRUYKgQ.png" /></figure><p>To enter into Postgres database CLI, run the following command:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/942/1*o1ZI1m1L7ZDJ611WN9125Q.png" /></figure><p>Create users table in Postgres:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3Obq5UA-Bgfd3espG6AwYA.png" /></figure><p><strong>2. Create jdbc sink connector</strong></p><p>Create jdbc sink connector with the following property to ingest encrypted records into the table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ScFBTL-imgNGkiHtkMTsrQ.png" /></figure><p>Install jdbc sink connector using kafkacat command:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MQ04LHx66Bj71o7oRvzzTw.png" /></figure><p>Once the connector is in ready state, please verify the data into Postgres users table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*b31hiO4ynbDLRrXWEFF4aQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*b31hiO4ynbDLRrXWEFF4aQ.png" /></figure><h3>Conclusion</h3><p>This guide outlines a practical approach to implementing client-side field-level encryption for Kafka using PII-SMT. Beyond securing sensitive data in streaming pipelines, this solution can be extended to a variety of use cases, such as compliance with data privacy regulations (e.g., GDPR, HIPAA), protecting financial or healthcare data, and enabling secure data sharing between teams. Future improvements may include support for more complex data types, integration with centralized key management systems, and better tooling for schema evolution and audit logging. Organizations adopting this approach can enhance their data security posture while maintaining flexibility and scalability in their streaming architecture.</p><p>For a high-level overview, please refer to <a href="https://jungleegames.medium.com/pii-smt-client-side-field-level-encryption-decryption-for-apache-kafka-part-1-9ade835518a2">part 1</a>.</p><p><strong><em>Contributors</em></strong></p><ul><li><a href="https://www.linkedin.com/in/panditrahulsharma">Rahul Sharma</a>, Data Platform Engineer-III</li><li><a href="https://www.linkedin.com/in/guramritpal/">Guramritpal Saggu</a>, Data Platform Engineer-II</li><li><a href="https://www.linkedin.com/in/deepanshu-nagpal-90950826a/">Deepanshu Nagpal</a>, Lead Data Engineer</li><li><a href="https://www.linkedin.com/in/vinod-keshav-seetharamu-220306b/">Vinod Keshav Seetharamu</a>, AVP — Data Engineering</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6829afef2cec" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PII-SMT: Client-Side Field-Level Encryption/Decryption for Apache Kafka — Part 1]]></title>
            <link>https://jungleegames.medium.com/pii-smt-client-side-field-level-encryption-decryption-for-apache-kafka-part-1-9ade835518a2?source=rss-35891010b23e------2</link>
            <guid isPermaLink="false">https://medium.com/p/9ade835518a2</guid>
            <dc:creator><![CDATA[Junglee Games]]></dc:creator>
            <pubDate>Fri, 14 Feb 2025 06:49:55 GMT</pubDate>
            <atom:updated>2025-02-14T06:49:55.972Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>PII-SMT: Client-Side Field-Level Encryption/Decryption for Apache Kafka — Part 1</strong></h3><p>PII-SMT is a proprietary transformation library designed to perform field-level encryption and decryption of records as they flow into and out of Apache Kafka.</p><p>Below is the flow of the article, which will be followed by Part 2 soon.</p><ol><li>Objective</li><li>Initial Flow</li><li>What is SMT?</li><li>How PII-SMT Works</li><li>Supported Features of PII-SMT</li><li>Key Features</li><li>Data Type Support</li></ol><h3>1. Objective</h3><p>At Junglee Games, data is critical for making business decisions, and it is equally important to store data securely and maintain its confidentiality using best-in-class technological solutions. We consume data from Kafka and store it in various layers in S3. Although our S3 objects are encrypted at rest, we wanted to implement field-level encryption for enhanced security as well as to make it compliant with the upcoming <a href="https://www.meity.gov.in/writereaddata/files/Digital%20Personal%20Data%20Protection%20Act%202023.pdf">DPDP</a> (Digital Personal Data Protection) law. To address this, we developed a Single Message Transform (SMT) that encrypts records before they are published in Kafka. This ensures that only encrypted data is stored at rest, and downstream jobs read the encrypted data securely, maintaining the integrity and confidentiality of our data lake.</p><h3>2. Initial Flow</h3><p>In the initial flow, we consumed data from various sources and performed necessary transformations while storing data in S3. This approach, while compliant with standard data protection requirements, may have fallen short of today’s standards. While Kafka provided SSL for secure communication, we wanted to add another layer of security by encrypting the PII fields during Kafka transit.</p><h3>3. What is SMT?</h3><p>Kafka Single Message Transform (<a href="https://docs.confluent.io/platform/current/connect/transforms/overview.html">SMT</a>) is a powerful feature within Kafka Connect that allows real-time transformations of data as it flows through connectors. SMTs are lightweight transformations applied to each individual message, enabling modifications without the need for additional processing layers. They can be used to mask, rename, filter, or, as with PII-SMT, encrypt specific fields, adding flexibility and security to data pipelines. By leveraging SMTs, organizations can enforce data transformation policies (e.g., field-level encryption) directly within Kafka Connect, making it easier to meet privacy and compliance requirements while managing data in transit.</p><h3>4. How PII-SMT Works</h3><p>Before PII-SMT for Kafka, there was no simple, open-source solution for client-side field-level encryption in Kafka. While encryption could be done at the client or with intermediary tools, these methods added complexity. PII-SMT addresses this by enabling encryption and decryption directly within Kafka Connect, allowing worker nodes to secure designated sensitive fields in ConnectRecord instances. This SMT provides ready-to-use field-level encryption using AES(Advanced Encryption Standard) in GCM(Galois/Counter Mode) or SIV(Synthetic Initialization Vector) mode and works with any message format, even adapting for schema-aware formats like AVRO. The CipherField SMT easily integrates into Kafka Connect pipelines to protect sensitive data from unauthorized access.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*065qN5g7_HONVmSz" /></figure><p>Use the concrete transformation type designed for the record key <em>(com.rp.kafka.connect.transforms.cypher.Cryptography$key) or value (com.rp.kafka.connect.transforms.cypher.Cryptography$Value).</em></p><p><strong>Examples</strong></p><p><strong>Encrypt value</strong></p><p>This configuration snippet shows how to use cipher to encrypt the value of a field:</p><p><em>{<br>“Transforms”:”cipher”,<br>“transforms.cipher.type”:”com.rp.kafka.connect.transforms.cypher.Cryptography$Value”,<br>“transforms.cipher.aws_secretsmanager_key”:”**********”,<br>“transforms.cipher.field_config”:”[{“encryption_type”:”non-deterministic”,”pii_field_name”:”mobile”},{“pii_field_name”:”dob”}]”<br>}</em></p><p>A cipher <em>mobile and dob</em> transforming the original message as seen here<br><em>{“mobile”:12345678, “dob”:”15–12–1990&quot;}</em><br>into the result here:<br><em>{“mobile”:”jkfwnekjfnwe==”, “dob”:”klkdqwkjdqwkk==”}</em></p><h3>4. Supported features of PII-SMT</h3><p>The PII-SMT (Single Message Transform) connector is designed to support a range of encryption features suitable for various data privacy and security needs, with options for AWS KMS integration, nested and multi-table encryption, and both schema and schemaless data encryption.</p><h4>Key Features</h4><ol><li><strong>Encryption Algorithm Support</strong></li></ol><ul><li>AES Algorithm: Supports both deterministic and non-deterministic encryption types.</li><li>AWS KMS Integration: Allows the direct use of AWS KMS-encrypted keys by specifying the SM key ID, which references the KMS key.</li></ul><p><strong>2. Encryption Modes</strong></p><ul><li>Single Source Connector with Single Table Encryption: Encrypts data from a single source and single table.</li><li>Single Source Connector with Multi-Table Encryption: Supports encryption across multiple tables within a single source.</li><li>Multi-Table Encryption with Multi-Encryption Keys: Allows each table within a source to be encrypted with a distinct encryption key.</li></ul><p><strong>3. Data Schema Support</strong></p><ul><li>Schema and Schemaless Data Encryption: Supports encryption for both structured data with a predefined schema and schemaless data (such as JSON).</li></ul><p><strong>4. Nested Column Encryption</strong></p><ul><li>Provides support for encrypting nested columns, making it suitable for handling hierarchical data structures like JSON in schemaless formats.</li></ul><h4>Data Type Support</h4><p>The PII-SMT connector supports the following data types for encryption:</p><ul><li>Integer (Int)</li><li>Float</li><li>String</li><li>Date</li><li>Timestamp</li><li>Null values</li></ul><p><strong><em>Contributors</em></strong></p><ul><li><a href="https://www.linkedin.com/in/panditrahulsharma">Rahul Sharma</a>, Data Platform Engineer-2</li><li><a href="https://www.linkedin.com/in/guramritpal/">Guramritpal Saggu</a>, Data Platform Engineer-2</li><li><a href="https://www.linkedin.com/in/deepanshu-nagpal-90950826a/">Deepanshu Nagpal</a>, Lead Data Engineer</li><li><a href="https://www.linkedin.com/in/pankajarora19/">Pankaj Arora</a>, Associate Director — Data Engineering</li><li><a href="https://www.linkedin.com/in/vinod-keshav-seetharamu-220306b/">Vinod Keshav Seetharamu</a>, AVP — Data Engineering</li></ul><p>Stay tuned for <strong>Part 2,</strong> which will be published in the coming days.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9ade835518a2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Migration to .hyper File Creation: A Game Changer for Data Extracts and Cost Efficiency]]></title>
            <link>https://jungleegames.medium.com/migration-to-hyper-file-creation-a-game-changer-for-data-extracts-and-cost-efficiency-e3c9a87e7482?source=rss-35891010b23e------2</link>
            <guid isPermaLink="false">https://medium.com/p/e3c9a87e7482</guid>
            <dc:creator><![CDATA[Junglee Games]]></dc:creator>
            <pubDate>Mon, 11 Nov 2024 10:52:29 GMT</pubDate>
            <atom:updated>2024-11-11T10:54:30.297Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Migration to .hyper File Creation: A Game Changer for Data Extracts and Cost Efficiency</strong></h3><p>In today’s fast-paced business environment, optimizing data processing and visualization is crucial. We recently undertook a significant transformation by migrating to .hyper file creation for our major Tableau extracts related to Rummy, Poker, and Howzat. This strategic shift has not only resulted in remarkable performance gains but also led to substantial cost savings.</p><h3>Challenges faced</h3><p>Previously we relied on a traditional method in which Tableau queries Trino (S3 in backend) and after querying the data, Tableau pulls the results into its environment, where it is used to create an extract (like a .hyper file). However, this approach posed several challenges.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*a07aLoVqCPRP8g4n" /><figcaption>Old flow</figcaption></figure><ul><li><strong>High costs</strong>: Higher Tableau server cost, primarily due to extended extraction times and computational resource usage.</li><li><strong>Performance bottlenecks</strong>: Some extracts ran for several hours, leading to delays and inefficiencies.</li><li><strong>Scalability issues</strong>: Increasing data volumes made it difficult to scale our existing setup as the actual extract creation (hyper file generation) still relied on single-node execution with parallelism within that node.</li></ul><h3>What is a .hyper file?</h3><p>A <a href="https://www.tableau.com/products/new-features/hyper">.hyper</a> file is a proprietary format used by <a href="https://www.tableau.com/"><strong>Tableau</strong></a><strong>, </strong>a leading data visualization tool. Introduced in Tableau 10.5, the .hyper format leverages an in-memory data engine designed to handle large datasets with speed and efficiency. It offers</p><ul><li><strong>High-speed data processing</strong> for faster data ingestion and queries.</li><li><strong>Optimized compression</strong> to reduce storage requirements.</li><li><strong>Concurrency and parallelism,</strong> allowing multiple users to interact with data simultaneously.</li></ul><h4>File format supports</h4><ul><li>CSV</li><li>JSON</li><li>Parquet</li><li>Delta/Iceberg files</li></ul><p>Given these benefits, migrating to .hyper file creation has streamlined our data workflows and led to reduced costs and improved overall performance.</p><h3>The migration solution</h3><p>To address these challenges, we developed a <strong>custom framework</strong> for .hyper file creation using Python. Key steps included</p><ol><li><strong>Custom framework</strong>: Python scripts extract data from our data lake and create .hyper files, optimized for handling large datasets.</li><li><strong>Direct integration with Tableau</strong>: Once created, .hyper files are directly published to Tableau by leveraging the <strong>Tableau Hyper API.</strong></li><li><strong>Automation and scheduling</strong>: We automated the pipeline using Airflow, ensuring efficient and timely data delivery.</li></ol><h3>Cost savings and performance gains</h3><ul><li><strong>Cost savings</strong>: The monthly cost was reduced by approximately <strong>25%</strong> by eliminating traditional ETL operations.</li><li><strong>65% performance improvement</strong>: Data extracts that previously ran for hours now complete in a fraction of the time.</li><li><strong>Scalability</strong>: The new system is more scalable, enabling us to handle increasing data volumes without performance degradation.</li></ul><h3>Live extract examples</h3><p>We went live with approximately <strong>20 extracts</strong>, ranging from a few<strong> MBs to GBs</strong>, and observed significant performance gains. For example, for one of our heavy extracts related to gameplay, we observed that the publish time was reduced from <strong>12 hours</strong> to <strong>2 hours</strong> (1–1.5 hours for data preparation, 0.5 hours for publishing).</p><h3>Machine configuration</h3><ul><li><strong>Old Setup</strong>: 5 machines (c5a.16xlarge) costing over <strong>$100K</strong> annually.</li><li><strong>New Setup</strong>: 3 machines (r5.12xlarge) with over ~25% savings annually.</li></ul><h3>Bitbucket to Tableau: Automated flow</h3><p>We have empowered our BI team to directly modify and deploy .hyper files using a Bitbucket-based CI/CD pipeline.</p><ul><li><strong>Bitbucket BI Repo</strong>: Add/Update Python scripts with queries.</li><li><strong>CI/CD pipeline</strong>: Validate and deploy. The pipeline creates a folder in S3 and updates the <strong>Airflow master DAG</strong> for hyper extracts.</li><li><strong>Airflow pipeline</strong>: It generates the data, creates the hyper file, and publishes it to Tableau.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IjFCJpc7u2JYanZ1" /><figcaption>New automated flow</figcaption></figure><p><strong>Limitations</strong></p><ul><li>It does not support 128-bit data types.</li><li>We cannot publish multiple large extracts simultaneously as the process runs on a single node.</li><li>We can utilize only 40–45% of the total memory available on the node because it stores uncompressed data in the memory.</li><li>It stores the data in memory first, before starting the publishing process, so we should have enough memory available on the node.</li><li>We cannot perform incremental updates directly. We need to overwrite the existing data every time.</li><li>Slower performance with very large files or very small files.</li><li>Troubleshooting can be challenging due to the lack of clear logs available in the Python API.</li></ul><p><em>Contributors:</em></p><ul><li><a href="https://www.linkedin.com/in/shivambansal08021998/">Shivam Bansal</a>, Data Engineer</li><li><a href="https://www.linkedin.com/in/ashwinshankar01">Ashwin Shankar</a>, Data Engineer</li><li><a href="https://www.linkedin.com/in/deepanshu-nagpal-90950826a/">Deepanshu Nagpal</a>, Lead Data Engineer</li><li>Yatin Lekhi, Manager Analytics</li><li><a href="https://www.linkedin.com/in/rama-b-a60b0172?lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base_contact_details%3BfDwgI3MbSimMgSwhDKnc8Q%3D%3D">Ramakoteswararao bandla</a>, Tableau Administrator</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e3c9a87e7482" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tagging customer queries via Natural Language Processing (NLP) for incredibly fast query resolution]]></title>
            <link>https://jungleegames.medium.com/tagging-customer-queries-via-natural-language-processing-nlp-for-incredibly-fast-query-resolution-185398688c3?source=rss-35891010b23e------2</link>
            <guid isPermaLink="false">https://medium.com/p/185398688c3</guid>
            <category><![CDATA[tensorboard]]></category>
            <category><![CDATA[nlp]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[deep-learning]]></category>
            <category><![CDATA[automation]]></category>
            <dc:creator><![CDATA[Junglee Games]]></dc:creator>
            <pubDate>Wed, 27 Jan 2021 11:41:17 GMT</pubDate>
            <atom:updated>2021-01-27T11:41:17.602Z</atom:updated>
            <content:encoded><![CDATA[<p>Junglee Games is one of the leaders in India’s online gaming and handles over 2 million games every day. It has hundreds of thousands of daily active players visiting the platform daily and will continue to grow in scale as India opens up to the online gaming arena rapidly. As you can imagine, with Junglee’s expanding user base, the customer care system needs to be well equipped to resolve customer queries and pain points in a quick and efficient manner. Speedy customer issue redressals are important, not just in terms of engagement but for creating trust within the gaming community and brand loyalty.</p><p>As part of the Machine Learning team, we are responsible for identifying opportunities in areas of experience during gameplay, customer engagement, fair play, and building advanced algorithms to address them. We saw an opportunity in optimizing our Customer Experience (CX) department’s time in the way they handle complaints. We used Natural Language Processing to streamline one of the key processes of customer query redressal. Here’s how we did it.</p><p><strong>Problem Statement</strong></p><p>Our customers can raise queries through various channels. Complaints received via the app and emails are a major proportion of the complaints that the CX team receives. Our CX agents get to see these complaints on the Salesforce UI which is our primary CRM tool. Subsequently, an agent manually tags these queries and routes them to relevant teams. Tagging involves identifying the category and the sub-category which in turn, also helps us prioritize these queries. Below are some examples of categories and sub-categories:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/562/0*rxK2RKaz7PYU8JUZ.png" /></figure><p>Manual tagging has been a bottleneck for us for a long time and we wanted to automate the process, focusing mainly on emails. Emails are in the form of free text as compared to app-generated complaints where customers choose their issue from a list of drop-downs. Hence manual categorization requires more time and effort for emails.</p><p>This article shares our insights on 2 broad sections detailing how we automated tags for customer complaints:</p><ul><li><em>Modeling</em>: Data Exploration, Model Architecture (Word Vectorization, CNNs, Hybrid Neural Networks), Thumb Rules</li><li><em>Deployment</em>: Simple Salesforce API, Salesforce Workbench, Data query and updating through Salesforce API</li></ul><h3><strong>Modeling</strong></h3><p>Just like any modeling exercise, we went about improving the model through various iterations of hyperparameter tuning. The diagram below should give a view of the process:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1001/0*_5hz1NWpChpHCGqf.png" /></figure><p>We had several months of data tagged with the subject and the mail body taken from Salesforce for us to build a <strong>supervised multi-class classification model</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/261/0*mwV_J55usyhvkQEj.png" /></figure><p><strong><em>Data Exploration</em></strong></p><p>We came across some interesting challenges during the data exploration phase</p><ul><li><em>High number of labels</em>: There are 40+ subcategories to which a case can be tagged. Additionally, these labels can have overlapping meanings which can lead to ambiguity in training. We resolved this by clubbing labels with similar meanings. . Eliminating ambiguity to the modeling input was crucial to higher accuracy.</li><li><em>HTML tags, revert snippets in email texts</em>: Extensive data cleaning was done to remove special characters and unwanted text snippets.</li><li><em>Incorrect spellings, mixed language</em>: This prompted us to adopt an approach that uses context modeling instead of keyword-based modeling. We will cover this in more detail later.</li></ul><p>Post target variable identification and data exploration, we moved into the model building phase</p><p><strong>Model Architecture</strong></p><p>Our final architecture had various components to it, just like most well-trained neural networks. The model is a mix of independently trained and concurrently trained models that finally get combined to predict the tags given the text data and non-text data. Below are the details related to the final model.</p><p><strong><em>Modeling on Text Data</em></strong></p><p>The text data is passed into the Word2Vec algorithm which learns word meanings. The learned meanings are then given as input to a Convolutional Neural Network (CNN) to identify patterns within the text data. More on these below.</p><p><em>Word2Vec: </em>The seminal word2vec algorithm by<em> </em><a href="https://arxiv.org/abs/1301.3781">Mikolov et al</a> has many applications and has pretty much been universally accepted as state-of-art. It helped us to convert our text data into a feature vector. Given the huge corpus of words we had that included spelling errors as well, we generated word embeddings using word2vec for dimensionality reduction and calculating word similarities. As a result, words like ‘tournament’ and ‘turnament’ or any such misspelled word will be similar in the transformed vector space even if frequency of ‘turnament’ occurring in the dataset is low. The model was trained independently using the ‘gensim’ package in python. We used CBOW (<strong>Continuous Bag of Words</strong>) as an option to predict the probability of a word given the context. The final embedding size was of 100 dimensions which gave us the best results while tuning the model.</p><p>The word similarities inferred from Word2Vec can be visualized on <a href="https://www.tensorflow.org/tensorboard/tensorboard_projector_plugin">Tensorboard</a> as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bEpAuysm8yKODVf0DKg9ng.gif" /><figcaption>Words most similar to ‘tournament’</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yxT1onkGfjR0txQe4QBegQ.gif" /><figcaption>Words most similar to ‘rummy’</figcaption></figure><p>Word2Vec model training:</p><pre>from gensim.models.word2vec import Word2Vec<br>from gensim.models.doc2vec import TaggedDocument<br>from sklearn import utils# Create unique tags for each document</pre><pre>def labelize(text,label):<br>    result = []<br>    prefix = label<br>    for i, t in zip(text.index, text):<br>        result.append(TaggedDocument(t.split(), <br>                      [prefix + &#39;_%s&#39; % i]))<br>    return resultdef CBOW_model(textseries, epochs=30, size=100):<br>    all_x = textseries<br>    all_x_w2v = labelize(all_x, &#39;all&#39;)# Define model parameters<br>    model_cbow = Word2Vec(sg=0, size=100, <br>                             negative=5, window=2, <br>                             min_count=2)<br>    model_cbow.build_vocab([x.words for x in all_x_w2v])<br>    for epoch in range(epochs):<br>        model_cbow.train(utils.shuffle([x.words for x <br>                            in all_x_w2v]),<br>                            total_examples=len(all_x_w2v), epochs=1)<br>    return model_cbow</pre><pre>model_cbow = CBOW_model(df[&#39;text&#39;], epochs=30, size=100)</pre><p>The inferred word meanings were transferred to the neural network model using a pre-trained word embedding matrix. Word embedding matrix is nothing but a matrix version of mapping for each word in the corpus to the vector space.</p><p>Building the word to vector embedding matrix:</p><pre># NUM_WORDS: number of words retained in corpus</pre><pre>def create_embedding_matrix(model_cbow,text_column, NUM_WORDS):<br>    embeddings_index = {}<br>    <br>    # Create dictionary: Word to Vector Mapping<br>          # key = vocabulary words. # value = word vector<br>    for w in model_cbow.wv.vocab.keys():<br>        embeddings_index[w] = model_cbow.wv[w]</pre><pre>    # List containing no of words in each case<br>    length = []<br>    for x in text_column:<br>        length.append(len(x.split()))<br>    <br>    # Word to Vector Mapping from dictionary to matrix<br>    tokenizer = build_tokenizer(text_column, NUM_WORDS)    <br>    embedding_matrix = np.zeros((NUM_WORDS, 100))<br>    for word, i in tokenizer.word_index.items():<br>        if i &gt;= num_words:<br>            continue<br>        embedding_vector = embeddings_index.get(word)<br>        if embedding_vector is not None:<br>            embedding_matrix[i] = embedding_vector<br>            <br>    return embedding_matrix</pre><pre>embedding_matrix = create_embedding_matrix(model_cbow,<br>                               df[&#39;text&#39;], NUM_WORDS)</pre><p><em>Convolutional Neural Networks (CNN)</em>: A 1D CNN is effective when we expect to derive interesting features from shorter (fixed-length) segments of the overall data set and where the location of the feature within the segment is not of high relevance.</p><p>With the tokenized sequence from each case as input, a set of filters convolve over the word embedding matrix. The objective of the convolution is to identify patterns that will minimize the model loss function. The weights within the filters are the trainable parameters and the model iterates on these weights to identify the best patterns.</p><p>We will take a look at a simplified example to understand 1-dimensional convolution:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/0*vAj0yRLRDpXLmAFR.png" /></figure><p>The words have been transformed to a 4-dimensional vector space. Convolution is applied for the text ‘Upload KYC documents’ with 2 filters and a kernel size of 2. Kernel size gives the number of rows in each sub-matrix to be multiplied with the filters. In the example above, there will be 2 sub-matrices: [[0.5,-1.2,3.6,4.5],[2.4,4.5,-0.6,0.0]] and [[2.4,4.5,-0.6,0.0],[-3.2,5.0,1.0,-4.2]] which will be scalar multiplied with each filter f1 and f2. For a sequence with n words convolved through a kernel size of m and number of filters f, the output size = [(n-m+1),f]; [(3–2+1),2] in our case.</p><p>A Global Average Pooling Layer is added to the output of the convolution which gives the average value for each filter. Pooling is done to reduce the dimensionality of the output and can be thought of in terms of images as downscaling the pixel size.</p><p><strong><em>Modeling on Non-text Data:</em></strong></p><p>An important observation during data exploration was that the labels were influenced by information outside the text of the complaint as well. For example, the domain name of the sender’s Email ID helps identify Spam emails. This information was learned through a separate Multi-Layer Perceptron network. Following is a subset of the non-text feature list used in the model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/614/0*lfWcvsQlSfDsJLmZ.png" /></figure><p><strong><em>Combining the two models</em>, </strong>we get a Hybrid Neural network structure as shown below. The CNN and MLP models are trained concurrently and the output nodes from the 2 models are connected to a new hidden layer. This is then connected to the output layer which outputs probabilities for each subcategory. The subcategory having the highest probability is selected as the subcategory for the case.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/758/0*NHxfdxdKczzL8tTu.png" /></figure><p>Data was divided into 3 sets — training (81%), validation (9%) and testing (10%). Multiple iterations were performed on the number of neurons, filter dimensions in the Convolution layer to get the best model. The model with the maximum accuracy on testing and validation sets was chosen, at the same time ensuring that the model is not overfitting</p><pre>from keras.models import Sequential<br>from keras.layers import Conv1D, GlobalAveragePooling1D, Embedding<br>from keras.layers import concatenate, Model, layers</pre><pre># NUM_WORDS: number of words retained in corpus<br># EMBEDDING_DIM: dimensions of embedding matrix<br># MAXLEN: length for a sequence:<br>#           if length &gt; MAXLEN, sequence is trimmed<br>#           if length &lt; MAXLEN, sequence is padded with 0s</pre><pre># Define MLP network<br>def create_mlp():<br>    model = Sequential()<br>    model.add(Dense(100, <br>               input_dim=X_train_mlp.shape[1], activation=&quot;relu&quot;))<br>               model.add(Dense(50, activation=&quot;relu&quot;))<br>    return model</pre><pre># Define CNN network<br>def create_cnn():<br>    model = Sequential()<br>    model.add(layers.Embedding(NUM_WORDS, EMBEDDING_DIM, <br>              weights=[embedding_matrix],<br>              input_length=MAXLEN, trainable=True))<br>    model.add(Conv1D(filters=128, kernel_size=2, <br>              padding=&#39;valid&#39;, activation=&#39;relu&#39;, strides=1))<br>    model.add(GlobalAveragePooling1D())<br>    model.add(Dense(100, activation=&#39;relu&#39;))<br>    return modelmlp = create_mlp()<br>cnn = create_cnn()</pre><pre># Combine input from MLP and CNN and connect to a new dense layer<br>combinedInput = concatenate([mlp.output, cnn.output])<br>x = Dense(50, activation=&quot;relu&quot;)(combinedInput)<br>x = Dense(Y_train_enc.shape[1], activation=&#39;softmax&#39;)(x)<br>model = Model(inputs=[mlp.input, cnn.input], outputs=x)<br>model.compile(loss=&#39;categorical_crossentropy&#39;, <br>              optimizer=&#39;adam&#39;, metrics=[&#39;accuracy&#39;])<br>model.fit([np.array(X_train_mlp), X_train_cnn_seq],Y_train_enc, <br>          validation_data=([np.array(X_val_mlp), X_val_cnn_seq],<br>                           Y_val_enc),<br>           epochs=10, batch_size=32)</pre><p>Besides inferencing based on text and non-text data from complaints, there were some thumb rules which were coded up so as to run on top of the model’s outputs.</p><p><strong><em>Thumb Rules</em></strong></p><p>These are some inherent rules in the manual categorization process which do not need the model to make predictions. The thumb rules were the most important accuracy booster. For example, when customers send empty emails, the email is categorized under the Subcategory ‘Blank Email’. A simple rule has been written once the model predictions are made to check the number of characters in the mail and all emails with less than 5 characters are tagged to a Blank Email, irrespective of model predictions.</p><p>This was a crucial step and needed frequent deliberations with the CX team. It was important to completely internalize the existing process of categorization by the agents.</p><h3><strong>Deployment</strong></h3><p>Once the model building was complete, the next step was deploying it on the Salesforce platform. Since Salesforce is a widely used tool, various APIs serving different purposes are available. We used the API SimpleSalesforce (SF) for Python (read the documentation <a href="https://pypi.org/project/simple-salesforce/0.3/">here</a>). This is a powerful API that enables you to query data from the Salesforce backend and perform update operations for records.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/0*V947EqK-Uh_jx3OM.png" /></figure><p><em>Data Pull</em>: Untagged complaints are pulled from SF API using <em>sf.query_all(query)</em> command. Input is in the form of SOQL queries which is similar to a SQL query. The architecture of table names and column names within Salesforce can be viewed from <a href="https://workbench.developerforce.com/login.php?startUrl=%2Fquery.php">SalesForce WorkBench</a></p><p><em>Model Predictions</em>: Once complaints to be categorized are obtained, a <em>get_predictions</em> function is called which gives the model output subcategories and categories. We set the <em>get_predictions</em> such that all additional data pulls (customer details etc), data cleaning, thumb rules on top of predictions are included in the function. This makes the structure more readable and easier to interpret</p><p><em>Update Categories</em>: The Category and Subcategory for each complaint is updated using the <em>sf.bulk.Case.update(output_dictionary) </em>command. This performs a bulk update instead of sequential updates and hence is faster when number of complaints to be updated each time is higher than 10</p><p>Input is to be passed in the form of a list of dictionaries. One dictionary corresponds to 1 complaint with 1 key value pair in each dictionary for the Id of the complaint (Primary key). Columns that need to be updated are then passed as other key value pairs</p><p><em>Testing</em>: Salesforce offers a testing server called Salesforce Sandbox to experiment with new functionalities. The System Integration testing (SIT) was done on this server before deploying into the main Production server</p><p><em>Logging</em>: All complaints updated by the model are logged. The associate has the option to overwrite the predictions by the model on the Salesforce platform if an incorrect prediction is made by the model. This helps us track model performance.</p><p><em>Deployment Environment</em>: The Python Scripts were simply deployed on a Windows Task scheduler on a Virtual Machine. A time volume analysis of complaints was performed to determine the ideal frequency of update to the Salesforce server.</p><pre>from simple_salesforce import Salesforce</pre><pre># Create Salesforce connection<br>sf = Salesforce(<br>username= USERNAME, <br>password= PASSWORD, security_token=TOKEN)</pre><pre># Query data<br>sf_data = sf.query_all(query)<br>df = pd.DataFrame(sf_data[&#39;records&#39;]).drop(columns=&#39;attributes&#39;)</pre><pre># Get model predictions in the desired output format<br>output_dicts = get_model_predictions()</pre><pre># Update Salesforce data tables<br>sf.bulk.Case.update(output_dicts)</pre><h4><strong>Results</strong>:</h4><ul><li>The accuracy after deployment was similar to the testing and validation accuracies: 82% at Category and 74% at the subcategory level.</li><li>The weighted precision and recall for the trained model was 74% and 76% respectively. This is pretty decent for a textual dataset with more than 40 different labels and emails that can include vernacular language.</li><li>For datasets with a lower number of labels, techniques like Correlated topic modeling, Latent Dirichlet Allocation can give decent performance as well.</li></ul><p>While we may have solved for a few million daily games, we’re gearing up for tens of millions. The NLP model can be further extended from pure categorization to send auto-responses to customers for cases where we are fairly confident of the model’s predictions<em>. </em>The significant savings in time achieved here will in turn enable our Customer Experience (CX) department to get through higher number of complaint redressals as we grow in scale each day</p><p>At Junglee Games, we love solving complex problems. If you’re interested in solving incredible ML problems in a high growth environment, drop us a note at <a href="/service/mailto:careers@jungleegames.com">careers@jungleegames.com</a></p><p><a href="https://www.linkedin.com/in/nevin-nicholas/">-Nevin Nicholas</a>, <a href="https://www.linkedin.com/in/vivek-kapoor-881b0b10/">Vivek Kapoor</a>, <a href="https://www.linkedin.com/in/avishek-das-36972a10/">Avishek Das</a>, <a href="https://www.linkedin.com/in/aakashdhaundiyal/">Aakash Dhondiyal</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=185398688c3" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>