You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@iceberg.apache.org by Ajantha Bhat <aj...@gmail.com> on 2024/10/17 12:17:57 UTC
[Discuss] Iceberg View Interoperability
Hi everyone,
It’s been over six months since Iceberg views were introduced (in version
1.5.0), and while we’ve seen some benefits—such as versioning and
cross-engine recognition—there’s still a critical gap in terms of true
interoperability. Although views created by one engine are recognized by
another, they currently cannot be queried across engines as one engine
cannot understand the SQL dialect of another engine.
To address this, I propose a two-step approach:
*Step 1: A temporary solution to manually add SQL dialects.*It is already
possible to do this using some java code but most of the view users
interact via SQL, there’s currently no SQL-native method to manually add
dialects from other engines into Iceberg views. I suggest adding a call
procedure to enable this. I've recently worked on this and have submitted
the following PRs for review:
- Spark-3.5: Refactor BaseProcedure to support views [#11326
<https://github.com/apache/iceberg/pull/11326>]
- Spark-3.5: Procedure to add view dialect [#11327
<https://github.com/apache/iceberg/pull/11327>]
With these changes, Iceberg views could be queried across different engines
using their respective dialects.
Please review and provide feedback.
*Step 2: A long-term solution for full view interoperability.*For a
sustainable solution, we need to consider both open-source and commercial
engines (e.g., Snowflake, Dremio, BigQuery). A framework that can
automatically translate SQL dialects from one engine to another seems ideal.
I’ve explored Coral*[1]*, which works well with open-source engines like
Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In
contrast, SQLGlot*[2]* seems to be a better fit, as it supports 23 SQL
dialects and would provide a more robust solution for interoperability. But
SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
Additionally, I looked into Substrait*[3]* as a potential intermediate
representation for views, but as per my understanding, integration with
commercial engines isn’t quite ready yet. This technology has broader
applications beyond views, including table planning, and may be worth
revisiting in the future.
Given this, I believe that incorporating a translation layer with SQLGlot
is the right direction. If no one is currently working on view
interoperability, I’d be happy to explore this further and take the lead. I
welcome any thoughts or feedback from those involved in related projects.
Looking forward to hearing your opinions.
[1] https://github.com/linkedin/coral
[2] https://sqlglot.com/sqlglot.html
[3] https://substrait.io/
- Ajantha
Re: [Discuss] Iceberg View Interoperability
Posted by Jacques Nadeau <ja...@gmail.com>.
Hey Ajantha, thanks for looping me in. This is a great conversation.
FYI, I'm a co-creator of Substrait so read this all with that in mind.
Substrait has a couple of key underpinnings that are worth noting:
1. It's a specification first and foremost (with tools to help work with
the specification). This is how the project started and continues to work.
Clear specifications are required to succeed at having multiple different
communities and languages work independently but be compatible. This is
inspired by work I've been a part of in Arrow, Iceberg and Parquet.
2. Efficient IR (aka bytecode) is about creating a representation that is
easy for computers to consume and manipulate (as opposed to humans).
On the first item, I think we've already reaped the benefits of this. A
good example is the independent implementations of Substrait in multiple
languages (e.g. Ibis in python, Duckdb in C and Datafusion in Rust,
Calcite/Isthmus in Java). And a fun recent paper by MSFT on their use of
Substrait as a standard plan representation across engines [1]. The success
of the specification is defined by having multiple implementations written
independently (and sometimes competitively). FWIW, I know some lament all
the different Parquet readers and writers written in the world. While there
are probably too many, that's actually a testament to the success of the
specification.
On the second item, I disagree that IR is "just another dialect". Substrait
is built and operates much more like LLVM IR Bytecode or JVM Bytecode.
Swift, Rust and C++ aren't in the same class as LLVM IR. Scala, Kotlin and
Java aren't in the same class as JVM Bytecode. A key characteristic of
formal IRs is deliberate design decisions on the representation for
simplicity, specification and consumption that represents well across a
range of systems. It's a key effort in the Substrait community. Good
concrete examples would be our work to define simple decisions like
disallowing implicit casts as well as more complex topics like how to
represent subqueries [2], specify output type derivation rules [3], and
producing engine independent function compliance tests [4], etc.
WRT to multiple IRs, I don't think there are any others like Substrait
right now. (e.g. (1) well-specified, (2) language agonistic, (3)
serializable, (4) domain specific, (5) system agnostic and (6) built for
"dumb" consumers). I think the most mature attempt prior to Substrait was
probably the GPORCA XML representation [5] (which was used across Hawq &
Greenplum). That project ultimately failed due to single-vendor ownership
and focus on advanced query optimization as opposed to interoperability.
(For what it's worth--when starting on Substrait I spoke to some of the
creators of that project to get feedback on what went well and what
didn't).
I agree with Walaa that any translation engine will need an intermediate
representation to do m=>n translations. In most translation systems, the ir
is something internal that was built over time (what I'd call a "lowercase
ir"). I believe sqlglot and Coral both exist in that category. Both systems
may one day move towards formalizing a representation for input and output.
I think the world would be better if they used an existing standard like
Substrait as opposed to introducing a new standard (it's a lot of work to
get right, let's do it just once as a community). The strengths of tools
like Coral and sqlglot are they are complete products. You can pick each up
and translate SQL today. The strength of Substrait isn't SQL translations,
it's being a clear serializable way to represent data processing
instructions (including SQL).
In many ways I'm reminded of engines that came before Arrow. Each engine
had an internal representation of data. Some were columnar. Some even
looked somewhat like Arrow. When Arrow arrived, they said "we already have
an intermediate representation for data" and they did. People would say
"I'm not going to change my internal representation of data". We never
expected they would. Rewriting an existing engine to a new native format is
near impossible. The goal was new engines would just use Arrow rather than
reinventing the wheel and existing systems would build high-efficiency
adapters to their internal representations. And that's what happened.
Datafusion and Velox didn't need to invent entirely new representations.
When they needed new features they just added them to Arrow (like
Stringview, thanks Meta folks!) I believe Substrait has much the same
opportunity [6]. When people are working on new translations systems, they
use Substrait because it makes things easier. Systems that already have
internal representations pre-Substrait will build adapters over time
(sounds like Coral already has some).
TL;DR, serialization of Iceberg views is exactly the kind of use case that
Substrait was built for and I hope it becomes the main way to get reliable
interoperability. The ecosystem around Substrait (including Coral via
Calcite & Isthmus) is key to turning the specified representation into
something that engines can easily produce/consume. My suggestion would be a
new field that is structured as opposed to overloading a SQL field. Until
Substrait is supported across systems many systems will continue to do what
I'd call "hopeful translation" where they either just run the query (and
hope it works) or run it through a transpiler and hope to get the same
result (type rules be damned). Making it be a hard choice between Substrait
or SQL creates a big impediment to getting from here to where we all want
(reliable consistent views across engines). So I'd say store the SQL and
dialect like now, just start saving the Substrait too.
[1] https://arxiv.org/html/2411.13704v1
[2] https://github.com/substrait-io/substrait/pull/134/files
[3]
https://substrait.io/expressions/scalar_functions/#return-type-expressions
[4]
https://github.com/substrait-io/substrait/tree/main/tests/cases/arithmetic
[5] https://github.com/greenplum-db/gporca-archive
[6]
https://docs.google.com/presentation/d/1HQReIM6uB1Dli_yXfELOJWAE6KsvXAoUmHLlTYZ8laA/edit#slide=id.g150d13b05a9_0_471
On Fri, Nov 29, 2024 at 4:01 PM Ajantha Bhat <aj...@gmail.com> wrote:
> Hi Walaa, thanks for summarizing the questions.
>
> ** If there are interesting applications of introducing an IR in addition
>> to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
>> IR", or should it be able to "represent IRs" in the same way it is able to
>> "represent dialects"?*
>
>
> IMO, having one standardized IR which is widely adopted can help here. If
> it is not widely adopted. It cannot be considered as standardized.
>
> ** What is the unique problem that is solved if Iceberg represents an IR
>> as opposed to representing a SQL dialect? We can keep in mind the following
>> when answering this question:*
>
> Yes we still need conversion if the engines cannot understand IR directly.
> IMO, converting IR to a calcite plan is more efficient than converting SQL
> dialect to each engine's SQL dialect.
>
> I have tagged Jacques on this thread. I am sure he can explain more on why
> Substrait is the suitable choice here.
>
> - Ajantha
>
>
> On Fri, Nov 29, 2024 at 2:30 AM Walaa Eldin Moustafa <
> wa.moustafa@gmail.com> wrote:
>
>> Hi Ajantha,
>>
>> I do not clearly see a consensus in this thread. If anything, I see this
>> thread posing more questions than answers. Here is the collection of
>> questions I could distill from the thread:
>>
>> ** What is the unique problem that is solved if Iceberg represents an IR
>> as opposed to representing a SQL dialect? We can keep in mind the following
>> when answering this question:*
>> ** An IR is a form of a dialect. Dialect is in text form. IR is in
>> structured form.
>> ** Engines typically use Dialect as their first class citizen. So
>> interoperability is typically between SQL dialects. (IR helps, but not
>> necessarily through "storing" it).
>> ** Both dialect and IR conversion require translation.
>> ** Both dialect and IR can be fully specified. For example, the SQL
>> Standard is based on some form of a SQL dialect, not a structured IR.
>>
>> ** If there are interesting applications of introducing an IR in addition
>> to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
>> IR", or should it be able to "represent IRs" in the same way it is able to
>> "represent dialects"?*
>>
>> ** If the answer is to adopt a single IR, what is the framework/criteria
>> to design or choose that IR?*
>> ** Is it serializability, expressibility, or translatability?
>> ** How do we score the IRs against this criteria?
>>
>> ** If the answer is to support representing multiple IRs, the type of
>> problems Iceberg would be concerned with will be different. We may have to
>> think about different types of questions in this case.*
>>
>> Thanks,
>> Walaa.
>>
>>
>> On Mon, Nov 4, 2024 at 8:40 AM Matt Topol <zo...@gmail.com> wrote:
>>
>>> For reference, there are two reasons why I chose to add that
>>> substrait.go:
>>>
>>> 1) The Golang Arrow implementation has a compute package which is able
>>> to evaluate substrait expressions as long as the kernels exist in the
>>> package.
>>>
>>> 2) Along the lines of this conversation, I wanted to be able to
>>> generically create Substrait expressions from iceberg expressions. With the
>>> goal being that the go implementation could potentially be able to create a
>>> full substrait plan (including the reading) from an iceberg table (and
>>> metadata) and expression. Eventually the plan would be able to be sent to a
>>> compute engine which wouldn't have to know anything about iceberg to
>>> execute it!
>>>
>>> On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
>>>
>>>> Matt also just added `substrait.go` to the Iceberg-Go implementation
>>>> that I was reviewing today:
>>>>
>>>> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>>>>
>>>> That converts an Iceberg expression into a substrate one, pretty
>>>> exciting stuff
>>>>
>>>> Kind regards,
>>>> Fokko
>>>>
>>>> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb@nanthrax.net
>>>> >:
>>>>
>>>>> Hi Ajantha,
>>>>>
>>>>> During CommunityOverCode, I chatted with Matt Topol about Substrait
>>>>> and ADBC.
>>>>> I checked the Substrait support in DataFusion and it's interesting.
>>>>>
>>>>> I was thinking about where to actually store the Substrait plan (I was
>>>>> thinking about an intermediate SQL representation that we could store
>>>>> as a metadata instead of directly the plan).
>>>>>
>>>>> Maybe, we could start with a proposal document to explore the
>>>>> different options (and so follow Iceberg proposals process, creating a
>>>>> GitHub Issue with the proposal tag, and attaching the document) ?
>>>>>
>>>>> Thanks !
>>>>> Regards
>>>>> JB
>>>>>
>>>>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>>>>> wrote:
>>>>> >
>>>>> > Thanks everyone for the detailed discussions.
>>>>> >
>>>>> > Looks like we have consensus towards Substrait.
>>>>> > Last time I checked it was not adopted by all the engines. But we
>>>>> can work towards the adoption as well.
>>>>> >
>>>>> > I will explore further on Substrait and come up with the design doc
>>>>> on the same.
>>>>> >
>>>>> > Thanks,
>>>>> > Ajantha
>>>>> >
>>>>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>>>>> wrote:
>>>>> >>
>>>>> >> Hey all,
>>>>> >>
>>>>> >> I'm +1 in efforts to make views more interoperable across engines
>>>>> as I believe such efforts would be beneficial for the wider ecosystem. I
>>>>> think the way to do that is through higher fidelity IRs such as Substrait.
>>>>> >>
>>>>> >> I agree with Walaa that there's not really a valid distinction
>>>>> between IR vs non-IR projects when it comes to translation; my
>>>>> understanding is that in the end any translation framework would have to
>>>>> normalize to an intermediate representation. With the SQLGlot case, it's
>>>>> just that the IR is at the AST level and with the others they have higher
>>>>> fidelity to capture more accurate query semantics (correct me if I'm wrong
>>>>> here). As of today, it is already possible to use SQLGlot, translate to the
>>>>> desired SQL and store these SQL representations. However, since it's not as
>>>>> high fidelity as a proper IR layer, there are issues to consider like Fokko
>>>>> mentioned; but again, if users are happy with their results, they can do
>>>>> this today without any spec changes.
>>>>> >>
>>>>> >> In my opinion, the biggest hurdle for Substrait or any other IR to
>>>>> be a viable standard in Iceberg that's worth maintaining is that there
>>>>> would need to be consensus across different engine/language communities
>>>>> (e.g. Walaa referenced the Trino community's perspective on such IR
>>>>> layers). Otherwise it risks becoming something that's defined in the
>>>>> standard but really isn't well accepted which I think we all want to avoid.
>>>>> >>
>>>>> >> I think as a starting point, it would be great to sync with at
>>>>> least OSS engines/language communities and try and understand any concrete
>>>>> points of skepticism for considering such a standard. So far a lot of the
>>>>> points of skepticism as I read it are around such a layer being only
>>>>> considerate of 1 engine or having such substantial feature gaps that it
>>>>> can't be considered; but no concrete cases have been called out.
>>>>> >> Once we establish concrete gaps, I think then it would make sense
>>>>> to work with the respective IR community to help close those gaps or if
>>>>> needed consider other paths.
>>>>> >>
>>>>> >> Thanks,
>>>>> >> Amogh Jahagirdar
>>>>> >>
>>>>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>>>>> piotr.findeisen@gmail.com> wrote:
>>>>> >>>
>>>>> >>> Hi,
>>>>> >>>
>>>>> >>> I have no experience with Substrait, but i agree it looks like the
>>>>> tool for the job.
>>>>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the
>>>>> views.
>>>>> >>>
>>>>> >>> We can experiment with serialized IR being stored as a String with
>>>>> new dialect name, and this is how we should get this started.
>>>>> >>> It's probably good end solution as well, but the important
>>>>> value-add is if we manage to converge towards one shared IR that's "native
>>>>> to iceberg".
>>>>> >>> This would be best for the users -- more views would just work.
>>>>> >>> And best for long-term evolution of the project -- standardized IR
>>>>> would help things like incremental refreshes (for materialized views).
>>>>> >>>
>>>>> >>> Best
>>>>> >>> Piotr
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>>>>> wa.moustafa@gmail.com> wrote:
>>>>> >>>>
>>>>> >>>> Hi Fokko,
>>>>> >>>>
>>>>> >>>> We can implement Python/Rust/Go clients to interop with the
>>>>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>>>>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>>>>> etc) be reimplemented in those languages. Such implementations actually
>>>>> depend on the reuse of the native parsers of those dialects which are
>>>>> typically in Java (also this is to your point about the language coverage
>>>>> -- reusing native parsers is a principle that Coral follows to be compliant
>>>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>>>> around) would be a good start. For example, Python-specific backends and
>>>>> front-end implementations can follow from that.
>>>>> >>>>
>>>>> >>>> Thanks,
>>>>> >>>> Walaa.
>>>>> >>>>
>>>>> >>>>
>>>>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <
>>>>> fokko@apache.org> wrote:
>>>>> >>>>>
>>>>> >>>>> Hey everyone,
>>>>> >>>>>
>>>>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly
>>>>> because tooling in Python tends to work with data frames, rather than SQL.
>>>>> I do think it would be valuable to extend support there.
>>>>> >>>>>
>>>>> >>>>> I have a bit of experience in turning SQL into ASTs and
>>>>> extending grammar, and I'm confident to say that it is nearly impossible to
>>>>> cover all the grammar of a specific dialect. My main question is, what will
>>>>> SQLGlot do when we try to translate a dialect that it doesn't fully
>>>>> understand? Will it error out, or will it produce faulty SQL? A simple
>>>>> example can be functions that are not supported in other engines up to
>>>>> recursive CTE's. In this case, not failing upfront would cause correctness
>>>>> issues.
>>>>> >>>>>
>>>>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>>>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>>>> actual execution. This was still an early stage and required a lot of work
>>>>> around credentials and field-IDs, but it was quite promising. Using
>>>>> Substrait as views looks easier to me, and would also translate to a
>>>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>>>> Python/Rust/Go support?
>>>>> >>>>>
>>>>> >>>>> Kind regards,
>>>>> >>>>> Fokko
>>>>> >>>>>
>>>>> >>>>>
>>>>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>>>> wa.moustafa@gmail.com>:
>>>>> >>>>>>
>>>>> >>>>>> I think this may need some more discussion.
>>>>> >>>>>>
>>>>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In
>>>>> this case, this dialect will be mostly specific to Iceberg, and compute
>>>>> engines will still support reading views in their native SQL. There are
>>>>> some data points on this from the Trino community in a previous discussion
>>>>> [1]. In addition to being not directly consumable by engines, a serialized
>>>>> IR will be hard to consume by humans too.
>>>>> >>>>>>
>>>>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>>>>> serialized IR, we will end up again doing translation, from that IR to the
>>>>> engine's dialect on view read time, and from the engine's dialect to that
>>>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>>>> >>>>>>
>>>>> >>>>>> I think it is better to not quickly adopt the serialized IR
>>>>> path until it is proven to work and there is sufficient tooling and support
>>>>> around it, else it will end up being a constraint.
>>>>> >>>>>>
>>>>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>>>>> some fundamental differences between their approaches, mainly around the
>>>>> intermediate representation abstraction. Coral models both the AST and the
>>>>> logical plan of a query, making it able to capture the query semantics more
>>>>> accurately and hence perform precise transformations. On the flip side,
>>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>>> a major gap in any solution that does not capture the logical plan for
>>>>> example, yet very important to perform successful translation. This is
>>>>> backed up by some experiments we performed on actual queries and their
>>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>>> SQLGlot).
>>>>> >>>>>>
>>>>> >>>>>> For the IR: Any translation solution (including Coral) must
>>>>> rely on an IR, and it has to be decoupled from any of the input and output
>>>>> dialects. This is true in the Coral case today. Such IR is the way to
>>>>> represent both the intermediate AST and logical plans. Therefore, I do not
>>>>> think we can necessarily split projects as "IR projects" vs not, since all
>>>>> solutions must use an IR. With that said, IR serialization is a matter of
>>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>>> and straightforward.
>>>>> >>>>>>
>>>>> >>>>>> [1]
>>>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>>> >>>>>>
>>>>> >>>>>> Thanks,
>>>>> >>>>>> Walaa.
>>>>> >>>>>>
>>>>> >>>>>>
>>>>> >>>>>>
>>>>>
>>>>
Re: [Discuss] Iceberg View Interoperability
Posted by Ajantha Bhat <aj...@gmail.com>.
Hi Walaa, thanks for summarizing the questions.
** If there are interesting applications of introducing an IR in addition
> to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
> IR", or should it be able to "represent IRs" in the same way it is able to
> "represent dialects"?*
IMO, having one standardized IR which is widely adopted can help here. If
it is not widely adopted. It cannot be considered as standardized.
** What is the unique problem that is solved if Iceberg represents an IR as
> opposed to representing a SQL dialect? We can keep in mind the following
> when answering this question:*
Yes we still need conversion if the engines cannot understand IR directly.
IMO, converting IR to a calcite plan is more efficient than converting SQL
dialect to each engine's SQL dialect.
I have tagged Jacques on this thread. I am sure he can explain more on why
Substrait is the suitable choice here.
- Ajantha
On Fri, Nov 29, 2024 at 2:30 AM Walaa Eldin Moustafa <wa...@gmail.com>
wrote:
> Hi Ajantha,
>
> I do not clearly see a consensus in this thread. If anything, I see this
> thread posing more questions than answers. Here is the collection of
> questions I could distill from the thread:
>
> ** What is the unique problem that is solved if Iceberg represents an IR
> as opposed to representing a SQL dialect? We can keep in mind the following
> when answering this question:*
> ** An IR is a form of a dialect. Dialect is in text form. IR is in
> structured form.
> ** Engines typically use Dialect as their first class citizen. So
> interoperability is typically between SQL dialects. (IR helps, but not
> necessarily through "storing" it).
> ** Both dialect and IR conversion require translation.
> ** Both dialect and IR can be fully specified. For example, the SQL
> Standard is based on some form of a SQL dialect, not a structured IR.
>
> ** If there are interesting applications of introducing an IR in addition
> to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
> IR", or should it be able to "represent IRs" in the same way it is able to
> "represent dialects"?*
>
> ** If the answer is to adopt a single IR, what is the framework/criteria
> to design or choose that IR?*
> ** Is it serializability, expressibility, or translatability?
> ** How do we score the IRs against this criteria?
>
> ** If the answer is to support representing multiple IRs, the type of
> problems Iceberg would be concerned with will be different. We may have to
> think about different types of questions in this case.*
>
> Thanks,
> Walaa.
>
>
> On Mon, Nov 4, 2024 at 8:40 AM Matt Topol <zo...@gmail.com> wrote:
>
>> For reference, there are two reasons why I chose to add that substrait.go:
>>
>> 1) The Golang Arrow implementation has a compute package which is able to
>> evaluate substrait expressions as long as the kernels exist in the package.
>>
>> 2) Along the lines of this conversation, I wanted to be able to
>> generically create Substrait expressions from iceberg expressions. With the
>> goal being that the go implementation could potentially be able to create a
>> full substrait plan (including the reading) from an iceberg table (and
>> metadata) and expression. Eventually the plan would be able to be sent to a
>> compute engine which wouldn't have to know anything about iceberg to
>> execute it!
>>
>> On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
>>
>>> Matt also just added `substrait.go` to the Iceberg-Go implementation
>>> that I was reviewing today:
>>>
>>> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>>>
>>> That converts an Iceberg expression into a substrate one, pretty
>>> exciting stuff
>>>
>>> Kind regards,
>>> Fokko
>>>
>>> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb@nanthrax.net
>>> >:
>>>
>>>> Hi Ajantha,
>>>>
>>>> During CommunityOverCode, I chatted with Matt Topol about Substrait and
>>>> ADBC.
>>>> I checked the Substrait support in DataFusion and it's interesting.
>>>>
>>>> I was thinking about where to actually store the Substrait plan (I was
>>>> thinking about an intermediate SQL representation that we could store
>>>> as a metadata instead of directly the plan).
>>>>
>>>> Maybe, we could start with a proposal document to explore the
>>>> different options (and so follow Iceberg proposals process, creating a
>>>> GitHub Issue with the proposal tag, and attaching the document) ?
>>>>
>>>> Thanks !
>>>> Regards
>>>> JB
>>>>
>>>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>>>> wrote:
>>>> >
>>>> > Thanks everyone for the detailed discussions.
>>>> >
>>>> > Looks like we have consensus towards Substrait.
>>>> > Last time I checked it was not adopted by all the engines. But we can
>>>> work towards the adoption as well.
>>>> >
>>>> > I will explore further on Substrait and come up with the design doc
>>>> on the same.
>>>> >
>>>> > Thanks,
>>>> > Ajantha
>>>> >
>>>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>>>> wrote:
>>>> >>
>>>> >> Hey all,
>>>> >>
>>>> >> I'm +1 in efforts to make views more interoperable across engines as
>>>> I believe such efforts would be beneficial for the wider ecosystem. I think
>>>> the way to do that is through higher fidelity IRs such as Substrait.
>>>> >>
>>>> >> I agree with Walaa that there's not really a valid distinction
>>>> between IR vs non-IR projects when it comes to translation; my
>>>> understanding is that in the end any translation framework would have to
>>>> normalize to an intermediate representation. With the SQLGlot case, it's
>>>> just that the IR is at the AST level and with the others they have higher
>>>> fidelity to capture more accurate query semantics (correct me if I'm wrong
>>>> here). As of today, it is already possible to use SQLGlot, translate to the
>>>> desired SQL and store these SQL representations. However, since it's not as
>>>> high fidelity as a proper IR layer, there are issues to consider like Fokko
>>>> mentioned; but again, if users are happy with their results, they can do
>>>> this today without any spec changes.
>>>> >>
>>>> >> In my opinion, the biggest hurdle for Substrait or any other IR to
>>>> be a viable standard in Iceberg that's worth maintaining is that there
>>>> would need to be consensus across different engine/language communities
>>>> (e.g. Walaa referenced the Trino community's perspective on such IR
>>>> layers). Otherwise it risks becoming something that's defined in the
>>>> standard but really isn't well accepted which I think we all want to avoid.
>>>> >>
>>>> >> I think as a starting point, it would be great to sync with at least
>>>> OSS engines/language communities and try and understand any concrete points
>>>> of skepticism for considering such a standard. So far a lot of the points
>>>> of skepticism as I read it are around such a layer being only considerate
>>>> of 1 engine or having such substantial feature gaps that it can't be
>>>> considered; but no concrete cases have been called out.
>>>> >> Once we establish concrete gaps, I think then it would make sense to
>>>> work with the respective IR community to help close those gaps or if needed
>>>> consider other paths.
>>>> >>
>>>> >> Thanks,
>>>> >> Amogh Jahagirdar
>>>> >>
>>>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>>>> piotr.findeisen@gmail.com> wrote:
>>>> >>>
>>>> >>> Hi,
>>>> >>>
>>>> >>> I have no experience with Substrait, but i agree it looks like the
>>>> tool for the job.
>>>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the
>>>> views.
>>>> >>>
>>>> >>> We can experiment with serialized IR being stored as a String with
>>>> new dialect name, and this is how we should get this started.
>>>> >>> It's probably good end solution as well, but the important
>>>> value-add is if we manage to converge towards one shared IR that's "native
>>>> to iceberg".
>>>> >>> This would be best for the users -- more views would just work.
>>>> >>> And best for long-term evolution of the project -- standardized IR
>>>> would help things like incremental refreshes (for materialized views).
>>>> >>>
>>>> >>> Best
>>>> >>> Piotr
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>>>> wa.moustafa@gmail.com> wrote:
>>>> >>>>
>>>> >>>> Hi Fokko,
>>>> >>>>
>>>> >>>> We can implement Python/Rust/Go clients to interop with the
>>>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>>>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>>>> etc) be reimplemented in those languages. Such implementations actually
>>>> depend on the reuse of the native parsers of those dialects which are
>>>> typically in Java (also this is to your point about the language coverage
>>>> -- reusing native parsers is a principle that Coral follows to be compliant
>>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>>> around) would be a good start. For example, Python-specific backends and
>>>> front-end implementations can follow from that.
>>>> >>>>
>>>> >>>> Thanks,
>>>> >>>> Walaa.
>>>> >>>>
>>>> >>>>
>>>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
>>>> wrote:
>>>> >>>>>
>>>> >>>>> Hey everyone,
>>>> >>>>>
>>>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly
>>>> because tooling in Python tends to work with data frames, rather than SQL.
>>>> I do think it would be valuable to extend support there.
>>>> >>>>>
>>>> >>>>> I have a bit of experience in turning SQL into ASTs and extending
>>>> grammar, and I'm confident to say that it is nearly impossible to cover all
>>>> the grammar of a specific dialect. My main question is, what will SQLGlot
>>>> do when we try to translate a dialect that it doesn't fully understand?
>>>> Will it error out, or will it produce faulty SQL? A simple example can be
>>>> functions that are not supported in other engines up to recursive CTE's. In
>>>> this case, not failing upfront would cause correctness issues.
>>>> >>>>>
>>>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>>> actual execution. This was still an early stage and required a lot of work
>>>> around credentials and field-IDs, but it was quite promising. Using
>>>> Substrait as views looks easier to me, and would also translate to a
>>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>>> Python/Rust/Go support?
>>>> >>>>>
>>>> >>>>> Kind regards,
>>>> >>>>> Fokko
>>>> >>>>>
>>>> >>>>>
>>>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>>> wa.moustafa@gmail.com>:
>>>> >>>>>>
>>>> >>>>>> I think this may need some more discussion.
>>>> >>>>>>
>>>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In this
>>>> case, this dialect will be mostly specific to Iceberg, and compute engines
>>>> will still support reading views in their native SQL. There are some data
>>>> points on this from the Trino community in a previous discussion [1]. In
>>>> addition to being not directly consumable by engines, a serialized IR will
>>>> be hard to consume by humans too.
>>>> >>>>>>
>>>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>>>> serialized IR, we will end up again doing translation, from that IR to the
>>>> engine's dialect on view read time, and from the engine's dialect to that
>>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>>> >>>>>>
>>>> >>>>>> I think it is better to not quickly adopt the serialized IR path
>>>> until it is proven to work and there is sufficient tooling and support
>>>> around it, else it will end up being a constraint.
>>>> >>>>>>
>>>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>>>> some fundamental differences between their approaches, mainly around the
>>>> intermediate representation abstraction. Coral models both the AST and the
>>>> logical plan of a query, making it able to capture the query semantics more
>>>> accurately and hence perform precise transformations. On the flip side,
>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>> a major gap in any solution that does not capture the logical plan for
>>>> example, yet very important to perform successful translation. This is
>>>> backed up by some experiments we performed on actual queries and their
>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>> SQLGlot).
>>>> >>>>>>
>>>> >>>>>> For the IR: Any translation solution (including Coral) must rely
>>>> on an IR, and it has to be decoupled from any of the input and output
>>>> dialects. This is true in the Coral case today. Such IR is the way to
>>>> represent both the intermediate AST and logical plans. Therefore, I do not
>>>> think we can necessarily split projects as "IR projects" vs not, since all
>>>> solutions must use an IR. With that said, IR serialization is a matter of
>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>> and straightforward.
>>>> >>>>>>
>>>> >>>>>> [1]
>>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>> >>>>>>
>>>> >>>>>> Thanks,
>>>> >>>>>> Walaa.
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>>
>>>
Re: [Discuss] Iceberg View Interoperability
Posted by Vladimir Ozerov <vo...@querifylabs.com>.
Hi Wes,
We have a positive experience using Substrait to transfer IRs between
engines and express custom behavior via extensions, but in a tightly
integrated controlled environment. I am not sure that extensions will work
well at a large scale, as it may lead to the same problem we are trying to
solve: multiple, incompatible declarative programs (be it an SQL string or
a logical IR). It's hard to believe that engine developers will start
incorporating subtleties of other engines to improve interoperability. "1 /
3" gives "0" in Trino but "0.333333" in Hive - does anybody want to
implement, maintain, test, and document both in a single product? :-)
I was thinking about a more radical approach: Iceberg integrates Substarit
and then declares the official recommendations on how the functions must
behave. This might be the same as the default Substrait behavior (if any)
or an Iceberg-specific flavor. So instead of having "hive/divide",
"trino/divide", and "datafusion/divide", there will be only one
"iceberg/divide". Each engine will continue maintaining its own semantics
plus an optional implementation of a recommended Substrait/Iceberg
semantics. Note that this is an optional decision for each engine and
doesn't preclude the given engine from implementing custom flavors of other
engines. If this approach generates sufficient traction, N engines will
require 1 (own) +1 (Iceberg/Substrait) implementations of each function to
reach sufficient interoperability with others.
I'd also like to mention that different parts of a REST protocol may have
different requirements to the IR "strictness". Imagine that we use an IR to
express materialized view plans. Any discrepancy between the producing and
consuming engine would lead to an incorrect result. But imagine that in
future we extend REST protocol to store predicate statistics, such as "x >
1 over table T at snapshot S yields 95 rows and NDV 44". Since these are
optimizer hints, engines may potentially use them even without
perfectly aligned semantics. I.e., even if we couldn't agree on Substrait
for view plans, there are still valid cases where it could be useful in
Iceberg.
Regards,
Vladimir
On Thu, Dec 5, 2024 at 8:28 PM Weston Pace <we...@gmail.com> wrote:
> Disclaimer: Also on Substrait SMC here and also have made some
> considerable investment into Substrait in my professional work (Lance) so
> this is an not unbiased opinion. I just want to give a few words on why I
> think an IR (and specifically Substrait) is not just another dialect (I'll
> try and be more concise than Jacques :P).
>
> IRs have fewer dialects and less ambiguity than SQL strings. I suspect
> this is mostly because they are written by machines and not humans. So if
> interoperability is the goal I think an IR (Substrait or any other) is
> going to provide an advantage over SQL. In Substrait, we have tried very
> hard to be precise and thorough in our relation definitions. We have an
> initial framework[1] in place for ensuring different producers/consumers
> are translating the IR consistently with tests.
>
> The vast majority of discrepancies (e.g. the difference between Hive and
> Trino with regards to a / b) are actually discrepancies in function
> semantics. Substrait is not opinionated here. Function semantics are
> "extensions". We aim to be capable of expressing all semantics. Partly by
> differently named functions (hive.org/divide vs. trino.org/divide) and
> partly by introducing function options (substrait's reference
> implementation of divide has an option for how nans are handled for
> example). There is an entirely different framework in place for testing
> function / dialect interoperability[2].
>
> We are still very much in the early days and the current work here I think
> is focused on cataloguing the various differences. My hope is that the
> next step we can get producers to stop writing "bland substrait" (no
> specification of dialect) and start writing "localized substrait"
> (specifying exactly what dialect the producer expects). Then we can move
> to consumers rejecting "localized substrait" when they do not understand
> the dialect. Finally we can move towards a world with multi-dialect
> consumers and/or dialect conversion tools.
>
> This effort is definitely just getting started so this is a roadmap. As I
> said, we're still beginning the "cataloguing" stage.
>
> [1] https://github.com/substrait-io/consumer-testing
> [2] https://github.com/substrait-io/bft
>
> P.S. With specific regards to the plan "SELECT a / b" I can tell you that
> there are 9 different special cases of division (0/0, 0/NAN, 0/INF, NAN/0,
> NAN/NAN, NAN/INF, INF/0, INF/NAN, INF/INF) and it is extremely rare to find
> two engines that agree on the behavior across those 9 cases (duckdb,
> postgres, datafusion, for example, all disagree on at least one case).
>
> P.P.S. The plan "SELECT sum(a)" is even more diabolical as it pulls
> numerical precision and processing order into the mix (e.g. some engines
> can give you two different answers on two different calls to the _same
> engine_).
>
> On Thu, Dec 5, 2024 at 5:32 AM Vladimir Ozerov <vo...@querifylabs.com>
> wrote:
>
>> Hi Walaa,
>>
>> Many of these questions do not have universally correct answers. SQL
>> dialects and IRs are incompatible between engines. And even if we find a
>> compatible IR, many of the simplest queries with the same IR will behave
>> differently between engines, largely defeating the whole idea of view
>> interoperability. E.g. "SELECT a / b" will work differently in Hive and
>> Trino, and "SELECT sum(a)" will return different results in ClickHouse and
>> Spark. Both queries require the simplest IR, yet it doesn't help.
>>
>> Substrait will not help resolve all these problems all at once. Like any
>> protocol, it has some gaps, favors some non-ideal Calcite-inspired design
>> decisions, etc. But if community decide to integrate Substrait, the gravity
>> of Iceberg will **force** engine developers and the Substrait community to
>> start polishing missing pieces and discrepancies because otherwise, vendors
>> will simply start losing opportunities. And money is a wonderful motivator
>> to start doing something that you otherwise postpone indefinitely :-)
>>
>> IMO, the community should not try to eliminate all possible unknowns
>> beforehand (which pushes us towards the status quo) but instead take a leap
>> of faith. Substrait has the real potential to pave the path towards
>> reusable computations (be it views, matviews, predicate statistics, etc)
>> for a large class of applications, because it was created to solve exactly
>> this kind of problems.
>>
>> Regards,
>> Vladimir.
>>
>> On Fri, Nov 29, 2024 at 12:00 AM Walaa Eldin Moustafa <
>> wa.moustafa@gmail.com> wrote:
>>
>>> Hi Ajantha,
>>>
>>> I do not clearly see a consensus in this thread. If anything, I see this
>>> thread posing more questions than answers. Here is the collection of
>>> questions I could distill from the thread:
>>>
>>> ** What is the unique problem that is solved if Iceberg represents an IR
>>> as opposed to representing a SQL dialect? We can keep in mind the following
>>> when answering this question:*
>>> ** An IR is a form of a dialect. Dialect is in text form. IR is in
>>> structured form.
>>> ** Engines typically use Dialect as their first class citizen. So
>>> interoperability is typically between SQL dialects. (IR helps, but not
>>> necessarily through "storing" it).
>>> ** Both dialect and IR conversion require translation.
>>> ** Both dialect and IR can be fully specified. For example, the SQL
>>> Standard is based on some form of a SQL dialect, not a structured IR.
>>>
>>> ** If there are interesting applications of introducing an IR in
>>> addition to dialects, should Iceberg adopt only one IR as the canonical
>>> "Iceberg IR", or should it be able to "represent IRs" in the same way it is
>>> able to "represent dialects"?*
>>>
>>> ** If the answer is to adopt a single IR, what is the framework/criteria
>>> to design or choose that IR?*
>>> ** Is it serializability, expressibility, or translatability?
>>> ** How do we score the IRs against this criteria?
>>>
>>> ** If the answer is to support representing multiple IRs, the type of
>>> problems Iceberg would be concerned with will be different. We may have to
>>> think about different types of questions in this case.*
>>>
>>> Thanks,
>>> Walaa.
>>>
>>>
>>> On Mon, Nov 4, 2024 at 8:40 AM Matt Topol <zo...@gmail.com>
>>> wrote:
>>>
>>>> For reference, there are two reasons why I chose to add that
>>>> substrait.go:
>>>>
>>>> 1) The Golang Arrow implementation has a compute package which is able
>>>> to evaluate substrait expressions as long as the kernels exist in the
>>>> package.
>>>>
>>>> 2) Along the lines of this conversation, I wanted to be able to
>>>> generically create Substrait expressions from iceberg expressions. With the
>>>> goal being that the go implementation could potentially be able to create a
>>>> full substrait plan (including the reading) from an iceberg table (and
>>>> metadata) and expression. Eventually the plan would be able to be sent to a
>>>> compute engine which wouldn't have to know anything about iceberg to
>>>> execute it!
>>>>
>>>> On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
>>>>
>>>>> Matt also just added `substrait.go` to the Iceberg-Go implementation
>>>>> that I was reviewing today:
>>>>>
>>>>> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>>>>>
>>>>> That converts an Iceberg expression into a substrate one, pretty
>>>>> exciting stuff
>>>>>
>>>>> Kind regards,
>>>>> Fokko
>>>>>
>>>>> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <
>>>>> jb@nanthrax.net>:
>>>>>
>>>>>> Hi Ajantha,
>>>>>>
>>>>>> During CommunityOverCode, I chatted with Matt Topol about Substrait
>>>>>> and ADBC.
>>>>>> I checked the Substrait support in DataFusion and it's interesting.
>>>>>>
>>>>>> I was thinking about where to actually store the Substrait plan (I was
>>>>>> thinking about an intermediate SQL representation that we could store
>>>>>> as a metadata instead of directly the plan).
>>>>>>
>>>>>> Maybe, we could start with a proposal document to explore the
>>>>>> different options (and so follow Iceberg proposals process, creating a
>>>>>> GitHub Issue with the proposal tag, and attaching the document) ?
>>>>>>
>>>>>> Thanks !
>>>>>> Regards
>>>>>> JB
>>>>>>
>>>>>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>>>>>> wrote:
>>>>>> >
>>>>>> > Thanks everyone for the detailed discussions.
>>>>>> >
>>>>>> > Looks like we have consensus towards Substrait.
>>>>>> > Last time I checked it was not adopted by all the engines. But we
>>>>>> can work towards the adoption as well.
>>>>>> >
>>>>>> > I will explore further on Substrait and come up with the design doc
>>>>>> on the same.
>>>>>> >
>>>>>> > Thanks,
>>>>>> > Ajantha
>>>>>> >
>>>>>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>>>>>> wrote:
>>>>>> >>
>>>>>> >> Hey all,
>>>>>> >>
>>>>>> >> I'm +1 in efforts to make views more interoperable across engines
>>>>>> as I believe such efforts would be beneficial for the wider ecosystem. I
>>>>>> think the way to do that is through higher fidelity IRs such as Substrait.
>>>>>> >>
>>>>>> >> I agree with Walaa that there's not really a valid distinction
>>>>>> between IR vs non-IR projects when it comes to translation; my
>>>>>> understanding is that in the end any translation framework would have to
>>>>>> normalize to an intermediate representation. With the SQLGlot case, it's
>>>>>> just that the IR is at the AST level and with the others they have higher
>>>>>> fidelity to capture more accurate query semantics (correct me if I'm wrong
>>>>>> here). As of today, it is already possible to use SQLGlot, translate to the
>>>>>> desired SQL and store these SQL representations. However, since it's not as
>>>>>> high fidelity as a proper IR layer, there are issues to consider like Fokko
>>>>>> mentioned; but again, if users are happy with their results, they can do
>>>>>> this today without any spec changes.
>>>>>> >>
>>>>>> >> In my opinion, the biggest hurdle for Substrait or any other IR to
>>>>>> be a viable standard in Iceberg that's worth maintaining is that there
>>>>>> would need to be consensus across different engine/language communities
>>>>>> (e.g. Walaa referenced the Trino community's perspective on such IR
>>>>>> layers). Otherwise it risks becoming something that's defined in the
>>>>>> standard but really isn't well accepted which I think we all want to avoid.
>>>>>> >>
>>>>>> >> I think as a starting point, it would be great to sync with at
>>>>>> least OSS engines/language communities and try and understand any concrete
>>>>>> points of skepticism for considering such a standard. So far a lot of the
>>>>>> points of skepticism as I read it are around such a layer being only
>>>>>> considerate of 1 engine or having such substantial feature gaps that it
>>>>>> can't be considered; but no concrete cases have been called out.
>>>>>> >> Once we establish concrete gaps, I think then it would make sense
>>>>>> to work with the respective IR community to help close those gaps or if
>>>>>> needed consider other paths.
>>>>>> >>
>>>>>> >> Thanks,
>>>>>> >> Amogh Jahagirdar
>>>>>> >>
>>>>>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>>>>>> piotr.findeisen@gmail.com> wrote:
>>>>>> >>>
>>>>>> >>> Hi,
>>>>>> >>>
>>>>>> >>> I have no experience with Substrait, but i agree it looks like
>>>>>> the tool for the job.
>>>>>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the
>>>>>> views.
>>>>>> >>>
>>>>>> >>> We can experiment with serialized IR being stored as a String
>>>>>> with new dialect name, and this is how we should get this started.
>>>>>> >>> It's probably good end solution as well, but the important
>>>>>> value-add is if we manage to converge towards one shared IR that's "native
>>>>>> to iceberg".
>>>>>> >>> This would be best for the users -- more views would just work.
>>>>>> >>> And best for long-term evolution of the project -- standardized
>>>>>> IR would help things like incremental refreshes (for materialized views).
>>>>>> >>>
>>>>>> >>> Best
>>>>>> >>> Piotr
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>>>>>> wa.moustafa@gmail.com> wrote:
>>>>>> >>>>
>>>>>> >>>> Hi Fokko,
>>>>>> >>>>
>>>>>> >>>> We can implement Python/Rust/Go clients to interop with the
>>>>>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>>>>>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>>>>>> etc) be reimplemented in those languages. Such implementations actually
>>>>>> depend on the reuse of the native parsers of those dialects which are
>>>>>> typically in Java (also this is to your point about the language coverage
>>>>>> -- reusing native parsers is a principle that Coral follows to be compliant
>>>>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>>>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>>>>> around) would be a good start. For example, Python-specific backends and
>>>>>> front-end implementations can follow from that.
>>>>>> >>>>
>>>>>> >>>> Thanks,
>>>>>> >>>> Walaa.
>>>>>> >>>>
>>>>>> >>>>
>>>>>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <
>>>>>> fokko@apache.org> wrote:
>>>>>> >>>>>
>>>>>> >>>>> Hey everyone,
>>>>>> >>>>>
>>>>>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly
>>>>>> because tooling in Python tends to work with data frames, rather than SQL.
>>>>>> I do think it would be valuable to extend support there.
>>>>>> >>>>>
>>>>>> >>>>> I have a bit of experience in turning SQL into ASTs and
>>>>>> extending grammar, and I'm confident to say that it is nearly impossible to
>>>>>> cover all the grammar of a specific dialect. My main question is, what will
>>>>>> SQLGlot do when we try to translate a dialect that it doesn't fully
>>>>>> understand? Will it error out, or will it produce faulty SQL? A simple
>>>>>> example can be functions that are not supported in other engines up to
>>>>>> recursive CTE's. In this case, not failing upfront would cause correctness
>>>>>> issues.
>>>>>> >>>>>
>>>>>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>>>>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>>>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>>>>> actual execution. This was still an early stage and required a lot of work
>>>>>> around credentials and field-IDs, but it was quite promising. Using
>>>>>> Substrait as views looks easier to me, and would also translate to a
>>>>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>>>>> Python/Rust/Go support?
>>>>>> >>>>>
>>>>>> >>>>> Kind regards,
>>>>>> >>>>> Fokko
>>>>>> >>>>>
>>>>>> >>>>>
>>>>>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>>>>> wa.moustafa@gmail.com>:
>>>>>> >>>>>>
>>>>>> >>>>>> I think this may need some more discussion.
>>>>>> >>>>>>
>>>>>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In
>>>>>> this case, this dialect will be mostly specific to Iceberg, and compute
>>>>>> engines will still support reading views in their native SQL. There are
>>>>>> some data points on this from the Trino community in a previous discussion
>>>>>> [1]. In addition to being not directly consumable by engines, a serialized
>>>>>> IR will be hard to consume by humans too.
>>>>>> >>>>>>
>>>>>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>>>>>> serialized IR, we will end up again doing translation, from that IR to the
>>>>>> engine's dialect on view read time, and from the engine's dialect to that
>>>>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>>>>> >>>>>>
>>>>>> >>>>>> I think it is better to not quickly adopt the serialized IR
>>>>>> path until it is proven to work and there is sufficient tooling and support
>>>>>> around it, else it will end up being a constraint.
>>>>>> >>>>>>
>>>>>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>>>>>> some fundamental differences between their approaches, mainly around the
>>>>>> intermediate representation abstraction. Coral models both the AST and the
>>>>>> logical plan of a query, making it able to capture the query semantics more
>>>>>> accurately and hence perform precise transformations. On the flip side,
>>>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>>>> a major gap in any solution that does not capture the logical plan for
>>>>>> example, yet very important to perform successful translation. This is
>>>>>> backed up by some experiments we performed on actual queries and their
>>>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>>>> SQLGlot).
>>>>>> >>>>>>
>>>>>> >>>>>> For the IR: Any translation solution (including Coral) must
>>>>>> rely on an IR, and it has to be decoupled from any of the input and output
>>>>>> dialects. This is true in the Coral case today. Such IR is the way to
>>>>>> represent both the intermediate AST and logical plans. Therefore, I do not
>>>>>> think we can necessarily split projects as "IR projects" vs not, since all
>>>>>> solutions must use an IR. With that said, IR serialization is a matter of
>>>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>>>> and straightforward.
>>>>>> >>>>>>
>>>>>> >>>>>> [1]
>>>>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>>>> >>>>>>
>>>>>> >>>>>> Thanks,
>>>>>> >>>>>> Walaa.
>>>>>> >>>>>>
>>>>>> >>>>>>
>>>>>> >>>>>>
>>>>>>
>>>>>
>>
>> --
>> *Vladimir Ozerov*
>> Founder
>> querifylabs.com
>>
>
Re: [Discuss] Iceberg View Interoperability
Posted by Weston Pace <we...@gmail.com>.
Disclaimer: Also on Substrait SMC here and also have made some considerable
investment into Substrait in my professional work (Lance) so this is an not
unbiased opinion. I just want to give a few words on why I think an IR
(and specifically Substrait) is not just another dialect (I'll try and be
more concise than Jacques :P).
IRs have fewer dialects and less ambiguity than SQL strings. I suspect
this is mostly because they are written by machines and not humans. So if
interoperability is the goal I think an IR (Substrait or any other) is
going to provide an advantage over SQL. In Substrait, we have tried very
hard to be precise and thorough in our relation definitions. We have an
initial framework[1] in place for ensuring different producers/consumers
are translating the IR consistently with tests.
The vast majority of discrepancies (e.g. the difference between Hive and
Trino with regards to a / b) are actually discrepancies in function
semantics. Substrait is not opinionated here. Function semantics are
"extensions". We aim to be capable of expressing all semantics. Partly by
differently named functions (hive.org/divide vs. trino.org/divide) and
partly by introducing function options (substrait's reference
implementation of divide has an option for how nans are handled for
example). There is an entirely different framework in place for testing
function / dialect interoperability[2].
We are still very much in the early days and the current work here I think
is focused on cataloguing the various differences. My hope is that the
next step we can get producers to stop writing "bland substrait" (no
specification of dialect) and start writing "localized substrait"
(specifying exactly what dialect the producer expects). Then we can move
to consumers rejecting "localized substrait" when they do not understand
the dialect. Finally we can move towards a world with multi-dialect
consumers and/or dialect conversion tools.
This effort is definitely just getting started so this is a roadmap. As I
said, we're still beginning the "cataloguing" stage.
[1] https://github.com/substrait-io/consumer-testing
[2] https://github.com/substrait-io/bft
P.S. With specific regards to the plan "SELECT a / b" I can tell you that
there are 9 different special cases of division (0/0, 0/NAN, 0/INF, NAN/0,
NAN/NAN, NAN/INF, INF/0, INF/NAN, INF/INF) and it is extremely rare to find
two engines that agree on the behavior across those 9 cases (duckdb,
postgres, datafusion, for example, all disagree on at least one case).
P.P.S. The plan "SELECT sum(a)" is even more diabolical as it pulls
numerical precision and processing order into the mix (e.g. some engines
can give you two different answers on two different calls to the _same
engine_).
On Thu, Dec 5, 2024 at 5:32 AM Vladimir Ozerov <vo...@querifylabs.com>
wrote:
> Hi Walaa,
>
> Many of these questions do not have universally correct answers. SQL
> dialects and IRs are incompatible between engines. And even if we find a
> compatible IR, many of the simplest queries with the same IR will behave
> differently between engines, largely defeating the whole idea of view
> interoperability. E.g. "SELECT a / b" will work differently in Hive and
> Trino, and "SELECT sum(a)" will return different results in ClickHouse and
> Spark. Both queries require the simplest IR, yet it doesn't help.
>
> Substrait will not help resolve all these problems all at once. Like any
> protocol, it has some gaps, favors some non-ideal Calcite-inspired design
> decisions, etc. But if community decide to integrate Substrait, the gravity
> of Iceberg will **force** engine developers and the Substrait community to
> start polishing missing pieces and discrepancies because otherwise, vendors
> will simply start losing opportunities. And money is a wonderful motivator
> to start doing something that you otherwise postpone indefinitely :-)
>
> IMO, the community should not try to eliminate all possible unknowns
> beforehand (which pushes us towards the status quo) but instead take a leap
> of faith. Substrait has the real potential to pave the path towards
> reusable computations (be it views, matviews, predicate statistics, etc)
> for a large class of applications, because it was created to solve exactly
> this kind of problems.
>
> Regards,
> Vladimir.
>
> On Fri, Nov 29, 2024 at 12:00 AM Walaa Eldin Moustafa <
> wa.moustafa@gmail.com> wrote:
>
>> Hi Ajantha,
>>
>> I do not clearly see a consensus in this thread. If anything, I see this
>> thread posing more questions than answers. Here is the collection of
>> questions I could distill from the thread:
>>
>> ** What is the unique problem that is solved if Iceberg represents an IR
>> as opposed to representing a SQL dialect? We can keep in mind the following
>> when answering this question:*
>> ** An IR is a form of a dialect. Dialect is in text form. IR is in
>> structured form.
>> ** Engines typically use Dialect as their first class citizen. So
>> interoperability is typically between SQL dialects. (IR helps, but not
>> necessarily through "storing" it).
>> ** Both dialect and IR conversion require translation.
>> ** Both dialect and IR can be fully specified. For example, the SQL
>> Standard is based on some form of a SQL dialect, not a structured IR.
>>
>> ** If there are interesting applications of introducing an IR in addition
>> to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
>> IR", or should it be able to "represent IRs" in the same way it is able to
>> "represent dialects"?*
>>
>> ** If the answer is to adopt a single IR, what is the framework/criteria
>> to design or choose that IR?*
>> ** Is it serializability, expressibility, or translatability?
>> ** How do we score the IRs against this criteria?
>>
>> ** If the answer is to support representing multiple IRs, the type of
>> problems Iceberg would be concerned with will be different. We may have to
>> think about different types of questions in this case.*
>>
>> Thanks,
>> Walaa.
>>
>>
>> On Mon, Nov 4, 2024 at 8:40 AM Matt Topol <zo...@gmail.com> wrote:
>>
>>> For reference, there are two reasons why I chose to add that
>>> substrait.go:
>>>
>>> 1) The Golang Arrow implementation has a compute package which is able
>>> to evaluate substrait expressions as long as the kernels exist in the
>>> package.
>>>
>>> 2) Along the lines of this conversation, I wanted to be able to
>>> generically create Substrait expressions from iceberg expressions. With the
>>> goal being that the go implementation could potentially be able to create a
>>> full substrait plan (including the reading) from an iceberg table (and
>>> metadata) and expression. Eventually the plan would be able to be sent to a
>>> compute engine which wouldn't have to know anything about iceberg to
>>> execute it!
>>>
>>> On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
>>>
>>>> Matt also just added `substrait.go` to the Iceberg-Go implementation
>>>> that I was reviewing today:
>>>>
>>>> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>>>>
>>>> That converts an Iceberg expression into a substrate one, pretty
>>>> exciting stuff
>>>>
>>>> Kind regards,
>>>> Fokko
>>>>
>>>> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb@nanthrax.net
>>>> >:
>>>>
>>>>> Hi Ajantha,
>>>>>
>>>>> During CommunityOverCode, I chatted with Matt Topol about Substrait
>>>>> and ADBC.
>>>>> I checked the Substrait support in DataFusion and it's interesting.
>>>>>
>>>>> I was thinking about where to actually store the Substrait plan (I was
>>>>> thinking about an intermediate SQL representation that we could store
>>>>> as a metadata instead of directly the plan).
>>>>>
>>>>> Maybe, we could start with a proposal document to explore the
>>>>> different options (and so follow Iceberg proposals process, creating a
>>>>> GitHub Issue with the proposal tag, and attaching the document) ?
>>>>>
>>>>> Thanks !
>>>>> Regards
>>>>> JB
>>>>>
>>>>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>>>>> wrote:
>>>>> >
>>>>> > Thanks everyone for the detailed discussions.
>>>>> >
>>>>> > Looks like we have consensus towards Substrait.
>>>>> > Last time I checked it was not adopted by all the engines. But we
>>>>> can work towards the adoption as well.
>>>>> >
>>>>> > I will explore further on Substrait and come up with the design doc
>>>>> on the same.
>>>>> >
>>>>> > Thanks,
>>>>> > Ajantha
>>>>> >
>>>>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>>>>> wrote:
>>>>> >>
>>>>> >> Hey all,
>>>>> >>
>>>>> >> I'm +1 in efforts to make views more interoperable across engines
>>>>> as I believe such efforts would be beneficial for the wider ecosystem. I
>>>>> think the way to do that is through higher fidelity IRs such as Substrait.
>>>>> >>
>>>>> >> I agree with Walaa that there's not really a valid distinction
>>>>> between IR vs non-IR projects when it comes to translation; my
>>>>> understanding is that in the end any translation framework would have to
>>>>> normalize to an intermediate representation. With the SQLGlot case, it's
>>>>> just that the IR is at the AST level and with the others they have higher
>>>>> fidelity to capture more accurate query semantics (correct me if I'm wrong
>>>>> here). As of today, it is already possible to use SQLGlot, translate to the
>>>>> desired SQL and store these SQL representations. However, since it's not as
>>>>> high fidelity as a proper IR layer, there are issues to consider like Fokko
>>>>> mentioned; but again, if users are happy with their results, they can do
>>>>> this today without any spec changes.
>>>>> >>
>>>>> >> In my opinion, the biggest hurdle for Substrait or any other IR to
>>>>> be a viable standard in Iceberg that's worth maintaining is that there
>>>>> would need to be consensus across different engine/language communities
>>>>> (e.g. Walaa referenced the Trino community's perspective on such IR
>>>>> layers). Otherwise it risks becoming something that's defined in the
>>>>> standard but really isn't well accepted which I think we all want to avoid.
>>>>> >>
>>>>> >> I think as a starting point, it would be great to sync with at
>>>>> least OSS engines/language communities and try and understand any concrete
>>>>> points of skepticism for considering such a standard. So far a lot of the
>>>>> points of skepticism as I read it are around such a layer being only
>>>>> considerate of 1 engine or having such substantial feature gaps that it
>>>>> can't be considered; but no concrete cases have been called out.
>>>>> >> Once we establish concrete gaps, I think then it would make sense
>>>>> to work with the respective IR community to help close those gaps or if
>>>>> needed consider other paths.
>>>>> >>
>>>>> >> Thanks,
>>>>> >> Amogh Jahagirdar
>>>>> >>
>>>>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>>>>> piotr.findeisen@gmail.com> wrote:
>>>>> >>>
>>>>> >>> Hi,
>>>>> >>>
>>>>> >>> I have no experience with Substrait, but i agree it looks like the
>>>>> tool for the job.
>>>>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the
>>>>> views.
>>>>> >>>
>>>>> >>> We can experiment with serialized IR being stored as a String with
>>>>> new dialect name, and this is how we should get this started.
>>>>> >>> It's probably good end solution as well, but the important
>>>>> value-add is if we manage to converge towards one shared IR that's "native
>>>>> to iceberg".
>>>>> >>> This would be best for the users -- more views would just work.
>>>>> >>> And best for long-term evolution of the project -- standardized IR
>>>>> would help things like incremental refreshes (for materialized views).
>>>>> >>>
>>>>> >>> Best
>>>>> >>> Piotr
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>>
>>>>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>>>>> wa.moustafa@gmail.com> wrote:
>>>>> >>>>
>>>>> >>>> Hi Fokko,
>>>>> >>>>
>>>>> >>>> We can implement Python/Rust/Go clients to interop with the
>>>>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>>>>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>>>>> etc) be reimplemented in those languages. Such implementations actually
>>>>> depend on the reuse of the native parsers of those dialects which are
>>>>> typically in Java (also this is to your point about the language coverage
>>>>> -- reusing native parsers is a principle that Coral follows to be compliant
>>>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>>>> around) would be a good start. For example, Python-specific backends and
>>>>> front-end implementations can follow from that.
>>>>> >>>>
>>>>> >>>> Thanks,
>>>>> >>>> Walaa.
>>>>> >>>>
>>>>> >>>>
>>>>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <
>>>>> fokko@apache.org> wrote:
>>>>> >>>>>
>>>>> >>>>> Hey everyone,
>>>>> >>>>>
>>>>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly
>>>>> because tooling in Python tends to work with data frames, rather than SQL.
>>>>> I do think it would be valuable to extend support there.
>>>>> >>>>>
>>>>> >>>>> I have a bit of experience in turning SQL into ASTs and
>>>>> extending grammar, and I'm confident to say that it is nearly impossible to
>>>>> cover all the grammar of a specific dialect. My main question is, what will
>>>>> SQLGlot do when we try to translate a dialect that it doesn't fully
>>>>> understand? Will it error out, or will it produce faulty SQL? A simple
>>>>> example can be functions that are not supported in other engines up to
>>>>> recursive CTE's. In this case, not failing upfront would cause correctness
>>>>> issues.
>>>>> >>>>>
>>>>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>>>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>>>> actual execution. This was still an early stage and required a lot of work
>>>>> around credentials and field-IDs, but it was quite promising. Using
>>>>> Substrait as views looks easier to me, and would also translate to a
>>>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>>>> Python/Rust/Go support?
>>>>> >>>>>
>>>>> >>>>> Kind regards,
>>>>> >>>>> Fokko
>>>>> >>>>>
>>>>> >>>>>
>>>>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>>>> wa.moustafa@gmail.com>:
>>>>> >>>>>>
>>>>> >>>>>> I think this may need some more discussion.
>>>>> >>>>>>
>>>>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In
>>>>> this case, this dialect will be mostly specific to Iceberg, and compute
>>>>> engines will still support reading views in their native SQL. There are
>>>>> some data points on this from the Trino community in a previous discussion
>>>>> [1]. In addition to being not directly consumable by engines, a serialized
>>>>> IR will be hard to consume by humans too.
>>>>> >>>>>>
>>>>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>>>>> serialized IR, we will end up again doing translation, from that IR to the
>>>>> engine's dialect on view read time, and from the engine's dialect to that
>>>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>>>> >>>>>>
>>>>> >>>>>> I think it is better to not quickly adopt the serialized IR
>>>>> path until it is proven to work and there is sufficient tooling and support
>>>>> around it, else it will end up being a constraint.
>>>>> >>>>>>
>>>>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>>>>> some fundamental differences between their approaches, mainly around the
>>>>> intermediate representation abstraction. Coral models both the AST and the
>>>>> logical plan of a query, making it able to capture the query semantics more
>>>>> accurately and hence perform precise transformations. On the flip side,
>>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>>> a major gap in any solution that does not capture the logical plan for
>>>>> example, yet very important to perform successful translation. This is
>>>>> backed up by some experiments we performed on actual queries and their
>>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>>> SQLGlot).
>>>>> >>>>>>
>>>>> >>>>>> For the IR: Any translation solution (including Coral) must
>>>>> rely on an IR, and it has to be decoupled from any of the input and output
>>>>> dialects. This is true in the Coral case today. Such IR is the way to
>>>>> represent both the intermediate AST and logical plans. Therefore, I do not
>>>>> think we can necessarily split projects as "IR projects" vs not, since all
>>>>> solutions must use an IR. With that said, IR serialization is a matter of
>>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>>> and straightforward.
>>>>> >>>>>>
>>>>> >>>>>> [1]
>>>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>>> >>>>>>
>>>>> >>>>>> Thanks,
>>>>> >>>>>> Walaa.
>>>>> >>>>>>
>>>>> >>>>>>
>>>>> >>>>>>
>>>>>
>>>>
>
> --
> *Vladimir Ozerov*
> Founder
> querifylabs.com
>
Re: [Discuss] Iceberg View Interoperability
Posted by Vladimir Ozerov <vo...@querifylabs.com>.
Hi Walaa,
Many of these questions do not have universally correct answers. SQL
dialects and IRs are incompatible between engines. And even if we find a
compatible IR, many of the simplest queries with the same IR will behave
differently between engines, largely defeating the whole idea of view
interoperability. E.g. "SELECT a / b" will work differently in Hive and
Trino, and "SELECT sum(a)" will return different results in ClickHouse and
Spark. Both queries require the simplest IR, yet it doesn't help.
Substrait will not help resolve all these problems all at once. Like any
protocol, it has some gaps, favors some non-ideal Calcite-inspired design
decisions, etc. But if community decide to integrate Substrait, the gravity
of Iceberg will **force** engine developers and the Substrait community to
start polishing missing pieces and discrepancies because otherwise, vendors
will simply start losing opportunities. And money is a wonderful motivator
to start doing something that you otherwise postpone indefinitely :-)
IMO, the community should not try to eliminate all possible unknowns
beforehand (which pushes us towards the status quo) but instead take a leap
of faith. Substrait has the real potential to pave the path towards
reusable computations (be it views, matviews, predicate statistics, etc)
for a large class of applications, because it was created to solve exactly
this kind of problems.
Regards,
Vladimir.
On Fri, Nov 29, 2024 at 12:00 AM Walaa Eldin Moustafa <wa...@gmail.com>
wrote:
> Hi Ajantha,
>
> I do not clearly see a consensus in this thread. If anything, I see this
> thread posing more questions than answers. Here is the collection of
> questions I could distill from the thread:
>
> ** What is the unique problem that is solved if Iceberg represents an IR
> as opposed to representing a SQL dialect? We can keep in mind the following
> when answering this question:*
> ** An IR is a form of a dialect. Dialect is in text form. IR is in
> structured form.
> ** Engines typically use Dialect as their first class citizen. So
> interoperability is typically between SQL dialects. (IR helps, but not
> necessarily through "storing" it).
> ** Both dialect and IR conversion require translation.
> ** Both dialect and IR can be fully specified. For example, the SQL
> Standard is based on some form of a SQL dialect, not a structured IR.
>
> ** If there are interesting applications of introducing an IR in addition
> to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
> IR", or should it be able to "represent IRs" in the same way it is able to
> "represent dialects"?*
>
> ** If the answer is to adopt a single IR, what is the framework/criteria
> to design or choose that IR?*
> ** Is it serializability, expressibility, or translatability?
> ** How do we score the IRs against this criteria?
>
> ** If the answer is to support representing multiple IRs, the type of
> problems Iceberg would be concerned with will be different. We may have to
> think about different types of questions in this case.*
>
> Thanks,
> Walaa.
>
>
> On Mon, Nov 4, 2024 at 8:40 AM Matt Topol <zo...@gmail.com> wrote:
>
>> For reference, there are two reasons why I chose to add that substrait.go:
>>
>> 1) The Golang Arrow implementation has a compute package which is able to
>> evaluate substrait expressions as long as the kernels exist in the package.
>>
>> 2) Along the lines of this conversation, I wanted to be able to
>> generically create Substrait expressions from iceberg expressions. With the
>> goal being that the go implementation could potentially be able to create a
>> full substrait plan (including the reading) from an iceberg table (and
>> metadata) and expression. Eventually the plan would be able to be sent to a
>> compute engine which wouldn't have to know anything about iceberg to
>> execute it!
>>
>> On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
>>
>>> Matt also just added `substrait.go` to the Iceberg-Go implementation
>>> that I was reviewing today:
>>>
>>> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>>>
>>> That converts an Iceberg expression into a substrate one, pretty
>>> exciting stuff
>>>
>>> Kind regards,
>>> Fokko
>>>
>>> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb@nanthrax.net
>>> >:
>>>
>>>> Hi Ajantha,
>>>>
>>>> During CommunityOverCode, I chatted with Matt Topol about Substrait and
>>>> ADBC.
>>>> I checked the Substrait support in DataFusion and it's interesting.
>>>>
>>>> I was thinking about where to actually store the Substrait plan (I was
>>>> thinking about an intermediate SQL representation that we could store
>>>> as a metadata instead of directly the plan).
>>>>
>>>> Maybe, we could start with a proposal document to explore the
>>>> different options (and so follow Iceberg proposals process, creating a
>>>> GitHub Issue with the proposal tag, and attaching the document) ?
>>>>
>>>> Thanks !
>>>> Regards
>>>> JB
>>>>
>>>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>>>> wrote:
>>>> >
>>>> > Thanks everyone for the detailed discussions.
>>>> >
>>>> > Looks like we have consensus towards Substrait.
>>>> > Last time I checked it was not adopted by all the engines. But we can
>>>> work towards the adoption as well.
>>>> >
>>>> > I will explore further on Substrait and come up with the design doc
>>>> on the same.
>>>> >
>>>> > Thanks,
>>>> > Ajantha
>>>> >
>>>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>>>> wrote:
>>>> >>
>>>> >> Hey all,
>>>> >>
>>>> >> I'm +1 in efforts to make views more interoperable across engines as
>>>> I believe such efforts would be beneficial for the wider ecosystem. I think
>>>> the way to do that is through higher fidelity IRs such as Substrait.
>>>> >>
>>>> >> I agree with Walaa that there's not really a valid distinction
>>>> between IR vs non-IR projects when it comes to translation; my
>>>> understanding is that in the end any translation framework would have to
>>>> normalize to an intermediate representation. With the SQLGlot case, it's
>>>> just that the IR is at the AST level and with the others they have higher
>>>> fidelity to capture more accurate query semantics (correct me if I'm wrong
>>>> here). As of today, it is already possible to use SQLGlot, translate to the
>>>> desired SQL and store these SQL representations. However, since it's not as
>>>> high fidelity as a proper IR layer, there are issues to consider like Fokko
>>>> mentioned; but again, if users are happy with their results, they can do
>>>> this today without any spec changes.
>>>> >>
>>>> >> In my opinion, the biggest hurdle for Substrait or any other IR to
>>>> be a viable standard in Iceberg that's worth maintaining is that there
>>>> would need to be consensus across different engine/language communities
>>>> (e.g. Walaa referenced the Trino community's perspective on such IR
>>>> layers). Otherwise it risks becoming something that's defined in the
>>>> standard but really isn't well accepted which I think we all want to avoid.
>>>> >>
>>>> >> I think as a starting point, it would be great to sync with at least
>>>> OSS engines/language communities and try and understand any concrete points
>>>> of skepticism for considering such a standard. So far a lot of the points
>>>> of skepticism as I read it are around such a layer being only considerate
>>>> of 1 engine or having such substantial feature gaps that it can't be
>>>> considered; but no concrete cases have been called out.
>>>> >> Once we establish concrete gaps, I think then it would make sense to
>>>> work with the respective IR community to help close those gaps or if needed
>>>> consider other paths.
>>>> >>
>>>> >> Thanks,
>>>> >> Amogh Jahagirdar
>>>> >>
>>>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>>>> piotr.findeisen@gmail.com> wrote:
>>>> >>>
>>>> >>> Hi,
>>>> >>>
>>>> >>> I have no experience with Substrait, but i agree it looks like the
>>>> tool for the job.
>>>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the
>>>> views.
>>>> >>>
>>>> >>> We can experiment with serialized IR being stored as a String with
>>>> new dialect name, and this is how we should get this started.
>>>> >>> It's probably good end solution as well, but the important
>>>> value-add is if we manage to converge towards one shared IR that's "native
>>>> to iceberg".
>>>> >>> This would be best for the users -- more views would just work.
>>>> >>> And best for long-term evolution of the project -- standardized IR
>>>> would help things like incremental refreshes (for materialized views).
>>>> >>>
>>>> >>> Best
>>>> >>> Piotr
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>>>> wa.moustafa@gmail.com> wrote:
>>>> >>>>
>>>> >>>> Hi Fokko,
>>>> >>>>
>>>> >>>> We can implement Python/Rust/Go clients to interop with the
>>>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>>>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>>>> etc) be reimplemented in those languages. Such implementations actually
>>>> depend on the reuse of the native parsers of those dialects which are
>>>> typically in Java (also this is to your point about the language coverage
>>>> -- reusing native parsers is a principle that Coral follows to be compliant
>>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>>> around) would be a good start. For example, Python-specific backends and
>>>> front-end implementations can follow from that.
>>>> >>>>
>>>> >>>> Thanks,
>>>> >>>> Walaa.
>>>> >>>>
>>>> >>>>
>>>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
>>>> wrote:
>>>> >>>>>
>>>> >>>>> Hey everyone,
>>>> >>>>>
>>>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly
>>>> because tooling in Python tends to work with data frames, rather than SQL.
>>>> I do think it would be valuable to extend support there.
>>>> >>>>>
>>>> >>>>> I have a bit of experience in turning SQL into ASTs and extending
>>>> grammar, and I'm confident to say that it is nearly impossible to cover all
>>>> the grammar of a specific dialect. My main question is, what will SQLGlot
>>>> do when we try to translate a dialect that it doesn't fully understand?
>>>> Will it error out, or will it produce faulty SQL? A simple example can be
>>>> functions that are not supported in other engines up to recursive CTE's. In
>>>> this case, not failing upfront would cause correctness issues.
>>>> >>>>>
>>>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>>> actual execution. This was still an early stage and required a lot of work
>>>> around credentials and field-IDs, but it was quite promising. Using
>>>> Substrait as views looks easier to me, and would also translate to a
>>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>>> Python/Rust/Go support?
>>>> >>>>>
>>>> >>>>> Kind regards,
>>>> >>>>> Fokko
>>>> >>>>>
>>>> >>>>>
>>>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>>> wa.moustafa@gmail.com>:
>>>> >>>>>>
>>>> >>>>>> I think this may need some more discussion.
>>>> >>>>>>
>>>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In this
>>>> case, this dialect will be mostly specific to Iceberg, and compute engines
>>>> will still support reading views in their native SQL. There are some data
>>>> points on this from the Trino community in a previous discussion [1]. In
>>>> addition to being not directly consumable by engines, a serialized IR will
>>>> be hard to consume by humans too.
>>>> >>>>>>
>>>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>>>> serialized IR, we will end up again doing translation, from that IR to the
>>>> engine's dialect on view read time, and from the engine's dialect to that
>>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>>> >>>>>>
>>>> >>>>>> I think it is better to not quickly adopt the serialized IR path
>>>> until it is proven to work and there is sufficient tooling and support
>>>> around it, else it will end up being a constraint.
>>>> >>>>>>
>>>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>>>> some fundamental differences between their approaches, mainly around the
>>>> intermediate representation abstraction. Coral models both the AST and the
>>>> logical plan of a query, making it able to capture the query semantics more
>>>> accurately and hence perform precise transformations. On the flip side,
>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>> a major gap in any solution that does not capture the logical plan for
>>>> example, yet very important to perform successful translation. This is
>>>> backed up by some experiments we performed on actual queries and their
>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>> SQLGlot).
>>>> >>>>>>
>>>> >>>>>> For the IR: Any translation solution (including Coral) must rely
>>>> on an IR, and it has to be decoupled from any of the input and output
>>>> dialects. This is true in the Coral case today. Such IR is the way to
>>>> represent both the intermediate AST and logical plans. Therefore, I do not
>>>> think we can necessarily split projects as "IR projects" vs not, since all
>>>> solutions must use an IR. With that said, IR serialization is a matter of
>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>> and straightforward.
>>>> >>>>>>
>>>> >>>>>> [1]
>>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>> >>>>>>
>>>> >>>>>> Thanks,
>>>> >>>>>> Walaa.
>>>> >>>>>>
>>>> >>>>>>
>>>> >>>>>>
>>>>
>>>
--
*Vladimir Ozerov*
Founder
querifylabs.com
Re: [Discuss] Iceberg View Interoperability
Posted by Walaa Eldin Moustafa <wa...@gmail.com>.
Hi Ajantha,
I do not clearly see a consensus in this thread. If anything, I see this
thread posing more questions than answers. Here is the collection of
questions I could distill from the thread:
** What is the unique problem that is solved if Iceberg represents an IR as
opposed to representing a SQL dialect? We can keep in mind the following
when answering this question:*
** An IR is a form of a dialect. Dialect is in text form. IR is in
structured form.
** Engines typically use Dialect as their first class citizen. So
interoperability is typically between SQL dialects. (IR helps, but not
necessarily through "storing" it).
** Both dialect and IR conversion require translation.
** Both dialect and IR can be fully specified. For example, the SQL
Standard is based on some form of a SQL dialect, not a structured IR.
** If there are interesting applications of introducing an IR in addition
to dialects, should Iceberg adopt only one IR as the canonical "Iceberg
IR", or should it be able to "represent IRs" in the same way it is able to
"represent dialects"?*
** If the answer is to adopt a single IR, what is the framework/criteria to
design or choose that IR?*
** Is it serializability, expressibility, or translatability?
** How do we score the IRs against this criteria?
** If the answer is to support representing multiple IRs, the type of
problems Iceberg would be concerned with will be different. We may have to
think about different types of questions in this case.*
Thanks,
Walaa.
On Mon, Nov 4, 2024 at 8:40 AM Matt Topol <zo...@gmail.com> wrote:
> For reference, there are two reasons why I chose to add that substrait.go:
>
> 1) The Golang Arrow implementation has a compute package which is able to
> evaluate substrait expressions as long as the kernels exist in the package.
>
> 2) Along the lines of this conversation, I wanted to be able to
> generically create Substrait expressions from iceberg expressions. With the
> goal being that the go implementation could potentially be able to create a
> full substrait plan (including the reading) from an iceberg table (and
> metadata) and expression. Eventually the plan would be able to be sent to a
> compute engine which wouldn't have to know anything about iceberg to
> execute it!
>
> On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
>
>> Matt also just added `substrait.go` to the Iceberg-Go implementation that
>> I was reviewing today:
>>
>> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>>
>> That converts an Iceberg expression into a substrate one, pretty exciting
>> stuff
>>
>> Kind regards,
>> Fokko
>>
>> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb...@nanthrax.net>:
>>
>>> Hi Ajantha,
>>>
>>> During CommunityOverCode, I chatted with Matt Topol about Substrait and
>>> ADBC.
>>> I checked the Substrait support in DataFusion and it's interesting.
>>>
>>> I was thinking about where to actually store the Substrait plan (I was
>>> thinking about an intermediate SQL representation that we could store
>>> as a metadata instead of directly the plan).
>>>
>>> Maybe, we could start with a proposal document to explore the
>>> different options (and so follow Iceberg proposals process, creating a
>>> GitHub Issue with the proposal tag, and attaching the document) ?
>>>
>>> Thanks !
>>> Regards
>>> JB
>>>
>>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>>> wrote:
>>> >
>>> > Thanks everyone for the detailed discussions.
>>> >
>>> > Looks like we have consensus towards Substrait.
>>> > Last time I checked it was not adopted by all the engines. But we can
>>> work towards the adoption as well.
>>> >
>>> > I will explore further on Substrait and come up with the design doc on
>>> the same.
>>> >
>>> > Thanks,
>>> > Ajantha
>>> >
>>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>>> wrote:
>>> >>
>>> >> Hey all,
>>> >>
>>> >> I'm +1 in efforts to make views more interoperable across engines as
>>> I believe such efforts would be beneficial for the wider ecosystem. I think
>>> the way to do that is through higher fidelity IRs such as Substrait.
>>> >>
>>> >> I agree with Walaa that there's not really a valid distinction
>>> between IR vs non-IR projects when it comes to translation; my
>>> understanding is that in the end any translation framework would have to
>>> normalize to an intermediate representation. With the SQLGlot case, it's
>>> just that the IR is at the AST level and with the others they have higher
>>> fidelity to capture more accurate query semantics (correct me if I'm wrong
>>> here). As of today, it is already possible to use SQLGlot, translate to the
>>> desired SQL and store these SQL representations. However, since it's not as
>>> high fidelity as a proper IR layer, there are issues to consider like Fokko
>>> mentioned; but again, if users are happy with their results, they can do
>>> this today without any spec changes.
>>> >>
>>> >> In my opinion, the biggest hurdle for Substrait or any other IR to be
>>> a viable standard in Iceberg that's worth maintaining is that there would
>>> need to be consensus across different engine/language communities (e.g.
>>> Walaa referenced the Trino community's perspective on such IR layers).
>>> Otherwise it risks becoming something that's defined in the standard but
>>> really isn't well accepted which I think we all want to avoid.
>>> >>
>>> >> I think as a starting point, it would be great to sync with at least
>>> OSS engines/language communities and try and understand any concrete points
>>> of skepticism for considering such a standard. So far a lot of the points
>>> of skepticism as I read it are around such a layer being only considerate
>>> of 1 engine or having such substantial feature gaps that it can't be
>>> considered; but no concrete cases have been called out.
>>> >> Once we establish concrete gaps, I think then it would make sense to
>>> work with the respective IR community to help close those gaps or if needed
>>> consider other paths.
>>> >>
>>> >> Thanks,
>>> >> Amogh Jahagirdar
>>> >>
>>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>>> piotr.findeisen@gmail.com> wrote:
>>> >>>
>>> >>> Hi,
>>> >>>
>>> >>> I have no experience with Substrait, but i agree it looks like the
>>> tool for the job.
>>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the
>>> views.
>>> >>>
>>> >>> We can experiment with serialized IR being stored as a String with
>>> new dialect name, and this is how we should get this started.
>>> >>> It's probably good end solution as well, but the important value-add
>>> is if we manage to converge towards one shared IR that's "native to
>>> iceberg".
>>> >>> This would be best for the users -- more views would just work.
>>> >>> And best for long-term evolution of the project -- standardized IR
>>> would help things like incremental refreshes (for materialized views).
>>> >>>
>>> >>> Best
>>> >>> Piotr
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>>> wa.moustafa@gmail.com> wrote:
>>> >>>>
>>> >>>> Hi Fokko,
>>> >>>>
>>> >>>> We can implement Python/Rust/Go clients to interop with the
>>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>>> etc) be reimplemented in those languages. Such implementations actually
>>> depend on the reuse of the native parsers of those dialects which are
>>> typically in Java (also this is to your point about the language coverage
>>> -- reusing native parsers is a principle that Coral follows to be compliant
>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>> around) would be a good start. For example, Python-specific backends and
>>> front-end implementations can follow from that.
>>> >>>>
>>> >>>> Thanks,
>>> >>>> Walaa.
>>> >>>>
>>> >>>>
>>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
>>> wrote:
>>> >>>>>
>>> >>>>> Hey everyone,
>>> >>>>>
>>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly
>>> because tooling in Python tends to work with data frames, rather than SQL.
>>> I do think it would be valuable to extend support there.
>>> >>>>>
>>> >>>>> I have a bit of experience in turning SQL into ASTs and extending
>>> grammar, and I'm confident to say that it is nearly impossible to cover all
>>> the grammar of a specific dialect. My main question is, what will SQLGlot
>>> do when we try to translate a dialect that it doesn't fully understand?
>>> Will it error out, or will it produce faulty SQL? A simple example can be
>>> functions that are not supported in other engines up to recursive CTE's. In
>>> this case, not failing upfront would cause correctness issues.
>>> >>>>>
>>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>> actual execution. This was still an early stage and required a lot of work
>>> around credentials and field-IDs, but it was quite promising. Using
>>> Substrait as views looks easier to me, and would also translate to a
>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>> Python/Rust/Go support?
>>> >>>>>
>>> >>>>> Kind regards,
>>> >>>>> Fokko
>>> >>>>>
>>> >>>>>
>>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>> wa.moustafa@gmail.com>:
>>> >>>>>>
>>> >>>>>> I think this may need some more discussion.
>>> >>>>>>
>>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In this
>>> case, this dialect will be mostly specific to Iceberg, and compute engines
>>> will still support reading views in their native SQL. There are some data
>>> points on this from the Trino community in a previous discussion [1]. In
>>> addition to being not directly consumable by engines, a serialized IR will
>>> be hard to consume by humans too.
>>> >>>>>>
>>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>>> serialized IR, we will end up again doing translation, from that IR to the
>>> engine's dialect on view read time, and from the engine's dialect to that
>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>> >>>>>>
>>> >>>>>> I think it is better to not quickly adopt the serialized IR path
>>> until it is proven to work and there is sufficient tooling and support
>>> around it, else it will end up being a constraint.
>>> >>>>>>
>>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>>> some fundamental differences between their approaches, mainly around the
>>> intermediate representation abstraction. Coral models both the AST and the
>>> logical plan of a query, making it able to capture the query semantics more
>>> accurately and hence perform precise transformations. On the flip side,
>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>> a major gap in any solution that does not capture the logical plan for
>>> example, yet very important to perform successful translation. This is
>>> backed up by some experiments we performed on actual queries and their
>>> translation results (from Spark to Trino, comparing results of Coral and
>>> SQLGlot).
>>> >>>>>>
>>> >>>>>> For the IR: Any translation solution (including Coral) must rely
>>> on an IR, and it has to be decoupled from any of the input and output
>>> dialects. This is true in the Coral case today. Such IR is the way to
>>> represent both the intermediate AST and logical plans. Therefore, I do not
>>> think we can necessarily split projects as "IR projects" vs not, since all
>>> solutions must use an IR. With that said, IR serialization is a matter of
>>> staging/milestones of the project. Serialized IR is next on Coral's
>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>> and straightforward.
>>> >>>>>>
>>> >>>>>> [1]
>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>> >>>>>>
>>> >>>>>> Thanks,
>>> >>>>>> Walaa.
>>> >>>>>>
>>> >>>>>>
>>> >>>>>>
>>>
>>
Re: [Discuss] Iceberg View Interoperability
Posted by Matt Topol <zo...@gmail.com>.
For reference, there are two reasons why I chose to add that substrait.go:
1) The Golang Arrow implementation has a compute package which is able to
evaluate substrait expressions as long as the kernels exist in the package.
2) Along the lines of this conversation, I wanted to be able to generically
create Substrait expressions from iceberg expressions. With the goal being
that the go implementation could potentially be able to create a full
substrait plan (including the reading) from an iceberg table (and metadata)
and expression. Eventually the plan would be able to be sent to a compute
engine which wouldn't have to know anything about iceberg to execute it!
On Mon, Nov 4, 2024, 5:34 PM Fokko Driesprong <fo...@apache.org> wrote:
> Matt also just added `substrait.go` to the Iceberg-Go implementation that
> I was reviewing today:
>
> https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
>
> That converts an Iceberg expression into a substrate one, pretty exciting
> stuff
>
> Kind regards,
> Fokko
>
> Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb...@nanthrax.net>:
>
>> Hi Ajantha,
>>
>> During CommunityOverCode, I chatted with Matt Topol about Substrait and
>> ADBC.
>> I checked the Substrait support in DataFusion and it's interesting.
>>
>> I was thinking about where to actually store the Substrait plan (I was
>> thinking about an intermediate SQL representation that we could store
>> as a metadata instead of directly the plan).
>>
>> Maybe, we could start with a proposal document to explore the
>> different options (and so follow Iceberg proposals process, creating a
>> GitHub Issue with the proposal tag, and attaching the document) ?
>>
>> Thanks !
>> Regards
>> JB
>>
>> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
>> wrote:
>> >
>> > Thanks everyone for the detailed discussions.
>> >
>> > Looks like we have consensus towards Substrait.
>> > Last time I checked it was not adopted by all the engines. But we can
>> work towards the adoption as well.
>> >
>> > I will explore further on Substrait and come up with the design doc on
>> the same.
>> >
>> > Thanks,
>> > Ajantha
>> >
>> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
>> wrote:
>> >>
>> >> Hey all,
>> >>
>> >> I'm +1 in efforts to make views more interoperable across engines as I
>> believe such efforts would be beneficial for the wider ecosystem. I think
>> the way to do that is through higher fidelity IRs such as Substrait.
>> >>
>> >> I agree with Walaa that there's not really a valid distinction between
>> IR vs non-IR projects when it comes to translation; my understanding is
>> that in the end any translation framework would have to normalize to an
>> intermediate representation. With the SQLGlot case, it's just that the IR
>> is at the AST level and with the others they have higher fidelity to
>> capture more accurate query semantics (correct me if I'm wrong here). As of
>> today, it is already possible to use SQLGlot, translate to the desired SQL
>> and store these SQL representations. However, since it's not as high
>> fidelity as a proper IR layer, there are issues to consider like Fokko
>> mentioned; but again, if users are happy with their results, they can do
>> this today without any spec changes.
>> >>
>> >> In my opinion, the biggest hurdle for Substrait or any other IR to be
>> a viable standard in Iceberg that's worth maintaining is that there would
>> need to be consensus across different engine/language communities (e.g.
>> Walaa referenced the Trino community's perspective on such IR layers).
>> Otherwise it risks becoming something that's defined in the standard but
>> really isn't well accepted which I think we all want to avoid.
>> >>
>> >> I think as a starting point, it would be great to sync with at least
>> OSS engines/language communities and try and understand any concrete points
>> of skepticism for considering such a standard. So far a lot of the points
>> of skepticism as I read it are around such a layer being only considerate
>> of 1 engine or having such substantial feature gaps that it can't be
>> considered; but no concrete cases have been called out.
>> >> Once we establish concrete gaps, I think then it would make sense to
>> work with the respective IR community to help close those gaps or if needed
>> consider other paths.
>> >>
>> >> Thanks,
>> >> Amogh Jahagirdar
>> >>
>> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
>> piotr.findeisen@gmail.com> wrote:
>> >>>
>> >>> Hi,
>> >>>
>> >>> I have no experience with Substrait, but i agree it looks like the
>> tool for the job.
>> >>> Or, as I proposed earlier, we define our own Iceberg IR for the views.
>> >>>
>> >>> We can experiment with serialized IR being stored as a String with
>> new dialect name, and this is how we should get this started.
>> >>> It's probably good end solution as well, but the important value-add
>> is if we manage to converge towards one shared IR that's "native to
>> iceberg".
>> >>> This would be best for the users -- more views would just work.
>> >>> And best for long-term evolution of the project -- standardized IR
>> would help things like incremental refreshes (for materialized views).
>> >>>
>> >>> Best
>> >>> Piotr
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
>> wa.moustafa@gmail.com> wrote:
>> >>>>
>> >>>> Hi Fokko,
>> >>>>
>> >>>> We can implement Python/Rust/Go clients to interop with the
>> serialized Coral IR. Not sure if it makes sense to have all front-end and
>> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
>> etc) be reimplemented in those languages. Such implementations actually
>> depend on the reuse of the native parsers of those dialects which are
>> typically in Java (also this is to your point about the language coverage
>> -- reusing native parsers is a principle that Coral follows to be compliant
>> with the source dialect). I think making Python/Rust/Go interop/handle the
>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>> around) would be a good start. For example, Python-specific backends and
>> front-end implementations can follow from that.
>> >>>>
>> >>>> Thanks,
>> >>>> Walaa.
>> >>>>
>> >>>>
>> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
>> wrote:
>> >>>>>
>> >>>>> Hey everyone,
>> >>>>>
>> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly because
>> tooling in Python tends to work with data frames, rather than SQL. I do
>> think it would be valuable to extend support there.
>> >>>>>
>> >>>>> I have a bit of experience in turning SQL into ASTs and extending
>> grammar, and I'm confident to say that it is nearly impossible to cover all
>> the grammar of a specific dialect. My main question is, what will SQLGlot
>> do when we try to translate a dialect that it doesn't fully understand?
>> Will it error out, or will it produce faulty SQL? A simple example can be
>> functions that are not supported in other engines up to recursive CTE's. In
>> this case, not failing upfront would cause correctness issues.
>> >>>>>
>> >>>>> Regarding Substrait. Within PyIceberg there was also successful
>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>> actual execution. This was still an early stage and required a lot of work
>> around credentials and field-IDs, but it was quite promising. Using
>> Substrait as views looks easier to me, and would also translate to a
>> dataframe-based world. Walaa, do you have any outlook on Coral
>> Python/Rust/Go support?
>> >>>>>
>> >>>>> Kind regards,
>> >>>>> Fokko
>> >>>>>
>> >>>>>
>> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>> wa.moustafa@gmail.com>:
>> >>>>>>
>> >>>>>> I think this may need some more discussion.
>> >>>>>>
>> >>>>>> To me, a "serialized IR" is another form of a "dialect". In this
>> case, this dialect will be mostly specific to Iceberg, and compute engines
>> will still support reading views in their native SQL. There are some data
>> points on this from the Trino community in a previous discussion [1]. In
>> addition to being not directly consumable by engines, a serialized IR will
>> be hard to consume by humans too.
>> >>>>>>
>> >>>>>> From that perspective, even if Iceberg adopts some form of a
>> serialized IR, we will end up again doing translation, from that IR to the
>> engine's dialect on view read time, and from the engine's dialect to that
>> IR on the view write time. So serialized IR cannot eliminate translation.
>> >>>>>>
>> >>>>>> I think it is better to not quickly adopt the serialized IR path
>> until it is proven to work and there is sufficient tooling and support
>> around it, else it will end up being a constraint.
>> >>>>>>
>> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are
>> some fundamental differences between their approaches, mainly around the
>> intermediate representation abstraction. Coral models both the AST and the
>> logical plan of a query, making it able to capture the query semantics more
>> accurately and hence perform precise transformations. On the flip side,
>> SQLGlot abstraction is at the AST level only. Data type inference would be
>> a major gap in any solution that does not capture the logical plan for
>> example, yet very important to perform successful translation. This is
>> backed up by some experiments we performed on actual queries and their
>> translation results (from Spark to Trino, comparing results of Coral and
>> SQLGlot).
>> >>>>>>
>> >>>>>> For the IR: Any translation solution (including Coral) must rely
>> on an IR, and it has to be decoupled from any of the input and output
>> dialects. This is true in the Coral case today. Such IR is the way to
>> represent both the intermediate AST and logical plans. Therefore, I do not
>> think we can necessarily split projects as "IR projects" vs not, since all
>> solutions must use an IR. With that said, IR serialization is a matter of
>> staging/milestones of the project. Serialized IR is next on Coral's
>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>> compatibility with engines that adopt Coral (like Trino) much more robust
>> and straightforward.
>> >>>>>>
>> >>>>>> [1]
>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>> >>>>>>
>> >>>>>> Thanks,
>> >>>>>> Walaa.
>> >>>>>>
>> >>>>>>
>> >>>>>>
>>
>
Re: [Discuss] Iceberg View Interoperability
Posted by Fokko Driesprong <fo...@apache.org>.
Matt also just added `substrait.go` to the Iceberg-Go implementation that I
was reviewing today:
https://github.com/apache/iceberg-go/pull/185/files#diff-81cfac9f2e1dcf6265c569d0a3397964f0b78e07f45bb9dcdd3effe0623aaf73
That converts an Iceberg expression into a substrate one, pretty exciting
stuff
Kind regards,
Fokko
Op ma 4 nov 2024 om 14:03 schreef Jean-Baptiste Onofré <jb...@nanthrax.net>:
> Hi Ajantha,
>
> During CommunityOverCode, I chatted with Matt Topol about Substrait and
> ADBC.
> I checked the Substrait support in DataFusion and it's interesting.
>
> I was thinking about where to actually store the Substrait plan (I was
> thinking about an intermediate SQL representation that we could store
> as a metadata instead of directly the plan).
>
> Maybe, we could start with a proposal document to explore the
> different options (and so follow Iceberg proposals process, creating a
> GitHub Issue with the proposal tag, and attaching the document) ?
>
> Thanks !
> Regards
> JB
>
> On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com>
> wrote:
> >
> > Thanks everyone for the detailed discussions.
> >
> > Looks like we have consensus towards Substrait.
> > Last time I checked it was not adopted by all the engines. But we can
> work towards the adoption as well.
> >
> > I will explore further on Substrait and come up with the design doc on
> the same.
> >
> > Thanks,
> > Ajantha
> >
> > On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com>
> wrote:
> >>
> >> Hey all,
> >>
> >> I'm +1 in efforts to make views more interoperable across engines as I
> believe such efforts would be beneficial for the wider ecosystem. I think
> the way to do that is through higher fidelity IRs such as Substrait.
> >>
> >> I agree with Walaa that there's not really a valid distinction between
> IR vs non-IR projects when it comes to translation; my understanding is
> that in the end any translation framework would have to normalize to an
> intermediate representation. With the SQLGlot case, it's just that the IR
> is at the AST level and with the others they have higher fidelity to
> capture more accurate query semantics (correct me if I'm wrong here). As of
> today, it is already possible to use SQLGlot, translate to the desired SQL
> and store these SQL representations. However, since it's not as high
> fidelity as a proper IR layer, there are issues to consider like Fokko
> mentioned; but again, if users are happy with their results, they can do
> this today without any spec changes.
> >>
> >> In my opinion, the biggest hurdle for Substrait or any other IR to be a
> viable standard in Iceberg that's worth maintaining is that there would
> need to be consensus across different engine/language communities (e.g.
> Walaa referenced the Trino community's perspective on such IR layers).
> Otherwise it risks becoming something that's defined in the standard but
> really isn't well accepted which I think we all want to avoid.
> >>
> >> I think as a starting point, it would be great to sync with at least
> OSS engines/language communities and try and understand any concrete points
> of skepticism for considering such a standard. So far a lot of the points
> of skepticism as I read it are around such a layer being only considerate
> of 1 engine or having such substantial feature gaps that it can't be
> considered; but no concrete cases have been called out.
> >> Once we establish concrete gaps, I think then it would make sense to
> work with the respective IR community to help close those gaps or if needed
> consider other paths.
> >>
> >> Thanks,
> >> Amogh Jahagirdar
> >>
> >> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
> piotr.findeisen@gmail.com> wrote:
> >>>
> >>> Hi,
> >>>
> >>> I have no experience with Substrait, but i agree it looks like the
> tool for the job.
> >>> Or, as I proposed earlier, we define our own Iceberg IR for the views.
> >>>
> >>> We can experiment with serialized IR being stored as a String with new
> dialect name, and this is how we should get this started.
> >>> It's probably good end solution as well, but the important value-add
> is if we manage to converge towards one shared IR that's "native to
> iceberg".
> >>> This would be best for the users -- more views would just work.
> >>> And best for long-term evolution of the project -- standardized IR
> would help things like incremental refreshes (for materialized views).
> >>>
> >>> Best
> >>> Piotr
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <
> wa.moustafa@gmail.com> wrote:
> >>>>
> >>>> Hi Fokko,
> >>>>
> >>>> We can implement Python/Rust/Go clients to interop with the
> serialized Coral IR. Not sure if it makes sense to have all front-end and
> back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino,
> etc) be reimplemented in those languages. Such implementations actually
> depend on the reuse of the native parsers of those dialects which are
> typically in Java (also this is to your point about the language coverage
> -- reusing native parsers is a principle that Coral follows to be compliant
> with the source dialect). I think making Python/Rust/Go interop/handle the
> IR (i.e., convert the serialized IR to in-memory IR and the other way
> around) would be a good start. For example, Python-specific backends and
> front-end implementations can follow from that.
> >>>>
> >>>> Thanks,
> >>>> Walaa.
> >>>>
> >>>>
> >>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
> wrote:
> >>>>>
> >>>>> Hey everyone,
> >>>>>
> >>>>> Views in PyIceberg are not yet as mature as in Java, mostly because
> tooling in Python tends to work with data frames, rather than SQL. I do
> think it would be valuable to extend support there.
> >>>>>
> >>>>> I have a bit of experience in turning SQL into ASTs and extending
> grammar, and I'm confident to say that it is nearly impossible to cover all
> the grammar of a specific dialect. My main question is, what will SQLGlot
> do when we try to translate a dialect that it doesn't fully understand?
> Will it error out, or will it produce faulty SQL? A simple example can be
> functions that are not supported in other engines up to recursive CTE's. In
> this case, not failing upfront would cause correctness issues.
> >>>>>
> >>>>> Regarding Substrait. Within PyIceberg there was also successful
> experimentation of having a DuckDB query, sending it to PyIceberg to do the
> Iceberg query planning, and returning a physical plan to DuckDB to do the
> actual execution. This was still an early stage and required a lot of work
> around credentials and field-IDs, but it was quite promising. Using
> Substrait as views looks easier to me, and would also translate to a
> dataframe-based world. Walaa, do you have any outlook on Coral
> Python/Rust/Go support?
> >>>>>
> >>>>> Kind regards,
> >>>>> Fokko
> >>>>>
> >>>>>
> >>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
> wa.moustafa@gmail.com>:
> >>>>>>
> >>>>>> I think this may need some more discussion.
> >>>>>>
> >>>>>> To me, a "serialized IR" is another form of a "dialect". In this
> case, this dialect will be mostly specific to Iceberg, and compute engines
> will still support reading views in their native SQL. There are some data
> points on this from the Trino community in a previous discussion [1]. In
> addition to being not directly consumable by engines, a serialized IR will
> be hard to consume by humans too.
> >>>>>>
> >>>>>> From that perspective, even if Iceberg adopts some form of a
> serialized IR, we will end up again doing translation, from that IR to the
> engine's dialect on view read time, and from the engine's dialect to that
> IR on the view write time. So serialized IR cannot eliminate translation.
> >>>>>>
> >>>>>> I think it is better to not quickly adopt the serialized IR path
> until it is proven to work and there is sufficient tooling and support
> around it, else it will end up being a constraint.
> >>>>>>
> >>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
> fundamental differences between their approaches, mainly around the
> intermediate representation abstraction. Coral models both the AST and the
> logical plan of a query, making it able to capture the query semantics more
> accurately and hence perform precise transformations. On the flip side,
> SQLGlot abstraction is at the AST level only. Data type inference would be
> a major gap in any solution that does not capture the logical plan for
> example, yet very important to perform successful translation. This is
> backed up by some experiments we performed on actual queries and their
> translation results (from Spark to Trino, comparing results of Coral and
> SQLGlot).
> >>>>>>
> >>>>>> For the IR: Any translation solution (including Coral) must rely on
> an IR, and it has to be decoupled from any of the input and output
> dialects. This is true in the Coral case today. Such IR is the way to
> represent both the intermediate AST and logical plans. Therefore, I do not
> think we can necessarily split projects as "IR projects" vs not, since all
> solutions must use an IR. With that said, IR serialization is a matter of
> staging/milestones of the project. Serialized IR is next on Coral's
> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
> Iceberg interoperable with a Coral-based serialized IR. This will make the
> compatibility with engines that adopt Coral (like Trino) much more robust
> and straightforward.
> >>>>>>
> >>>>>> [1]
> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
> >>>>>>
> >>>>>> Thanks,
> >>>>>> Walaa.
> >>>>>>
> >>>>>>
> >>>>>>
>
Re: [Discuss] Iceberg View Interoperability
Posted by Jean-Baptiste Onofré <jb...@nanthrax.net>.
Hi Ajantha,
During CommunityOverCode, I chatted with Matt Topol about Substrait and ADBC.
I checked the Substrait support in DataFusion and it's interesting.
I was thinking about where to actually store the Substrait plan (I was
thinking about an intermediate SQL representation that we could store
as a metadata instead of directly the plan).
Maybe, we could start with a proposal document to explore the
different options (and so follow Iceberg proposals process, creating a
GitHub Issue with the proposal tag, and attaching the document) ?
Thanks !
Regards
JB
On Mon, Nov 4, 2024 at 10:38 AM Ajantha Bhat <aj...@gmail.com> wrote:
>
> Thanks everyone for the detailed discussions.
>
> Looks like we have consensus towards Substrait.
> Last time I checked it was not adopted by all the engines. But we can work towards the adoption as well.
>
> I will explore further on Substrait and come up with the design doc on the same.
>
> Thanks,
> Ajantha
>
> On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com> wrote:
>>
>> Hey all,
>>
>> I'm +1 in efforts to make views more interoperable across engines as I believe such efforts would be beneficial for the wider ecosystem. I think the way to do that is through higher fidelity IRs such as Substrait.
>>
>> I agree with Walaa that there's not really a valid distinction between IR vs non-IR projects when it comes to translation; my understanding is that in the end any translation framework would have to normalize to an intermediate representation. With the SQLGlot case, it's just that the IR is at the AST level and with the others they have higher fidelity to capture more accurate query semantics (correct me if I'm wrong here). As of today, it is already possible to use SQLGlot, translate to the desired SQL and store these SQL representations. However, since it's not as high fidelity as a proper IR layer, there are issues to consider like Fokko mentioned; but again, if users are happy with their results, they can do this today without any spec changes.
>>
>> In my opinion, the biggest hurdle for Substrait or any other IR to be a viable standard in Iceberg that's worth maintaining is that there would need to be consensus across different engine/language communities (e.g. Walaa referenced the Trino community's perspective on such IR layers). Otherwise it risks becoming something that's defined in the standard but really isn't well accepted which I think we all want to avoid.
>>
>> I think as a starting point, it would be great to sync with at least OSS engines/language communities and try and understand any concrete points of skepticism for considering such a standard. So far a lot of the points of skepticism as I read it are around such a layer being only considerate of 1 engine or having such substantial feature gaps that it can't be considered; but no concrete cases have been called out.
>> Once we establish concrete gaps, I think then it would make sense to work with the respective IR community to help close those gaps or if needed consider other paths.
>>
>> Thanks,
>> Amogh Jahagirdar
>>
>> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <pi...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> I have no experience with Substrait, but i agree it looks like the tool for the job.
>>> Or, as I proposed earlier, we define our own Iceberg IR for the views.
>>>
>>> We can experiment with serialized IR being stored as a String with new dialect name, and this is how we should get this started.
>>> It's probably good end solution as well, but the important value-add is if we manage to converge towards one shared IR that's "native to iceberg".
>>> This would be best for the users -- more views would just work.
>>> And best for long-term evolution of the project -- standardized IR would help things like incremental refreshes (for materialized views).
>>>
>>> Best
>>> Piotr
>>>
>>>
>>>
>>>
>>>
>>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <wa...@gmail.com> wrote:
>>>>
>>>> Hi Fokko,
>>>>
>>>> We can implement Python/Rust/Go clients to interop with the serialized Coral IR. Not sure if it makes sense to have all front-end and back-end implementations (e.g., Spark to Coral IR or Coral IR to Trino, etc) be reimplemented in those languages. Such implementations actually depend on the reuse of the native parsers of those dialects which are typically in Java (also this is to your point about the language coverage -- reusing native parsers is a principle that Coral follows to be compliant with the source dialect). I think making Python/Rust/Go interop/handle the IR (i.e., convert the serialized IR to in-memory IR and the other way around) would be a good start. For example, Python-specific backends and front-end implementations can follow from that.
>>>>
>>>> Thanks,
>>>> Walaa.
>>>>
>>>>
>>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org> wrote:
>>>>>
>>>>> Hey everyone,
>>>>>
>>>>> Views in PyIceberg are not yet as mature as in Java, mostly because tooling in Python tends to work with data frames, rather than SQL. I do think it would be valuable to extend support there.
>>>>>
>>>>> I have a bit of experience in turning SQL into ASTs and extending grammar, and I'm confident to say that it is nearly impossible to cover all the grammar of a specific dialect. My main question is, what will SQLGlot do when we try to translate a dialect that it doesn't fully understand? Will it error out, or will it produce faulty SQL? A simple example can be functions that are not supported in other engines up to recursive CTE's. In this case, not failing upfront would cause correctness issues.
>>>>>
>>>>> Regarding Substrait. Within PyIceberg there was also successful experimentation of having a DuckDB query, sending it to PyIceberg to do the Iceberg query planning, and returning a physical plan to DuckDB to do the actual execution. This was still an early stage and required a lot of work around credentials and field-IDs, but it was quite promising. Using Substrait as views looks easier to me, and would also translate to a dataframe-based world. Walaa, do you have any outlook on Coral Python/Rust/Go support?
>>>>>
>>>>> Kind regards,
>>>>> Fokko
>>>>>
>>>>>
>>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <wa...@gmail.com>:
>>>>>>
>>>>>> I think this may need some more discussion.
>>>>>>
>>>>>> To me, a "serialized IR" is another form of a "dialect". In this case, this dialect will be mostly specific to Iceberg, and compute engines will still support reading views in their native SQL. There are some data points on this from the Trino community in a previous discussion [1]. In addition to being not directly consumable by engines, a serialized IR will be hard to consume by humans too.
>>>>>>
>>>>>> From that perspective, even if Iceberg adopts some form of a serialized IR, we will end up again doing translation, from that IR to the engine's dialect on view read time, and from the engine's dialect to that IR on the view write time. So serialized IR cannot eliminate translation.
>>>>>>
>>>>>> I think it is better to not quickly adopt the serialized IR path until it is proven to work and there is sufficient tooling and support around it, else it will end up being a constraint.
>>>>>>
>>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some fundamental differences between their approaches, mainly around the intermediate representation abstraction. Coral models both the AST and the logical plan of a query, making it able to capture the query semantics more accurately and hence perform precise transformations. On the flip side, SQLGlot abstraction is at the AST level only. Data type inference would be a major gap in any solution that does not capture the logical plan for example, yet very important to perform successful translation. This is backed up by some experiments we performed on actual queries and their translation results (from Spark to Trino, comparing results of Coral and SQLGlot).
>>>>>>
>>>>>> For the IR: Any translation solution (including Coral) must rely on an IR, and it has to be decoupled from any of the input and output dialects. This is true in the Coral case today. Such IR is the way to represent both the intermediate AST and logical plans. Therefore, I do not think we can necessarily split projects as "IR projects" vs not, since all solutions must use an IR. With that said, IR serialization is a matter of staging/milestones of the project. Serialized IR is next on Coral's roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make Iceberg interoperable with a Coral-based serialized IR. This will make the compatibility with engines that adopt Coral (like Trino) much more robust and straightforward.
>>>>>>
>>>>>> [1] https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>>>>
>>>>>> Thanks,
>>>>>> Walaa.
>>>>>>
>>>>>>
>>>>>>
Re: [Discuss] Iceberg View Interoperability
Posted by Ajantha Bhat <aj...@gmail.com>.
Thanks everyone for the detailed discussions.
Looks like we have consensus towards Substrait.
Last time I checked it was not adopted by all the engines. But we can work
towards the adoption as well.
I will explore further on Substrait and come up with the design doc on the
same.
Thanks,
Ajantha
On Mon, Oct 28, 2024 at 11:20 PM Amogh Jahagirdar <2a...@gmail.com> wrote:
> Hey all,
>
> I'm +1 in efforts to make views more interoperable across engines as I
> believe such efforts would be beneficial for the wider ecosystem. I think
> the way to do that is through higher fidelity IRs such as Substrait.
>
> I agree with Walaa that there's not really a valid distinction between IR
> vs non-IR projects when it comes to translation; my understanding is that
> in the end any translation framework would have to normalize to an
> intermediate representation. With the SQLGlot case, it's just that the IR
> is at the AST level and with the others they have higher fidelity to
> capture more accurate query semantics (correct me if I'm wrong here). As of
> today, it is already possible to use SQLGlot, translate to the desired SQL
> and store these SQL representations. However, since it's not as high
> fidelity as a proper IR layer, there are issues to consider like Fokko
> mentioned; but again, if users are happy with their results, they can do
> this today without any spec changes.
>
> In my opinion, the biggest hurdle for Substrait or any other IR to be a
> viable standard in Iceberg that's worth maintaining is that there would
> need to be consensus across different engine/language communities (e.g.
> Walaa referenced the Trino community's perspective on such IR layers).
> Otherwise it risks becoming something that's defined in the standard but
> really isn't well accepted which I think we all want to avoid.
>
> I think as a starting point, it would be great to sync with at least OSS
> engines/language communities and try and understand any concrete points of
> skepticism for considering such a standard. So far a lot of the points of
> skepticism as I read it are around such a layer being only considerate of 1
> engine or having such substantial feature gaps that it can't be considered;
> but no concrete cases have been called out.
> Once we establish concrete gaps, I think then it would make sense to work
> with the respective IR community to help close those gaps or if needed
> consider other paths.
>
> Thanks,
> Amogh Jahagirdar
>
> On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <
> piotr.findeisen@gmail.com> wrote:
>
>> Hi,
>>
>> I have no experience with Substrait, but i agree it looks like the tool
>> for the job.
>> Or, as I proposed earlier, we define our own Iceberg IR for the views.
>>
>> We can experiment with serialized IR being stored as a String with new
>> dialect name, and this is how we should get this started.
>> It's probably good end solution as well, but the important value-add is
>> if we manage to converge towards one shared IR that's "native to iceberg".
>> This would be best for the users -- more views would just work.
>> And best for long-term evolution of the project -- standardized IR would
>> help things like incremental refreshes (for materialized views).
>>
>> Best
>> Piotr
>>
>>
>>
>>
>>
>> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <wa...@gmail.com>
>> wrote:
>>
>>> Hi Fokko,
>>>
>>> We can implement Python/Rust/Go clients to interop with the serialized
>>> Coral IR. Not sure if it makes sense to have all front-end and back-end
>>> implementations (e.g., Spark to Coral IR or Coral IR to Trino, etc) be
>>> reimplemented in those languages. Such implementations actually depend on
>>> the reuse of the native parsers of those dialects which are typically in
>>> Java (also this is to your point about the language coverage --
>>> reusing native parsers is a principle that Coral follows to be compliant
>>> with the source dialect). I think making Python/Rust/Go interop/handle the
>>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>>> around) would be a good start. For example, Python-specific backends and
>>> front-end implementations can follow from that.
>>>
>>> Thanks,
>>> Walaa.
>>>
>>>
>>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
>>> wrote:
>>>
>>>> Hey everyone,
>>>>
>>>> Views in PyIceberg are not yet as mature as in Java, mostly because
>>>> tooling in Python tends to work with data frames, rather than SQL. I do
>>>> think it would be valuable to extend support there.
>>>>
>>>> I have a bit of experience in turning SQL into ASTs and extending
>>>> grammar, and I'm confident to say that it is nearly impossible to cover all
>>>> the grammar of a specific dialect. My main question is, what will SQLGlot
>>>> do when we try to translate a dialect that it doesn't fully understand?
>>>> Will it error out, or will it produce faulty SQL? A simple example can be
>>>> functions that are not supported in other engines up to recursive CTE's. In
>>>> this case, not failing upfront would cause correctness issues.
>>>>
>>>> Regarding Substrait. Within PyIceberg there was also successful
>>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>>> actual execution. This was still an early stage and required a lot of work
>>>> around credentials and field-IDs, but it was quite promising. Using
>>>> Substrait as views looks easier to me, and would also translate to a
>>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>>> Python/Rust/Go support?
>>>>
>>>> Kind regards,
>>>> Fokko
>>>>
>>>>
>>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>>> wa.moustafa@gmail.com>:
>>>>
>>>>> I think this may need some more discussion.
>>>>>
>>>>> To me, a "serialized IR" is another form of a "dialect". In this case,
>>>>> this dialect will be mostly specific to Iceberg, and compute engines
>>>>> will still support reading views in their native SQL. There are some data
>>>>> points on this from the Trino community in a previous discussion [1]. In
>>>>> addition to being not directly consumable by engines, a serialized IR will
>>>>> be hard to consume by humans too.
>>>>>
>>>>> From that perspective, even if Iceberg adopts some form of a
>>>>> serialized IR, we will end up again doing translation, from that IR to the
>>>>> engine's dialect on view read time, and from the engine's dialect to that
>>>>> IR on the view write time. So serialized IR cannot eliminate translation.
>>>>>
>>>>> I think it is better to not quickly adopt the serialized IR path until
>>>>> it is proven to work and there is sufficient tooling and support around it,
>>>>> else it will end up being a constraint.
>>>>>
>>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
>>>>> fundamental differences between their approaches, mainly around the
>>>>> intermediate representation abstraction. Coral models both the AST and the
>>>>> logical plan of a query, making it able to capture the query semantics more
>>>>> accurately and hence perform precise transformations. On the flip side,
>>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>>> a major gap in any solution that does not capture the logical plan for
>>>>> example, yet very important to perform successful translation. This is
>>>>> backed up by some experiments we performed on actual queries and their
>>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>>> SQLGlot).
>>>>>
>>>>> For the IR: Any translation solution (including Coral) must rely on an
>>>>> IR, and it has to be decoupled from any of the input and output dialects.
>>>>> This is true in the Coral case today. Such IR is the way to represent both
>>>>> the intermediate AST and logical plans. Therefore, I do not think we can
>>>>> necessarily split projects as "IR projects" vs not, since all solutions
>>>>> must use an IR. With that said, IR serialization is a matter of
>>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>>> and straightforward.
>>>>>
>>>>> [1]
>>>>> https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>>>
>>>>> Thanks,
>>>>> Walaa.
>>>>>
>>>>>
>>>>>
>>>>>
Re: [Discuss] Iceberg View Interoperability
Posted by Amogh Jahagirdar <2a...@gmail.com>.
Hey all,
I'm +1 in efforts to make views more interoperable across engines as I
believe such efforts would be beneficial for the wider ecosystem. I think
the way to do that is through higher fidelity IRs such as Substrait.
I agree with Walaa that there's not really a valid distinction between IR
vs non-IR projects when it comes to translation; my understanding is that
in the end any translation framework would have to normalize to an
intermediate representation. With the SQLGlot case, it's just that the IR
is at the AST level and with the others they have higher fidelity to
capture more accurate query semantics (correct me if I'm wrong here). As of
today, it is already possible to use SQLGlot, translate to the desired SQL
and store these SQL representations. However, since it's not as high
fidelity as a proper IR layer, there are issues to consider like Fokko
mentioned; but again, if users are happy with their results, they can do
this today without any spec changes.
In my opinion, the biggest hurdle for Substrait or any other IR to be a
viable standard in Iceberg that's worth maintaining is that there would
need to be consensus across different engine/language communities (e.g.
Walaa referenced the Trino community's perspective on such IR layers).
Otherwise it risks becoming something that's defined in the standard but
really isn't well accepted which I think we all want to avoid.
I think as a starting point, it would be great to sync with at least OSS
engines/language communities and try and understand any concrete points of
skepticism for considering such a standard. So far a lot of the points of
skepticism as I read it are around such a layer being only considerate of 1
engine or having such substantial feature gaps that it can't be considered;
but no concrete cases have been called out.
Once we establish concrete gaps, I think then it would make sense to work
with the respective IR community to help close those gaps or if needed
consider other paths.
Thanks,
Amogh Jahagirdar
On Mon, Oct 28, 2024 at 11:43 AM Piotr Findeisen <pi...@gmail.com>
wrote:
> Hi,
>
> I have no experience with Substrait, but i agree it looks like the tool
> for the job.
> Or, as I proposed earlier, we define our own Iceberg IR for the views.
>
> We can experiment with serialized IR being stored as a String with new
> dialect name, and this is how we should get this started.
> It's probably good end solution as well, but the important value-add is if
> we manage to converge towards one shared IR that's "native to iceberg".
> This would be best for the users -- more views would just work.
> And best for long-term evolution of the project -- standardized IR would
> help things like incremental refreshes (for materialized views).
>
> Best
> Piotr
>
>
>
>
>
> On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <wa...@gmail.com>
> wrote:
>
>> Hi Fokko,
>>
>> We can implement Python/Rust/Go clients to interop with the serialized
>> Coral IR. Not sure if it makes sense to have all front-end and back-end
>> implementations (e.g., Spark to Coral IR or Coral IR to Trino, etc) be
>> reimplemented in those languages. Such implementations actually depend on
>> the reuse of the native parsers of those dialects which are typically in
>> Java (also this is to your point about the language coverage --
>> reusing native parsers is a principle that Coral follows to be compliant
>> with the source dialect). I think making Python/Rust/Go interop/handle the
>> IR (i.e., convert the serialized IR to in-memory IR and the other way
>> around) would be a good start. For example, Python-specific backends and
>> front-end implementations can follow from that.
>>
>> Thanks,
>> Walaa.
>>
>>
>> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org>
>> wrote:
>>
>>> Hey everyone,
>>>
>>> Views in PyIceberg are not yet as mature as in Java, mostly because
>>> tooling in Python tends to work with data frames, rather than SQL. I do
>>> think it would be valuable to extend support there.
>>>
>>> I have a bit of experience in turning SQL into ASTs and extending
>>> grammar, and I'm confident to say that it is nearly impossible to cover all
>>> the grammar of a specific dialect. My main question is, what will SQLGlot
>>> do when we try to translate a dialect that it doesn't fully understand?
>>> Will it error out, or will it produce faulty SQL? A simple example can be
>>> functions that are not supported in other engines up to recursive CTE's. In
>>> this case, not failing upfront would cause correctness issues.
>>>
>>> Regarding Substrait. Within PyIceberg there was also successful
>>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>>> actual execution. This was still an early stage and required a lot of work
>>> around credentials and field-IDs, but it was quite promising. Using
>>> Substrait as views looks easier to me, and would also translate to a
>>> dataframe-based world. Walaa, do you have any outlook on Coral
>>> Python/Rust/Go support?
>>>
>>> Kind regards,
>>> Fokko
>>>
>>>
>>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>>> wa.moustafa@gmail.com>:
>>>
>>>> I think this may need some more discussion.
>>>>
>>>> To me, a "serialized IR" is another form of a "dialect". In this case,
>>>> this dialect will be mostly specific to Iceberg, and compute engines
>>>> will still support reading views in their native SQL. There are some data
>>>> points on this from the Trino community in a previous discussion [1]. In
>>>> addition to being not directly consumable by engines, a serialized IR will
>>>> be hard to consume by humans too.
>>>>
>>>> From that perspective, even if Iceberg adopts some form of a serialized
>>>> IR, we will end up again doing translation, from that IR to the engine's
>>>> dialect on view read time, and from the engine's dialect to that IR on the
>>>> view write time. So serialized IR cannot eliminate translation.
>>>>
>>>> I think it is better to not quickly adopt the serialized IR path until
>>>> it is proven to work and there is sufficient tooling and support around it,
>>>> else it will end up being a constraint.
>>>>
>>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
>>>> fundamental differences between their approaches, mainly around the
>>>> intermediate representation abstraction. Coral models both the AST and the
>>>> logical plan of a query, making it able to capture the query semantics more
>>>> accurately and hence perform precise transformations. On the flip side,
>>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>>> a major gap in any solution that does not capture the logical plan for
>>>> example, yet very important to perform successful translation. This is
>>>> backed up by some experiments we performed on actual queries and their
>>>> translation results (from Spark to Trino, comparing results of Coral and
>>>> SQLGlot).
>>>>
>>>> For the IR: Any translation solution (including Coral) must rely on an
>>>> IR, and it has to be decoupled from any of the input and output dialects.
>>>> This is true in the Coral case today. Such IR is the way to represent both
>>>> the intermediate AST and logical plans. Therefore, I do not think we can
>>>> necessarily split projects as "IR projects" vs not, since all solutions
>>>> must use an IR. With that said, IR serialization is a matter of
>>>> staging/milestones of the project. Serialized IR is next on Coral's
>>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>>> and straightforward.
>>>>
>>>> [1] https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>>
>>>> Thanks,
>>>> Walaa.
>>>>
>>>>
>>>>
>>>>
Re: [Discuss] Iceberg View Interoperability
Posted by Piotr Findeisen <pi...@gmail.com>.
Hi,
I have no experience with Substrait, but i agree it looks like the tool for
the job.
Or, as I proposed earlier, we define our own Iceberg IR for the views.
We can experiment with serialized IR being stored as a String with new
dialect name, and this is how we should get this started.
It's probably good end solution as well, but the important value-add is if
we manage to converge towards one shared IR that's "native to iceberg".
This would be best for the users -- more views would just work.
And best for long-term evolution of the project -- standardized IR would
help things like incremental refreshes (for materialized views).
Best
Piotr
On Mon, 28 Oct 2024 at 18:30, Walaa Eldin Moustafa <wa...@gmail.com>
wrote:
> Hi Fokko,
>
> We can implement Python/Rust/Go clients to interop with the serialized
> Coral IR. Not sure if it makes sense to have all front-end and back-end
> implementations (e.g., Spark to Coral IR or Coral IR to Trino, etc) be
> reimplemented in those languages. Such implementations actually depend on
> the reuse of the native parsers of those dialects which are typically in
> Java (also this is to your point about the language coverage --
> reusing native parsers is a principle that Coral follows to be compliant
> with the source dialect). I think making Python/Rust/Go interop/handle the
> IR (i.e., convert the serialized IR to in-memory IR and the other way
> around) would be a good start. For example, Python-specific backends and
> front-end implementations can follow from that.
>
> Thanks,
> Walaa.
>
>
> On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org> wrote:
>
>> Hey everyone,
>>
>> Views in PyIceberg are not yet as mature as in Java, mostly because
>> tooling in Python tends to work with data frames, rather than SQL. I do
>> think it would be valuable to extend support there.
>>
>> I have a bit of experience in turning SQL into ASTs and extending
>> grammar, and I'm confident to say that it is nearly impossible to cover all
>> the grammar of a specific dialect. My main question is, what will SQLGlot
>> do when we try to translate a dialect that it doesn't fully understand?
>> Will it error out, or will it produce faulty SQL? A simple example can be
>> functions that are not supported in other engines up to recursive CTE's. In
>> this case, not failing upfront would cause correctness issues.
>>
>> Regarding Substrait. Within PyIceberg there was also successful
>> experimentation of having a DuckDB query, sending it to PyIceberg to do the
>> Iceberg query planning, and returning a physical plan to DuckDB to do the
>> actual execution. This was still an early stage and required a lot of work
>> around credentials and field-IDs, but it was quite promising. Using
>> Substrait as views looks easier to me, and would also translate to a
>> dataframe-based world. Walaa, do you have any outlook on Coral
>> Python/Rust/Go support?
>>
>> Kind regards,
>> Fokko
>>
>>
>> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
>> wa.moustafa@gmail.com>:
>>
>>> I think this may need some more discussion.
>>>
>>> To me, a "serialized IR" is another form of a "dialect". In this case,
>>> this dialect will be mostly specific to Iceberg, and compute engines
>>> will still support reading views in their native SQL. There are some data
>>> points on this from the Trino community in a previous discussion [1]. In
>>> addition to being not directly consumable by engines, a serialized IR will
>>> be hard to consume by humans too.
>>>
>>> From that perspective, even if Iceberg adopts some form of a serialized
>>> IR, we will end up again doing translation, from that IR to the engine's
>>> dialect on view read time, and from the engine's dialect to that IR on the
>>> view write time. So serialized IR cannot eliminate translation.
>>>
>>> I think it is better to not quickly adopt the serialized IR path until
>>> it is proven to work and there is sufficient tooling and support around it,
>>> else it will end up being a constraint.
>>>
>>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
>>> fundamental differences between their approaches, mainly around the
>>> intermediate representation abstraction. Coral models both the AST and the
>>> logical plan of a query, making it able to capture the query semantics more
>>> accurately and hence perform precise transformations. On the flip side,
>>> SQLGlot abstraction is at the AST level only. Data type inference would be
>>> a major gap in any solution that does not capture the logical plan for
>>> example, yet very important to perform successful translation. This is
>>> backed up by some experiments we performed on actual queries and their
>>> translation results (from Spark to Trino, comparing results of Coral and
>>> SQLGlot).
>>>
>>> For the IR: Any translation solution (including Coral) must rely on an
>>> IR, and it has to be decoupled from any of the input and output dialects.
>>> This is true in the Coral case today. Such IR is the way to represent both
>>> the intermediate AST and logical plans. Therefore, I do not think we can
>>> necessarily split projects as "IR projects" vs not, since all solutions
>>> must use an IR. With that said, IR serialization is a matter of
>>> staging/milestones of the project. Serialized IR is next on Coral's
>>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>>> compatibility with engines that adopt Coral (like Trino) much more robust
>>> and straightforward.
>>>
>>> [1] https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>>
>>> Thanks,
>>> Walaa.
>>>
>>>
>>>
>>>
Re: [Discuss] Iceberg View Interoperability
Posted by Walaa Eldin Moustafa <wa...@gmail.com>.
Hi Fokko,
We can implement Python/Rust/Go clients to interop with the serialized
Coral IR. Not sure if it makes sense to have all front-end and back-end
implementations (e.g., Spark to Coral IR or Coral IR to Trino, etc) be
reimplemented in those languages. Such implementations actually depend on
the reuse of the native parsers of those dialects which are typically in
Java (also this is to your point about the language coverage --
reusing native parsers is a principle that Coral follows to be compliant
with the source dialect). I think making Python/Rust/Go interop/handle the
IR (i.e., convert the serialized IR to in-memory IR and the other way
around) would be a good start. For example, Python-specific backends and
front-end implementations can follow from that.
Thanks,
Walaa.
On Mon, Oct 28, 2024 at 6:05 AM Fokko Driesprong <fo...@apache.org> wrote:
> Hey everyone,
>
> Views in PyIceberg are not yet as mature as in Java, mostly because
> tooling in Python tends to work with data frames, rather than SQL. I do
> think it would be valuable to extend support there.
>
> I have a bit of experience in turning SQL into ASTs and extending grammar,
> and I'm confident to say that it is nearly impossible to cover all the
> grammar of a specific dialect. My main question is, what will SQLGlot do
> when we try to translate a dialect that it doesn't fully understand? Will
> it error out, or will it produce faulty SQL? A simple example can be
> functions that are not supported in other engines up to recursive CTE's. In
> this case, not failing upfront would cause correctness issues.
>
> Regarding Substrait. Within PyIceberg there was also successful
> experimentation of having a DuckDB query, sending it to PyIceberg to do the
> Iceberg query planning, and returning a physical plan to DuckDB to do the
> actual execution. This was still an early stage and required a lot of work
> around credentials and field-IDs, but it was quite promising. Using
> Substrait as views looks easier to me, and would also translate to a
> dataframe-based world. Walaa, do you have any outlook on Coral
> Python/Rust/Go support?
>
> Kind regards,
> Fokko
>
>
> Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
> wa.moustafa@gmail.com>:
>
>> I think this may need some more discussion.
>>
>> To me, a "serialized IR" is another form of a "dialect". In this case,
>> this dialect will be mostly specific to Iceberg, and compute engines
>> will still support reading views in their native SQL. There are some data
>> points on this from the Trino community in a previous discussion [1]. In
>> addition to being not directly consumable by engines, a serialized IR will
>> be hard to consume by humans too.
>>
>> From that perspective, even if Iceberg adopts some form of a serialized
>> IR, we will end up again doing translation, from that IR to the engine's
>> dialect on view read time, and from the engine's dialect to that IR on the
>> view write time. So serialized IR cannot eliminate translation.
>>
>> I think it is better to not quickly adopt the serialized IR path until it
>> is proven to work and there is sufficient tooling and support around it,
>> else it will end up being a constraint.
>>
>> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
>> fundamental differences between their approaches, mainly around the
>> intermediate representation abstraction. Coral models both the AST and the
>> logical plan of a query, making it able to capture the query semantics more
>> accurately and hence perform precise transformations. On the flip side,
>> SQLGlot abstraction is at the AST level only. Data type inference would be
>> a major gap in any solution that does not capture the logical plan for
>> example, yet very important to perform successful translation. This is
>> backed up by some experiments we performed on actual queries and their
>> translation results (from Spark to Trino, comparing results of Coral and
>> SQLGlot).
>>
>> For the IR: Any translation solution (including Coral) must rely on an
>> IR, and it has to be decoupled from any of the input and output dialects.
>> This is true in the Coral case today. Such IR is the way to represent both
>> the intermediate AST and logical plans. Therefore, I do not think we can
>> necessarily split projects as "IR projects" vs not, since all solutions
>> must use an IR. With that said, IR serialization is a matter of
>> staging/milestones of the project. Serialized IR is next on Coral's
>> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
>> Iceberg interoperable with a Coral-based serialized IR. This will make the
>> compatibility with engines that adopt Coral (like Trino) much more robust
>> and straightforward.
>>
>> [1] https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>>
>> Thanks,
>> Walaa.
>>
>>
>>
>>
Re: [Discuss] Iceberg View Interoperability
Posted by Fokko Driesprong <fo...@apache.org>.
Hey everyone,
Views in PyIceberg are not yet as mature as in Java, mostly because tooling
in Python tends to work with data frames, rather than SQL. I do think it
would be valuable to extend support there.
I have a bit of experience in turning SQL into ASTs and extending grammar,
and I'm confident to say that it is nearly impossible to cover all the
grammar of a specific dialect. My main question is, what will SQLGlot do
when we try to translate a dialect that it doesn't fully understand? Will
it error out, or will it produce faulty SQL? A simple example can be
functions that are not supported in other engines up to recursive CTE's. In
this case, not failing upfront would cause correctness issues.
Regarding Substrait. Within PyIceberg there was also successful
experimentation of having a DuckDB query, sending it to PyIceberg to do the
Iceberg query planning, and returning a physical plan to DuckDB to do the
actual execution. This was still an early stage and required a lot of work
around credentials and field-IDs, but it was quite promising. Using
Substrait as views looks easier to me, and would also translate to a
dataframe-based world. Walaa, do you have any outlook on Coral
Python/Rust/Go support?
Kind regards,
Fokko
Op vr 25 okt 2024 om 22:16 schreef Walaa Eldin Moustafa <
wa.moustafa@gmail.com>:
> I think this may need some more discussion.
>
> To me, a "serialized IR" is another form of a "dialect". In this case,
> this dialect will be mostly specific to Iceberg, and compute engines
> will still support reading views in their native SQL. There are some data
> points on this from the Trino community in a previous discussion [1]. In
> addition to being not directly consumable by engines, a serialized IR will
> be hard to consume by humans too.
>
> From that perspective, even if Iceberg adopts some form of a serialized
> IR, we will end up again doing translation, from that IR to the engine's
> dialect on view read time, and from the engine's dialect to that IR on the
> view write time. So serialized IR cannot eliminate translation.
>
> I think it is better to not quickly adopt the serialized IR path until it
> is proven to work and there is sufficient tooling and support around it,
> else it will end up being a constraint.
>
> For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
> fundamental differences between their approaches, mainly around the
> intermediate representation abstraction. Coral models both the AST and the
> logical plan of a query, making it able to capture the query semantics more
> accurately and hence perform precise transformations. On the flip side,
> SQLGlot abstraction is at the AST level only. Data type inference would be
> a major gap in any solution that does not capture the logical plan for
> example, yet very important to perform successful translation. This is
> backed up by some experiments we performed on actual queries and their
> translation results (from Spark to Trino, comparing results of Coral and
> SQLGlot).
>
> For the IR: Any translation solution (including Coral) must rely on an IR,
> and it has to be decoupled from any of the input and output dialects. This
> is true in the Coral case today. Such IR is the way to represent both
> the intermediate AST and logical plans. Therefore, I do not think we can
> necessarily split projects as "IR projects" vs not, since all solutions
> must use an IR. With that said, IR serialization is a matter of
> staging/milestones of the project. Serialized IR is next on Coral's
> roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
> Iceberg interoperable with a Coral-based serialized IR. This will make the
> compatibility with engines that adopt Coral (like Trino) much more robust
> and straightforward.
>
> [1] https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
>
> Thanks,
> Walaa.
>
>
>
>
Re: [Discuss] Iceberg View Interoperability
Posted by Walaa Eldin Moustafa <wa...@gmail.com>.
I think this may need some more discussion.
To me, a "serialized IR" is another form of a "dialect". In this case, this
dialect will be mostly specific to Iceberg, and compute engines will still
support reading views in their native SQL. There are some data points on
this from the Trino community in a previous discussion [1]. In addition to
being not directly consumable by engines, a serialized IR will be hard to
consume by humans too.
From that perspective, even if Iceberg adopts some form of a serialized IR,
we will end up again doing translation, from that IR to the engine's
dialect on view read time, and from the engine's dialect to that IR on the
view write time. So serialized IR cannot eliminate translation.
I think it is better to not quickly adopt the serialized IR path until it
is proven to work and there is sufficient tooling and support around it,
else it will end up being a constraint.
For Coral vs SQLGlot (Disclaimer: I maintain Coral): There are some
fundamental differences between their approaches, mainly around the
intermediate representation abstraction. Coral models both the AST and the
logical plan of a query, making it able to capture the query semantics more
accurately and hence perform precise transformations. On the flip side,
SQLGlot abstraction is at the AST level only. Data type inference would be
a major gap in any solution that does not capture the logical plan for
example, yet very important to perform successful translation. This is
backed up by some experiments we performed on actual queries and their
translation results (from Spark to Trino, comparing results of Coral and
SQLGlot).
For the IR: Any translation solution (including Coral) must rely on an IR,
and it has to be decoupled from any of the input and output dialects. This
is true in the Coral case today. Such IR is the way to represent both
the intermediate AST and logical plans. Therefore, I do not think we can
necessarily split projects as "IR projects" vs not, since all solutions
must use an IR. With that said, IR serialization is a matter of
staging/milestones of the project. Serialized IR is next on Coral's
roadmap. If Iceberg ends up adopting an IR, it might be a good idea to make
Iceberg interoperable with a Coral-based serialized IR. This will make the
compatibility with engines that adopt Coral (like Trino) much more robust
and straightforward.
[1] https://github.com/trinodb/trino/pull/19818#issuecomment-1925894002
Thanks,
Walaa.
Re: [Discuss] Iceberg View Interoperability
Posted by Szehon Ho <sz...@gmail.com>.
+1 on this, should we add an IR field / type to View
<https://iceberg.apache.org/view-spec/> spec
<https://iceberg.apache.org/view-spec/> then? Sorry if its discussed
already, catching up a bit on the context.
Thanks
Szehon
On Fri, Oct 25, 2024 at 11:51 AM rdblue@gmail.com <rd...@gmail.com> wrote:
> Substrait is one of the reasons why we designed views with the ability to
> have different representations. I think that SQL translation is not a great
> solution. I'd like to see more focus on a portable intermediate
> representation like Substrait. That would solve a lot of the limitations
> with the SQL representation (like not wanting to rewrite after resolution).
>
> On Fri, Oct 25, 2024 at 10:21 AM Szehon Ho <sz...@gmail.com>
> wrote:
>
>> Im dont have hands on experience on Substrait, but wondering, is
>> substrait representation possible today with existing Iceberg view spec?
>> Ie, engines can store today the text serialized substrait representation
>> with sql dialect 'substrait'? Or is it an abuse of spec and we should
>> make a proper field for IR representations?
>>
>> I imagine if its possible, interested engines can start working on
>> translating to and from substrait, and hopefully it will catch on, but as
>> of now Im not sure that Iceberg even provides a standard option?
>>
>> Thoughts?
>> Szehon
>>
>> On Tue, Oct 22, 2024 at 4:05 AM Will Raschkowski
>> <wr...@palantir.com.invalid> wrote:
>>
>>> It’s all nascent, but we have a relatively solid experience going from
>>> Spark to Substrait [1], Substrait to Calcite [2], and Calcite to one of its
>>> supported SQL dialects [3]. All these translations are Java-based.
>>>
>>>
>>> [1]: https://github.com/substrait-io/substrait-java/pull/271
>>>
>>> [2]:
>>> https://github.com/substrait-io/substrait-java/blob/main/isthmus/README.md
>>>
>>> [3]:
>>> https://svn.apache.org/repos/asf/calcite/site/apidocs/org/apache/calcite/sql/dialect/package-summary.html
>>>
>>>
>>>
>>> *From: *Ajantha Bhat <aj...@gmail.com>
>>> *Date: *Tuesday, 22 October 2024 at 08:22
>>> *To: *dev@iceberg.apache.org <de...@iceberg.apache.org>
>>> *Subject: *Re: [Discuss] Iceberg View Interoperability
>>>
>>> *CAUTION:* This email originates from an external party (outside of
>>> Palantir). If you believe this message is suspicious in nature, please use
>>> the "Report Message" button built into Outlook.
>>>
>>>
>>>
>>> Thanks Dan for the reply.
>>>
>>> but I'm not convinced using a procedure is a good idea or really moves
>>> things forward in that direction.
>>> I just feel like the procedure approach has a number of drawbacks
>>> including, relying on the user to do the translation, being dependent on
>>> Spark for defining the other representations (the API would typically be
>>> available to you if you're using spark), there are a number of
>>> quoting/escaping issues that make it awkward to define, and it introduces a
>>> second way to define a view.
>>>
>>>
>>> I didn't consider this as a problem as the procedure is not exposing any
>>> new functionalities, users can still achieve this using java API and end up
>>> with the same situations mentioned above. Also, not just Spark, each engine
>>> can have this procedure.
>>> But I see what you mean, having a centralized solution independent of
>>> the engine can be better here.
>>>
>>> I feel like PyIceberg is the right path forward here.
>>>
>>>
>>>
>>> If we are using the python based solutions like SQLGlot, it makes sense
>>> to use the PyIceberg to expose a view API to translate and add additional
>>> dialects from it. But SQLGlot also doesn't support every engine out there
>>> (Like Flink, Dremio, Impala etc). So, we may still need an API to manually
>>> add the dialect for unsupported engines from python.
>>>
>>> I am sure we have people here who work on Coral, SQLGlot, Substrait and
>>> similar technologies in this mailing list. I will wait for a week to see if
>>> there are any opinions on the same. If not, we can start the development
>>> with SQLGlot.
>>>
>>> - Ajantha
>>>
>>>
>>>
>>> On Thu, Oct 17, 2024 at 9:32 PM Daniel Weeks <dw...@apache.org> wrote:
>>>
>>> Hey Ajantha,
>>>
>>>
>>>
>>> I think it's good to figure out a path forward for extending view
>>> support, but I'm not convinced using a procedure is a good idea or really
>>> moves things forward in that direction.
>>>
>>>
>>>
>>> As you already indicated, there are a number of different libraries to
>>> translate views, but of the various options, It feels like there are two
>>> approaches that resonate with me are either letting the engine perform the
>>> translation (like Trino has done with Coral) or supporting views in
>>> python [github.com]
>>> <https://urldefense.com/v3/__https:/github.com/apache/iceberg-python/issues/818__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JObc743Gc$>
>>> so that you can use a combination of libraries like SQLGlot. Python is the
>>> most universally available and is already used in a lot of tooling for
>>> running/managing DDL statements.
>>>
>>>
>>>
>>> I just feel like the procedure approach has a number of drawbacks
>>> including, relying on the user to do the translation, being dependent on
>>> Spark for defining the other representations (the API would typically be
>>> available to you if you're using spark), there are a number of
>>> quoting/escaping issues that make it awkward to define, and it introduces a
>>> second way to define a view.
>>>
>>>
>>>
>>> Long-term IR (e.g. substrate) is the ideal, but I have questions about
>>> whether we'll be able to achieve enough consensus to gain wide adoption. I
>>> feel like PyIceberg is the right path forward here.
>>>
>>>
>>>
>>> -Dan
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Oct 17, 2024 at 5:19 AM Ajantha Bhat <aj...@gmail.com>
>>> wrote:
>>>
>>> Hi everyone,
>>>
>>> It’s been over six months since Iceberg views were introduced (in
>>> version 1.5.0), and while we’ve seen some benefits—such as versioning and
>>> cross-engine recognition—there’s still a critical gap in terms of true
>>> interoperability. Although views created by one engine are recognized by
>>> another, they currently cannot be queried across engines as one engine
>>> cannot understand the SQL dialect of another engine.
>>>
>>> To address this, I propose a two-step approach:
>>>
>>>
>>> *Step 1: A temporary solution to manually add SQL dialects. *It is
>>> already possible to do this using some java code but most of the view users
>>> interact via SQL, there’s currently no SQL-native method to manually add
>>> dialects from other engines into Iceberg views. I suggest adding a call
>>> procedure to enable this. I've recently worked on this and have submitted
>>> the following PRs for review:
>>>
>>> - Spark-3.5: Refactor BaseProcedure to support views [#11326
>>> [github.com]
>>> <https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11326__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOcbrMwYs$>
>>> ]
>>> - Spark-3.5: Procedure to add view dialect [#11327 [github.com]
>>> <https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11327__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOdRsXqDS$>
>>> ]
>>>
>>> With these changes, Iceberg views could be queried across different
>>> engines using their respective dialects.
>>> Please review and provide feedback.
>>>
>>>
>>> *Step 2: A long-term solution for full view interoperability. *For a
>>> sustainable solution, we need to consider both open-source and commercial
>>> engines (e.g., Snowflake, Dremio, BigQuery). A framework that can
>>> automatically translate SQL dialects from one engine to another seems ideal.
>>>
>>> I’ve explored Coral*[1]*, which works well with open-source engines
>>> like Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In
>>> contrast, SQLGlot*[2]* seems to be a better fit, as it supports 23 SQL
>>> dialects and would provide a more robust solution for interoperability. But
>>> SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
>>>
>>> Additionally, I looked into Substrait*[3]* as a potential intermediate
>>> representation for views, but as per my understanding, integration with
>>> commercial engines isn’t quite ready yet. This technology has broader
>>> applications beyond views, including table planning, and may be worth
>>> revisiting in the future.
>>>
>>> Given this, I believe that incorporating a translation layer with
>>> SQLGlot is the right direction. If no one is currently working on view
>>> interoperability, I’d be happy to explore this further and take the lead. I
>>> welcome any thoughts or feedback from those involved in related projects.
>>>
>>> Looking forward to hearing your opinions.
>>>
>>> [1] https://github.com/linkedin/coral [github.com]
>>> <https://urldefense.com/v3/__https:/github.com/linkedin/coral__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOaU9qn72$>
>>> [2] https://sqlglot.com/sqlglot.html [sqlglot.com]
>>> <https://urldefense.com/v3/__https:/sqlglot.com/sqlglot.html__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOSbK5Ggx$>
>>> [3] https://substrait.io/ [substrait.io]
>>> <https://urldefense.com/v3/__https:/substrait.io/__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JORcivRrB$>
>>>
>>> - Ajantha
>>>
>>>
Re: [Discuss] Iceberg View Interoperability
Posted by "rdblue@gmail.com" <rd...@gmail.com>.
Substrait is one of the reasons why we designed views with the ability to
have different representations. I think that SQL translation is not a great
solution. I'd like to see more focus on a portable intermediate
representation like Substrait. That would solve a lot of the limitations
with the SQL representation (like not wanting to rewrite after resolution).
On Fri, Oct 25, 2024 at 10:21 AM Szehon Ho <sz...@gmail.com> wrote:
> Im dont have hands on experience on Substrait, but wondering, is substrait
> representation possible today with existing Iceberg view spec? Ie, engines
> can store today the text serialized substrait representation with sql
> dialect 'substrait'? Or is it an abuse of spec and we should make a
> proper field for IR representations?
>
> I imagine if its possible, interested engines can start working on
> translating to and from substrait, and hopefully it will catch on, but as
> of now Im not sure that Iceberg even provides a standard option?
>
> Thoughts?
> Szehon
>
> On Tue, Oct 22, 2024 at 4:05 AM Will Raschkowski
> <wr...@palantir.com.invalid> wrote:
>
>> It’s all nascent, but we have a relatively solid experience going from
>> Spark to Substrait [1], Substrait to Calcite [2], and Calcite to one of its
>> supported SQL dialects [3]. All these translations are Java-based.
>>
>>
>> [1]: https://github.com/substrait-io/substrait-java/pull/271
>>
>> [2]:
>> https://github.com/substrait-io/substrait-java/blob/main/isthmus/README.md
>>
>> [3]:
>> https://svn.apache.org/repos/asf/calcite/site/apidocs/org/apache/calcite/sql/dialect/package-summary.html
>>
>>
>>
>> *From: *Ajantha Bhat <aj...@gmail.com>
>> *Date: *Tuesday, 22 October 2024 at 08:22
>> *To: *dev@iceberg.apache.org <de...@iceberg.apache.org>
>> *Subject: *Re: [Discuss] Iceberg View Interoperability
>>
>> *CAUTION:* This email originates from an external party (outside of
>> Palantir). If you believe this message is suspicious in nature, please use
>> the "Report Message" button built into Outlook.
>>
>>
>>
>> Thanks Dan for the reply.
>>
>> but I'm not convinced using a procedure is a good idea or really moves
>> things forward in that direction.
>> I just feel like the procedure approach has a number of drawbacks
>> including, relying on the user to do the translation, being dependent on
>> Spark for defining the other representations (the API would typically be
>> available to you if you're using spark), there are a number of
>> quoting/escaping issues that make it awkward to define, and it introduces a
>> second way to define a view.
>>
>>
>> I didn't consider this as a problem as the procedure is not exposing any
>> new functionalities, users can still achieve this using java API and end up
>> with the same situations mentioned above. Also, not just Spark, each engine
>> can have this procedure.
>> But I see what you mean, having a centralized solution independent of the
>> engine can be better here.
>>
>> I feel like PyIceberg is the right path forward here.
>>
>>
>>
>> If we are using the python based solutions like SQLGlot, it makes sense
>> to use the PyIceberg to expose a view API to translate and add additional
>> dialects from it. But SQLGlot also doesn't support every engine out there
>> (Like Flink, Dremio, Impala etc). So, we may still need an API to manually
>> add the dialect for unsupported engines from python.
>>
>> I am sure we have people here who work on Coral, SQLGlot, Substrait and
>> similar technologies in this mailing list. I will wait for a week to see if
>> there are any opinions on the same. If not, we can start the development
>> with SQLGlot.
>>
>> - Ajantha
>>
>>
>>
>> On Thu, Oct 17, 2024 at 9:32 PM Daniel Weeks <dw...@apache.org> wrote:
>>
>> Hey Ajantha,
>>
>>
>>
>> I think it's good to figure out a path forward for extending view
>> support, but I'm not convinced using a procedure is a good idea or really
>> moves things forward in that direction.
>>
>>
>>
>> As you already indicated, there are a number of different libraries to
>> translate views, but of the various options, It feels like there are two
>> approaches that resonate with me are either letting the engine perform the
>> translation (like Trino has done with Coral) or supporting views in
>> python [github.com]
>> <https://urldefense.com/v3/__https:/github.com/apache/iceberg-python/issues/818__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JObc743Gc$>
>> so that you can use a combination of libraries like SQLGlot. Python is the
>> most universally available and is already used in a lot of tooling for
>> running/managing DDL statements.
>>
>>
>>
>> I just feel like the procedure approach has a number of drawbacks
>> including, relying on the user to do the translation, being dependent on
>> Spark for defining the other representations (the API would typically be
>> available to you if you're using spark), there are a number of
>> quoting/escaping issues that make it awkward to define, and it introduces a
>> second way to define a view.
>>
>>
>>
>> Long-term IR (e.g. substrate) is the ideal, but I have questions about
>> whether we'll be able to achieve enough consensus to gain wide adoption. I
>> feel like PyIceberg is the right path forward here.
>>
>>
>>
>> -Dan
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Thu, Oct 17, 2024 at 5:19 AM Ajantha Bhat <aj...@gmail.com>
>> wrote:
>>
>> Hi everyone,
>>
>> It’s been over six months since Iceberg views were introduced (in version
>> 1.5.0), and while we’ve seen some benefits—such as versioning and
>> cross-engine recognition—there’s still a critical gap in terms of true
>> interoperability. Although views created by one engine are recognized by
>> another, they currently cannot be queried across engines as one engine
>> cannot understand the SQL dialect of another engine.
>>
>> To address this, I propose a two-step approach:
>>
>>
>> *Step 1: A temporary solution to manually add SQL dialects. *It is
>> already possible to do this using some java code but most of the view users
>> interact via SQL, there’s currently no SQL-native method to manually add
>> dialects from other engines into Iceberg views. I suggest adding a call
>> procedure to enable this. I've recently worked on this and have submitted
>> the following PRs for review:
>>
>> - Spark-3.5: Refactor BaseProcedure to support views [#11326
>> [github.com]
>> <https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11326__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOcbrMwYs$>
>> ]
>> - Spark-3.5: Procedure to add view dialect [#11327 [github.com]
>> <https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11327__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOdRsXqDS$>
>> ]
>>
>> With these changes, Iceberg views could be queried across different
>> engines using their respective dialects.
>> Please review and provide feedback.
>>
>>
>> *Step 2: A long-term solution for full view interoperability. *For a
>> sustainable solution, we need to consider both open-source and commercial
>> engines (e.g., Snowflake, Dremio, BigQuery). A framework that can
>> automatically translate SQL dialects from one engine to another seems ideal.
>>
>> I’ve explored Coral*[1]*, which works well with open-source engines like
>> Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In
>> contrast, SQLGlot*[2]* seems to be a better fit, as it supports 23 SQL
>> dialects and would provide a more robust solution for interoperability. But
>> SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
>>
>> Additionally, I looked into Substrait*[3]* as a potential intermediate
>> representation for views, but as per my understanding, integration with
>> commercial engines isn’t quite ready yet. This technology has broader
>> applications beyond views, including table planning, and may be worth
>> revisiting in the future.
>>
>> Given this, I believe that incorporating a translation layer with SQLGlot
>> is the right direction. If no one is currently working on view
>> interoperability, I’d be happy to explore this further and take the lead. I
>> welcome any thoughts or feedback from those involved in related projects.
>>
>> Looking forward to hearing your opinions.
>>
>> [1] https://github.com/linkedin/coral [github.com]
>> <https://urldefense.com/v3/__https:/github.com/linkedin/coral__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOaU9qn72$>
>> [2] https://sqlglot.com/sqlglot.html [sqlglot.com]
>> <https://urldefense.com/v3/__https:/sqlglot.com/sqlglot.html__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOSbK5Ggx$>
>> [3] https://substrait.io/ [substrait.io]
>> <https://urldefense.com/v3/__https:/substrait.io/__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JORcivRrB$>
>>
>> - Ajantha
>>
>>
Re: [Discuss] Iceberg View Interoperability
Posted by Szehon Ho <sz...@gmail.com>.
Im dont have hands on experience on Substrait, but wondering, is substrait
representation possible today with existing Iceberg view spec? Ie, engines
can store today the text serialized substrait representation with sql
dialect 'substrait'? Or is it an abuse of spec and we should make a
proper field for IR representations?
I imagine if its possible, interested engines can start working on
translating to and from substrait, and hopefully it will catch on, but as
of now Im not sure that Iceberg even provides a standard option?
Thoughts?
Szehon
On Tue, Oct 22, 2024 at 4:05 AM Will Raschkowski
<wr...@palantir.com.invalid> wrote:
> It’s all nascent, but we have a relatively solid experience going from
> Spark to Substrait [1], Substrait to Calcite [2], and Calcite to one of its
> supported SQL dialects [3]. All these translations are Java-based.
>
>
> [1]: https://github.com/substrait-io/substrait-java/pull/271
>
> [2]:
> https://github.com/substrait-io/substrait-java/blob/main/isthmus/README.md
>
> [3]:
> https://svn.apache.org/repos/asf/calcite/site/apidocs/org/apache/calcite/sql/dialect/package-summary.html
>
>
>
> *From: *Ajantha Bhat <aj...@gmail.com>
> *Date: *Tuesday, 22 October 2024 at 08:22
> *To: *dev@iceberg.apache.org <de...@iceberg.apache.org>
> *Subject: *Re: [Discuss] Iceberg View Interoperability
>
> *CAUTION:* This email originates from an external party (outside of
> Palantir). If you believe this message is suspicious in nature, please use
> the "Report Message" button built into Outlook.
>
>
>
> Thanks Dan for the reply.
>
> but I'm not convinced using a procedure is a good idea or really moves
> things forward in that direction.
> I just feel like the procedure approach has a number of drawbacks
> including, relying on the user to do the translation, being dependent on
> Spark for defining the other representations (the API would typically be
> available to you if you're using spark), there are a number of
> quoting/escaping issues that make it awkward to define, and it introduces a
> second way to define a view.
>
>
> I didn't consider this as a problem as the procedure is not exposing any
> new functionalities, users can still achieve this using java API and end up
> with the same situations mentioned above. Also, not just Spark, each engine
> can have this procedure.
> But I see what you mean, having a centralized solution independent of the
> engine can be better here.
>
> I feel like PyIceberg is the right path forward here.
>
>
>
> If we are using the python based solutions like SQLGlot, it makes sense to
> use the PyIceberg to expose a view API to translate and add additional
> dialects from it. But SQLGlot also doesn't support every engine out there
> (Like Flink, Dremio, Impala etc). So, we may still need an API to manually
> add the dialect for unsupported engines from python.
>
> I am sure we have people here who work on Coral, SQLGlot, Substrait and
> similar technologies in this mailing list. I will wait for a week to see if
> there are any opinions on the same. If not, we can start the development
> with SQLGlot.
>
> - Ajantha
>
>
>
> On Thu, Oct 17, 2024 at 9:32 PM Daniel Weeks <dw...@apache.org> wrote:
>
> Hey Ajantha,
>
>
>
> I think it's good to figure out a path forward for extending view support,
> but I'm not convinced using a procedure is a good idea or really moves
> things forward in that direction.
>
>
>
> As you already indicated, there are a number of different libraries to
> translate views, but of the various options, It feels like there are two
> approaches that resonate with me are either letting the engine perform the
> translation (like Trino has done with Coral) or supporting views in
> python [github.com]
> <https://urldefense.com/v3/__https:/github.com/apache/iceberg-python/issues/818__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JObc743Gc$>
> so that you can use a combination of libraries like SQLGlot. Python is the
> most universally available and is already used in a lot of tooling for
> running/managing DDL statements.
>
>
>
> I just feel like the procedure approach has a number of drawbacks
> including, relying on the user to do the translation, being dependent on
> Spark for defining the other representations (the API would typically be
> available to you if you're using spark), there are a number of
> quoting/escaping issues that make it awkward to define, and it introduces a
> second way to define a view.
>
>
>
> Long-term IR (e.g. substrate) is the ideal, but I have questions about
> whether we'll be able to achieve enough consensus to gain wide adoption. I
> feel like PyIceberg is the right path forward here.
>
>
>
> -Dan
>
>
>
>
>
>
>
>
>
>
>
> On Thu, Oct 17, 2024 at 5:19 AM Ajantha Bhat <aj...@gmail.com>
> wrote:
>
> Hi everyone,
>
> It’s been over six months since Iceberg views were introduced (in version
> 1.5.0), and while we’ve seen some benefits—such as versioning and
> cross-engine recognition—there’s still a critical gap in terms of true
> interoperability. Although views created by one engine are recognized by
> another, they currently cannot be queried across engines as one engine
> cannot understand the SQL dialect of another engine.
>
> To address this, I propose a two-step approach:
>
>
> *Step 1: A temporary solution to manually add SQL dialects. *It is
> already possible to do this using some java code but most of the view users
> interact via SQL, there’s currently no SQL-native method to manually add
> dialects from other engines into Iceberg views. I suggest adding a call
> procedure to enable this. I've recently worked on this and have submitted
> the following PRs for review:
>
> - Spark-3.5: Refactor BaseProcedure to support views [#11326
> [github.com]
> <https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11326__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOcbrMwYs$>
> ]
> - Spark-3.5: Procedure to add view dialect [#11327 [github.com]
> <https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11327__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOdRsXqDS$>
> ]
>
> With these changes, Iceberg views could be queried across different
> engines using their respective dialects.
> Please review and provide feedback.
>
>
> *Step 2: A long-term solution for full view interoperability. *For a
> sustainable solution, we need to consider both open-source and commercial
> engines (e.g., Snowflake, Dremio, BigQuery). A framework that can
> automatically translate SQL dialects from one engine to another seems ideal.
>
> I’ve explored Coral*[1]*, which works well with open-source engines like
> Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In
> contrast, SQLGlot*[2]* seems to be a better fit, as it supports 23 SQL
> dialects and would provide a more robust solution for interoperability. But
> SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
>
> Additionally, I looked into Substrait*[3]* as a potential intermediate
> representation for views, but as per my understanding, integration with
> commercial engines isn’t quite ready yet. This technology has broader
> applications beyond views, including table planning, and may be worth
> revisiting in the future.
>
> Given this, I believe that incorporating a translation layer with SQLGlot
> is the right direction. If no one is currently working on view
> interoperability, I’d be happy to explore this further and take the lead. I
> welcome any thoughts or feedback from those involved in related projects.
>
> Looking forward to hearing your opinions.
>
> [1] https://github.com/linkedin/coral [github.com]
> <https://urldefense.com/v3/__https:/github.com/linkedin/coral__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOaU9qn72$>
> [2] https://sqlglot.com/sqlglot.html [sqlglot.com]
> <https://urldefense.com/v3/__https:/sqlglot.com/sqlglot.html__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOSbK5Ggx$>
> [3] https://substrait.io/ [substrait.io]
> <https://urldefense.com/v3/__https:/substrait.io/__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JORcivRrB$>
>
> - Ajantha
>
>
Re: [Discuss] Iceberg View Interoperability
Posted by Will Raschkowski <wr...@palantir.com.INVALID>.
It’s all nascent, but we have a relatively solid experience going from Spark to Substrait [1], Substrait to Calcite [2], and Calcite to one of its supported SQL dialects [3]. All these translations are Java-based.
[1]: https://github.com/substrait-io/substrait-java/pull/271
[2]: https://github.com/substrait-io/substrait-java/blob/main/isthmus/README.md
[3]: https://svn.apache.org/repos/asf/calcite/site/apidocs/org/apache/calcite/sql/dialect/package-summary.html
From: Ajantha Bhat <aj...@gmail.com>
Date: Tuesday, 22 October 2024 at 08:22
To: dev@iceberg.apache.org <de...@iceberg.apache.org>
Subject: Re: [Discuss] Iceberg View Interoperability
CAUTION: This email originates from an external party (outside of Palantir). If you believe this message is suspicious in nature, please use the "Report Message" button built into Outlook.
Thanks Dan for the reply.
but I'm not convinced using a procedure is a good idea or really moves things forward in that direction.
I just feel like the procedure approach has a number of drawbacks including, relying on the user to do the translation, being dependent on Spark for defining the other representations (the API would typically be available to you if you're using spark), there are a number of quoting/escaping issues that make it awkward to define, and it introduces a second way to define a view.
I didn't consider this as a problem as the procedure is not exposing any new functionalities, users can still achieve this using java API and end up with the same situations mentioned above. Also, not just Spark, each engine can have this procedure.
But I see what you mean, having a centralized solution independent of the engine can be better here.
I feel like PyIceberg is the right path forward here.
If we are using the python based solutions like SQLGlot, it makes sense to use the PyIceberg to expose a view API to translate and add additional dialects from it. But SQLGlot also doesn't support every engine out there (Like Flink, Dremio, Impala etc). So, we may still need an API to manually add the dialect for unsupported engines from python.
I am sure we have people here who work on Coral, SQLGlot, Substrait and similar technologies in this mailing list. I will wait for a week to see if there are any opinions on the same. If not, we can start the development with SQLGlot.
- Ajantha
On Thu, Oct 17, 2024 at 9:32 PM Daniel Weeks <dw...@apache.org>> wrote:
Hey Ajantha,
I think it's good to figure out a path forward for extending view support, but I'm not convinced using a procedure is a good idea or really moves things forward in that direction.
As you already indicated, there are a number of different libraries to translate views, but of the various options, It feels like there are two approaches that resonate with me are either letting the engine perform the translation (like Trino has done with Coral) or supporting views in python [github.com]<https://urldefense.com/v3/__https:/github.com/apache/iceberg-python/issues/818__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JObc743Gc$> so that you can use a combination of libraries like SQLGlot. Python is the most universally available and is already used in a lot of tooling for running/managing DDL statements.
I just feel like the procedure approach has a number of drawbacks including, relying on the user to do the translation, being dependent on Spark for defining the other representations (the API would typically be available to you if you're using spark), there are a number of quoting/escaping issues that make it awkward to define, and it introduces a second way to define a view.
Long-term IR (e.g. substrate) is the ideal, but I have questions about whether we'll be able to achieve enough consensus to gain wide adoption. I feel like PyIceberg is the right path forward here.
-Dan
On Thu, Oct 17, 2024 at 5:19 AM Ajantha Bhat <aj...@gmail.com>> wrote:
Hi everyone,
It’s been over six months since Iceberg views were introduced (in version 1.5.0), and while we’ve seen some benefits—such as versioning and cross-engine recognition—there’s still a critical gap in terms of true interoperability. Although views created by one engine are recognized by another, they currently cannot be queried across engines as one engine cannot understand the SQL dialect of another engine.
To address this, I propose a two-step approach:
Step 1: A temporary solution to manually add SQL dialects.
It is already possible to do this using some java code but most of the view users interact via SQL, there’s currently no SQL-native method to manually add dialects from other engines into Iceberg views. I suggest adding a call procedure to enable this. I've recently worked on this and have submitted the following PRs for review:
* Spark-3.5: Refactor BaseProcedure to support views [#11326 [github.com]<https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11326__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOcbrMwYs$>]
* Spark-3.5: Procedure to add view dialect [#11327 [github.com]<https://urldefense.com/v3/__https:/github.com/apache/iceberg/pull/11327__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOdRsXqDS$>]
With these changes, Iceberg views could be queried across different engines using their respective dialects.
Please review and provide feedback.
Step 2: A long-term solution for full view interoperability.
For a sustainable solution, we need to consider both open-source and commercial engines (e.g., Snowflake, Dremio, BigQuery). A framework that can automatically translate SQL dialects from one engine to another seems ideal.
I’ve explored Coral[1], which works well with open-source engines like Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In contrast, SQLGlot[2] seems to be a better fit, as it supports 23 SQL dialects and would provide a more robust solution for interoperability. But SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
Additionally, I looked into Substrait[3] as a potential intermediate representation for views, but as per my understanding, integration with commercial engines isn’t quite ready yet. This technology has broader applications beyond views, including table planning, and may be worth revisiting in the future.
Given this, I believe that incorporating a translation layer with SQLGlot is the right direction. If no one is currently working on view interoperability, I’d be happy to explore this further and take the lead. I welcome any thoughts or feedback from those involved in related projects.
Looking forward to hearing your opinions.
[1] https://github.com/linkedin/coral [github.com]<https://urldefense.com/v3/__https:/github.com/linkedin/coral__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOaU9qn72$>
[2] https://sqlglot.com/sqlglot.html [sqlglot.com]<https://urldefense.com/v3/__https:/sqlglot.com/sqlglot.html__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JOSbK5Ggx$>
[3] https://substrait.io/ [substrait.io]<https://urldefense.com/v3/__https:/substrait.io/__;!!NkS9JGVQ2sDq!8bASoPc-OIE9M3O3F6FPExuXHCZjdf6v32IN8GkYLTwwfEGmZcpYq8QAmmFAE0tfLgtOJDXpv7C7gQ-JORcivRrB$>
- Ajantha
Re: [Discuss] Iceberg View Interoperability
Posted by Ajantha Bhat <aj...@gmail.com>.
Thanks Dan for the reply.
but I'm not convinced using a procedure is a good idea or really moves
> things forward in that direction.
> I just feel like the procedure approach has a number of drawbacks
> including, relying on the user to do the translation, being dependent on
> Spark for defining the other representations (the API would typically be
> available to you if you're using spark), there are a number of
> quoting/escaping issues that make it awkward to define, and it introduces a
> second way to define a view.
I didn't consider this as a problem as the procedure is not exposing any
new functionalities, users can still achieve this using java API and end up
with the same situations mentioned above. Also, not just Spark, each engine
can have this procedure.
But I see what you mean, having a centralized solution independent of the
engine can be better here.
I feel like PyIceberg is the right path forward here.
If we are using the python based solutions like SQLGlot, it makes sense to
use the PyIceberg to expose a view API to translate and add additional
dialects from it. But SQLGlot also doesn't support every engine out there
(Like Flink, Dremio, Impala etc). So, we may still need an API to manually
add the dialect for unsupported engines from python.
I am sure we have people here who work on Coral, SQLGlot, Substrait and
similar technologies in this mailing list. I will wait for a week to see if
there are any opinions on the same. If not, we can start the development
with SQLGlot.
- Ajantha
On Thu, Oct 17, 2024 at 9:32 PM Daniel Weeks <dw...@apache.org> wrote:
> Hey Ajantha,
>
> I think it's good to figure out a path forward for extending view support,
> but I'm not convinced using a procedure is a good idea or really moves
> things forward in that direction.
>
> As you already indicated, there are a number of different libraries to
> translate views, but of the various options, It feels like there are two
> approaches that resonate with me are either letting the engine perform the
> translation (like Trino has done with Coral) or supporting views in python
> <https://github.com/apache/iceberg-python/issues/818> so that you can use
> a combination of libraries like SQLGlot. Python is the most universally
> available and is already used in a lot of tooling for running/managing DDL
> statements.
>
> I just feel like the procedure approach has a number of drawbacks
> including, relying on the user to do the translation, being dependent on
> Spark for defining the other representations (the API would typically be
> available to you if you're using spark), there are a number of
> quoting/escaping issues that make it awkward to define, and it introduces a
> second way to define a view.
>
> Long-term IR (e.g. substrate) is the ideal, but I have questions about
> whether we'll be able to achieve enough consensus to gain wide adoption. I
> feel like PyIceberg is the right path forward here.
>
> -Dan
>
>
>
>
>
> On Thu, Oct 17, 2024 at 5:19 AM Ajantha Bhat <aj...@gmail.com>
> wrote:
>
>> Hi everyone,
>>
>> It’s been over six months since Iceberg views were introduced (in version
>> 1.5.0), and while we’ve seen some benefits—such as versioning and
>> cross-engine recognition—there’s still a critical gap in terms of true
>> interoperability. Although views created by one engine are recognized by
>> another, they currently cannot be queried across engines as one engine
>> cannot understand the SQL dialect of another engine.
>>
>> To address this, I propose a two-step approach:
>>
>>
>> *Step 1: A temporary solution to manually add SQL dialects.*It is
>> already possible to do this using some java code but most of the view users
>> interact via SQL, there’s currently no SQL-native method to manually add
>> dialects from other engines into Iceberg views. I suggest adding a call
>> procedure to enable this. I've recently worked on this and have submitted
>> the following PRs for review:
>>
>> - Spark-3.5: Refactor BaseProcedure to support views [#11326
>> <https://github.com/apache/iceberg/pull/11326>]
>> - Spark-3.5: Procedure to add view dialect [#11327
>> <https://github.com/apache/iceberg/pull/11327>]
>>
>> With these changes, Iceberg views could be queried across different
>> engines using their respective dialects.
>> Please review and provide feedback.
>>
>>
>> *Step 2: A long-term solution for full view interoperability.*For a
>> sustainable solution, we need to consider both open-source and commercial
>> engines (e.g., Snowflake, Dremio, BigQuery). A framework that can
>> automatically translate SQL dialects from one engine to another seems ideal.
>>
>> I’ve explored Coral*[1]*, which works well with open-source engines like
>> Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In
>> contrast, SQLGlot*[2]* seems to be a better fit, as it supports 23 SQL
>> dialects and would provide a more robust solution for interoperability. But
>> SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
>>
>> Additionally, I looked into Substrait*[3]* as a potential intermediate
>> representation for views, but as per my understanding, integration with
>> commercial engines isn’t quite ready yet. This technology has broader
>> applications beyond views, including table planning, and may be worth
>> revisiting in the future.
>>
>> Given this, I believe that incorporating a translation layer with SQLGlot
>> is the right direction. If no one is currently working on view
>> interoperability, I’d be happy to explore this further and take the lead. I
>> welcome any thoughts or feedback from those involved in related projects.
>>
>> Looking forward to hearing your opinions.
>>
>> [1] https://github.com/linkedin/coral
>> [2] https://sqlglot.com/sqlglot.html
>> [3] https://substrait.io/
>>
>> - Ajantha
>>
>
Re: [Discuss] Iceberg View Interoperability
Posted by Daniel Weeks <dw...@apache.org>.
Hey Ajantha,
I think it's good to figure out a path forward for extending view support,
but I'm not convinced using a procedure is a good idea or really moves
things forward in that direction.
As you already indicated, there are a number of different libraries to
translate views, but of the various options, It feels like there are two
approaches that resonate with me are either letting the engine perform the
translation (like Trino has done with Coral) or supporting views in python
<https://github.com/apache/iceberg-python/issues/818> so that you can use a
combination of libraries like SQLGlot. Python is the most universally
available and is already used in a lot of tooling for running/managing DDL
statements.
I just feel like the procedure approach has a number of drawbacks
including, relying on the user to do the translation, being dependent on
Spark for defining the other representations (the API would typically be
available to you if you're using spark), there are a number of
quoting/escaping issues that make it awkward to define, and it introduces a
second way to define a view.
Long-term IR (e.g. substrate) is the ideal, but I have questions about
whether we'll be able to achieve enough consensus to gain wide adoption. I
feel like PyIceberg is the right path forward here.
-Dan
On Thu, Oct 17, 2024 at 5:19 AM Ajantha Bhat <aj...@gmail.com> wrote:
> Hi everyone,
>
> It’s been over six months since Iceberg views were introduced (in version
> 1.5.0), and while we’ve seen some benefits—such as versioning and
> cross-engine recognition—there’s still a critical gap in terms of true
> interoperability. Although views created by one engine are recognized by
> another, they currently cannot be queried across engines as one engine
> cannot understand the SQL dialect of another engine.
>
> To address this, I propose a two-step approach:
>
>
> *Step 1: A temporary solution to manually add SQL dialects.*It is already
> possible to do this using some java code but most of the view users
> interact via SQL, there’s currently no SQL-native method to manually add
> dialects from other engines into Iceberg views. I suggest adding a call
> procedure to enable this. I've recently worked on this and have submitted
> the following PRs for review:
>
> - Spark-3.5: Refactor BaseProcedure to support views [#11326
> <https://github.com/apache/iceberg/pull/11326>]
> - Spark-3.5: Procedure to add view dialect [#11327
> <https://github.com/apache/iceberg/pull/11327>]
>
> With these changes, Iceberg views could be queried across different
> engines using their respective dialects.
> Please review and provide feedback.
>
>
> *Step 2: A long-term solution for full view interoperability.*For a
> sustainable solution, we need to consider both open-source and commercial
> engines (e.g., Snowflake, Dremio, BigQuery). A framework that can
> automatically translate SQL dialects from one engine to another seems ideal.
>
> I’ve explored Coral*[1]*, which works well with open-source engines like
> Hive, Trino, and Spark, but doesn’t fully cover commercial engines. In
> contrast, SQLGlot*[2]* seems to be a better fit, as it supports 23 SQL
> dialects and would provide a more robust solution for interoperability. But
> SQLGlot is a python project. So, we need REST or Py4J to work with SQLGlot.
>
> Additionally, I looked into Substrait*[3]* as a potential intermediate
> representation for views, but as per my understanding, integration with
> commercial engines isn’t quite ready yet. This technology has broader
> applications beyond views, including table planning, and may be worth
> revisiting in the future.
>
> Given this, I believe that incorporating a translation layer with SQLGlot
> is the right direction. If no one is currently working on view
> interoperability, I’d be happy to explore this further and take the lead. I
> welcome any thoughts or feedback from those involved in related projects.
>
> Looking forward to hearing your opinions.
>
> [1] https://github.com/linkedin/coral
> [2] https://sqlglot.com/sqlglot.html
> [3] https://substrait.io/
>
> - Ajantha
>