Hadoop isn't a program so much as a software ecosystem. It contains a framework for the distributed running of map-reduce jobs over huge sets of data, usually read in from the distributed filesystem called HDFS. The real win of Hadoop is that you take the computation to the data rather than moving the data to the computation. The goal of Hadoop is to reduce and distribute I/O load. Hadoop also takes care of some fault tolerance and ensuring your jobs always complete quickly, even if you have a bad node in your cluster.
MapReduce aims to solve problems where you can easily split up data. Take perhaps the entire Gutenberg library. You can easily break these down upon word boundaries, or even chapter boundaries if you're smart enough. The idea being, you rely on associative/commutative operators as much as possible so you can shard the work infinitely then reduce it later.
As for MapReduce, in the WordCount example, say we do a frequency analysis on words with a length greater than four. In our Mapper, it's handed a set of data. You split your paragraph(s) on word boundaries and then emit a key,value pair which will later be sent to a reducer. So if we got the word "hadoop", we'd emit ("hadoop", 1) as we'd seen it once. We do this emission for every word greater than four characters long. These are then sent to the reducers. Each reducer takes charge of a particular key, so all the instances of "hadoop" go to a specific reducer. Since we emitted a bunch of ("hadoop", 1) keys we can then collapse these down into something like ("hadoop", 17) and have a single key value pair.
More or less, a Mapper is a transformation/filtering function. It doesn't have to be, but that's the general use case.
Apache Hive/Pig are simply abstractions which use MapReduce jobs to run queries over large sets of data, especially log files. They're a great way to extract analytics from real-time logging data from a cluster of webservers.
You can run this locally. Check out the Cloudera CDH releases, it can get you started almost instantly. By default, the Map-Reduce jobs run on your machine in series. You can configure a pseudo-distributed cluster on your local machine to take advantage of multiple processors, but the LocalJobRunner is built to test your ability to break up data and get the right answer, not for legitimate job running.
Cloudera also just released a SCM Express package which can let you install a cluster of up to 50 machines in almost no time flat.