Snowflake
Published in

Snowflake

Facebook Prophet forecasts running in Snowflake with Snowpark

Now Facebook Prophet runs inside Snowflake — no need for external functions or dependencies. Let’s see how.

“Crystal ball at the Snowpark” — image generated by AI

A simple UDF

create or replace function simple_prophet_udf(
dates array, vals array, periods number)
returns variant
language python
runtime_version = 3.8
packages = ('prophet')
handler = 'x'
as $$
import pandas as pd
from prophet import Prophet
def x(dates, vals, periods):
df = pd.DataFrame(list(zip(dates, vals)),
columns =['ds', 'y'])
model = Prophet()
model.fit(df)
future_df = model.make_future_dataframe(
periods=periods,
include_history=False)
forecast = model.predict(future_df)
return [[x.yhat, x.ds] for x in forecast.itertuples()]
$$
;
select value[1]::date d, value[0] prediction
from table(flatten(
simple_prophet_udf(['2020-01-01', '2020-01-02'], [1, 2], 4)
))
Prophet correctly predicts a linear growth during the 4 following days

A more complex UDTF

create or replace function prophetize_daily(id string, x float, y date, periods number)
returns table(id string, y float, d string)
language python
runtime_version = 3.8
packages = ('prophet')
handler = 'X'
as $$
import pandas as pd
from prophet import Prophet
class X:
def __init__(self):
self._dates = []
self._values = []

def process(self, id, y, d, periods):
self._dates.append(d)
self._values.append(y)
self._id = id
self._periods = periods
return ((id, y, d), )
def end_partition(self):

df = pd.DataFrame(list(zip(self._dates, self._values)),
columns =['ds', 'y'])

model = Prophet()
model.fit(df)
future_df = model.make_future_dataframe(
periods=self._periods,
include_history=False)
forecast = model.predict(future_df)
for row in forecast.itertuples():
yield(self._id, row.yhat, row.ds)
yield(self._id + '_lower', row.yhat_lower, row.ds)
yield(self._id + '_upper', row.yhat_upper, row.ds)
$$;

Prophet UDTF in action

with data as (
select "Currency", "Units", "Currency Description", "Date", "Value"
from knoema_economy_data_atlas.economy.exratescc2018
where "Indicator Name"= 'Close'
and "Currency Exchange" = 'Real-time FX'
and "Frequency" = 'D'
and "Date" > '2021-08-01'
and "Currency Description" in (
'US Dollar Mexican Peso'
, 'US Dollar Japanese Yen')
)
select p.*
from data
, table(prophetize_daily("Currency", "Value", "Date", 360)
over (partition by "Currency")) p
Predictions for the Japanese Yen and Mexican Peso
Predictions for the Japanese Yen and Mexican Peso

Want more?

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Felipe Hoffa

Data Cloud Advocate at Snowflake ❄️. Originally from Chile, now in San Francisco and around the world. Previously at Google. Let’s talk data.