I wouldn't try this until you have unlocked The Happiness achievement and have the super jump.
The big blue pipe in question is in the area of the final battle. The pipe is up high and has water pouring from it which makes up the river where the battle takes place. You have to get up above the outlet onto the ledge above it. The adventurer is up there near the rest of the pipe, you can't miss him.
If you're starting from the original village, get high and head out and to the left to the volcano area. The pipe is near the second peak, you can see it from near where the rainbow finishes.