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.
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)This can be obtained by passing numbers in scientific notation.
1
