In a sector that is experiencing massive digital disruption, we allow educational publishers \u0026 content creators unlock the digital potential of their existing content \u0026 open up new revenue streams that were previous inaccessible to them

Druid Learning

Unlike normal reporting, collects the data from a combination of dimensions every time. It is also a complicated kind of analysis of immense data. In our case, we have a real-time data volume of millions of OPS (Operations Per Second), with each operation involving around 20 data tags and over a dozen dimensions. A/B testing For effective A/B testing, as data engineers, we must ensure quick computation as well as high data integrity (which means no duplication and no data loss). I'm sure I'm not the only one to say this: it is hard! Let me show you our long-term struggle with our previous Druid-based data platform. Platform Architecture 1.0 : Apache Storm + Apache Druid + MySQL Components This was our real-time data warehouse, where Apache Storm was the real-time data processing engine and Apache Druid pre-aggregated the data. However, Druid did not support certain paging and join queries, so we wrote data from Druid to MySQL regularly, making MySQL the "materialized view" of Druid. But that was only a duct tape solution as it couldn't support our ever-enlarging real-time data size. So data timeliness was unattainable. Platform Architecture 2.0 : Apache Flink + Apache Druid + TiDB Components This time, we replaced Storm with Flink and MySQL with TiDB. Flink was more powerful in terms of semantics and features, while TiDB, with its distributed capability, was more maintainable than MySQL. But architecture 2.0 was nowhere near our goal of end-to-end data consistency, either, because when processing huge data, enabling TiDB transactions largely slowed down data writing. Plus, Druid itself did not support standard SQL, so there were some learning costs and friction in usage. Platform Architecture 3.0 : Apache Flink + Components Apache Doris We replaced Apache Druid with Apache Doris as the OLAP engine, which could also serve as a unified data-serving gateway. So in Architecture 3.0, we only need to maintain one set of query logic. And we layered our real-time data warehouse to increase the reusability of real-time data. Turns out the combination of Flink and Doris was the answer. We can exploit their features to realize quick computation and data consistency. Keep reading and see how we make it happen. Quick Computation As one piece of operation data can be attached to 20 tags, in A/B testing, we compare two groups of data centering only one tag each time. At first, we thought about splitting one piece of operation data (with 20 tags) into 20 pieces of data of only one tag upon data ingestion and then importing them into Doris for analysis, but that could cause a data explosion and thus huge pressure on our clusters. Then we tried moving part of the workload to the computation engine. So we tried and "exploded" the data in Flink, but soon regretted it because when we aggregated the data using the global hash windows in Flink jobs, the network and CPU usage also "exploded". Our third shot was to aggregate data locally in Flink right after we split it. As is shown below, we create a window in the memory of one operator for local aggregation; then we further aggregate it using the global hash windows. Since two operators chained together are in one thread, transferring data between operators consumes much fewer network resources. The two-step aggregation method, combined with the Aggregate model of Apache Doris, can keep data explosion in a manageable range. For convenience in A/B testing, we make the test tag ID the first sorted field in Apache Doris, so we can quickly locate the target data using sorted indexes. To further minimize data processing in queries, we create materialized views with the frequently used dimensions. With constant modification and updates, the materialized views are applicable in 80% of our queries. To sum up, with the application of sorted index and materialized views, we reduce our query response time to merely seconds in A/B testing. Data Integrity Guarantee Imagine that your algorithm designers worked sweat and tears trying to improve the business, only to find their solution unable to be validated by A/B testing due to data loss. This is an unbearable situation, and we make every effort to avoid it. Develop a Sink-to-Doris Component To ensure end-to-end data integrity, we developed a Sink-to-Doris component. It is built on our own Flink Stream API scaffolding and realized by the idempotent writing of Apache Doris and the two-stage commit mechanism of Apache Flink. On top of it, we have a data protection mechanism against anomalies. It is the result of our long-term evolution. We used to ensure data consistency by implementing "one writing for one tag ID". Then we realized we could make good use of the transactions in Apache Doris and the two-stage commit of Apache Flink. As is shown above, this is how a two-stage commit works to guarantee data consistency: Write data into local files; Stage One: pre-commit data to Apache Doris. Save the Doris transaction ID into status; If the checkpoint fails, manually abandon the transaction; if the checkpoint succeeds, commit the transaction in Stage Two; If the commit fails after multiple retries, the transaction ID and the relevant data will be saved in HDFS, and we can restore the data via Broker Load. We make it possible to split a single checkpoint into multiple transactions so that we can prevent one Stream Load from taking more time than a Flink checkpoint in the event of large data volumes. Application Display This is how we implement Sink-to-Doris. The component has blocked API calls and topology assembly. With simple configuration, we can write data into Apache Doris via Stream Load. Cluster Monitoring For cluster and host monitoring, we adopted the metrics templates provided by the Apache Doris community. For data monitoring, in addition to the template metrics, we added Stream Load request numbers and loading rates. Other metrics of our concerns include data writing speed and task processing time. In the case of anomalies, we will receive notifications in the form of phone calls, messages, and emails. Key Takeaways The recipe for successful A/B testing is quick computation and high data integrity. For this purpose, we implement a two-step aggregation method in Apache Flink, utilizing the Aggregate model, materialized view, and short indexes of Apache Doris. Then we develop a Sink-to-Doris component, which is realized by the idempotent writing of Apache Doris and the two-stage commit mechanism of Apache Flink.

A/B Testing was a Jerk, Until we Found the Replacement for Druid

361 Stories To Learn About Big Data

213 Stories To Learn About Sql

369 Stories To Learn About Database

