-
A gentle introduction to CouchDB for relational practitioners
CouchDB is a document-oriented database written in Erlang that addresses a particular “sweet spot” in data storage and retrieval needs. This blog post is an introduction to CouchDB for those of us who have a relational database background.
A CouchDB database doesn’t have tables. It has a collection of documents, stored in a B+Tree. A document is a collection of attributes and values. Values can be atomic, or complex nested structures such as arrays and sub-documents. When you add a document to a database, CouchDB stores it in the B+Tree, indexed by two attributes with special meaning: _id and _rev.
CouchDB lets you store related data together even if it isn’t all the same type of data; you can store documents representing blog posts, users, and comments — all in the same database. This is not as chaotic as it sounds. To get your data back out of CouchDB in sensible ways, you define views over the database. A view stores a subset of the database’s documents. You can think of them as materialized partial indexes. You can create a view of blog posts, and a view of comments, and so on. Each view is another B+Tree. It stays up-to-date with the changes you make to the database.
You can structure your documents anyway you want. There is no fixed schema. If you decide after a while that you want to add tags to your blog posts, you can simply write new posts with a collection of tags and save them into the database. Old posts won’t have tags, but that’s OK; if your application code can read the old format and write the new format, you have an application that doesn’t need a fixed schema.
Updates are never done in-place. Everything is copy-on-write. New revisions are saved into the database as new documents, obsoleting old ones, and CouchDB increments the _rev property each time. To update a document, you fetch it, change it, and send it back, specifying the _id and the most recent _rev. If someone else changed the document in the meantime, your _rev is stale, and your update fails. You must re-fetch and re-save; you can’t lock a document.
CouchDB runs on HTTP and JSON. All of its operations, such as store and retrieve, are standard HTTP requests. The documents themselves are represented in JSON. You can talk directly to CouchDB with curl, Ajax, and anything else that can speak HTTP. There is no “protocol” other than this. CouchDB isn’t just Web-friendly, it is actually made of the same technologies that the Web is made of. You query CouchDB by specifying the database, document ID, view name, and so forth directly in the URL. For example, to fetch a blog post document from the “blog” database, you might issue a GET /blog/helloworld. Queries against views and other objects have simple clean URLs, too.
CouchDB uses special documents, called “design documents,” to store JavaScript code in the database. The code defines the views I mentioned earlier. Another thing you can store is validation functions. This is code that CouchDB executes when you save a document to the database. It accepts a document as input, and can reject it, so you do have control over the schema of documents — it doesn’t have to be a free-for-all. In the blog application, you can have a validation function that starts by enforcing “every document must have a ‘type’ property, and its content must be one of (post,user,comment).” Then you can have separate validation logic for each type of document.
Design documents can also contain something called “show functions.” CouchDB will execute the function’s code in response to HTTP requests to that URL, and send the resulting data back as an HTTP response (as usual). With show functions, you can store entire applications inside the database. Your browser might never even know that it’s talking to a database directly, instead of a web server with a database behind it.
CouchDB isn’t designed for arbitrary queries at runtime. You can only query one view, show function, or database at a time. You can’t do joins. You can’t do arbitrary GROUP BY and ORDER BY. You have to decide in advance what operations you’re going to need, and build views for them. You can then issue requests to those views, essentially the equivalent of key lookups and range scans with a few basic options such as an offset, limit, and reverse order. Now, having said that, you can define views that reduce the database down to aggregates, create a custom ordering, and so on. You can define the equivalent of the relational “project” operation inside your view code.
Here’s how: a view is a map-reduce operation. A view is defined in two parts, the map and the reduce. The map is not optional; it generates the contents of the view. It is a JavaScript function. CouchDB iterates over the database and feeds each document into the function, collects the results, and inserts them into the view’s B+Tree index. Inside the view function’s code, you emit key-value 2-tuples.
The key will identify the tuple in the index that’s built to store this view. It can be simple or complex, so you can create a view that’s keyed by [this,that,the_other_thing]. The view will be ordered by the same thing; that’s how B+Trees work.
The value you emit is whatever you want the B+Tree to store at its leaf nodes, and can also be complex (it’s a document, like any other).
The “reduce” part of the operation is optional. It computes what is stored in the non-leaf nodes of the B+Tree index. For example, you can use it to create aggregates, such as summing up counts of comments. In addition to the reduce part of the code, the is a “rereduce”. The rereduce is called as the operation is invoked on higher and higher non-leaf nodes, all the way to the root of the tree. CouchDB knows how to take advantage of the data that’s stored by these reduce and rereduce operations, so for example, it doesn’t necessarily have to descend all the way to the leaf nodes and scan in order to count how many documents match a particular query.
An important thing to know about all this code is that nothing is allowed to have side effects. You can’t modify the database in a view definition, for example. Documents are immutable; it’s all copy-on-write. You get input; you can specify output; that’s it, period. It’s a form of functional programming. Why do we care? Because it keeps things simple and elegant, and enables all kinds of nice properties and functionality, such as replication and eventual consistency and cache expiry and scaling to multiple nodes and so on.
The database file is append-only. Old versions don’t automatically get cleaned up. The database grows forever until you compact it. This process builds a new database and then does a swap-and-discard. The append-only, copy-on-write design makes backups easy, and data corruption unlikely.
CouchDB comes with a “graphical user interface” called Futon. It’s built right into the database, and surprise! — it works through HTTP and Ajax. You just fire up CouchDB, point your Web browser to /_utils, and go. It’s a fun way to explore CouchDB.
With all that in mind, why would you want to use CouchDB instead of a relational database? For most things I’m involved with, I want a relational database. But I got asked recently to help with a database that’ll store records about people. Although nobody has implemented anything yet, it’s a terrible match for a relational database, and an excellent fit for a document-oriented one. The inputs are going to be arbitrary documents with different structures, such as census records, birth records, tax records, estate and probate records, marriage records, and so on. Nobody knows what it’s going to store in the future. When people build “flexible schemas” in relational databases, they usually go for the so-called EAV or EBLOB models. In other words, they aren’t using the database relationally at all, and it simply doesn’t work well. This type of project needs a document-oriented database.
I’ve left out a lot of important details, but the point of this post is to understand the high-level CouchDB concepts and how they’re implemented, so you can reason for yourself about it. If you’ve read this far and you think that CouchDB might be a good fit for your needs, I encourage you to take a look at CouchDB, The Definitive Guide.
Related posts:NoSQL doesn’t mean non-relationalA review of SQL and Relational Theory by C. J. DateKickfire: relational algebra in a chipAn introduction to InnoDB error handlingHow to use extended properties as documentation with sp_showdoc
-
MySQL GIS – Part 3
TweetWhat data is available?
GEO data is expensive to create, so has been created by governments. In the past governments charged for this data. In 1980 the USGS was charging $300 (usd) per county for Oklahoma GEO data. (I complained to my congressman.) Today, a quick Internet search turns up lots of free GIS data.
I was hoping to find a neat collection of basic GEO data. It would be nice if there was one place you could get world political borders (Polygons), postal codes (Polygons) and points of interest like hospitals and airports. What you can find is lots of lists, often collections of odd data created for a virility of complex political purpose. For example, The Global Change Master Directory is a large list of data sources on earth and climate change, but you will not find the data here.
Remember, as you dig for data there are two types Vector (text) and Raster (pictures). Most sites don’t distinguish between them or combine them for you making them less useful your your own uses.
If you find good sources of GEO data PLEASE share them with me so I can share them with everyone else.
Here is a short list of the sites I found and used to create my test data in my series of posts.
www.cloudmade.com – has shape files for the entire world with administrative, natural, coastline, water and points of interest. The data some from Open Street Maps and is available under the Creative Commons Attribution-ShareAlike 2.0 license.
data.geocomm.com -
gisdata.blogspot.com – Public domain GIS data and Free GIS data repositories and clearinghouses.
collinssoftware.com – http://www.collinssoftware.com/freegis_by_region.htm
census.gov/geo/www/tiger – http://www.census.gov/geo/www/tiger/index.html
http://www.hostip.info/dl/index.html
I live in the state of Oklahoma. Because I know it well, I’m using it for my examples. My search for Oklahoma GIS data turned up these sources. This should give you some idea of the data you might find in your searching. I found county borders (polygons), a list of hospitals (points), city borders (polygons), points of interest (points) and zip codes (polygons). Each of these came as shape files so the process was simple to get the data into MySQL.
libremap.org/data/state/oklahoma/ – View the entire list of USGS Oklahoma Digital Raster Graphic Maps
geo.ou.edu – County Boundary, Municipal Boundaries, Voting Precincts, School Districts, State House Districts, State Senate Districts
tin.er.usgs.gov/geology/state/ – Oklahoma geologic map data
okmaps.onenet.net – 25 digital-map data sets, known as the Digital Atlas of Oklahoma
Adding these to my ‘geo’ database was simple. I ran each .shp file through the ogr2ogr program.
wget http://www.okladot.state.ok.us/hqdiv/p-r-div/maps/shp-files/munibnd.zip
unzip munibnd.zip
ogr2ogr -f "MySQL" MySQL:"geo,user=root,host=localhost,password=" -nln oklahoma_cities -lco engine=MYISAM munibnd.shp
More examples on what you can do with GIS data and MySQL.
Viewing our GIS data.
How to collect your own GIS data.
Good and bad examples of searching GIS data.
Optimizing MySQL GIS. Is it really worth using?
Data sources shared by users.
-
dbbenchmark.com – MySQL (basic) connection pool support added
In this latest release I’ve added a basic MySQL connection pool to the benchmarking script which improves the method in which connections to MySQL are handled and reused. In addition, there have been some optimizations made to the thread handler functions for better debug reporting. Download the latest release now and see how your MySQL server performs against the rest of the community! Download here: download page.
-
2011 MySQL Conferences
Next year will mark a significant change for the MySQL community. At least three major conferences will have dedicated MySQL content that is great for attendees getting the best information on how to use MySQL from the experts in the field.
O’Reilly MySQL Conference & Expo
The 9th Annual MySQL conference will be held at is usual home of recent years. Colin will again be back as committee chair for a 3rd year and this will be my 6th straight MySQL conference.
Date: April 11 – 14, 2011
Location: Hyatt Regency, Santa Clara, California
Website: There is no website at this time
Call for Papers: There are no details for call for papers
Program Chairs: Colin Charles from Monty Program AB and Brian Aker.
Collaborate 11
Collaborate is a larger conference (4,000-5,000 attendees) that is actually three separate conferences in one run by the IOUG, OAUG and Quest. The IOUG content is generally a focus for Oracle DBA’s. Last year marked the first year with any MySQL sessions, and this year Collaborate will have dedicated MySQL tracks chaired by fellow ACE director Sheeri Cabral who is well known for her work in the MySQL community.
Date: April 10 – 14, 2011
Location: Orange County Convention Center West, Orlando, Florida
Website: http://collaborate11.ioug.org/
Call for Papers: Now open. Closes Friday October 1, 2010
Program Chair: Sheeri Cabral
KScope 11
ODTUG Kaleidoscope (Kscope for short) is a conference (1500 attendees) that is very focused on delivering the best content from the top community contributors for the communities benefit. 2010 was my first Kaleidoscope conference and I felt completely at home. Great people, great events and the best conference food I’ve had in many years.
With a dedicated MySQL track in 2010 for the first time I will again be the MySQL Program Chair in 2011 with an extended format for the MySQL developer and DBA. The focus will be the best way to develop successful applications with MySQL and will include Architecture, Performance Tuning, Best Practices, Case Studies and Hands-On streams.
Date: June 26 – 30, 2011
Website: http://kscope11.com
Location: Long Beach, California
Call for Papers: Closes Tuesday October 26, 2010
Program Chair: Ronald Bradford – Independent Consultant
Recap
2010 is also not over. MySQL Sunday at OOW promises to be a great event in San Francisco in under 2 weeks. You can still register at a very cheap price of $75 for 4 dedicated tracks of MySQL content. Open SQL Camp being organized also by Sheeri in Boston in October will continue the tradition of a small but focused and free event for the MySQL community.
-
Cloud systems vs. NoSQL email with tons of questions
I had an email after my webinar on NoSQL/SQL for Oracle Development Tools User Group last week (http://www.odtug.com/apex/f?p=500:1:0) from an attendee that was chock full of some questions. I decided to answer them to clarify with this fellow NoSQL and Cloud Systems. I'm pretty happy with my answers. I'd be glad for any thoughts from people about my replies.Here are my responses (the fellow's name is also Patrick):Patrick,You are welcome! Thank you for attending. I put that together a bit hasty but thought it was a good topic to be covering as there are so many organizations that are considering such an architecture.Patrick Francois wrote:> Hi,>> Thank you for the NoSQL Webinar!> Not an easy theme. ..kind of "wide open".> I have recently also tried getting more info on NoSQL and related systems and performance issues. ...and get confused, especially also with the "cloud systems".Yeah, a lot of buzzword-BS and reinventing the wheel going on. They key to remember is that SQL is not going to be replace SQL any time soon, but the combination of SQL and NoSQL can have its advantages.>> I checked for example this document regarding benchmarking "cloud systems":> (maybe you know that document as well)> -> "BenchmarkingCloudServingSystemswithYCSB" > (YCSB -> Yahoo!CloudServingBenchmark)> -> straight link: http://www.brianfrankcooper.net/pubs/ycsb.pdf>> There they speak about : explosion of new systems for datastorage and management "in the cloud"> They mention there all these different NoSQL storage systems,> and say for example also: Some systems are offered only as cloud services, either directly in the case of Amazon SimpleDB[1]Yeah, SimpleDB being Amazon's S3, a proprietary system. You can download it and run it on your own cluster or private cloud.> ....>> That's why I'm also a bit confused about "cloud systems" and "NoSQL".Cloud systems - usually virtual machines that can be easy spun up for elasticity - I want to add more servers to my virtual network on the fly. This can be via either your own VMWare Labmanager setup or something like EC2. There are also real hardware clouds using services such as what Rackspace offers.By "cloud systems" and "NoSQL", it just means running these databases in an environment such as EC2, VMware, or Rackspace, etc... For instance, you can have relational databases in the cloud such as with MySQL, Drizzle, or etc...You can also run them NoSQL databases on your own systems. NoSQL and Cloud systems are not mutually exclusive of each other.> Basically if you speak about cloud systems, you also speak about scaling out same as you are speaking about "scaling out" when speaking about NoSQL.Scaling out can be in the cloud as well as outside the cloud. It just means running applications and databases over a several systems versus using an every increasing big huge server and growing that server to scale as was done in the Good Old Days (TM).>> So, does it imply that NoSQL systems are cloud systems?No, they can be, but are not. NoSQL means a database system that doesn't use SQL to access data, non-relational> How would you see the relation "NoSQL" / "Cloud systems".They complement each other. NoSQL works well in a cloud paradigm.>> --> Even "cloud system" meaning as such is rather unclear.> There was the question about "Cassandra server farm":> If I create an own cassandra server farm, can I then still speak about a "cloud system", or can I speak about a "cloud system" only when underlying servers are also geographically distrubuted?Geographical distribution isn't the determinant in the definition of cloud system. I could have a cloud system down in my cellar if I wanted to if I have multiple real or virtual servers.>> There is also the "security" issue when it comes to "cloud systems" and some say security is bigger because data is distributed. I understand that in that way, you cannot really get hand (or get hacked) on all the data at once, eventually just a part of the data.> But if I think on an own Cassandra farm, where eventually all machines are in the same machine room and network, I can imagine if you can get into one machine, you can get into all of them.This issue requires you to do some homework about how to secure your servers. Set up a good image with everything locked down and use something like puppet to have it come up with all the goodies you set up in that image. Define a list of must-haves before that box (virtual or real) is on the network.>> From the YCSB-document "some systems are offered only as cloud services",> I understand that for using Amazon's SimpleDB, you could not create your own server farm, rather need to buy that service from Amazon?Yeah, you're not going to run your own S3. You can run other NoSQL or distrubuted file systems - pick your choice with a Google Search.>> That is causing me confusion between "NoSQL" and "Cloud systems".NoSQL - as I defined in the presentation - schema less, often non-relational, doesn't use SQL as the Linga Franca of data access.Cloud systems - multiple boxes, real or virtual, elastic, as I mention above.Two different but mutually complementary concepts.> --> Memcached and Membased sounded very interesting. I will check more on those.Please do. And do join the mailing lists. I see Matt Ingenthron and Perry Krug answering emails every day!> --> Can you eventually also provide the slides you used?Certainly - let me make sure they care clean and I'll send them to you!
|
