You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@madlib.apache.org by orhankislal <gi...@git.apache.org> on 2016/07/06 21:04:33 UTC
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
GitHub user orhankislal opened a pull request:
https://github.com/apache/incubator-madlib/pull/54
Pivoting: Phase 2
JIRA: MADLIB-1004
This phase of the pivoting development adds the following functionality
- Multiple indices
- Multiple pivot columns
- Multiple value columns
- Multiple aggregate functions
- Value column specific aggregate functions
- Keep null values in pivot columns
- Fill null values in the output table
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/orhankislal/incubator-madlib feature/pivoting_take5
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-madlib/pull/54.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #54
----
commit aa7bce4387487c7d96f3fcccde1e95250996fbf7
Author: Orhan Kislal <ok...@pivotal.io>
Date: 2016-07-06T21:00:29Z
Pivoting: Phase 2
JIRA: MADLIB-1004
This phase of the pivoting development adds the following functionality
- Multiple indices
- Multiple pivot columns
- Multiple value columns
- Multiple aggregate functions
- Value column specific aggregate functions
- Keep null values in pivot columns
- Fill null values in the output table
----
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib issue #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on the issue:
https://github.com/apache/incubator-madlib/pull/54
Out of 5 sql queries, I reduced the first 3 to single lines as you suggested. For the other two I would rather keep them broken into a list since they are created inside the 4 level loop.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib issue #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on the issue:
https://github.com/apache/incubator-madlib/pull/54
Could you please change all dynamically created SQL strings to have the query structure as a single string with variables filling in the dynamic entries? It's much easier to understand the query as a single string. Let me know if you need specific pointers.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71961127
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
--- End diff --
`... if not agg_set ...`
We need explanation on the special name.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72857563
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
To understand the behavior: if a user does not specify the `aggregate_func` parameter then we use a default. If this is a list then we apply that for all values. But it's not possible to say: use default for `val1` and use `val2=[a, b, c]`?
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72861263
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
I know it looks a bit overcomplicated, that is why I added the comments. You suggestion will look much cleaner. The only issue is we have to create a map that has every value column to the same aggregate(s) if the user doesn't need a map. It shouldn't be that costly since 1600 is still the upper limit.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72855881
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
Use the `agg_dict` directly as a bool to check if it's empty.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r70123955
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,164 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}' present"
+ " in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
+
+ # Find the distinct values of pivot_cols
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ distinct_str = ["SELECT "]
+ distinct_str.append(
+ ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols))
+ distinct_str.append(" FROM " + source_table)
+ distinct_values = plpy.execute(''.join(distinct_str))
+
+ # Collect the distinc values for every pivot column
+ pcol_distinct_values = []
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in
+ distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = filter(None,pcol_tmp)
--- End diff --
This removes 0 values in addition to NULLs. I replaced it with `pcol_tmp = [x for x in pcol_tmp if x is not None]`
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72856933
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
also why do we add `__madlib_pivot_def_agg__` to `agg_dict` and then check for `len > 1` instead of just checking for empty dict?
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib issue #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on the issue:
https://github.com/apache/incubator-madlib/pull/54
I see what you mean, should be easy enough.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71962137
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ distinct_str = ["SELECT "]
+ distinct_str.append(
+ ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols))
+ distinct_str.append(" FROM " + source_table)
+ distinct_values = plpy.execute(''.join(distinct_str))
+
+ # Collect the distinc values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ pcol_distinct_values[pcol]=pcol_tmp
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # For non postgres systems add null to the pcol_distinct_values if necessary
+ # This is required because DISTINCT keyword is implemented differently
+ is_pg = m4_ifdef(<!__POSTGRESQL__!>, True, False)
+ if keep_null and not is_pg:
+ null_str_begin = ["SELECT "]
--- End diff --
Again, this would be cleaner with the whole query as a single string.
Also you could simplify it by using `EXISTS (SELECT 1 FROM {tbl} WHERE {pcol} is NULL) as {p_col}_isnull, ...`
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71962292
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ distinct_str = ["SELECT "]
+ distinct_str.append(
+ ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols))
+ distinct_str.append(" FROM " + source_table)
+ distinct_values = plpy.execute(''.join(distinct_str))
+
+ # Collect the distinc values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ pcol_distinct_values[pcol]=pcol_tmp
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # For non postgres systems add null to the pcol_distinct_values if necessary
+ # This is required because DISTINCT keyword is implemented differently
+ is_pg = m4_ifdef(<!__POSTGRESQL__!>, True, False)
+ if keep_null and not is_pg:
+ null_str_begin = ["SELECT "]
+ null_str_begin.append(
+ ', '.join("array_agg({pcol}_isn) as {pcol}_isnull".
+ format(pcol=pcol) for pcol in pcols))
+ null_str_end = [" FROM (SELECT DISTINCT "]
+ null_str_end.append(
+ ', '.join(" (CASE WHEN {pcol} IS NULL THEN 1 END) "
+ " AS {pcol}_isn".
+ format(pcol=pcol) for pcol in pcols))
+ null_str_end.append(" FROM " + source_table +") x")
+ null_str = ''.join(null_str_begin) + ''.join(null_str_end)
+ null_values = plpy.execute(null_str)
+ for pcol in pcols:
+ pcol_tmp = [item for item in null_values[0][pcol+"_isnull"]]
+ if 1 in pcol_tmp:
+ pcol_distinct_values[pcol].append(None)
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
--- End diff --
I suggest keeping the product output as an iterator (i.e. don't make it a list) since you're only looping through it once. Further keep a count of number of pivot_comb seen while iterating and return a warning when you've reached the threshold. Finally, make the threshold a constant and initialize it at top with explanation on why it's set to that particular value (1000).
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71961433
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ distinct_str = ["SELECT "]
+ distinct_str.append(
+ ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols))
+ distinct_str.append(" FROM " + source_table)
+ distinct_values = plpy.execute(''.join(distinct_str))
--- End diff --
Why not create a single query string instead of joining them?
```
array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".format(pcol=pcol) for pcol in pcols)
plpy.execute("SELECT {0} FROM {1}".format(array_agg_str, source_table))
```
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72856491
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
I see. I got confused with this. So a user can't add some values in the agg_dict expecting default for the rest?
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib issue #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on the issue:
https://github.com/apache/incubator-madlib/pull/54
Even for the other two, I suggest keeping the static parts all together with only the dynamic part created in the loops. That avoids having the `SELECT` clause separated from the `FROM` by 70 lines. That avoids creating a variable `insert_sql_begin` 50+ lines from where it's used (and can be replaced by static string `INSERT INTO`).
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72857123
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
No, they either use a function(s) for every value column or provide a value to function(s) mapping.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71961445
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ distinct_str = ["SELECT "]
+ distinct_str.append(
+ ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols))
+ distinct_str.append(" FROM " + source_table)
+ distinct_values = plpy.execute(''.join(distinct_str))
+
+ # Collect the distinc values for every pivot column
--- End diff --
`s/distinc/distinct`
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-madlib/pull/54
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71960956
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
--- End diff --
why do we need this?
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72859110
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
Also I now see that `__madlib_pivot_def_agg__` isn't just the default agg but can also be the list of input agg. That is going to confuse folks in the future.
How about this: we have a single variable `agg_func_map` that is always a dict and contains mapping between value and aggregate list to apply. Depending on input (`aggregate_func`) we populate this mapping as follow:
1. if input is NULL then each `val` is mapped to `[avg]`
2. if input is an agg or a list of aggs then each `val` is mapped to `list | [agg]`
3. if input is a mapping then we start with 1 and update mapping for each `val` in input.
All of this logic can be in a separate function with good explanation. That way the main function remains simple: for each value we have a mapping that gives the agg to apply on that value.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72856182
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
agg_dict is not empty. It has either a single record (the default) or a list for each value column.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71961520
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
--- End diff --
It can be removed. I moved the default aggregate but forgot to remove this line.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72353399
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
+ else []
+ # If the list is empty set 'avg' as default
+ agg_dict['__madlib_def_agg__'] = ['avg'] if len(agg_set) < 1 else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ distinct_str = ["SELECT "]
+ distinct_str.append(
+ ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols))
+ distinct_str.append(" FROM " + source_table)
+ distinct_values = plpy.execute(''.join(distinct_str))
+
+ # Collect the distinc values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ pcol_distinct_values[pcol]=pcol_tmp
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # For non postgres systems add null to the pcol_distinct_values if necessary
+ # This is required because DISTINCT keyword is implemented differently
+ is_pg = m4_ifdef(<!__POSTGRESQL__!>, True, False)
+ if keep_null and not is_pg:
+ null_str_begin = ["SELECT "]
+ null_str_begin.append(
+ ', '.join("array_agg({pcol}_isn) as {pcol}_isnull".
+ format(pcol=pcol) for pcol in pcols))
+ null_str_end = [" FROM (SELECT DISTINCT "]
+ null_str_end.append(
+ ', '.join(" (CASE WHEN {pcol} IS NULL THEN 1 END) "
+ " AS {pcol}_isn".
+ format(pcol=pcol) for pcol in pcols))
+ null_str_end.append(" FROM " + source_table +") x")
+ null_str = ''.join(null_str_begin) + ''.join(null_str_end)
+ null_values = plpy.execute(null_str)
+ for pcol in pcols:
+ pcol_tmp = [item for item in null_values[0][pcol+"_isnull"]]
+ if 1 in pcol_tmp:
+ pcol_distinct_values[pcol].append(None)
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
--- End diff --
Thank you very much for the comments @iyerr3. The calculation for threshold happens if the code throws an exception. List is accessed multiple times if there are multiple value columns or aggregates. I fixed rest of the issues you pointed out.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r71961026
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,255 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ param_types['__madlib_def_pval__'] = list
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if len(agg_dict) < 1 \
--- End diff --
`... if not agg_dict ...`
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-madlib pull request #54: Pivoting: Phase 2
Posted by orhankislal <gi...@git.apache.org>.
Github user orhankislal commented on a diff in the pull request:
https://github.com/apache/incubator-madlib/pull/54#discussion_r72858556
--- Diff: src/ports/postgres/modules/utilities/pivot.py_in ---
@@ -58,66 +61,256 @@ def pivot(schema_madlib, source_table, out_table,
pivoted table
@param aggregate_func The aggregate function to be applied to the
values
+ @param fill_value If specified, determines how to fill NULL
+ values resulting from pivot operation
+ @param keep_null The flag for determining how to handle NULL
+ values in pivot columns
"""
-
"""
Assume we have the following table
pivset( id INTEGER, piv FLOAT8, val FLOAT8 )
- where the piv column has 3 distinct values (10.0, 20.0 and 30.0).
+ where the piv column has 3 distinct values (10, 20 and 30).
If the pivot function call is :
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
We want to construct the following sql code to pivot the table.
CREATE TABLE pivout AS (SELECT id,
- sum(CASE WHEN "piv" = '10.0' THEN val ELSE NULL END ) as "piv_10.0",
- sum(CASE WHEN "piv" = '20.0' THEN val ELSE NULL END ) as "piv_20.0",
- sum(CASE WHEN "piv" = '30.0' THEN val ELSE NULL END ) as "piv_30.0"
+ avg(CASE WHEN "piv" = '10' THEN val ELSE NULL END ) as "val_avg_piv_10",
+ avg(CASE WHEN "piv" = '20' THEN val ELSE NULL END ) as "val_avg_piv_20",
+ avg(CASE WHEN "piv" = '30' THEN val ELSE NULL END ) as "val_avg_piv_30"
FROM pivset GROUP BY id ORDER BY id)
"""
+
+ # If there are more than 1000 columns for the output table, we give a
+ # warning as it might give an error.
+ MAX_OUTPUT_COLUMN_COUNT = 1000
+
+ # If a column name has more than 63 characters it gets trimmed automaticly,
+ # which may cause an exception. We enable the output dictionary in this case
+ MAX_COLUMN_LENGTH = 63
+
indices = split_quoted_delimited_str(index)
- pcol = split_quoted_delimited_str(pivot_cols)
- pval = split_quoted_delimited_str(pivot_values)
- validate_pivot_coding(source_table, out_table, indices, pcol, pval)
- new_col_names =[]
- sql_list = ["CREATE TABLE " + out_table + " AS (SELECT " + index]
+ pcols = split_quoted_delimited_str(pivot_cols)
+ pvals = split_quoted_delimited_str(pivot_values)
+ validate_pivot_coding(source_table, out_table, indices, pcols, pvals)
+
+ # Strip end quotes from pivot columns
+ pcols = [strip_end_quotes(pcol.strip()) for pcol in pcols]
+ pvals = [strip_end_quotes(pval.strip()) for pval in pvals]
+
+ # Parse the aggregate_func as a dictionary
+ try:
+ param_types = dict.fromkeys(pvals, list)
+ agg_dict = extract_keyvalue_params(aggregate_func,param_types)
+ except KeyError, e:
+ with MinWarning("warning"):
+ plpy.warning("Pivot: Not all columns from '{aggregate_func}'"
+ " present in '{pivot_values}'".format(**locals()))
+ raise
+
+ # If the dictionary is empty, parse it as a list
+ agg_set = split_quoted_delimited_str(aggregate_func) if not agg_dict else []
- pcol_no_quotes = strip_end_quotes(pcol[0].strip())
- pval_no_quotes = strip_end_quotes(pval[0].strip())
+ # __madlib_pivot_def_agg__ denotes the aggregate function(s) if the user
+ # does not specify a value: aggregate dictionary
+ # If no aggregates are given, set average as default
+ agg_dict['__madlib_pivot_def_agg__'] = ['avg'] if not agg_set else agg_set
# Find the distinct values of pivot_cols
- distinct_values = plpy.execute(
- "SELECT array_agg(DISTINCT {pcol} ORDER BY {pcol}) AS value "
- "FROM {source_table}".
- format(pcol=pcol[0], source_table=source_table))
-
- distinct_values = [strip_end_quotes(item)
- for item in distinct_values[0]['value']]
- # The aggregate collects pivot_values values for a given pivot_cols value
- case_str = ("{agg}("
- "CASE WHEN \"{{pcol}}\" = '{{value}}' THEN {pval} ELSE NULL END"
- ")".
- format(agg=aggregate_func,
- pval=pval_no_quotes))
- sql_list.append(
- ", " +
- # Assign the name of the new column
- ', '.join("{case_str} as \"{{pcol}}_{{value}}\"".
- format(case_str=case_str).
- format(pcol=pcol_no_quotes, value=str(value))
- for value in distinct_values if value is not None))
+ # Note that the distinct values are appended in order of users list
+ # This ordering is important when we access pivot_comb entries
+ array_agg_str = ', '.join("array_agg(DISTINCT {pcol}) AS {pcol}_values".
+ format(pcol=pcol) for pcol in pcols)
+
+ null_str = ""
+ if keep_null:
+ # Create an additional column for every pivot column
+ # If there is a null value, this column will get True
+ null_str = ","+', '.join(
+ "bool_or(CASE WHEN {pcol} IS NULL THEN True END)"
+ "AS {pcol}_isnull".format(pcol=pcol) for pcol in pcols)
+
+ distinct_values = plpy.execute("SELECT {0} {1} FROM {2}".
+ format(array_agg_str, null_str, source_table))
+
+ # Collect the distinct values for every pivot column
+ pcol_distinct_values = {}
+ pcol_max_length = 0
+ for pcol in pcols:
+ # Read the distinct values for this pcol
+ pcol_tmp = [item for item in distinct_values[0][pcol+"_values"]]
+ # Remove null values if keep null is not true
+ if not keep_null:
+ pcol_tmp = [x for x in pcol_tmp if x is not None]
+ elif distinct_values[0][pcol+"_isnull"] and None not in pcol_tmp:
+ pcol_tmp.append(None)
+
+ pcol_distinct_values[pcol]=sorted(pcol_tmp)
+ # Max length of the string that pcol values can create +
+ # length of pcol + 1 (for _ character)
+ pcol_max_length+=max([len(str(item)) for item in pcol_tmp])+len(pcol)+1
+
+ # Create the combination of every possible pivot column
+ # Assume piv and piv2 are pivot columns. piv=(1,2) and piv2=(3,4,5)
+ # pivot_comb = ((1,3),(1,4),(1,5),(2,3),(2,4),(2,5))
+ pivot_comb = list(itertools.product(*([pcol_distinct_values[pcol]
+ for pcol in pcols])))
+ #Prepare the wrapper for fill value
+ fill_str_begin = ""
+ fill_str_end = ""
+ if fill_value is not None:
+ fill_str_begin = " COALESCE("
+ fill_str_end = ", "+fill_value+" ) "
+
+ # Check the max possible length of a output column name
+ # If it is over 63 (psql upper limit) create table lookup
+ for pval in pvals:
+
+ col_name_len = pcol_max_length+len(pval)+1
+ try:
+ # If user specifies a list of aggregates for a value column
+ # Every value column has to have an entry
+ agg_func = agg_dict[pval] if len(agg_dict) > 1 else \
--- End diff --
I added the default to the dictionary to keep track of one less variable name and keeping all of aggregate functions in the same list seemed like an acceptable idea.
Yes that is the behavior. I can change the behavior to assume the default function (avg) for the missing value columns but that will bring a new issue where the user tries to define the default as well as a list.
---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---