def _model_udf(self) -> Any:
from mlflow import pyfunc
spark = default_session()
return pyfunc.spark_udf(spark,
model_uri=self._model_uri,
result_type=self._return_type)
def get_option(key: str, default: Union[Any, _NoValueType] = _NoValue) -> Any:
"""
Retrieves the value of the specified option.
Parameters
----------
key : str
The key which should match a single option.
default : object
The default value if the option is not set yet. The value should be JSON serializable.
Returns
-------
result : the value of the option
Raises
------
OptionError : if no such option exists and the default is not provided
"""
_check_option(key)
if default is _NoValue:
default = _options_dict[key].default
_options_dict[key].validate(default)
return json.loads(default_session().conf.get(_key_format(key),
default=json.dumps(default)))
def reset_option(key: str) -> None:
"""
Reset one option to their default value.
Pass "all" as argument to reset all options.
Parameters
----------
key : str
If specified only option will be reset.
Returns
-------
None
"""
_check_option(key)
default_session().conf.unset(_key_format(key))
def set_option(key: str, value: Any) -> None:
"""
Sets the value of the specified option.
Parameters
----------
key : str
The key which should match a single option.
value : object
New value of option. The value should be JSON serializable.
Returns
-------
None
"""
_check_option(key)
_options_dict[key].validate(value)
default_session().conf.set(_key_format(key), json.dumps(value))
def value_counts(self,
normalize=False,
sort=True,
ascending=False,
bins=None,
dropna=True) -> Series:
if (LooseVersion(pyspark.__version__) < LooseVersion("2.4") and
default_session().conf.get("spark.sql.execution.arrow.enabled")
== "true" and isinstance(self, MultiIndex)):
raise RuntimeError(
"if you're using pyspark < 2.4, set conf "
"'spark.sql.execution.arrow.enabled' to 'false' "
"for using this function with MultiIndex")
return super().value_counts(normalize=normalize,
sort=sort,
ascending=ascending,
bins=bins,
dropna=dropna)
def sql(
query: str,
index_col: Optional[Union[str, List[str]]] = None,
globals: Optional[Dict[str, Any]] = None,
locals: Optional[Dict[str, Any]] = None,
**kwargs: Any
) -> DataFrame:
"""
Execute a SQL query and return the result as a pandas-on-Spark DataFrame.
This function also supports embedding Python variables (locals, globals, and parameters)
in the SQL statement by wrapping them in curly braces. See examples section for details.
In addition to the locals, globals and parameters, the function will also attempt
to determine if the program currently runs in an IPython (or Jupyter) environment
and to import the variables from this environment. The variables have the same
precedence as globals.
The following variable types are supported:
* string
* int
* float
* list, tuple, range of above types
* pandas-on-Spark DataFrame
* pandas-on-Spark Series
* pandas DataFrame
Parameters
----------
query : str
the SQL query
index_col : str or list of str, optional
Column names to be used in Spark to represent pandas-on-Spark's index. The index name
in pandas-on-Spark is ignored. By default, the index is always lost.
.. note:: If you want to preserve the index, explicitly use :func:`DataFrame.reset_index`,
and pass it to the sql statement with `index_col` parameter.
For example,
>>> psdf = ps.DataFrame({"A": [1, 2, 3], "B":[4, 5, 6]}, index=['a', 'b', 'c'])
>>> psdf_reset_index = psdf.reset_index()
>>> ps.sql("SELECT * FROM {psdf_reset_index}", index_col="index")
... # doctest: +NORMALIZE_WHITESPACE
A B
index
a 1 4
b 2 5
c 3 6
For MultiIndex,
>>> psdf = ps.DataFrame(
... {"A": [1, 2, 3], "B": [4, 5, 6]},
... index=pd.MultiIndex.from_tuples(
... [("a", "b"), ("c", "d"), ("e", "f")], names=["index1", "index2"]
... ),
... )
>>> psdf_reset_index = psdf.reset_index()
>>> ps.sql("SELECT * FROM {psdf_reset_index}", index_col=["index1", "index2"])
... # doctest: +NORMALIZE_WHITESPACE
A B
index1 index2
a b 1 4
c d 2 5
e f 3 6
Also note that the index name(s) should be matched to the existing name.
globals : dict, optional
the dictionary of global variables, if explicitly set by the user
locals : dict, optional
the dictionary of local variables, if explicitly set by the user
kwargs
other variables that the user may want to set manually that can be referenced in the query
Returns
-------
pandas-on-Spark DataFrame
Examples
--------
Calling a built-in SQL function.
>>> ps.sql("select * from range(10) where id > 7")
id
0 8
1 9
A query can also reference a local variable or parameter by wrapping them in curly braces:
>>> bound1 = 7
>>> ps.sql("select * from range(10) where id > {bound1} and id < {bound2}", bound2=9)
id
0 8
You can also wrap a DataFrame with curly braces to query it directly. Note that when you do
that, the indexes, if any, automatically become top level columns.
>>> mydf = ps.range(10)
>>> x = range(4)
>>> ps.sql("SELECT * from {mydf} WHERE id IN {x}")
id
0 0
1 1
2 2
3 3
Queries can also be arbitrarily nested in functions:
>>> def statement():
... mydf2 = ps.DataFrame({"x": range(2)})
... return ps.sql("SELECT * from {mydf2}")
>>> statement()
x
0 0
1 1
Mixing pandas-on-Spark and pandas DataFrames in a join operation. Note that the index is
dropped.
>>> ps.sql('''
... SELECT m1.a, m2.b
... FROM {table1} m1 INNER JOIN {table2} m2
... ON m1.key = m2.key
... ORDER BY m1.a, m2.b''',
... table1=ps.DataFrame({"a": [1,2], "key": ["a", "b"]}),
... table2=pd.DataFrame({"b": [3,4,5], "key": ["a", "b", "b"]}))
a b
0 1 3
1 2 4
2 2 5
Also, it is possible to query using Series.
>>> myser = ps.Series({'a': [1.0, 2.0, 3.0], 'b': [15.0, 30.0, 45.0]})
>>> ps.sql("SELECT * from {myser}")
0
0 [1.0, 2.0, 3.0]
1 [15.0, 30.0, 45.0]
"""
if globals is None:
globals = _get_ipython_scope()
_globals = builtin_globals() if globals is None else dict(globals)
_locals = builtin_locals() if locals is None else dict(locals)
# The default choice is the globals
_dict = dict(_globals)
# The vars:
_scope = _get_local_scope()
_dict.update(_scope)
# Then the locals
_dict.update(_locals)
# Highest order of precedence is the locals
_dict.update(kwargs)
return SQLProcessor(_dict, query, default_session()).execute(index_col)
def setUpClass(cls):
cls.spark = default_session()
cls.spark.conf.set(SPARK_CONF_ARROW_ENABLED, True)
def sql(
query: str,
index_col: Optional[Union[str, List[str]]] = None,
**kwargs: Any,
) -> DataFrame:
"""
Execute a SQL query and return the result as a pandas-on-Spark DataFrame.
This function acts as a standard Python string formatter with understanding
the following variable types:
* pandas-on-Spark DataFrame
* pandas-on-Spark Series
* pandas DataFrame
* pandas Series
* string
Parameters
----------
query : str
the SQL query
index_col : str or list of str, optional
Column names to be used in Spark to represent pandas-on-Spark's index. The index name
in pandas-on-Spark is ignored. By default, the index is always lost.
.. note:: If you want to preserve the index, explicitly use :func:`DataFrame.reset_index`,
and pass it to the sql statement with `index_col` parameter.
For example,
>>> psdf = ps.DataFrame({"A": [1, 2, 3], "B":[4, 5, 6]}, index=['a', 'b', 'c'])
>>> new_psdf = psdf.reset_index()
>>> ps.sql("SELECT * FROM {new_psdf}", index_col="index", new_psdf=new_psdf)
... # doctest: +NORMALIZE_WHITESPACE
A B
index
a 1 4
b 2 5
c 3 6
For MultiIndex,
>>> psdf = ps.DataFrame(
... {"A": [1, 2, 3], "B": [4, 5, 6]},
... index=pd.MultiIndex.from_tuples(
... [("a", "b"), ("c", "d"), ("e", "f")], names=["index1", "index2"]
... ),
... )
>>> new_psdf = psdf.reset_index()
>>> ps.sql(
... "SELECT * FROM {new_psdf}", index_col=["index1", "index2"], new_psdf=new_psdf)
... # doctest: +NORMALIZE_WHITESPACE
A B
index1 index2
a b 1 4
c d 2 5
e f 3 6
Also note that the index name(s) should be matched to the existing name.
kwargs
other variables that the user want to set that can be referenced in the query
Returns
-------
pandas-on-Spark DataFrame
Examples
--------
Calling a built-in SQL function.
>>> ps.sql("SELECT * FROM range(10) where id > 7")
id
0 8
1 9
>>> ps.sql("SELECT * FROM range(10) WHERE id > {bound1} AND id < {bound2}", bound1=7, bound2=9)
id
0 8
>>> mydf = ps.range(10)
>>> x = tuple(range(4))
>>> ps.sql("SELECT {ser} FROM {mydf} WHERE id IN {x}", ser=mydf.id, mydf=mydf, x=x)
id
0 0
1 1
2 2
3 3
Mixing pandas-on-Spark and pandas DataFrames in a join operation. Note that the index is
dropped.
>>> ps.sql('''
... SELECT m1.a, m2.b
... FROM {table1} m1 INNER JOIN {table2} m2
... ON m1.key = m2.key
... ORDER BY m1.a, m2.b''',
... table1=ps.DataFrame({"a": [1,2], "key": ["a", "b"]}),
... table2=pd.DataFrame({"b": [3,4,5], "key": ["a", "b", "b"]}))
a b
0 1 3
1 2 4
2 2 5
Also, it is possible to query using Series.
>>> psdf = ps.DataFrame({"A": [1, 2, 3], "B":[4, 5, 6]}, index=['a', 'b', 'c'])
>>> ps.sql("SELECT {mydf.A} FROM {mydf}", mydf=psdf)
A
0 1
1 2
2 3
"""
if os.environ.get("PYSPARK_PANDAS_SQL_LEGACY") == "1":
from pyspark.pandas import sql_processor
warnings.warn(
"Deprecated in 3.3.0, and the legacy behavior "
"will be removed in the future releases.",
FutureWarning,
)
return sql_processor.sql(query, index_col=index_col, **kwargs)
session = default_session()
formatter = PandasSQLStringFormatter(session)
try:
sdf = session.sql(formatter.format(query, **kwargs))
finally:
formatter.clear()
index_spark_columns, index_names = _get_index_map(sdf, index_col)
return DataFrame(
InternalFrame(spark_frame=sdf,
index_spark_columns=index_spark_columns,
index_names=index_names))
def sql(query: str, globals=None, locals=None, **kwargs) -> DataFrame:
"""
Execute a SQL query and return the result as a Koalas DataFrame.
This function also supports embedding Python variables (locals, globals, and parameters)
in the SQL statement by wrapping them in curly braces. See examples section for details.
In addition to the locals, globals and parameters, the function will also attempt
to determine if the program currently runs in an IPython (or Jupyter) environment
and to import the variables from this environment. The variables have the same
precedence as globals.
The following variable types are supported:
* string
* int
* float
* list, tuple, range of above types
* Koalas DataFrame
* Koalas Series
* pandas DataFrame
Parameters
----------
query : str
the SQL query
globals : dict, optional
the dictionary of global variables, if explicitly set by the user
locals : dict, optional
the dictionary of local variables, if explicitly set by the user
kwargs
other variables that the user may want to set manually that can be referenced in the query
Returns
-------
Koalas DataFrame
Examples
--------
Calling a built-in SQL function.
>>> ps.sql("select * from range(10) where id > 7")
id
0 8
1 9
A query can also reference a local variable or parameter by wrapping them in curly braces:
>>> bound1 = 7
>>> ps.sql("select * from range(10) where id > {bound1} and id < {bound2}", bound2=9)
id
0 8
You can also wrap a DataFrame with curly braces to query it directly. Note that when you do
that, the indexes, if any, automatically become top level columns.
>>> mydf = ps.range(10)
>>> x = range(4)
>>> ps.sql("SELECT * from {mydf} WHERE id IN {x}")
id
0 0
1 1
2 2
3 3
Queries can also be arbitrarily nested in functions:
>>> def statement():
... mydf2 = ps.DataFrame({"x": range(2)})
... return ps.sql("SELECT * from {mydf2}")
>>> statement()
x
0 0
1 1
Mixing Koalas and pandas DataFrames in a join operation. Note that the index is dropped.
>>> ps.sql('''
... SELECT m1.a, m2.b
... FROM {table1} m1 INNER JOIN {table2} m2
... ON m1.key = m2.key
... ORDER BY m1.a, m2.b''',
... table1=ps.DataFrame({"a": [1,2], "key": ["a", "b"]}),
... table2=pd.DataFrame({"b": [3,4,5], "key": ["a", "b", "b"]}))
a b
0 1 3
1 2 4
2 2 5
Also, it is possible to query using Series.
>>> myser = ps.Series({'a': [1.0, 2.0, 3.0], 'b': [15.0, 30.0, 45.0]})
>>> ps.sql("SELECT * from {myser}")
0
0 [1.0, 2.0, 3.0]
1 [15.0, 30.0, 45.0]
"""
if globals is None:
globals = _get_ipython_scope()
_globals = builtin_globals() if globals is None else dict(globals)
_locals = builtin_locals() if locals is None else dict(locals)
# The default choice is the globals
_dict = dict(_globals)
# The vars:
_scope = _get_local_scope()
_dict.update(_scope)
# Then the locals
_dict.update(_locals)
# Highest order of precedence is the locals
_dict.update(kwargs)
return SQLProcessor(_dict, query, default_session()).execute()
def _model_udf(self):
spark = default_session()
return pyfunc.spark_udf(spark,
model_uri=self._model_uri,
result_type=self._return_type)
