Is there any such open source table format for row based data? I've been dabbling with implementing a database, and adopting a format would be much easier.
SQLite is something like that.
Open table formats are inevitable for analytical datasets
31–40 of 60 posts
Re: Open table formats are inevitable for analytical datasets
#32I don't see any mention of sqlite. Is a sqlite file not the same thing they're talking about here? Pretty sure it has a spec and hasn't changed formats in many years so if you wanted to read it out in something that isn't sqlite, I imagine it wouldn't be too hard.
I'm a fan of datasette.io, which is built around sqlite. Essentially it's a set of tools that you point at a sqlite db to explore datasettes.
Re: Open table formats are inevitable for analytical datasets
#33I don't see any mention of sqlite. Is a sqlite file not the same thing they're talking about here? Pretty sure it has a spec and hasn't changed formats in many years so if you wanted to read it out in something that isn't sqlite, I imagine it wouldn't be too hard.
Column orientation is extremely important for query + storage efficiency.
Furthermore, you need a distributed query engine (Athena, Bigquery etc.) and they all support parquet.
Re: Open table formats are inevitable for analytical datasets
#34I don't see any mention of sqlite. Is a sqlite file not the same thing they're talking about here? Pretty sure it has a spec and hasn't changed formats in many years so if you wanted to read it out in something that isn't sqlite, I imagine it wouldn't be too hard.
This is more about arranging files in cloud storage than about the engine used to query them.
You could store your data in a SQLite database, but that's not really interoperable in the way a bunch of Parquet files are. Source: tried it.
Re: Open table formats are inevitable for analytical datasets
#35I don't see any mention of sqlite. Is a sqlite file not the same thing they're talking about here? Pretty sure it has a spec and hasn't changed formats in many years so if you wanted to read it out in something that isn't sqlite, I imagine it wouldn't be too hard.
Here's one more problem not mentioned by others: SQLite has some functions that it declares but doesn't define. For example, regexp(). So, if your table is defined as having a constraint with regexp()... well, sucks to be you: it might depend on what regexp() implementation you load whether that constraint can be applied or not.
I know this because I needed this functionality in Ada code, which links statically with SQLite, and I didn't want to also link with PCRE library just to get regexp(), especially since GNATCOLL already, sort of has regexp... except it doesn't have the "fancy" features, s.a. lookaheads / lookbehinds / Unicode support etc.
So, a table that uses regexp() in its constraint definition isn't portable. But, if you only use the table data without the schema you lose a lot of valuable information...
----
Also, come to think about it: unlike server-client databases (eg. MySQL, PostgreSQL etc) SQLite doesn't have a wire-transfer format. Its interface returns values in the way C language understands them. The on-disk binary format isn't at all designed for transfer because it's optimized for access efficiency. This, beside other things, results in SQLite database file typically having tons of empty space, it doesn't use efficient (compressed) value representation etc.
So, trying to use SQLite format for transferring data isn't going to be a good arrangement. It's going to be wasteful and slow.
Re: Open table formats are inevitable for analytical datasets
#36Earlier quoted context omitted.
DuckDB is about querying existing files or am I missing something?
The documentation puts a lot of emphasis on loading/querying existing files, as that's the first thing you will want to do for a OLAP use-case, but in general it's the same as sqlite where you maintain a database file.
Thanks. That's good to know. The file format is not highlighted in the documentation but I found some information about it: https://duckdb.org/internals/storage.html
It seems to be a bit early to rely on it to store data in an object store, but I will do some tests to compare with SQLite:
> The DuckDB internal storage format is currently in flux, and is expected to change with each release until we reach v1.0.0.
Re: Open table formats are inevitable for analytical datasets
#37Earlier quoted context omitted.
I agree that sqlite has a number of similar benefits - openness, table abstractions and concurrent transactions. It’s also a library so close to how delta, iceberg and hudi are implemented. I’m glad it’s had an uptick in interest recently but I haven’t yet seen it mentioned for analytics yet. I assume it’s row rather than column oriented?
Yes, SQLite is row oriented. It's not a very space efficient format because it also doesn't support compression or compact representations of numbers in binary. But it doesn't rely on the JVM and a typical JVM ecosystem. It is a big benefit for some use-cases, like dealing with numerical data on the edge.
OLAP or column oriented SQLite is DuckDB.
Re: Open table formats are inevitable for analytical datasets
#38Is this the new SQL "killer" ?
Re: Open table formats are inevitable for analytical datasets
#39Is this the new SQL "killer" ?
No! Haven't you heard? The new SQL "killer" is simply tying LLMs into your data and giving it commands in the common business parlance of your choice! kinda. sorta.
Re: Open table formats are inevitable for analytical datasets
#40I don't see any mention of sqlite. Is a sqlite file not the same thing they're talking about here? Pretty sure it has a spec and hasn't changed formats in many years so if you wanted to read it out in something that isn't sqlite, I imagine it wouldn't be too hard.
Column orientation is extremely important for query + storage efficiency. Furthermore, you need a distributed query engine (Athena, Bigquery etc.) and they all support parquet.
DuckDB is SQLite but column oriented!