SELECT random sample of data via PRESTO connector

user_13238303 0
Viewed 4469

I am currently looking for optimal way how to obtain a random data sample from the table (for instance in HIVE). I know that PRESTO provides either RANDOM() function or TABLESAMPLE BERNOULLI/SYSTEM. Problem is when querying table with significant number of records, it takes a lot of time, which is not suitable with cooperation with JayDeBeApi which might close the connection waiting too long for the response.

I would prefer to use TABLESAMPLE BERNOULLI/SYSTEM which takes as an argument percentage of the records to be fetched. To compare with ORACLE, SAP or MSSQL databases which enable to pass the precise percentage, i.e. 0.003123412%, the PRESTO does not allows you, despite that the function are quite similar, and everything is converted in the range 1-100%.

Does anyone know some workaround how to solve this? I would prefer to avoid the limit clause in cooperation with TABLESAMPLE BERNOULLI/SYSTEM which might not work as expected.

presto python database
2 Answers

With Presto 341 (upcoming release) you can just

 ... FROM my_table TABLESAMPLE BERNOULLI (0.01)

as @michal.kyjovsky points out, older versions had a bug, requiring use of scientific notation to get sub-percent sampling ratio

 ... FROM my_table TABLESAMPLE BERNOULLI (0.01e0)
user_654580

This can be obtained by passing numbers in scientific notation.

user_132383030
Related