My first experience with crypto wasn’t under very good circumstances: a friend who takes care of several servers at his job was infected with ransomware – and this malware was demanding he pay the ransom amount in a cryptocurrency called Monero (XMR). After this not-so-friendly introduction, I started to study how the technology behind cryptocurrencies works, and I fell in love with it. I was already interested in the stock market, so I joined the familiar (stock market) with the novel (crypto). To test the knowledge I’d learned from my stock market books, I started creating a simple crossover bot. Moving Average Convergence Divergence (MACD) This worked for a while, but I quickly realized that I should - and could - make the bot a lot better. Now, the project that I started as a hobby has a capital management system, a combination of technical indicators, and sentiment analysis powered by machine learning. Between 10 March 2020 and 10 August 2020, my bot resulted in a of 63.1%, of 1.74, and cumulative gross result of approximately 588% (you can see a copy of all of my trades during this period in ). success rate profit factor this Google Sheet report About my project I needed a bot that gave me a high-performance, scalable way to calculate technical indicators and process sentiment data in real-time. To do everything I need in terms of my technical indicators calculation, I collect data and market depth via an always-up websocket connection that tracks every Bitcoin market on the (~215 in total, 182 being tradeable, at this moment). candlestick chart Binance exchange The machine learning sentiment analysis started as a simple experiment to see if external news affected the market. For example: if a famous person in the crypto ecosystem tweeted that a big exchange was hacked, the price will probably fall and affect the whole market. Likewise, very good news should impact the price in a positive way. I calculated sentiment analysis scores in real-time, as soon as new data was ingested from sources like Twitter, Reddit, RSS feeds, and etc. Then, using these scores, I could determine market conditions at the moment. Now, I combine these two components with a weighted average, 60% technical indicators and 40% sentiment analysis. Current trading bot dashboard where I track all my ongoing trades and results; In this particular case, filtered for the period of 10-March-2020 to 10-August-2020. Quick breakdown of my results and success rates week-over-week; In this particular case, filtered for the period of 10-March-2020 to 10-August-2020. Using TimescaleDB At the beginning, I tried to save the collected data in simple files, but quickly realized that wasn’t a good way to store and process this data. I started looking for an alternative: a performant database. I went through several databases, and all of them always lacked something I wound up needing to continue my project. I tried MongoDB, InfluxDB, and Druid, but none of them 100% met my needs. Of the databases I tried, InfluxDB was a good option; however, every query that I tried to run was painful, due to their own query language (InfluxQL). As soon as my series started to grow exponentially to higher levels, the server didn't have enough memory to handle them all in real-time. This is because the current storage engine requires more and more allocated memory for each series. I have a large number of unique metrics, so the process ran out of available memory quickly, InfluxDB TSM I handle somewhat large amounts of data every day, especially on days with many market movements. On average, I’m ingesting around 20k records/market, or 3.6 million total records, per day (20k*182 markets) and even with this huge amount of data, my query response time is in the milliseconds. require This is where TimescaleDB started to shine for me. It gave me fast real-time aggregations, built-in time-series functions, high ingestion rates – and it didn’t elevated memory usage to do all of this. In addition to this raw market data, a common use case for me is to analyze the data in different time frames (e.g., 1min, 5min, 1hr, etc.) I maintain these records in a pre-computed aggregate to increase my query performance and allow me to make faster decisions about whether or not to enter a position. For example, here’s a simple query that I use a lot to follow the performance of my trades on a daily or weekly basis (daily in this case): time_group, total_trades, positive_trades, 
	negative_trades, ( * (positive_trades / total_trades), ) success_rate, profit gross_profit, ((profit - (total_trades * )), ) net_profit ( time_bucket( , buy_at:: ):: time_group, (*) total_trades, ( profit > ):: positive_trades, ( profit <= ):: negative_trades, ( (profit), ) profit trade time_group time_group 
) T time_group SELECT ROUND 100 2 AS as ROUND 0.15 2 AS FROM SELECT '1 day' TIMESTAMP DATE AS COUNT AS SUM CASE WHEN 0 THEN 1 ELSE 0 END NUMERIC AS SUM CASE WHEN 0 THEN 1 ELSE 0 END NUMERIC AS ROUND SUM 2 AS FROM GROUP BY ORDER BY ORDER BY And, I often use this function to : measure market volatility, decomposing the range of a market pair in a period tr(_symbol , _till ) ( ZONE, ( , ), ( , )) plpgsql $$ candlestick ( * candlestick c c.symbol = _symbol c.time > () - _till ) d.time, ( (a, b, c)) :: ( , ) , ( (a, b, c) / d.close) :: ( , ) ( today.time, today.close, today.high - today.low a, ( (today.high - yesterday.close), ) b, ( (today.low - yesterday.close), ) c candlestick today LATERAL ( yesterday.close candlestick yesterday yesterday.time < today.time yesterday.time ) yesterday today.time > () - _till) d; ; $$; atr(_interval , _symbol , _till ) ( ZONE, ( , ), ( , )) plpgsql $$ true_range ( * tr(_symbol, _till) ) tr.date, avg.sma , avg.sma_percent true_range tr LATERAL ( (lat.result) sma, (lat.percent) sma_percent ( * true_range inr inr.date <= tr.date inr.date _interval
			 ) lat
		) tr.date > () - _till tr.date; ; $$; * atr( , , ) CREATE OR REPLACE FUNCTION TEXT INTERVAL RETURNS TABLE date TIMESTAMP WITHOUT TIME result NUMERIC 9 8 percent NUMERIC 9 8 LANGUAGE AS DECLARE BEGIN RETURN QUERY WITH AS SELECT FROM WHERE AND NOW SELECT GREATEST NUMERIC 9 8 as result GREATEST NUMERIC 9 8 as percent FROM SELECT as COALESCE ABS 0 COALESCE ABS 0 FROM LEFT JOIN SELECT FROM WHERE ORDER BY DESC LIMIT 1 ON TRUE WHERE NOW END CREATE OR REPLACE FUNCTION INT TEXT INTERVAL RETURNS TABLE date TIMESTAMP WITHOUT TIME result NUMERIC 9 8 percent NUMERIC 9 8 LANGUAGE AS DECLARE BEGIN RETURN QUERY WITH AS SELECT FROM SELECT result percent FROM INNER JOIN SELECT avg avg FROM SELECT FROM WHERE ORDER BY DESC LIMIT avg ON TRUE WHERE NOW ORDER BY END SELECT FROM 14 'BNBBTC' '4 HOURS' ORDER BY date My current deployment & future plans My current architecture. To develop my bot and all its capabilities, I used Node.js as my main programming language and various libraries: to communicate between all my modules without overengineering, to train and deploy all my machine learning models, and for technical indicator calculation, as well as various others. cote tensorFlow tulind I modified some to meet my needs and created some from scratch, including a , a level calculator for , and . candlestick recognition pattern support/resistance Fibonacci retracement Today, I have a total of 55 markets (which are re-evaluated every month, based on trade simulation performance) that trade simultaneously 24/7; when all my strategy conditions are met, a trade is automatically opened. The bot respects my capital management system, which is basically to limit myself to 10 opened positions and only using 10% of the available capital at a given time. To keep track of the results of an open trade, I use dynamic and . Trailing Stop Loss Trailing Take Profit The process of re-evaluating a market requires a second instance of my bot that runs in the background and uses my main strategy to simulate trades in all Bitcoin markets. When it detects that a market is doing well, based on the metrics I track, that market enters the main bot instance and starts live trading. The same applies to those that are performing poorly; as soon as the main instance of my bot detects things are going badly, the market is removed from the main instance and the second instance begins tracking it. If it improves, it's added back in. As every developer likely knows all too well, the process of building a software is to always improve it. Right now, I’m trying to improve my capital management system using . Kelly Criterion Why ? TimescaleDB For my use case, I’ve found TimescaleDB is a powerful and solid choice: it’s fast with reliable ingest rates, efficiently stores and compresses a huge dataset in a way that’s manageable and cost-effective, and gives me real-time aggregation functionality. The , , and this about managing and processing huge time-series datasets is all pretty easy to understand and follow – and the TimescaleDB team is responsive and helpful (and they always show up in community discussions, like ). TimescaleDB website core documentation blog post my Reddit AMA It’s been easy and straightforward to scale, without adding any new technologies to the stack. And, as an SQL user, TimescaleDB adds very little maintenance overhead, especially compared to learning or maintaining a new database or language. This post is slightly modified from original post on the TimescaleDB blog (read it here ).

