A Journey to the World of Data Warehouse ~ Part-II

credits: Adobe FireFly

Introduction

Testing and deploying a new system from scratch is a long marathon. Feeling excitement when you deploy a new feature and disappointment when things start to go wrong or don’t work are all part of the process, like a sinus wave. This article discusses some of the parts of this journey. It would be wrong to directly compare the systems discussed in this article, as they have different architectures and solutions. In general, it is intended to provide a starting point for those who will work with these systems.

Content Insight

1. EXASOL
2. SingleStore
3. CitusData
4. Sharding
5. Tableau
6. Data Warehouse
7. Business Intelligence

Chapter-III Overview of Tested Data Warehouse Systems

When utilizing Tableau as our Business Intelligence (BI) tool, it consistently delivers exceptional performance whether we’re accessing data in real-time or working with intricate SQL queries. Tableau extraction feature proves invaluable, especially when dealing with extensive datasets, as it ensures that our system remains efficient and reports are generated in mere seconds.

The Tableau Extract feature reads tables or custom SQL queries through its scheduling module and writes this data to the Tableau Server. Tableau reports leverage this data to enhance performance and efficiency. The usage of PostgreSQL materialized views parallels that of Tableau Extract, enabling quicker data access. Such a materialized view can be particularly beneficial for presenting graphs on the dashboard designed for salespeople. To maintain the statistics up to date, a scheduled job can be created, running the SQL statement “refresh materialized view summary” each night.

Tableau’s in-memory data engine directly queries the extract. However, since an extract is essentially a static snapshot of the data, it must be periodically refreshed to incorporate updates from the original data source, be it a local file or an on-premises database. In contrast, live connections provide the advantage of real-time updates, ensuring that any modifications in the data source are instantly reflected in Tableau. We have created over 200+ extract and 100 live data source connections.

However, as our expectations continue to evolve, we’re actively seeking ways to enhance our system further. We encounter some limitations, particularly with tables containing over 100 million rows and more than 20 columns. Additionally, there are complex queries with numerous JOIN operations that pose a significant challenge. Our goal is to seamlessly analyze this data on demand while utilizing Tableau’s filtering capabilities.

To enhance our capabilities, we have undertaken the evaluation of various Data Warehouse (DWH) solutions, including EXASOL, SingleStore, and, most recently, CitusData. In general, I have found no significant issues while working with these DWH solutions. However, it’s important to acknowledge that there are trade-offs involved. To better navigate these considerations, I’ve compiled a table detailing the pros and cons of my experience with each.

Overview and Experience Table

This table does not provide information about the migration phase, which was also quite time-consuming. The migration process involves a series of ETL (Extract, Transform, Load) tasks, encompassing adjustments to the database connections, resolving SQL compatibility issues, altering data types, table creation, data transfer, SQL code modifications, and replacement of data sources and other Tableau variables.

It’s important to emphasize that Disaster Recovery (DR) is essential to showcase the duration of the recovery process and the methods employed, with particular attention to insert times as a critical aspect in assessing DR effectiveness. This criterion is designed to provide an overview of how recovery procedures are executed. In the following section, you’ll discover a detailed table outlining insert tests.

Insert Times of each records with 1K batch

Unlike batch inserts, SingleStore and Citus achieve similar bulk insertion times. Both platforms inserted a file containing 24 million rows into the database in approximately 27 minutes.

In terms of performance, EXASOL demonstrated exceptional speed; according to tpc-h benchmark test EXASOL is the world’s fastest analytics database (The Transaction Processing Performance Council (TPC) is a non-profit that creates unbiased tests to determine how the same database runs on different hardware), while SingleStore also delivered strong performance. No significant changes were needed on the Tableau Server side. However, one drawback with SingleStore was the time-consuming nature of inserting operations. We conducted our Create, Read, Update, and Delete (CRUD) tests using the KNIME Analytics tool.

It’s worth noting that both EXASOL and SingleStore exhibit limitations when it comes to leveraging the full power of SQL for data analysis. In contrast, CitusData stands out by supporting subqueries, JSON functions, lateral joins, pivoting, filtering, and much more. We appreciate CitusData for allowing us to seamlessly execute our complex queries without requiring modifications. It’s important to be cautious when analyzing large datasets with in-memory databases, as they may encounter “out of memory” exceptions.

Chapter IV — Migration: Tableau & CitusData

Overall, the migration process was relatively smoother with CitusData, primarily owing to its PostgreSQL engine. In contrast, with SingleStore and Exasol, configuring these In-Memory Databases was less time-intensive, and we didn’t require much assistance from a Database Administrator (DBA). Most of our efforts were directed toward adapting SQL code and aligning table structures.

