If your goal is "pre-vis" then the accuracy (visual quality) isn't as important as it would be if you were producing a final product in iClone.Good. That gives you more freedom.
I had moderate success using a cloth, and changing the material properties so it was reflective and nearly transparent.
Try searching the forum for "water".
Here's a link to a thread. The last post on that thread includes a link to a little proof-of-concept video I made.
Someone else did a pretty cool fountain.
One last thing... You can apply a VIDEO as a texture. So you actually us a solid pipe and then apply a "water" video to the pipe. For pre-vis work it might be sufficient.