Sampling
Sampling is concerned with the selection of a subset of data from a large dataset to run queries and verify results. The dataset may be too large to run queries on the whole data. Therefore in development and testing phases it is a good idea to run queries on a sample of dataset.
TABLESAMPLE Clause
We can run Hive queries on a sample of data using the TABLESAMPLE clause. Any column can be used for sampling the data. We need to provide the required sample size in the queries.
Sampling by Bucketing
We can use TABLESAMPLE clause to bucket the table on the given column and get data from only some of the buckets.
TABLESAMPLE (BUCKET x OUT OF y [ON colname])
colname indicates the column to be used to bucket the data into y buckets[1-y]. All the rows which are in the bucket x are returned.
If the table is not bucketed on the column(s) used in sampling, TABLESAMPLE will scan the entire table and fetch the sample.
If the hive table is bucketed on some column(s), then we can directly use that column(s) to get a sample. In this case Hive need not read all the data to generate sample as the data is already organized into different buckets using the column(s) used in the sampling query. Hive will read data only from some buckets as per the size specified in the sampling query.
Block Sampling
Block sampling allows Hive to select at least n% data from the whole dataset. Sampling granularity is at the HDFS block size level. If HDFS block size is 64MB and n% of input size is only 10MB, then 64MB of data is fetched.
TABLESAMPLE (n PERCENT)Example
Running Sampling Queries in Hive