Alternative to EAV for dynamic fields in a star schema data warehouse

Question

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against them in the future (so it is not just storage, so I can't use blob for them)

e.g. http://example.com/?action=test&foo=abc&bar=def...

I need to store all the field => value mappings, i.e. (action => test), (foo => abc), (bar => def), and since the field is such dynamic, the only solution I have found is to use Entity-Attribute-Value, however, people keep saying it is a very bad design.

So, consider my use case above, what would be a suitable alternative to EAV?

My current schema using KAV

1. Table requests
(id, timestamp, uri)
   e.g. (1, 149382220, '/')

2. Table params
(request_id, key, value)
   e.g. (1, 'action', 'test'), (1, 'foo', 'abc'), (1, 'bar', 'def')

Any suggestions?

Update: We run the warehouse on AWS RedShift

Answer 1

I can think of three solutions - EAV, XML, and Sparse Columns. The latter is vendor-specific and may not be useful to you.

Whichever method you choose, you may wish to consider storing the original request data in a raw format, in a table or flat file. It will make it easy to try new ways of storing the data, allow you to reload data if you discover a mistake with the way you're parsing your requests, and offer opportunities for parsing the API requests using batch processing or "big data" tools if you find that your data warehouse isn't able to efficiently deal with the data.

EAV considerations

EAV/KVS, as you've described it above, is likely to be the most straightforward implementation.

Unfortunately it's also going to be very expensive - to get any sort of efficient queries on commonly used keys you'll need to have indexes on the key column, which could get very fragmented. Querying for particular keys would be extremely expensive.

You may be able to reduce the cost of indexing or index scans by supporting your EAV store with materialised views (many vendors support this) for querying keys or values that you care about.

XML

Most enterprise database systems offer very mature XML handling, including validation, indexing, and sophisticated querying.

Loading the API request into the database as XML would provide one tuple per request, which logically might be a bit more palatable to you than having an unknown number of rows in an EAV table.

Whether this is efficient would depend a lot on your RDBMS vendor and your implementation.

The biggest downside is that this is probably the only way of managing data that's more complicated than string manipulation of the original request!

Sparse Columns / traditional tables

It's possible that you could load your data into a traditional table structure, with one column per key.

SQL Server's Sparse Columns feature is a great alternative to an EAV store. A table with Sparse Columns behaves much the same as a normal table, except that it can have up to 30,000 columns, and NULL values in sparse columns consume no space in the table.

Combining them with Filtered Indexes (another SQL Server specific feature) can provide an extremely efficient alternative to an EAV store if you're frequently querying for a couple of specific columns and/or values.

Using a traditional table with other vendors may be viable - IBM supports over 700 columns per table and Oracle about 1000, and features such as compression or Oracle's treatment of trailing nulls might mean that you can store your API data fairly efficiently.

The obvious downside of this approach is that as you added new keys to your API, you'd need to adjust your schema accordingly.

Answer 2

EAV is not a bad design, per se, it is simply a design that requires a fair amount of forethought and can be wrought with performance issues as the quantity of data rises. It may be that for your system, it would work well.

When I designed a system for storing query strings, I had no idea in advance what fields I would be interested in. I created a table to store the query string in serialized binary format, and built a system that allowed me to split apart the query string into its component pieces once I knew the pieces I was interested in. From there I created a set of tables; one each for the sets of data commonly contained within the query string.

For instance, I eventually had a table for referrer data, one for target request data, and one for user-related items such as the search query they entered.

I found the ability to store the entire query string in a single table as a blob, while providing the ability to split that blob apart in future, met my needs very well.