BigQuery: PI() in Standard SQL

I've been using Google BigQuery's legacy SQL for a while and when I need the number Pi, there is a convenient PI() function:

SELECT PI()

But in Standard SQL, this function no longer exists. I have been unable to find an equivalent function in the docs. What would be the easiest, most accurate way to have an equivalent of the PI() function in Standard SQL?

Solution 1:

Yet another alternative is to use built-in trigonometric functions - arc cosine of -1 will be exactly PI:

SELECT ACOS(-1)

results in

Row f0_  
1   3.141592653589793

If you use ACOS(-1) inside your query, it will be automatically constant folded by the optimizer and computed only once.

Solution 2:

You appear to be correct. I also cannot find a corresponding PI function in standard SQL.

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#mathematical-functions

Maybe a Googler can confirm that it's simply not available yet in standard SQL. But until then, as a workaround, how about using a very simple UDF? It's a little clunky, but might do the trick:

CREATE TEMPORARY FUNCTION PI()
RETURNS FLOAT64
LANGUAGE js AS """
  return Math.PI;
""";
SELECT PI() as PI

Legacy SQL as comparison:

Solution 3:

You can define a SQL function, which is has less overhead than using JavaScript:

CREATE TEMP FUNCTION PI() AS (3.141592653589793);

If having such a constant is important to you, you could find a feature request on the issue tracker.

Solution 4:

There is now a utility function in BigQuery called bqutil.fn.pi() which will return the value of pi.

You may use it like so:

SELECT bqutil.fn.pi() AS pi

Here is a screenshot of it in action.