If you cast this on a Water Elemental how much damage would it take?

Judging by the "in an open container" bit of the description, I'd say it would just fail to do anything.

It'd be some fun though 

Oh absolutely, it'll just need a lenient DM.  That and some creativity in shoving the thing into a bathtub or something. :P

I just realized that the lungs are technically a container. I doubt a DM would ever allow this, but you could theoretically suddenly fill an enemy's lungs, stomach, etc. with water in the middle of combat. The only specification is that it has to be open. So, you just have to wait for them to inhale/exhale, and they're now drowning. 

Container is a very broad word. Its definition is "an object that can be used to hold or transport something.". I mean, it may not be something's intended purpose, but it could still be considered a container, the only clause is that it must be open. Even if your DM rules that its purpose must be that of a container, the lung thing still works, because its purpose is to hold and transport oxygen throughout the body. 

There's also plenty of possibilities considering where their head is located :p. Personally I was looking up the rules to know if 1) I could destroy water _in_ their body (can't) and 2) if i can destroy water in an open ocean environment. If say, I were going to visit some water genasi and wanted to use this spell to **** one up, I could theoretically cast this spell centered on their head. The "duration" is instantaneous which a kind DM (or one with a physics background) would know that creates a vacuum and water entering a vacuum hits very hard hehe. Reference: https://what-if.xkcd.com/6/

My DM allows me to Create Holy Water if I use it as the source of the spell. We are doing a campaign were Vampires and undead are the main enemy. I have a question though, how long does the rain last?

Did some googling, and 1 inch of rain over 1000 sq feet amounts to 620 gallons (Source). A half inch of rain accumulates at a moderate rate of 1-2 hours (let's say an hour), or heavy rain for 30-45min (Source). 
We have 900 sq ft, and can make up to 10 gallons of water. 
620 gallons / 1000 ft^2 * 900 ft^2 / 120min/hr => 9.3 gal/min 

So basically, it lasts for about 30 seconds at a "moderate rate". (actual is 32.26s). If your DM thinks in order to inflict damage it should be heavy rain/a downpour, then maybe it only lasts 12-18 seconds (2-3 rounds) or maybe something like 1d4 rounds of combat. 

Huge elephant in the room: Holy Water does 2d6 damage when you throw the flask of it, and a first level spell doing 2d6 damage for 1d4 rounds of combat without concentration is completely broken. Alternative idea: you can cast the Create Water spell and you just do the 2d6 damage, but you expend the Holy Water for the one time damage. So effectively, you spend two 1st level spells: one to create the Holy Water, and one to cast Create Water, in order to get the "guaranteed" damage. Since it is a 30 foot cube, I think it's unreasonable that an enemy in the center would be able to make a Dex save to dodge it. 

Or, you buy a flask of holy water, use Create water to make more and just refill it during the spells effect.

One of my players had the same idea for filling the lungs, technically speaking the lungs are and open container so an arguement could be made that they could do that. The player also tried to shape the water to turn it to ice but since you have to see the water so i said no. However going back to the water filled lungs, i said it would take them atleast 2 full rounds to get the water out of their lungs so the enemy was at disadvantage against the party and took 4d4 worth of damage per round, so essentially 4d4 are rolled twice. Its hard to think of these things when people get really creative, there should be more help like a chart or something for dms when it comes to dealing with creative ways people use spells. 

I just had an idea for a villain. Some guy who’s decided he wants to destroy the ocean! And he uses this spell to do it!

WOLFMEME, APEKING33 Line of effect still matters. Unless you can see a creature's lungs, you couldn't target them with this spell.

On the other hand, what is a man if not a fleshy bag of meat and bone? And what is a bag if not a container? And are mouths, eyeholes, ears, pores, and other orifices not considered "openings"? 

Man, we Australians could really use this spell during bushfire season. i.e right now!

No where does the spell description say that you have to be able to see the container, so I would rule that this would work.

ummm ok 

On the lung container thing... Of note. Lungs aren't a container.  They're not "open" like they show in cartoons and comics and such.  So they aren't remotely a container.  The only container in the body is the stomach, bladder, and maybe intestinal tract.  The rest of it all is a closed system. 

You can force water into lungs (in real life), but that doesn't "fill things up" it just tears and rends things. It effectively swells and shreds your l ung tissue into bloody (via osmosis) water into your lungs.   A sponge isn't a container--or at least I wouldn't call it a container as there is no method to "close" it.   Lungs are closer functionally speaking to sponges than an open space. (neither descriptor is truly very accurate in either case though)

Also containers are objects-at least by all descriptions I can find in game and in colloqual english. so a living person can't really be a container.  You could do it to a corpse though, but it would just be the stomach/intestine/bladdar deal

Nada. Elementals are creatures. This spell only affects water not creatures. 

You'd need to open the container first.

there is something excessively violent about the implications of that sentence.

Water Weirds, while not technically made of water, instantly die if separated from water.