Zamzam is a well, and it is inside the Masjid al-Haram at Mecca, a short distance from the Kaaba. Its water is the only substance in Islam that is holy by where it comes from rather than by anything done to it. There is no rite of consecration and there is no officiant; the water is drawn and it is drunk.
The tradition explains it through Hagar. Abraham leaves her in the valley with the infant Ishmael and a little provision, and when that runs out she goes up the hill of Safa to look for help, sees nothing, crosses to Marwa, sees nothing, and goes back and forth seven times. The water then appears at the child's feet, and she contains it by banking earth around it. Islamic accounts add that had she left it alone it would have run as an open spring; because she confined it, it is a well.
The connection between the story and the ritual is direct rather than commemorative. Pilgrims performing hajj or umrah walk the same course between Safa and Marwa seven times, a rite called the sai, and the well sits in the same sanctuary. The narrative accounts for the rite and the rite keeps the narrative in use.
Hadith collected by al-Bukhari and by Muslim record the Prophet speaking of the water in strong terms, describing it as blessed and as sufficient nourishment, and one widely transmitted report has him say that it serves whatever purpose it is drunk for. That last has shaped how it is used more than any other statement about it.
A further tradition holds that the well was lost for generations after the tribe of Jurhum filled it in, and was rediscovered by Abd al-Muttalib, the grandfather of the Prophet, who was shown its location in a dream.