There's a lot of hype around certain technologies, and I suspect it does not reflect reality. I've used hadoop+pig a few years ago and it was absolutely terrible.
The debug cycles are so much faster with sql queries.
Redshift seems like a great fit for Adhoc analytics. Just a shame you can't run it in your own data centre (some of us can't use the cloud).
Dream startup would be to make the day to day life of running a cluster more manageable. If I had an app that could tell me how to optimize queries, tune the parameters and make the what/why/where/how questions easier I would buy it instantly.
But if you are stuck in SV's echo chamber it's all MapReduce/Hadoop/Spark/etc.