How do I ingest it in such a way to experience the psychoactive/euphoric effects?
In Skyrim, Skooma is just a stamina potion.
Even in Oblivion and Morrowind it was just a potion that had a mix of positive and negative effects.
The euphoric drug effects are Lore only, not gameplay mechanics.
Drink some Sleeping Tree Sap. That'll get you feeling good and dizzy.
red water skooma
now that knocks ya off your feet
Yeah, that stuff will kill you, like, literally.
If you're on PC then there is a mod for it.
http://www.nexusmods.com/skyrim/mods/8888/?
I use a similar mod for my alcoholic character.