How I Power My Trading Bot with TimescaleDB

Do you know why microservice design is so popular within the development of BI tools? The answer is clear: it helps to develop scalable and flexible solutions. But microservice architecture has a great drawback. Its performance usually requires great improvements. The FreshCode team also faced the problem and I’ve decided to show how we coped with it. The article is written together with FreshCode CTO and based on our recent case of development reporting microservice. You will find here its tech scheme, estimates, as well as a list of tools for on-premise and SaaS products. MEET MICROSERVICE DESIGN. WHAT SHOULD YOU CARE ABOUT? If you wonder why is microservice style so popular, you should think about the recent IT trends. The demand for Agile and DevOps practices led to microservice popularity. Today such great players as Uber, Airbnb, Netflix use microservices to solve their business problems. The best way to explain what does microservice design mean is to compare it with a common monolith app. The monolithic system uses one processor for all the logic. Meanwhile, microservice includes a few separate processors. They usually are: UI database server Any change in the system leads to the deployment of a new version of the server part of the system. Let’s consider the concept in detail. MICROSERVICE DESIGN IN DETAIL Microservice design means a set of services, but the definition is vague. I can single out 4 features that a microserver usually has: the decentralized control of languages and data responsibility for a specific business need automatic deployment endpoints On the picture below you can see microservice design compared to a monolith app. Scalability in microservices One of the main benefits of the microservice design is its scalability. You can scale several services without changing the whole system. So, you save resources and keep the app less complex. One of the most famous cases that prove this fact is Netflix user base. The company had to cope with the growing subscribers’ database. The microservice design was a great solution for scaling it. Each microservice needs its own database. Otherwise, you can’t use all the benefits of the modularization pattern. But the variety of databases leads to challenges in the reporting process. We will discuss the problem later. Microservice design speeds up app development and allows to launch the product earlier. Each part can be rolled out separately. So, the deployment of microservices is quicker and easier. Pros of microservices 1. The possibility of convenient horizontal system scaling 2. Increased development team members productivity 3. Simplification of the debugging and maintenance processes 4. The ability to work in smaller teams and use an Agile approach 5. Flexibility in continuous integration and deployment Cons of microservices Despite all these benefits, microservice architecture has its own drawbacks. I mean the necessity of operating many systems and completing various tasks in the distributed environment. So, the main microservice pitfalls are: 1. The complexity of microservice design makes developer plan and act more carefully. 2. The external API communication in microservice architecture leads to more significant risks of attacks. 3. Sometimes it’s difficult to switch between them in the development and deployment processes. REPORTING IN MICROSERVICE SYSTEM We worked on a legacy EdTech project. The system was very complex and included many microservices. Its main parts were: sophisticated financial and billing system multi-organisation structure for large group entities workflow management tool for business processes integrated bulk email, SMS and live chat online system for surveys, quizzes, examination flexible assessment and learning management system FreshCode worked on the project on the stage of migrating to a new interface. The product was preparing for the global launch. The microservice system was supposed to process great amounts of data. As for the app target audience, it was developed for that manage 100s of campuses large education networks that have up to 200k schools, colleges and universities governments Meanwhile, the EdTech app design was convenient both for great education networks and a of about 100 students. small school So, FreshCode development team faced the problem of managing and improving the performance of the complex microservice architecture. It should be mentioned that the client wanted to build both SaaS and self-hosted systems. So, we have chosen the technical solutions keeping this fact in mind. IMPROVING PERFORMANCE IN MICROSERVICES The process of generating reports required engagement with different services. Thus, it caused performance issues. That’s why Freshcode team decided to optimize the app architecture by creating a separate reporting microservice. It received data from all the databases. Then, it saved them and transformed into custom reports. On the picture below you can see the scheme of reporting microservices system and technologies for its implementation. Yellow color marks all microservices in the system. Each of them has its own database. The reporting module tracks all changes in them with the help of a messaging system. Then, it stores the new data in its own report database. 6 STEPS OF THE MICROSERVICE IMPLEMENTATION Let’s look at the 6 main part of the reporting system, technologies that can be used and the best solutions. Change Data Capturing (CDC) CDC tracks every single change (insert, update, delete) and performs some logic on it. There were 3 possible tools for the first step of implementing the microservice reporting system. 1. Apache NiFi It allows creating simple CDC without coding at all. Apache NiFi has a lot of built-in processors and supports data routing, transformation and system mediation logic. Pros: Support of cluster mode and easy scaling Built-in PutToKafka and PutToKinesis activities Implementation of custom activities on any JVM language User-friendly UI Cons: No predefined data format for messaging between activities Supports only JVM languages The quality of default activities isn’t perfect No Oracle CDC activity 2. StreamSets Data Collector Popular open source solution for continuous big data ingestion in a microservice reporting system. Its main advantages are simple creation of data pipelines and support of many widespread technologies. Pros: Built-in AWS S3, Kinesis, Kafka, Oracle, Postgres processors Open source software can be adjusted for your needs Simple and convenient UI Support of most of the popular tools Cons: It’s a new solution that is still actively developing It’s a little bit difficult to start working with StreamSets Data Collector 3. Matillion The innovative ELT architecture has an easy-to-use interface. It is built specifically for Amazon Redshift, Google BigQuery and Snowflake. Pros: A proprietary tool Support of the development team Well-tested solution Cons: Only several databases can be used with this tool ELT architecture doesn’t match to all projects Oracle was the main database of our microservice reporting system. So, we choose StreamSets Data Collector, because of Oracle CDC support out of the box. Messaging System It allows sending messages between computer systems, as well as setting publishing standards for them. 1. Apache Kafka One of the most famous tools for real-time analytics. Apache Kafka has high throughput and reliability characteristics. Pros: High throughput, fault tolerance, durable Great scalability, high concurrency Batch mode, native computation over stream A great choice for on-premise microservice reporting system Cons: Requires DevOps knowledge for correct setup No built-in monitoring tool 2. AWS Kinesis It simplifies collecting, processing, analyzing streaming data. Amazon Kinesis offers key capabilities for the cost-effective process at any scale. Pros: Easy to manage and scale Great integration with other AWS services Almost no DevOps effort Built-in monitoring and alert system Cons: Needs some cost optimizations No way to use for on-premise software Although Apache Kafka required a bit more effort to deploy and setup, we used it as a cost-efficient on-premise solution. Streaming Computation Systems The high-performance computer system analyzes multiple data streams from many sources. It helps to prepare data before ingestion. So, it’s possible to denormalize/join them and add any info if needed. 1. Spark Streaming Brings Apache Spark’s language-integrated API for stream processing. So, it allows writing streaming jobs the same way we write batch jobs. Pros: Stateful exactly-once semantics out of the box Fault-tolerance, scalability In-memory computation Cons: Pretty expensive to use Manual optimization No built-in state management 2. Apache Flink It is useful for stateful computations over unbounded and bounded data streams. Apache Flink suits for all common cluster environments and performs computations at in-memory speed. Pros: Exactly once state consistency SQL on Stream & Batch Data Low latency, scalability, fault-tolerance Support of very large state Cons: Requires high programming skills Complicated architecture Flink community is less than Spark but growing 3. Apache Samza The scalable data processing engine for real-time analytics that can be used in a microservice reporting system. Pros: Can maintain a large state Low latency, high throughput, mature and tested at scale Fault-tolerant and high performance Cons: At-least-once processing guarantee Lack of advanced streaming features (watermarks, sessions, triggers) 4. AWS Kinesis Services The set of tools includes Data Firehose, Data Analytics, and Data Streams. As a result, it helps to build powerful stream processing without implementing any custom code. Pros: Pay only for what you use The easiest way to process data streams in real time with SQL Handle any amount of streaming data Cons: No way to use on-premise Complicated to customize AWS provides a great set of tools for ETL and data procession. It’s a good start point. But there is no way to deploy it on custom servers. That’s why it doesn’t fit for on-premise solutions. Apache Flink is the most feature reach and performant solution. It allows storing large application state (multi-terabyte). But it requires more developers to be involved and should be deployed by yourself. Data Lake The central repository of integrated data from one or more disparate sources. It stores current and historical data in one single place. So, we can use them for creating analytical reports, machine learning, etc. 1. AWS S3 The object storage service offers industry-leading scalability, data availability, security and performance. Pros: Easy to integrate with other AWS services Designed for 99.999999999% (11 9’s) of data durability Cost-effective for rarely accessed data Has an open source implementation with full API support Cons: High network pricing Previously S3 met availability issues, but it’s not a problem for a Data Lake 2. Apache Hadoop The primary data storage system used by Hadoop applications. It allows storing and processing large amounts of data. Pros: Efficiently works with huge amounts of data Integration with many analytical and operational tools (Impala, Hive, HBase, etc) Cons: Complicated to deploy and manage Needs to set up monitoring and high availability We decided to start with AWS S3. It has an open source implementation. That’s why we could integrate it to the on-premise microservice reporting system. Report Databases 1. AWS Aurora It is up to 5 times faster than standard MySQL databases and 3 times faster than PostgreSQL databases. Pros: Pretty fast SQL database High Availability and Durability Fully Managed Easy to scale Cons: Bad performance for analytical reports in case of big data projectsThe minimally available instance is too big, but we can easily replace it by plain PostgreSQL 2. AWS Redshift Redshift delivers 10 times faster performance than other data warehouses. It is using machine learning, massively parallel query execution and columnar storage on high-performance disk. Pros: May run queries on external S3 files Easy to set up, use and manage Columnar storage Cons: Doesn’t enforce uniqueness Can’t be used as a live app database It’s mostly useful for run aggregation on a large amount of data 3. Kinetica The vectorized, columnar, memory-first database designed for analytical (OLAP) workloads. Kinetica automatically distributes any workload across CPUs and GPUs for optimal results. Pros: Pretty fast aggregation performance, run on GPU and CPU Supports materialized join views, and can update them incrementally Cons: GPU instances still cost a lot No way to join data between different partitions 4. Apache Druid It generally works well with any event-oriented, clickstream, time series, or telemetry data, especially streaming datasets from Apache Kafka. Druid provides exactly once consumption semantics from Apache Kafka and is commonly used as a sink for event-oriented Kafka topics. Pros: Druid can be deployed in any *NIX environment on commodity hardware Best for interactive dashboards with full drill-down capabilities Stores only pre-aggregated data Cons: Isn’t perfect for custom reports that may be built by users Works only on time series data No full join support All of these databases are amazing. But our client’s goal was to create reports based on all data from all microservices. So, the development team considered AWS Aurora as the best choice for this task. It simplified the workflow a lot. Report Microservice The report microservice was responsible for storing information about data objects and relations between them. It also stood for managing security and generating reports itself. Since these reports were based on the chosen data objects. TECH SOLUTIONS We prepared 2 variants of the technological stack for the microservice reporting system. As for the SaaS product on AWS, we used: StreamSets for CDC Apache Kafka as a messaging system AWS S3 Data Lake AWS Aurora as a reporting database AWS ElasticCache as an in-memory data store The reporting microservice was written in . You can see rough estimates for SaaS solution on the table below. NodeJS Note: These are calculations for production deployment. The development process required much smaller infrastructure. Such infrastructure was the most appropriate for the client’s requirements. Its main advantage was the easy way to replace AWS services with self-hosted solutions. It allowed us to avoid code/logic duplication for different deployment schemas. For on-premise one we used Minio, PostgreSQL, Redis accordingly. Their APIs were fully compatible. So, we didn’t have any significant problems in the microservice reporting system at all. RESULTS Our team solved the clients’ technical challenges. The reporting microservice module was effective and convenient. It was capable of: Generating clear and convenient reports Providing many standard reporting templates Adding a large number of filters Customizing report interface FreshCode client improved the microservice reporting system and achieved these goals: the app’s architecture and design to update the product by adding new features to improve performance, increase flexibility and scalability to optimize If you are interested in solving the same problem or have any other technical challenges, contact our . We provide free expert advice for startups, small business and enterprises. Check FreshCode portfolio to find out other interesting . team projects Would you like to read more case-based articles? Let me know in the comments below and stay in touch! *** The original article was published on FreshCode blog

