You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@madlib.apache.org by ri...@apache.org on 2018/08/17 03:18:41 UTC
[1/3] madlib git commit: Elastic Net: Allow grouping by non-numeric
column
Repository: madlib
Updated Branches:
refs/heads/master 441f16bd5 -> a3b59356f
Elastic Net: Allow grouping by non-numeric column
JIRA: MADLIB-1262
- Grouping columns should be quoted if the type of the column is of type
TEXT.
- Grouping column names that require double quoting need special
handling.
Closes #309
Co-authored-by: Domino Valdano <dv...@pivotal.io>
Co-authored-by: Rahul Iyer <ri...@apache.org>
Project: http://git-wip-us.apache.org/repos/asf/madlib/repo
Commit: http://git-wip-us.apache.org/repos/asf/madlib/commit/ec328dba
Tree: http://git-wip-us.apache.org/repos/asf/madlib/tree/ec328dba
Diff: http://git-wip-us.apache.org/repos/asf/madlib/diff/ec328dba
Branch: refs/heads/master
Commit: ec328dba6853d31df5b1bd6bbdcd35933596fe78
Parents: 441f16b
Author: Arvind Sridhar <ar...@berkeley.edu>
Authored: Thu Aug 16 20:02:48 2018 -0700
Committer: Rahul Iyer <ri...@apache.org>
Committed: Thu Aug 16 20:03:22 2018 -0700
----------------------------------------------------------------------
.../elastic_net_generate_result.py_in | 63 ++++++----
.../modules/elastic_net/test/elastic_net.sql_in | 122 +++++++++++++++++++
2 files changed, 161 insertions(+), 24 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/madlib/blob/ec328dba/src/ports/postgres/modules/elastic_net/elastic_net_generate_result.py_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/elastic_net/elastic_net_generate_result.py_in b/src/ports/postgres/modules/elastic_net/elastic_net_generate_result.py_in
index 1dbd664..15881b4 100644
--- a/src/ports/postgres/modules/elastic_net/elastic_net_generate_result.py_in
+++ b/src/ports/postgres/modules/elastic_net/elastic_net_generate_result.py_in
@@ -2,7 +2,10 @@ import plpy
from elastic_net_utils import _process_results
from elastic_net_utils import _compute_log_likelihood
from utilities.validate_args import get_cols_and_types
+from utilities.validate_args import quote_ident
from utilities.utilities import split_quoted_delimited_str
+from internal.db_utils import quote_literal
+
def _elastic_net_generate_result(optimizer, iteration_run, **args):
"""
@@ -33,26 +36,30 @@ def _elastic_net_generate_result(optimizer, iteration_run, **args):
col_grp_key = args['col_grp_key']
grouping_str = args['grouping_str']
cols_types = dict(get_cols_and_types(args["tbl_source"]))
- grouping_str1 = grouping_column + ","
+ grouping_cols_list = split_quoted_delimited_str(grouping_column)
+ grouping_str1 = ','.join(['{0} AS {1}'.format(g, quote_ident(g))
+ for g in grouping_cols_list])
select_mean_and_std = ''
inner_join_x = ''
inner_join_y = ''
- grouping_cols_list = split_quoted_delimited_str(grouping_column)
- select_grp = ','.join(['n_tuples_including_nulls_subq.'+str(grp)
- for grp in grouping_cols_list]) + ','
- select_grouping_info = ','.join([grp_col+"\t"+cols_types[grp_col]
+ select_grp = ','.join(['n_tuples_including_nulls_subq.' + str(quote_ident(grp))
+ for grp in grouping_cols_list]) + ','
+ select_grouping_info = ','.join([grp_col + "\t" + cols_types[grp_col]
for grp_col in grouping_cols_list]) + ","
if data_scaled:
x_grp_cols = ' AND '.join([
- 'n_tuples_including_nulls_subq.{0}={1}.{2}'.format(grp,
- args["x_mean_table"], grp) for grp in grouping_cols_list])
+ '{0} = {1}.{2}'.format('n_tuples_including_nulls_subq.' + str(quote_ident(grp)),
+ args["x_mean_table"], grp)
+ for grp in grouping_cols_list])
y_grp_cols = ' AND '.join([
- 'n_tuples_including_nulls_subq.{0}={1}.{2}'.format(grp,
- args["y_mean_table"], grp) for grp in grouping_cols_list])
- select_mean_and_std = ' {0}.mean AS x_mean, '.format(args["x_mean_table"]) +\
- ' {0}.mean AS y_mean, '.format(args["y_mean_table"]) +\
- ' {0}.std AS x_std, '.format(args["x_mean_table"])
+ '{0}={1}.{2}'.format('n_tuples_including_nulls_subq.' + str(quote_ident(grp)),
+ args["y_mean_table"], grp)
+ for grp in grouping_cols_list])
+ select_mean_and_std = (
+ ' {0}.mean AS x_mean, '.format(args["x_mean_table"]) +
+ ' {0}.mean AS y_mean, '.format(args["y_mean_table"]) +
+ ' {0}.std AS x_std, '.format(args["x_mean_table"]))
inner_join_x = ' INNER JOIN {0} ON {1} '.format(
args["x_mean_table"], x_grp_cols)
inner_join_y = ' INNER JOIN {0} ON {1} '.format(
@@ -66,7 +73,7 @@ def _elastic_net_generate_result(optimizer, iteration_run, **args):
FROM
(
SELECT
- {grouping_str1}
+ {grouping_str1},
array_to_string(ARRAY[{grouping_str}], ',') AS {col_grp_key}
FROM {source_table}
GROUP BY {grouping_column}, {col_grp_key}
@@ -83,7 +90,7 @@ def _elastic_net_generate_result(optimizer, iteration_run, **args):
col_grp_iteration=args["col_grp_iteration"], **locals())
else:
# It's a much simpler query when there is no grouping.
- grouping_str1 = ""
+ grouping_cols_list = []
select_grouping_info = ""
out_table_qstr = """
SELECT
@@ -117,13 +124,15 @@ def _elastic_net_generate_result(optimizer, iteration_run, **args):
result = plpy.execute(out_table_qstr)
for res in result:
- build_output_table(res, grouping_column, grouping_str1,
+ build_output_table(res, grouping_column, grouping_cols_list,
standardize_flag, iteration_run, **args)
# Create summary table, listing the grouping columns used.
grouping_text = "NULL" if not grouping_column else grouping_column
failed_groups = plpy.execute("""
- SELECT count(*) AS num_failed_groups FROM {0} WHERE coef_all IS NULL
+ SELECT count(*) AS num_failed_groups
+ FROM {0}
+ WHERE coef_all IS NULL
""".format(args['tbl_result']))[0]
all_groups = plpy.execute("SELECT count(*) AS num_all_groups FROM {0} ".
format(args['tbl_result']))[0]
@@ -142,7 +151,8 @@ def _elastic_net_generate_result(optimizer, iteration_run, **args):
'{family}'::varchar AS family,
{alpha}::float AS alpha,
{lambda_value}::float AS lambda_value,
- '{grouping_text}'::varchar AS grouping_col,
+ $madlib_super_quote${grouping_text}$madlib_super_quote$::varchar
+ AS grouping_col,
{num_all_groups}::integer AS num_all_groups,
{num_failed_groups}::integer AS num_failed_groups
""".format(grouping_text=grouping_text,
@@ -150,7 +160,7 @@ def _elastic_net_generate_result(optimizer, iteration_run, **args):
return None
-def build_output_table(res, grouping_column, grouping_str1,
+def build_output_table(res, grouping_column, grouping_col_list,
standardize_flag, iteration_run, **args):
"""
Insert model captured in "res" into the output table
@@ -160,10 +170,11 @@ def build_output_table(res, grouping_column, grouping_str1,
if args["normalization"]:
if grouping_column:
(coef, intercept) = _restore_scale(r_coef, res["intercept"],
- args, res["x_mean"], res["x_std"], res["y_mean"])
+ args, res["x_mean"],
+ res["x_std"], res["y_mean"])
else:
(coef, intercept) = _restore_scale(r_coef,
- res["intercept"], args)
+ res["intercept"], args)
else:
coef = r_coef
intercept = res["intercept"]
@@ -173,11 +184,13 @@ def build_output_table(res, grouping_column, grouping_str1,
log_likelihood = _compute_log_likelihood(r_coef, res["intercept"], **args)
if grouping_column:
- grouping_info = ','.join([str(res[grp_col.strip()])
- for grp_col in grouping_str1.split(',')
- if grp_col.strip() in res.keys()]) + ","
+ grouping_info = ",".join([quote_literal(str(res[grp_col.strip()]))
+ for grp_col in grouping_col_list
+ if grp_col.strip() in res.keys()])
else:
grouping_info = ""
+ if grouping_info:
+ grouping_info += ', '
fquery = """
INSERT INTO {tbl_result} VALUES
({grouping_info} '{family}', '{features}'::text[], '{features_selected}'::text[],
@@ -191,8 +204,10 @@ def build_output_table(res, grouping_column, grouping_str1,
**args)
plpy.execute(fquery)
# ------------------------------------------------------------------------
+
+
def _restore_scale(coef, intercept, args,
- x_mean=None, x_std=None, y_mean=None):
+ x_mean=None, x_std=None, y_mean=None):
"""
Restore the original scales
"""
http://git-wip-us.apache.org/repos/asf/madlib/blob/ec328dba/src/ports/postgres/modules/elastic_net/test/elastic_net.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/elastic_net/test/elastic_net.sql_in b/src/ports/postgres/modules/elastic_net/test/elastic_net.sql_in
index e3765ee..94ef902 100644
--- a/src/ports/postgres/modules/elastic_net/test/elastic_net.sql_in
+++ b/src/ports/postgres/modules/elastic_net/test/elastic_net.sql_in
@@ -839,3 +839,125 @@ SELECT elastic_net_train(
SELECT * FROM house_en;
SELECT * FROM house_en_summary;
SELECT * FROM house_en_cv;
+
+-- Test grouping on non-numeric column
+
+DROP TABLE IF EXISTS grouping_test_non_numeric;
+CREATE TABLE grouping_test_non_numeric ( id INT,
+ tax INT,
+ bedroom INT,
+ bath FLOAT,
+ price INT,
+ size INT,
+ lot INT,
+ zipcode TEXT,
+ testbool boolean);
+INSERT INTO grouping_test_non_numeric (id, tax, bedroom, bath, price, size, lot, zipcode, testbool) VALUES
+ (1 , 590 , 2 , 1 , 50000 , 770 , 22100 , 'test', 'true'),
+ (2 , 1050 , 3 , 2 , 85000 , 1410 , 12000 , 'test2', 'true'),
+ (3 , 20 , 3 , 1 , 22500 , 1060 , 3500 , 'test', 'false'),
+ (4 , 870 , 2 , 2 , 90000 , 1300 , 17500 , 'test2', 'false');
+
+DROP TABLE IF EXISTS grouping_test_non_numeric_en1, grouping_test_non_numeric_en1_summary;
+SELECT elastic_net_train( 'grouping_test_non_numeric', -- Source table
+ 'grouping_test_non_numeric_en1', -- Result table
+ 'price', -- Dependent variable
+ 'array[tax, bath, size]', -- Independent variable
+ 'gaussian', -- Regression family
+ 0.5, -- Alpha value
+ 0.1, -- Lambda value
+ TRUE, -- Standardize
+ 'zipcode', -- Grouping column(s)
+ 'fista', -- Optimizer
+ '', -- Optimizer parameters
+ NULL, -- Excluded columns
+ 10000, -- Maximum iterations
+ 1e-6 -- Tolerance value
+);
+
+DROP TABLE IF EXISTS grouping_test_non_numeric_en1, grouping_test_non_numeric_en1_summary;
+SELECT elastic_net_train( 'grouping_test_non_numeric', -- Source table
+ 'grouping_test_non_numeric_en1', -- Result table
+ 'price', -- Dependent variable
+ 'array[tax, bath, size]', -- Independent variable
+ 'gaussian', -- Regression family
+ 0.5, -- Alpha value
+ 0.1, -- Lambda value
+ TRUE, -- Standardize
+ 'testbool', -- Grouping column(s)
+ 'fista', -- Optimizer
+ '', -- Optimizer parameters
+ NULL, -- Excluded columns
+ 10000, -- Maximum iterations
+ 1e-6 -- Tolerance value
+);
+
+-- Special character tests
+
+DROP TABLE IF EXISTS special_character_tests;
+CREATE TABLE special_character_tests ( id INT,
+ tax INT,
+ bedroom INT,
+ bath FLOAT,
+ price INT,
+ size INT,
+ lot INT,
+ zipcode TEXT,
+ testbool boolean);
+INSERT INTO special_character_tests (id, tax, bedroom, bath, price, size, lot, zipcode, testbool) VALUES
+ (1 , 590 , 2 , 1 , 50000 , 770 , 22100 , 'tes''t', 'true'),
+ (2 , 1050 , 3 , 2 , 85000 , 1410 , 12000 , 'test$$%*Ж!#"()2', 'true'),
+ (3 , 20 , 3 , 1 , 22500 , 1060 , 3500 , 'tes''t', 'false'),
+ (4 , 870 , 2 , 2 , 90000 , 1300 , 17500 , 'test$$%*Ж!#"()2', 'false');
+
+DROP TABLE IF EXISTS special_character_tests_en1, special_character_tests_en1_summary;
+SELECT elastic_net_train( 'special_character_tests', -- Source table
+ 'special_character_tests_en1', -- Result table
+ 'price', -- Dependent variable
+ 'array[tax, bath, size]', -- Independent variable
+ 'gaussian', -- Regression family
+ 0.5, -- Alpha value
+ 0.1, -- Lambda value
+ TRUE, -- Standardize
+ 'zipcode', -- Grouping column(s)
+ 'fista', -- Optimizer
+ '', -- Optimizer parameters
+ NULL, -- Excluded columns
+ 10000, -- Maximum iterations
+ 1e-6 -- Tolerance value
+);
+
+-- Test case is commented out because support for special characters in column names has not yet been added to this module
+
+DROP TABLE IF EXISTS special_character_tests2;
+CREATE TABLE special_character_tests2 ( id INT,
+ tax INT,
+ bedroom INT,
+ bath FLOAT,
+ price INT,
+ size INT,
+ lot INT,
+ "zip'code" TEXT,
+ testbool boolean);
+INSERT INTO special_character_tests2 (id, tax, bedroom, bath, price, size, lot, "zip'code", testbool) VALUES
+ (1 , 590 , 2 , 1 , 50000 , 770 , 22100 , 'test', 'true'),
+ (2 , 1050 , 3 , 2 , 85000 , 1410 , 12000 , 'test2', 'true'),
+ (3 , 20 , 3 , 1 , 22500 , 1060 , 3500 , 'test', 'false'),
+ (4 , 870 , 2 , 2 , 90000 , 1300 , 17500 , 'test2', 'false');
+
+DROP TABLE IF EXISTS special_character_tests2_en1, special_character_tests2_en1_summary;
+SELECT elastic_net_train( 'special_character_tests2', -- Source table
+ 'special_character_tests2_en1', -- Result table
+ 'price', -- Dependent variable
+ 'array[tax, bath, size]', -- Independent variable
+ 'gaussian', -- Regression family
+ 0.5, -- Alpha value
+ 0.1, -- Lambda value
+ TRUE, -- Standardize
+ '"zip''code"', -- Grouping column(s)
+ 'fista', -- Optimizer
+ '', -- Optimizer parameters
+ NULL, -- Excluded columns
+ 10000, -- Maximum iterations
+ 1e-6 -- Tolerance value
+);
[3/3] madlib git commit: Multiple: Re-enable tests in PCA, Pagerank
Posted by ri...@apache.org.
Multiple: Re-enable tests in PCA, Pagerank
JIRA: MADLIB-1264
Some tests were commented out due to failures on GPDB 5.X.
These tests are now working and have been enabled again.
Closes #312
Co-authored-by: Arvind Sridhar <ar...@berkeley.edu>
Project: http://git-wip-us.apache.org/repos/asf/madlib/repo
Commit: http://git-wip-us.apache.org/repos/asf/madlib/commit/a3b59356
Tree: http://git-wip-us.apache.org/repos/asf/madlib/tree/a3b59356
Diff: http://git-wip-us.apache.org/repos/asf/madlib/diff/a3b59356
Branch: refs/heads/master
Commit: a3b59356f328fb949d63758a518aeca6d72220cf
Parents: 5ccf12e
Author: Jingyi Mei <jm...@pivotal.io>
Authored: Thu Aug 16 20:12:25 2018 -0700
Committer: Rahul Iyer <ri...@apache.org>
Committed: Thu Aug 16 20:18:21 2018 -0700
----------------------------------------------------------------------
.../postgres/modules/graph/test/pagerank.sql_in | 23 +++++++++++---------
src/ports/postgres/modules/pca/test/pca.sql_in | 16 ++++++--------
2 files changed, 20 insertions(+), 19 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/madlib/blob/a3b59356/src/ports/postgres/modules/graph/test/pagerank.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/graph/test/pagerank.sql_in b/src/ports/postgres/modules/graph/test/pagerank.sql_in
index 14d3371..e797812 100644
--- a/src/ports/postgres/modules/graph/test/pagerank.sql_in
+++ b/src/ports/postgres/modules/graph/test/pagerank.sql_in
@@ -60,6 +60,7 @@ INSERT INTO "EDGE" VALUES
(5, 6, 2),
(6, 3, 2);
+-- Test pagerank without group
DROP TABLE IF EXISTS pagerank_out, pagerank_out_summary;
SELECT pagerank(
'vertex', -- Vertex table
@@ -73,6 +74,8 @@ SELECT assert(relative_error(SUM(pagerank), 1) < 0.00001,
'PageRank: Scores do not sum up to 1.'
) FROM pagerank_out;
+
+-- Test pagerank with group
DROP TABLE IF EXISTS pagerank_gr_out;
DROP TABLE IF EXISTS pagerank_gr_out_summary;
SELECT pagerank(
@@ -84,7 +87,7 @@ SELECT pagerank(
NULL, -- Default damping factor (0.85)
NULL, -- Default max iters (100)
NULL, -- Default Threshold
- 'user_id'); -- Personlized Nodes
+ 'user_id'); -- Grouping Column
-- View the PageRank of all vertices, sorted by their scores.
SELECT assert(relative_error(SUM(pagerank), 1) < 0.00001,
@@ -94,8 +97,16 @@ SELECT assert(relative_error(SUM(pagerank), 1) < 0.00001,
'PageRank: Scores do not sum up to 1 for group 2.'
) FROM pagerank_gr_out WHERE user_id=2;
--- Tests for Personalized Page Rank
+-- Check the iteration numbers for convergency
+SELECT assert(relative_error(__iterations__, 11) = 0,
+ 'PageRank: Incorrect iterations for group 1.'
+ ) FROM pagerank_gr_out_summary WHERE user_id=1;
+SELECT assert(relative_error(__iterations__, 14) = 0,
+ 'PageRank: Incorrect iterations for group 2.'
+ ) FROM pagerank_gr_out_summary WHERE user_id=2;
+
+-- Tests for Personalized Page Rank
-- Test without grouping
DROP TABLE IF EXISTS pagerank_ppr_out;
@@ -141,14 +152,6 @@ SELECT assert(relative_error(SUM(pagerank), 1) < 0.005,
) FROM pagerank_ppr_grp_out WHERE user_id=1;
select assert(array_agg(user_id order by pagerank desc)= '{2, 2, 1, 1, 2, 2, 1, 1, 2, 1, 2, 1, 2, 1}','Unexpected Ranking with grouping ') from pagerank_ppr_grp_out ;
--- These tests have been temporarily removed for GPDB5 alpha support
-
--- SELECT assert(relative_error(__iterations__, 27) = 0,
--- 'PageRank: Incorrect iterations for group 1.'
--- ) FROM pagerank_gr_out_summary WHERE user_id=1;
--- SELECT assert(relative_error(__iterations__, 31) = 0,
--- 'PageRank: Incorrect iterations for group 2.'
--- ) FROM pagerank_gr_out_summary WHERE user_id=2;
-- Test to capture corner case reported in https://issues.apache.org/jira/browse/MADLIB-1229
http://git-wip-us.apache.org/repos/asf/madlib/blob/a3b59356/src/ports/postgres/modules/pca/test/pca.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/pca/test/pca.sql_in b/src/ports/postgres/modules/pca/test/pca.sql_in
index 5a97c94..1510254 100644
--- a/src/ports/postgres/modules/pca/test/pca.sql_in
+++ b/src/ports/postgres/modules/pca/test/pca.sql_in
@@ -145,16 +145,14 @@ COPY mat (id, row_vec, grp) FROM stdin delimiter '|';
16|{739,651,678,577,273,935,661,47,373,618}|2
\.
--- This test has been temporarily removed for GPDB5 alpha support
-
-- Learn individaul PCA models based on grouping column (grp)
--- drop table if exists result_table_214712398172490837;
--- drop table if exists result_table_214712398172490837_mean;
--- drop table if exists result_table_214712398172490838;
--- select pca_train('mat', 'result_table_214712398172490837', 'id', 0.8,
--- 'grp', 5, FALSE, 'result_table_214712398172490838');
--- select * from result_table_214712398172490837;
--- select * from result_table_214712398172490838;
+drop table if exists result_table_214712398172490837;
+drop table if exists result_table_214712398172490837_mean;
+drop table if exists result_table_214712398172490838;
+select pca_train('mat', 'result_table_214712398172490837', 'id', 0.8,
+'grp', 5, FALSE, 'result_table_214712398172490838');
+select * from result_table_214712398172490837;
+select * from result_table_214712398172490838;
-- Matrix in the column format
DROP TABLE IF EXISTS cmat;
[2/3] madlib git commit: Vec2Cols: Allow arrays of different lengths
Posted by ri...@apache.org.
Vec2Cols: Allow arrays of different lengths
JIRA: MADLIB-1270
Added support to split arrays of different lengths in the vector_col.
If the user does not provide feature names, we pad each array to the
maximum length and split across the maximum possible number of features.
If the user does provide feature names, we truncate/pad the arrays
according to the number of features the user desires.
Closes #311
Co-authored-by: Arvind Sridhar <ar...@berkeley.edu>
Project: http://git-wip-us.apache.org/repos/asf/madlib/repo
Commit: http://git-wip-us.apache.org/repos/asf/madlib/commit/5ccf12e1
Tree: http://git-wip-us.apache.org/repos/asf/madlib/tree/5ccf12e1
Diff: http://git-wip-us.apache.org/repos/asf/madlib/diff/5ccf12e1
Branch: refs/heads/master
Commit: 5ccf12e113f04b02b9ccf8c9aee107a4feb4bd88
Parents: ec328db
Author: Rahul Iyer <ri...@apache.org>
Authored: Thu Aug 16 20:08:32 2018 -0700
Committer: Rahul Iyer <ri...@apache.org>
Committed: Thu Aug 16 20:08:32 2018 -0700
----------------------------------------------------------------------
.../utilities/test/transform_vec_cols.sql_in | 47 ++++++++++++++++++++
.../unit_tests/test_transform_vec_cols.py_in | 14 +-----
.../modules/utilities/transform_vec_cols.py_in | 34 +++++++-------
3 files changed, 64 insertions(+), 31 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/madlib/blob/5ccf12e1/src/ports/postgres/modules/utilities/test/transform_vec_cols.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/test/transform_vec_cols.sql_in b/src/ports/postgres/modules/utilities/test/transform_vec_cols.sql_in
index 47ab299..b43b39f 100644
--- a/src/ports/postgres/modules/utilities/test/transform_vec_cols.sql_in
+++ b/src/ports/postgres/modules/utilities/test/transform_vec_cols.sql_in
@@ -104,6 +104,53 @@ SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name
SELECT assert ((SELECT clouds_airquality[1] FROM dt_golf WHERE id = 1) = (SELECT clouds FROM out_table WHERE id = 1), 'Split values do not match up');
SELECT assert ((SELECT clouds_airquality[2] FROM dt_golf WHERE id = 1) = (SELECT air_quality FROM out_table WHERE id = 1), 'Split values do not match up');
+-- Testing splitting arrays of different lengths into features
+
+DROP TABLE IF EXISTS diff_lengths_test;
+CREATE TABLE diff_lengths_test(
+ "id" INTEGER,
+ "arr" TEXT[]);
+INSERT INTO diff_lengths_test VALUES (1, '{a, b}'), (2, '{c, d}'), (3, '{e, f, g, h}'), (4, '{i}'), (5, '{}');
+
+DROP TABLE IF EXISTS out_table;
+SELECT vec2cols(
+ 'diff_lengths_test',
+ 'out_table',
+ 'arr'
+);
+
+SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = (SELECT max(array_upper(arr, 1)) from diff_lengths_test), 'Number of split columns does not match');
+
+DROP TABLE IF EXISTS out_table;
+SELECT vec2cols(
+ 'diff_lengths_test',
+ 'out_table',
+ 'arr',
+ ARRAY['a']
+);
+
+SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 1, 'Number of split columns does not match');
+
+DROP TABLE IF EXISTS out_table;
+SELECT vec2cols(
+ 'diff_lengths_test',
+ 'out_table',
+ 'arr',
+ ARRAY['a', 'b', 'c']
+);
+
+SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 3, 'Number of split columns does not match');
+
+DROP TABLE IF EXISTS out_table;
+SELECT vec2cols(
+ 'diff_lengths_test',
+ 'out_table',
+ 'arr',
+ ARRAY['a', 'b', 'c', 'd', 'e', 'f', 'g']
+);
+
+SELECT assert ((SELECT count(*) FROM information_schema.columns WHERE table_name='out_table') = 7, 'Number of split columns does not match');
+
-- Special character tests
DROP TABLE IF EXISTS special_char_check;
http://git-wip-us.apache.org/repos/asf/madlib/blob/5ccf12e1/src/ports/postgres/modules/utilities/test/unit_tests/test_transform_vec_cols.py_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/test/unit_tests/test_transform_vec_cols.py_in b/src/ports/postgres/modules/utilities/test/unit_tests/test_transform_vec_cols.py_in
index 6475f9b..3020309 100644
--- a/src/ports/postgres/modules/utilities/test/unit_tests/test_transform_vec_cols.py_in
+++ b/src/ports/postgres/modules/utilities/test/unit_tests/test_transform_vec_cols.py_in
@@ -125,23 +125,13 @@ class Vec2ColsTestSuite(unittest.TestCase):
def test_get_names_for_split_output_cols_feature_names_none(self):
self.plpy_mock_execute.return_value = [{"n_x": 3}]
- new_cols = self.subject.get_names_for_split_output_cols(self.default_source_table, 'foobar', None)
+ new_cols = self.subject.get_names_for_split_output_cols(self.default_source_table, 'foobar')
self.assertEqual(['f1', 'f2', 'f3'], new_cols)
- def test_get_names_for_split_output_cols_feature_names_not_none(self):
- self.plpy_mock_execute.return_value = [{"n_x": 3}]
- new_cols = self.subject.get_names_for_split_output_cols(self.default_source_table, 'foobar', ['a', 'b', 'c'])
- self.assertEqual(['a', 'b', 'c'], new_cols)
-
def test_get_names_for_split_output_cols_array_upper_returns_none(self):
self.plpy_mock_execute.return_value = [{"n_x": None}]
with self.assertRaises(plpy.PLPYException):
- self.subject.get_names_for_split_output_cols(self.default_source_table, 'foobar', None)
-
- def test_get_names_for_split_output_cols_feature_names_size_matches_vector_col(self):
- self.plpy_mock_execute.return_value = [{"n_x": 3}]
- with self.assertRaises(plpy.PLPYException):
- self.subject.get_names_for_split_output_cols(self.default_source_table, 'foobar', ['a', 'b'])
+ self.subject.get_names_for_split_output_cols(self.default_source_table, 'foobar')
def test_validate_output_cols_max_cols_exception(self):
with self.assertRaises(plpy.PLPYException):
http://git-wip-us.apache.org/repos/asf/madlib/blob/5ccf12e1/src/ports/postgres/modules/utilities/transform_vec_cols.py_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/transform_vec_cols.py_in b/src/ports/postgres/modules/utilities/transform_vec_cols.py_in
index bf9229e..49064d3 100644
--- a/src/ports/postgres/modules/utilities/transform_vec_cols.py_in
+++ b/src/ports/postgres/modules/utilities/transform_vec_cols.py_in
@@ -83,7 +83,7 @@ class vec2cols:
_assert(is_col_1d_array(source_table, vector_col),
"{0}: vector_col must be a 1-dimensional array.".format(self.module_name))
- def get_names_for_split_output_cols(self, source_table, vector_col, feature_names):
+ def get_names_for_split_output_cols(self, source_table, vector_col):
"""
Get list of names for the newly-split columns to include in the
output table.
@@ -94,24 +94,15 @@ class vec2cols:
use for the split elements in the vector_col array
"""
query = """
- SELECT array_upper({0}, 1) AS n_x
+ SELECT max(array_upper({0}, 1)) AS n_x
FROM {1}
- LIMIT 1
""".format(vector_col, source_table)
- result = plpy.execute(query)[0]["n_x"]
- if not result:
+ max_array_length = plpy.execute(query)[0]["n_x"]
+ if not max_array_length:
plpy.error('{0}: Column to split ({1}) must not be an empty array'
.format(self.module_name, vector_col))
- if not feature_names:
- # Create custom col names for output columns, with prefix "f".
- feature_names = ["f{0}".format(i+1) for i in range(result)]
- else:
- # Check if the array dimension is equal to the number of col names
- # specified in feature_names.
- _assert(result == len(feature_names),
- "{0}: Mismatch between size of vector_col and number of "
- "cols in feature_names.".format(self.module_name))
- return feature_names
+ # Create custom col names for output columns, with prefix "f".
+ return ["f{0}".format(i+1) for i in range(max_array_length)]
def validate_output_cols(self, features_to_unnest, cols_to_keep):
# If there are more than 1600 columns for the output table, we give a
@@ -146,8 +137,13 @@ class vec2cols:
cols_to_output)
# Get names of columns to use for the split vector_col
- features_to_unnest = self.get_names_for_split_output_cols(source_table,
- vector_col, feature_names)
+ if not feature_names:
+ features_to_unnest = self.get_names_for_split_output_cols(
+ source_table, vector_col)
+ else:
+ features_to_unnest = feature_names
+
+ # Get the columns to keep from the source table
cols_to_keep = self.get_cols_helper.get_cols_as_list(cols_to_output,
source_table)
@@ -155,8 +151,8 @@ class vec2cols:
# Construct the output query and populate the output table with all the
# correct parameters
- select_new_cols = ', '.join(["{0}[{1}] AS {2}".format(vector_col,
- i+1, features_to_unnest[i]) for i in range(len(features_to_unnest))])
+ select_new_cols = (', '.join(["{0}[{1}] AS {2}".format(vector_col, i + 1, f)
+ for i, f in enumerate(features_to_unnest)]))
cols_from_src_table = ', '.join(cols_to_keep)+', ' if cols_to_keep else ''
query = """
CREATE TABLE {output_table} AS