Interesting Data Links, Week Ending 2015-06-27
The most interesting data and database-related articles to come my way this week.
Oracle’s biggest database foe: Could it be Postgres? via Postgres Weekly.
Oracle has such a huge head start, I doubt that PostgreSQL is really a threat. Still, I’m glad to see PostgreSQL so popular among start-ups.
Don’t Let Your Data Out of the Database
Another excellent post by Pat Shaughnessy.
Introducing HypoPG, hypothetical indexes for PostgreSQL
Being able to test the usefulness of an index without having to create it on disk is a fantastic tool for developers. Over-indexing can be as bad as not indexing at all.
My Latest Post at “Engineering Copyright & Content Solutions”
I recently wrote a post for the Engineering blog at my employer. “Using Talend Big Data to Move Data from MongoDB to PostgreSQL” describes ETL from a NoSQL data store, in this case, MongoDB, to a traditional relational data store, PostgreSQL.
Slides from my MongoDB Boston 2013 Talk “No More SQL”
MongoDB was kind enough to ask me to present at MongoDB Boston 2013 last Friday (2013-10-25). Below are the slides from my talk, entitled “No More SQL”.
I spoke about the experience at my workplace while moving from a 2+ TB relational database to a MongoDB cluster (twelve shards). My hope was to convey some of the challenges we encountered and the lessons we learned while working on this project.
Make Your Relational Database Self-Documenting
Have you ever had to work with a legacy relational database and found that there was little, if any documentation about the existing tables and columns? In my career, this has been a common problem for me, especially when coming up-to-speed on an existing application’s database. It’s bad enough when there is no ER diagram to give you an overview of the database, but many database developers do everything in their power, it seems, to make the database itself opaque.
What are some best practices for making your database easier to understand?
Make Your Table and Column Names Long Enough to be Descriptive
Instead of something like CSTACCT, use a name like CUSTOMER_ACCOUNT. From a self-documenting perspective, there is very little point to using short, cryptic names, when you may have 30 characters (Oracle) or as many as 63 characters (PostgreSQL) to work with.
Establish Abbreviation Standards
When you must abbreviate names, use consistent abbreviations, and maintain a list of approved abbreviations that all database designers and developers may use. Enforce the standard abbreviations through code reviews.
Be Consistent with Table and Column Naming
Another practice that makes for difficult-to-understand databases is the use of inconsistent naming conventions. Will your “flag” columns be named “completed_yn”, “completed_flag”, or “completed_ind”? It probably doesn’t matter which convention you adopt, as long as you use it consistently throughout your database.
Use Table and Column Comments
Most RDBMs today allow you to store comments right in the database itself. This makes it easy to generate a data dictionary. Adopt a coding standard for your data definition SQL that requires table and column comments (sometimes called “remarks”).
Create a Data Dictionary
Whenever you’re responsible for database design or maintenance, publish a data dictionary. There are expensive tools like ErWin that make this easy, but I like to use the free and open-source SchemaSpy. It’s critical that you keep the data dictionary up-to-date, so be sure to publish a revised copy whenever there are revisions to your database schema.
What Do You Gain?
By adopting the practices I’ve described, you’ll gain the powerful ability to help others understand your databases and the data itself. In summary:
- New developers can more easily understand where data is stored in your databases.
- Other teams, outside the software engineering department, can find the data they’re interested in, whether for ad hoc queries or for regularly-scheduled reports.
- The ETL logic for migrating data, when moving to a new system, is much easier to develop, if all existing data is clearly described.
- Along the same lines of data integration, it will be straightforward for ETL developers to identify source and target database elements.
Book Review: MongoDB Applied Design Patterns
MongoDB Applied Design Patterns is a book that I will read again. I generally don’t say that about technical books, but the strengths of this work are such that many parts merit a second reading.
This book is for folks with some experience using MongoDB. If you’ve never worked with MongoDB before, you should start with another book. Python developers, in particular, will benefit from studying this book, as most of the code examples are in that language. As long as you have some object-oriented programming experience and have worked with the MongoDB shell, though, you’ll have little difficulty following the code examples.
Another group of people who will strongly benefit from this book are those with only relational database experience. The author does a thorough job, particularly in the early sections of the book, of comparing MongoDB with traditional relational database management systems.
I particularly liked the author’s discussion of transactions, in chapter 3. The example is complex, and not a simple debit-credit discussion. You understand through this example that you must write your own transaction management when you give up using a relational database system. To me, this is an important point, and I’m glad that the author spends so much time on this example.
Some of the use cases presented are similar to those in the MongoDB manual, in particular chapters four, five, and six. The remaining use cases go beyond what is described in that manual. All of the discussion in these use cases is thorough. There is typically an explanation of the data model (schema design) and then of standard CRUD operations. The author also goes into not-so-typical operations, like aggregation. I was particularly pleased that each use case includes sharding concerns.
In summary, I highly recommend this book. It’s great to see MongoDB being adopted for so many different uses.
MongoDB for DBAs Course Impressions
I recently received my final score from 10gen Education’s MongoDB for DBA course. I’m pleased to report that I scored in the top tier. 10gen even said that I am “awesome”.
I wanted to give my impressions of the course and to encourage as many folks who are interested to take the free training that 10gen is providing. The next DBA course begins on April 29, and the MongoDB for Developers course (for Java developers) starts on May 13.
Course Format
There are six weeks of lectures, and a seventh week for a final exam, which is really a hands-on project.
Each week is divided up into six to twelve video “lectures”, of varying length. Some of the lectures are only three or four minutes long, though some are as many as fifteen to twenty minutes. Most of the video lectures are followed by one or two quizzes. These quizzes are almost all multiple-choice questions. You’re given up to three chances to get a quiz answer correct, and can even peek at the answer before submitting your solution. While this may seem like a way to cheat, you’re really cheating yourself if you make no attempt to answer the quiz questions honestly. If you don’t understand the material in the lectures, you will not be able to complete the homework. Think of the quizzes as a way of checking your understanding of the video lectures, and re-watch any lectures where you found the quiz difficult.
There are typically four or five homework problems per week. These are primarily not multiple-choice, but worked problems that require you to actually perform various operations with MongoDB. If you haven’t mastered the material in the lectures, you will not complete the homework successfully, as the problems are not trivial.
The Less Good
Like most MOOCs, this is no substitute for an instructor-led class. You can’t ask questions of the instructor in real-time, but only through the course message board.
The quizzes are somewhat simplistic. This may be somewhat attributable to the test engine that 10gen used (edX). It would be helpful to students if the questions weren’t multiple choice, and required a bit more understanding.
I found myself referring to the excellent on-line documentation for MongoDB to fill in gaps that were left by the lectures themselves. Many weeks I found myself wanting more information than the lectures provided.
The Good
It’s free! 10gen is very wise to offer this training free to the community. The more folks who know how to use MongoDB, the better it is for MongoDB and 10gen.
The DBA course is taught by one of the founders of 10gen, Dwight Merriman. To have someone at that level spending precious time on instruction tells me that 10gen clearly values building up its user base and community.
The homework assignments really test your understanding of the material. 10gen was very ingenuous in making it difficult to cheat on the homework questions. I think the homework is the best part of the course, actually. I’ve referred back to homework questions several times to help me solve a problem at work. The course would be even stronger if similar effort had been put into the quizzes.
I’m really grateful to 10gen for making this training freely available. I was so impressed by my experience with MongoDB for DBAs that I’ve registered for the MongoDB for Java Developers course (M101J) , which begins on May 13. Can’t wait!
A New Year and New Technology
Happy New Year!
With a new year here, I’ve been thinking about ways to expand my skill set and the technologies to learn more about in 2012. As I’m still a data guy at heart, it should be no surprise that the technologies that interest me are related to data and databases.
MongoDB
I hoping to do a lot of work with MongoDB this year. In the past, I’ve played around with it some, but it looks as if there will be at least one project at work that will let me get some deep experience using MongoDB. I’m currently reading “MongoDB: The Definitive Guide” by Kristina Chodorow and Michael Dirolf and am really enjoying it. Based on what I’ve read so far, I would say this is one of those classic O’Reilly books: specialized, but so well-written that you almost forget that you’re reading something highly technical.
Hadoop
I attended a number of talks at QCon SF back in November and one topic that recurred frequently was how companies are using Hadoop. It seemed as if every presenter described how their company is finding a way to use at least part of the Hadoop ecosystem. And Hadoop is truly that: an entire ecosystem, encompassing not only the core project, but also Pig, ZooKeeper, Mahout, HBase, and still others. You can find more information at the Apache Hadoop project page. I’m hoping to get a proof-of-concept cluster up and running by the middle of the year.
Lucene/Solr
Full-text search technology falls into an area that, like MongoDB, I have had some exposure to in the past, but would like very much to learn more about. I’m planning to spend some time this year on Lucene first, and then move onto the search server, Solr. I’m fortunate to have some colleagues with a lot of experience in this area, and I intend to mine their knowledge whenever possible.
XML
I believe that those of us steeped in the relational database world can take a great step toward a more data architect mindset by having a deep understanding of XML and its related technologies. My plan this year to is to get a firmer understanding of XML, XPath, XSLT, XML Schema, etc. I’ve done quite a bit of work with XML in the past, but this has typically been in relation to Oracle’s handling of XML documents within the database. I want to gain a broader understanding.
Of course JSON is coming on strong in supplanting XML in some of its previous strongholds. For example, MongoDB’s data model relies on JSON-formatted documents.
This seems like enough to keep me busy outside of my day job for one year!
