[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[njayaram2]

Github user njayaram2 commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65457433
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
+   @param time_out: float, Delta time between subsequent events to 
define a sessions
+   
+   """
+   with MinWarning("error"):
+   _validate(source_table, output_table, partition_expr, 
time_stamp, time_out)
+
+   all_input_cols_str = ', '.join([i.strip() for i in 
get_cols(source_table, schema_madlib)])
+   session_id = 'session_id' if not is_var_valid(source_table, 
'session_id') else unique_string('session_id')
+
+   plpy.execute("""
+   CREATE TABLE {output_table} AS
+   SELECT
+   {all_input_cols_str},
+   CASE WHEN {time_stamp} NOTNULL
--- End diff --

Are you suggesting we mandate that the input table has no NULL timestamps?

I tried leaving out the NOT NULL mentioned in the comment, but it will end 
up showing a session id for rows with NULL timestamps too if I do that. I 
wanted to make sure rows with NULL timestamps have no session ID at all, so had 
included the NOT NULL along with the NOT NULL inside the subselect.


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[njayaram2]

Github user njayaram2 commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65455496
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
+   @param time_out: float, Delta time between subsequent events to 
define a sessions
+   
+   """
+   with MinWarning("error"):
+   _validate(source_table, output_table, partition_expr, 
time_stamp, time_out)
+
+   all_input_cols_str = ', '.join([i.strip() for i in 
get_cols(source_table, schema_madlib)])
+   session_id = 'session_id' if not is_var_valid(source_table, 
'session_id') else unique_string('session_id')
+
+   plpy.execute("""
+   CREATE TABLE {output_table} AS
+   SELECT
+   {all_input_cols_str},
+   CASE WHEN {time_stamp} NOTNULL
+   THEN (SUM(new_event_boundary) 
OVER (PARTITION BY {partition_expr} ORDER BY {time_stamp})) END AS {session_id}
--- End diff --

I quickly tried the query with min_time proposed in MADLIB-1002, and it 
seems to be missing one of the requirements. Guess phase 3 might indeed have to 
stay as planned! :-/


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[decibel]

Github user decibel commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65454766
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
+   @param time_out: float, Delta time between subsequent events to 
define a sessions
+   
+   """
+   with MinWarning("error"):
+   _validate(source_table, output_table, partition_expr, 
time_stamp, time_out)
+
+   all_input_cols_str = ', '.join([i.strip() for i in 
get_cols(source_table, schema_madlib)])
+   session_id = 'session_id' if not is_var_valid(source_table, 
'session_id') else unique_string('session_id')
+
+   plpy.execute("""
+   CREATE TABLE {output_table} AS
+   SELECT
+   {all_input_cols_str},
+   CASE WHEN {time_stamp} NOTNULL
+   THEN (SUM(new_event_boundary) 
OVER (PARTITION BY {partition_expr} ORDER BY {time_stamp})) END AS {session_id}
--- End diff --

> If the min_time solution you have proposed in
> https://issues.apache.org/jira/browse/MADLIB-1002 works fine, do you
> think we should merge the sessionization phases 1 and 3?

Seems logical to me, but I'm not the one doing the work... ;)



---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[njayaram2]

Github user njayaram2 commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65446205
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
+   @param time_out: float, Delta time between subsequent events to 
define a sessions
+   
+   """
+   with MinWarning("error"):
+   _validate(source_table, output_table, partition_expr, 
time_stamp, time_out)
+
+   all_input_cols_str = ', '.join([i.strip() for i in 
get_cols(source_table, schema_madlib)])
+   session_id = 'session_id' if not is_var_valid(source_table, 
'session_id') else unique_string('session_id')
+
+   plpy.execute("""
+   CREATE TABLE {output_table} AS
+   SELECT
+   {all_input_cols_str},
+   CASE WHEN {time_stamp} NOTNULL
+   THEN (SUM(new_event_boundary) 
OVER (PARTITION BY {partition_expr} ORDER BY {time_stamp})) END AS {session_id}
--- End diff --

@decibel great comments! I think the refactored code you have put up in 
MADLIB-1002 incorporates most of the comments you have made here. I will also 
include the type casting suggestion and make the changes.

If the min_time solution you have proposed in 
https://issues.apache.org/jira/browse/MADLIB-1002 works fine, do you think we 
should merge the sessionization phases 1 and 3?


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[njayaram2]

Github user njayaram2 commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65445316
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
--- End diff --

You are right, that's a typo!


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[decibel]

Github user decibel commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65437304
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
+   @param time_out: float, Delta time between subsequent events to 
define a sessions
+   
+   """
+   with MinWarning("error"):
+   _validate(source_table, output_table, partition_expr, 
time_stamp, time_out)
+
+   all_input_cols_str = ', '.join([i.strip() for i in 
get_cols(source_table, schema_madlib)])
+   session_id = 'session_id' if not is_var_valid(source_table, 
'session_id') else unique_string('session_id')
+
+   plpy.execute("""
+   CREATE TABLE {output_table} AS
+   SELECT
+   {all_input_cols_str},
+   CASE WHEN {time_stamp} NOTNULL
+   THEN (SUM(new_event_boundary) 
OVER (PARTITION BY {partition_expr} ORDER BY {time_stamp})) END AS {session_id}
--- End diff --

I did some refactoring of this while taking a look at 
https://issues.apache.org/jira/browse/MADLIB-1002. Please take a look at it, as 
I think it's clearer than this code. *Note that I have not tested it for 
correctness!*


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] incubator-madlib pull request #44: Feature: Sessionize funtion

