OpenRefine vs Presto

To provide employees with the critical need of interactive querying, we’ve worked with Presto, an open-source distributed SQL query engine, over the years. Operating Presto at Pinterest’s scale has involved resolving quite a few challenges like, supporting deeply nested and huge thrift schemas, slow/ bad worker detection and remediation, auto-scaling cluster, graceful cluster shutdown and impersonation support for ldap authenticator.

Our infrastructure is built on top of Amazon EC2 and we leverage Amazon S3 for storing our data. This separates compute and storage layers, and allows multiple compute clusters to share the S3 data.

We have hundreds of petabytes of data and tens of thousands of Apache Hive tables. Our Presto clusters are comprised of a fleet of 450 r4.8xl EC2 instances. Presto clusters together have over 100 TBs of memory and 14K vcpu cores. Within Pinterest, we have close to more than 1,000 monthly active users (out of total 1,600+ Pinterest employees) using Presto, who run about 400K queries on these clusters per month.

Each query submitted to Presto cluster is logged to a Kafka topic via Singer. Singer is a logging agent built at Pinterest and we talked about it in a previous post. Each query is logged when it is submitted and when it finishes. When a Presto cluster crashes, we will have query submitted events without corresponding query finished events. These events enable us to capture the effect of cluster crashes over time.

Each Presto cluster at Pinterest has workers on a mix of dedicated AWS EC2 instances and Kubernetes pods. Kubernetes platform provides us with the capability to add and remove workers from a Presto cluster very quickly. The best-case latency on bringing up a new worker on Kubernetes is less than a minute. However, when the Kubernetes cluster itself is out of resources and needs to scale up, it can take up to ten minutes. Some other advantages of deploying on Kubernetes platform is that our Presto deployment becomes agnostic of cloud vendor, instance types, OS, etc.

#BigData #AWS #DataScience #DataEngineering

I'm looking for an open-source/free/cheap tool to clean messy data coming from various travel APIs. We use many different APIs and save the info in our DB. However, many duplicates cannot be easily recognized as such.

We would either write an algorithm or use smart technology/tools with ML to help with product management.

While there are many things to be considered, this is one feature that it should have:

"To avoid confusion, we need to merge the suppliers & products accordingly. Products and suppliers must be able to be merged and assigned separately.

Reason: It may happen that one supplier offers different products. E.g., 1 tour operator offers 3 products via 1 API, but only 1 product with 3 (or a different amount of) variations via a different API. Also, the commission may differ for products, which we need to consider. Very often, products that are live (are bookable in real-time) on via 1 API, but are not live on the other. E.g., Supplier product 1 & 2 of API1 are live, product 3 not. For the same supplier, API2 provides live availability for products 1, 2, and 3.

Summing up, when merging the suppliers (tour operators) we need to consider:

• Are the products the same for all APIs?
• Which booking system API gives a better commission? Note: Some APIs charge us 1-5% depending on the monthly sale, which needs to be considered
• Which booking system provides live availability
• Is it the same supplier, or is the name only similar?

Most of the time, the supplier names differ even if they are the same (e.g., API1 often names them XX Pty Ltd, while API2 leaves "Pty Ltd" out). Additionally, the product title, description, etc. differ.

We need to write logic and create an algorithm to find the duplicates & to merge, assign, or (de)activate the respective supplier or product. My previous developer started a module to merge the suppliers, which does not seem to work correctly. Also, it is way too time taking considering the high amount of products that we have.

I would recommend merging, assigning etc. products and suppliers only if our algorithm says it's 90- 100% the matching supplier/product. Otherwise, admins need to be able to check & modify this. E.g. everything with a lower possibility of matching will be matched automatically, but can be undone or modified.

The next time the cron job runs, this needs to be considered to avoid recreating duplicates & creating a mess."

I am not sure in what way OpenRefine can help to achieve this and what ML tool can be connected to learn from the decisions the product management team makes. Maybe you have an idea of how other travel portals deal with messy data, duplicates, etc.?

I'm looking for the cheapest solution for a start-up, but it should do the work properly.

The platform deals with time series data from sensors aggregated against things( event data that originates at periodic intervals). We use Cassandra as our distributed database to store time series data. Aggregated data insights from Cassandra is delivered as web API for consumption from other applications. Presto as a distributed sql querying engine, can provide a faster execution time provided the queries are tuned for proper distribution across the cluster. Another objective that we had was to combine Cassandra table data with other business data from RDBMS or other big data systems where presto through its connector architecture would have opened up a whole lot of options for us.