Re: Updates/Deletes/Upserts in Iceberg

I have a new version of the proposal, updated to reflect our discussion. I have misgivings about the elimination of the unique row ID. It makes reading the dataset potentially much more expensive. We

Re: Updates/Deletes/Upserts in Iceberg

It works for me too. .. Owen > On Jul 3, 2019, at 11:27, Anton Okolnychyi > wrote: > > Works for me too. > >> On 3 Jul 2019, at 19:09, Erik Wright wrote: >> >> That works for me. >> >> On Wed, Jul 3, 2019 at 2:01 PM Ryan Blue wrote: >>> How about 9AM PDT on Friday, 5 July then? >>>

Re: Updates/Deletes/Upserts in Iceberg

Works for me too. > On 3 Jul 2019, at 19:09, Erik Wright wrote: > > That works for me. > > On Wed, Jul 3, 2019 at 2:01 PM Ryan Blue wrote: > How about 9AM PDT on Friday, 5 July then? > > On Wed, Jul 3, 2019 at 10:55 AM Owen O'Malley > wrote: > I'd like to call

Re: Updates/Deletes/Upserts in Iceberg

That works for me. On Wed, Jul 3, 2019 at 2:01 PM Ryan Blue wrote: > How about 9AM PDT on Friday, 5 July then? > > On Wed, Jul 3, 2019 at 10:55 AM Owen O'Malley > wrote: > >> I'd like to call in, but I'm out Thursday. Friday would work except 11am >> to 1pm pdt. >> >> .. Owen >> >> On Wed, Jul

Re: Updates/Deletes/Upserts in Iceberg

How about 9AM PDT on Friday, 5 July then? On Wed, Jul 3, 2019 at 10:55 AM Owen O'Malley wrote: > I'd like to call in, but I'm out Thursday. Friday would work except 11am > to 1pm pdt. > > .. Owen > > On Wed, Jul 3, 2019 at 10:42 AM Ryan Blue > wrote: > >> I'm available Thursday and Friday this

Re: Updates/Deletes/Upserts in Iceberg

I'd like to call in, but I'm out Thursday. Friday would work except 11am to 1pm pdt. .. Owen On Wed, Jul 3, 2019 at 10:42 AM Ryan Blue wrote: > I'm available Thursday and Friday this week as well, but it's a holiday in > the US so some people may be out. If there are no objections from anyone >

Re: Updates/Deletes/Upserts in Iceberg

I'm available Thursday and Friday this week as well, but it's a holiday in the US so some people may be out. If there are no objections from anyone that would like to attend, then I'm up for that. On Wed, Jul 3, 2019 at 10:40 AM Anton Okolnychyi wrote: > I apologize for the delay on my side. I’l

Re: Updates/Deletes/Upserts in Iceberg

I apologize for the delay on my side. I’ll still have to go through the last emails. I am available on Thursday/Friday this week and would be great to sync. Thanks, Anton > On 3 Jul 2019, at 01:29, Ryan Blue wrote: > > Sorry I didn't get back to this thread last week. Let's try to have a video

Re: Updates/Deletes/Upserts in Iceberg

Sorry I didn't get back to this thread last week. Let's try to have a video call to sync up on this next week. What days would work for everyone? rb On Fri, Jun 21, 2019 at 9:06 AM Erik Wright wrote: > With regards to operation values. Currently they are: > >- append: data files were added

Re: Updates/Deletes/Upserts in Iceberg

With regards to operation values. Currently they are: - append: data files were added and no files were removed. - replace: data files were rewritten with the same data; i.e., compaction, changing the data file format, or relocating data files. - overwrite: data files were deleted and

Re: Updates/Deletes/Upserts in Iceberg

I don’t see that we need [sequence numbers] for file/offset-deletes, since they apply to a specific file. They’re not harmful, but the don’t seem relevant. These delete files will probably contain a path and an offset and could contain deletes for multiple files. In that case, the sequence number

Re: Updates/Deletes/Upserts in Iceberg

On Thu, Jun 20, 2019 at 3:26 PM Erik Wright wrote: > > > On Thu, Jun 20, 2019 at 12:57 PM Ryan Blue wrote: > >> Sounds like we’re in agreement on the direction! Let’s have a sync up >> sometime next week to make sure we are agreed and plan some of this work. >> What days work best for everyone?

Re: Updates/Deletes/Upserts in Iceberg

