Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Append to YXDB

TaraM
Alteryx Alumni (Retired)
Created

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...). 

 

The nature of YXDB files makes them poorly suited for 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. If you want indexes and stuff, you can build them with a separate tool and Alteryx will be able to use them.

 

SQLite is conveniently built into the Alteryx Input and Output Tools:

 

Simply choose SQLite(*.sqlite) from Files of Type...

 

 

2019-04-22_10-26-57.png

 

 

2019-04-22_10-28-50.png

 

 

From both the Input and Output tool configurations, all of the expected options for reading and writing to traditional databases are there - such as specifying a table or query, Pre and Post SQL statements and the output options of Append, Drop, Create and Overwrite.

 

One thing you may notice with working with SQLite databases is that the files are larger than a YXDB. This is due to the fact the YXDB file is compressed (as explained above). 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.

 

This article is based on the comments resulting from an Idea posted here.

Comments
HeliaS
6 - Meteoroid

Very helpful! Thanks

Nick612Haylund
10 - Fireball
10 - Fireball

2+ y/o write-up...but still saving us time! Thank @TaraM