Bethpage Red--it's not everyone's first choice; it's a straight hole, 471 yds; downhill tee shot, not necessarily 'pretty' then to an uphill green. What I think is most exciting about it is, hitting the fairway--the ball seems to hang in the air forever, and a good drive will leave the player with a mid-iron in (I've hit 6i into that hole) and a good chance to make par or birdie.
On a hole of this length, especially as the opening hole, you feel as if you can hit the fairway and the green, get a par, it's going to be a very good round.
Conversely, if you don't hit the fairway, it's going to take some work to make a 5.