def table_to_df(self, table_name, columns="*", limit=None):
"""
Return cols as Spark dataframe from a specific table
:type table_name: object
:param columns:
:param limit: how many rows will be retrieved
"""
db_table = table_name
query = self.driver_context.count_query(db_table=db_table)
if limit == "all":
count = self.execute(query, "all").first()[0]
# We want to count the number of rows to warn the users how much it can take to bring the whole data
print(str(int(count)) + " rows")
if columns == "*":
columns_sql = "*"
else:
columns = val_to_list(columns)
columns_sql = ",".join(columns)
query = "SELECT " + columns_sql + " FROM " + db_table
logger.print(query)
df = self.execute(query, limit)
# Bring the data to local machine if not every time we call an action is going to be
# retrieved from the remote server
df = df.run()
return df
def percentile_agg(col_name, df, values, relative_error):
"""
Return the percentile of a dataframe
:param col_name: '*', list of columns names or a single column name.
:param df:
:param values: list of percentiles to be calculated
:param relative_error: If set to zero, the exact percentiles are computed, which could be very expensive. 0 to 1 accepted
:return: percentiles per columns
"""
# Make sure values are double
if values is None:
values = [0.05, 0.25, 0.5, 0.75, 0.95]
values = val_to_list(values)
values = list(map(str, values))
if is_column_a(df, col_name, PYSPARK_NUMERIC_TYPES):
# Get percentiles
p = F.expr(
"percentile_approx(`{COLUMN}`, array({VALUES}), {ERROR})".format(
COLUMN=col_name,
VALUES=" , ".join(values),
ERROR=relative_error))
# Zip the arrays
expr = [[F.lit(v), p.getItem(i)] for i, v in enumerate(values)]
expr = F.create_map(*list(itertools.chain(*expr)))
else:
expr = None
# print(expr)
return expr
def append(dfs, like="columns"):
"""
Concat multiple dataFrames columns or rows wise
:param dfs: List of DataFrames
:param like: concat as columns or rows
:return:
"""
# FIX: Because monotonically_increasing_id can create different
# sequence for different dataframes the result could be wrong.
if like == "columns":
temp_dfs = []
col_temp_name = "id_" + random_int()
dfs = val_to_list(dfs)
for df in dfs:
temp_dfs.append(
df.withColumn(col_temp_name, F.monotonically_increasing_id()))
def _append(df1, df2):
return df1.join(df2, col_temp_name, "outer")
df_result = reduce(_append, temp_dfs).drop(col_temp_name)
elif like == "rows":
df_result = reduce(DataFrame.union, dfs)
else:
RaiseIt.value_error(like, ["columns", "rows"])
return df_result
def _add_spark_packages(self, packages):
"""
Define the Spark packages that must be loaded at start time
:param packages:
:return:
"""
for p in val_to_list(packages):
self.packages.append(p)
def is_column_a(df, column, dtypes):
"""
Check if column match a list of data types
:param df: dataframe
:param column: column to be compared with
:param dtypes: types to be checked
:return:
"""
column = val_to_list(column)
if len(column) > 1:
RaiseIt.length_error(column, 1)
data_type = tuple(val_to_list(parse_spark_dtypes(dtypes)))
column = one_list_to_val(column)
# Filter columns by data type
return isinstance(df.schema[column].dataType, data_type)
def select_lower_bound(self):
col_name = self.col_name
sample = {
"columns": [{
"title": cols
} for cols in val_to_list(self.col_name)],
"value":
self.df.rows.select(self.df[col_name] < self.lower_bound).limit(
100).rows.to_list(col_name)
}
return dump_json(sample)
def show(self, table_names="*", limit=None):
db = self.db
if table_names is "*":
table_names = db.tables_names_to_json()
else:
table_names = val_to_list(table_names)
print("Total Tables:" + str(len(table_names)))
for table_name in table_names:
db.table_to_df(table_name, "*", limit) \
.table(title=table_name)
def get_output_cols(input_cols, output_cols):
"""
Construct output columns names
:param input_cols:
:param output_cols:
:return:
"""
if is_list(input_cols) and is_list(output_cols):
if len(input_cols) != len(output_cols):
RaiseIt.length_error(input_cols, output_cols)
elif is_list(input_cols) and is_str(output_cols):
if len(input_cols) > 1:
output_cols = list([i + output_cols for i in input_cols])
else:
output_cols = val_to_list(output_cols)
elif is_str(input_cols) and is_str(output_cols):
output_cols = val_to_list(output_cols)
elif output_cols is None:
output_cols = input_cols
return output_cols
def name_col(col_names: str, append: str) -> str:
"""
Whenever you want to name and output user this function. This ensure that we manage and Standard when naming
:param col_names: Column name
:param append: string to be appended
:return:
"""
col_names = val_to_list(col_names)
if len(col_names) > 1:
output_col = ('_'.join(str(elem) for elem in col_names))[:10] + "***"
else:
output_col = one_list_to_val(col_names)
return output_col + "_" + append.upper()
def filter_col_name_by_dtypes(df, data_type):
"""
Return column names filtered by the column data type
:param df: Dataframe which columns are going to be filtered
:param data_type: Datatype used to filter the column.
:type data_type: str or list
:return:
"""
data_type = parse_spark_dtypes(data_type)
# isinstace require a tuple
data_type = tuple(val_to_list(data_type))
# Filter columns by data type
return [c for c in df.columns if isinstance(df.schema[c].dataType, data_type)]
def absolute_path(files, format="posix"):
"""
User project base folder to construct and absolute path
:param files: path files
:param format: posix or uri
:return:
"""
files = val_to_list(files)
if format == "uri":
result = [Path(ROOT_DIR + file).as_uri() for file in files]
elif format == "posix":
result = [Path(ROOT_DIR + file).as_posix() for file in files]
else:
RaiseIt.value_error(format, ["posix", "uri"])
result = one_list_to_val(result)
return result
def validate_columns_names(df, col_names, index=0):
"""
Check if a string or list of string are valid dataframe columns
:param df: Data frame to be analyzed
:param col_names: columns names to be checked
:param index:
:return:
"""
columns = val_to_list(col_names)
if is_list_of_tuples(columns):
columns = [c[index] for c in columns]
# Remove duplicates in the list
if is_list_of_strings(columns):
columns = OrderedSet(columns)
check_for_missing_columns(df, columns)
return True
def type_error(var, data_types):
"""
Raise a TypeError exception
:param var:
:param data_types: data types expected as string or list of strings
:return:
"""
data_types = val_to_list(data_types)
from optimus.helpers.debug import get_var_name
if len(data_types) == 1:
divisor = ""
elif len(data_types) == 2:
divisor = " or "
elif len(data_types) > 2:
divisor = ", "
_type = divisor.join(map(lambda x: "'" + x + "'", data_types))
raise TypeError(
"'{var_name}' must be of type {type}, received '{var_type}'".
format(var_name=get_var_name(var), type=_type, var_type=type(var)))
def __init__(self,
session=None,
master="local[*]",
app_name="optimus",
checkpoint=False,
path=None,
file_system="local",
verbose=False,
server=False,
repositories=None,
packages=None,
jars=None,
driver_class_path=None,
options=None,
additional_options=None,
comm=None,
load_avro=False,
cache=True):
"""
Transform and roll out
:param master: 'Master', 'local' or ip address to a cluster
:param app_name: Spark app name
:param path: path to the checkpoint folder
:param checkpoint: If True create a checkpoint folder
:param file_system: 'local' or 'hadoop'
:param additional_options:
:param options: Configuration options that are passed to spark-submit.
See `the list of possible options
<https://spark.apache.org/docs/2.4.1/configuration.html#available-properties>`_.
Note that any options set already through PYSPARK_SUBMIT_ARGS will override
these.
:type options: (dict[str,str])
:param repositories: List of additional maven repositories for package lookup.
:type repositories: (list[str])
:param packages: Spark packages that should be installed.
:type packages: (list[str])
:param jars: Full paths to jar files that we want to include to the session.
:type jars: (list[str])
"""
self.preserve = False
Optimus.cache = cache
if comm is True:
Comm.instance = Comm()
else:
Comm.instance = comm
if jars is None:
jars = []
if driver_class_path is None:
driver_class_path = []
if session is None:
# Creating Spark Session
# If a Spark session in not passed by argument create one
self.master = master
self.app_name = app_name
if options is None:
options = {}
self.options = options
# Initialize as lists
self.packages = val_to_list(packages)
self.repositories = val_to_list(repositories)
self.jars = val_to_list(jars)
self.driver_class_path = val_to_list(driver_class_path)
self.additional_options = additional_options
self.verbose(verbose)
# Because avro depends of a external package you can decide if should be loaded
if load_avro == "2.4":
self._add_spark_packages(
["org.apache.spark:spark-avro_2.12:2.4.3"])
elif load_avro == "2.3":
self._add_spark_packages(
["com.databricks:spark-avro_2.11:4.0.0"])
jdbc_jars = [
"/jars/spark-redis-2.4.1-SNAPSHOT-jar-with-dependencies.jar",
"/jars/RedshiftJDBC42-1.2.16.1027.jar",
"/jars/mysql-connector-java-8.0.16.jar", "/jars/ojdbc8.jar",
"/jars/postgresql-42.2.5.jar", "/jars/presto-jdbc-0.224.jar",
"/jars/spark-cassandra-connector_2.11-2.4.1.jar",
"/jars/sqlite-jdbc-3.27.2.1.jar",
"/jars/mssql-jdbc-7.4.1.jre8.jar"
]
self._add_jars(absolute_path(jdbc_jars, "uri"))
self._add_driver_class_path(absolute_path(jdbc_jars, "posix"))
self._create_session()
if path is None:
path = os.getcwd()
if checkpoint is True:
self._set_check_point_folder(path, file_system)
else:
# If a session is passed by arguments just save the reference
# logger.print("Spark session")
Spark.instance = Spark().load(session)
# Initialize Spark
logger.print("""
____ __ _
/ __ \____ / /_(_)___ ___ __ _______
/ / / / __ \/ __/ / __ `__ \/ / / / ___/
/ /_/ / /_/ / /_/ / / / / / / /_/ (__ )
\____/ .___/\__/_/_/ /_/ /_/\__,_/____/
/_/
""")
logger.print(STARTING_OPTIMUS)
# Pickling
Spark.instance.sc.addPyFile(absolute_path("/infer.py"))
if server:
logger.print("Starting Optimus Server...")
s = Server()
s.start()
self.server_instance = s
logger.print(SUCCESS)
self.create = Create()
self.load = Load()
self.read = self.spark.read
# Create singleton profiler
Profiler.instance = Profiler()
self.profiler = Profiler.instance
self.ml = ML()
# Set global output as html
self.output("html")
def _add_driver_class_path(self, driver_class_path):
for d in val_to_list(driver_class_path):
self.driver_class_path.append(d)
def _add_jars(self, jar):
for j in val_to_list(jar):
self.jars.append(j)
def parse_columns(df,
cols_args,
get_args=False,
is_regex=None,
filter_by_column_dtypes=None,
accepts_missing_cols=False,
invert=False):
"""
Return a list of columns and check that columns exists in the dataframe
Accept '*' as parameter in which case return a list of all columns in the dataframe.
Also accept a regex.
If a list of tuples return to list. The first element is the columns name the others element are params.
This params can be used to create custom transformation functions. You can find and example in cols().cast()
:param df: Dataframe in which the columns are going to be checked
:param cols_args: Accepts * as param to return all the string columns in the dataframe
:param get_args:
:param is_regex: Use True is col_attrs is a regex
:param filter_by_column_dtypes: A data type for which a columns list is going be filtered
:param accepts_missing_cols: if true not check if column exist in the dataframe
:param invert: Invert the final selection. For example if you want to select not integers
:return: A list of columns string names
"""
attrs = None
# if columns value is * get all dataframes columns
if is_regex is True:
r = re.compile(cols_args[0])
cols = list(filter(r.match, df.columns))
elif cols_args == "*" or cols_args is None:
cols = df.columns
# In case we have a list of tuples we use the first element of the tuple is taken as the column name
# and the rest as params. We can use the param in a custom function as follow
# def func(attrs): attrs return (1,2) and (3,4)
# return attrs[0] + 1
# df.cols().apply([('col_1',1,2),('cols_2', 3 ,4)], func)
# Verify if we have a list with tuples
elif is_tuple(cols_args) or is_list_of_tuples(cols_args):
cols_args = val_to_list(cols_args)
# Extract a specific position in the tuple
cols = [(i[0:1][0]) for i in cols_args]
attrs = [(i[1:]) for i in cols_args]
else:
# if not a list convert to list
cols = val_to_list(cols_args)
# Get col name from index
cols = [c if is_str(c) else df.columns[c] for c in cols]
# Check for missing columns
if accepts_missing_cols is False:
check_for_missing_columns(df, cols)
# Filter by column data type
if filter_by_column_dtypes is not None:
filter_by_column_dtypes = val_to_list(filter_by_column_dtypes)
columns_residual = None
# If necessary filter the columns by data type
if filter_by_column_dtypes:
# Get columns for every data type
columns_filtered = filter_col_name_by_dtypes(df,
filter_by_column_dtypes)
# Intersect the columns filtered per data type from the whole dataframe with the columns passed to the function
final_columns = list(OrderedSet(cols).intersection(columns_filtered))
# This columns match filtered data type
columns_residual = list(
OrderedSet(cols) - OrderedSet(columns_filtered))
else:
final_columns = cols
# Return cols or cols an params
cols_params = []
if invert:
final_columns = list(OrderedSet(cols) - OrderedSet(final_columns))
if get_args is True:
cols_params = final_columns, attrs
elif get_args is False:
cols_params = final_columns
else:
RaiseIt.value_error(get_args, ["True", "False"])
if columns_residual:
logger.print("%s %s %s", ",".join(escape_columns(columns_residual)),
"column(s) was not processed because is/are not",
",".join(filter_by_column_dtypes))
return cols_params
