BigQuery: PI() in Standard SQL

Asked at 2017-01-11 10:03:24Z
  • 5 Subscribers
  • 114 Views
2

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?


3 answers in total

1
Elliott Brossard Posted at 2017-01-11 14:14:22Z

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.

4
Mosha Pasumansky Posted at 2017-01-11 16:52:36Z

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.

3
Graham Polley Posted at 2017-01-11 11:46:45Z

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

enter image description here

Legacy SQL as comparison:

enter image description here

Answer this questsion