On Thu, Jun 20, 2019 at 12:57 PM Ryan Blue wrote: > Sounds like we’re in agreement on the direction! Let’s have a sync up > sometime next week to make sure we are agreed and plan some of this work. > What days work best for everyone? > Tuesday until 2PM PT Wednesday 11AM -> 2PM PT Thursday 9AM ->

Re: Updates/Deletes/Upserts in Iceberg

Sounds like we’re in agreement on the direction! Let’s have a sync up sometime next week to make sure we are agreed and plan some of this work. What days work best for everyone? Here’s a quick summary of the approach from my perspective: - Will define two options for writing delete files that

Re: Updates/Deletes/Upserts in Iceberg

On Wed, Jun 19, 2019 at 6:39 PM Ryan Blue wrote: > Replies inline. > > On Mon, Jun 17, 2019 at 10:59 AM Erik Wright > erik.wri...@shopify.com.invalid > wrote: > > Because snapshots and versions are basically the same idea, we don’t need >>> both. If

Re: Updates/Deletes/Upserts in Iceberg

Replies inline. On Mon, Jun 17, 2019 at 10:59 AM Erik Wright erik.wri...@shopify.com.invalid wrote: Because snapshots and versions are basically the same idea, we don’t need >> both. If we were to add versions, they should replace snapshots. >> > >

Re: Updates/Deletes/Upserts in Iceberg

Hi Ryan, Thanks for providing more clarification on the current workings of the metadata system in Iceberg. I must say that having read the Table Spec document, it's still quite difficult to understand in detail how everything works conceptually. Based on your description below, and particula

Re: Updates/Deletes/Upserts in Iceberg

On Mon, Jun 17, 2019 at 5:51 PM Erik Wright wrote: > That's a really insightful summary of the different proposals that have > been made. My reading of Ryan's suggestion pointed to ways that both > approaches can be supported. The precise mechanisms available > for representing deltas will certai

Re: Updates/Deletes/Upserts in Iceberg