[decibel]

Github user decibel commented on a diff in the pull request:

https://github.com/apache/incubator-madlib/pull/44#discussion_r65424347
  
--- Diff: src/ports/postgres/modules/utilities/sessionize.py_in ---
@@ -0,0 +1,101 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import plpy
+import string
+
+from control import MinWarning
+from utilities import unique_string, _assert
+from validate_args import get_cols
+from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid
+
+m4_changequote(`')
+
+def sessionize(schema_madlib, source_table, output_table, partition_expr,
+   time_stamp, time_out, **kwargs):
+   """
+   Perform sessionization over a sequence of rows.
+
+   Args:
+   @param schema_madlib: str, Name of the MADlib schema
+   @param source_table: str, Name of the input table/view
+   @param output_table: str, Name of the table to store result
+   @param partition_expr: str, Expression to partition (group) the 
input data
+   @param time_stamp: float, Column name with time used for 
sessionization calculation
+   @param time_out: float, Delta time between subsequent events to 
define a sessions
+   
+   """
+   with MinWarning("error"):
+   _validate(source_table, output_table, partition_expr, 
time_stamp, time_out)
+
+   all_input_cols_str = ', '.join([i.strip() for i in 
get_cols(source_table, schema_madlib)])
+   session_id = 'session_id' if not is_var_valid(source_table, 
'session_id') else unique_string('session_id')
+
+   plpy.execute("""
+   CREATE TABLE {output_table} AS
+   SELECT
+   {all_input_cols_str},
+   CASE WHEN {time_stamp} NOTNULL
--- End diff --

Is it necessary to include rows with NULL timestamps? While that's not a 
big deal here, I think it's going to lead to unwanted complexity down the road 
(I'm looking at JIRA MADLIB-1002).


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

Sessionization min time param

[Frank McQuillan]

There has been some good discussion on the sessionization feature lately,
e.g.,
https://github.com/apache/incubator-madlib/pull/44
Thanks to Jim Nasby  for his comments.

I broke sessionization into 3 phases, of which Phase 1 and 2 are in work
now by Nandish Jayaram:

Sessionization - Phase 1
https://issues.apache.org/jira/browse/MADLIB-909

Sessionization - Phase 2 (output controls)
https://issues.apache.org/jira/browse/MADLIB-1001


But Phase 3 has no assignee if someone is interested in thinking about how
to implement a min time parameter, either with window functions or another
approach?

Sessionization - Phase 3 (minimum time)
https://issues.apache.org/jira/browse/MADLIB-1002

Frank