Business Intelligence in microservices: improving performance

You can read this article on my own blog, if you prefer that to medium. is a fairly new column store database. It’s developed by the guys over at Yandex (the Google of Russia), made to scale horizontally reasonably well and run high speed aggregate queries on hundreds of billions of rows of data. Clickhouse It uses its own SQL dialect and it matches pl/pgSQL in terms of expressivity and simplicity. It even includes higher order functions for working with nested object within rows, such as and . arrayMap arrayFilter What is Clickhouse used for ? In short, Clickhouse is used to run fast analytics on very large amount of data. It’s rather bad as a transactional database, but it can run aggregate queries on billions of rows in sub second times. It fills the ever increasing niche that technologies like Hadoop, Spark, Druid, Big Query, Redshift, Athena and MonetDb aim for. It allows a team to easily store and analyze large sets of data with high dimensionality. More importantly, it blows the aforementioned solutions out of the water in terms of speed and query complexity. Clickhouse is meant solely for analytic workloads, so as long as you have a lot of and aggregated functions such as , and it will be blazing fast, even if your number of rows is in the 11 to 12 digits area. But if you want to do a lot of large scale joins and point queries, a typical RDBMS is still going to be the better choice. GROUP BY COUNT SUM DISTINCT It should be noted here, Clickhouse does not yet support queries like and in its stable syntax. Even when it will, they will be slow due to its compression mechanism. Again, this is not a database meant for transactional workloads. DELETE UPDATE It will also compress your data, the space this saves is based on the column granularity and on how much query speed you are willing to sacrifice. The way Clickhouse compresses and partitions data is a key to its quick queries. If you want to read more about how column stores work, I would suggest you start with the , since most of the information there is quite easy to understand and accurate. Wikipedia article What exactly makes Clickhouse amazing ? 1It’s extremely easy to setup and use,even if you need data replication and data distribution on a few dozen machines. Deploying a Clickhouse cluster on 3 machines is almost instant if you are familiar with what you are doing and could take you a few hours to do properly if you are learning everything from scratch. No need for obscure libraries, no endless configuration files, user permission issues and arcane problems with data distribution and replication. 2 It’s not fragile. One horrible thing I’ve noticed in my rendezvous with various “big data” technologies (spark is a chief offender here) is that they tend to often break with no warning and for no apparent reason. Even worse, they give little information as to why the problem happened within the humongous logs they generate. Clickhouse is designed to recover from failure and it produces quite a reasonably sized system log with clear explanations of the problems it encountered. 3 It doesn’t reinvent the wheel, it adds a steam engine to the carriage. Perhaps the most amazing thing about Clickhouse is that it doesn’t try to push a lot of limitations on you nor does it force you to completely re-think the way you store and query your data. Its data types cover everything from nested structure to matrices, it has a gigantic library of functions and one of the most versatile query language that I’ve ever seen. Unlike Map-reduce or other column store solutions, that will force you to rethink your entire schema, you can pretty much duplicate a transactional database schema with Clickhouse and it will do just fine. 4It’s actually got quite an amazing documentation and a great community. Perhaps the nastiest thing with “big data” technologies nowadays is that most of them are designed in order to sell support. A prime example of this would be Druid, open source and free in theory, but an unbreakable bastion of weird complexity coupled with a complete lack of documentation and community involvement. is relatively easy to search through and it’s being groomed, expanded and improved constantly. Clickhouse’s documentation The community is nothing short of amazing, it’s quite tiny, consisting of the developers and users from Yandex plus a few other early adopters, but that hasn’t stopped every question and issue I raised on Github and StackOverflow from being answered within hours. The developer also seem to very receptive to contributors external to Yandex. Enough talk, let’s install the thing The first step is to install Clickhouse, for the purpose of this tutorial, it’s best to have a machine running Debian or Ubuntu, since that’s the official OS Yandex supports. It’s also quite easy to install it on , I’ve personally installed it with success from the on Arch, it exists as a . If all else fails, you can use the or . Fedora, CentOS, Suse and RHEL AUR repository package for Gentoo docker image build it from source To install it on Ubuntu, the execute the following: sudo apt-add-repository 'deb http://repo.yandex.ru/clickhouse/deb/stable/ main/' sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4 sudo apt-get update sudo apt-get install clickhouse-client clickhouse-server Now you can start the server as a daemon by running and connect to it via the client by running . sudo systemctl start clickhouse-server clickhouse-client If you want to play around with the configurations, you can find them inside and respectively. /etc/clickhouse-server /etc/clickhouse-client Playing with some data For this introduction, I chose to use the , it’s a rather small dataset of ~1 million rows, but it means downloading and insertion will be very quick. The query speed will scale linearly with larger datasets as long as they fit on a local RAID and won’t suffer to much if data is distributed. NYC property taxes dataset In case you’ve installed an older version you may want to run: SET experimental_allow_extended_storage_definition_syntax = 1; This will allow you to use the syntax used throughout this tutorial. If your version is new enough, trying to run this will just throw an exception, no need to worry, you are all set. Let’s create our database and table: CREATE DATABASE test_database; USE test_database; CREATE TABLE tax_bills_nyc (bbl Int64,owner_name String,address String,tax_class String,tax_rate String,emv Float64,tbea Float64,bav Float64,tba String,property_tax String,condonumber String,condo String,insertion_date DateTime MATERIALIZED now()) Before we insert that data, let’s look at the things which might be a bit unclear here. is a way to create to column that is automatically set whenever a row is inserted based on the function after the keyword. In this case, the function I chose to use is , in order to get the insertion timestamp for each row. MATERIALIZED now() sets the storage engine for the table. There are many types of that clickhouse supports. Including your bog standard engine, engines that are meant to buffer rows when a very high write rate is needed, engines that are meant to speed up joins and many others. MergeTrees are the best table engines for most analytic queries, and the engine is the most simple of those, and usually the best choice. ENGINE = MergeTree table engines MEMORY BUFFER JOIN MergeTree Whilst I the specific of the engines would take too long to explain here. If you are familiar with , , , or … then thing of MergeTrees as being something conceptually close to that. MergeTree long-structured merge-trees Radix trees immutable “Vectors” Merkel trees Patricia trees If you aren’t, I’d like to invite you to the when your local church of Functional programming next meets. data structures sermon will partition the table using the column. Partitions are the largest storage unit under tables in clickhouse. You can delete partitions, attach partitions and temporarily drop partitions. Efficient clickhouse replication and distribution also depends on good partitioning. PARTITION BY tax_class tax_class From the end user perspective, what you should care about most is that partitions are very fast to operate on using , and . > < == For the column you partition by, you should chose one with somewhat low granularity, as a general rule of thumb. A good rule of thumb is to have less than 1000 distinct values per billion of rows. When your data associated with a point in time, a good partition key is the date associated with every row. is similar to a btree index in an RDBMS, from a user perspective. It speeds up queries using comparison operators. Unlike , you can use high granularity data here without losing performance. Again, in the case of a table where you often query based on a time associate with each data point, a good candidate for this value is the timestamp of each row. You can order your table by a combination of columns if you so desire. ORDER BY PARTITION Now you should be familiar with most of the new concepts you need to get along with Clickhouse, the rest should feel quite natural to you. Let’s insert our data: curl -X GET 'http://taxbills.nyc/tax_bills_june15_bbls.csv'  | /usr/bin/clickhouse-client --input_format_allow_errors_num=10 --query="INSERT INTO test_database.tax_bills_nyc FORMAT CSV" That should take a few seconds or minutes based on your internet speed, once it’s done, we can start testing some queries. If you want to work with a bigger dataset but don’t have time to define your own table, just run the above command a few more times :) Let’s take it for a ride Ok, lets run our first query, don’t blink: . That query should have been relatively instant, probably done in sub ms time if you are using server grade hardware. SELECT COUNT(*)/pow(10,3) FROM tax_bills_nyc For the purpose of this tutorial I’m using an E3–1270v6 with 32GB of EEC DDR4 at 2.133 GHz, but really, any old server or laptop will do. Lets try something a bit harder: SELECT SUM(emv) as total_emv, SUM(tbea) as total_tbea, SUM(bav) as total_bav, tax_class,tax_rateFROM tax_bills_nycGROUP BY tax_class, tax_rate That should have taken somewhere between 50 to 10ms depending on your machine, quite the feat for doing a GROUP BY 2 dimensions and summing up 4 different columns. Ok, what if we add a high dimensionality column in the , let’s say the owner’s name, since there are about 885,000 distinct names in the dataset: GROUP BY SELECT SUM(emv) as total_emv, SUM(tbea) as total_tbea, SUM(bav) as total_bav, tax_class,tax_rateFROM tax_bills_nycGROUP BY tax_class, tax_rate, owner_name That was probably slightly slower, but it still probably took under half a second, despite the high granularity of our new grouping criteria. But, if we built our schema correctly, we should be able to filter most queries using our order or partition column, let’s add a where clause based on the column: tax_class SELECT SUM(emv) as total_emv, SUM(tbea) as total_tbea, SUM(bav) as total_bav, tax_class,tax_rateFROM tax_bills_nycWHERE tax_class='1c - condo in 1-3 unit building'GROUP BY tax_class, tax_rate, owner_name Suddenly, our query time drops in the sub 10ms range again. Clickhouse not only offers speed, but it has various type and value manipulation functions that can allow us to easily write a query which output something that’s easily human readable: SELECT concat(toString(uniq(owner_name) / pow(10, 3)), ' thousand distinct names among the owners of ', toString(uniq(address) / pow(10, 3)), ' thousand properties') AS result FROM tax_bills_nyc This should give you a result similar to: 85.058 thousand distinct names among the owners of 989.466 thousand properties Ok, let’s try and see what happens if we try grouping by every single column in this table. To save you some typing, we’ll use the function, which will essentially do just that. DISTINCT(*) SELECT COUNT(DISTINCT(*)) FROM tax_bills_nyc Surprisingly enough, this once again takes under half a second. It might actually be quicker then some of the previous GROUP BYs since we didn’t have to transform any of the string typed columns into a printable version. In my books, that was quite impressive when I first saw it. Have I peeked() your interest ? Hopefully you can see why I’m very excited about this database due to the expressive syntax and amazing query speed alone. This was mostly a toy dataset, meant to make this tutorial easy to follow. So I encourage you to try and go up to a few billion rows and see for yourself how unbelievable this database is. As for the dataset you want to do that with, I leave that up to you. Maybe try one of your large internal tables on which you often have trouble running analytical queries. Try formatting some of the data in your “data lake”, which you’d usually use a tool like Spark or Athena to query, and insert it into Clickhouse. If enough people are interested, I might produce a followup article or series with some more tips, tricks and real world usecases for Clickhouse. If you enjoyed this article you may also like: Imaginary Problems Are the Root of Bad Software If I were to select the worst Linux syscall