That's a really insightful summary of the different proposals that have been made. My reading of Ryan's suggestion pointed to ways that both approaches can be supported. The precise mechanisms available for representing deltas will certainly differ depending on use-case, but it would seem ideal (an

Re: Updates/Deletes/Upserts in Iceberg

I am really glad to see progress on this! I was off for a couple of days, so could take a detailed look at Erik’s proposal just recently. I would highlight the following features of Erik’s proposal: - Performing certain natural key operations without reading existing data. - Cheap retries if an u

Re: Updates/Deletes/Upserts in Iceberg

Thanks so much for this in-depth response. Analytical and incremental reads are the two use cases that I had primarily been considering. I don't think synthetic keys are necessary for those scenarios, although perhaps they offer a level of performance optimization. The MERGE INTO scenarios are qu

Re: Updates/Deletes/Upserts in Iceberg

First, I want to clear up some of the confusion with a bit of background on how Iceberg works, but I have inline replies below as well. Conceptually, a snapshot is a table version. Each snapshot is the complete table state at some point in time and each snapshot is independent. Independence is a k

Re: Updates/Deletes/Upserts in Iceberg

> On May 21, 2019, at 1:31 PM, Jacques Nadeau wrote: > > The main thing I'm talking about is how you target a deletion across time. If > you have a file A, and you want to delete record X in A, you define delete > A.X. At the same time, another process may be compacting A into A'. In so > do

Re: Updates/Deletes/Upserts in Iceberg

> On May 15, 2019, at 12:54 PM, Ryan Blue wrote: > > 2. Iceberg diff files should use synthetic keys > > A lot of the discussion on the doc is about whether natural keys are > practical or what assumptions we can make or trade about them. In my opinion, > Iceberg tables will absolutely need

Re: Updates/Deletes/Upserts in Iceberg

Thanks for taking the time to read through this and give your feedback. I agree that we are closing in on something here. On Tue, Jun 11, 2019 at 12:36 PM Ryan Blue wrote: > Erik, thanks for working on this doc. It’s a good detailed write-up of the > approach using natural keys and I found the s

Re: Updates/Deletes/Upserts in Iceberg

Erik, thanks for working on this doc. It’s a good detailed write-up of the approach using natural keys and I found the section about efficiently merging changes together to be helpful. For the specific updates to Iceberg metadata, I think we will need to update it. Dan and I were thinking about th

Re: Updates/Deletes/Upserts in Iceberg

Yep, sounds good. Thank you for putting this together, it certainly adds a lot of clarification to the discussion, and opens a clear path forward. +1 from me (or +3 if we add Miguel and Anton) on going ahead with this approach Sent from my iPhone > On 7 Jun 2019, at 21:23, Erik Wright wrote:

Re: Updates/Deletes/Upserts in Iceberg

Hi Cristian, I'm glad to hear that this feels like alignment to you. I agree that it should closely correspond to the option that you described. My intent in writing this out was to clarify some of the details because they were a sticking point in the live discussion. One thing though: guaranteei

Re: Updates/Deletes/Upserts in Iceberg

Hey Erik, I was able to parse your proposal quite quickly since it seems to be pretty much exactly the "lazy with natural key" in our proposed approach. I must say it is however much concretely expressed. This natural key approach is in fact our preferred option too, even though it wasn't exp

Re: Updates/Deletes/Upserts in Iceberg

Thanks, Erik! Great to see progress here. I'll set aside some time to look this over in detail. On Fri, Jun 7, 2019 at 11:46 AM Erik Wright wrote: > I apologize for the delay, but I have finally put together a document > describing an alternative approach to supporting updates in Iceberg while >

Re: Updates/Deletes/Upserts in Iceberg

I apologize for the delay, but I have finally put together a document describing an alternative approach to supporting updates in Iceberg while minimizing write amplification. Proposal: Iceberg Merge-on-Read

Re: Updates/Deletes/Upserts in Iceberg

Yeah, I totally forgot to record our discussion. Will do so next time, sorry. -- Jacques Nadeau CTO and Co-Founder, Dremio On Wed, May 29, 2019 at 4:24 PM Ryan Blue wrote: > It wasn't recorded, but I can summarize what we talked about. Sorry I > haven't sent this out earlier. > > We talked abou

Re: Updates/Deletes/Upserts in Iceberg

It wasn't recorded, but I can summarize what we talked about. Sorry I haven't sent this out earlier. We talked about the options and some of the background in Iceberg -- basically that it isn't possible to determine the order of commits before you commit so you can't rely on some monotonically inc

Re: Updates/Deletes/Upserts in Iceberg

Hi Ryan, I couldn't attend the meeting. Just curious, if this is recorded by any chance. Regards Venkata krishnan On Fri, May 24, 2019 at 8:49 AM Ryan Blue wrote: > Yes, I agree. I'll talk a little about a couple of the constraints of this > as well. > > On Fri, May 24, 2019 at 5:52 AM Anton

Re: Updates/Deletes/Upserts in Iceberg

Yes, I agree. I'll talk a little about a couple of the constraints of this as well. On Fri, May 24, 2019 at 5:52 AM Anton Okolnychyi wrote: > The agenda looks good to me. I think it would also make sense to clarify > the responsibilities of query engines and Iceberg. Not only in terms of > uniqu

Re: Updates/Deletes/Upserts in Iceberg

The agenda looks good to me. I think it would also make sense to clarify the responsibilities of query engines and Iceberg. Not only in terms of uniqueness, but also in terms of applying diffs on read, for example. > On 23 May 2019, at 01:59, Ryan Blue wrote: > > Here’s a rough agenda: > > U

Re: Updates/Deletes/Upserts in Iceberg

Here’s a rough agenda: - Use cases: everyone come with a use case that you’d like to have supported. We’ll go around and introduce ourselves and our use cases. - Main topic: How should Iceberg identify rows that are deleted? - Side topics from my initial email, if we have time: should

Re: Updates/Deletes/Upserts in Iceberg

On Wed, May 22, 2019 at 4:04 PM Cristian Opris wrote: > Agreed with Erik here, we're certainly not looking to build the equivalent > of a relational database, and for that matter not even that of a local disk > storage analytics database (like Vertica). Those are very > different designs with ver

Re: Updates/Deletes/Upserts in Iceberg

Agreed with Erik here, we're certainly not looking to build the equivalent of a relational database, and for that matter not even that of a local disk storage analytics database (like Vertica). Those are very different designs with very different trade-offs and optimizations. We're looking to a

Re: Updates/Deletes/Upserts in Iceberg

> > We have two rows with the same natural key and we use that natural key in > diff files: > nk | col1 | col2 > 1 | 1 | 1 > 1 | 2 | 2 > Then we have a delete statement: > DELETE FROM t WHERE col1 = 1 I think this example cuts to the point of the differences of understanding. Does Iceberg want to

Re: Updates/Deletes/Upserts in Iceberg

Yes, I think we should. I was going to propose one after catching up on the rest of this thread today. On Wed, May 22, 2019 at 9:08 AM Anton Okolnychyi wrote: > Thanks! Would it make sense to discuss the agenda in advance? > > On 22 May 2019, at 17:04, Ryan Blue wrote: > > I sent out an invite

Re: Updates/Deletes/Upserts in Iceberg

Thanks! Would it make sense to discuss the agenda in advance? > On 22 May 2019, at 17:04, Ryan Blue wrote: > > I sent out an invite and included everyone on this thread. If anyone else > would like to join, please join the Zoom meeting. If you'd like to be added > to the calendar invite, just

Re: Updates/Deletes/Upserts in Iceberg

I sent out an invite and included everyone on this thread. If anyone else would like to join, please join the Zoom meeting. If you'd like to be added to the calendar invite, just let me know and I'll add you. On Wed, May 22, 2019 at 8:57 AM Jacques Nadeau wrote: > works for me. > > To make thing

Re: Updates/Deletes/Upserts in Iceberg

works for me. To make things easier, we can use my zoom meeting if people like: Join Zoom Meeting https://zoom.us/j/4157302092 One tap mobile +16465588656,,4157302092# US (New York) +16699006833,,4157302092# US (San Jose) Dial by your location +1 646 558 8656 US (New York) +1 66

Re: Updates/Deletes/Upserts in Iceberg

9AM on Friday works best for me. How about then? On Wed, May 22, 2019 at 5:05 AM Anton Okolnychyi wrote: > What about this Friday? One hour slot from 9:00 to 10:00 am or 10:00 to > 11:00 am PST? Some folks are based in London, so meeting later than this is > hard. If Friday doesn’t work, we can

Re: Updates/Deletes/Upserts in Iceberg

What about this Friday? One hour slot from 9:00 to 10:00 am or 10:00 to 11:00 am PST? Some folks are based in London, so meeting later than this is hard. If Friday doesn’t work, we can consider Tuesday or Wednesday next week. > On 22 May 2019, at 00:54, Jacques Nadeau wrote: > > I agree with A

Re: Updates/Deletes/Upserts in Iceberg

I agree with Anton that we should probably spend some time on hangouts further discussing things. Definitely differing expectations here and we seem to be talking a bit past each other. -- Jacques Nadeau CTO and Co-Founder, Dremio On Tue, May 21, 2019 at 3:44 PM Cristian Opris wrote: > I love a

Re: Updates/Deletes/Upserts in Iceberg

I love a good flame war :P > On 21 May 2019, at 22:57, Jacques Nadeau wrote: > > > That's my point, truly independent writers (two Spark jobs, or a Spark job > and Dremio job) means a distributed transaction. It would need yet another > external transaction coordinator on top of both Spark an

Re: Updates/Deletes/Upserts in Iceberg

I would propose to have a series of sessions over hangouts to clarify all pending points. We can start this week if there is a timeslot that works for everyone. Potential topics (feel free to suggest yours): - Use cases I believe it is critical that everyone is on the same page when it comes to

Re: Updates/Deletes/Upserts in Iceberg

> That's my point, truly independent writers (two Spark jobs, or a Spark job > and Dremio job) means a distributed transaction. It would need yet another > external transaction coordinator on top of both Spark and Dremio, Iceberg > by itself > cannot solve this. > I'm not ready to accept this. Ice

Re: Updates/Deletes/Upserts in Iceberg

Another point to add to my list of clarifications, which I hope will help understand what the document is proposing better: The delete diff files can be simply list of keys to delete from previous snapshots. Natural keys or synthetic keys, it really doesn't matter. This is simple and elegant, i

Re: Updates/Deletes/Upserts in Iceberg

Hi Jacques, > On 21 May 2019, at 22:11, Jacques Nadeau wrote: > > It’s not at all clear why unique keys would be needed at all. > > If we turn your questions around, you answer yourself. If you have > independent writers, you need unique keys. > > Also truly independent writers (like a job

Re: Updates/Deletes/Upserts in Iceberg

> > It’s not at all clear why unique keys would be needed at all. If we turn your questions around, you answer yourself. If you have independent writers, you need unique keys. Also truly independent writers (like a job writing while a job compacts), > means effectively a distributed transaction,

Re: Updates/Deletes/Upserts in Iceberg

Hi Jacques, It’s not at all clear why unique keys would be needed at all. Also truly independent writers (like a job writing while a job compacts), means effectively a distributed transaction, and I believe it’s clearly out of scope for Iceberg to solve that ? > On 21 May 2019, at 21:31, Jacq

Re: Updates/Deletes/Upserts in Iceberg

Synthetic vs natural keys generated a lot of discussion internally when coming up with the proposal :) There is lots of detail and lots of subtelty but a few things that may help explain our thinking: Scale - Iceberg is for remote storage at scale, not local disk. This means mainly operating on

Re: Updates/Deletes/Upserts in Iceberg

It would be useful to describe the types of concurrent operations that > would be supported (i.e., failed snapshotting could easily be recovered, > vs. the whole operation needing to be re-executed) vs. those that wouldn't. > Solving for unlimited concurrency cases may create way more complexity th

Re: Updates/Deletes/Upserts in Iceberg

On Tue, May 21, 2019 at 2:01 PM Jacques Nadeau wrote: > I think we just need to have further discussion about keys. Ryan said: > > 3. Synthetic keys should be based on filename and position > > > But I'm not clear there is consensus around that. I'm also not sure > whether he means lossless inclu

Re: Updates/Deletes/Upserts in Iceberg

I think we just need to have further discussion about keys. Ryan said: 3. Synthetic keys should be based on filename and position But I'm not clear there is consensus around that. I'm also not sure whether he means lossless inclusion, simply derived-from or something else. My thinking before is

Re: Updates/Deletes/Upserts in Iceberg

On Thu, May 16, 2019 at 4:13 PM Ryan Blue wrote: > Replies inline. > > On Thu, May 16, 2019 at 10:07 AM Erik Wright > wrote: > >> I would be happy to participate. Iceberg with merge-on-read capabilities >> is a technology choice that my team is actively considering. It appears >> that our scenar

Fwd: Updates/Deletes/Upserts in Iceberg

Sorry, I didn't mean to reply to only Erik. Here's my response from yesterday. -- Forwarded message - From: Ryan Blue Date: Thu, May 16, 2019 at 1:13 PM Subject: Re: Updates/Deletes/Upserts in Iceberg To: Erik Wright Replies inline. On Thu, May 16, 2019 at 10:

Re: Updates/Deletes/Upserts in Iceberg

I would be happy to participate. Iceberg with merge-on-read capabilities is a technology choice that my team is actively considering. It appears that our scenario differs meaningfully from the one that Anton and Miguel are considering. It would be great to take the time to compare the two and see i

Re: Updates/Deletes/Upserts in Iceberg

Thanks for working on this, Anton and Miguel! Would anyone be interested in scheduling a hangout to talk about next steps and tentative design choices? The doc is a great start and does a good job laying out the trade-offs between different approaches. I appreciate the idea to get a discussion st

Re: Updates/Deletes/Upserts in Iceberg

We did take a look at Hudi. The overall design seems to be pretty complicated and, unfortunately, I didn’t have time to explore every detail. Here is my understanding (correct me if I am wrong): - Hudi has RECORD_KEY, which is expected to be unique. - Hudi has PRECOMBINED_KEY, which is used to p

Re: Updates/Deletes/Upserts in Iceberg

Thanks for putting this forward. Another term for the "lazy" approach would be "merge on read". My team has built something internally that uses merge-on-read internally but uses an "Eager" materialization for publication to Presto. Roughly, we maintain a table metadata file that looks a bit like

Re: Updates/Deletes/Upserts in Iceberg

Hi, As Anton said, we purposely avoided making a "decision" on which approach should be implemented in order to allow for a meaningful discussion with the community. The document starts with an eager approach as it is straightforward and easy to understand: steps resemble the usual file level

Re: Updates/Deletes/Upserts in Iceberg

Thanks for the feedback, Jacques! You are correct, we kept the question of the best approach as open :) The idea was to have a discussion in the community. Hopefully, we can reach a consensus. While the proposed “lazy” approaches certainly offer significant benefits, they require more changes i

Re: Updates/Deletes/Upserts in Iceberg

This is a nice doc and it covers many different options. Upon first skim, I don't see a strong argument for particular approach. D In our own development, we've been leaning heavily towards what you describe in the document as "lazy with SRI". I believe this is consistent with what the Hive commun

Re: Updates/Deletes/Upserts in Iceberg

Awesome. This was on my list for some time. Glad you got it started. On Wed, May 8, 2019, 3:42 AM Anton Okolnychyi wrote: > Hi folks, > > Miguel (cc) and I have spent some time thinking about how to perform > updates/deletes/upserts on top of Iceberg tables. This functionality is > essential for

Updates/Deletes/Upserts in Iceberg

Hi folks, Miguel (cc) and I have spent some time thinking about how to perform updates/deletes/upserts on top of Iceberg tables. This functionality is essential for many modern use cases. We've summarized our ideas in a doc [1], which, hopefully, will trigger a discussion in the community. The