The Hookah was used by the Caterpillar in order to transfer Alice into the Fungiferous Flora to reach the Mushroom of Life. Caterpillar inhaled a long drag that let him blow out a deep blue smoke that made a ring, which on one side had a portal, the other side was a transparent hole.
Alice: Madness Returns
The Hookah was more useful to Alice during her trials in the Vale of Doom. Whenever Alice smoked from the hookah, the drugs affected her mind and the effects manifested themselves as physical changes in the form of platforms to Wonderland. The Caterpillar also used the smoke once again on Alice to shrink her down to size for the Oriental Grove. While Alice was traveling through the Monastery Shelf Valley, she saw a huge Hookah on a top of a mountain.
It is unknown whether or not the Hookah is a source of power or magic for the Caterpillar, or if it helps augment it (if not, it is assumed these talents are natural).