Randomize selection from snowflake SQL

user_17637315 0
Viewed 626

I am trying to select 1,000 random rows from a database of 97 million rows. I am using the following code:

SELECT *
FROM "DB"."SCHEMA"."TABLE"
ORDER BY                                         
   RAND()                                      LIMIT 1000

I tried this code and got an error stating "SQL compilation error: Unknown function RAND." Is there a better way to do this in Snowflake? I am worried that this code only works in MySQL.

snowflake-cloud-data-platform sql
2 Answers

Using SAMPLE clause:

Returns a subset of rows sampled randomly from the specified table. The following sampling methods are supported:

  • Sample a fraction of a table, with a specified probability for including a given row. The number of rows returned depends on the size of the table and the requested probability. A seed can be specified to make the sampling deterministic.

  • Sample a fixed, specified number of rows. The exact number of specified rows is returned unless the table contains fewer rows.

SELECT *
FROM "DB"."SCHEMA"."TABLE"
SAMPLE (1000 ROWS);
user_50708790

In Snowflake the function is RANDOM(), not RAND().

So your original query should be:

SELECT *
FROM "DB"."SCHEMA"."TABLE"
ORDER BY
   RANDOM()
LIMIT 1000

But as Lukasz mentioned, SAMPLE() function is the native way to do it in Snowflake.

user_98793790
Related