| """ |
| @file pca.py_in |
| |
| @namespace pca |
| """ |
| |
| from convex.utils_regularization import utils_ind_var_scales |
| from linalg.matrix_ops import get_dims |
| from linalg.matrix_ops import create_temp_sparse_matrix_table_with_dims |
| from linalg.matrix_ops import cast_dense_input_table_to_correct_columns |
| from linalg.matrix_ops import validate_dense |
| from linalg.matrix_ops import validate_sparse |
| from linalg.svd import create_summary_table |
| from linalg.svd import _svd_lower_wrap |
| from linalg.svd import _svd_upper_wrap |
| from utilities.utilities import _array_to_string |
| from utilities.utilities import add_postfix |
| from utilities.utilities import __mad_version |
| from utilities.utilities import unique_string, split_quoted_delimited_str |
| from utilities.utilities import _assert |
| from utilities.validate_args import get_cols, get_cols_and_types |
| from utilities.control import MinWarning |
| from utilities.validate_args import columns_exist_in_table |
| from utilities.validate_args import table_exists |
| |
| import time |
| import plpy |
| |
| version_wrapper = __mad_version() |
| string_to_array = version_wrapper.select_vecfunc() |
| array_to_string = version_wrapper.select_vec_return() |
| |
| |
| def pca_sparse(schema_madlib, source_table, pc_table, row_id, |
| col_id, val_id, row_dim, col_dim, k, grouping_cols, |
| lanczos_iter, use_correlation, result_summary_table, |
| variance, **kwargs): |
| """ |
| Args: |
| @param schema_madlib |
| @param source_table |
| @param pc_table |
| @param row_id |
| @param col_id |
| @param val_id |
| @param row_dim |
| @param col_dim |
| @param k |
| @param grouping_cols |
| @param lanczos_iter |
| @param use_correlation |
| @param result_summary_table |
| @param variance |
| |
| Returns: |
| None |
| |
| """ |
| pca_wrap(schema_madlib, source_table, pc_table, row_id, |
| k, grouping_cols, lanczos_iter, use_correlation, |
| result_summary_table, variance, True, col_id, |
| val_id, row_dim, col_dim) |
| # ------------------------------------------------------------------------ |
| |
| |
| def pca(schema_madlib, source_table, pc_table, row_id, |
| k, grouping_cols, lanczos_iter, use_correlation, |
| result_summary_table, variance, **kwargs): |
| """ |
| Args: |
| @param schema_madlib |
| @param source_table |
| @param pc_table |
| @param row_id |
| @param k |
| @param grouping_cols |
| @param lanczos_iter |
| @param use_correlation |
| @param result_summary_table |
| @param variance |
| |
| Returns: |
| None |
| |
| """ |
| pca_wrap(schema_madlib, source_table, pc_table, row_id, |
| k, grouping_cols, lanczos_iter, use_correlation, |
| result_summary_table, variance) |
| # ------------------------------------------------------------------------ |
| |
| |
| def pca_wrap(schema_madlib, source_table, pc_table, row_id, |
| k, grouping_cols, lanczos_iter, use_correlation, |
| result_summary_table, variance, is_sparse=False, col_id=None, |
| val_id=None, row_dim=None, col_dim=None, **kwargs): |
| """ |
| This wrapper was added to support grouping columns. This |
| function does the necessary pre-processing for handling |
| grouping_cols, if set. It then constructs a single query |
| that includes a separate "madlib._pca_union(...)" for each |
| group. |
| """ |
| # Reset the message level to avoid random messages |
| with MinWarning('warning'): |
| grouping_cols_list = [] |
| if is_sparse: |
| _validate_args(schema_madlib, source_table, pc_table, k, row_id, col_id, |
| val_id, row_dim, col_dim, lanczos_iter, |
| use_correlation, result_summary_table, variance) |
| else: |
| _validate_args(schema_madlib, source_table, pc_table, k, |
| row_id, None, None, None, None, |
| lanczos_iter, use_correlation, |
| result_summary_table, variance) |
| if grouping_cols: |
| # validate the grouping columns. We currently only support grouping_cols |
| # to be column names in the source_table, and not expressions! |
| grouping_cols_list = split_quoted_delimited_str(grouping_cols) |
| _assert(columns_exist_in_table(source_table, grouping_cols_list, schema_madlib), |
| "PCA error: One or more grouping columns in {0} do not exist!". |
| format(grouping_cols)) |
| distinct_grouping_values = plpy.execute( |
| "SELECT DISTINCT {grouping_cols} FROM {source_table}". |
| format(grouping_cols=grouping_cols, source_table=source_table)) |
| else: |
| grouping_cols = '' |
| other_columns_in_table = [col for col in get_cols(source_table) if col not in grouping_cols_list] |
| grouping_cols_clause = '' |
| if grouping_cols_list: |
| cols_names_types = get_cols_and_types(source_table) |
| grouping_cols_clause = ', ' + ', '.join([c_name + " " + c_type for (c_name, c_type) in cols_names_types if c_name in grouping_cols_list]) |
| # Create all output tables |
| plpy.execute(""" |
| CREATE TABLE {pc_table} ( |
| row_id INTEGER, |
| principal_components double precision[], |
| std_dev double precision, |
| proportion double precision |
| {grouping_cols_clause} |
| ) |
| """.format(pc_table=pc_table, |
| grouping_cols_clause=grouping_cols_clause)) |
| pc_table_mean = add_postfix(pc_table, "_mean") |
| plpy.execute("DROP TABLE IF EXISTS {0}".format(pc_table_mean)) |
| plpy.execute(""" |
| CREATE TABLE {pc_table_mean} ( |
| column_mean double precision[] |
| {grouping_cols_clause} |
| ) |
| """.format(pc_table_mean=pc_table_mean, grouping_cols_clause=grouping_cols_clause)) |
| if result_summary_table: |
| plpy.execute("DROP TABLE IF EXISTS {0}".format(result_summary_table)) |
| plpy.execute(""" |
| CREATE TABLE {0} ( |
| rows_used INTEGER, |
| "exec_time (ms)" numeric, |
| iter INTEGER, |
| recon_error double precision, |
| relative_recon_error double precision, |
| use_correlation boolean |
| {1} |
| ) |
| """.format(result_summary_table, grouping_cols_clause)) |
| else: |
| result_summary_table = '' |
| |
| # declare variables whose values will be different for each group, if |
| # grouping_cols is specified |
| grouping_where_clause = '' |
| sparse_where_condition = '' |
| select_grouping_cols = '' |
| temp_table_columns = '' |
| result_summary_table_temp = '' |
| # For Dense matrix format only: |
| # We can now ignore the original row_id for all computations since we will |
| # create a new table with a row_id column that has not duplicates and ranges |
| # from 1 to number of rows in the group/table. This is to mainly support the |
| # grouping scneario where the row_id values might not range between 1 and |
| # number of rows in the group, for each group. Doing this also just extends |
| # this behavior for non-grouping scenarios too. If creating a new temp table |
| # that corrects the row_id column is not of much importance in non-grouping |
| # cases, we can avoid creating the temp table and save some computation time. |
| # But, at the moment, the code creates the temp table even for the non-grouping |
| # scenario. |
| # We don't need to do this for sparse representation because of the nature |
| # of its definition. |
| other_columns_in_table.remove(row_id) |
| temp_table_columns = (" ROW_NUMBER() OVER() AS row_id, " + |
| ','.join(other_columns_in_table)) |
| |
| pca_union_call_list = [] |
| grp_id = 0 |
| if not is_sparse: |
| col_id = 'NULL' |
| val_id = 'NULL' |
| row_dim = 0 |
| col_dim = 0 |
| while True: |
| if result_summary_table: |
| result_summary_table_temp = ("pg_temp.{0}_{1}". |
| format(unique_string(), grp_id)) |
| if grouping_cols: |
| grp_value_dict = distinct_grouping_values[grp_id] |
| where_conditions = ' AND '.join( |
| ["{0} = '{1}'".format(key, value) |
| for key, value in grp_value_dict.items()]) |
| sparse_where_condition = ' AND ' + where_conditions |
| grouping_where_clause = ' WHERE ' + where_conditions |
| select_grouping_cols = ( |
| ', ' + ', '.join(["'{1}' AS {0}".format(key, value) |
| for key, value in grp_value_dict.items()])) |
| |
| pca_union_call_list.append(""" |
| {schema_madlib}._pca_union( |
| '{source_table}', |
| '{pc_table}', |
| '{pc_table_mean}', |
| '{row_id}', |
| {k_null}, |
| '{grouping_cols}', |
| {lanczos_iter}, |
| {use_correlation}, |
| '{result_summary_table}', |
| '{result_summary_table_temp}', |
| {variance_null}, |
| {grp_id}, |
| $${grouping_where_clause}$$, |
| $${sparse_where_condition}$$, |
| $${select_grouping_cols}$$, |
| '{temp_table_columns}', |
| {is_sparse}, |
| '{col_id}', |
| '{val_id}', |
| {row_dim}, |
| {col_dim}) |
| """.format(k_null='NULL' if k is None else k, |
| variance_null='NULL' if variance is None else variance, |
| **locals())) |
| grp_id += 1 |
| if not grouping_cols_list or len(distinct_grouping_values) == grp_id: |
| break |
| # "SELECT <query_1>, <query_2>, <query_3>, ..." is expected to run each |
| # <query_i> in parallel. |
| pca_union_call = 'SELECT ' + ', '.join(pca_union_call_list) |
| plpy.execute(pca_union_call) |
| |
| |
| def _pca_union(schema_madlib, source_table, pc_table, pc_table_mean, |
| row_id, k, grouping_cols, lanczos_iter, use_correlation, |
| result_summary_table, result_summary_table_temp, variance, |
| grp_id, grouping_where_clause, sparse_where_condition, |
| select_grouping_cols, temp_table_columns, is_sparse, col_id, |
| val_id, row_dim, col_dim, **kwargs): |
| """ |
| This function does all the heavy lifting of PCA, for both pca and pca_sparse. |
| Compute the PCA of the matrix in source_table. This function is the specific |
| call for dense matrices and creates three tables corresponding to the three |
| decomposition matrices. |
| |
| Args: |
| @param source_table TEXT, -- Source table name (dense matrix) |
| @param pc_table TEXT, -- Output table name for the principal components |
| @param pc_table_mean TEXT, -- Output table name for the principal components |
| @param row_id TEXT, -- Column name for the ID for each row |
| @param k INTEGER, -- Number of principal components to compute |
| @param grouping_cols TEXT, -- Comma-separated list of grouping columns (Default: NULL) |
| @param lanczos_iter INTEGER, -- The number of Lanczos iterations for the SVD calculation (Default: min(k+40, smallest Matrix dimension)) |
| @param use_correlation BOOLEAN, -- If True correlation matrix is used for principal components (Default: False) |
| @param result_summary_table TEXT, -- Table name to store summary of results (Default: NULL) |
| @param result_summary_table_temp TEXT, -- Table name to store summary of results (Default: NULL) |
| @param variance DOUBLE PRECISION, -- The proportion of variance (Default: NULL) |
| @param grp_id INTEGER, -- a place holder id for each group |
| @param grouping_where_clause TEXT, -- WHERE clause using grouping_cols |
| @param select_grouping_cols TEXT, -- SELECT clause using grouping_cols |
| @param temp_table_columns TEXT, -- SELECT caluse for creating temporary copy of the source_table |
| @param is_sparse BOOLEAN, -- specifies if the PCA call is for sparse or dense matrices |
| @param col_id TEXT, -- sparse representation based detail |
| @param val_id TEXT, -- sparse representation based detail |
| @param row_dim INTEGER, -- sparse representation based detail |
| @param col_dim INTEGER -- sparse representation based detail |
| |
| Returns: |
| None |
| """ |
| startTime = time.time() # measure the starting time |
| # Step 1: Modify data format for sparse input |
| if is_sparse: |
| # Step 1.1: Densify the matrix for sparse input tables |
| # We densify the matrix because the recentering process will generate a |
| # dense matrix, so we just wrap around regular PCA. |
| # First we must copy the sparse matrix and add in the dimension information |
| sparse_temp = "pg_temp." + unique_string() + "_sparse" |
| # Add in the dimension information needed by the densifying process |
| create_temp_sparse_matrix_table_with_dims(source_table, sparse_temp, |
| row_id, col_id, val_id, |
| row_dim, col_dim, sparse_where_condition) |
| validate_sparse(sparse_temp, |
| {'row': row_id, 'col': col_id, 'val': val_id}, |
| check_col=False) |
| # Step 1.2: Densify the input matrix |
| x_dense = "pg_temp." + unique_string() + "_dense" |
| plpy.execute(""" |
| SELECT {schema_madlib}.matrix_densify( |
| '{sparse_temp}', |
| 'row={row_id}, col={col_id}, val={val_id}', |
| '{x_dense}', 'row=row_id, val=row_vec') |
| """.format(schema_madlib=schema_madlib, |
| sparse_temp=sparse_temp, row_id=row_id, |
| col_id=col_id, val_id=val_id, x_dense=x_dense)) |
| plpy.execute(""" |
| DROP TABLE IF EXISTS {0}; |
| """.format(sparse_temp)) |
| source_table_grouped = x_dense |
| else: |
| # Creation of this temp table is unnecessary if the scenario does not involve |
| # grouping, and/or, the input table had perfect values for the row_id column. |
| # This temp table will ensure pca works even when the value of row_id column |
| # in dense matrix format does not have values ranging from 1 to number of rows. |
| source_table_grouped = "pg_temp." + unique_string() + "group_" + str(grp_id) |
| plpy.execute(""" |
| CREATE TABLE {source_table_grouped} AS |
| SELECT {temp_table_columns} |
| FROM {source_table} |
| {grouping_where_clause} |
| """.format(source_table_grouped=source_table_grouped, |
| source_table=source_table, |
| grouping_where_clause=grouping_where_clause, |
| temp_table_columns=temp_table_columns)) |
| row_id = 'row_id' |
| # Make sure that the table has row_id and row_vec |
| source_table_copy = "pg_temp." + unique_string() + "_reformated_names" |
| created_new_table = cast_dense_input_table_to_correct_columns( |
| schema_madlib, source_table_grouped, source_table_copy, row_id) |
| |
| if(created_new_table): |
| plpy.execute("DROP TABLE {0}".format(source_table_grouped)) |
| source_table_grouped = source_table_copy |
| [row_dim, col_dim] = get_dims(source_table_grouped, |
| {'row': 'row_id', 'col': 'col_id', |
| 'val': 'row_vec'}) |
| validate_dense(source_table_grouped, |
| {'row': 'row_id', 'val': 'row_vec'}, |
| check_col=False, row_dim=row_dim) |
| if k: |
| if k > min(row_dim, col_dim): |
| plpy.error( |
| "PCA error: k cannot be larger than min(row_dim, col_dim)!") |
| curK = k |
| else: |
| curK = min(row_dim, col_dim) |
| # If using the default number of lanczos iterations, set to the default |
| if lanczos_iter == 0: |
| if k: |
| lanczos_iter = min(k + 40, min(col_dim, row_dim)) |
| else: |
| lanczos_iter = min(col_dim, row_dim) |
| else: |
| if variance: # lanczos_iter overrides the proportion default for k |
| curK = lanczos_iter |
| # Note: we currently don't support grouping columns or correlation matrices |
| if not use_correlation: |
| # Step 2: Normalize the data (Column means) |
| dimension = col_dim |
| scaled_source_table = "pg_temp." + unique_string() + "_scaled_table" |
| column_mean_str = _recenter_data(schema_madlib, |
| source_table_grouped, |
| scaled_source_table, |
| 'row_id', |
| 'row_vec', |
| dimension) |
| # Step 3: Create temporary output & result summary table |
| svd_output_temp_table = "pg_temp." + unique_string() + "_svd_out_tbl" |
| |
| if result_summary_table_temp is None: |
| result_summary_table_string = '' |
| else: |
| result_summary_table_string = ", '{0}'".format(result_summary_table_temp) |
| # Step 4: Perform SVD |
| # Step 4.1: Perform upper part of SVD |
| if result_summary_table_temp: |
| t0 = time.time() |
| |
| (source_table_svd, bd_pref) = _svd_upper_wrap( |
| schema_madlib, scaled_source_table, svd_output_temp_table, |
| row_id, curK, lanczos_iter, result_summary_table_temp) |
| |
| # Calculate the sum of values for proportion |
| svd_var_s = add_postfix(svd_output_temp_table, "_s") |
| eigen_sum = plpy.execute( |
| """ |
| SELECT {schema_madlib}.array_sum( |
| {schema_madlib}.sum( |
| {schema_madlib}.array_square(row_vec) |
| ) |
| ) |
| FROM {scaled_source_table} |
| """.format(**locals()))[0]['array_sum'] |
| # Step 4.2: Adjust the k value |
| if variance: |
| variance_tmp_table = "pg_temp." + unique_string() + "_var_tmp" |
| plpy.execute( |
| """ |
| CREATE TABLE {variance_tmp_table} AS ( |
| SELECT row_id, |
| col_id, |
| sum(value*value) OVER(ORDER BY value DESC) |
| AS sum_value_square |
| FROM {svd_var_s}) |
| """.format(variance_tmp_table=variance_tmp_table, |
| svd_var_s=svd_var_s)) |
| ecount = plpy.execute(""" |
| SELECT count(sum_value_square) |
| FROM {variance_tmp_table} |
| WHERE sum_value_square / {eigen_sum} < {variance} |
| """.format(variance_tmp_table=variance_tmp_table, |
| eigen_sum=eigen_sum, |
| variance=variance)) |
| curK = ecount[0]['count'] |
| # The next value is the first that is over the treshold |
| if (lanczos_iter > curK): |
| curK = curK + 1 |
| plpy.execute(""" |
| DROP TABLE IF EXISTS {variance_tmp_table} |
| """.format(variance_tmp_table=variance_tmp_table)) |
| # Step 4.3: Perform the lower part of SVD |
| tmp_matrix_table = "temp_" + unique_string() + "_matrix" |
| tmp_matrix_s_table = add_postfix(tmp_matrix_table, "_s") |
| |
| if variance: # remove elements after the variance threshold |
| plpy.execute(""" |
| DELETE FROM {svd_var_s} |
| WHERE row_id >{curK}; |
| ALTER TABLE {svd_var_s} RENAME TO {tmp_matrix_s_table}; |
| """.format(**locals())) |
| _svd_lower_wrap(schema_madlib, source_table_svd, |
| svd_output_temp_table, row_id, curK, lanczos_iter, bd_pref, |
| tmp_matrix_s_table) |
| else: |
| tmp_matrix_table = svd_output_temp_table |
| _svd_lower_wrap(schema_madlib, source_table_svd, |
| svd_output_temp_table, row_id, curK, lanczos_iter, bd_pref) |
| # Step 4.4: Create the SVD result table |
| if result_summary_table_temp: |
| t1 = time.time() |
| [row_dim, col_dim] = get_dims(source_table_grouped, |
| {'row': 'row_id', 'col': 'col_id', 'val': 'row_vec'}) |
| arguments = {'schema_madlib': schema_madlib, |
| 'source_table': scaled_source_table, |
| 'matrix_u': add_postfix(svd_output_temp_table, "_u"), |
| 'matrix_v': add_postfix(svd_output_temp_table, "_v"), |
| 'matrix_s': add_postfix(tmp_matrix_table, "_s"), |
| 'row_dim': row_dim, |
| 'col_dim': col_dim, |
| 'result_summary_table': result_summary_table_temp, |
| 'temp_prefix': "pg_temp." + unique_string(), |
| 't0': t0, 't1': t1} |
| create_summary_table(**arguments) |
| # Step 5: Transpose SVD output matrix |
| svd_v_transpose = "pg_temp." + unique_string() + "transpose" |
| |
| svd_output_temp_table_s = add_postfix(tmp_matrix_table, "_s") |
| svd_output_temp_table_v = add_postfix(svd_output_temp_table, "_v") |
| svd_output_temp_table_u = add_postfix(svd_output_temp_table, "_u") |
| plpy.execute( |
| """ |
| SELECT {schema_madlib}.matrix_trans( |
| '{svd_output_temp_table_v}', |
| 'row=row_id, col=col_id, val=row_vec', |
| '{svd_v_transpose}', 'row=row_id, col=col_id, val=row_vec'); |
| """.format(svd_output_temp_table_v=svd_output_temp_table_v, |
| svd_v_transpose=svd_v_transpose, |
| schema_madlib=schema_madlib) |
| ) |
| # Step 6: Insert the output of SVD into the PCA table |
| plpy.execute( |
| """ |
| INSERT INTO {pc_table} |
| SELECT {svd_v_transpose}.row_id, |
| row_vec AS principal_components, |
| value / sqrt({row_dim} - 1) AS std_dev, |
| ((value*value)/ {eigen_sum}) AS proportion |
| {select_grouping_cols} |
| FROM {svd_v_transpose}, |
| {svd_output_temp_table_s} |
| WHERE ({svd_v_transpose}.row_id = {svd_output_temp_table_s}.row_id) |
| AND ({svd_v_transpose}.row_id <= {k}) |
| AND value is not NULL |
| """.format(svd_output_temp_table_s=svd_output_temp_table_s, |
| k=curK, |
| svd_v_transpose=svd_v_transpose, |
| pc_table=pc_table, |
| row_dim=row_dim, |
| eigen_sum=eigen_sum, |
| select_grouping_cols=select_grouping_cols)) |
| # Output the column mean |
| plpy.execute( |
| """ |
| INSERT INTO {pc_table_mean} |
| SELECT '{column_mean_str}'::FLOAT8[] AS column_mean |
| {select_grouping_cols} |
| """.format(pc_table_mean=pc_table_mean, |
| column_mean_str=column_mean_str, |
| select_grouping_cols=select_grouping_cols)) |
| # Step 7: Append to the SVD summary table to get the PCA summary table |
| if result_summary_table_temp: |
| stopTime = time.time() |
| dt = (stopTime - startTime) * 1000. |
| plpy.execute( |
| """ |
| INSERT INTO {result_summary_table} |
| SELECT |
| rows_used, |
| {dt} AS "exec_time (ms)", |
| {iter} AS iter, |
| recon_error, |
| relative_recon_error, |
| {use_correlation} AS use_correlation |
| {select_grouping_cols} |
| FROM {result_summary_table_temp}; |
| """.format(result_summary_table=result_summary_table, |
| dt=str(dt), iter=curK, |
| use_correlation=bool(use_correlation), |
| result_summary_table_temp=result_summary_table_temp, |
| select_grouping_cols=select_grouping_cols)) |
| plpy.execute("DROP TABLE {result_summary_table_temp};".format( |
| result_summary_table_temp=result_summary_table_temp)) |
| |
| # Step 8: Output handling & cleanup |
| plpy.execute( |
| """ |
| DROP TABLE IF EXISTS {tmp_matrix_s_table}; |
| DROP TABLE IF EXISTS {svd_v_transpose}; |
| DROP TABLE IF EXISTS {source_table_copy}; |
| DROP TABLE IF EXISTS {svd_output_temp_table}; |
| DROP TABLE IF EXISTS {svd_output_temp_table_s}; |
| DROP TABLE IF EXISTS {svd_output_temp_table_u}; |
| DROP TABLE IF EXISTS {svd_output_temp_table_v}; |
| DROP TABLE IF EXISTS {scaled_source_table}; |
| DROP TABLE IF EXISTS {source_table_grouped}; |
| """.format(svd_output_temp_table=svd_output_temp_table, |
| svd_output_temp_table_s=svd_output_temp_table_s, |
| svd_output_temp_table_u=svd_output_temp_table_u, |
| svd_output_temp_table_v=svd_output_temp_table_v, |
| scaled_source_table=scaled_source_table, |
| svd_v_transpose=svd_v_transpose, |
| source_table_copy=source_table_copy, |
| tmp_matrix_s_table=tmp_matrix_s_table, |
| source_table_grouped=source_table_grouped)) |
| # ------------------------------------------------------------------------ |
| |
| # ------------------------------------------------------------------------ |
| # Validate arguments: Same as pca |
| # ------------------------------------------------------------------------ |
| |
| |
| def _validate_args(schema_madlib, |
| source_table, |
| pc_table, |
| k, |
| row_id, |
| col_id=None, |
| val_id=None, |
| row_dim=None, |
| col_dim=None, |
| lanczos_iter=0, |
| use_correlation=False, |
| result_summary_table=None, |
| variance=None): |
| """ |
| Validates all arguments passed to the PCA function |
| Args: |
| @param schema_madlib Name of the schema where MADlib is installed |
| @param source_table Name of the source table |
| @param output_table Name of the output table |
| @param k Number of singular vectors to return |
| @param row_id Name of the row_id column |
| @param col_id Name of the col_id column |
| @param val_id Name of the val_id column |
| @param grouping_cols The columns that the data should be grouped by |
| @param lanczos_iter The number of lanczos iterations to use in the SVD calculation |
| @param use_correlation If the correlation matrix should be used instead of the covariance matrix |
| @param result_summary_table Name of summary table |
| @param variance Proportion of variance |
| |
| Returns: |
| None |
| |
| Throws: |
| plpy.error if any argument is invalid |
| |
| """ |
| _assert(source_table is not None and table_exists(source_table), |
| "PCA error: Source data table {0} does not exist!". |
| format(str(source_table))) |
| if not k and not variance: |
| plpy.error("""PCA error: components_param must be either |
| a positive integer or a float in the range (0.0,1.0]!""") |
| if k: |
| if k <= 0: |
| plpy.error("""PCA error: components_param must be either |
| a positive integer or a float in the range (0.0,1.0]!""") |
| if variance: |
| if (variance <= 0) or (variance > 1): |
| plpy.error("""PCA error: components_param must be either |
| a positive integer or a float in the range (0.0,1.0]!""") |
| # confirm output tables are valid |
| if pc_table: |
| _assert(not table_exists(pc_table, only_first_schema=True) and |
| not table_exists(pc_table + '_mean', only_first_schema=True), |
| "PCA error: Output table {pc_table}/{pc_table}_mean " |
| "already exist!".format(pc_table=pc_table)) |
| else: |
| plpy.error("PCA error: Invalid output table prefix!") |
| |
| _assert(columns_exist_in_table(source_table, [row_id], schema_madlib), |
| "PCA error: {1} column does not exist in {0}!". |
| format(source_table, "NULL" if row_id is None else row_id)) |
| |
| if (lanczos_iter < 0): |
| plpy.error("PCA error: lanczos_iter can't be negative! (Use zero for \ |
| default value) The provided value is {0}".format(str(lanczos_iter))) |
| |
| # If using sparse matrices, check that the parameters are reasonable |
| if col_id or val_id: |
| if not col_id: |
| plpy.error("PCA error: Column ID should be provided if \ |
| value ID is input!") |
| if not val_id: |
| plpy.error("PCA error: Value ID should be provided if \ |
| column ID is input!") |
| |
| _assert(columns_exist_in_table(source_table, [col_id], schema_madlib), |
| "PCA error: {1} column does not exist in {0}!". |
| format(source_table, col_id)) |
| _assert(columns_exist_in_table(source_table, [val_id], schema_madlib), |
| "PCA error: {1} column does not exist in {0}!". |
| format(source_table, val_id)) |
| |
| if not col_dim: |
| plpy.error("PCA error: Column dimensions should be provided if \ |
| using sparse matrices!") |
| if not row_dim: |
| plpy.error("PCA error: Row dimensions should be provided if \ |
| using sparse matrices!") |
| if row_dim <= 0: |
| plpy.error("PCA error: The row dimension must be larger than 0!") |
| |
| if col_dim <= 0: |
| plpy.error("PCA error: The column dimension must be larger than 0!") |
| |
| if use_correlation: |
| plpy.error("PCA error: Using the correlation matrix is not enabled! \ |
| This value must be set to FALSE") |
| |
| if result_summary_table: |
| if not result_summary_table.strip(): |
| plpy.error("PCA error: Invalid result summary table name!") |
| _assert(not table_exists(result_summary_table, only_first_schema=True), |
| "PCA error: Result summary table {0} \ |
| already exists!".format(result_summary_table)) |
| # ======================================================================== |
| |
| |
| def _recenter_data(schema_madlib, source_table, output_table, row_id, |
| col_name, dimension): |
| """ |
| Rescales the data table by column means. |
| |
| The output is stored in output_table. The input table should have a |
| array column that contains the data |
| |
| Args: |
| @param schema_madlib Name of the schema where MADlib is installed |
| @param source_table Name of the source table |
| @param output_table Name of the output table |
| @param row_id Name of the row_id column |
| @param col_name Name of the array column from input table |
| @param dimension |
| |
| Returns: |
| Column mean |
| """ |
| # Step 1: Compute column mean values |
| x_scales = utils_ind_var_scales(tbl_data=source_table, |
| col_ind_var=col_name, |
| dimension=dimension, |
| schema_madlib=schema_madlib) |
| |
| x_mean_str = _array_to_string(x_scales["mean"]) |
| x_std_str = _array_to_string([1] * dimension) |
| |
| # Step 2: Rescale the matrices |
| plpy.execute( |
| """ |
| CREATE TABLE {output_table} |
| m4_ifdef(`__POSTGRESQL__', `', `WITH (appendonly=true)') |
| AS |
| SELECT |
| {row_id} as row_id, |
| ({schema_madlib}.utils_normalize_data( |
| {col_name}, |
| '{x_mean_str}'::double precision[], |
| '{x_std_str}'::double precision[])) |
| AS row_vec |
| FROM {source_table} |
| """.format(schema_madlib=schema_madlib, |
| col_name=col_name, |
| row_id=row_id, |
| source_table=source_table, |
| output_table=output_table, |
| x_mean_str=x_mean_str, |
| x_std_str=x_std_str)) |
| |
| return x_mean_str |
| # ------------------------------------------------------------------------ |
| |
| # Sparse PCA train help function |
| |
| |
| def pca_sparse_help_message(schema_madlib, message=None, **kwargs): |
| """ |
| Given a help string, provide usage information |
| |
| Args: |
| @param schema_madlib Name of the MADlib schema |
| @param message Helper message to print |
| |
| Returns: |
| None |
| """ |
| if message is not None and \ |
| message.lower() in ("usage", "help", "?"): |
| return """ |
| ----------------------------------------------------------------------- |
| USAGE |
| ----------------------------------------------------------------------- |
| SELECT {schema_madlib}.pca_sparse_train( |
| source_table -- TEXT, Name of data table |
| pc_table -- TEXT, Name of the table containing the principal components |
| row_id -- TEXT, Column name for the row coordinates. |
| col_id -- TEXT, Column name for the column coordinates. |
| val_id -- TEXT, Column name for the sparse values. |
| row_dim, -- INTEGER, The number of rows in the sparse matrix |
| col_dim, -- INTEGER, The number of columns in the sparse matrix |
| components_param -- INTEGER OR FLOAT, The parameter to control the number of |
| principal components to calculate from the input data. |
| grouping_cols -- TEXT, Comma-separated list of grouping columns |
| (Default: NULL) |
| lanczos_iter -- INTEGER, The number of Lanczos iterations to use in the SVD calculation |
| (Default: minimum of of the smallest input |
| matrix dimension and k+40) |
| use_correlation -- BOOLEAN, If True correlation matrix is used for principal components |
| (Default: False) |
| rslt_summary_table -- TEXT, Table name to store summary of results |
| (Default: NULL) |
| ); |
| If components_param is INTEGER it is used for denoting the number of principal components to compute. |
| If components_param is FLOAT it is used as the target proportion of variance. |
| ------------------------------------------------------------------------- |
| OUTPUT TABLES |
| ------------------------------------------------------------------------- |
| A PCA model is created for each group, if grouping_cols is specified. |
| The output table ("pc_table" above) has the following columns: |
| row_id -- INTEGER, The ranking of the eigenvalues |
| prin_comp -- FLOAT[], The principal components |
| eigen_values -- FLOAT[] The eigenvalues associated with each principal component |
| grouping_cols -- The grouping columns (with their types), if any, |
| specified in grouping_cols |
| |
| A secondary output table named "pc_table"_mean is also generated. |
| This table has only the single column: |
| column_mean -- FLOAT[], The column means of the input data |
| |
| ------------------------------------------------------------------------- |
| RESULT SUMMARY TABLE |
| ------------------------------------------------------------------------- |
| The result summary table ("rslt_summary_table" above) has the following columns |
| rows_used -- INTEGER, Number of rows used in the PCA calculation |
| exec_time -- FLOAT, Number of milliseconds the PCA calculation took |
| use_correlation -- BOOLEAN, Value of parameter use_correlation |
| iter -- INTEGER, Number of iterations the SVD took to converge |
| recon_error -- FLOAT, Absolute error in the approximation |
| relative_recon_error -- FLOAT Relative error in the approximation |
| grouping_cols -- The grouping columns (with their types), if any, |
| specified in grouping_cols |
| """.format(schema_madlib=schema_madlib) |
| else: |
| return """ |
| ---------------------------------------------------------------- |
| Summary: Sparse PCA Training |
| ---------------------------------------------------------------- |
| Principal component analysis (PCA) is a mathematical procedure that uses an |
| orthogonal transformation to convert a set of observations of possibly |
| correlated variables into a set of values of linearly uncorrelated variables |
| called principal components. This transformation is defined in such a way that |
| the first principal component has the largest possible variance (i.e., |
| accounts for as much of the variability in the data as possible), and each |
| succeeding component in turn has the highest variance possible under the |
| constraint that it be orthogonal to (i.e., uncorrelated with) the preceding |
| components. |
| -- |
| For an overview on usage, run: |
| SELECT {schema_madlib}.pca_sparse_train('usage'); |
| -- |
| """.format(schema_madlib=schema_madlib) |
| |
| |
| def pca_help_message(schema_madlib, message=None, **kwargs): |
| """ |
| Given a help string, provide usage information |
| |
| Args: |
| @param schema_madlib Name of the MADlib schema |
| @param message Helper message to print |
| |
| Returns: |
| None |
| """ |
| if message is not None and \ |
| message.lower() in ("usage", "help", "?"): |
| return """ |
| ----------------------------------------------------------------------- |
| USAGE |
| ----------------------------------------------------------------------- |
| SELECT {schema_madlib}.pca_train( |
| source_table -- TEXT, Name of data table |
| pc_table -- TEXT, Name of the table containing the principal components |
| row_id -- TEXT, Column name for the row coordinates. |
| components_param -- INTEGER OR FLOAT, The parameter to control the number of |
| principal components to calculate from |
| the input data. |
| |
| grouping_cols -- TEXT, Comma-separated list of grouping column names |
| (Default: NULL) |
| lanczos_iter -- INTEGER, The number of Lanczos iterations to use in the SVD calculation |
| (Default: minimum of of the smallest input |
| matrix dimension and k+40) |
| use_correlation -- BOOLEAN, If True correlation matrix is used for principal components |
| (Default: False) |
| rslt_summary_table -- TEXT, Table name to store summary of results |
| (Default: NULL) |
| variance -- DOUBLE PRECISION, Proportion of variance |
| (Default: NULL) |
| ); |
| If components_param is INTEGER it is used for denoting the number of |
| principal components to compute. If components_param is FLOAT it is used |
| as the target proportion of variance. |
| ------------------------------------------------------------------------- |
| OUTPUT TABLES |
| ------------------------------------------------------------------------- |
| A PCA model is created for each group, if grouping_cols is specified. |
| The output table ("pc_table" above) has the following columns: |
| row_id -- INTEGER, The ranking of the eigenvalues |
| prin_comp -- FLOAT[], The principal components |
| eigen_values -- FLOAT[], The eigenvalues associated with each |
| principal component |
| grouping_cols -- The grouping columns (with their types), if any, |
| specified in grouping_cols |
| |
| A secondary output table named "pc_table"_mean is also generated. |
| This table has only the single column: |
| column_mean -- FLOAT[], The column means of the input data |
| grouping_cols -- The grouping columns (with their types), if any, |
| specified in grouping_cols |
| ------------------------------------------------------------------------- |
| RESULT SUMMARY TABLE |
| ------------------------------------------------------------------------- |
| The result summary table ("rslt_summary_table" above) has the following columns |
| rows_used -- INTEGER, Number of rows used in the PCA calculation |
| exec_time -- FLOAT, Number of milliseconds the PCA calculation took |
| use_correlation -- BOOLEAN, Value of parameter use_correlation |
| iter -- INTEGER, Number of iterations the SVD took to converge |
| recon_error -- FLOAT, Absolute error in the approximation |
| relative_recon_error -- FLOAT Relative error in the approximation |
| grouping_cols -- The grouping columns (with their types), if any, |
| specified in grouping_cols |
| """.format(schema_madlib=schema_madlib) |
| |
| else: |
| return """ |
| ---------------------------------------------------------------- |
| Summary: PCA Training |
| ---------------------------------------------------------------- |
| Principal component analysis (PCA) is a mathematical procedure that uses an |
| orthogonal transformation to convert a set of observations of possibly |
| correlated variables into a set of values of linearly uncorrelated variables |
| called principal components. This transformation is defined in such a way that |
| the first principal component has the largest possible variance (i.e., |
| accounts for as much of the variability in the data as possible), and each |
| succeeding component in turn has the highest variance possible under the |
| constraint that it be orthogonal to (i.e., uncorrelated with) the preceding |
| components. |
| -- |
| For an overview on usage, run: |
| SELECT {schema_madlib}.pca_train('usage'); |
| -- |
| """.format(schema_madlib=schema_madlib) |
