13

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

  • 2
    What's wrong with trying what you are suggesting on a dev database? Also, are you talking about SQL Server? The sql tag is pretty broad. – Max Vernon May 7 '14 at 19:48
  • Updated my question – Howard May 8 '14 at 3:43
  • 1
    Which DBMS are you using? Some have pretty good text indexing capabilities, so I wouldn't rule out using a "long text" field to store requests. Having said that, I wouldn't have a problem using the model you propose. While EAV in a strict sense, it's only being used for this very specific purpose. Again, having said that, what sort of queries do you need to be able to do? Try and write these queries against this model to see if it works for you. – Colin 't Hart May 10 '14 at 7:15
  • 1
    What RDBMS are you using? SQL is not specific enough. You have been asked twice. I am the third. – Erwin Brandstetter May 11 '14 at 22:19
  • 2
    Since RedShift is based on PostgreSQL, I would try to use the hstore or json datatypes (or jsonb if/when they "upgrade" to 9.4). – Colin 't Hart May 16 '14 at 12:45
11
+50

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.

| improve this answer | |
  • 2
    In PostgreSQL I wouldn't recommend XML but either hstore or json. In the coming 9.4 jsonb would be my recommendation. – Colin 't Hart May 10 '14 at 16:40
  • I really like this answer with the pros cons and explanation of each. Very informative - I definitely appreciate the Sparse Columns info . I would like an example of EAV using the sparse column approach. – StixO Jan 15 at 4:44
9

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.

| improve this answer | |
  • 1
    In both the question and the answer the term BLOB is used which means Binary Long OBject. I would prefer to use a CLOB (Character Long OBject) or something like text in PostgreSQL, since we are talking about character and not binary data. – Colin 't Hart May 10 '14 at 7:17
  • 2
    I used a binary field since I actually serialized the entire session object and stored the entire thing in the database. – Max Vernon May 10 '14 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.