def tearDown(self):
table_name = 'raw_prescribing_data_2016_01'
try:
BQClient('tmp_eu').delete_table(table_name)
except NotFound:
pass
table = BQClient('hscic').get_table('prescribing')
table.delete_all_rows()
try:
os.remove('frontend/tests/fixtures/commands/' +
'convert_hscic_prescribing/2016_01/' +
'Detailed_Prescribing_Information_formatted.CSV')
except OSError:
pass
def tearDown(self):
table_name = "raw_prescribing_data_2016_01"
try:
BQClient("tmp_eu").delete_table(table_name)
except NotFound:
pass
table = BQClient("hscic").get_table("prescribing_v2")
table.delete_all_rows()
try:
os.remove("frontend/tests/fixtures/commands/" +
"convert_hscic_prescribing/2016_01/" +
"Detailed_Prescribing_Information_formatted.CSV")
except OSError:
pass
def test_data_is_aggregated(self):
# there are 11 rows in the input file; 2 are for the same
# practice/presentation and should be collapsed, and 1 is for
# an UNKNONWN SURGERY (see issue #349)
raw_data_path = ("frontend/tests/fixtures/commands/" +
"convert_hscic_prescribing/2016_01/" +
"EPD_201601.csv")
gcs_path = "hscic/prescribing_v2/2016_01/EPD_201601.csv"
client = StorageClient()
bucket = client.get_bucket()
blob = bucket.blob(gcs_path)
with open(raw_data_path, "rb") as f:
blob.upload_from_file(f)
call_command("convert_hscic_prescribing", filename=raw_data_path)
# Test that data added to prescribing table
client = BQClient()
sql = """SELECT *
FROM {hscic}.prescribing_v2
WHERE month = TIMESTAMP('2016-01-01')"""
rows = list(results_to_dicts(client.query(sql)))
self.assertEqual(len(rows), 9)
for row in rows:
if row["practice"] == "P92042" and row[
"bnf_code"] == "0202010B0AAABAB":
self.assertEqual(row["quantity"], 1288)
def update_bnf_table():
"""Update `bnf` table from cloud-stored CSV"""
storage_client = StorageClient()
bucket = storage_client.get_bucket()
blobs = bucket.list_blobs(prefix="hscic/bnf_codes/")
blobs = sorted(blobs, key=lambda blob: blob.name, reverse=True)
blob = blobs[0]
bq_client = BQClient("hscic")
table = bq_client.get_table("bnf")
table.insert_rows_from_storage(blob.name, skip_leading_rows=1)
def test_data_is_aggregated(self):
# there are 11 rows in the input file; 2 are for the same
# practice/presentation and should be collapsed, and 1 is for
# an UNKNONWN SURGERY (see issue #349)
raw_data_path = 'frontend/tests/fixtures/commands/' +\
'convert_hscic_prescribing/2016_01/' +\
'Detailed_Prescribing_Information.csv'
converted_data_path = 'frontend/tests/fixtures/commands/' +\
'convert_hscic_prescribing/2016_01/' +\
'Detailed_Prescribing_Information_formatted.CSV'
gcs_path = 'hscic/prescribing/2016_01/' +\
'Detailed_Prescribing_Information.csv'
client = StorageClient()
bucket = client.get_bucket()
blob = bucket.blob(gcs_path)
with open(raw_data_path) as f:
blob.upload_from_file(f)
call_command('convert_hscic_prescribing', filename=raw_data_path)
# Test that data added to prescribing table
client = BQClient()
sql = '''SELECT *
FROM {hscic}.prescribing
WHERE month = TIMESTAMP('2016-01-01')'''
rows = list(results_to_dicts(client.query(sql)))
self.assertEqual(len(rows), 9)
for row in rows:
if row['practice'] == 'P92042' and \
row['bnf_code'] == '0202010B0AAABAB':
self.assertEqual(row['quantity'], 1288)
# Test that downloaded data is correct
with open(converted_data_path) as f:
rows = list(csv.reader(f))
self.assertEqual(len(rows), 9)
for row in rows:
if row[1] == 'P92042' and row[2] == '0202010B0AAABAB':
self.assertEqual(row[6], '1288')
def handle(self, *args, **kwargs):
update_bnf_table()
client = BQClient("hscic")
table = client.get_table("practices")
table.insert_rows_from_pg(models.Practice, schemas.PRACTICE_SCHEMA)
table = client.get_table("presentation")
table.insert_rows_from_pg(
models.Presentation,
schemas.PRESENTATION_SCHEMA,
transformer=schemas.presentation_transform,
)
table = client.get_table("practice_statistics")
columns = [field.name for field in schemas.PRACTICE_STATISTICS_SCHEMA]
columns[0] = "date"
columns[-1] = "practice_id"
table.insert_rows_from_pg(
models.PracticeStatistics,
schema=schemas.PRACTICE_STATISTICS_SCHEMA,
columns=columns,
transformer=schemas.statistics_transform,
)
sql = "SELECT MAX(month) FROM {hscic}.practice_statistics_all_years"
results = client.query(sql)
if results.rows[0][0] is None:
last_uploaded_practice_statistics_date = datetime.date(1900, 1, 1)
else:
last_uploaded_practice_statistics_date = results.rows[0][0].date()
table = client.get_table("practice_statistics_all_years")
sql = """SELECT *
FROM {hscic}.practice_statistics
WHERE month > TIMESTAMP('{date}')"""
substitutions = {"date": last_uploaded_practice_statistics_date}
table.insert_rows_from_query(sql,
write_disposition="WRITE_APPEND",
substitutions=substitutions)
table = client.get_table("pcns")
table.insert_rows_from_pg(models.PCN, schemas.PCN_SCHEMA)
table = client.get_table("ccgs")
table.insert_rows_from_pg(models.PCT,
schemas.CCG_SCHEMA,
transformer=schemas.ccgs_transform)
table = client.get_table("stps")
table.insert_rows_from_pg(models.STP, schemas.STP_SCHEMA)
table = client.get_table("regional_teams")
table.insert_rows_from_pg(models.RegionalTeam,
schemas.REGIONAL_TEAM_SCHEMA)
date = models.ImportLog.objects.latest_in_category(
"prescribing").current_at
table = client.get_table("prescribing_" + date.strftime("%Y_%m"))
sql = """SELECT * FROM {hscic}.prescribing_v2
WHERE month = TIMESTAMP('{date}')"""
substitutions = {"date": date}
table.insert_rows_from_query(sql, substitutions=substitutions)
def handle(self, *args, **kwargs):
# Make sure that PracticeStatistics and Prescription tables both have
# latest data.
latest_practice_statistic_date = models.PracticeStatistics.objects.aggregate(
Max("date"))["date__max"]
latest_prescription_date = models.Prescription.objects.aggregate(
Max("processing_date"))["processing_date__max"]
if latest_practice_statistic_date != latest_prescription_date:
msg = ("Latest PracticeStatistics object has date {}, "
"while latest Prescription object has processing_date {}".
format(latest_practice_statistic_date,
latest_prescription_date))
raise CommandError(msg)
date = latest_prescription_date
update_bnf_table()
client = BQClient("hscic")
table = client.get_table("practices")
table.insert_rows_from_pg(models.Practice, schemas.PRACTICE_SCHEMA)
table = client.get_table("presentation")
table.insert_rows_from_pg(
models.Presentation,
schemas.PRESENTATION_SCHEMA,
transformer=schemas.presentation_transform,
)
table = client.get_table("practice_statistics")
columns = [field.name for field in schemas.PRACTICE_STATISTICS_SCHEMA]
columns[0] = "date"
columns[-1] = "practice_id"
table.insert_rows_from_pg(
models.PracticeStatistics,
schema=schemas.PRACTICE_STATISTICS_SCHEMA,
columns=columns,
transformer=schemas.statistics_transform,
)
sql = "SELECT MAX(month) FROM {hscic}.practice_statistics_all_years"
results = client.query(sql)
if results.rows[0][0] is None:
last_uploaded_practice_statistics_date = datetime.date(1900, 1, 1)
else:
last_uploaded_practice_statistics_date = results.rows[0][0].date()
table = client.get_table("practice_statistics_all_years")
sql = """SELECT *
FROM {hscic}.practice_statistics
WHERE month > TIMESTAMP('{date}')"""
substitutions = {"date": last_uploaded_practice_statistics_date}
table.insert_rows_from_query(sql,
write_disposition="WRITE_APPEND",
substitutions=substitutions)
table = client.get_table("ccgs")
table.insert_rows_from_pg(models.PCT,
schemas.CCG_SCHEMA,
transformer=schemas.ccgs_transform)
table = client.get_table("stps")
table.insert_rows_from_pg(models.STP, schemas.STP_SCHEMA)
table = client.get_table("regional_teams")
table.insert_rows_from_pg(models.RegionalTeam,
schemas.REGIONAL_TEAM_SCHEMA)
table = client.get_table("prescribing_" + date.strftime("%Y_%m"))
sql = """SELECT * FROM {hscic}.prescribing
WHERE month = TIMESTAMP('{date}')"""
substitutions = {"date": date}
table.insert_rows_from_query(sql, substitutions=substitutions)
def setUp(self):
client = BQClient("hscic")
client.get_or_create_table("prescribing", PRESCRIBING_SCHEMA)
def handle(self, *args, **kwargs):
# Make sure that PracticeStatistics and Prescription tables both have
# latest data.
latest_practice_statistic_date = models.PracticeStatistics.objects\
.aggregate(Max('date'))['date__max']
latest_prescription_date = models.Prescription.objects\
.aggregate(Max('processing_date'))['processing_date__max']
if latest_practice_statistic_date != latest_prescription_date:
msg = 'Latest PracticeStatistics object has date {}, '\
'while latest Prescription object has processing_date {}'\
.format(latest_practice_statistic_date, latest_prescription_date)
raise CommandError(msg)
date = latest_prescription_date
update_bnf_table()
client = BQClient('hscic')
table = client.get_table('practices')
columns = [field.name for field in schemas.PRACTICE_SCHEMA]
table.insert_rows_from_pg(models.Practice, columns)
table = client.get_table('presentation')
columns = [field.name for field in schemas.PRESENTATION_SCHEMA]
table.insert_rows_from_pg(models.Presentation, columns,
schemas.presentation_transform)
table = client.get_table('practice_statistics')
columns = [field.name for field in schemas.PRACTICE_STATISTICS_SCHEMA]
columns[0] = 'date'
columns[-1] = 'practice_id'
table.insert_rows_from_pg(models.PracticeStatistics, columns,
schemas.statistics_transform)
sql = 'SELECT MAX(month) FROM {hscic}.practice_statistics_all_years'
results = client.query(sql)
if results.rows[0][0] is None:
last_uploaded_practice_statistics_date = datetime.date(1900, 1, 1)
else:
last_uploaded_practice_statistics_date = results.rows[0][0].date()
table = client.get_table('practice_statistics_all_years')
sql = '''SELECT
month,
pct_id,
practice,
male_0_4,
female_0_4,
male_5_14,
female_5_14,
male_15_24,
female_15_24,
male_25_34,
female_25_34,
male_35_44,
female_35_44,
male_45_54,
female_45_54,
male_55_64,
female_55_64,
male_65_74,
female_65_74,
male_75_plus,
female_75_plus,
total_list_size
FROM {hscic}.practice_statistics
WHERE month > TIMESTAMP('{date}')'''
substitutions = {'date': last_uploaded_practice_statistics_date}
table.insert_rows_from_query(sql,
write_disposition='WRITE_APPEND',
substitutions=substitutions)
table = client.get_table('ccgs')
columns = [field.name for field in schemas.CCG_SCHEMA]
table.insert_rows_from_pg(models.PCT, columns, schemas.ccgs_transform)
table = client.get_table('ppu_savings')
columns = [field.name for field in PPU_SAVING_SCHEMA]
table.insert_rows_from_pg(PPUSaving, columns, ppu_savings_transform)
table = client.get_table('prescribing_' + date.strftime('%Y_%m'))
sql = '''SELECT * FROM {hscic}.prescribing
WHERE month = TIMESTAMP('{date}')'''
substitutions = {'date': date}
table.insert_rows_from_query(sql, substitutions=substitutions)
def run_end_to_end():
print('BQ_NONCE: {}'.format(settings.BQ_NONCE))
call_command('migrate')
path = os.path.join(settings.APPS_ROOT, 'frontend', 'management',
'commands', 'measure_definitions')
# No MeasureGlobals or MeasureValues are generated for the ghost branded
# generics measure, because both numerator and denominator are computed
# from a view (vw__ghost_generic_measure) which has no data. Rather than
# populate this view, it is simpler to pretend it doesn't exist.
num_measures = len(os.listdir(path)) - 1
shutil.rmtree(settings.PIPELINE_DATA_BASEDIR, ignore_errors=True)
with open(settings.PIPELINE_IMPORT_LOG_PATH, 'w') as f:
f.write('{}')
for blob in StorageClient().bucket().list_blobs():
blob.delete()
for dataset_key in DATASETS:
BQClient(dataset_key).create_dataset()
client = BQClient('hscic')
client.create_table('bnf', schemas.BNF_SCHEMA)
client.create_table('ccgs', schemas.CCG_SCHEMA)
client.create_table('ppu_savings', schemas.PPU_SAVING_SCHEMA)
client.create_table(
'practice_statistics',
schemas.PRACTICE_STATISTICS_SCHEMA
)
client.create_table(
'practice_statistics_all_years',
schemas.PRACTICE_STATISTICS_SCHEMA
)
client.create_table('practices', schemas.PRACTICE_SCHEMA)
client.create_table('prescribing', schemas.PRESCRIBING_SCHEMA)
client.create_table('presentation', schemas.PRESENTATION_SCHEMA)
client.create_table('tariff', schemas.TARIFF_SCHEMA)
client.create_table('bdz_adq', schemas.BDZ_ADQ_SCHEMA)
client = BQClient('measures')
# This is enough of a schema to allow the practice_data_all_low_priority
# table to be created, since it references these fields. Once populated by
# import_measures, the tables in the measures dataset will have several
# more fields. But we don't need to specify exactly what they are, as BQ
# will work it out when the data is inserted with insert_rows_from_query.
measures_schema = build_schema(
('month', 'DATE'),
('practice_id', 'STRING'),
('numerator', 'INTEGER'),
('denominator', 'INTEGER'),
)
path = os.path.join(settings.APPS_ROOT, 'frontend', 'management',
'commands', 'measure_definitions', '*.json')
for path in glob.glob(path):
measure_id = os.path.splitext(os.path.basename(path))[0]
client.create_table('practice_data_' + measure_id, measures_schema)
client.create_table('ccg_data_' + measure_id, measures_schema)
client.create_table('global_data_' + measure_id, measures_schema)
# Although there are no model instances, we call upload_model to create the
# tables in BQ that might be required by certain measure views.
client = BQClient('dmd')
client.upload_model(TariffPrice)
client.upload_model(VMPP)
call_command('generate_presentation_replacements')
path = os.path.join(settings.APPS_ROOT, 'frontend', 'management',
'commands', 'replace_matviews.sql')
with open(path) as f:
with connection.cursor() as c:
c.execute(f.read())
copy_tree(
os.path.join(e2e_path, 'data-1'),
os.path.join(e2e_path, 'data'),
)
runner.run_all(2017, 9, under_test=True)
# We expect one MeasureGlobal per measure per month.
assert_count_equal(num_measures, MeasureGlobal)
# We expect one MeasureValue for each organisation per measure per month
# (There are 4 practices, 2 CCGs, 2 STPs, and 2 regional teams).
assert_count_equal(10 * num_measures, MeasureValue)
# We expect one statistic per CCG per month
assert_raw_count_equal(2, 'vw__ccgstatistics')
# We expect one chemical summary per CCG per month
assert_raw_count_equal(2, 'vw__chemical_summary_by_ccg',
"chemical_id = '1001030C0'")
# We expect one chemical summary per practice per month
assert_raw_count_equal(4, 'vw__chemical_summary_by_practice',
"chemical_id = '1001030C0'")
# We expect one summary per practice per month
assert_raw_count_equal(4, 'vw__practice_summary')
# We expect one presentation summary per month
assert_raw_count_equal(1, 'vw__presentation_summary',
"presentation_code = '1001030C0AAAAAA'")
# We expect one presentation summary per CCG per month
assert_raw_count_equal(2, 'vw__presentation_summary_by_ccg',
"presentation_code = '1001030C0AAAAAA'")
copy_tree(
os.path.join(e2e_path, 'data-2'),
os.path.join(e2e_path, 'data'),
)
runner.run_all(2017, 10, under_test=True)
# We expect one MeasureGlobal per measure per month
assert_count_equal(2 * num_measures, MeasureGlobal)
# We expect one MeasureValue for each organisation per measure per month
assert_count_equal(20 * num_measures, MeasureValue)
# We expect one statistic per CCG per month
assert_raw_count_equal(4, 'vw__ccgstatistics')
# We expect one chemical summary per CCG per month
assert_raw_count_equal(4, 'vw__chemical_summary_by_ccg',
"chemical_id = '1001030C0'")
# We expect one chemical summary per practice per month
assert_raw_count_equal(8, 'vw__chemical_summary_by_practice',
"chemical_id = '1001030C0'")
# We expect one summary per practice per month
assert_raw_count_equal(8, 'vw__practice_summary')
# We expect one presentation summary per month
assert_raw_count_equal(2, 'vw__presentation_summary',
"presentation_code = '1001030C0AAAAAA'")
# We expect one presentation summary per CCG per month
assert_raw_count_equal(4, 'vw__presentation_summary_by_ccg',
"presentation_code = '1001030C0AAAAAA'")
def run_end_to_end():
print("BQ_NONCE: {}".format(settings.BQ_NONCE))
call_command("migrate")
# No MeasureGlobals or MeasureValues are generated for the ghost branded
# generics measure, because both numerator and denominator are computed
# from a view (vw__ghost_generic_measure) which has no data. Rather than
# populate this view, it is simpler to pretend it doesn't exist.
num_measures = (len(
glob.glob(os.path.join(settings.MEASURE_DEFINITIONS_PATH, "*.json"))) -
1)
shutil.rmtree(settings.PIPELINE_DATA_BASEDIR, ignore_errors=True)
with open(settings.PIPELINE_IMPORT_LOG_PATH, "w") as f:
f.write("{}")
for blob in StorageClient().bucket().list_blobs():
blob.delete()
for dataset_key in DATASETS:
BQClient(dataset_key).create_dataset()
client = BQClient("hscic")
client.create_table("bnf", schemas.BNF_SCHEMA)
client.create_table("ccgs", schemas.CCG_SCHEMA)
client.create_table("stps", schemas.STP_SCHEMA)
client.create_table("regional_teams", schemas.REGIONAL_TEAM_SCHEMA)
client.create_table("ppu_savings", schemas.PPU_SAVING_SCHEMA)
client.create_table("practice_statistics",
schemas.PRACTICE_STATISTICS_SCHEMA)
client.create_table("practice_statistics_all_years",
schemas.PRACTICE_STATISTICS_SCHEMA)
client.create_table("practices", schemas.PRACTICE_SCHEMA)
client.create_table("prescribing", schemas.PRESCRIBING_SCHEMA)
client.create_table("presentation", schemas.PRESENTATION_SCHEMA)
client.create_table("tariff", schemas.TARIFF_SCHEMA)
client.create_table("bdz_adq", schemas.BDZ_ADQ_SCHEMA)
client = BQClient("measures")
# This is enough of a schema to allow the practice_data_all_low_priority
# table to be created, since it references these fields. Once populated by
# import_measures, the tables in the measures dataset will have several
# more fields. But we don't need to specify exactly what they are, as BQ
# will work it out when the data is inserted with insert_rows_from_query.
measures_schema = build_schema(
("month", "DATE"),
("practice_id", "STRING"),
("numerator", "INTEGER"),
("denominator", "INTEGER"),
)
for path in glob.glob(
os.path.join(settings.MEASURE_DEFINITIONS_PATH, "*.json")):
measure_id = os.path.splitext(os.path.basename(path))[0]
client.create_table("practice_data_" + measure_id, measures_schema)
client.create_table("ccg_data_" + measure_id, measures_schema)
client.create_table("global_data_" + measure_id, measures_schema)
# Although there are no model instances, we call upload_model to create the
# dm+d tables in BQ that are required by certain measure views.
client = BQClient("dmd")
for model in apps.get_app_config("dmd2").get_models():
client.upload_model(model)
call_command("generate_presentation_replacements")
copy_tree(os.path.join(e2e_path, "data-1"), os.path.join(e2e_path, "data"))
runner.run_all(2017, 9, under_test=True)
# We expect one MeasureGlobal per measure per month.
assert_count_equal(num_measures, MeasureGlobal)
# We expect one MeasureValue for each organisation per measure per month
# (There are 4 practices, 2 CCGs, 2 STPs, and 2 regional teams).
assert_count_equal(10 * num_measures, MeasureValue)
copy_tree(os.path.join(e2e_path, "data-2"), os.path.join(e2e_path, "data"))
runner.run_all(2017, 10, under_test=True)
# We expect one MeasureGlobal per measure per month
assert_count_equal(2 * num_measures, MeasureGlobal)
# We expect one MeasureValue for each organisation per measure per month
assert_count_equal(20 * num_measures, MeasureValue)
def run_end_to_end():
print('BQ_NONCE: {}'.format(settings.BQ_NONCE))
num_measures = 56
shutil.rmtree(settings.PIPELINE_DATA_BASEDIR, ignore_errors=True)
with open(settings.PIPELINE_IMPORT_LOG_PATH, 'w') as f:
f.write('{}')
for blob in StorageClient().bucket().list_blobs():
blob.delete()
for dataset_key in DATASETS:
BQClient(dataset_key).create_dataset()
client = BQClient('hscic')
client.create_table('bnf', schemas.BNF_SCHEMA)
client.create_table('ccgs', schemas.CCG_SCHEMA)
client.create_table('ppu_savings', schemas.PPU_SAVING_SCHEMA)
client.create_table(
'practice_statistics',
schemas.PRACTICE_STATISTICS_SCHEMA
)
client.create_table(
'practice_statistics_all_years',
schemas.PRACTICE_STATISTICS_SCHEMA
)
client.create_table('practices', schemas.PRACTICE_SCHEMA)
client.create_table('prescribing', schemas.PRESCRIBING_SCHEMA)
client.create_table('presentation', schemas.PRESENTATION_SCHEMA)
client.create_table('tariff', schemas.TARIFF_SCHEMA)
call_command('generate_presentation_replacements')
path = os.path.join(settings.SITE_ROOT, 'frontend', 'management',
'commands', 'replace_matviews.sql')
with open(path) as f:
with connection.cursor() as c:
c.execute(f.read())
copy_tree(
os.path.join(e2e_path, 'data-1'),
os.path.join(e2e_path, 'data'),
)
runner.run_all(2017, 9, under_test=True)
# We expect one MeasureGlobal per measure per month. If this assert fails,
# check that num_measures is still correct.
assert_count_equal(num_measures, MeasureGlobal)
# We expect one MeasureValue for each CCG or Practice per measure per month
assert_count_equal(6 * num_measures, MeasureValue)
# We expect one statistic per CCG per month
assert_raw_count_equal(2, 'vw__ccgstatistics')
# We expect one chemical summary per CCG per month
assert_raw_count_equal(2, 'vw__chemical_summary_by_ccg',
"chemical_id = '1001030C0'")
# We expect one chemical summary per practice per month
assert_raw_count_equal(4, 'vw__chemical_summary_by_practice',
"chemical_id = '1001030C0'")
# We expect one summary per practice per month
assert_raw_count_equal(4, 'vw__practice_summary')
# We expect one presentation summary per month
assert_raw_count_equal(1, 'vw__presentation_summary',
"presentation_code = '1001030C0AAAAAA'")
# We expect one presentation summary per CCG per month
assert_raw_count_equal(2, 'vw__presentation_summary_by_ccg',
"presentation_code = '1001030C0AAAAAA'")
copy_tree(
os.path.join(e2e_path, 'data-2'),
os.path.join(e2e_path, 'data'),
)
runner.run_all(2017, 10, under_test=True)
# We expect one MeasureGlobal per measure per month
assert_count_equal(2 * num_measures, MeasureGlobal)
# We expect one MeasureValue for each CCG or Practice per measure per month
assert_count_equal(12 * num_measures, MeasureValue)
# We expect one statistic per CCG per month
assert_raw_count_equal(4, 'vw__ccgstatistics')
# We expect one chemical summary per CCG per month
assert_raw_count_equal(4, 'vw__chemical_summary_by_ccg',
"chemical_id = '1001030C0'")
# We expect one chemical summary per practice per month
assert_raw_count_equal(8, 'vw__chemical_summary_by_practice',
"chemical_id = '1001030C0'")
# We expect one summary per practice per month
assert_raw_count_equal(8, 'vw__practice_summary')
# We expect one presentation summary per month
assert_raw_count_equal(2, 'vw__presentation_summary',
"presentation_code = '1001030C0AAAAAA'")
# We expect one presentation summary per CCG per month
assert_raw_count_equal(4, 'vw__presentation_summary_by_ccg',
"presentation_code = '1001030C0AAAAAA'")
def run_end_to_end():
print('BQ_NONCE: {}'.format(settings.BQ_NONCE))
num_measures = 57
shutil.rmtree(settings.PIPELINE_DATA_BASEDIR, ignore_errors=True)
with open(settings.PIPELINE_IMPORT_LOG_PATH, 'w') as f:
f.write('{}')
for blob in StorageClient().bucket().list_blobs():
blob.delete()
for dataset_key in DATASETS:
BQClient(dataset_key).create_dataset()
client = BQClient('hscic')
client.create_table('bnf', schemas.BNF_SCHEMA)
client.create_table('ccgs', schemas.CCG_SCHEMA)
client.create_table('ppu_savings', schemas.PPU_SAVING_SCHEMA)
client.create_table('practice_statistics',
schemas.PRACTICE_STATISTICS_SCHEMA)
client.create_table('practice_statistics_all_years',
schemas.PRACTICE_STATISTICS_SCHEMA)
client.create_table('practices', schemas.PRACTICE_SCHEMA)
client.create_table('prescribing', schemas.PRESCRIBING_SCHEMA)
client.create_table('presentation', schemas.PRESENTATION_SCHEMA)
client.create_table('tariff', schemas.TARIFF_SCHEMA)
client = BQClient('measures')
# This is enough of a schema to allow the practice_data_all_low_priority
# table to be created, since it references these fields. Once populated by
# import_measures, the tables in the measures dataset will have several
# more fields. But we don't need to specify exactly what they are, as BQ
# will work it out when the data is inserted with insert_rows_from_query.
measures_schema = build_schema(
('month', 'DATE'),
('practice_id', 'STRING'),
('numerator', 'INTEGER'),
('denominator', 'INTEGER'),
)
path = os.path.join(settings.SITE_ROOT, 'frontend', 'management',
'commands', 'measure_definitions', '*.json')
for path in glob.glob(path):
measure_id = os.path.splitext(os.path.basename(path))[0]
client.create_table('practice_data_' + measure_id, measures_schema)
client.create_table('ccg_data_' + measure_id, measures_schema)
client.create_table('global_data_' + measure_id, measures_schema)
call_command('generate_presentation_replacements')
path = os.path.join(settings.SITE_ROOT, 'frontend', 'management',
'commands', 'replace_matviews.sql')
with open(path) as f:
with connection.cursor() as c:
c.execute(f.read())
copy_tree(
os.path.join(e2e_path, 'data-1'),
os.path.join(e2e_path, 'data'),
)
runner.run_all(2017, 9, under_test=True)
# We expect one MeasureGlobal per measure per month. If this assert fails,
# check that num_measures is still correct.
assert_count_equal(num_measures, MeasureGlobal)
# We expect one MeasureValue for each CCG or Practice per measure per month
assert_count_equal(6 * num_measures, MeasureValue)
# We expect one statistic per CCG per month
assert_raw_count_equal(2, 'vw__ccgstatistics')
# We expect one chemical summary per CCG per month
assert_raw_count_equal(2, 'vw__chemical_summary_by_ccg',
"chemical_id = '1001030C0'")
# We expect one chemical summary per practice per month
assert_raw_count_equal(4, 'vw__chemical_summary_by_practice',
"chemical_id = '1001030C0'")
# We expect one summary per practice per month
assert_raw_count_equal(4, 'vw__practice_summary')
# We expect one presentation summary per month
assert_raw_count_equal(1, 'vw__presentation_summary',
"presentation_code = '1001030C0AAAAAA'")
# We expect one presentation summary per CCG per month
assert_raw_count_equal(2, 'vw__presentation_summary_by_ccg',
"presentation_code = '1001030C0AAAAAA'")
copy_tree(
os.path.join(e2e_path, 'data-2'),
os.path.join(e2e_path, 'data'),
)
runner.run_all(2017, 10, under_test=True)
# We expect one MeasureGlobal per measure per month
assert_count_equal(2 * num_measures, MeasureGlobal)
# We expect one MeasureValue for each CCG or Practice per measure per month
assert_count_equal(12 * num_measures, MeasureValue)
# We expect one statistic per CCG per month
assert_raw_count_equal(4, 'vw__ccgstatistics')
# We expect one chemical summary per CCG per month
assert_raw_count_equal(4, 'vw__chemical_summary_by_ccg',
"chemical_id = '1001030C0'")
# We expect one chemical summary per practice per month
assert_raw_count_equal(8, 'vw__chemical_summary_by_practice',
"chemical_id = '1001030C0'")
# We expect one summary per practice per month
assert_raw_count_equal(8, 'vw__practice_summary')
# We expect one presentation summary per month
assert_raw_count_equal(2, 'vw__presentation_summary',
"presentation_code = '1001030C0AAAAAA'")
# We expect one presentation summary per CCG per month
assert_raw_count_equal(4, 'vw__presentation_summary_by_ccg',
"presentation_code = '1001030C0AAAAAA'")
def setUp(self):
client = BQClient('hscic')
table = client.get_or_create_table('prescribing', PRESCRIBING_SCHEMA)