Free Porn
20.8 C
New York
Saturday, July 20, 2024

DuckDB Meets Apache Arrow | GoodData

That is a part of a sequence about our FlexQuery and the Longbow engine powering it.

You might have heard about DuckDB, Apache Arrow, or each. On this article, I’ll let you know about how we (GoodData) are the primary analytics (BI) platform powered by the mixture of those applied sciences. I imagine the motivation is obvious – efficiency 🏎️ and developer velocity.

Additionally, to reply the query about DuckDB I discovered on Reddit, sure, we’re utilizing it.

Reddit thread about using DuckDB in production. Source:
Reddit thread about utilizing DuckDB in manufacturing. Supply:

This entire mixture was attainable because of the FlexQuery and the Longbow engine behind it. If you want to be taught extra about them, see the opposite elements of the sequence, just like the FlexQuery introduction, or the structure of the Longbow undertaking.

A short introduction to DuckDB

DuckDB is an open-source, in-process analytical database. That has an incredible group, and a product referred to as MotherDuck is constructed on high of it. It’s quick and feature-rich. As well as, it offers superior options that assist engineers of their day-to-day routine. Let me provide you with a fast instance.

ATTACH 'sqlite:sakila.db' AS sqlite;
ATTACH 'postgres:dbname=postgresscanner' AS postgres;
ATTACH 'mysql:consumer=root database=mysqlscanner' AS mysql;


SELECT first_name, last_name
JOIN sqlite.film_actor ON (movie.film_id = film_actor.film_id)
JOIN ON (actor.actor_id = film_actor.actor_id)

One of many newest superior options is attaching a number of databases resembling Postgres or MySQL and querying on high of them. Hyperlink to the weblog put up presenting the function.

DuckDB additionally has a versatile extension mechanism that enables for dynamic loading extensions. Extensibility, basically, is a good factor, and it exhibits that the know-how is developer-friendly.

DuckDB has a huge community and is still growing.
DuckDB has an enormous group and remains to be rising.

A Temporary introduction to Apache Arrow

Apache Arrow is an open-source growth platform for in-memory analytics. It offers a standardized, language-independent columnar reminiscence format for flat and hierarchical knowledge, organized for environment friendly analytic operations on trendy {hardware}. Knowledge motion is predicated on the environment friendly FlightRPC protocol. If you would like to be taught extra about this protocol, make sure to take a look at our Arrow Flight RPC 101 article.

The success of Apache Arrow may be confirmed by its adoption or the ecosystem constructed round it. Let me provide you with a fast instance. Everybody who works with Python and knowledge is aware of the Pandas library. The primary launch of Pandas was in 2008. Since then, Pandas has come a great distance, and in 2024, Apache Arrow will develop into a required dependency of Pandas. One other instance is the Polars library, Pandas various written in Rust, which makes use of Apache Arrow because the backend from the start.

Apache Arrow is undoubtedly an superior know-how. Does it imply the whole lot is vibrant and glossy with it? Nicely, not precisely. Regardless that the know-how is great, the core of Apache Arrow and the educational curve may be steep for newcomers. This notion is echoed, for instance, on Reddit. Not way back, I used to be shopping Reddit and stumbled upon a put up about  PyArrow (Apache Arrow adoption for Python) lacking tutorials and assets, which I can affirm, as I’ve skilled this first-hand.

Example of people missing key resources for PyArrow. Source:
Instance of individuals lacking key assets for PyArrow. Supply:

How we make the most of these applied sciences

We make the most of Apache Arrow to implement an analytics lake. Initially, we began with a cache (storage) layer between the info warehouse and underlying knowledge for analytics objects (dashboards, visualizations, and many others.). Due to the modular structure of our system and Flight RPC protocol, it’s straightforward to construct and deploy knowledge companies. They are often within the type of a module or an operation inside a module. Yow will discover extra detailed data within the Longbow structure article by Lubomir (lupko) Slivka.

Essentially the most handy knowledge service you would possibly consider is executing SQL straight on caches. We discovered that DuckDB is the perfect match for this, because it has been suitable with Apache Arrow since 2021. While you learn DuckDB documentation, you’ll see that there’s a devoted Arrow extension, though it’s non-obligatory for integration with Apache Arrow. Importantly, DuckDB helps native integration with Apache Arrow.

import duckdb
import pyarrow as pa

my_arrow_table = pa.Desk.from_pydict({'i': [1, 2, 3, 4],
'j': ["one", "two", "three", "four"]})

outcomes = duckdb.sql("SELECT j FROM my_arrow_table").arrow()

With DuckDB and Apache Arrow, we are able to see important pace and reminiscence effectivity because of the zero-copy mechanism enabled by integrating these instruments.

Presently, we use a mixture of those applied sciences within the manufacturing. The entire magic is hidden in our strategy to analytics with CSV information. First, CSVs are uploaded to sturdy storage like AWS S3, the place we carry out evaluation straight on high of those information. We derive knowledge sorts, and based mostly on them, we resolve whether or not the column represents an attribute, a reality, or a date. Customers can then manually change the info sorts to their liking. After this, our platform treats CSVs as an ordinary knowledge supply and performs SQL queries utilizing DuckDB. Nevertheless, that is only the start. We plan to make the most of the combination of DuckDB and Apache Arrow much more. Keep tuned, as extra updates are on their approach.


Trying forward, there are a number of future steps that may be thought of. Nevertheless, I’d like to focus on solely two: Pre-aggregations and knowledge federation.


Think about having quite a few caches, every produced by an SQL question. Nevertheless, querying a knowledge warehouse is pricey. So, the thought behind pre-aggregations is that SQL queries are analyzed, and the output of the evaluation, within the best-case situation, is a single SQL question – minimizing direct queries to the info warehouse.

Let’s name this the “mom” question, which might be used to question a knowledge warehouse – producing the “mom” cache. We are able to derive the identical outcomes from this cache utilizing DuckDB, for instance, as we might by executing SQL queries individually. Pre-aggregations may be additional optimized by contemplating bodily and utilization stats about caches.

Fairly simple, no? Nicely, not precisely. The arduous half is to create the “mom” question. We already make the most of Apache Calcite, which helps us assemble and analyze SQL queries, and we may use it within the case of pre-aggregations as nicely. Or this might be dealt with by DuckDB as an extension. Or maybe AI might be concerned? We plan to discover all of those approaches.

Knowledge Federation

aData federation pertains to pre-aggregations. There are a number of methods to strategy knowledge federation. One among them is, for instance, utilizing the pre-aggregations talked about above. Think about that as a substitute of 1 “mom” question, you can also make a number of of them by pointing to completely different knowledge sources after which working DuckDB. The opposite approach is to make the most of DuckDB’s extensions and fasten databases.

Need to be taught extra?

As I discussed within the introduction, that is a part of a sequence of articles the place the GoodData dev group takes you on a journey of how we constructed our new analytics stack on high of Apache Arrow and what we realized about it within the course of.

Different elements of the sequence are concerning the introduction of FlexQuery, particulars concerning the versatile storage and caching, and final however not least, the Longbow Mission itself!

If you wish to see how nicely all of it works in follow, you may attempt the GoodData free trial! Or when you’d prefer to attempt our new experimental options enabled by this new strategy (AI, Machine Studying, and rather more), be happy to join our Labs Atmosphere.

In the event you’d like to debate our analytics stack (or anything), be happy to hitch our Slack group!

Related Articles


Please enter your comment!
Please enter your name here

Latest Articles