<?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[Explorium.ai - Medium]]></title>
        <description><![CDATA[Explorium offers a first of its kind end-to-end automated external data platform. Our unique solution automatically connects and integrates the most relevant external data signals out of thousands of sources to optimise and accelerate your ML investment and advanced analytics. - Medium]]></description>
        <link>https://medium.com/explorium-ai?source=rss----bb1f897d2958---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Explorium.ai - Medium</title>
            <link>https://medium.com/explorium-ai?source=rss----bb1f897d2958---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 12:51:08 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/explorium-ai" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Building a Recommender System for Data Enrichment]]></title>
            <link>https://medium.com/explorium-ai/building-a-recommender-system-for-data-enrichment-b289a1a054c5?source=rss----bb1f897d2958---4</link>
            <guid isPermaLink="false">https://medium.com/p/b289a1a054c5</guid>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-enrichment]]></category>
            <category><![CDATA[neural-networks]]></category>
            <category><![CDATA[recommender-systems]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Yaniv Goldfrid]]></dc:creator>
            <pubDate>Thu, 24 Jun 2021 13:48:05 GMT</pubDate>
            <atom:updated>2021-06-24T13:48:05.227Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tPaCV8TK-TUMRYIG69XAaA.png" /></figure><h3><strong>What are Recommender Systems?</strong></h3><p>You’ve seen it everywhere. Every big tech company is now using some kind of Recommender System in their platform. <strong>Facebook</strong> suggests friends, <strong>Netflix</strong> recommends movies, same with <strong>Youtube</strong> and videos and <strong>Amazon</strong> recommends… well everything. Why do they do it? Well, every case is different but in general, good recommendations mean a better user experience and they will find new ways to keep users engaged and satisfied with the service. Happy users equal recurring users.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/833/1*iSdMod-tK4_6-svm9iPg3g.png" /><figcaption>This is not an ad, it’s Netflix recommending TV shows :D</figcaption></figure><p>But should we implement a recommender system just because all the cool kids are doing it? We need to think about our <strong>business needs</strong> and determine whether or not a system like this will <strong>add value</strong> to our service. At <a href="http://www.explorium.ai/">Explorium </a>we provide our users with thousands of <strong>data enrichment</strong> signals to enrich their predictive models and advanced analytics, but sometimes it is difficult to find the most relevant data enrichment signals among the variety and volume of external data. In order to help organizations identify the data signals with the highest impact on their analysis and predictions, we built a Data Enrichment Recommender System. This article explains how the recommender system was designed and built.</p><h3><strong>Implementing a Recommender System</strong></h3><p>First, let’s talk about the theory behind Recommender Systems. There are many different approaches:</p><ol><li><strong>Content-based:</strong> As the name states, these recommendations are based on the <strong>content</strong> of the product we are recommending. For example, if a user enjoys mystery novels, we are going to recommend more mystery novels to them.</li><li><strong>Collaborative Filtering: </strong>In this case, the user&#39;s <strong>preferences</strong> are stored (for example as ratings) and then we find similar users in terms of their preferences. That way if we know two users are alike, we can recommend a product one of them enjoyed to the other.</li><li><strong>Hybrid: </strong>A mix between the two, recommending products based on their content and also based on similar user’s preferences.</li></ol><p>In this case, I used the <strong>Collaborative Filtering</strong> approach.</p><p>Let’s take the simplest example of the Collaborative Filtering method. Let’s say we have a matrix where rows are <strong>users</strong> and columns are <strong>books</strong>. In each cell, we have a value between 1 and 5 which represents the <strong>rating</strong> a user gave to a book. If the cell is blank it means the user didn’t read that book.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/539/1*_ja5302zxAzzOhyBWYR02Q.png" /><figcaption>Here our goal is to predict the missing values in the matrix</figcaption></figure><p>What we want to do here is recommend books that people will enjoy. In other words, we should recommend a book to a person only if that person would give a high score to that book after reading it. But how can we know the score a person will give to a book? We need to <strong>predict </strong>it with the information we have. If this sounds like a Machine Learning task it’s because it is.</p><h4>Matrix Factorization to the rescue</h4><p>The 2 most common techniques to deal with an issue like this consist of decomposing the matrix into smaller matrices and then performing some extra calculations with them:</p><ul><li><strong>Singular Value Decomposition (SVD):</strong> Both for negative and positive values. Useful for calculating the Eigen Vectors. It doesn’t work with missing values so value imputation is needed which might bring extra noise. One classic example is PCA</li><li><strong>Non-negative Matrix Factorization (NMF):</strong> Only positive values. It works great with sparse matrices since the missing value assumption is built into the algorithm.</li></ul><p>In our problem, we have a big sparse matrix and we need to predict the missing values. So naturally, it makes more sense to use the NMF technique.</p><p>Remember in elementary school when you learned about basic factorization? I’ll refresh your memory. It consists of finding the factors of a number such that when you multiply them, the result will be the original number. For example, if we factorize 15 we will get 3 and 5.</p><p>Now, matrix factorization follows the same principle. We need to find the factors of a matrix. The result will be two matrices such that when you multiply them, the result will be the original matrix.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/0*aWs9lrTAgkC9fRXh" /><figcaption>We need to find the embedding matrices</figcaption></figure><p>The resulting matrices are called <strong>Embedding Matrices</strong>. The first one will be an embedding matrix for the Users and the second one an embedding matrix for the Books. <strong>An embedding is just a vector representation of something.</strong></p><p>Once we have the embedding matrices the problem is solved. We need only to store them and whenever we want to predict a score we <strong>multiply</strong> the respective row and column.</p><h4>Now a more complex example: Data Enrichment</h4><p>After understanding the User/Book example we can apply the same concept to our business problem. But first, we need to define 2 things here:</p><p><strong>Project: </strong>Simply a CSV file (or multiple CSV files linked together), so you can simply think of it as a table; <strong>rows and columns</strong>.</p><p><strong>Enrichment: </strong>Given a specific <strong>column</strong>, a data enrichment will match additional data according to the column type, shown as extra columns in the original table.</p><p>So for example, a project can be a table of companies where the columns are <strong>company_name</strong>, <strong>website</strong>, <strong>address</strong>, etc. And an enrichment for the company_name can add some extra columns such as <strong>phone_number</strong>, <strong>business_owner</strong>, etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/668/1*F0WLhgCoSEHDXyJJ2OAkxg.png" /><figcaption>Companies project</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/887/1*4NAqovNCVK9Ep7CZ6CTI6A.png" /><figcaption>Companies project after using a specific enrichment</figcaption></figure><p>Our “Users” will be the projects and our “Books” will be the actual data enrichments. We will recommend <strong>enrichments</strong> to a <strong>project</strong> analyzing similar projects and see what worked for them.</p><p><strong>But what should we use as the score? </strong>This is the first question we should ask ourselves. In our basic example, a human gave a rating between 1 and 5 to a book. A very clear way of knowing the score. But now we have projects and enrichments. Projects cannot do anything, they cannot talk, walk or fall in love, and they certainly cannot give 5 stars to an enrichment even if it really improved their performance (selfish projects). So we created a “relevance” score. This score is a counter to how many features of an enrichment made it to the best model of a project. In other words, it measures how relevant an enrichment is for a project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/368/1*ZAV3A13Fn6Z9WPHxqQFycg.png" /><figcaption>Relevance Score Table</figcaption></figure><h4>Modeling</h4><p>Once we have our data we need to build our <strong>Machine Learning</strong> model. As you can suspect, this is a <strong>Regression</strong> problem. We have the scores which many projects gave to many enrichments, and we want to predict the ones that are missing. There is a classic way of modeling this problem using a <strong>Neural Network</strong> and this is what we did here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/602/0*6dij20zymSkZRsUX" /><figcaption>Neural Network Model for Recommender Systems</figcaption></figure><p>There is one hyperparameter we need to tune, which is the size of the embedding (K). It could be any number but usually, a number between 20–50 gives good results. If we have N projects, our ProjectEmbedding layer will be of size N x K and if we have M enrichments, our EnrichmentEmbedding will be of size M x K.</p><p>I built this Neural Network using <strong>Tensorflow</strong>, but you can do it with <strong>PyTorch</strong> as well, there is no difference really.</p><p>After we create our model we need to train it of course. I did a very straightforward training, 100 epochs with <strong>Early Stop</strong> on the <strong>Validation Loss</strong> to prevent <strong>overfitting</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ewYjq2iLCswt2Oz_aPEEjw.png" /><figcaption>Train and Validation MSE during training</figcaption></figure><h4><strong>Predicting</strong></h4><p>After training our model the only thing remaining is to extract the <strong>Embedding Matrices </strong>(both for the projects and the enrichments)<strong> </strong>and use them for our predictions. If you are wondering what an Embedding Matrix looks like, here I’ll show you one, keep in mind it’s not very exciting, it’s just a bunch of numbers on a table. But we don’t like it because it looks cool, we like it because it’s useful for predicting scores (and thus, making recommendations)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/894/1*aHxtAjL8Sj6hFI5OvgBoLA.png" /><figcaption>Embedding Matrix for Enrichments</figcaption></figure><p>We can do many things with the Embedding Matrices now. For example, we could do some <strong>Principal Component Analysis (PCA)</strong> and try to cluster together some of the Enrichments that are similar to each other. Or we can calculate the <strong>Cosine Similarity </strong>numerically check which Enrichment is the most similar to a specific one.</p><p>Here you can see I used <strong>Tensorboard </strong>to visualize both things. We can see on the PCA plot how all OSM (open street maps) enrichments are clustered together and their cosine similarity proves they are very similar to each other.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KuLkdPJCfHnN5qa0cOaY7w.png" /><figcaption>PCA and Cosine Similarity on the Enrichment Embedding Matrix</figcaption></figure><p>As you can see our model did a pretty good job finding similarities between enrichments that we know are supposed to be similar. And now we are confident that it will perform well when actually recommending enrichments to a project. In order to do so we need to:</p><ol><li>Choose the <strong>Project</strong> we want to get enrichment recommendations for</li><li>Find its respective <strong>vector</strong> in the <strong>Project</strong> <strong>Embedded Matrix</strong></li><li>Perform a <strong>matrix multiplication</strong> between the vector and the entire <strong>Enrichment Embedding Matrix</strong></li><li>Sort the results</li><li>Choose the top 5 (or the number of recommendations you want)</li></ol><h3>Conclusion</h3><p>Recommender systems are incredibly useful for improving user experience and keeping users engaged. Nevertheless, this is not the only use for this kind of system. In this article, I showed how even in situations where recommendations are <strong>not directly for the users</strong>, we can apply the same concepts to solve specific business problems we may face. With this personalized implementation in <strong>Explorium</strong>, we can save a lot of time, money, and effort during the data enrichment process of its platform. This can truly revolutionize the way we tackle these kinds of problems and take us to the next level of the <strong>Auto-ML</strong> world.</p><p><em>Yaniv Goldfrid is an expert data and machine learning developer who is a member of the data science team of </em><a href="https://www.explorium.ai/"><em>Explorium</em></a><em>. Explorium offers the industry’s first automated External Data Platform for Advanced Analytics and Machine Learning. Explorium empowers data scientists and business leaders to drive decision-making by eliminating the barrier to acquiring and integrating the right external data and dramatically decreasing the time to superior predictive power. Learn more at</em><a href="http://www.explorium.ai/"><em> www.explorium.ai</em></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b289a1a054c5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/explorium-ai/building-a-recommender-system-for-data-enrichment-b289a1a054c5">Building a Recommender System for Data Enrichment</a> was originally published in <a href="https://medium.com/explorium-ai">Explorium.ai</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to dramatically increase your Elasticsearch throughput and concurrency capacity]]></title>
            <link>https://medium.com/explorium-ai/how-to-dramatically-increase-your-elasticsearch-throughput-and-concurrency-capacity-c32d7bb02ac2?source=rss----bb1f897d2958---4</link>
            <guid isPermaLink="false">https://medium.com/p/c32d7bb02ac2</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[elasticsearch]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Flomin Ron]]></dc:creator>
            <pubDate>Thu, 10 Jun 2021 08:22:08 GMT</pubDate>
            <atom:updated>2021-06-10T17:42:18.774Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Elasticsearch" src="https://cdn-images-1.medium.com/max/1024/1*lKD0gO08MxxhTkyAG_UJuA.png" /></figure><p>Every Data Engineer who uses Elasticsearch as a documents store, knows that there are many parameters that affect the queries latency, throughput, and eventually the Queries Per Second (AKA — QPS).</p><p>In one of our Projects at Explorium, we have an Elasticsearch cluster, hosted in AWS with 14 nodes of m5.4xlarge.elasticsearch.</p><p>In the cluster we have around 20M documents which are 10 GB.</p><p>This cluster receives tons of queries, rapidly and very frequently, and requires a very high concurrency of queries.</p><p>While the usage of the database got bigger and bigger, we noticed two things:</p><ul><li>Some queries were rejected by our Elasticsearch cluster due to a full internal Elasticsearch queue.</li><li>Our QPS was around 100 and we wanted to achieve a much higher rate.</li></ul><p>We decided to get deeper into Elasticsearch internals in order to solve these two issues.</p><p><strong>In this article I will introduce the changes we have made in order to increase our QPS by 7X and even support higher concurrency.</strong></p><p><strong><em>Indexes</em></strong></p><p>In our architecture, when new data is ingested, it is inserted into a dedicated daily index.<br>We decided to go with this strategy since we want to be able to rollback new indexed data.</p><p>If we would index the data into a single index, it would require us to submit hard deletes over the index in order to rollback a new indexed data (an action which we want to avoid).</p><p>During the performance tests, we had 20 daily indexes.</p><p><strong><em>Sizing</em></strong></p><p>Behind each Elasticsearch index there are Shards. Each shard is a single Lucene index and it holds part of the index data.</p><p>When you create an Elasticsearch index, you can specify how many shards it will contain.</p><p>Most people are looking for a magic number that will work the best for them, but actually, there isn’t.</p><p>The amount of wanted shards is affected by multiple parameters, such as:</p><ul><li>Index size</li><li>Document size</li><li>Amount of documents</li><li>Queries patterns</li><li>Data Structure</li></ul><p>In our use case, we had 10 shards per index.</p><p><strong><em>Read Replicas</em></strong></p><p>A read replica is a copy of the original Primary shard.</p><p>When creating an Elasticsearch index you can specify how many read replicas you want to create along with the index.</p><p>Read replicas are used in case of hardware failures and are also useful with serving the data (searches) in a high concurrency.</p><p>In our case, we had 1 read replica per shard.</p><h3>Now you know what cluster and index configurations we had, but how did we achieve this huge improvement in terms of QPS and concurrency? Let’s dive in.</h3><p><strong><em>Indexes</em></strong></p><p>As mentioned above, we had 20 daily indexes. Let’s multiply this by 10 (the original amount of shards per index).</p><p>This means that in each search, we submitted the query to 200 shards!</p><p>It made us think that we should decrease the number of shards that are being queried.</p><p>Since we had small indexes, we decided to start with reducing the number of indexes in our alias.</p><p>We implemented a mechanism that merges daily indexes into a large single index.</p><p>At the end of the process we ended with 1 Large index with 10 shards.</p><p><strong><em>Sizing</em></strong></p><p>When we talk about a single “query” over Elasticsearch, we need to split it into two tasks, query and fetch.</p><p>The <strong>query</strong> part contains sending the request from the client to the coordinating node and then sending the query to each shard in the index.</p><p>The <strong>fetch </strong>part is when each data node executes the query locally(among its shards), returns the results to the coordinating node and then the final result is returned to the client.</p><p>We found out that most of the time each submitted query to Elasticsearch is being spent on query time and not on fetch time.</p><p>This means that most of the time is being spent on sending the queries to the shards and not on the actual search.</p><p>You can find your index stats by using the _stats api:</p><p><em>GET index_name/_stats</em></p><p>You will get a document that includes statistics over the requested index.</p><p>If you look at the “_all_ field and then “search” field you will find a JSON in this structure:</p><pre>search” : {</pre><pre>“open_contexts” : 0,</pre><pre>“query_total” : 5,</pre><pre>“query_time_in_millis” : 0,</pre><pre>“query_current” : 0,</pre><pre>“fetch_total” : 1,</pre><pre>“fetch_time_in_millis” : 0,</pre><pre>“fetch_current” : 0,</pre><pre>“scroll_total” : 0,</pre><pre>“scroll_time_in_millis” : 0,</pre><pre>“scroll_current” : 0,</pre><pre>“suggest_total” : 0,</pre><pre>“suggest_time_in_millis” : 0,</pre><pre>“suggest_current” : 0</pre><pre>}</pre><p>Here you can see the “fetch_time_in_millis” and the “query_time_in_millis” so you can have an idea where most of the time is being spent in your queries over the index.</p><p>We realized that we should take immediate action: Reducing the number of shards per index to 6.</p><p>Why 6? We tried 3, 6, and 10, and the results of 6 were the best, both in terms of QPS and indexing latency.</p><p><strong><em>Read Replicas</em></strong></p><p>As I wrote above, having Read Replicas can help when trying to serve data in a high concurrency.</p><p>We decided to increase our Read Replicas to 5 per index (as I said before, there are no magic numbers, you have to test your use case and find the best number that suits it).</p><h3>Summary</h3><p>After applying the new changes, our QPS was more than <strong>700 </strong>and we were able to support <strong>3X </strong>concurrent queries to our Cluster.</p><p>If you have an Elasticsearch cluster and you want to maximize the QPS and concurrency, I strongly recommend that you run a benchmark test that includes increasing and decreasing the number of shards per index / read replicas/indexes.</p><p><em>Ron Flomin is an expert data and machine learning engineer who is a member of the data team of </em><a href="https://www.explorium.ai/"><em>Explorium</em></a><em>. Explorium offers the industry’s first automated External Data Platform for Advanced Analytics and Machine Learning. Explorium empowers data scientists and business leaders to drive decision-making by eliminating the barrier to acquiring and integrating the right external data and dramatically decreasing the time to superior predictive power. Learn more at</em><a href="http://www.explorium.ai/"><em> www.explorium.ai</em></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c32d7bb02ac2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/explorium-ai/how-to-dramatically-increase-your-elasticsearch-throughput-and-concurrency-capacity-c32d7bb02ac2">How to dramatically increase your Elasticsearch throughput and concurrency capacity</a> was originally published in <a href="https://medium.com/explorium-ai">Explorium.ai</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Debugging PySpark with PyCharm and AWS EMR]]></title>
            <link>https://medium.com/explorium-ai/debugging-pyspark-with-pycharm-and-aws-emr-d50f90077c92?source=rss----bb1f897d2958---4</link>
            <guid isPermaLink="false">https://medium.com/p/d50f90077c92</guid>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Flomin Ron]]></dc:creator>
            <pubDate>Tue, 04 May 2021 06:53:28 GMT</pubDate>
            <atom:updated>2021-05-04T07:29:01.952Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Debugging PySpark with PyCharm and AWS EMR" src="https://cdn-images-1.medium.com/max/1024/1*4BuFBCxN-ha38lSGL7vn2Q.png" /></figure><p>Have you ever found yourself developing PySpark inside EMR notebooks? Have you debugged PySpark locally, but wanted to run it over a real and big data set and couldn’t because your computer lacked resources?</p><p>On one of my projects at Explorium, I was implementing a scalable entity resolution process to automatically match and merge entities from dozens of data sources. (Stay tuned for more details in the following articles).</p><p>This process is very resource-heavy and so I decided to implement it using PySpark.</p><p>With PySpark I had the advantages of being able to run very complex processes with high computation abilities, and being able to use our entities data model which is implemented in Python.</p><p>When I started implementing the process I faced the issue of not being able to debug my process against a real data set on my local computer, so I tried to do so against AWS EMR.</p><p>I was sure I could find some solutions in a quick search, but surprisingly, I didn’t. I even opened a Stack Overflow thread regarding this most basic need: “How to debug PySpark on EMR using PyCharm”, but no one answered.</p><p>After doing some research, I would like to share my insights on how to debug PySpark with PyCharm and AWS EMR with others.</p><h3>Requirements:</h3><ul><li>PyCharm PRO (I used version 2020.3.2)</li><li>A running AWS EMR cluster with spark installed (I used version emr-6.2.0)</li></ul><p>The first step is to create an SSH Python interpreter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*DgKjEHAqvuNtvJhs" /></figure><p>Fill in the host of the AWS master public DNS (this can be found inside the EMR UI), and put “hadoop” as the username.</p><p>Afterward, use your pem / ppl file for authentication (look <a href="https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-key-pairs.html#prepare-key-pair">here</a> for more details).</p><p>Use this as the Python path: “/usr/bin/python3.7”. This is where the Python is located over the master server.</p><p>It will automatically create a temporary folder in the master server for the project’s files.</p><p>Next, let’s make sure that we have a remote deployment configuration so all of the project files will be uploaded to the master server.</p><p>In your Pycharm, go to:</p><ol><li>Tools</li><li>Deployment</li><li>Configuration</li></ol><p>Make sure that you have an existing configuration that points to the SSH configuration you have just created.</p><p>If not, create one:</p><ol><li>Click on the + symbol at the top left</li><li>Choose SFTP</li><li>Name your deployment configuration</li><li>Point your deployment configuration to your SSH configuration</li></ol><p>Eventually, your configuration should look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IHEFSW2Kh2dTdLo2" /></figure><p>Next, click on mappings and make sure that the local path points to the current project.</p><p>If the deployment path is empty, please fill it with this value: /tmp/pycharm_project_&lt;project_name&gt;</p><p>Exit the popup screen and go again to Tools &gt; Deployment and make sure that “Automatic Upload” is on. It makes sure that when we submit some changes in the files, the changed files will be uploaded automatically.</p><p>Last but not least, edit the “Run/Debug” configuration of your runnable file and add these values to the environment variables:</p><pre>YARN_CONF_DIR=/etc/hadoop/</pre><pre>SPARK_HOME=/usr/lib/spark</pre><pre>HADOOP_HOME=/usr/lib/hadoop</pre><pre>PYSPARK_PYTHON=/usr/bin/python3</pre><pre>PYTHON_PATH=/usr/bin/python3</pre><p>These variables are important so that PyCharm can point to the Spark, Hadoop and yarn files.</p><h3>Summary</h3><p>So what did we just do?</p><ol><li>We created an SSH configuration so PyCharm can know where to connect.</li><li>Then, we specified the Python path, so PyCharm can know where Python is located on the master server.</li><li>We created a remote deployment that takes care of uploading the project files into the master server.</li><li>We added important environment variables.</li></ol><p>And that’s it. You should be able to run and debug your PySpark application from your local PyCharm.</p><p><em>Ron Flomin is an expert data and machine learning engineer who is a member of the data team of </em><a href="https://www.explorium.ai/"><em>Explorium</em></a><em>. Explorium offers the industry’s first automated External Data Platform for Advanced Analytics and Machine Learning. Explorium empowers data scientists and business leaders to drive decision-making by eliminating the barrier to acquiring and integrating the right external data and dramatically decreasing the time to superior predictive power. Learn more at</em><a href="http://www.explorium.ai/"><em> www.explorium.ai</em></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d50f90077c92" width="1" height="1" alt=""><hr><p><a href="https://medium.com/explorium-ai/debugging-pyspark-with-pycharm-and-aws-emr-d50f90077c92">Debugging PySpark with PyCharm and AWS EMR</a> was originally published in <a href="https://medium.com/explorium-ai">Explorium.ai</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Benchmarking SQL engines for Data Serving: PrestoDb, Trino, and Redshift]]></title>
            <link>https://medium.com/explorium-ai/benchmarking-sql-engines-for-data-serving-prestodb-trino-and-redshift-1c5f16d6e5da?source=rss----bb1f897d2958---4</link>
            <guid isPermaLink="false">https://medium.com/p/1c5f16d6e5da</guid>
            <category><![CDATA[trinos]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[presto]]></category>
            <category><![CDATA[data-services]]></category>
            <dc:creator><![CDATA[Anton Peniaziev]]></dc:creator>
            <pubDate>Mon, 12 Apr 2021 18:19:41 GMT</pubDate>
            <atom:updated>2021-04-12T18:19:41.828Z</atom:updated>
            <content:encoded><![CDATA[<p>In the business of external data enrichment for data science, the main focus is on the ability to provide a <strong>fast and scalable way</strong> to aggregate, join and match large datasets received from data providers with the customer’s internal data. Enriched datasets derive more features, which are leveraged by customer data science teams, resulting in higher AUC and better predictive power.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*laJcO2tr8o1eSL2crWl_bg.jpeg" /></figure><p>This performance comparison has arisen from the very specific engineering problem of enrichments with<a href="https://www.investopedia.com/terms/f/foot-traffic.asp#:~:text=Foot%20traffic%20is%20a%20term,stores%2C%20such%20as%20department%20stores.&amp;text=However%2C%20foot%20traffic%20alone%20is%20not%20enough%20to%20generate%20new%20sales."> foot traffic</a> data, collected from mobile devices over a time span of half a year. However because this dataset is large in volume (4.5B records) and uniform (20–25M per day), it provides a generic real-world example for performance testing without adding extra complexity and variables like data-skew or complex relations. The data itself is the result of Spark job ETL and consists of parquet files — 1 file per partition, partitioned by day (~250MB each file), which resides on Amazon S3. A delta-lake table is metadata created in order to enable tabular access via the AWS Glue catalog.</p><p><strong>Objective</strong>: Given a user dataset with a date column in the format YYYY%MM%%dd and coordinates in the form of H3 geo-hash, enrich their data with records from the 4.5B dataset with schema (local_day, h310), where local_day is the date in the same format as the user’s data, and h310 is<a href="https://eng.uber.com/h3/"> Uber’s H3 geospatial index</a>, easily calculated from decimal coordinates.</p><p>The user’s inquiry results in a dataset received via an API call, which could range from 1 to 1M rows, and will be referred to as <strong>query_table</strong>. Our base 4.5B dataset has unique h3’s per day, and half a year’s worth of data, which will be referred to as <strong>foottraffic_daily_visitation</strong>. The following query performs the required operation and will be benchmarked:</p><pre>WITH SELECT_QUERY AS (<br>   SELECT <br>       local_day, h310, total_visitors<br>   FROM <br>       sqlperformance.foottraffic_daily_visitation <br>)<br>SELECT <br>   SELECT_QUERY.* <br>FROM <br>   SELECT_QUERY SELECT_QUERY <br>JOIN<br>   {query_table} TMP_TABLE <br>ON  <br>   SELECT_QUERY.local_day = date(TMP_TABLE.date)  AND  SELECT_QUERY.h310 = TMP_TABLE.h310 <br>WHERE  <br>   SELECT_QUERY.local_day IN ({dates_predicate})</pre><p>A note regarding the dates_predicate part — it’s a predicate pushdown, supported both in Presto and Redshift, which will significantly reduce the number of partitions scanned in the join. The user’s query is received dynamically and is not known in advance. The solution will include on the fly creation of temporary query_table, but since the content is known before the join query creation we can retrieve dates_predicate and ingest them inside.</p><h3>The Setup</h3><p><strong>PrestoDB<br></strong>We will use EMR 6.2.0 distribution which is Presto 0.238.3. Additional nice-to-have apps are Ganglia 3.7.2 for load monitoring and Hue 4.8.0 for interactive querying. The hardware is 1Xr5.4xlarge, 6Xm5.2xlarge. The JSON configuration we’ve used is:</p><pre>[<br>    {<br>        &quot;classification&quot;:&quot;presto-connector-memory&quot;,<br>        &quot;properties&quot;:{<br>            &quot;memory.max-data-per-node&quot;:&quot;256MB&quot;<br>        },<br>        &quot;configurations&quot;:[<br>            <br>        ]<br>    },<br>    {<br>        &quot;classification&quot;:&quot;presto-connector-hive&quot;,<br>        &quot;properties&quot;:{<br>            &quot;hive.parquet.use-column-names&quot;:&quot;true&quot;,<br>            &quot;hive.metastore.glue.datacatalog.enabled&quot;:&quot;true&quot;,<br>            &quot;hive.s3-file-system-type&quot;:&quot;PRESTO&quot;<br>        },<br>        &quot;configurations&quot;:[<br>            <br>        ]<br>    },<br>    {<br>        &quot;classification&quot;:&quot;presto-config&quot;,<br>        &quot;properties&quot;:{<br>            &quot;query.max-run-time&quot;:&quot;15m&quot;,<br>            &quot;task.concurrency&quot;:&quot;16&quot;,<br>            &quot;query.max-history&quot;:&quot;1000&quot;,<br>            &quot;experimental.reserved-pool-enabled&quot;:&quot;false&quot;,<br>            &quot;query.max-concurrent-queries&quot;:&quot;12&quot;<br>        },<br>        &quot;configurations&quot;:[<br>            <br>        ]<br>    }<br>]</pre><p>The most important part is hive properties that would enable us to utilize <a href="https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-presto-glue.html">the glue catalog</a>. presto-connector-memory and presto-config are presto application-specific properties. When deploying Presto yourself those are defined in memory. properties and config. properties files respectively, but for EMR deployment you need to specify them in configuration json. To better understand how to translate Presto configurations into EMR json refer to<a href="https://github.com/awsdocs/amazon-emr-release-guide/blob/master/doc_source/presto-adding-db-connectors.md"> this</a> guide. There are also a few ways to reconfigure a running Presto cluster:</p><ul><li>AWS Console — go to Clusters, choose your running cluster, then in Configurations chose any instance group in Filter. A Reconfigure button will appear, but before saving changes select <em>Apply this configuration to all active instance groups.</em></li><li>SSH to master node — create or edit required configuration files under <strong>/etc/presto/conf/catalog, </strong>save them and restart presto service using:<br><strong>sudo systemctl restart presto-server.service<br></strong>To make sure the service is back, use:<br><strong>sudo systemctl status presto-server.service</strong></li><li>Using SET SESSION queries you can alter almost any Presto configuration for a specific session. This is very convenient for benchmarking different configurations which may be a good material for another post.<br>Check out the differences between a connection and session on different clients, for example in Hue, SET SESSION wouldn’t take effect since each query is a different session. In most python clients instantiating a new cursor will induce a new session. To make sure the changes are applied use SHOW SESSION.</li></ul><p>There’s a great deal of performance tuning advice in the<a href="https://www.oreilly.com/library/view/presto-the-definitive/9781492044260/"> Presto Definitive Guide</a> book, but for this benchmark, we will stick to out-of-the-box, close to<a href="https://prestodb.io/docs/current/admin/properties.html"> default</a> values.</p><p><strong>Trino</strong></p><p>Previously known as PrestoSQL, Trino is an open-source version of PrestoDB. It has a vibrant community and a very helpful Slack workspace.</p><p>We will use the same EMR version and hardware, but check PrestoSQL 343 application instead of PrestoDB. There are also slight syntactic differences in the json configuration:</p><pre>[<br>    {<br>        &quot;classification&quot;:&quot;prestosql-connector-memory&quot;,<br>        &quot;properties&quot;:{<br>            &quot;connector.name&quot;:&quot;memory&quot;,<br>            &quot;memory.max-data-per-node&quot;:&quot;256MB&quot;<br>        },<br>        &quot;configurations&quot;:[<br>            <br>        ]<br>    },<br>    {<br>        &quot;classification&quot;:&quot;prestosql-connector-hive&quot;,<br>        &quot;properties&quot;:{<br>            &quot;hive.parquet.use-column-names&quot;:&quot;true&quot;,<br>            &quot;hive.metastore&quot;:&quot;glue&quot;,<br>            &quot;hive.s3-file-system-type&quot;:&quot;PRESTO&quot;<br>        },<br>        &quot;configurations&quot;:[<br>            <br>        ]<br>    },<br>    {<br>        &quot;classification&quot;:&quot;prestosql-config&quot;,<br>        &quot;properties&quot;:{<br>            &quot;query.max-run-time&quot;:&quot;15m&quot;,<br>            &quot;task.concurrency&quot;:&quot;16&quot;,<br>            &quot;query.max-history&quot;:&quot;1000&quot;,<br>            &quot;experimental.reserved-pool-enabled&quot;:&quot;false&quot;,<br>            &quot;http-server.process-forwarded&quot;:&quot;true&quot;,<br>            &quot;query.max-concurrent-queries&quot;:&quot;12&quot;<br>        },<br>        &quot;configurations&quot;:[<br>            <br>        ]<br>    }<br>]</pre><p>Pay attention to “connector.name”:”memory” which is necessary to enable in-memory table creation (turned on by default in PrestoDb). Also “http-server.process-forwarded”:”true” will help to forward the authorization to Trino in case you are connecting through a proxy.</p><blockquote>For both Presto and trino you can access Web-UI which contain useful information about queries, query plans, and runtimes:<br>&lt;master DNS&gt;:8889/ui</blockquote><p><strong>Redshift</strong></p><p>The hardware chosen is: 2 nodes ra3.4xlarge. In order to query data on S3 we’ll use the Spectrum feature, which will require adding the permission for S3 and AWS Glue. If you are an IAM user, create a role with the following policies:AmazonDMSRedshiftS3Role, AmazonRedshiftQueryEditor, GlueFullAccessPolicy.</p><p>Next, create a cluster with a database named dev and port 5439, then in cluster permission choose the IAM role you’ve created and add it.</p><p>In order to enable client connection you need to make sure the cluster can receive TCP requests. If you work above VPC — go to your Security Group settings, check <em>Inbound Rules </em>and add a rule: Type Redshift, Protocol TCP, Port Range 5439, source , then add a specific IP or a wildcard. Sometimes in different subregions the cluster could still be inaccessible, so to fix it go to cluster actions and enable public accessibility.</p><p>Now, in order to enable querying S3 you need to create an external schema like so:</p><pre><strong>create external schema sqlperformance<br>from data catalog<br>database ‘sqlperformance’<br>region ‘eu-west-1’<br>iam_role ‘arn:aws:iam::&lt;iam role you&#39;ve created&gt;’;</strong></pre><p><strong>Python Clients</strong></p><p>For this benchmark, we use Pyhive client for both Presto and Trino and psycopg2 for Redshift. A special note has to be made when measuring queries’ runtime with python clients — a query isn’t finished until the client returns ack. That is, be cautious when running queries that induce a lot of data to be transferred through the network. To eliminate this bottleneck we’ve surrounded all our join queries by count(*). Another valid technique is CTAS (create table as). CTAS into the blackhole connector has the advantage of no additional overhead. In spite of all the precautions, it’s always good to run queries from CLI to make sure runtimes are similar to those we receive in python.</p><h3>Benchmarking</h3><p>When comparing runtimes in different conditions it’s important not to adjust too many parameters at a time. The great Andrew NG explains the concept of orthogonalization in one of his<a href="https://www.youtube.com/watch?v=GV-DKOItQtA&amp;ab_channel=intrigano"> courses</a>.</p><p>In order to minimize the possible effects of caching mechanisms and sporadic network delays each test was run 10 times with randomized unique dates in each run, and the runtime was averaged.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/943/1*K6vET9Jd4RaCUDB5NuMHrQ.png" /></figure><p>The above experiment is a fixed amount of dates (partition) and a rising amount of rows in each query (10k, 100K, 1 million). All engines are almost indifferent to the amount of rows and Redshift is far behind. Since our use-case requires mostly computational ability out of the engine and not necessarily flexibility to connect to various data lakes (the Connectors architecture is the most valuable feature of Presto/Trino!), we can assume our ETL has transitioned from S3 to writing into Redshift disks directly. In order to simulate that let’s create a local foootraffic_daily_visitation table on the Redshift cluster:</p><pre>CREATE TABLE foootraffic_daily_visitation<br>sortkey(local_day)<br>AS<br>SELECT * FROM sqlperformance.foottraffic_daily_visitation</pre><p>sortkey is similar to partitioning in hive. This query ought to take about 12 minutes so don’t run it from an online query editor which has a limitation of 10 minutes. Then we run the same experiment, but now without Spectrum:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/934/1*ZV8YSgdS4_yWk06UEB0cCw.png" /></figure><p>Redshift performance improves significantly. In fact spectrum’s performance on the hive table on S3 is similar to performance with an on-disk local table without sortkey specified.</p><p>To strengthen the claim that tools are indifferent to the number of rows let’s run a wider range:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tofS7tPfwhfz4lxWaR-Ldg.png" /></figure><p>Of course, the number of rows means more data, and even though the join runtime doesn’t seem to be influenced, insertion of query_table and data retrieval will be much more affected. Let’s measure the whole cycle — insert+join:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-6jH7o9Dg-1ANUPvukyKTA.png" /></figure><p>It’s worth mentioning that the query_table insertion method is similar for all tools on the client side — bulk insertion of 10k rows at a time, but under the hood. Presto and Trino have memory connectors which allow us to insert a temporary table into cluster RAM, when in Redshift we write those bulks on SSD. That explains that while Redshift remained faster, its relative slowdown is ~10x whereas Presto’s is about 2x when going from 10K to 100K rows.</p><p>Now let’s fix the row number and turn another knob — dates number:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JlRKbMnXnpXtRz_IBxrwWA.png" /></figure><p>The number of partitions queried seems to be the only factor for join runtime.</p><p>In production, our platform serves dozens of users and processes. System response on concurrently running queries should provide an insight into whether initial cluster size is sufficient, and at which point scaling out is necessary. We’ll run the same query size with different random dates simultaneously:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WR16gS1YZLX7AfLetvyVtA.png" /></figure><p>The n_conn suffix stands for n concurrent running instances of a query. As one of the Presto developers states in<a href="https://www.youtube.com/watch?t=549&amp;v=Pu80FkBRP-k&amp;feature=youtu.be"> this</a> video, Presto will always try to utilize all the available machines and resources for each query. One may ask why we don’t observe more than a 2x increase in runtime when doubling the amount of concurring queries, but the answer is of course that 100% utilization is never really achieved.</p><h4>Simplifying the query</h4><p>One could argue that for smaller inquiries it’s an unnecessary complication to include join. In fact our initial motivation to query a big table by joining it with temp-table was that there’s a limitation for an sql-query size. Let’s then measure the following where query:</p><pre>SELECT<br>    local_day, h310, total_visitors,<br>FROM<br>    sqlperformance.foottraffic_daily_visitation<br>WHERE<br>    1=0 OR local_day = date &#39;2020-10-15&#39; AND h310 = &#39;8a26e0cb344ffff&#39; OR local_day = date &#39;2020-10-03&#39; AND h310 = &#39;.</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/749/1*Eov9uNzvKs4flEz-5iW3Lg.png" /></figure><p>The Redshift’s slowdown is especially concerning. If we measure Redshift separately we’ll see it definitely struggles to optimize the where clause and scan only necessary partitions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/785/1*dNFlfm2EdzerEDNUiinV8A.png" /></figure><p>In Presto the problem doesn’t seem to be that noticeable. However, each tool may have its own quirks, for example when performing initial benchmarking we’ve tested join with static query_tables that were preloaded in S3 instead of dynamically creating them with random dates. Remember {dates_predicate}? In order to get it, we’ve simply added select distinct(date) from static_query_table. We expected the same predicate pushdown to happen as we have in production, however Trino had some issues with it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/844/1*2GNdkrPdYEhtqdENBU1KnA.png" /></figure><p>As this post is being written there’s still an ongoing investigation in Trino’s Slack with experienced engineers from Starburst trying to get to the root of the problem.</p><h4>Pricing</h4><p>current monthly benchmark costs:</p><p>Presto/Trino — $2400</p><p>Redshift —$5192</p><p>Let’s try to equal costs, by choosing a smaller Redshift cluster: 3 ra3.xlplus nodes which will cost $2596 a month.</p><p>The previous dates rising test:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/957/1*shnDYb78YVdjEHyRsirDUQ.png" /></figure><p>The concurrent test:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YhpoocFa83JNoHO4f7RMAw.png" /></figure><p>As we observe, Redshift’s performance dropped, but it still outperforms Trino and Presto at roughly the same price. In fact, the price could be even less due to different Redshift cluster policies: they charge for compute and storage separately and $2596 is an estimated price when you use almost all the cluster’s 96TB. Plus, great cost reduction could be achieved by committing to use the cluster for 1 or 3 years and paying upfront:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MvdddOo1bCpRCvzw706Btg.png" /></figure><h4>Conclusion</h4><p>Data serving is a special business case, which demands real-time low-latency on small queries, the ability to scale and withstand abrupt peak loads, and high concurrency. When comparing different engines it’s important to distinguish between pure technical performance on specific queries, the tool’s ability to optimize sql queries submitted by non-technical staff, scaling, and how it all translates into business value. For growing startups a hybrid approach using different engines for different parts of the serving platform could be a viable option, allowing flexibility while building up engineering staff experience with those tools over time.</p><h4>Next Steps</h4><p>To introduce metrics that represent the potential peak load on the engine, considering the maximal time that the query can remain queued, and the minimal time needed for provisioning of additional resources.</p><p><em>Anton Peniaziev is an experienced data and machine learning engineer who is a member of the expert data team of </em><a href="https://www.explorium.ai/"><em>Explorium</em></a><em>. Explorium offers the industry’s first automated External Data Platform for Advanced Analytics and Machine Learning. Explorium empowers data scientists and business leaders to drive decision-making by eliminating the barrier to acquiring and integrating the right external data and dramatically decreasing the time to superior predictive power. Learn more at</em><a href="http://www.explorium.ai/"><em> www.explorium.ai</em></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1c5f16d6e5da" width="1" height="1" alt=""><hr><p><a href="https://medium.com/explorium-ai/benchmarking-sql-engines-for-data-serving-prestodb-trino-and-redshift-1c5f16d6e5da">Benchmarking SQL engines for Data Serving: PrestoDb, Trino, and Redshift</a> was originally published in <a href="https://medium.com/explorium-ai">Explorium.ai</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>