DWH System with CitusData and Tableau

Sharding

Sharding is a technique used in database systems and distributed computing to horizontally partition data across multiple servers or nodes. It involves breaking up a large database or dataset into smaller, more manageable parts called Shards. Each shard contains a subset of the data, and together, they form the complete dataset. It’s wise to use a unique attribute which is mostly used in JOIN criteria and “colocate_with” feature can be used in conjuction with other distributed tables.

Here some tips: You may consider to distribute your table before loading the data. You have to choose right distribution column. Otherwise performance can suffer. After building your dataset, best practice is running VACUUM ANALYZE on the table. And the last one is finding a magical number for shard_count which needs to be divisible by a lot of numbers.

Co-location is the practice of dividing data tactically, keeping related information on the same machines to enable efficient relational operations, while taking advantage of the horizontal scalability for the whole dataset.

credits: freepick

shard_count: finding the best number that is divisible by a lot of numbers

In EXASOL and SingleStore, we shard large tables based on the attribute with the highest uniqueness, which often aligns with the most frequently used JOIN condition attribute. Aside from this sharding strategy, we have not implemented any additional performance enhancements.

Cold cache means, database queries or operations often experience slower performance because they must fetch data directly from the primary storage, which is typically slower in terms of access time. A warm cache means database queries can be satisfied from the cache, which is faster than retrieving data from the primary storage.

credits: Adobe FireFly

Notably, SingleStore exhibits exceptional speed even with a cold cache compared to CitusData. However, when working with a warm cache, we did not observe any significant variations in data retrieval speed. The tests are conducted using the Tableau platform, where we’ve developed worksheets that include extensive tables and intricate SQL queries, often involving complex table JOIN operations.

On CitusData, we initiated our migration tests with a table containing 25 columns and has a 1.7 billion rows of data. During this process, we encountered a challenge related to sharding, wherein CitusData generated an ID that wasn’t present in the reference table. To circumvent this issue, we adopted a strategy of transferring tables intended for sharding through the Debezium connector.

Notably, Tableau encountered difficulties when trying to retrieve data from CitusData due to its use of the fetch-cursor. To address this, we leveraged a user-defined properties file, commonly known as a TDC (Tableau Data Connector) file. This enabled us to disable the cursor feature while establishing connections to PostgreSQL databases. Furthermore, Tableau offers an “initial SQL” feature, allowing us to include statements like “set max_parallel_workers_per_gather to 16.” Executing the “Explain” command prior to running SQL queries provided insight into the planned number of workers (with the default being 2 if not explicitly disabled)

Reference Table

A reference table is a specific type of distributed table in which the complete dataset is consolidated into a single shard, which is then replicated on every worker in the system. On CitusData we’ve implemented the reference feature for over 50 small tables in our database.

In both EXASOL and SingleStore, our reference tables are truncated and transferred daily using KNIME at scheduled times. However, in the case of CitusData, we also need to add primary keys to specific tables that require replication.

Epilogue — Conclusion and Ideas

EXASOL and SingleStore are both exceptionally reliable and robust as In-Memory Database systems. As a team, we wholeheartedly recommend using these platforms, as our experience with them has been consistently positive. CitusData, on the other hand, excels in advanced SQL capabilities (JSON functions, lateral, subquery, pivot, filter, stored procedure), making it an ideal choice when complex data analysis within SQL is required.

Our KAFKA system has significantly improved its stability since upgrading from PostgreSQL 11.x to PostgreSQL 15.x. However, managing disaster recovery and addressing logical replication failures remains a challenging aspect of our setup. The use of the Debezium connector has proven to be a valuable tool in managing partitioned tables within a single topic, simplifying the sharding process in our In-Memory database system.

For Change Data Capture (CDC) and the transfer of reference tables, we seamlessly integrate EXASOL and SingleStore with the KNIME Analytics platform. KNIME offers a user-friendly interface for transferring reference tables and conducting insertion tests, with the added benefit of supporting multi-threaded operations.

Our integration of CitusData with Tableau has been highly effective, particularly as we migrated 50 reports, including significant Big Data projects. However, when working with Tableau, we’ve encountered two primary challenges: SQL conversion and data source replacement.

While Tableau may not automatically convert variables if they’ve been renamed, it offers a useful reference history feature to streamline the renaming process. Additionally, the use of a TDC (Tableau Data Connector) file becomes necessary to address cursor-fetch issues and maintain smooth functionality.