How is `sample` different from `TABLESAMPLE` in Spark?

Daniel Tan 0
Viewed 25

I'd like to get create a random sub-sample of my data.

  1. Spark's sample function (link) is the API I'd like to use. Particularly, because it allows me to toggle if the sampling is done with or without replacement. However, executing this function takes a long time. Based on the answers from this question Spark sample is too slow, it seems like sample requires a full table scan.
  2. TABLESAMPLE seems like a faster alternative, albeit, the ability to toggle with and without replacement is lost.

I'd like to understand how sample and TABLESAMPLE are different, and why does TABLESAMPLE execute faster than sample. Could it be that TABLESAMPLE does not require a full table scan?

apache-spark scala
1 Answers

TABLESAMPLE has three ways of sampling:

  • percentage (or fraction): under the hood, does the same thing as sample. It is used to create uniform sampling.
  • num_rows: under the hood, does the same thing as LIMIT, which is why this API is very fast.
  • bucket OUT OF: specifies the portion out of the total to sample.

This is stated on the documentation:

Always use TABLESAMPLE (percent PERCENT) if randomness is important. TABLESAMPLE (num_rows ROWS) is not a simple random sample but instead is implemented using LIMIT.

So the answer whether sample and TABLESAMPLE are the same thing is no, but TABLESAMPLE used with percentage (fraction) and sample are the same thing.

If you want to read more about this, Databricks has some good information about this.

vilalabinot0
Related