bigframes.bigquery.sql_scalar#

bigframes.bigquery.sql_scalar(sql_template: str, columns: DataFrame | Sequence[Series], *, output_dtype: BooleanDtype | Float64Dtype | Int64Dtype | StringDtype | ArrowDtype | GeometryDtype | None = None) → Series[source]#

Create a Series from a SQL template.

Examples:

>>> import bigframes.pandas as bpd
>>> import bigframes.bigquery as bbq

Either pass in a sequence of series, in which case use integers in the format strings.

>>> s = bpd.Series(["1.5", "2.5", "3.5"])
>>> s = s.astype(pd.ArrowDtype(pa.decimal128(38, 9)))
>>> bbq.sql_scalar("ROUND({0}, 0, 'ROUND_HALF_EVEN')", [s])
0    2.000000000
1    2.000000000
2    4.000000000
dtype: decimal128(38, 9)[pyarrow]

Or pass in a DataFrame, in which case use the column names in the format strings.

>>> df = bpd.DataFrame({"a": ["1.5", "2.5", "3.5"]})
>>> df = df.astype({"a": pd.ArrowDtype(pa.decimal128(38, 9))})
>>> bbq.sql_scalar("ROUND({a}, 0, 'ROUND_HALF_EVEN')", df)
0    2.000000000
1    2.000000000
2    4.000000000
dtype: decimal128(38, 9)[pyarrow]

You can also use the .bigquery DataFrame accessor to apply a SQL scalar function.

Compute SQL scalar using a pandas DataFrame:

>>> import pandas as pd
>>> df = pd.DataFrame({"x": [1, 2, 3]})
>>> bpd.options.display.progress_bar = None 
>>> pandas_s = df.bigquery.sql_scalar("POW({0}, 2)") 
>>> type(pandas_s) 
<class 'pandas.core.series.Series'>

Compute SQL scalar using a BigFrames DataFrame:

>>> bf_df = bpd.DataFrame({"x": [1, 2, 3]})
>>> bf_s = bf_df.bigquery.sql_scalar("POW({0}, 2)") 
>>> type(bf_s) 
<class 'bigframes.series.Series'>

Parameters:

• sql_template (str) A SQL format string with Python-style {0} placeholders for each of the Series objects in columns.

• ( (columns) Sequence[bigframes.pandas.Series] | bigframes.pandas.DataFrame

• ) Series objects representing the column inputs to the sql_template. Must contain at least one Series.

• output_dtype (a BigQuery DataFrames compatible dtype, optional) If provided, BigQuery DataFrames uses this to determine the output of the returned Series. This avoids a dry run query.

Returns:

A Series with the SQL applied.

Return type:

bigframes.pandas.Series

Raises:

ValueError If columns is empty.