[et_pb_section fb_built=”1″ fullwidth=”on” _builder_version=”4.4.1″][et_pb_fullwidth_post_title meta=”off” featured_placement=”background” _builder_version=”4.7.7″ title_font=”||||||||” title_text_color=”#ff6317″ title_font_size=”3.5em” meta_font=”|300|||||||” meta_text_color=”#ffffff” meta_font_size=”1em” background_enable_color=”off” use_background_color_gradient=”on” background_color_gradient_start=”rgba(248,248,248,0.85)” background_color_gradient_end=”rgba(248,248,248,0.75)” background_color_gradient_overlays_image=”on” min_height=”20vh” height=”400px” custom_padding=”6vh||6vh||false|false” global_module=”403″ locked=”off”][\/et_pb_fullwidth_post_title][\/et_pb_section][et_pb_section fb_built=”1″ admin_label=”intro” _builder_version=”4.4.6″ custom_padding=”|||0px||” locked=”off”][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
BigData is one of the big software topics that everyone \u201cgets\u201d, but only a few can actually nail it down to bare details. Possibly because, from its first mention back in the 1990s, BigData kept evolving along three main vectors:<\/p>\n
All of these have a significant impact not only on software technologies but also on a business\u2019s bottom line (e.g. one needs to keep an eye on the cost of data storage) and top line (e.g. maximize the value of BigData).<\/p>\n
And since there are no two identical companies and request sets, the solutions are almost always custom-made. Unique assessments and decisions need to be made on a case-by-case basis:<\/p>\n
To clear things up, let\u2019s look into a specific usage case that came with not one, but two sets of very specific, and quite conflicting sets of requests involving BigData.<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
For a recent project involving large amounts of data, we have run into a couple of challenges:<\/p>\n
These are conflicting needs, and there\u2019s definitely no solution on the market, that can accommodate both. That\u2019s why we took some steps back; assessed the needs and created the theoretical frameworks; then adopted, expanded, and implemented two different solutions.<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
Since we have a strong SQL background, our first approach was to store normalized data. We considered the storage space to be the most important factor of the ecosystem and expected the DB-engine to take care of everything else.<\/p>\n
We, therefore, started to define the first, second, third normal forms for stored data. Everything worked fine until we crossed into the millions-of-rows territory \u2013 at which point, the server started to struggle.[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
Using a relational structure for such large quantities of data resulted (naturally) in slow responses for even the simplest queries. We, therefore, needed to define separate\/parallel data structures, until we got stuck. Using index files just increased the DB size without adding more speed. Moreover, maintaining the indexes added latency to the insert event.<\/p>\n
After some time, collected data becomes irrelevant\/useless, so we needed to dispose of it at regular intervals, in order to free-up the database. Unfortunately, doing that in a relational world and in all \u201cadditional\u201d tables was way too complicated to execute and maintain.[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
Therefore, we started looking into several NoSQL, BigData solutions: some expensive, others with limitations on free versions. We needed continuity, stability, and maturity (e.g. the solution should have previous versions\/iterations), ideally with a backing community.<\/p>\n
Our first trial was with Mongo (due to its large community and the MySQL migration mechanisms), then moved to Crate.io. In the end, both offered limited solutions to our needs and were quite expensive for unlimited sizes.<\/p>\n
Then the question of BigData experts came up, and we were equally stuck: there are too many of them, on too many niches, each offering a solution that would change the initial request.[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
Each solution\u2019s footprint was too big for our needs and too difficult to maintain compared to what we actually needed (e.g. rapid storage of data). Therefore, we had to look into simpler and safer solutions. Since all the data are collected by our application, we could then transform it until it fits any structure.<\/p>\n
It was at this point in time when we took the biggest step back to build the conceptual framework, before choosing a solution: What are the needs (must-haves vs. nice-to-haves)? And what can we do internally, to fit any solution we choose?[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
Very quickly, we agreed that a single DB version will not solve our application\u2019s main needs. Also, trying to use BD in an unappropriated way will reduce performance. We avoided any data coupling (between SQL and Bigdata) and tried to think Not SQL. For example, it does not matter if we store the same name a million times, it will just occupy more space but will speed up the search.<\/p>\n
A database will never work well when used in a different way than it was designed to work. One can try workarounds, only with lost time and suboptimal results. A better approach is to model your thinking and\/or business around dedicated tools, than doing workarounds.<\/p>\n
Therefore, we have split the big job into two separate main tasks (and two separate databases), each with its own solution:<\/p>\n
After a brief debating, our final choices were:<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n
ClickHouse [link<\/a>] is a Hybrid between BigData and SQL databases:<\/p>\n This means: keep adding, merging, compressing, and adding to the database \u2013 all without breaks. The only showstopper is when all hard disk locations are full, i.e. the system is blocked, and no further files can be written.<\/p>\n For this specific case, we added a mechanism to save in bursts with periodic breaks, which would allow the system to quietly merge the data. Bonus points for defining the partitions and deleting them without affecting the insert process.[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n Elasticsearch [link<\/a>] is a framework and tool suite, best known for its BigData stocking and analysis solution. Their offering also includes various tools to ingest data, predict, visualize, etc. We picked Elasticsearch because it solves our needs for quick search and storage.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n Splitting the needs into two parts was the first step, but then the implementation involved several other learning points and \u201cjumps\u201d forward.<\/p>\n Each DB is accessed through an HTTP client using proper Java connectors. We will not add to the many \u201chow-to\u2019s\u201d already available online, but provide some hints on how we advanced through the project:<\/p>\n Our applications are provided in Linux virtual machines and in the beginning, we let the update mechanism automatically download the latest version of each tool. We eventually stopped some of them in order to keep the versioning under control. This way, we avoided a lot of conflicts and incompatibilities.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n As usual, these DBs come with minimal tools \u2013 so we looked into third parties (and enjoyed it!). The main criteria were:<\/p>\n For example:<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n For our usage cases, we found Kibana and Grafana to be precisely what we needed:<\/p>\n Due to our very specific needs and the close-knit community, we eventually got to contribute to the Grafana\/Clickhouse community for two plugins (vertamedia-clickhouse-datasource and briangann-datatable-panel).<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n After going through the whole SQL-Elasticsearch-ClickHouse circle, here are some things that we would love to see happening:<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=”1″ admin_label=”intro” _builder_version=”4.4.6″ custom_padding=”|||0px||” locked=”off”][et_pb_row use_custom_gutter=”on” _builder_version=”4.4.6″ custom_margin=”||||false|false” custom_padding=”6vh||||false|false” border_color_left=”rgba(0,0,0,0)”][et_pb_column type=”4_4″ _builder_version=”4.4.1″][et_pb_text _builder_version=”4.6.6″ text_font=”|300|||||||” text_font_size=”1.1em” text_line_height=”1.6em” quote_font=”|700|||||||” quote_text_align=”left” quote_font_size=”16px” header_2_text_color=”#ff6317″ header_2_font_size=”1.5em” header_2_line_height=”0.9em” header_3_font_size=”23px” header_4_font=”||||||||” header_4_font_size=”16px” header_4_line_height=”1.5em” header_5_font_size=”14px” custom_margin=”||||false|false” custom_padding=”||||false|false” border_color_left=”#ff6317″]<\/p>\n\n
Elasticsearch<\/h2>\n
\n
Implementation<\/h2>\n
\n
Tools \/ Show me the money<\/h2>\n
\n
\n
Visualizations<\/h2>\n
\n
The future is SQL-ish<\/h2>\n
\n
\uff3f<\/h2>\n