You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@madlib.apache.org by xt...@apache.org on 2016/03/25 23:50:23 UTC
incubator-madlib git commit: Path: Return results for each match
Repository: incubator-madlib
Updated Branches:
refs/heads/master c9cb10cda -> 360f13482
Path: Return results for each match
JIRA: MADLIB-973
- Previously, path returned the result per partition. This work changes
that to output a result per match within each partition.
- 'agg_func' converted to a default argument.
- Match ID and symbol string added to the output tuples table.
Closes #29
Project: http://git-wip-us.apache.org/repos/asf/incubator-madlib/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-madlib/commit/360f1348
Tree: http://git-wip-us.apache.org/repos/asf/incubator-madlib/tree/360f1348
Diff: http://git-wip-us.apache.org/repos/asf/incubator-madlib/diff/360f1348
Branch: refs/heads/master
Commit: 360f1348267f0810ffd70ff6d5c0fd7023219ce9
Parents: c9cb10c
Author: Rahul Iyer <ri...@pivotal.io>
Authored: Fri Mar 25 15:44:01 2016 -0700
Committer: Xiaocheng Tang <xi...@gmail.com>
Committed: Fri Mar 25 15:49:28 2016 -0700
----------------------------------------------------------------------
doc/mainpage.dox.in | 9 +-
src/ports/postgres/modules/utilities/path.py_in | 386 +++++++++------
.../postgres/modules/utilities/path.sql_in | 468 +++++++++++++++----
.../postgres/modules/utilities/test/path.sql_in | 26 +-
.../postgres/modules/utilities/utilities.sql_in | 5 -
.../modules/utilities/validate_args.py_in | 11 +-
6 files changed, 654 insertions(+), 251 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/360f1348/doc/mainpage.dox.in
----------------------------------------------------------------------
diff --git a/doc/mainpage.dox.in b/doc/mainpage.dox.in
index bc9e4c0..040611d 100644
--- a/doc/mainpage.dox.in
+++ b/doc/mainpage.dox.in
@@ -122,12 +122,13 @@ complete matrix stored as a distributed table.
@defgroup grp_desc_stats Descriptive Statistics
@ingroup grp_stats
@{A collection of methods to compute descriptive statistics of the dataset @}
- @defgroup grp_summary Summary
- @ingroup grp_desc_stats
@defgroup grp_correlation Pearson's Correlation
@ingroup grp_desc_stats
+ @defgroup grp_summary Summary
+ @ingroup grp_desc_stats
+
@defgroup grp_inf_stats Inferential Statistics
@ingroup grp_stats
@{A collection of methods to compute inferential statistics on a dataset.@}
@@ -211,6 +212,9 @@ complete matrix stored as a distributed table.
@defgroup grp_sparse_linear_solver Sparse Linear Systems
@ingroup grp_linear_solver
+ @defgroup grp_path Path Functions
+ @ingroup grp_utility_functions
+
@defgroup grp_pmml PMML Export
@ingroup grp_utility_functions
@@ -234,7 +238,6 @@ Interface and implementation are subject to change.
@defgroup grp_cg Conjugate Gradient
@defgroup grp_bayes Naive Bayes Classification
- @defgroup grp_path Path functions
@defgroup grp_sample Random Sampling
@defgroup grp_kernmach Support Vector Machines
@}
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/360f1348/src/ports/postgres/modules/utilities/path.py_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/path.py_in b/src/ports/postgres/modules/utilities/path.py_in
index 64fd3c0..947e565 100644
--- a/src/ports/postgres/modules/utilities/path.py_in
+++ b/src/ports/postgres/modules/utilities/path.py_in
@@ -28,22 +28,19 @@ import string
import re
from control import MinWarning
-from utilities import unique_string
-from utilities import _assert
-from utilities import add_postfix
+from utilities import unique_string, _assert, add_postfix
+from utilities import py_list_to_sql_string
from validate_args import get_cols
-from validate_args import input_tbl_valid
-from validate_args import output_tbl_valid
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
from validate_args import quote_ident
# ------------------------------------------------------------------------
-
m4_changequote(`<!', `!>')
def path(schema_madlib, source_table, output_table, partition_expr,
order_expr, pattern_expr, symbol_expr, agg_func,
- persist_rows=False, **kwargs):
+ persist_rows=None, **kwargs):
"""
Perform regular pattern matching over a sequence of rows.
@@ -64,18 +61,15 @@ def path(schema_madlib, source_table, output_table, partition_expr,
as_partition_expr = "1 = 1 as all"
else:
as_partition_expr = partition_expr
+ if persist_rows is None:
+ # persist_rows = None implies no preference
+ # persist_rows = False implies do not store the matched rows
+ persist_rows = not bool(agg_func)
+
_validate(source_table, output_table, partition_expr, order_expr,
pattern_expr, symbol_expr, agg_func, persist_rows)
- # replace each occurence of the original symbol with the new
- # perform this operation in descending order of length to avoid substituting
- # subset of any symbol
- sym_mapping, sym_str = _parse_symbol_str(symbol_expr)
- old_sym_desc = list(reversed(sorted(sym_mapping.keys(), key=len)))
- replace_pattern = re.compile('|'.join(old_sym_desc), re.IGNORECASE)
- pattern_expr = replace_pattern.sub(
- lambda m: sym_mapping[re.escape(string.lower(m.group(0)))],
- pattern_expr)
+ new_pattern_expr, long_sym_str, short_sym_str = _parse_symbol_str(symbol_expr, pattern_expr)
# build variables for intermediate objects
input_with_id = unique_string('input_with_id')
@@ -83,8 +77,13 @@ def path(schema_madlib, source_table, output_table, partition_expr,
id_col_name = unique_string('id_col')
matched_partitions = unique_string('matched_partitions')
seq_gen = unique_string('seq_gen')
- symbol_name_str = unique_string('symbol')
- all_input_cols_str = ', '.join(get_cols(source_table, schema_madlib))
+ short_sym_name_str = unique_string('short_sym')
+ long_sym_name_str = unique_string('long_sym')
+ match_to_row_id = unique_string('match_to_row_id')
+ match_id = unique_string('match_id')
+
+ all_input_cols = [i.strip() for i in get_cols(source_table, schema_madlib)]
+ all_input_cols_str = ', '.join(all_input_cols)
if persist_rows:
matched_rows = add_postfix(output_table, "_tuples")
table_or_view = 'TABLE'
@@ -100,16 +99,18 @@ def path(schema_madlib, source_table, output_table, partition_expr,
p_col_name_str = ', '.join(p_col_names)
distribution = m4_ifdef(<!__POSTGRESQL__!>, <!''!>,
<!"DISTRIBUTED BY ({0})".format(p_col_name_str)!>)
- plpy.execute("CREATE SEQUENCE " + seq_gen)
plpy.execute("""
CREATE TEMP TABLE {input_with_id} AS
SELECT
{p_col_as_str},
*,
- nextval('{seq_gen}') AS {id_col_name},
+ row_number() OVER() AS {id_col_name},
CASE
- {sym_str}
- END AS {symbol_name_str}
+ {short_sym_str}
+ END AS {short_sym_name_str},
+ CASE
+ {long_sym_str}
+ END AS {long_sym_name_str}
FROM {source_table}
{distribution}
""".format(**locals()))
@@ -118,140 +119,171 @@ def path(schema_madlib, source_table, output_table, partition_expr,
# string produced by concatenating the symbols. The exact rows that
# produce the match are identified by correlating the matched string
# indices with another array containing row ids.
- #
- # matched_partitions: For each partition (group), concatenate all symbols
- # into a single string (sym_str). Keep corresponding ids in an array in the
- # same order as the symbols. This is performed only for partitions
- # that contain a match.
- # build_multiple_matched_rows:
- # q1: Split sym_str into an array containing the lengths of the
- # strings between the matches.
- # q2: Store lengths of matches into an array
- # q3: Merge q1 and q2 and unnest the arrays (ensuring same length).
- # Also right shift the matches array.
- # q4: Compute the cumulative sum of the arrays.
+
+ # matched_partitions: For each partition, concatenate all symbols
+ # into a single string (sym_str). Keep corresponding ids in an
+ # array (match_to_row_id) in the same order as the symbols.
+ # This is performed only for partitions that contain a match.
+ match_id_name = "__madlib_path_match_id__" if "match_id" in all_input_cols else "match_id"
+ symbol_name = "__madlib_path_symbol__" if "symbol" in all_input_cols else "symbol"
plpy.execute("""
CREATE TEMP TABLE {matched_partitions} AS
SELECT
{p_col_name_str},
- array_to_string(array_agg({symbol_name_str} ORDER BY {order_expr}), '') as sym_str,
- array_agg({id_col_name} ORDER BY {order_expr}) as matched_ids
+ array_to_string(array_agg({short_sym_name_str} ORDER BY {order_expr}), '') as sym_str,
+ array_agg({id_col_name} ORDER BY {order_expr}) as {match_to_row_id}
FROM {input_with_id}
+ WHERE {short_sym_name_str} is NOT NULL
GROUP BY {p_col_name_str}
- HAVING array_to_string(array_agg({symbol_name_str} ORDER BY {order_expr}), '') ~* '{pattern_expr}'
+ HAVING array_to_string(array_agg({short_sym_name_str} ORDER BY {order_expr}), '')
+ ~* '{new_pattern_expr}'
+ """.format(**locals()))
+
+ # length_of_matches: For each partition in matched_partitions:
+ # - find all matches and compute the lengths of each match.
+ # - output these lengths in an array (matches), along with
+ # an array of corresponding rank of each match (match_indices).
+ length_of_matches = unique_string("match_length_view")
+ plpy.execute("""
+ CREATE VIEW {length_of_matches} AS
+ SELECT
+ {p_col_name_str},
+ {match_to_row_id},
+ array_agg(length(matches) ORDER BY match_index) AS matches,
+ array_agg(match_index ORDER BY match_index) AS match_indices
+ FROM (
+ SELECT
+ {p_col_name_str},
+ {match_to_row_id},
+ generate_series(1, num_matches) AS match_index,
+ (regexp_matches(
+ sym_str, '(?i)({new_pattern_expr})', 'g'))[1] AS matches
+ FROM (
+ SELECT
+ {p_col_name_str},
+ {match_to_row_id},
+ sym_str,
+ count(matches) AS num_matches
+ FROM (
+ SELECT
+ {p_col_name_str},
+ {match_to_row_id},
+ sym_str,
+ (regexp_matches(
+ sym_str, '(?i)({new_pattern_expr})', 'g'))[1] AS matches
+ FROM
+ {matched_partitions}
+ ) t1
+ GROUP BY {p_col_name_str}, {match_to_row_id}, sym_str
+ ) t2
+ ) subq2
+ GROUP BY {p_col_name_str}, {match_to_row_id}
""".format(**locals()))
+
+ # length_of_between_matches: For each partition in matched_partitions:
+ # - find all matches and compute the lengths of text between the matches.
+ # - output these lengths in an array (match_splits)
+ length_of_between_matches = unique_string('match_split_view')
+ plpy.execute("""
+ CREATE VIEW {length_of_between_matches} AS
+ SELECT
+ {p_col_name_str},
+ array_agg(length(match_splits) ORDER BY match_index) AS match_splits
+ FROM (
+ SELECT
+ {p_col_name_str},
+ generate_series(1, ARRAY_UPPER(match_splits, 1)) AS match_index,
+ unnest(match_splits) AS match_splits
+ FROM (
+ SELECT
+ {p_col_name_str},
+ regexp_split_to_array(sym_str, '(?i){new_pattern_expr}') AS match_splits
+ FROM
+ {matched_partitions}
+ ) ssubq1
+ ) subq1
+ GROUP BY {p_col_name_str}
+ """.format(**locals()))
+ # build_multiple_matched_rows:
+ # q1: Lengths of the strings between the matches (length_of_between_matches)
+ # q2: Lengths of matches (length_of_matches)
+ # q3: Merge q1 and q2 and unnest the arrays (ensuring same length).
+ # Since q2 represents the following match to each split in q1,
+ # right shifting q2 (prev_matches) gives the preceeding match to each split.
+ # q4: Compute the cumulative sum of the arrays.
+
build_multiple_matched_rows = """
CREATE {table_or_view} {matched_rows} AS
- SELECT {all_input_cols_str}
+ SELECT {all_input_cols_str},
+ {long_sym_name_str} AS {symbol_name},
+ {match_id} AS {match_id_name}
FROM
{input_with_id} as source,
(
+ -- get the actual row ids using the start/end values
SELECT
- unnest(matched_ids[l:r]) AS matched_ids
+ unnest({match_to_row_id}[s:e]) AS {match_to_row_id},
+ match_index AS {match_id}
FROM
(
+ -- unnest to get tuples where each tuple gives the start/end of a match
SELECT
- matched_ids,
- unnest(left_range) AS l,
- unnest(right_range) AS r
+ {match_to_row_id},
+ unnest(match_start) AS s,
+ unnest(match_end) AS e,
+ unnest(match_indices) as match_index
FROM (
+ -- use cumulative sum to get the start and end of each match
+ -- (counting from start of symbol string)
SELECT
{p_col_name_str},
- matched_ids,
- {m}.array_cum_sum({m}.array_add(match_splits, prev_matches)) AS left_range,
- {m}.array_cum_sum({m}.array_add(match_splits, matches)) AS right_range
+ {match_to_row_id},
+ {m}.array_cum_sum({m}.array_add(match_splits, prev_matches)) AS match_start,
+ {m}.array_cum_sum({m}.array_add(match_splits, matches)) AS match_end,
+ match_indices
FROM (
+ -- right shift matches to get array of preceeding matches
SELECT
- {p_col_name_str}, q1.matched_ids,
+ {p_col_name_str},
+ {match_to_row_id},
+ matches,
+ match_indices,
match_splits[1:array_upper(matches, 1)] AS match_splits,
- matches AS matches,
array_prepend(1, matches[1:array_upper(matches, 1) - 1]) AS prev_matches
- FROM (
- -- lengths of text between matches
- SELECT
- {p_col_name_str},
- matched_ids,
- array_agg(length(match_splits) ORDER BY match_index) AS match_splits
- FROM (
- SELECT
- {p_col_name_str},
- matched_ids,
- generate_series(1, array_upper(match_splits, 1)) AS match_index,
- unnest(match_splits) AS match_splits
- FROM (
- SELECT
- {p_col_name_str},
- matched_ids,
- regexp_split_to_array(sym_str, '(?i){pattern_expr}') AS match_splits
- FROM
- {matched_partitions}
- ) ssubq1
- ) subq1
- GROUP BY {p_col_name_str}, matched_ids
- ) q1
+ FROM
+ {length_of_between_matches} AS q1
JOIN
- (
- -- lengths of all matches
- SELECT
- {p_col_name_str},
- matched_ids,
- array_agg(length(matches) ORDER BY match_index) AS matches
- FROM (
- SELECT
- {p_col_name_str},
- matched_ids,
- generate_series(1, num_matches) AS match_index,
- (regexp_matches(
- sym_str, '(?i)({pattern_expr})', 'g'))[1] AS matches
- FROM (
- SELECT
- {p_col_name_str},
- matched_ids,
- sym_str,
- count(matches) AS num_matches
- FROM (
- SELECT
- {p_col_name_str},
- matched_ids,
- sym_str,
- (regexp_matches(
- sym_str, '(?i)({pattern_expr})', 'g'))[1] AS matches
- FROM
- {matched_partitions}
- ) t1
- GROUP BY {p_col_name_str}, matched_ids, sym_str
- ) t2
- ) subq2
- GROUP BY {p_col_name_str}, matched_ids
- ) q2
+ {length_of_matches} as q2
USING ({p_col_name_str})
- GROUP BY {p_col_name_str}, q1.matched_ids, match_splits, matches
) q3
) q4
) q5
) matched_rows
- WHERE source.{id_col_name} = matched_rows.matched_ids
+ WHERE source.{id_col_name} = matched_rows.{match_to_row_id}
""".format(m=schema_madlib, **locals())
plpy.execute(build_multiple_matched_rows)
quoted_split_p_cols = [quote_ident(i) for i in split_p_cols]
p_col_orig_name_str = ','.join(
[i + " AS " + j for i, j in zip(p_col_names, quoted_split_p_cols)])
- plpy.execute("""
- CREATE TABLE {output_table} AS
- SELECT
- {as_partition_expr},
- {agg_func}
- FROM {matched_rows}
- GROUP BY {partition_expr}
- """.format(**locals()))
+ if agg_func:
+ plpy.execute("""
+ CREATE TABLE {output_table} AS
+ SELECT
+ {as_partition_expr},
+ {match_id_name},
+ {agg_func}
+ FROM {matched_rows}
+ GROUP BY {as_partition_expr}, {match_id_name}
+ """.format(**locals()))
+ result = "Aggregation result available in table " + output_table
+ else:
+ result = "No aggregation table created"
if not persist_rows:
plpy.execute("DROP VIEW IF EXISTS " + matched_rows)
- plpy.execute("DROP TABLE IF EXISTS " + input_with_id)
- plpy.execute("DROP SEQUENCE IF EXISTS " + seq_gen)
- result = "Result available in table " + output_table
- if persist_rows:
- result += "\n Matched tuples can be found in table " + matched_rows
+ else:
+ result += "\n Matched tuples can be found in table " + matched_rows
+ plpy.execute("DROP TABLE IF EXISTS " + input_with_id)
return result
# ------------------------------------------------------------------------------
@@ -263,65 +295,113 @@ def _validate(source_table, output_table, partition_expr, order_expr,
if persist_rows:
output_tbl_valid(add_postfix(output_table, "_tuples"), 'Path')
- plpy.execute("""SELECT {partition_expr}
- FROM {source_table}
- ORDER BY {order_expr}
- LIMIT 0
- """.format(**locals()))
# ensure the expressions are not None or empty strings
_assert(partition_expr, "Path error: Invalid partition expression")
_assert(order_expr, "Path error: Invalid order expression")
+ # ensure the partition/order expression can actually be used
+ _assert(is_var_valid(source_table, partition_expr, order_expr),
+ "Path error: invalid partition expression or order expression")
+
_assert(pattern_expr, "Path error: Invalid pattern expression")
_assert(symbol_expr, "Path error: Invalid symbol expression")
- _assert(agg_func, "Path error: Invalid result expression")
# ----------------------------------------------------------------------
-def _parse_symbol_str(symbol_expr):
- """ Parse symbol definition string to build a CASE statement string
+def _parse_symbol_str(symbol_expr, pattern_expr):
+ """ Parse symbol definition to build a CASE statement string
and return a mapping of the definitions.
- We currently only allow single-character symbols. To allow input for
- multicharacter symbol, we map the symbols to a single character and
- return a dictionary that provides the mapping from old symbol to new symbol.
+ Currently only single-character symbols are allowed.
+ Postgresql regular expression match functions will be used on a string
+ of symbols, where each symbol represents a tuple. Only single-length
+ symbols can be used to maintain 1:1 correspondence between symbol and tuple.
+
+ To allow input for multicharacter symbol for user convenience, inputed
+ symbols are mapped to a single character in the pattern expression.
+ This updated pattern expression is returned back to the caller.
+
+ Further, two case statements are built to mark each tuple in the input table
+ with the corresponding original (user-supplied) symbol and the
+ new (single-character) symbol.
+
+ Args:
+ @param symbol_expr: str, A comma-separated string containing
+ symbol definitions of the form: <symbol> := <symbol_definition>
+ @param sym_mapping_tbl: str, Name of the table to output the
+ correspondence table between symbol
+ @param pattern_expr: str, The pattern expression where the original
+ symbols are used. The original symbols are replaced by the corresponding
+ new symbols.
- symbol_expr: A comma-separated string containing symbol definitions of the form:
- <symbol> := <symbol_definition>
Example:
symbol_expr = ('BEFORE:=start >= \'0:00:00\' and start < \'9:30:00\', '
'MARKET:=start >= \'9:30:00\' and start < \'16:00:00\'')
- output = ({'BEFORE': 'a',
- 'MARKET': 'b'},
- "CASE
- WHEN BEFORE THEN start >= \'0:00:00\' and start < \'9:30:00\'
- WHEN MARKET THEN start >= \'9:30:00\' and start < \'16:00:00\'
+ pattern_expr = "(BEFORE)*(MARKET)*"
+
+ returns ("a*b*",
+ "CASE
+ WHEN start >= \'0:00:00\' and start < \'9:30:00\' THEN 'BEFORE'
+ WHEN start >= \'9:30:00\' and start < \'16:00:00\' THEN 'MARKET'
+ END",
+ "CASE
+ WHEN start >= \'0:00:00\' and start < \'9:30:00\' THEN 'a'
+ WHEN start >= \'9:30:00\' and start < \'16:00:00\' THEN 'b'
END"
+ Returns:
+ (str, str, str)
"""
-
+ # all_symbols is all valid single-character symbols
all_symbols = iter(string.ascii_lowercase + string.digits)
symbol_expr_parser = shlex.shlex(symbol_expr)
symbol_expr_parser.wordchars = [i for i in string.printable
if i not in (symbol_expr_parser.quotes + ",")]
symbol_expr_parser.whitespace = ','
- symbol_splits = list(symbol_expr_parser)
- symbol_definitions = {}
- symbol_mapping = {}
- for each_sym in symbol_splits:
- each_sym_splits = each_sym.split(":=")
- if len(each_sym_splits) == 2:
- old_sym, sym_def = each_sym_splits
- old_sym = re.escape(old_sym.strip().lower())
+ # parse symbol expr to get the strings between commas
+ sym_def_parsed = list(symbol_expr_parser)
+
+ orig_symbols_ordered = []
+ orig_sym_definitions = {}
+ new_sym_definitions = {}
+ old_to_new = {}
+ for each_sym_def in sym_def_parsed:
+ # symbols are defined as a pair: <name> := <definition>
+ sym_def_split = each_sym_def.split(":=")
+ if len(sym_def_split) == 2:
+ orig_sym, sym_def = (i.strip() for i in sym_def_split)
+ orig_symbols_ordered.append(orig_sym)
try:
next_sym = all_symbols.next()
except StopIteration:
- plpy.error("Path error: Maximum number of symbols reached.")
- _assert(old_sym not in symbol_mapping,
+ plpy.error("Path error: Total symbols in the symbol expression "
+ "exceed maximum number of symbols allowed.")
+ # symbols are supposed to be case-insensitive. Use the lower-case
+ # version to maintain a mapping from original to new symbol name
+ orig_sym_lower = re.escape(orig_sym.lower())
+ _assert(orig_sym_lower not in old_to_new,
"Path error: Multipe definitions of a symbol")
- symbol_mapping[old_sym] = next_sym
- symbol_definitions[next_sym] = sym_def
- return (symbol_mapping,
- '\n'.join("WHEN {1} THEN '{0}'::text".format(key, val)
- for key, val in symbol_definitions.items()))
+ old_to_new[orig_sym_lower] = next_sym
+ orig_sym_definitions[orig_sym] = sym_def
+ new_sym_definitions[next_sym] = sym_def
+
+ # replace each occurence of the original symbol with the new
+ # perform this operation in descending order of length to avoid substituting
+ # subset of any symbol
+ old_symbols_desc = list(reversed(sorted(old_to_new.keys(), key=len)))
+ replace_pattern = re.compile('|'.join(old_symbols_desc), re.IGNORECASE)
+ new_pattern_expr = replace_pattern.sub(
+ lambda m: old_to_new[re.escape(string.lower(m.group(0)))],
+ pattern_expr)
+
+ # build a case statement to search a tuple for each definition and pick the
+ # appropriate symbol.
+ orig_sym_case_stmt = []
+ new_sym_case_stmt = []
+ case_stmt = "WHEN {d} THEN '{s}'::text"
+ for k in orig_symbols_ordered:
+ orig_sym_case_stmt.append(case_stmt.format(s=k, d=orig_sym_definitions[k]))
+ new_sym_case_stmt.append(case_stmt.format(s=old_to_new[k.lower()],
+ d=orig_sym_definitions[k]))
+ return (new_pattern_expr, '\n'.join(orig_sym_case_stmt), '\n'.join(new_sym_case_stmt))
# ----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/360f1348/src/ports/postgres/modules/utilities/path.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/path.sql_in b/src/ports/postgres/modules/utilities/path.sql_in
index a538449..91ccaaf 100644
--- a/src/ports/postgres/modules/utilities/path.sql_in
+++ b/src/ports/postgres/modules/utilities/path.sql_in
@@ -34,22 +34,43 @@ m4_include(`SQLCommon.m4')
<ul>
<li><a href="#syntax">Function Syntax</a></li>
<li><a href="#examples">Examples</a></li>
+<li><a href="#nomenclature">Nomenclature</a></li>
+<li><a href="#literature">Literature</a></li>
</ul>
</div>
-@brief Path functions
+@brief Path Functions
@details The goal of the MADlib path function is to perform regular pattern
-matching over a sequence of rows, and to extract useful information about the
-matches. The useful information could be a simple count of matches or
-something more involved like aggregation.
-
-There are many use cases where path functions are typically used:
-- Web analytics (clickstream)
-- Marketing revenue attribution
-- Telephone calling patterns
-- Stock market trading sequences
-- Predictive maintenance
-- Genomics sequencing
+matching over a sequence of rows, and to extract useful information about
+the pattern matches. The useful information could be a simple count of
+matches or something more involved like aggregations or window functions.
+
+Symbols are used to identify particular rows of interest. Then, standard
+PostgreSQL pattern matching using symbols can be applied to identify
+patterns across the rows of interest. (This is similar in concept to
+regular expressions which match patterns within strings of text.)
+
+For example, a symbol can be defined for purchase events by on-line
+shoppers. Then, preceding events that led to the purchase can be
+identified and operated on, perhaps to find the common actions that
+resulted in a purchase. Or conversely, to find actions that resulted
+in an exit without a purchase having been made.
+
+Steps on how to use path functions:
+
+1. Partition input rows.
+
+2. Order the partitions.
+
+3. Define symbols to match rows of interest.
+
+4. Define regular expression of symbols and operators to define
+patterns to match in your ordered partitions.
+
+5. Define an aggregate function to compute for each pattern match.
+
+6. If desired, output the pattern matches for inspection or to
+operate on them with subsequent queries.
@anchor syntax
@par Function Syntax
@@ -69,36 +90,53 @@ path(
\b Arguments
<dl class="arglist">
<dt>source_table</dt>
- <dd>VARCHAR. Name of the source table, containing data for path analysis.</dd>
+ <dd>VARCHAR. Name of the source table, containing data for path
+ analysis.</dd>
<dt>output_table</dt>
- <dd>VARCHAR. Name of the result table. This table contains columns
- predicated by the <em>result</em> argument (shown below).</dd>
+ <dd>VARCHAR. Name of the result table.
<dt>partition_expr</dt>
<dd>VARCHAR. The 'partition_expr' can be a single column or a list of
comma-separated columns/expressions to divide all rows into groups,
- or partitions, that share the same values of the partition expression(s).
- The matching is applied across the rows that fall into the same partition.
- This can be NULL or '' to indicate the matching is to be applied on the
- whole table.</dd>
+ or partitions. Matching is applied across the rows that fall into t
+ he same partition. This can be NULL or '' to indicate the matching is
+ to be applied to the whole table.</dd>
<dt>order_expr</dt>
<dd>VARCHAR. This expression controls the order in which rows are
- processed or matched in a partition.
+ processed or matched in a partition. For example, time is a common way
+ to order partitions.
</dd>
<dt>symbol</dt>
- <dd>VARCHAR. A symbol represents a row of a particular type that you’re
- searching for as part of a row sequence. In the SYMBOLS clause, you write a
- predicate to define the type of row that matches the symbol.
+ <dd>VARCHAR. Symbols enable you to express patterns of interest in a
+ simple way (see definition of ‘pattern’ argument below). A symbol
+ identifies a row of a particular type that you’re searching for as
+ part of a pattern match. Symbol definition uses the standard
+ PostgreSQL assignment statement 'identifier := expression;' [1].
+ A given row can only match one symbol. If a row matches multiple
+ symbols, the symbol that comes first in the symbol definition list
+ will take precedence.
</dd>
<dt>pattern</dt>
- <dd>VARCHAR. The PATTERN clause defines the pattern that path searches for.
- You express the PATTERN using symbols and operators. Symbols defined using more
- than 1 characters need to be wrapped in parentheses '()'.
- The following pattern-matching metacharacters are supported:
+ <dd>VARCHAR. The 'pattern' clause defines the pattern that the path
+ algorithm searches for. You express the pattern using symbols and
+ operators following regular PostgreSQL pattern matching syntax and
+ rules [2].
+
+@anchor note
+@note
+ Symbols defined using more than one (1) character need to be
+ enclosed in parentheses '()' when referenced in the 'pattern'
+ argument. For example:
+ - a symbol defined as 'a' in the 'symbol' argument can be used directly
+ in the 'pattern' argument
+ - a symbol defined as 'abc' in the 'symbol' argument must be written as
+ '(abc)' in the 'pattern' argument
+
+ The following pattern matching metacharacters are supported:
<ul>
<li>| denotes alternation (either of two alternatives). </li>
<li>? denotes repetition of the previous item zero or one time. </li>
@@ -112,13 +150,20 @@ path(
</dd>
<dt>aggregate_func</dt>
- <dd>VARCHAR. A comma-separated list of window functions and aggregates to be
- applied on the matched window.
+ <dd>VARCHAR. A comma-separated list of aggregates to be
+ applied to the pattern matches [3]. Please note that window functions
+ cannot currently be used in the parameter 'aggregate_func'. If you want
+ to use a window function [4], output the pattern matches and write a SQL
+ query with a window function over the output tuples (see 'persist_rows'
+ parameter below).
+
+ If you just want to output the pattern matched rows and not compute any
+ aggregates, you can put NULL or '' in the 'aggregate_func' parameter.
</dd>
<dt>persist_rows</dt>
- <dd>BOOLEAN. If TRUE the matched rows are persisted in another table.
- This table is named as <output_table>_tuples (the string
+ <dd>BOOLEAN. If TRUE the matched rows are persisted in a separate output
+ table. This table is named as <output_table>_tuples (the string
"_tuples" is added as suffix to the value of <em>output_table</em>).
</dd>
@@ -128,62 +173,301 @@ path(
@anchor examples
@examp
-- Build sample dataset
+The data set describes shopper behavior on a notional web site that sells
+beer and wine. A beacon fires an event to a log file when the shopper
+visits different pages on the site: landing page, beer selection page,
+wine selection page, and checkout. Other pages on the site like help
+pages show up in the logs as well. Let’s assume that the log has been
+sessionized.
+
+Create the date table:
+
+<pre class="example">
+DROP TABLE IF EXISTS eventlog, path_output, path_output_tuples;
+CREATE TABLE eventlog (event_timestamp TIMESTAMP,
+ user_id INT,
+ session_id INT,
+ page TEXT,
+ revenue FLOAT);
+INSERT INTO eventlog VALUES
+('04/15/2015 01:03:00', 100821, 100, 'LANDING', 0),
+('04/15/2015 01:04:00', 100821, 100, 'WINE', 0),
+('04/15/2015 01:05:00', 100821, 100, 'CHECKOUT', 39),
+('04/15/2015 02:06:00', 100821, 101, 'WINE', 0),
+('04/15/2015 02:09:00', 100821, 101, 'WINE', 0),
+('04/15/2015 01:15:00', 101121, 102, 'LANDING', 0),
+('04/15/2015 01:16:00', 101121, 102, 'WINE', 0),
+('04/15/2015 01:17:00', 101121, 102, 'CHECKOUT', 15),
+('04/15/2015 01:18:00', 101121, 102, 'LANDING', 0),
+('04/15/2015 01:19:00', 101121, 102, 'HELP', 0),
+('04/15/2015 01:21:00', 101121, 102, 'WINE', 0),
+('04/15/2015 01:22:00', 101121, 102, 'CHECKOUT', 23),
+('04/15/2015 02:15:00', 101331, 103, 'LANDING', 0),
+('04/15/2015 02:16:00', 101331, 103, 'WINE', 0),
+('04/15/2015 02:17:00', 101331, 103, 'HELP', 0),
+('04/15/2015 02:18:00', 101331, 103, 'WINE', 0),
+('04/15/2015 02:19:00', 101331, 103, 'CHECKOUT', 16),
+('04/15/2015 02:22:00', 101443, 104, 'BEER', 0),
+('04/15/2015 02:25:00', 101443, 104, 'CHECKOUT', 12),
+('04/15/2015 02:29:00', 101881, 105, 'LANDING', 0),
+('04/15/2015 02:30:00', 101881, 105, 'BEER', 0),
+('04/15/2015 01:05:00', 102201, 106, 'LANDING', 0),
+('04/15/2015 01:06:00', 102201, 106, 'HELP', 0),
+('04/15/2015 01:09:00', 102201, 106, 'LANDING', 0),
+('04/15/2015 02:15:00', 102201, 107, 'WINE', 0),
+('04/15/2015 02:16:00', 102201, 107, 'BEER', 0),
+('04/15/2015 02:17:00', 102201, 107, 'WINE', 0),
+('04/15/2015 02:18:00', 102871, 108, 'BEER', 0),
+('04/15/2015 02:19:00', 102871, 108, 'WINE', 0),
+('04/15/2015 02:22:00', 102871, 108, 'CHECKOUT', 21),
+('04/15/2015 02:25:00', 102871, 108, 'LANDING', 0),
+('04/15/2015 02:17:00', 103711, 109, 'BEER', 0),
+('04/15/2015 02:18:00', 103711, 109, 'LANDING', 0),
+('04/15/2015 02:19:00', 103711, 109, 'WINE', 0);
+</pre>
+
+-# Calculate the revenue by checkout:
<pre class="example">
-CREATE TABLE trades (id integer, sessionid integer, starttime timestamp, topic varchar, portfolio integer);
-COPY trades FROM STDIN DELIMITER AS '|';
-1|1|1/01/00 5:00 AM|Real-time Equity pricing|769
-1|2|1/01/00 5:30 AM|Real-time Index pricing|9898
-1|3|1/01/00 6:00 AM|Real-time Index pricing|9898
-1|4|1/01/00 5:00 AM|Calendar|98977
-1|5|1/01/00 10:00 AM|Real-time Equity pricing|769
-1|6|1/01/00 10:30 AM|Real-time Equity pricing|9898
-1|7|1/01/00 12:00 PM|Calendar|1325
-1|8|1/01/00 12:00 PM|Calendar|6777
-1|9|1/01/00 4:05 PM|Real-time Equity pricing|769
-1|10|1/01/00 4:10 PM|Report Dates and Analyst Recommendations|34346
-1|11|1/01/00 4:15 PM|Real-time Index pricing|9898
-1|12|1/01/00 4:20 PM|Snapshot Futures pricing|5568
-1|13|1/01/00 4:21 PM|Real-time Equity pricing|769
-1|14|1/01/00 4:25 PM|Bond security master|789
-1|15|1/01/00 10:00 PM|Bond pricing|55
-1|16|1/01/00 10:00 PM|Bond pricing|55
-1|17|1/01/00 10:00 PM|Bond pricing|55
-1|18|1/01/00 10:00 PM|Bond Yield Analysis|655
-1|19|1/01/00 10:00 PM|Bond pricing|55
-1|20|1/01/00 10:00 PM|Bond pricing|55
-2|1|1/04/00 12:00 AM|Real-time Equity pricing|769
-2|2|1/04/00 12:00 AM|Real-time Equity pricing|234234
-2|3|1/04/00 12:00 AM|Calendar|1325
-2|4|1/04/00 12:00 AM|Calendar|6777
-2|5|1/04/00 12:00 AM|Real-time Equity pricing|769
-2|6|1/04/00 12:00 AM|Report Dates and Analyst Recommendations|34346
-2|7|1/04/00 12:00 AM|Real-time Index pricing|9898
-2|8|1/04/00 12:00 AM|Snapshot Futures pricing|5568
-2|9|1/04/00 12:00 AM|Real-time Equity pricing|769
-2|10|1/04/00 12:00 AM|Bond security master|789
-2|11|1/04/00 12:00 AM|Bond pricing|55
-2|12|1/04/00 12:00 AM|Bond pricing|55
-2|13|1/04/00 12:00 AM|Bond pricing|55
-2|14|1/04/00 12:00 AM|Bond pricing|55
-\\.
-</pre>
-
-- Compare within each day for the same Topic/Portfolio across every user
+ SELECT madlib.path(
+ 'eventlog', -- Name of input table
+ 'path_output', -- Table name to store path results
+ 'session_id', -- Partition input table by session
+ 'event_timestamp ASC', -- Order partitions in input table by time
+ 'buy:=page=''CHECKOUT''', -- Define a symbol for checkout events
+ '(buy)', -- Pattern search: purchase
+ 'sum(revenue) as checkout_rev', -- Aggregate: sum revenue by checkout
+ TRUE -- Persist matches
+ );
+SELECT * FROM path_output ORDER BY session_id, match_id;
+</pre>
+Result:
+<pre class="result">
+ session_id | match_id | checkout_rev
+------------+----------+--------------
+ 100 | 1 | 39
+ 102 | 1 | 15
+ 102 | 2 | 23
+ 103 | 1 | 16
+ 104 | 1 | 12
+ 108 | 1 | 21
+(6 rows)
+</pre>
+Note that there are 2 checkouts within session 102, which is apparent
+from the 'match_id' column. This serves to illustrate that the
+'aggregate_func' operates on a <em>per pattern match</em> basis,
+not on a <em>per partition</em> basis. If in fact we wanted revenue
+by partition ('session_id' in this example), then we could do:
+<pre class="example">
+SELECT session_id, sum(checkout_rev) FROM path_output GROUP BY session_id ORDER BY session_id;
+</pre>
+Result:
+<pre class="result">
+ session_id | sum
+------------+-----
+ 100 | 39
+ 102 | 38
+ 103 | 16
+ 104 | 12
+ 108 | 21
+(5 rows)
+</pre>
+Since we set TRUE for 'persist_rows', we can view the associated
+pattern matches:
+<pre class="example">
+SELECT * FROM path_output_tuples ORDER BY session_id ASC, event_timestamp ASC;
+</pre>
+Result:
+<pre class="result">
+ event_timestamp | user_id | session_id | page | revenue | symbol | match_id
+---------------------+---------+------------+----------+---------+--------+----------
+ 2015-04-15 01:05:00 | 100821 | 100 | CHECKOUT | 39 | buy | 1
+ 2015-04-15 01:17:00 | 101121 | 102 | CHECKOUT | 15 | buy | 1
+ 2015-04-15 01:22:00 | 101121 | 102 | CHECKOUT | 23 | buy | 2
+ 2015-04-15 02:19:00 | 101331 | 103 | CHECKOUT | 16 | buy | 1
+ 2015-04-15 02:25:00 | 101443 | 104 | CHECKOUT | 12 | buy | 1
+ 2015-04-15 02:22:00 | 102871 | 108 | CHECKOUT | 21 | buy | 1
+(6 rows)
+</pre>
+Notice that the 'symbol' and 'match_id' columns are added to the
+right of the matched rows.
+
+-# We are interested in sessions with an order placed within 4 pages of
+entering the shopping site via the landing page. We represent this by the
+regular expression: '(land)[^(land)(buy)]{0,2}(buy)'. In other words, visit
+to the landing page followed by from 0 to 2 non-entry, non-sale pages,
+followed by a purchase. The SQL is as follows:
<pre class="example">
- SELECT madlib.path(
- 'trades',
- 'trades_out',
- 'starttime::date, topic, portfolio', \-\- each day of activity is looked at independently
- 'starttime', \-\- order by time
- 'BEFORE:=starttime::time >= ''0:00:00'' and starttime::time < ''9:30:00''::time,'
- 'MARKET:=starttime::time >= ''9:30:00'' and starttime::time < ''16:00:00''::time,'
- 'CLOSE:= starttime::time <= ''16:00:00'' and starttime::time < ''16:30:00''::time,'
- 'AFTER:= starttime::time <= ''16:30:00'' and starttime::time < ''24:00:00''::time',
- 'BEFORE*.MARKET+.CLOSE+.AFTER*', \-\- at least one event during each of MARKET and CLOSE, but gather up the rest
- 'count(*) as num_matches'
- )
+SELECT madlib.path(
+ 'eventlog', -- Name of input table
+ 'path_output', -- Table name to store path results
+ 'session_id', -- Partition input table by session
+ 'event_timestamp ASC', -- Order partitions in input table by time
+ 'land:=page=''LANDING'',
+ wine:=page=''WINE'',
+ beer:=page=''BEER'',
+ buy:=page=''CHECKOUT'',
+ other:=page<>''LANDING'' AND page<>''WINE'' AND page<>''BEER'' AND page<>''CHECKOUT''', -- Symbols for page types
+ '(land)[^(land)(buy)]{0,2}(buy)', -- Purchase within 4 pages entering site
+ 'sum(revenue) as checkout_rev', -- Aggregate: sum revenue by checkout
+ TRUE -- Persist matches
+ );
+SELECT * FROM path_output ORDER BY session_id, match_id;
+</pre>
+Result:
+<pre class="result">
+ session_id | match_id | session_rev
+------------+----------+-------------
+ 100 | 1 | 39
+ 102 | 1 | 15
+ 102 | 2 | 23
+(3 rows)
+</pre>
+Now view the associated pattern matches:
+<pre class="example">
+SELECT * FROM path_output_tuples ORDER BY session_id ASC, event_timestamp ASC;
+</pre>
+Result:
+<pre class="result">
+ event_timestamp | user_id | session_id | page | revenue | symbol | match_id
+---------------------+---------+------------+----------+---------+--------+----------
+ 2015-04-15 01:03:00 | 100821 | 100 | LANDING | 0 | land | 1
+ 2015-04-15 01:04:00 | 100821 | 100 | WINE | 0 | wine | 1
+ 2015-04-15 01:05:00 | 100821 | 100 | CHECKOUT | 39 | buy | 1
+ 2015-04-15 01:15:00 | 101121 | 102 | LANDING | 0 | land | 1
+ 2015-04-15 01:16:00 | 101121 | 102 | WINE | 0 | wine | 1
+ 2015-04-15 01:17:00 | 101121 | 102 | CHECKOUT | 15 | buy | 1
+ 2015-04-15 01:18:00 | 101121 | 102 | LANDING | 0 | land | 2
+ 2015-04-15 01:19:00 | 101121 | 102 | HELP | 0 | other | 2
+ 2015-04-15 01:21:00 | 101121 | 102 | WINE | 0 | wine | 2
+ 2015-04-15 01:22:00 | 101121 | 102 | CHECKOUT | 23 | buy | 2
+(10 rows)
+</pre>
+
+-# We may want to use a window function instead of an aggregate.
+Currently, only aggregates are supported in the core path function
+in the parameter 'aggregate_func'. However, you can write
+window functions on the output tuples to achieve the desired result.
+
+Continuing the previous example, let’s say we want to compute average
+revenue for checkouts within 4 pages of entering the shopping site
+via the landing page:
+<pre class="example">
+SELECT DATE(event_timestamp), user_id, session_id, revenue,
+ avg(revenue) OVER (PARTITION BY DATE(event_timestamp)) as avg_checkout_rev
+ FROM path_output_tuples
+ WHERE page='CHECKOUT'
+ ORDER BY user_id, session_id;
+</pre>
+Result:
+<pre class="result">
+ date | user_id | session_id | revenue | avg_checkout_rev
+------------+---------+------------+---------+------------------
+ 2015-04-15 | 100821 | 100 | 39 | 25.6666666666667
+ 2015-04-15 | 101121 | 102 | 15 | 25.6666666666667
+ 2015-04-15 | 101121 | 102 | 23 | 25.6666666666667
+(3 rows)
</pre>
+Here we are partitioning the window function by day because we want
+daily averages, although our sample data set only has a single day.
+
+-# Now we want to do a golden path analysis to find the most successful
+shopper paths through the site. Since our data set is small, we decide
+this means the most frequently viewed page just before a checkout is made:
+<pre class="example">
+SELECT madlib.path(
+ 'eventlog', -- Name of input table
+ 'path_output', -- Table name to store path results
+ 'session_id', -- Partition input table by session
+ 'event_timestamp ASC', -- Order partitions in input table by time
+ 'land:=page=''LANDING'',
+ wine:=page=''WINE'',
+ beer:=page=''BEER'',
+ buy:=page=''CHECKOUT'',
+ other:=page<>''LANDING'' AND page<>''WINE'' AND page<>''BEER'' AND page<>''CHECKOUT''', -- Symbols for page types
+ '[^(buy)](buy)', -- Pattern to match
+ 'array_agg(page ORDER BY session_id ASC, event_timestamp ASC) as page_path', -- Build array with shopper paths
+ FALSE -- Don't persist matches
+ );
+</pre>
+
+Now count the common paths and print the most frequent:
+
+<pre class="example">
+SELECT count(*), page_path from
+ (SELECT * FROM path_output) q
+GROUP BY page_path
+ORDER BY count(*) DESC
+LIMIT 10;
+</pre>
+
+Result:
+<pre class="result">
+ count | page_path
+-------+-----------------
+ 5 | {WINE,CHECKOUT}
+ 1 | {BEER,CHECKOUT}
+(2 rows)
+</pre>
+
+There are only 2 different paths. The wine page is viewed more frequently
+than the beer page just before checkout.
+
+@anchor note
+@note
+Please note some current limitations of the path algorithm. These
+limitations will be addressed in subsequent releases.
+- Window functions cannot currently be used in the parameter
+'aggregate_func'. Instead, output the pattern matches and write
+a SQL query with a window function over the output tuples.
+- Overlapping pattern matches are not supported. That is,
+a given row can only belong to one pattern match (non-overlapping).
+- A given row can only match one symbol. If a row matches
+multiple symbols, the symbol that comes <em>first</em> in the symbol
+definition list will take precedence.
+- Maximum number of symbols that can be defined is 35.
+- The columns 'match_id' and 'symbol' are generated by the path
+algorithm. If coincidently you have columns in your input data
+named 'match_id' or 'symbol', the system generated column names
+will be changed to "__madlib_path_match_id__" and "__madlib_path_symbol__"
+
+@anchor nomenclature
+@par Nomenclature
+
+Partition
+- scope of rows to be searched for pattern match
+- typical examples: user id, session id, portfolio id
+
+Order
+- sort order of input rows in partition
+- typical example: time
+
+Symbol
+- a row of a particular type that you’re searching for, that you want to
+include in a pattern
+
+Pattern
+- regular PostgreSQL pattern match expression of symbols and operators
+that you want to match across rows
+
+Pattern match
+- rows that result from a pattern match expression of symbols
+- can be multiple matches per partition
+
+@anchor literature
+@par Literature
+
+[1] PostgreSQL basic statements/assignment operator,
+http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html
+
+[2] PostgreSQL pattern matching,
+http://www.postgresql.org/docs/current/static/functions-matching.html
+
+[3] PostgreSQL aggregate functions,
+http://www.postgresql.org/docs/8.2/static/tutorial-agg.html
+
+[4] PostgreSQL window functions,
+http://www.postgresql.org/docs/8.4/static/tutorial-window.html
+
*/
-------------------------------------------------------------------------
@@ -205,6 +489,8 @@ m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');
-------------------------------------------------------------------------
-- Overloaded functions for default arguments ---------------
+
+-- Do not output matched rows by default
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.path(
source_table VARCHAR,
output_table VARCHAR,
@@ -214,10 +500,22 @@ CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.path(
pattern_expr VARCHAR,
agg_func VARCHAR
) RETURNS TEXT AS $$
- SELECT MADLIB_SCHEMA.path($1, $2, $3, $4, $5, $6, $7, FALSE)
+ SELECT MADLIB_SCHEMA.path($1, $2, $3, $4, $5, $6, $7, NULL)
$$ LANGUAGE SQL
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');
+-- if no agg func provided then output the matched rows only
+CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.path(
+ source_table VARCHAR,
+ output_table VARCHAR,
+ partition_expr VARCHAR,
+ order_expr VARCHAR,
+ symbol_expr VARCHAR,
+ pattern_expr VARCHAR
+) RETURNS TEXT AS $$
+ SELECT MADLIB_SCHEMA.path($1, $2, $3, $4, $5, $6, NULL, NULL)
+$$ LANGUAGE SQL
+m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');
-------------------------------------------------------------------------
-- To Implement -----------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/360f1348/src/ports/postgres/modules/utilities/test/path.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/test/path.sql_in b/src/ports/postgres/modules/utilities/test/path.sql_in
index c568938..e4d06e6 100644
--- a/src/ports/postgres/modules/utilities/test/path.sql_in
+++ b/src/ports/postgres/modules/utilities/test/path.sql_in
@@ -79,7 +79,7 @@ INSERT INTO weblog VALUES
/*
SELECT * FROM weblog ORDER BY event_timestamp ASC;
*/
-SELECT madlib.path(
+SELECT path(
'weblog', -- Name of the table
'"Path_output"', -- Table name to store the path results
'user_id', -- Partition expression to group the data table
@@ -96,3 +96,27 @@ SELECT assert(relative_error(array_agg(count), ARRAY[3, 3]) < 1e-6,
FROM "Path_output";
SELECT * FROM "Path_output_tuples";
+
+SELECT * FROM "Path_output";
+
+INSERT INTO weblog VALUES
+('04/15/2012 02:15:00', 101331, 2, 4, 'Female', 'East', 5, 0, 0, 0, 0),
+('04/15/2012 02:59:00', 101331, 2, 4, 'Female', 'East', 5, 1, 0, 0, 0),
+('04/15/2012 04:32:00', 101331, 2, 4, 'Female', 'East', 5, 1, 1, 112, 36);
+
+DROP TABLE "Path_output", "Path_output_tuples";
+SELECT path(
+ 'weblog', -- Name of the table
+ '"Path_output"', -- Table name to store the path results
+ 'user_id', -- Partition expression to group the data table
+ 'event_timestamp ASC', -- Order expression to sort the tuples of the data table
+ 'I:=click_event=0 AND purchase_event=0, Click:=click_event=1 AND purchase_event=0, Conv:=purchase_event=1', -- Definition of various symbols used in the pattern definition
+ 'I(click){1}(CONV){1}', -- Definition of the path pattern to search for
+ 'SUM(margin) as sum_of_margin, SUM(revenue) as sum_of_revenue', -- Aggregate/window functions to be applied on the matched paths
+ TRUE
+ );
+------------------------------------------------------------
+
+SELECT * FROM "Path_output_tuples";
+
+SELECT * FROM "Path_output";
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/360f1348/src/ports/postgres/modules/utilities/utilities.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/utilities.sql_in b/src/ports/postgres/modules/utilities/utilities.sql_in
index dc56d3b..ce46eff 100644
--- a/src/ports/postgres/modules/utilities/utilities.sql_in
+++ b/src/ports/postgres/modules/utilities/utilities.sql_in
@@ -23,11 +23,6 @@ m4_include(`SQLCommon.m4')
@brief Provides a collection of user-defined functions for performing common tasks in the database.
-\warning <em> This MADlib method is still in early stage development. There may be some
-issues that will be addressed in a future version. Interface and implementation
-is subject to change. </em>
-
-
The utility module consists of useful utility functions to assist data
scientists in using the product. Several of these functions can be used
while implementing new algorithms.
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/360f1348/src/ports/postgres/modules/utilities/validate_args.py_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/validate_args.py_in b/src/ports/postgres/modules/utilities/validate_args.py_in
index 7681489..77eb5c2 100644
--- a/src/ports/postgres/modules/utilities/validate_args.py_in
+++ b/src/ports/postgres/modules/utilities/validate_args.py_in
@@ -527,7 +527,7 @@ def array_col_has_no_null(tbl, col):
# -------------------------------------------------------------------------
-def is_var_valid(tbl, var):
+def is_var_valid(tbl, var, order_by=None):
"""
Test whether the variable(s) is valid by actually selecting it from
the table
@@ -535,11 +535,14 @@ def is_var_valid(tbl, var):
if var is None or var.lower().strip() == '':
return False
try:
+ order_by_str = "" if not order_by else "ORDER BY " + order_by
plpy.execute(
"""
- SELECT {var} FROM {tbl} LIMIT 0
- """.format(var=var,
- tbl=tbl))
+ SELECT {var}
+ FROM {tbl}
+ {order_by_str}
+ LIMIT 0
+ """.format(**locals()))
except Exception:
return False
return True