Hookah tobacco[1] is a San 'Shyuum (Prophet) stimulant that is smoked via water pipe.
Although the drug allows San 'Shyuum extra focus and mental acuity for a short duration, later reactions to the drug can include an extreme headache. A quick-acting herbal tea is available to deal with the side-effects.
Because it is not native to Earth, it is unlikely that Hookah is actually related to true Tobacco.
Sources
- ^ Halo: Contact Harvest, page 143