Append to YXDB

It would be nice to be able to append to a YXDB instead of having to read in the whole thing, union the new records, and then re-create it.

Not Planned

Desktop Experience

Category Preparation

Comments

adrian_loong

That would be a great feature as it would make much more efficient to add additional data to a yxdb dataset

anthony

I second this - I need to append data to my data set each month and need a clean way to pull xml, parse and just append to previous month data.

adrianloong

This would be a game changer!

Ned

The YXDB file format is designed for some very specific needs around high speed reading and writing as well as having random I/O (for browsing) and a spatial index (for mapping/spatial join, etc...) This makes it not suited to more database like operations like append or query. When looking for a desktop file format that has database like functionality, SQLITE is the way to go. It is well supported in Alteryx, allows you to append/query etc. And then if you want indexes and stuff, you can build them with a separate tool and Alteryx will be able to use them.

adrianloong

Thanks for the tip ned, we will give SQLite a try today. It's really great.

I think a few of users were trying to read write and then append back to yxdb.

adrianloong

SQLLite works like a charm! Really encourage other users to try this.

mbarone

Thank you Ned! Been wondering about that!

MarshallG

Ned- Thanks for your response and I understand where you are coming from. One difficulty we face (and one that I bet is quite common) is that any setting up of additional software requires IT / admin rights for installation, maintenance, etc. I can't even download SQLite to try it out without IT's involvement. Hence, being able to append to the native Alteryx database would save us a fair amount of startup time.

That said, we will look into SQLite as a way of appending, as this is a function we need. Our files are too large to continue appending as CSV, XLSX, but our organization isn't really big enough to maintain a true enterprise data warehouse.

Ned

You don't need to download SQLite. It is built in to the Alteryx Input and Output tools. No drivers or anything required.

mbarone

Ned - correct me if I'm wrong, but I see about a 3x increase in file size if I go from YXDB to SQLLITE. Does that seem right? My input is simply a YXDB. No tools in between. Just a YXDB input and a SQLLITE output. But the file size goes from about 50MB to 150MB.

Ned

The YXDB file is compressed which makes it smaller. That compression is part of what makes it so you can't append to one. The actual compression will vary based on what data you write to it. SQLite is appendable and queryable which inherently would be very difficult or impossible to support compression.

mbarone

Got it . . . thanks for all the info!

MarshallG

Very cool. Thanks, Ned!

anthony

Nice - glad I checked back in on this thread.

I was using a local mysql instance the last few days so I could insert 200k parsed xml files...1 at a time. It was way to slow doing this with read-union-write yxdb method, so I started using mysql and hated the fact I needed to add another technology to the stack.

This will work great to build my data and then export as a yxdb for faster down stream processing for other processes.

RachelW