def test_no_tau_noisy(self): # should never drop rows reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 0.01) for i in range(10): rs = private_reader.execute_df("SELECT COUNT(*) AS c FROM PUMS.PUMS WHERE age > 90 AND educ = '8'") assert(len(rs['c']) == 1)
def test_err1(self): s = copy.copy(schema) s["PUMS.PUMS"]["income"].maxval = None reader = PandasReader(df, s) private_reader = PrivateReader(reader, s, 4.0) with pytest.raises(ValueError): rs = private_reader.execute_df("SELECT SUM(income) FROM PUMS.PUMS")
def run_agg_query(self, df, metadata, query, confidence, get_exact=True): """ Run the query using the private reader and input query Get query response back """ reader = PandasReader(df, metadata) actual = 0.0 # VAR not supported in Pandas Reader. So not needed to fetch actual on every aggregation if (get_exact): actual = reader.execute_typed(query).rows()[1:][0][0] private_reader = PrivateReader(reader, metadata, self.epsilon) query_ast = private_reader.parse_query_string(query) srs_orig = private_reader.reader.execute_ast_typed(query_ast) noisy_values = [] low_bounds = [] high_bounds = [] for idx in range(self.repeat_count): srs = TypedRowset(srs_orig.rows(), list(srs_orig.types.values())) res = private_reader._execute_ast(query_ast, True) # Disabled because confidence interval not available in report #interval = res.report[res.colnames[0]].intervals[confidence] #low_bounds.append(interval[0].low) #high_bounds.append(interval[0].high) noisy_values.append(res.rows()[1:][0][0]) return np.array(noisy_values), actual, low_bounds, high_bounds
def test_group_by_noisy_typed_order_desc(self): reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 4.0) rs = private_reader.execute_typed( "SELECT COUNT(*) AS c, married AS m FROM PUMS.PUMS GROUP BY married ORDER BY c DESC" ) assert (rs['c'][0] > rs['c'][1])
def test_check_thresholds_gauss(self): # check tau for various privacy parameters epsilons = [0.1, 2.0] max_contribs = [1, 3] deltas = [10E-5, 10E-15] query = "SELECT COUNT(*) FROM PUMS.PUMS GROUP BY married" reader = PandasReader(df, schema) qp = QueryParser(schema) q = qp.query(query) for eps in epsilons: for d in max_contribs: for delta in deltas: # using slightly different formulations of same formula from different papers # make sure private_reader round-trips gaus_scale = math.sqrt(d) * math.sqrt( 2 * math.log(1.25 / delta)) / eps gaus_rho = 1 + gaus_scale * math.sqrt( 2 * math.log(d / math.sqrt(2 * math.pi * delta))) schema_c = copy.copy(schema) schema_c["PUMS.PUMS"].max_ids = d private_reader = PrivateReader(reader, schema_c, eps, delta) assert (private_reader._options.max_contrib == d) r = private_reader.execute_ast(q) assert (math.isclose(private_reader.tau, gaus_rho, rel_tol=0.03, abs_tol=2))
def test_ok1(self): s = copy.copy(schema) s["PUMS.PUMS"]["income"].maxval = None reader = PandasReader(df, s) private_reader = PrivateReader(reader, s, 4.0) rs = private_reader.execute_df( "SELECT income FROM PUMS.PUMS GROUP BY income")
def test_execute_without_dpsu(self): schema_no_dpsu = copy.copy(schema) schema_no_dpsu["PUMS.PUMS"].use_dpsu = False reader = PandasReader(df, schema_no_dpsu) private_reader = PrivateReader(reader, schema_no_dpsu, 1.0) assert(private_reader._options.use_dpsu == False) query = QueryParser(schema_no_dpsu).queries("SELECT COUNT(*) AS c FROM PUMS.PUMS GROUP BY married")[0] assert(private_reader._get_reader(query) is private_reader.reader)
def test_empty_result_count_typed_notau_prepost(self): reader = PandasReader(df, schema) query = QueryParser(schema).queries("SELECT COUNT(*) as c FROM PUMS.PUMS WHERE age > 100")[0] private_reader = PrivateReader(reader, schema, 1.0) private_reader._execute_ast(query, True) for i in range(3): trs = private_reader._execute_ast(query, True) assert(len(trs) == 2)
def QuerytoAST(self, query, meta, data): reader = PandasReader(meta, data) private_reader = PrivateReader(meta, reader, self.pp.epsilon) # query = 'SELECT Usage AS Usage, SUM(Usage) + 3 AS u FROM dataset.dataset GROUP BY Role' try: ast = private_reader.parse_query_string(query) except: return return ast
def test_viz_child_nodes(self): query = "SELECT AVG(age) AS my_sum FROM PUMS.PUMS GROUP BY age" reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 1.0) inner, outer = private_reader.rewrite(query) aggfuncs = outer.find_nodes(AggFunction) for aggfunc in aggfuncs: graph = aggfunc.visualize(n_trunc=30) assert (isinstance(graph, Digraph))
def test_yes_tau(self): # should usually drop some rows reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 1.0, 1/10000) lengths = [] for i in range(10): rs = private_reader.execute_df("SELECT COUNT(*) AS c FROM PUMS.PUMS WHERE age > 80 GROUP BY educ") lengths.append(len(rs['c'])) l = lengths[0] assert(any([l != ll for ll in lengths]))
def test_viz_query_rewritten(self): query = "SELECT SUM(age) AS my_sum FROM PUMS.PUMS GROUP BY age" parsed_query = QueryParser(schema).query(query) reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 1.0) inner, outer = private_reader.rewrite_ast(parsed_query) graph = outer.visualize(n_trunc=30) assert (isinstance(graph, Digraph)) #graph.render('ast_digraph', view=True, cleanup=True) graph = inner.visualize(n_trunc=30) assert (isinstance(graph, Digraph))
def test_legacy_params_private_reader(self): reader = PandasReader(df, schema) # params swapped with pytest.warns(Warning): private_reader = PrivateReader(schema, reader, 1.0) assert(isinstance(private_reader.reader, PandasReader)) # doubled up params of wrong type should fail with pytest.raises(Exception): private_reader = PrivateReader(schema, schema, 1.0) with pytest.raises(Exception): private_reader = PrivateReader(reader, reader, 1.0)
def test_calculate_multiplier(self): pums_meta_path = os.path.join( git_root_dir, os.path.join("service", "datasets", "PUMS.yaml")) pums_csv_path = os.path.join( git_root_dir, os.path.join("service", "datasets", "PUMS.csv")) pums_schema = CollectionMetadata.from_file(pums_meta_path) pums_df = pd.read_csv(pums_csv_path) pums_reader = PandasReader(pums_df, pums_schema) query = "SELECT COUNT(*) FROM PUMS.PUMS" cost = PrivateReader.get_budget_multiplier(pums_schema, pums_reader, query) query = "SELECT AVG(age) FROM PUMS.PUMS" cost_avg = PrivateReader.get_budget_multiplier(pums_schema, pums_reader, query) assert 1 + cost == cost_avg
def release(self, dataset: object) -> Report: """ Dataset is a collection of [Dataset Metadata, PandasReader] Releases response to SQL query based on the number of repetitions requested by eval_params if actual is set of False. """ private_reader = PrivateReader(dataset[1], dataset[0], self.privacy_params.epsilon) query_ast = private_reader.parse_query_string(self.algorithm) srs_orig = private_reader.reader._execute_ast_df(query_ast) noisy_values = [] for idx in range(self.eval_params.repeat_count): res = private_reader._execute_ast(query_ast, True) noisy_values.append(res[1:][0][0]) return Report({"__key__": noisy_values})
def setup_class(cls): meta = CollectionMetadata.from_file(meta_path) meta["PUMS.PUMS"].censor_dims = False df = pd.read_csv(csv_path) reader = PandasReader(df, meta) private_reader = PrivateReader(reader, meta, 10.0, 10E-3) cls.reader = private_reader
def setup_class(self): meta = CollectionMetadata.from_file(meta_path) meta["PUMS.PUMS"].censor_dims = False meta["PUMS.PUMS"]["sex"].type = "int" meta["PUMS.PUMS"]["educ"].type = "int" meta["PUMS.PUMS"]["married"].type = "bool" df = pd.read_csv(csv_path) reader = PandasReader(df, meta) private_reader = PrivateReader(reader, meta, 10.0, 10E-3) self.reader = private_reader
def test_dpsu_vs_korolova(self): query = "SELECT ngram, COUNT(*) as n FROM reddit.reddit GROUP BY ngram ORDER BY n desc" reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 3.0) private_reader.options.max_contrib = 10 result = private_reader.execute_typed(query) private_reader_korolova = PrivateReader(reader, schema, 3.0) private_reader_korolova.options.dpsu = False private_reader_korolova.options.max_contrib = 10 korolova_result = private_reader_korolova.execute_typed(query) assert len(result['n']) > len(korolova_result['n']) assert len(final_df) < len(df)
def run_agg_query_df(self, df, metadata, query, confidence, file_name="d1"): """ Run the query using the private reader and input query Get query response back for multiple dimensions and aggregations """ # Getting exact result reader = PandasReader(df, metadata) exact_res = reader.execute(query)[1:] private_reader = PrivateReader(reader, metadata, self.epsilon) query_ast = private_reader.parse_query_string(query) # Distinguishing dimension and measure columns sample_res = private_reader._execute_ast(query_ast, True) headers = sample_res[0] dim_cols = [] num_cols = [] out_syms = query_ast.all_symbols() out_types = [s[1].type() for s in out_syms] out_col_names = [s[0] for s in out_syms] for col, ctype in zip(out_col_names, out_types): if (ctype == "string"): dim_cols.append(col) else: num_cols.append(col) # Repeated query and store results res = [] for idx in range(self.repeat_count): dim_rows = [] num_rows = [] singleres = private_reader._execute_ast_df(query_ast, True) #values = singleres[col] for col in dim_cols: dim_rows.append(singleres[col].tolist()) for col in num_cols: values = singleres[col].tolist() num_rows.append(list(zip(values))) res.extend(list(zip(*dim_rows, *num_rows))) exact_df = pd.DataFrame(exact_res, columns=headers) noisy_df = pd.DataFrame(res, columns=headers) # Add a dummy dimension column for cases where no dimensions available for merging D1 and D2 if (len(dim_cols) == 0): dim_cols.append("__dim__") if (dim_cols[0] == "__dim__"): exact_df[dim_cols[0]] = ["key"] * len(exact_df) noisy_df[dim_cols[0]] = ["key"] * len(noisy_df) return noisy_df, exact_df, dim_cols, num_cols
import pandas as pd from opendp.smartnoise.sql import PostgresReader, PrivateReader from opendp.smartnoise.metadata import CollectionMetadata meta = CollectionMetadata.from_file('PUMS_large.yaml') query = 'SELECT married, AVG(income) AS income, COUNT(*) AS n FROM PUMS.PUMS_large GROUP BY married' query = 'SELECT AVG(age) FROM PUMS.PUMS_large' reader = PostgresReader('127.0.0.1', 'PUMS', 'postgres') private_reader = PrivateReader(reader, meta, 1.0) exact = reader.execute_typed(query) print(exact) private = private_reader.execute_typed(query) print(private)
import json import sys import pandas as pd from opendp.smartnoise.client import get_dataset_client from opendp.smartnoise.data.adapters import load_reader, load_metadata from opendp.smartnoise.sql import PrivateReader if __name__ == "__main__": dataset_name = sys.argv[1] budget = float(sys.argv[2]) query = sys.argv[3] with mlflow.start_run(): dataset_document = get_dataset_client().read(dataset_name, budget) reader = load_reader(dataset_document) metadata = load_metadata(dataset_document) budget_per_column = budget / PrivateReader.get_budget_multiplier( metadata, reader, query) private_reader = PrivateReader(reader, metadata, budget_per_column) rowset = private_reader.execute(query) result = {"query_result": rowset} df = pd.DataFrame(rowset[1:], columns=rowset[0]) with open("result.json", "w") as stream: json.dump(df.to_dict(), stream) mlflow.log_artifact("result.json")
def test_sum_noisy_postprocess(self): reader = PandasReader(df, schema) private_reader = PrivateReader(reader, schema, 1.0) trs = private_reader.execute_df("SELECT POWER(SUM(age), 2) as age_total FROM PUMS.PUMS") assert(trs['age_total'][0] > 1000 ** 2)
def run_agg_query_df(self, df, metadata, query, confidence, file_name="d1"): """ Run the query using the private reader and input query Get query response back for multiple dimensions and aggregations """ # Getting exact result reader = PandasReader(df, metadata) exact = reader.execute_typed(query).rows()[1:] exact_res = [] for row in exact: exact_res.append(row) private_reader = PrivateReader(reader, metadata, self.epsilon) query_ast = private_reader.parse_query_string(query) # Distinguishing dimension and measure columns srs_orig = private_reader.reader.execute_ast_typed(query_ast) srs = TypedRowset(srs_orig.rows(), list(srs_orig.types.values())) sample_res = private_reader._execute_ast(query_ast, True) headers = sample_res.colnames dim_cols = [] num_cols = [] for col in headers: if (sample_res.types[col] == "string"): dim_cols.append(col) else: num_cols.append(col) # Repeated query and store results along with intervals res = [] for idx in range(self.repeat_count): dim_rows = [] num_rows = [] srs = TypedRowset(srs_orig.rows(), list(srs_orig.types.values())) singleres = private_reader._execute_ast(query_ast, True) values = singleres[col] for col in dim_cols: dim_rows.append(singleres[col]) for col in num_cols: values = singleres[col] #low = singleres.report[col].intervals[confidence].low #high = singleres.report[col].intervals[confidence].high #num_rows.append(list(zip(values, low, high))) num_rows.append(list(zip(values))) res.extend(list(zip(*dim_rows, *num_rows))) exact_df = pd.DataFrame(exact_res, columns=headers) noisy_df = pd.DataFrame(res, columns=headers) # Add a dummy dimension column for cases where no dimensions available for merging D1 and D2 if (len(dim_cols) == 0): dim_cols.append("__dim__") if (dim_cols[0] == "__dim__"): exact_df[dim_cols[0]] = ["key"] * len(exact_df) noisy_df[dim_cols[0]] = ["key"] * len(noisy_df) return noisy_df, exact_df, dim_cols, num_cols
def create_private(self, meta, epsilon, delta): r = self.create() return PrivateReader(r, meta, epsilon, delta)
import pandas as pd from opendp.smartnoise.sql import PandasReader, PrivateReader from opendp.smartnoise.metadata import CollectionMetadata pums = pd.read_csv('PUMS.csv') meta = CollectionMetadata.from_file('PUMS.yaml') query = 'SELECT married, AVG(income) AS income, COUNT(*) AS n FROM PUMS.PUMS GROUP BY married' query = 'SELECT COUNT(*) AS n, COUNT(pid) AS foo FROM PUMS.PUMS WHERE age > 80 GROUP BY educ' reader = PandasReader(meta, pums) private_reader = PrivateReader(meta, reader, 4.0) private_reader.options.censor_dims = True private_reader.options.clamp_counts = True exact = reader.execute_typed(query) print(exact) private = private_reader.execute_typed(query) print(private)