There are four major types of acidity found in grapes/wine. Tartaric acid is (generally) the most prevalent; think "cream of tartar." Next is malic acid, the same type of acidity one finds in apples. Lactic is third, and this is associated with milk, of course. Finally, there is citric, although grapes are not a "citrus fruit" in the same was as an orange or a lemon.
"Sour" certainly involves some elements of acidity, to be sure, but sourness is associated with underripe fruit, be it grapes, an apple, an orange, etc. Bite into an unripe grape, apple, or orange, and it not only is sour, but it also doesn't taste much like a grape, an apple, or an orange. But bite into a ripe piece of fruit, and not only is it not sour (yet there still acidity present), but the ripe flavors are also there!
A sour wine is, in my experience, nearly always produced from underripe fruit, OR from improper acid adjustments using citric acid.
There are all sorts of different "balances" in a wine. The most common one to speak of is the "sugar-acid balance." Acid balances sugar.
Have you ever had a soft drink from a soda fountain dispenser (as opposed to having it from a bottle or can)? Ever have one where the mixture of syrup to carbonated water wasn't quite right? The syrup has lots of sugar. Carbonated water is water infused with carbonic ACID gas. If there isn't enough acid, that cola, root beer, whatever seems too sweet, and you feel like reaching for a glass of water . . .
Acidity is also responsible for the wine's "structure," its "backbone." If a wine doesn't have enough acidity, it can be "flabby." Too much acidity, and it can seem shrill and harsh.
Experiment: go to a drug store and buy a small bottle of citric acid. Take a California Chardonnay and a California Riesling . . . pour a glass of each per person; then take the rest of the bottle, and add some citric acid to the wine (if it's in tablets, grind them up first so they will dissolve more rapidly). Then pour a second glass for each person. Taste the two wines . . . taste the difference.
Balance involves acidity, flavors like fruitiness, tannins and alcohol. The most common time people notice when wine isn't balanced is when there is alcohol showing. But wine can have a high alcohol level and not show it because the alcohol is balanced by other flavors.
How much acidity is appropriate is a hard question to answer. Generally, if you get too much acid the wine demands food, because acidity is a good match for food.
A good current example for me is a Basque rose like Ameztoi Rubentis. This is a really high acid wine. A little spritz, too. I love the stuff. I have a wine geek friend who can't stand it. It's not that good to drink alone because of the acid, but it matches wonderfully with food. At least I think so.
Not sure that fruit and acid work at odds with each other. But let's look at this from a non-wine angle as a means of illustration that's less expensive than buying even a cheap bottle of wine.
Squeeze a lemon and taste some of the juice. Pretty acidic, right? In fact, likely too acidic to drink/enjoy. Now add some sugar and taste again. Better, right? Now try the opposite. Make some simple syrup (sugar and water), and take a sip. Sweet, maybe even cloying to some palates, and no flavor other than sugar. Now add enough lemon juice to bring the mixture to a level that you would say has balance.
Wines low in acidity aren't necessarily bad wines. Ditto for wines higher in acidity (unless we're talking volatile acidity). It's a matter of preference, and it's a matter of what food you are having with your wine. Some, not all, highly acidic grapes and resulting wines may be vinified with a higher amount of residual sugar (maybe what you mean by fruit) to bring the wine "into balance."
Not a fully complete answer, but a start perhaps.