def _gather_ehr_rdr_de_identified_queries(project_id, dataset_id):
"""
gathers all the queries required to clean de_identified dataset
:param project_id: project name
:param dataset_id: de_identified dataset name
:return: returns list of queries
"""
query_list = []
query_list.extend(
id_dedup.get_id_deduplicate_queries(project_id, dataset_id))
query_list.extend(
clean_years.get_year_of_birth_queries(project_id, dataset_id))
query_list.extend(
neg_ages.get_negative_ages_queries(project_id, dataset_id))
query_list.extend(
bad_end_dates.get_bad_end_date_queries(project_id, dataset_id))
query_list.extend(
person_validator.get_person_id_validation_queries(
project_id, dataset_id))
query_list.extend(
valid_death_dates.get_valid_death_date_queries(project_id, dataset_id))
query_list.extend(
drug_refills_supply.get_days_supply_refills_queries(
project_id, dataset_id))
query_list.extend(
fill_source_value.get_fill_freetext_source_value_fields_queries(
project_id, dataset_id))
return query_list
def test_get_person_id_validation_queries_deid(self):
# pre conditions
# test
results = validator.get_person_id_validation_queries('foo', 'bar_deid')
# post conditions
self.assertEqual(len(results), ((len(self.all_tables) * 2) - 1))
existing_and_consenting = validator.EXISTING_AND_VALID_CONSENTING_RECORDS
existing_in_person_table = validator.SELECT_EXISTING_PERSON_IDS
expected = []
for table in self.mapped_tables:
field_names = [
'entry.' + field['name']
for field in resources.fields_for(table)
]
fields = ', '.join(field_names)
expected.append({
clean_consts.QUERY:
existing_and_consenting.format(project='foo',
dataset='bar_deid',
mapping_dataset='bar',
table=table,
fields=fields),
clean_consts.DESTINATION_TABLE:
table,
clean_consts.DESTINATION_DATASET:
'bar_deid',
clean_consts.DISPOSITION:
bq_consts.WRITE_TRUNCATE,
})
for table in self.all_tables:
field_names = [
'entry.' + field['name']
for field in resources.fields_for(table)
]
fields = ', '.join(field_names)
expected.append({
clean_consts.QUERY:
existing_in_person_table.format(project='foo',
dataset='bar_deid',
table=table,
fields=fields),
clean_consts.DESTINATION_TABLE:
table,
clean_consts.DESTINATION_DATASET:
'bar_deid',
clean_consts.DISPOSITION:
bq_consts.WRITE_TRUNCATE,
})
self.assertEqual(expected, results)
def test_get_person_id_validation_queries(self):
# pre conditions
# test
results = validator.get_person_id_validation_queries(
self.project, self.dataset, self.sandbox)
# post conditions
self.assertEqual(len(results),
len(self.mapped_tables) + len(self.drop_tables) * 2)
existing_and_consenting = validator.EXISTING_AND_VALID_CONSENTING_RECORDS
existing_in_person_table = drop_rows_for_missing_persons.RECORDS_FOR_NON_EXISTING_PIDS
expected = []
for table in self.mapped_tables:
field_names = [
'entry.' + field['name']
for field in resources.fields_for(table)
]
fields = ', '.join(field_names)
expected.append({
clean_consts.QUERY:
existing_and_consenting.format(project=self.project,
dataset=self.dataset,
mapping_dataset=self.dataset,
table=table,
fields=fields),
clean_consts.DESTINATION_TABLE:
table,
clean_consts.DESTINATION_DATASET:
self.dataset,
clean_consts.DISPOSITION:
bq_consts.WRITE_TRUNCATE,
})
for table in self.drop_tables:
sandbox_ddl_query = common.JINJA_ENV.from_string(
SANDBOX_DDL).render(
project=self.project,
sandbox_dataset=self.sandbox,
sandbox_table=drop_rows_for_missing_persons_sandbox_for(
table))
expected.append({
clean_consts.QUERY:
existing_in_person_table.render(
query_type=sandbox_ddl_query,
project=self.project,
dataset=self.dataset,
table=table)
})
expected.append({
clean_consts.QUERY:
existing_in_person_table.render(query_type='DELETE',
project=self.project,
dataset=self.dataset,
table=table)
})
self.assertEqual(expected, results)