how to collect spark sql output to a file?

If you just want to count the number of lines in a big file on HDFS and write it to another file:

import java.nio.file.{ Files, Paths }
val path = "hdfs://quickstart.cloudera/user/hive/warehouse/people/people.txt"
val rdd = sc.textFile(path)
val linesCount = rdd.count
Files.write(Paths.get("line_count.txt"), linesCount.toString.getBytes)