Databases Need Extraction

Databases are traditionally awful at managing unstructured data, such as office documents, media files, or large blocks of text. Yet users still want to store documents in them. The reason is that documents often have associated structured data already in the database. For instance, an MP3 has an embedded artist, album, and title, which are typically mirrored in a database so they can be queried. Likewise, a resume may be a Word document, but the applicant's name and contact information were probably typed into a form and stored in a table.

Managing documents and their associated structured data separately is painful. Just consider the common approach of keeping the file in the file system and storing its path in the database. What if the artist embedded in the MP3 changes? Or the file is moved, or deleted? Issues like consistency control, synchronizing backups, queries over both structured and unstructured data, and even supporting multiple systems can become a nightmare. So people start putting documents in the database.

This is a call to arms: as database people, if there's a compelling reason for users to store data in a database, the database should help manage it. So what's involved in managing unstructured data?

At a high level, there are two types of management tasks over unstructured data:

1. Managing the bits: let users efficiently insert, update, and delete documents, as well as seek within them, stream them, etc. This includes other typical data services, such as backup and restore.
2. Managing the information represented by the bits: an MP3 has an artist, beats-per-minute, and lyrics, while a resume mentions schools, companies, and skills. To fully manage such documents, users need to query this information.
   

Recently, databases have gotten better at managing unstructured bits. For example, SQL Server has FILESTREAM, which improves streaming performance, and Remote Blob Storage, which stores unstructured data in a dedicated file server.

However, managing the information represented by the bits requires extraction. Specifically, it requires cracking open the document, extracting or inferring interesting content, then exposing it as queryable structured data. This means extraction is not a side task; it is an integral part of managing unstructured data.

Full-text search in databases is a step in this direction, but there's a lot more work to do. When we put MP3s in a table, we should be able to query them by artist, title, or even lyrics. When we store a resume, we should be able to find related job descriptions, or join the schools it mentions to our Employee table to find old classmates that work here.

It's estimated that about 80% of the data out there is unstructured, and thus probably not in databases. If we're going to take a serious shot at managing it, then databases need extraction.

Read more... Collapse post...

SQL PASS 09 - Semi-Structured Data in SQL Server

A couple coworkers and I just had the great opportunity to present a post-conference session at SQL PASS 2009. The session topic was "Managing Unstructured and Semi-Structured Data with SQL Server"; I own the semi-structured features in SQL Server, so I presented that part of the session.

For simplicity, think of "semi-structured data" as structured data that's not relational. To manage semi-structured data, SQL Server 2005 introduced XML, which is powerful and general (as someone once said, tongue in cheek, "XML is like violence: if it doesn't solve your problem, you're not using enough of it"). However, XML isn't ideal for certain situations, and doesn't integrate perfectly with relational databases.

Enter SQL Server 2008, which introduces some really useful features for managing semi-structured data more relationally. Briefly, if you find yourself managing the following kinds of data, take a look at these features:

• Trees or hierarchies (e.g., product categories, org charts, conversation threads): check out hierarchyid, a new type representing nodes in a tree.
  
• Properties that apply to only some items (e.g., product descriptions, custom document properties, tagging): check out the SPARSE column option, which gives you NULLs that take no space, constant-time addition of new columns, and up to 30,000 columns in a table.
  
• Tables where only some rows need indexing (e.g., columns with many NULLs, "hot" rows, heterogeneous row groups): check out filtered indexes, where you specify which rows are indexed. This makes the index much smaller, yields faster look-ups, and avoid unnecessary index maintenance. Filtered indexes are cool enough that I'll make a post about just them sometime soon.
  

For those interested, here's a slide deck and demo T-SQL code on these features, focusing on how you can use them to solve real-world problems. If you have any feedback or suggestions — about the deck, the demo code, or the features themselves — please post a comment. For example, here are some of the feedback and takeaways I got from attendees at SQL PASS:

• One attendee wondered how hierarchyid compares to left-right value tables (a.k.a. nested set model, or modified preorder traversal) for representing trees. Briefly, hierarchyid has the same sub-tree properties, so is equally good at descendant queries. Furthermore, it has three advantages: more compact (one column averaging 10 bytes per value can represent ~100M nodes); constant-time node insertion anywhere in the tree; and easier, more efficient level queries (e.g., (grand)children of a node, all nodes with level 3).
  
• People seem to like column sets, especially how they automatically shred XML inserts and updates into the proper sparse columns.
  
• People also seem to really like filtered indexes. One "wow" example was choosing which rows were worth indexing based on your workload. To illustrate, suppose table SoldAt tracks which products are sold at which stores. If only some stores have enough traffic to warrant indexing, you can create an index for just those stores with CREATE INDEX SoldAt_Key_Stores ON SoldAt(...) WHERE StoreId IN (1, 4, ...).
  
• Some people mentioned that they often need to manually update filtered statistics (such as are generated automatically for filtered indexes) to ensure good plans. This is something we can look into.
  

I'd love to hear more, so please comment away!

Read more... Collapse post...

Nice thing about PMing at SQL Server

Fair warning: shameless plug ahead.

Groups within Microsoft vary widely, including everything from the culture, to the engineering process...even the acronyms. Makes it hard to paint the whole company with one brush.

For instance, take SQL Server. We're not the 500lb gorilla; that's Oracle. In the database arena, we're one of the scrappy underdogs, and it shows in the culture. At the same time, our division is one of Microsoft's established workhorses; we're not an experimental foray into a new business. That also shows in the culture.

It's an interesting combination. It gives us constant drive and motivation, but directed by a maturity that comes from knowing we impact the company's bottom line. As a PM, I really feel both of these factors. I'm always encouraged to think about differentiating, and solving the biggest customer pain points. At the same time, I know we're working on cool stuff that will actually make a difference.

Read more... Collapse post...