Clickhouse, an analytics database for the 21st century

is an API that enables developers to build news feeds and activity streams ( ). We are used by over 500 companies and power the feeds of more than 300 million end users. Companies such as Product Hunt, , Powerschool, , , Compass and Fabric (Google) rely on Stream to power their news feeds. In addition to the API, the founders of Stream also wrote the most widely used . Stream try the API Under Armour Bandsintown Dubsmash open source solution for building scalable feeds Here’s what Stream looks like today: Number of servers: 180 Feed updates per month: 34 billion Average API response time: 12ms Average real-time response time: 2ms Regions: 4 (US-East, EU-West, Tokyo and Singapore) 1B API requests per month (~20k/minute) Given that most of our customers are engineers, we often talk about our stack. Here’s a high level overview: is our primary programming language. Go We use a custom solution built on top of + for our primary database (we started out on but wanted more control over performance). stores configs, API keys, etc. RocksDB Raft Cassandra PostgreSQL and handle tracing, and provide detailed monitoring and we use the stack for centralized logging. OpenTracing Jaeger StatsD Grafana ELK is our language of choice for machine learning, devops and our website ( , & ). Python https://getstream.io Django DRF React Stream uses a combination of and . This results in fast read performance when a users open their feed, as well as fast propagation times when a famous user posts an update. fanout-on-write fanout-on-read and I ( ) are the developers who started Stream nearly 3 years ago. We founded the company in Amsterdam, participated in Techstars NYC 2015 and opened up our Boulder, Colorado office in 2016. It’s been quite a crazy ride in a fairly short amount of time! With over 15 developers and a host of supporting roles, including sales and marketing, the team feels enormous compared to the early days. Tommaso Barguli @tschellenbach The Challenge: News Feeds & Activity Streams The nature of follow relationships makes it hard to scale feeds. Most of you will remember Facebook’s long load times, Twitter’s fail whale or Tumblr’s year of technical debt. Feeds are hard to scale since there is no clear way to shard the data. Follow relationships connect everyone to everyone else. This makes it difficult to split data across multiple machines. If you want to learn more about this problem, check out these papers: Twitter’s tech back in the days with 150 million active users LinkedIn’s ranked feeds Yahoo/Princeton research paper At a very high level there are 3 different ways to scale your feeds: Fanout-on-write: basically precompute everything. It’s expensive, but its easy to shard the data. Fanout-on-read: hard to scale, but more affordable and new activities show up faster. Combination of the above two approaches: better performance and reduced latency, but increased code complexity. Stream uses a combination of fanout-on-write and fanout-on-read. This allows us to effectively support both customers with highly connected graphs, as well as customers with a more sparse dataset. This is important since the ways in which our customers use Stream are very different. Have a look at these screenshots from , Unsplash, and : Bandsintown Product Hunt Switching from to Python Go After years of optimizing our existing feed technology we decided to make a larger leap with 2.0 of Stream. While the first iteration of Stream was powered by Python and Cassandra, for Stream 2.0 of our infrastructure we . The main reason why we switched from Python to Go is performance. Certain features of Stream such as aggregation, ranking and serialization were very difficult to speed up using Python. switched to Go We’ve been using Go since March 2017 and it’s been a great experience so far. Go has greatly increased the productivity of our development team. Not only has it improved the speed at which we develop, it’s also 30x faster for many components of Stream. The performance of Go greatly influenced our architecture in a positive way. With Python we often found ourselves delegating logic to the database layer purely for performance reasons. The high performance of Go gave us more flexibility in terms of architecture. This led to a huge simplification of our infrastructure and a dramatic improvement of latency. For instance, we saw a 10 to 1 reduction in web-server count thanks to the lower memory and CPU usage for the same number of requests. Initially we struggled a bit with package management for Go. However, using together with the contributed to creating a great workflow. Dep VG package If you’ve never tried Go, you’ll want to try this online tour: https://tour.golang.org/welcome/1 Go as a language is heavily focused on performance. The built-in tool is amazing for finding performance issues. Uber’s library is great for visualizing data from PPROF and will be bundled in PPROF in Go 1.10. PPROF Go-Torch Switching from Cassandra to & Raft RocksDB 1.0 of Stream leveraged for storing the feed. Cassandra is a common choice for building feeds. Instagram, for instance started, out with but eventually to handle their rapid usage growth. Cassandra can handle write heavy workloads very efficiently. Cassandra Redis switched to Cassandra Cassandra is a great tool that allows you to scale write capacity simply by adding more nodes, though it is also very complex. This complexity made it hard to diagnose performance fluctuations. Even though we had years of experience with running Cassandra, it still felt like a bit of a black box. When building Stream 2.0 we decided to go for a different approach and build . Keevo is our in-house key-value store built upon RocksDB, gRPC and Raft. Keevo is a highly performant embeddable database library developed and maintained by Facebook’s data engineering team. RocksDB started as a fork of Google’s LevelDB that introduced several performance improvements for SSD. Nowadays RocksDB is a project on its own and is under active development. It is written in C++ and it’s fast. Have a look at how this . In terms of technology it’s much more simple than Cassandra. This translates into reduced maintenance overhead, improved performance and, most importantly, more consistent performance. It’s interesting to note that also uses RocksDB for . RocksDB benchmark handles 7 million QPS LinkedIn their feed Our infrastructure is hosted on and is designed to survive entire availability zone outages. Unlike Cassandra, Keevo clusters organizes nodes into leaders and followers . When a leader (master) node becomes unavailable the other nodes in the same deployment will start an election and pick a new leader. Electing a new leader is a fast operation and barely impacts live traffic. AWS To do this, Keevo implements the Raft consensus algorithm using . This ensures that every bit stored in Keevo is stored on 3 different servers and operations are always consistent. This site does a great job of visualizing how Raft works: Hashicorp’s Go implementation https://raft.github.io/ Not Quite Microservices By leveraging Go and RocksDB we’re able to achieve great feed performance. The average response time is around 12ms. The architecture lies somewhere between a monolith and a microservice. Stream runs on the following 7 services: Stream API Keevo Real time & Firehose Analytics Personalization & Machine learning Site & Dashboard Async Workers To see all our services divided into stacks, head over . here Personalization & Machine Learning Almost all large apps with feeds use machine learning and personalization. For instance, LinkedIn prioritizes the items in your feed. Instagram’s explore feed displays pictures outside of the people you follow that you might be interested in. Etsy uses a similar approach to optimize ecommerce conversion. Stream supports the following 5 use cases for personalization: Documentation for building personalized feeds. All of these personalization use cases rely on combining feeds with analytics and machine learning. For the machine learning side we generate the models using . The models are different for each of our enterprise customers. Typically we’ll use one of these amazing libraries: Python — LightFM How to implement recommender systems XGBoost Scikit learn , and Dask Numpy Pandas (For development) Jupyter Notebook Mesa Framework Analytics Analytics data is collected using a tiny Go-based server. In the background, it will spawn go-routines to rollup the data as needed. The resulting metrics are stored in . In the past, we looked at , which seems like a solid project. For now, we could get away with a simpler solution though. Elastic Druid Dashboard & Site The dashboard is powered by and . We also use React and Redux for all of our example applications: React Redux Cabin: A tutorial series on building a social network with React & Redux (Winds 2.0 is coming out very soon) Winds: Open source RSS & Podcast app The site, as well as the API for the site, is powered by , and . Stream is sponsoring Django Rest Framework since it’s a pretty great open source project. If you need to build an API quickly there is no better tool than DRF and Python. Python Django Django Rest Framework We use to resize the images on our site. For us, Imgix is cost efficient, fast and overall a great service. is a good open source alternative. Imgix Thumbor Real time Our real time infrastructure is based on Go, Redis and the excellent library. It implements the Bayeux protocol. In terms of architecture it’s very similar to the node based library. gorilla websocket Faye It was interesting to read the “ ” post on Hacker News. The author moves from Go to Node to improve performance. We actually did the exact opposite and moved from Node to Go for our real time system. The new Go-based infrastructure handles 8x the traffic per node. Ditching Go for Node.js Devops, Testing & Multiple Regions In terms of devops the provisioning and configuration of instances is fully automated using a combination of: CloudFormation Cloud-Init Puppet Boto & Fabric Because our infrastructure is defined in code it has become trivial to launch new regions. We heavily use . Every single piece of our stack is defined in a CloudFormation template. If needed we are able to spawn a new dedicated shard in a few minutes. In addition, AWS Parameter Store is used to hold application settings. Our largest deployment is in US-East, but we also have regions in Tokyo, Singapore and Dublin. CloudFormation A combination of Puppet and Cloud-init is used to configure our instances. We run our self-contained Go’s binaries directly on the EC2 instance without any additional containerization layer. Releasing new versions of our services is done by . Travis first runs our test suite. Once it passes, it publishes a new release binary to . Common tasks such as installing dependencies for the Go project, or building a binary are automated using plain old Makefiles. (We know, crazy old school, right?) Our binaries are compressed using UPX. Travis GitHub Tool highlight: Travis has come a long way over the past years. I used to prefer in some cases since it was easier to debug broken builds. With the addition of the aptly named “debug build” button, Travis is now the clear winner. It’s easy to use and free for open source, with no need to maintain anything. Travis Jenkins Next we use to do a rolling deploy to our AWS instances. If anything goes wrong during the deploy it will halt the deploy. We take stability very seriously: Fabric A high level of test coverage is required. Releases are created by Travis (making it hard to deploy without running tests). Code is reviewed by at least 2 team members. Our extensive QA integration test suite evaluates if all 7 components still work. We’ve written about our experience with . When things do break we do our best to be transparent about the ongoing issue: testing our Go codebase StatusPage for 24/7 phone support Twilio for notifying our team VictorOps #firefighting channel on Slack for customer support Intercom VictorOps is a recent addition to our support stack. It’s made it very easy to collaborate on ongoing issues. Tool Highlight: VictorOps The best part about is how they use a timeline to collaborate amongst team members. VictorOps is an elegant way to keep our team in the loop about outages. It also integrates well with . This setup enables us to quickly react to any problems that make it into production, work together and resolve them faster. VictorOps Slack The vast majority of our infrastructure runs on AWS: for load balancing ELB for reliable Postgres hosting RDS for Redis hosting ElastiCache for our DNS Route53 for our CDN Cloudfront for ELK and tracing with AWS ElasticSearch Jaeger The devops responsibilities are shared across our team. While we do have one dedicated devops engineer, all our developers have to understand and own the entire workflow. Monitoring Stream uses for tracing and for beautiful dashboards. The tracking for Grafana is done using . The end result is this beauty: OpenTracing Grafana StatsD We track our errors in and use the ELK stack to centralize our logs. Sentry Tool Highlight: OpenTracing + Jaegar One new addition to the stack is . In the past we used , which works like a charm for Python, but isn’t able to automatically measure tracing information for Go. OpenTracing with is a great solution that works very well for Stream. It also has, perhaps, the best logo for a tracing solution: OpenTracing New Relic Jaeger Closing Thoughts is an absolutely amazing language and has been a major win in terms of performance and developer productivity. For tracing we use OpenTracing and Jaeger. Our monitoring is running on StatsD and Graphite. Centralized logging is handled by the ELK stack. Go Stream’s main database is a custom solution built on top of and Raft. In the past we used Cassandra, which we found hard to maintain and which didn’t give us enough control over performance when compared to RocksDB. RocksDB We leverage external tools and solutions for everything that’s not a core competence. hosting is handled by ElastiCache, Postgres by RDS, email by , test builds by Travis and error reporting by Sentry. Redis Mailgun Thank you for reading about our stack! If you’re a user of Stream, please be sure to add to your stack on StackShare. If you’re a talented individual, ! And finally, if you haven’t tried out Stream yet, take a look at this quick . Stream come work with us tutorial for the API This post was originally written by Thierry Schellenbach, CEO at GetStream.io . The original post can be found at https://stackshare.io/stream/stream-and-go-news-feeds-for-over-300-million-end-users .

DRUID LEARNING

druidlearning.com

-- employees

Since n.d.

COMPANY RANKING

DRUID LEARNING

EVERGREEN INDEX #4214

Representing this company? Click here to claim and customize this page!

Druid Learning WIKI

Apply to get your Tech Company News Page live on HackerNoon.com today!

DRUID LEARNING

druidlearning.com

-- employees

Since n.d.

COMPANY RANKING

DRUID LEARNING

EVERGREEN INDEX #4214

RELATED COMPANIESRANK

STORIES ABOUT DRUID LEARNING

Representing this company? Click here to claim and customize this page!

Druid Learning WIKI

Apply to get your Tech Company News Page live on HackerNoon.com today!