def _test_mapping_query(self):
table = 'measurement'
hpo_ids = ['chs', 'pitt']
project_id = bq_utils.app_identity.get_application_id()
dataset_id = bq_utils.get_dataset_id()
created_tables = []
for hpo_id in hpo_ids:
hpo_table = self._create_hpo_table(hpo_id, table, dataset_id)
created_tables.append(hpo_table)
q = ehr_union.mapping_query(table, hpo_ids, dataset_id, project_id)
def test_mapping_query(self):
table = 'measurement'
hpo_ids = ['chs', 'pitt']
mapping_msg = 'Expected mapping subquery count %s but got %s for hpo_id %s'
project_id = bq_utils.app_identity.get_application_id()
dataset_id = bq_utils.get_dataset_id()
created_tables = []
for hpo_id in hpo_ids:
hpo_table = self._create_hpo_table(hpo_id, table, dataset_id)
created_tables.append(hpo_table)
query = ehr_union.mapping_query(table, hpo_ids, dataset_id, project_id)
dataset_id = os.environ.get('BIGQUERY_DATASET_ID')
app_id = os.getenv('APPLICATION_ID')
#testing the query string
expected_query = '''
WITH all_measurement AS (
(SELECT 'chs_measurement' AS src_table_id,
measurement_id AS src_measurement_id,
ROW_NUMBER() over() + 100000000 as measurement_id
FROM `{app_id}.{dataset_id}.chs_measurement`)
UNION ALL
(SELECT 'pitt_measurement' AS src_table_id,
measurement_id AS src_measurement_id,
ROW_NUMBER() over() + 200000000 as measurement_id
FROM `{app_id}.{dataset_id}.pitt_measurement`)
)
SELECT
src_table_id,
src_measurement_id,
measurement_id,
SUBSTR(src_table_id, 1, STRPOS(src_table_id, "_measurement")-1) AS src_hpo_id
FROM all_measurement
'''.format(dataset_id=dataset_id, app_id=app_id)
self.assertEqual(
expected_query.strip(), query.strip(),
"Mapping query for \n {q} \n to is not as expected".format(
q=query))
def test_mapping_query(self):
table = 'measurement'
hpo_ids = ['nyc', 'pitt']
mapping_msg = 'Expected mapping subquery count %s but got %s for hpo_id %s'
project_id = bq_utils.app_identity.get_application_id()
dataset_id = bq_utils.get_dataset_id()
created_tables = []
for hpo_id in hpo_ids:
hpo_table = self._create_hpo_table(hpo_id, table, dataset_id)
created_tables.append(hpo_table)
query = ehr_union.mapping_query(table, hpo_ids, dataset_id, project_id)
# testing the query string
expected_query = '''
WITH all_measurement AS (
(SELECT 'nyc_measurement' AS src_table_id,
measurement_id AS src_measurement_id,
measurement_id + 3000000000000000 as measurement_id
FROM `{project_id}.{dataset_id}.nyc_measurement`)
UNION ALL
(SELECT 'pitt_measurement' AS src_table_id,
measurement_id AS src_measurement_id,
measurement_id + 4000000000000000 as measurement_id
FROM `{project_id}.{dataset_id}.pitt_measurement`)
)
SELECT DISTINCT
src_table_id,
src_measurement_id,
measurement_id,
SUBSTR(src_table_id, 1, STRPOS(src_table_id, "_measurement")-1) AS src_hpo_id
FROM all_measurement
'''.format(dataset_id=dataset_id, project_id=project_id)
self.assertEqual(
expected_query.strip(), query.strip(),
"Mapping query for \n {q} \n to is not as expected".format(
q=query))
def test_mapping_query(self, mock_list_all_table_ids, mock_hpo_info):
mock_hpo_info.return_value = [{
'hpo_id': hpo_id
} for hpo_id in self.hpo_ids]
mock_list_all_table_ids.return_value = [
f'{self.FAKE_SITE_1}_measurement', f'{self.FAKE_SITE_2}_measurement'
]
dataset_id = 'fake_dataset'
project_id = 'fake_project'
table = 'measurement'
query = eu.mapping_query(table, self.hpo_ids, dataset_id, project_id)
# testing the query string
expected_query = f'''
WITH all_measurement AS (
(SELECT '{self.FAKE_SITE_1}_measurement' AS src_table_id,
measurement_id AS src_measurement_id,
measurement_id + 3000000000000000 AS measurement_id
FROM `{project_id}.{dataset_id}.{self.FAKE_SITE_1}_measurement`)
UNION ALL
(SELECT '{self.FAKE_SITE_2}_measurement' AS src_table_id,
measurement_id AS src_measurement_id,
measurement_id + 4000000000000000 AS measurement_id
FROM `{project_id}.{dataset_id}.{self.FAKE_SITE_2}_measurement`)
)
SELECT DISTINCT
src_table_id,
src_measurement_id,
measurement_id,
SUBSTR(src_table_id, 1, STRPOS(src_table_id, "_measurement")-1) AS src_hpo_id,
'{dataset_id}' as src_dataset_id
FROM all_measurement
'''.format(dataset_id=dataset_id, project_id=project_id)
self.assertEqual(
expected_query.strip(), query.strip(),
"Mapping query for \n {q} \n to is not as expected".format(q=query))