Micah or someone more familiar (Jacques?), could you summarize some of the details of the Arrow IR proposal? What are the intended use cases and goals? We'd want to see whether the goals align with our own.
On Thu, Aug 26, 2021 at 9:24 AM Micah Kornfield <emkornfi...@gmail.com> wrote: > Small tangent: > > Regarding an IR, Apache Arrow is looking at this now [1] with several RFC > proposals. It would be nice to coordinate, at least to make sure > translation from one IR to another isn't too onerous (in an ideal world we > could maybe share the same one). Either way external feedback would be > useful. > > Cheers, > Micah > > > [1] > https://mail-archives.apache.org/mod_mbox/arrow-dev/202108.mbox/%3cCAKa9qDmLq_4dcmFTJJWpp=ejd0qpl9lygynq2jagxo+lfer...@mail.gmail.com%3e > > On Thu, Aug 26, 2021 at 8:21 AM Ryan Blue <b...@tabular.io> wrote: > >> I think that the current proposal is looking good, but it is always a >> good idea to give people a few days to review it and bring up any issues or >> further discussion on the topics in this thread. >> >> I'll also add this to the next sync agenda so we can farm for dissent >> next week. Sometimes you can get a better read on what is still concerning >> in person. >> >> Ryan >> >> On Wed, Aug 25, 2021 at 9:46 PM Anjali Norwood >> <anorw...@netflix.com.invalid> wrote: >> >>> Hello All, >>> I answered all the comments and made changes to the spec to reflect them >>> .. the doc now shows the new revision (link here: >>> https://docs.google.com/document/d/1wQt57EWylluNFdnxVxaCkCSvnWlI8vVtwfnPjQ6Y7aw/edit# >>> ). >>> Please diff with the version named 'Spec v1' in order to see the deltas. >>> Please let me know if any of your comments are not >>> satisfactorily addressed. >>> >>> @Ryan, Thank you for the insightful feedback, especially around the use >>> of Iceberg data types for schema and possible evolution. >>> I agree with your comment: *"I think we should move forward with the >>> proposal as it is, and pursue type annotations and possibly IR in >>> parallel."* >>> How do we achieve consensus/farm for dissent and move forward with the >>> proposal? >>> >>> thanks, >>> Anjali. >>> >>> >>> >>> On Sun, Aug 22, 2021 at 11:12 AM John Zhuge <jzh...@apache.org> wrote: >>> >>>> +1 >>>> >>>> On Sun, Aug 22, 2021 at 10:02 AM Ryan Blue <b...@tabular.io> wrote: >>>> >>>>> Thanks for working on this, Anjali! It’s great to see the thorough >>>>> discussion here with everyone. >>>>> >>>>> For the discussion about SQL dialect, I think that the right first >>>>> step is to capture the SQL or query dialect. That will give us the most >>>>> flexibility. Engines that can use Coral for translation can attempt to >>>>> convert and engines that don’t can see if the SQL is valid and can be >>>>> used. >>>>> >>>>> I think that the idea to create a minimal IR is an interesting one, >>>>> but can be added later. We will always need to record the SQL and dialect, >>>>> even if we translate to IR because users are going to configure views >>>>> using >>>>> SQL. Uses like showing view history or debugging need to show the original >>>>> SQL, plus relevant information like where it was created and the SQL >>>>> dialect. We should be able to add this later by adding additional metadata >>>>> to the view definition. I don’t think that it would introduce breaking >>>>> changes to add a common representation that can be optionally consumed. >>>>> >>>>> Let’s continue talking about a minimal IR, separately. View >>>>> translation is a hard problem. Right now, to get views across engines we >>>>> have to compromise confidence. IR is a way to have strong confidence, but >>>>> with limited expressibility. I think that’s a good trade in a lot of cases >>>>> and is worth pursuing, even if it will take a long time. >>>>> >>>>> Jacques makes a great point about types, but I think that the right >>>>> option here is to continue using Iceberg types. We’ve already had >>>>> discussions about whether Iceberg should support annotating types with >>>>> engine-specific ones, so we have a reasonable way to improve this while >>>>> also providing compatibility across engines: char(n) is not >>>>> necessarily supported everywhere and mapping it to string will make >>>>> sense in most places. The schema is primarily used to validate that the >>>>> data produced by the query hasn’t changed and that is more about the >>>>> number >>>>> of columns in structs and the names of fields rather than exact types. We >>>>> can fix up types when substituting without losing too much: if the SQL >>>>> produces a varchar(10) field that the view metadata says is a string, >>>>> then it’s okay that it is varchar(10). There is some loss in that we >>>>> don’t know if it was originally varchar(5), but I think that this is >>>>> not going to cause too many issues. Not all engines will even validate >>>>> that >>>>> the schema has not changed, since it could be valid to use select * >>>>> from x where ... and allow new fields to appear. >>>>> >>>>> Right now, I think we should move forward with the proposal as it is, >>>>> and pursue type annotations and possibly IR in parallel. Does that sound >>>>> reasonable to everyone? >>>>> >>>>> Ryan >>>>> >>>>> On Thu, Jul 29, 2021 at 7:50 AM Piotr Findeisen < >>>>> pi...@starburstdata.com> wrote: >>>>> >>>>>> Hi Anjali, >>>>>> >>>>>> That's a nice summary. >>>>>> >>>>>> re dialect field. It shouldn't be a bit trouble to have it (or any >>>>>> other way to identify application that created the view), and it might be >>>>>> useful. >>>>>> Why not make it required from the start? >>>>>> >>>>>> re "expanded/resolved SQL" -- i don't understand yet what we would >>>>>> put there, so cannot comment. >>>>>> >>>>>> I agree there it's nice to get something out of the door, and I see >>>>>> how the current proposal fits some needs already. >>>>>> However, i am concerned about the proliferation of non-cross-engine >>>>>> compatible views, if we do that. >>>>>> >>>>>> Also, if we later agree on any compatible approach (portable subset >>>>>> of SQL, engine-agnostic IR, etc.), then from the perspective of each >>>>>> engine, it would be a breaking change. >>>>>> Unless we make the compatible approach as expressive as full power of >>>>>> SQL, some views that are possible to create in v1 will not be possible to >>>>>> create in v2. >>>>>> Thus, if v1 is "some SQL" and v2 is "something awesomely >>>>>> compatible", we may not be able to roll it out. >>>>>> >>>>>> > the convention of common SQL has been working for a majority of >>>>>> users. SQL features commonly used are column projections, simple filter >>>>>> application, joins, grouping and common aggregate and scalar function. A >>>>>> few users occasionally would like to use Trino or Spark specific >>>>>> functions >>>>>> but are sometimes able to find a way to use a function that is common to >>>>>> both the engines. >>>>>> >>>>>> >>>>>> it's an awesome summary of what constructs are necessary to be able >>>>>> to define useful views, while also keep them portable. >>>>>> >>>>>> To be able to express column projections, simple filter application, >>>>>> joins, grouping and common aggregate and scalar function in a structured >>>>>> IR, how much effort do you think would be required? >>>>>> We didn't really talk about downsides of a structured approach, other >>>>>> than it looks complex. >>>>>> if we indeed estimate it as a multi-year effort, i wouldn't argue for >>>>>> that. Maybe i were overly optimistic though. >>>>>> >>>>>> >>>>>> As Jack mentioned, for engine-specific approach that's not supposed >>>>>> to be consumed by multiple engines, we may be better served with approach >>>>>> that's outside of Iceberg spec, like >>>>>> https://github.com/trinodb/trino/pull/8540. >>>>>> >>>>>> >>>>>> Best, >>>>>> PF >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Jul 29, 2021 at 12:33 PM Anjali Norwood >>>>>> <anorw...@netflix.com.invalid> wrote: >>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> Thank you for all the comments. I will try to address them all here >>>>>>> together. >>>>>>> >>>>>>> >>>>>>> - @all Cross engine compatibility of view definition: Multiple >>>>>>> options such as engine agnostic SQL or IR of some form have been >>>>>>> mentioned. >>>>>>> We can all agree that all of these options are non-trivial to >>>>>>> design/implement (perhaps a multi-year effort based on the option >>>>>>> chosen) >>>>>>> and merit further discussion. I would like to suggest that we >>>>>>> continue this >>>>>>> discussion but target this work for the future (v2?). In v1, we can >>>>>>> add an >>>>>>> optional dialect field and an optional expanded/resolved SQL field >>>>>>> that can >>>>>>> be interpreted by engines as they see fit. V1 can unlock many use >>>>>>> cases >>>>>>> where the views are either accessed by a single engine or >>>>>>> multi-engine use >>>>>>> cases where a (common) subset of SQL is supported. This proposal >>>>>>> allows for >>>>>>> desirable features such as versioning of views and a common format of >>>>>>> storing view metadata while allowing extensibility in the future. >>>>>>> *Does >>>>>>> anyone feel strongly otherwise?* >>>>>>> - @Piotr As for common views at Netflix, the restrictions on >>>>>>> SQL are not enforced, but are advised as best practices. The >>>>>>> convention of >>>>>>> common SQL has been working for a majority of users. SQL features >>>>>>> commonly >>>>>>> used are column projections, simple filter application, joins, >>>>>>> grouping and >>>>>>> common aggregate and scalar function. A few users occasionally would >>>>>>> like >>>>>>> to use Trino or Spark specific functions but are sometimes able to >>>>>>> find a >>>>>>> way to use a function that is common to both the engines. >>>>>>> - @Jacques and @Jack Iceberg data types are engine agnostic and >>>>>>> hence were picked for storing view schema. Thinking further, the >>>>>>> schema >>>>>>> field should be made 'optional', since not all engines require it. >>>>>>> (e.g. >>>>>>> Spark does not need it and Trino uses it only for validation). >>>>>>> - @Jacques Table references in the views can be arbitrary >>>>>>> objects such as tables from other catalogs or elasticsearch tables >>>>>>> etc. I >>>>>>> will clarify it in the spec. >>>>>>> >>>>>>> I will work on incorporating all the comments in the spec and make >>>>>>> the next revision available for review soon. >>>>>>> >>>>>>> Regards, >>>>>>> Anjali. >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Tue, Jul 27, 2021 at 2:51 AM Piotr Findeisen < >>>>>>> pi...@starburstdata.com> wrote: >>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> Thanks Jack and Jacques for sharing your thoughts. >>>>>>>> >>>>>>>> I agree that tracking dialect/origin is better than nothing. >>>>>>>> I think having a Map {dialect: sql} is not going to buy us much. >>>>>>>> I.e. it would be useful if there was some external app (or a human >>>>>>>> being) that would write those alternative SQLs for each dialect. >>>>>>>> Otherwise I am not imagining Spark writing SQL for Spark and Trino, >>>>>>>> or Trino writing SQL for Trino and Spark. >>>>>>>> >>>>>>>> Thanks Jacques for a good summary of SQL supporting options. >>>>>>>> While i like the idea of starting with Trino SQL ANTLR grammar file >>>>>>>> (it's really well written and resembles spec quite well), you made a >>>>>>>> good >>>>>>>> point that grammar is only part of the problem. Coercions, function >>>>>>>> resolution, dereference resolution, table resolution are part of query >>>>>>>> analysis that goes beyond just grammar. >>>>>>>> In fact, column scoping rules -- while clearly defined by the spec >>>>>>>> -- may easily differ between engines (pretty usual). >>>>>>>> That's why i would rather lean towards some intermediate >>>>>>>> representation that is *not *SQL, doesn't require parsing (is >>>>>>>> already structural), nor analysis (no scopes! no implicit coercions!). >>>>>>>> Before we embark on such a journey, it would be interesting to hear >>>>>>>> @Martin >>>>>>>> Traverso <mar...@starburstdata.com> 's thoughts on feasibility >>>>>>>> though. >>>>>>>> >>>>>>>> >>>>>>>> Best, >>>>>>>> PF >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Fri, Jul 23, 2021 at 3:27 AM Jacques Nadeau < >>>>>>>> jacquesnad...@gmail.com> wrote: >>>>>>>> >>>>>>>>> Some thoughts... >>>>>>>>> >>>>>>>>> - In general, many engines want (or may require) a resolved >>>>>>>>> sql field. This--at minimum--typically includes star expansion >>>>>>>>> since >>>>>>>>> traditional view behavior is stars are expanded at view creation >>>>>>>>> time >>>>>>>>> (since this is the only way to guarantee that the view returns the >>>>>>>>> same >>>>>>>>> logical definition even if the underlying table changes). This may >>>>>>>>> also >>>>>>>>> include a replacement of relative object names to absolute object >>>>>>>>> names >>>>>>>>> based on the session catalog & namespace. If I recall correctly, >>>>>>>>> Hive does >>>>>>>>> both of these things. >>>>>>>>> - It isn't clear in the spec whether the table references used >>>>>>>>> in views are restricted to other Iceberg objects or can be >>>>>>>>> arbitrary >>>>>>>>> objects in the context of a particular engine. Maybe I missed >>>>>>>>> this? For >>>>>>>>> example, can I have a Trino engine view that references an >>>>>>>>> Elasticsearch >>>>>>>>> table stored in an Iceberg view? >>>>>>>>> - Restricting schemas to the Iceberg types will likely lead to >>>>>>>>> unintended consequences. I appreciate the attraction to it but I >>>>>>>>> think it >>>>>>>>> would either create artificial barriers around the types of SQL >>>>>>>>> that are >>>>>>>>> allowed and/or mean that replacing a CTE with a view could >>>>>>>>> potentially >>>>>>>>> change the behavior of the query which I believe violates most >>>>>>>>> typical >>>>>>>>> engine behaviors. A good example of this is the simple sql >>>>>>>>> statement of >>>>>>>>> "SELECT c1, 'foo' as c2 from table1". In many engines (and Calcite >>>>>>>>> by >>>>>>>>> default I believe), c2 will be specified as a CHAR(3). In this >>>>>>>>> Iceberg >>>>>>>>> context, is this view disallowed? If it isn't disallowed then you >>>>>>>>> have an >>>>>>>>> issue where the view schema will be required to be different from >>>>>>>>> a CTE >>>>>>>>> since the engine will resolve it differently than Iceberg. Even if >>>>>>>>> you >>>>>>>>> ignore CHAR(X), you've still got VARCHAR(X) to contend with... >>>>>>>>> - It is important to remember that Calcite is a set of >>>>>>>>> libraries and not a specification. There are things that can be >>>>>>>>> specified >>>>>>>>> in Calcite but in general it doesn't have formal specification as >>>>>>>>> a first >>>>>>>>> principle. It is more implementation as a first principle. This is >>>>>>>>> in >>>>>>>>> contrast to projects like Arrow and Iceberg, which start with >>>>>>>>> well-formed >>>>>>>>> specifications. I've been working with Calcite since before it was >>>>>>>>> an >>>>>>>>> Apache project and I wouldn't recommend adopting it as any form of >>>>>>>>> a >>>>>>>>> specification. On the flipside, I am very supportive of using it >>>>>>>>> for a >>>>>>>>> reference implementation standard for Iceberg view consumption, >>>>>>>>> manipulation, etc. If anything, I'd suggest we start with the >>>>>>>>> adoption of >>>>>>>>> a relatively clear grammar, e.g. the Antlr grammar file that Spark >>>>>>>>> [1] >>>>>>>>> and/or Trino [2] use. Even that is not a complete specification as >>>>>>>>> grammar >>>>>>>>> must still be interpreted with regards to type promotion, function >>>>>>>>> resolution, consistent unnamed expression naming, etc that aren't >>>>>>>>> defined >>>>>>>>> at the grammar level. I'd definitely avoid using Calcite's JavaCC >>>>>>>>> grammar >>>>>>>>> as it heavily embeds implementation details (in a good way) and >>>>>>>>> relies on >>>>>>>>> some fairly complex logic in the validator and sql2rel components >>>>>>>>> to be >>>>>>>>> fully resolved/comprehended. >>>>>>>>> >>>>>>>>> Given the above, I suggest having a field which describes the >>>>>>>>> dialect(origin?) of the view and then each engine can decide how they >>>>>>>>> want >>>>>>>>> to consume/mutate that view (and whether they want to or not). It >>>>>>>>> does risk >>>>>>>>> being a dumping ground. Nonetheless, I'd expect the alternative of >>>>>>>>> establishing a formal SQL specification to be a similarly long >>>>>>>>> process to >>>>>>>>> the couple of years it took to build the Arrow and Iceberg >>>>>>>>> specifications. >>>>>>>>> (Realistically, there is far more to specify here than there is in >>>>>>>>> either >>>>>>>>> of those two domains.) >>>>>>>>> >>>>>>>>> Some other notes: >>>>>>>>> >>>>>>>>> - Calcite does provide a nice reference document [3] but it is >>>>>>>>> not sufficient to implement what is necessary for >>>>>>>>> parsing/validating/resolving a SQL string correctly/consistently. >>>>>>>>> - Projects like Coral [4] are interesting here but even Coral >>>>>>>>> is based roughly on "HiveQL" which also doesn't have a formal >>>>>>>>> specification >>>>>>>>> process outside of the Hive version you're running. See this >>>>>>>>> thread in >>>>>>>>> Coral slack [5] >>>>>>>>> - ZetaSQL [6] also seems interesting in this space. It feels >>>>>>>>> closer to specification based [7] than Calcite but is much less >>>>>>>>> popular in >>>>>>>>> the big data domain. I also haven't reviewed it's SQL completeness >>>>>>>>> closely, >>>>>>>>> a strength of Calcite. >>>>>>>>> - One of the other problems with building against an >>>>>>>>> implementation as opposed to a specification (e.g. Calcite) is it >>>>>>>>> can make >>>>>>>>> it difficult or near impossible to implement the same algorithms >>>>>>>>> again >>>>>>>>> without a bunch of reverse engineering. If interested in an >>>>>>>>> example of >>>>>>>>> this, see the discussion behind LZ4 deprecation on the Parquet >>>>>>>>> spec [8] for >>>>>>>>> how painful this kind of mistake can become. >>>>>>>>> - I'd love to use the SQL specification itself but nobody >>>>>>>>> actually implements that in its entirety and it has far too many >>>>>>>>> places >>>>>>>>> where things are "implementation-defined" [9]. >>>>>>>>> >>>>>>>>> [1] >>>>>>>>> https://github.com/apache/spark/blob/master/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 >>>>>>>>> [2] >>>>>>>>> https://github.com/trinodb/trino/blob/master/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4 >>>>>>>>> [3] https://calcite.apache.org/docs/reference.html >>>>>>>>> [4] https://github.com/linkedin/coral >>>>>>>>> [5] >>>>>>>>> https://coral-sql.slack.com/archives/C01FHBJR20Y/p1608064004073000 >>>>>>>>> [6] https://github.com/google/zetasql >>>>>>>>> [7] >>>>>>>>> https://github.com/google/zetasql/blob/master/docs/one-pager.md >>>>>>>>> [8] >>>>>>>>> https://github.com/apache/parquet-format/blob/master/Compression.md#lz4 >>>>>>>>> [9] https://twitter.com/sc13ts/status/1413728808830525440 >>>>>>>>> >>>>>>>>> On Thu, Jul 22, 2021 at 12:01 PM Jack Ye <yezhao...@gmail.com> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> Did not notice that we are also discussing cross-engine >>>>>>>>>> interoperability here, I will add my response in the design doc here. >>>>>>>>>> >>>>>>>>>> I would personally prefer cross-engine interoperability as a goal >>>>>>>>>> and get the spec in the right structure in the initial release, >>>>>>>>>> because: >>>>>>>>>> >>>>>>>>>> 1. I believe that cross-engine compatibility is a critical >>>>>>>>>> feature of Iceberg. If I am a user of an existing data lake that >>>>>>>>>> already >>>>>>>>>> supports views (e.g. Hive), I don't even need Iceberg to have this >>>>>>>>>> view >>>>>>>>>> feature. I can do what is now done for Trino to use views with >>>>>>>>>> Iceberg. I >>>>>>>>>> can also just use a table property to indicate the table is a view >>>>>>>>>> and >>>>>>>>>> store the view SQL as a table property and do my own thing in any >>>>>>>>>> query >>>>>>>>>> engine to support all the view features. One of the most valuable and >>>>>>>>>> unique features that Iceberg view can unlock is to allow a view to be >>>>>>>>>> created in one engine and read by another. Not supporting >>>>>>>>>> cross-engine >>>>>>>>>> compatibility feels like losing a lot of value to me. >>>>>>>>>> >>>>>>>>>> 2. In the view definition, it feels inconsistent to me that we >>>>>>>>>> have "schema" as an Iceberg native schema, but "sql" field as the >>>>>>>>>> view SQL >>>>>>>>>> that can come from any query engine. If the engine already needs to >>>>>>>>>> convert >>>>>>>>>> the view schema to iceberg shema, it should just do the same for the >>>>>>>>>> view >>>>>>>>>> SQL. >>>>>>>>>> >>>>>>>>>> Regarding the way to achieve it, I think it comes to either >>>>>>>>>> Apache Calcite (or some other third party alternative I don't know) >>>>>>>>>> or our >>>>>>>>>> own implementation of some intermediate representation. I don't have >>>>>>>>>> a very >>>>>>>>>> strong opinion, but my thoughts are the following: >>>>>>>>>> >>>>>>>>>> 1. Calcite is supposed to be the go-to software to deal with this >>>>>>>>>> kind of issue, but my personal concern is that the integration is >>>>>>>>>> definitely going to be much more involved, and it will become another >>>>>>>>>> barrier for newer engines to onboard because it not only needs to >>>>>>>>>> implement >>>>>>>>>> Iceberg APIs but also needs Calcite support. It will also start to >>>>>>>>>> become a >>>>>>>>>> constant discussion around what we maintain and what we should push >>>>>>>>>> to >>>>>>>>>> Calcite, similar to our situation today with Spark. >>>>>>>>>> >>>>>>>>>> 2. Another way I am leaning towards, as Piotr also suggested, is >>>>>>>>>> to have a native lightweight logical query structure representation >>>>>>>>>> of the >>>>>>>>>> view SQL and store that instead of the SQL string. We already deal >>>>>>>>>> with >>>>>>>>>> Expressions in Iceberg, and engines have to convert predicates to >>>>>>>>>> Iceberg >>>>>>>>>> expressions for predicate pushdown. So I think it would not be hard >>>>>>>>>> to >>>>>>>>>> extend on that to support this use case. Different engines can build >>>>>>>>>> this >>>>>>>>>> logical structure when traversing their own AST during a create view >>>>>>>>>> query. >>>>>>>>>> >>>>>>>>>> 3. With these considerations, I think the "sql" field can >>>>>>>>>> potentially be a map (maybe called "engine-sqls"?), where key is the >>>>>>>>>> engine >>>>>>>>>> type and version like "Spark 3.1", and value is the view SQL string. >>>>>>>>>> In >>>>>>>>>> this way, the engine that creates the view can still read the SQL >>>>>>>>>> directly >>>>>>>>>> which might lead to better engine-native integration and avoid >>>>>>>>>> redundant >>>>>>>>>> parsing. But in this approach there is always a default intermediate >>>>>>>>>> representation it can fallback to when the engine's key is not found >>>>>>>>>> in the >>>>>>>>>> map. If we want to make incremental progress and delay the design >>>>>>>>>> for the >>>>>>>>>> intermediate representation, I think we should at least use this map >>>>>>>>>> instead of just a single string. >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> Jack Ye >>>>>>>>>> >>>>>>>>>> On Thu, Jul 22, 2021 at 6:35 AM Piotr Findeisen < >>>>>>>>>> pi...@starburstdata.com> wrote: >>>>>>>>>> >>>>>>>>>>> Hi, >>>>>>>>>>> >>>>>>>>>>> First of all thank you for this discussion and all the >>>>>>>>>>> view-related work! >>>>>>>>>>> >>>>>>>>>>> I agree that solving cross-engine compatibility problem may not >>>>>>>>>>> be primary feature today, I am concerned that not thinking about >>>>>>>>>>> this from >>>>>>>>>>> the start may "tunnel" us into a wrong direction. >>>>>>>>>>> Cross-engine compatible views would be such a cool feature that >>>>>>>>>>> it is hard to just let it pass. >>>>>>>>>>> >>>>>>>>>>> My thinking about a smaller IR may be a side-product of me not >>>>>>>>>>> being familiar enough with Calcite. >>>>>>>>>>> However, with new IR being focused on compatible representation, >>>>>>>>>>> and not being tied to anything are actually good things. >>>>>>>>>>> For example, we need to focus on JSON representation, but we >>>>>>>>>>> don't need to deal with tree traversal or anything, so the code for >>>>>>>>>>> this >>>>>>>>>>> could be pretty simple. >>>>>>>>>>> >>>>>>>>>>> > Allow only ANSI-compliant SQL and anything that is truly >>>>>>>>>>> common across engines in the view definition (this is how currently >>>>>>>>>>> Netflix >>>>>>>>>>> uses these 'common' views across Spark and Trino) >>>>>>>>>>> >>>>>>>>>>> it's interesting. Anjali, do you have means to enforce that, or >>>>>>>>>>> is this just a convention? >>>>>>>>>>> >>>>>>>>>>> What are the common building blocks (relational operations, >>>>>>>>>>> constructs and functions) that you found sufficient for expressing >>>>>>>>>>> your >>>>>>>>>>> views? >>>>>>>>>>> Being able to enumerate them could help validate various >>>>>>>>>>> approaches considered here, including feasibility of dedicated >>>>>>>>>>> representation. >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Best, >>>>>>>>>>> PF >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Thu, Jul 22, 2021 at 2:28 PM Ryan Murray <rym...@gmail.com> >>>>>>>>>>> wrote: >>>>>>>>>>> >>>>>>>>>>>> Hey Anjali, >>>>>>>>>>>> >>>>>>>>>>>> I am definitely happy to help with implementing 1-3 in your >>>>>>>>>>>> first list once the spec has been approved by the community. My >>>>>>>>>>>> hope is >>>>>>>>>>>> that the final version of the view spec will make it easy to >>>>>>>>>>>> re-use existing rollback/time travel/metadata etc functionalities. >>>>>>>>>>>> >>>>>>>>>>>> Regarding SQL dialects.My personal opinion is: Enforcing >>>>>>>>>>>> ANSI-compliant SQL across all engines is hard and probably not >>>>>>>>>>>> desirable while storing Calcite makes it hard for eg python to use >>>>>>>>>>>> views. A >>>>>>>>>>>> project to make a cross language and cross engine IR for sql views >>>>>>>>>>>> and the >>>>>>>>>>>> relevant transpilers is imho outside the scope of this spec and >>>>>>>>>>>> probably >>>>>>>>>>>> deserving an apache project of its own. A smaller IR like Piotr >>>>>>>>>>>> suggested >>>>>>>>>>>> is possible but I feel it will likely quickly snowball into a >>>>>>>>>>>> larger >>>>>>>>>>>> project and slow down adoption of the view spec in iceberg. So I >>>>>>>>>>>> think the >>>>>>>>>>>> most reasonable way forward is to add a dialect field and a >>>>>>>>>>>> warning to >>>>>>>>>>>> engines that views are not (yet) cross compatible. This is at odds >>>>>>>>>>>> with the >>>>>>>>>>>> original spirit of iceberg tables and I wonder how the border >>>>>>>>>>>> community >>>>>>>>>>>> feels about it? I would hope that we can make the view spec >>>>>>>>>>>> engine-free >>>>>>>>>>>> over time and eventually deprecate the dialect field. >>>>>>>>>>>> >>>>>>>>>>>> Best, >>>>>>>>>>>> Ryan >>>>>>>>>>>> >>>>>>>>>>>> PS if anyone is interested in collaborating on engine agnostic >>>>>>>>>>>> views please reach out. I am keen on exploring this topic. >>>>>>>>>>>> >>>>>>>>>>>> On Tue, Jul 20, 2021 at 10:51 PM Anjali Norwood >>>>>>>>>>>> <anorw...@netflix.com.invalid> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Thank you Ryan (M), Piotr and Vivekanand for the comments. I >>>>>>>>>>>>> have and will continue to address them in the doc. >>>>>>>>>>>>> Great to know about Trino views, Piotr! >>>>>>>>>>>>> >>>>>>>>>>>>> Thanks to everybody who has offered help with implementation. >>>>>>>>>>>>> The spec as it is proposed in the doc has been implemented and is >>>>>>>>>>>>> in use at >>>>>>>>>>>>> Netflix (currently on Iceberg 0.9). Once we close the spec, we >>>>>>>>>>>>> will rebase >>>>>>>>>>>>> our code to Iceberg-0.12 and incorporate changes to format and >>>>>>>>>>>>> other feedback from the community and should be able to make this >>>>>>>>>>>>> MVP >>>>>>>>>>>>> implementation available quickly as a PR. >>>>>>>>>>>>> >>>>>>>>>>>>> A few areas that we have not yet worked on and would love for >>>>>>>>>>>>> the community to help are: >>>>>>>>>>>>> 1. Time travel on views: Be able to access the view as of a >>>>>>>>>>>>> version or time >>>>>>>>>>>>> 2. History table: A system table implementation for $versions >>>>>>>>>>>>> similar to the $snapshots table in order to display the history >>>>>>>>>>>>> of a view >>>>>>>>>>>>> 3. Rollback to a version: A way to rollback a view to a >>>>>>>>>>>>> previous version >>>>>>>>>>>>> 4. Engine agnostic SQL: more below. >>>>>>>>>>>>> >>>>>>>>>>>>> One comment that is worth a broader discussion is the dialect >>>>>>>>>>>>> of the SQL stored in the view metadata. The purpose of the spec >>>>>>>>>>>>> is to >>>>>>>>>>>>> provide a storage format for view metadata and APIs to access that >>>>>>>>>>>>> metadata. The dialect of the SQL stored is an orthogonal question >>>>>>>>>>>>> and is >>>>>>>>>>>>> outside the scope of this spec. >>>>>>>>>>>>> >>>>>>>>>>>>> Nonetheless, it is an important concern, so compiling a few >>>>>>>>>>>>> suggestions that came up in the comments to continue the >>>>>>>>>>>>> discussion: >>>>>>>>>>>>> 1. Allow only ANSI-compliant SQL and anything that is truly >>>>>>>>>>>>> common across engines in the view definition (this is how >>>>>>>>>>>>> currently Netflix >>>>>>>>>>>>> uses these 'common' views across Spark and Trino) >>>>>>>>>>>>> 2. Add a field to the view metadata to identify the dialect of >>>>>>>>>>>>> the SQL. This allows for any desired dialect, but no improved >>>>>>>>>>>>> cross-engine >>>>>>>>>>>>> operability >>>>>>>>>>>>> 3. Store AST produced by Calcite in the view metadata and >>>>>>>>>>>>> translate back and forth between engine-supported SQL and AST >>>>>>>>>>>>> 4. Intermediate structured language of our own. (What >>>>>>>>>>>>> additional functionality does it provide over Calcite?) >>>>>>>>>>>>> >>>>>>>>>>>>> Given that the view metadata is json, it is easily extendable >>>>>>>>>>>>> to incorporate any new fields needed to make the SQL truly >>>>>>>>>>>>> compatible >>>>>>>>>>>>> across engines. >>>>>>>>>>>>> >>>>>>>>>>>>> What do you think? >>>>>>>>>>>>> >>>>>>>>>>>>> regards, >>>>>>>>>>>>> Anjali >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> On Tue, Jul 20, 2021 at 3:09 AM Piotr Findeisen < >>>>>>>>>>>>> pi...@starburstdata.com> wrote: >>>>>>>>>>>>> >>>>>>>>>>>>>> Hi, >>>>>>>>>>>>>> >>>>>>>>>>>>>> FWIW, in Trino we just added Trino views support. >>>>>>>>>>>>>> https://github.com/trinodb/trino/pull/8540 >>>>>>>>>>>>>> Of course, this is by no means usable by other query engines. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Anjali, your document does not talk much about compatibility >>>>>>>>>>>>>> between query engines. >>>>>>>>>>>>>> How do you plan to address that? >>>>>>>>>>>>>> >>>>>>>>>>>>>> For example, I am familiar with Coral, and I appreciate its >>>>>>>>>>>>>> powers for dealing with legacy stuff like views defined by Hive. >>>>>>>>>>>>>> I treat it as a great technology supporting transitioning >>>>>>>>>>>>>> from a query engine to a better one. >>>>>>>>>>>>>> However, I would not base a design of some new system for >>>>>>>>>>>>>> storing cross-engine compatible views on that. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Is there something else we can use? >>>>>>>>>>>>>> Maybe the view definition should use some >>>>>>>>>>>>>> intermediate structured language that's not SQL? >>>>>>>>>>>>>> For example, it could represent logical structure of >>>>>>>>>>>>>> operations in semantics manner. >>>>>>>>>>>>>> This would eliminate need for cross-engine compatible parsing >>>>>>>>>>>>>> and analysis. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Best >>>>>>>>>>>>>> PF >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> On Tue, Jul 20, 2021 at 11:04 AM Ryan Murray < >>>>>>>>>>>>>> rym...@gmail.com> wrote: >>>>>>>>>>>>>> >>>>>>>>>>>>>>> Thanks Anjali! >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I have left some comments on the document. I unfortunately >>>>>>>>>>>>>>> have to miss the community meetup tomorrow but would love to >>>>>>>>>>>>>>> chat more/help >>>>>>>>>>>>>>> w/ implementation. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Best, >>>>>>>>>>>>>>> Ryan >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> On Tue, Jul 20, 2021 at 7:42 AM Anjali Norwood >>>>>>>>>>>>>>> <anorw...@netflix.com.invalid> wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> John Zhuge and I would like to propose the following spec >>>>>>>>>>>>>>>> for storing view metadata in Iceberg. The proposal has been >>>>>>>>>>>>>>>> implemented [1] >>>>>>>>>>>>>>>> and is in production at Netflix for over 15 months. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> https://docs.google.com/document/d/1wQt57EWylluNFdnxVxaCkCSvnWlI8vVtwfnPjQ6Y7aw/edit?usp=sharing >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> [1] >>>>>>>>>>>>>>>> https://github.com/Netflix/iceberg/tree/netflix-spark-2.4/view/src/main/java/com/netflix/bdp/view >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Please let us know your thoughts by adding comments to the >>>>>>>>>>>>>>>> doc. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Thanks, >>>>>>>>>>>>>>>> Anjali. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>> >>>>> -- >>>>> Ryan Blue >>>>> Tabular >>>>> >>>> -- >>>> John Zhuge >>>> >>> >> >> -- >> Ryan Blue >> Tabular >> > -- Ryan Blue Tabular
