Hydra x ILLA Cloud: A Low-Code Platform Partner with Powerful Columnar Database

Welcome to this tutorial blog catered to developers seeking to master the art of utilizing ILLA Cloud Connect to the columnar analytics tech of Hydra. With ILLA Cloud's capabilities, you can effortlessly create analytical reports and customer-facing dashboards, while leveraging Hydra’s scalable analytics database.

Solving analytics with Postgres and Hydra

Most startups today begin with Postgres - it’s a full-featured, open-source, mature database that organizations can trust. The problem? Postgres is a transactional database, meaning it isn’t built for fast analytical reporting. While this distinction isn’t immediately clear for small projects, as a database’s load or storage size grows with traction, analytics and aggregates on Postgres slow down significantly. This common performance problem forces Postgres users to isolate workloads and migrate to a columnar analytics database. Adopting additional databases is expensive: rewriting application code, duplicating data, and balancing syntax and functionality differences will amplify complexity and costs.

Enter Hydra, an open-source, column-oriented RDBMS built on Postgres that brings speed and effortless analytics to the table. In an exciting partnership, ILLA Cloud has integrated Hydra into its platform, offering users unparalleled power and flexibility in managing and leveraging their data.

What is a columnar database?

A columnar database is a type of database management system (DBMS) that organizes and stores data in a column-oriented format rather than the traditional row-oriented format used by relational databases. In a columnar database, data for each column is stored together, allowing for efficient compression, query performance, and analytics.

Columnar storage for supercharged analytics

In a traditional row-oriented database, data is stored and retrieved by rows, meaning that all the values for a particular row are stored together. This layout is optimized for transactional processing where entire rows are typically accessed or updated at once. However, for analytical workloads that involve large aggregations, filtering, and querying specific columns, row-oriented databases can be less efficient.

In contrast, columnar databases store and retrieve data by columns. This means that all values for a specific column are stored together, enabling advanced compression techniques such as run-length encoding or dictionary encoding. Columnar storage allows for efficient compression because columns often contain repeating values or patterns, which can be exploited to reduce storage space. Additionally, columnar databases can execute queries faster by loading only the columns required for a particular query, leading to improved query performance.

Let’s dive a bit deeper into the key benefits of columnar storage when it comes to analytics:

Optimized for Table Scans

Columnar is optimized for table scans — in fact, it doesn’t use indexes at all. Using columnar, it’s much quicker to obtain all data for a particular column. The database doesn’t need to read data that you are not interested in at the moment. It also uses the metadata about the values in the chunks to eliminate reading data. This is a form of “auto-indexing” the data.

Data Compression

Columnar storage allows for more efficient data compression. Columns often contain repeating values or patterns, which can be compressed more effectively compared to rows. This results in reduced storage requirements, which in turn reduces disk I/O and improves query performance. Efficient compression enables columnar databases to store and analyze larger volumes of data within a limited storage footprint.

Selective Column Access

In analytics, queries often involve aggregations or operations on specific columns rather than entire rows. With columnar storage, the database engine can selectively load only the required columns for a particular query, avoiding unnecessary data retrieval. This reduces disk I/O and improves query performance by minimizing the amount of data transferred from disk to memory. It allows for faster execution of complex analytical queries involving multiple columns.

Vectorized Operations

Columnar databases can leverage vectorized operations, also known as SIMD (Single Instruction, Multiple Data) processing. SIMD allows executing operations on multiple data elements in parallel, taking advantage of modern processor architectures. Since columns store data of the same type, vectorized operations can be applied efficiently, accelerating query processing. This boosts the performance of analytical workloads that involve computations on large datasets.

Hydra: A Game-Changer in Analytics

Hydra represents a significant advancement in the world of Columnar databases. Built on the solid foundation of Postgres, it harnesses the power of column-oriented storage, which enables lightning-fast analytics and query execution. This storage approach allows for better compression, improved query performance, and efficient data aggregation. Furthermore, Hydra's open-source nature allows developers to tailor the system to their specific requirements, making it highly customizable and adaptable.

Speed without Code Changes

Hydra simplifies the process of performing fast analytics by eliminating the need for extensive code changes. Users can leverage Hydra's column-oriented storage and optimized query processing without modifying their existing codebase. This capability enables businesses to accelerate their analytics capabilities, achieve real-time insights, and make data-driven decisions without the burden of significant code refactoring or data migration.

ILLA Cloud: Empowering Businesses with Low-Code Solutions

Enter ILLA Cloud, a low-code development platform (LCDP) that empowers businesses to build powerful applications and streamline their workflows with minimal coding. With a plethora of impressive features, ILLA Cloud enables users to create visually stunning data visualizations, develop robust React-based applications, and expedite app development processes. Now, with the integration of Hydra, ILLA Cloud takes data management and integration to new heights.

Utilizing Hydra in ILLA Cloud: A Step-by-Step Guide

Create Hydra

There are two ways to create a resource in ILLA after signing into your account.

• Create in Resources

Sign into your Illa account, select **Resources** on the top of the page, and click **Create New** button.

Select Hydra from the database list.

Connect to the database with the required parameters described in Connection Settings below.

Click **Test Connection** to see if we can successfully connect to the database. If yes, click Save Resources, else, double check the hostname, port, username, and password is correct.

After creating a resource, the ready Hydra will display as shown.

• Create in Builder

Sign in to your Illa account, create a project in Illa Builder in the **App** page, and navigate to the Action List at the bottom of the page. Click **new**, then select Hydra from the database list. Then, connect to the database with required parameters described in Connection Settings below.

Click **Test Connection** to see if we can successfully connect to the database. If yes, click Save Resources, else, double check the hostname, port, username, and password is correct.

Connection Settings

Here we need to provide information for connecting to the Hydra database.

Create Actions

We have created a Hydra resource, we can add the action by selecting Hyd from the action list and choosing the Create action button.

Now we have added the Hydra server as an action to our building page.

Configure Hydra

Example usage:

• Select

SELECT * FROM users ORDER BY lastname;

• Insert

INSERT INTO users

	(firstname, lastname, email)

VALUES

	(

		{{ NewUser.data.firstNameInput }},

		{{ NewUser.data.lastNameInput }},

		{{ NewUser.data.emailInput }}

	);

• Update

UPDATE users
  SET firstname = '{{form1.updatedProfile.first}}'
  WHERE id = {{ form1.updatedProfile.uid}};

• Delete

DELETE FROM users WHERE id = {{ form1.updatedProfile.uid }};

Conclusion

The partnership between ILLA Cloud and Hydra represents a powerful combination of cutting-edge technologies that revolutionize data management and integration. Hydra's open-source, column-oriented RDBMS architecture built on Postgres, alongside ILLA Cloud's low-code development platform, creates a comprehensive solution for businesses seeking efficient data storage, retrieval, and analysis. By integrating Hydra into the ILLA Cloud ecosystem, users can effortlessly leverage the power of Hydra's analytics capabilities and benefit from low-code development features, empowering them to make informed decisions, develop powerful applications, and drive business growth. Embrace this partnership and unlock the full potential of your data with ILLA Cloud and Hydra.

Join our Discord Community: discord.com/invite/illacloud

Try ILLA Cloud for free: cloud.illacloud.com

ILLA Home Page: illacloud.com

GitHub page: github.com/illacloud/illa-builder