On 4 March 2016 at 18:03, Greg Keogh <[email protected]> wrote: > Folks, anyone using Azure Tables Storage in anger? I really like it, simple > and effective. > > What is the query syntax equivalent of SQL "not null", that is, a row has a > named property? I have a table with tens of thousands of rows, but only a > small percentage contains a property value named ErrorMessage, and I want to > select them only. Going ErrorMessage neq "" works but it's too ugly to > believe there isn't a better way.
On 6 March 2016 at 17:26, Thomas Koster <[email protected]> wrote: > OData has a "null" literal, but I don't know if they have it in Azure > Tables (I have not used it "in anger"). > > Have you considered including something in the RowKey so that you can > distinguish these rows from the rest with a range query instead? On 7 March 2016 at 10:08, Greg Keogh <[email protected]> wrote: > OData may have a "null", but ATS certainly doesn't have the concept, or a > schema, so I'm sympathetic to the fact that IS NOT NULL will not have any > kind of direct efficient equivalent. This page on MSDN on how to write ATS queries directed me to the OData spec in several places: https://msdn.microsoft.com/en-au/library/azure/dd894031.aspx I guess they forgot to tell me which parts of OData they decided to ignore. On 7 March 2016 at 10:08, Greg Keogh <[email protected]> wrote: > However ... I need some way of finding > all the rows with an ErrorMessage property. If there is no way of doing this > without a full table scan, then it hints that I'm using the facility > inappropriately, in a "relational" way that it doesn't support. > > My logs RowKey is full of the timestamp, so unfortunately there's no room > left to squeeze a flag into it. Which opens up the question of how much > information you can squeeze into the Partition and Row keys in a readable > and searchable way. It becomes a technical quiz at times to figure out how > to use the pair of string keys effectively. You can finish up putting all of > the row data into the keys! With NoSQL databases, you must not fear duplication if you want more than one query to run efficiently (better than linear time). This is a trade-off you have full control over, which is a good thing. A covering index in a relational database also duplicates data to avoid row-id lookups. This is not much different. It sounds like your rows are small and immutable - perfect. Store the rows with errors twice: once with the usual key and once again with a different key suitable for this query. Make sure the keyspaces don't overlap. e.g. If your key was "yyyyMMddHHmmssfff-nnn" before, try these: all-yyyyMMddHHmmssfff-nnn error-yyyyMMddHHmmssfff-nnn Insert all rows with the "all" key. Insert rows with errors with the "error" key as well. Say that 10% of your data (by size) is in rows that have errors. Then your database just grew by 10%, at most [1][2]. This is not much - the overhead of using XML as a serialization format is far greater, for example. But you have transformed a linear query into a logarithmic query [3] which will scale as you do into the millions and billions of rows. If very few rows have errors, this "secondary index" will be very selective - also good. Don't bother with this if a large proportion of your rows have errors. Linear scans can outperform unselective indexes (this is true of all indexed data, not just NoSQL). Run benchmarks if you think it is "close". By the way, all this is basically what a CouchDB view does, except that CouchDB takes care of all of this for you, even when rows (documents) are modified. A CouchDB view is like a persisted, indexed view, except that you have far more flexibility with the choice of key and value. I have not yet found a cheap and easy way to deploy CouchDB to Azure, though. [1] You may not have to store *all* the properties in the duplicates; just store what you need and no more. [2] This is excluding the growth of the branches in ATS's primary index, which I assume is some kind of radix tree that should grow very, very, very slowly. [3] Probably logarithmic or better. Does anybody know if MS documents the actual time complexity bounds of various operations in ATS? I would think this is crucial, fundamental information. -- Thomas Koster
