How to get repeatable sample using Presto SQL?

user_2680858 0
Viewed 4972

I am trying to get a sample of data from a large table and want to make sure this can be repeated later on. Other SQL allow repeatable sampling to be done with either setting a seed using set.seed(integer) or repeatable (integer) command. However, this is not working for me in Presto. Is such a command not available yet? Thanks.

presto sampling sql
4 Answers

If you are using Presto 0.263 or higher you can use key_sampling_percent to reproducibly generate a double between 0.0 and 1.0 from a varchar.

For example, to reproducibly sample 20% of records in table using the id column:

select
    id
from table
where key_sampling_percent(id) < 0.2

If you are using an older version of Presto (e.g. AWS Athena), you can use what's in the source code for key_sampling_percent:

select
    id
from table
where (abs(from_ieee754_64(xxhash64(cast(id as varbinary)))) % 100) / 100. < 0.2
user_31497880

I have found that you have to use from_big_endian_64 instead of from_ieee754_64 to get reliable results in Athena. Otherwise I got no many numbers close to zero because of the negative exponent.

select id
    from table
    where (abs(from_big_endian_64(xxhash64(cast(id as varbinary)))) % 100) / 100. < 0.2
user_86704020
Related