(Credit: Screen capture by Eric Mack/CNET)
In Google Maps, hover over other the widget in the upper-right corner and you can chose the weather layer from the drop-down to make it visible. To actually see cloud cover and current weather conditions, you'll need to zoom out quite a ways, generally to at least a state-level view.
(Credit: Screen capture by Eric Mack/CNET)
The cloud-cover and other weather visualizations come courtesy of the U.S. Naval Research Lab. Current condition information and the local forecasts that pop up when a certain locale is clicked are provided by Weather.com.
The weather layer doesn't seem to be supported just yet on the Android version (I tried on the latest--recently updated Google Maps 5.9). Here's Google's quick demo: