def handle(self, *args, **kwargs):
base_path = os.path.join(
settings.APPS_ROOT, "frontend", "management", "commands", "measure_sql"
)
client = Client("measures")
for table_name in [
"dmd_objs_with_form_route",
"opioid_total_ome",
"practice_data_all_low_priority",
"pregabalin_total_mg",
"vw__median_price_per_unit",
"vw__ghost_generic_measure",
# This references pregabalin_total_mg, so must come afterwards
"gaba_total_ddd",
]:
path = os.path.join(base_path, table_name + ".sql")
with open(path, "r") as sql_file:
sql = sql_file.read()
try:
client.create_table_with_view(table_name, sql, False)
except Conflict:
client.delete_table(table_name)
client.create_table_with_view(table_name, sql, False)
# cmpa_products is a table that has been created and managed by Rich.
schema = build_schema(
("bnf_code", "STRING"), ("bnf_name", "STRING"), ("type", "STRING")
)
client.get_or_create_table("cmpa_products", schema)
def handle(self, *args, **kwargs):
base_path = os.path.join(settings.APPS_ROOT, 'frontend', 'management',
'commands', 'measure_sql')
client = Client("measures")
for table_name in [
'opioid_total_ome',
'practice_data_all_low_priority',
'pregabalin_total_mg',
'vw__median_price_per_unit',
'vw__ghost_generic_measure',
]:
path = os.path.join(base_path, table_name + '.sql')
with open(path, "r") as sql_file:
sql = sql_file.read()
try:
client.create_table_with_view(table_name, sql, False)
except Conflict:
client.delete_table(table_name)
client.create_table_with_view(table_name, sql, False)
# cmpa_products is a table that has been created and managed by Rich.
schema = build_schema(
('bnf_code', 'STRING'),
('bnf_name', 'STRING'),
('type', 'STRING'),
)
client.get_or_create_table('cmpa_products', schema)
def create_bigquery_views():
"""Create BigQuery views on the main prescribing data which map
historic BNF codes to their current equivalent.
If they already exist, do nothing.
"""
# We have to create legacy and standard versions of the view, as a
# legacy query cannot address a standard view, and vice versa, and
# we use both flavours in our code.
sql = """
SELECT
prescribing.sha AS sha,
practices.ccg_id AS pct,
prescribing.practice AS practice,
COALESCE(bnf_map.current_bnf_code, prescribing.bnf_code)
AS bnf_code,
prescribing.bnf_name AS bnf_name,
prescribing.items AS items,
prescribing.net_cost AS net_cost,
prescribing.actual_cost AS actual_cost,
prescribing.quantity AS quantity,
prescribing.month AS month
FROM
{project}.{hscic}.prescribing AS prescribing
LEFT JOIN
{project}.{hscic}.bnf_map AS bnf_map
ON
bnf_map.former_bnf_code = prescribing.bnf_code
INNER JOIN
{project}.{hscic}.practices AS practices
ON practices.code = prescribing.practice
"""
client = Client('hscic')
try:
client.create_table_with_view(
'normalised_prescribing_standard',
sql,
False
)
except Conflict:
pass
sql = sql.replace('{project}.', '{project}:')
try:
client.create_table_with_view(
'normalised_prescribing_legacy',
sql,
legacy=True
)
except Conflict:
pass
def create_pregabalin_total_mg_view():
fpath = os.path.dirname(__file__)
sql_path = os.path.join(fpath, "./measure_sql/pregabalin_total_mg.sql")
with open(sql_path, "r") as sql_file:
sql = sql_file.read()
client = Client("measures")
try:
client.create_table_with_view('pregabalin_total_mg', sql, False)
except Conflict:
pass
def create_omnibus_lp_view():
fpath = os.path.dirname(__file__)
sql_path = os.path.join(fpath, "./measure_sql/lpomnibusview.sql")
with open(sql_path, "r") as sql_file:
sql = sql_file.read()
client = Client("measures")
try:
client.create_table_with_view('practice_data_all_low_priority', sql,
False)
except Conflict:
pass
def handle(self, *args, **kwargs):
sql = """
SELECT
prescribing.sha AS sha,
ccgs.regional_team_id AS regional_team,
ccgs.stp_id AS stp,
practices.ccg_id AS pct,
prescribing.practice AS practice,
COALESCE(bnf_map.current_bnf_code, prescribing.bnf_code)
AS bnf_code,
prescribing.bnf_name AS bnf_name,
prescribing.items AS items,
prescribing.net_cost AS net_cost,
prescribing.actual_cost AS actual_cost,
prescribing.quantity AS quantity,
prescribing.month AS month
FROM
{project}.{hscic}.prescribing AS prescribing
LEFT JOIN
{project}.{hscic}.bnf_map AS bnf_map
ON
bnf_map.former_bnf_code = prescribing.bnf_code
INNER JOIN
{project}.{hscic}.practices AS practices
ON practices.code = prescribing.practice
INNER JOIN
{project}.{hscic}.ccgs AS ccgs
ON practices.ccg_id = ccgs.code
"""
client = Client('hscic')
for table_name, legacy in [
('normalised_prescribing_legacy', True),
('normalised_prescribing_standard', False),
]:
if kwargs['recreate']:
try:
client.delete_table(table_name)
except NotFound:
pass
client.create_table_with_view(table_name, sql, legacy)
def handle(self, *args, **kwargs):
base_path = os.path.join(settings.SITE_ROOT, 'frontend', 'management',
'commands', 'measure_sql')
client = Client("measures")
for table_name in [
'opioid_total_ome',
'practice_data_all_low_priority',
'pregabalin_total_mg',
]:
path = os.path.join(base_path, table_name + '.sql')
with open(path, "r") as sql_file:
sql = sql_file.read()
try:
client.create_table_with_view(table_name, sql, False)
except Conflict:
pass
def setUpClass(cls):
if 'SKIP_BQ_LOAD' not in os.environ:
# Create local test data from fixtures, then upload this to a
# test project in bigquery
call_command('loaddata',
'frontend/tests/fixtures/orgs.json',
verbosity=0)
call_command('loaddata',
'frontend/tests/fixtures/practices.json',
verbosity=0)
call_command('loaddata',
'frontend/tests/fixtures/practice_listsizes.json',
verbosity=0)
client = Client('hscic')
table = client.get_or_create_table('prescribing',
PRESCRIBING_SCHEMA)
prescribing_fixture_path = os.path.join(
'frontend', 'tests', 'fixtures', 'commands',
'prescribing_bigquery_views_fixture.csv')
table.insert_rows_from_csv(prescribing_fixture_path)
table = client.get_or_create_table('ccgs', CCG_SCHEMA)
columns = [field.name for field in CCG_SCHEMA]
table.insert_rows_from_pg(PCT, columns, ccgs_transform)
table = client.get_or_create_table('practices', PRACTICE_SCHEMA)
columns = [field.name for field in PRACTICE_SCHEMA]
table.insert_rows_from_pg(Practice, columns)
table = client.get_or_create_table('practice_statistics',
PRACTICE_STATISTICS_SCHEMA)
columns = [field.name for field in PRACTICE_STATISTICS_SCHEMA]
columns[0] = 'date'
columns[-1] = 'practice_id'
table.insert_rows_from_pg(PracticeStatistics, columns,
statistics_transform)
sql = """
SELECT
prescribing.sha AS sha,
practices.ccg_id AS pct,
prescribing.practice AS practice,
prescribing.bnf_code AS bnf_code,
prescribing.bnf_name AS bnf_name,
prescribing.items AS items,
prescribing.net_cost AS net_cost,
prescribing.actual_cost AS actual_cost,
prescribing.quantity AS quantity,
prescribing.month AS month
FROM
{project}.{hscic}.prescribing AS prescribing
INNER JOIN
{project}.{hscic}.practices AS practices
ON practices.code = prescribing.practice
"""
try:
client.delete_table('normalised_prescribing_standard')
except NotFound:
pass
client.create_table_with_view('normalised_prescribing_standard',
sql, False)
client = StorageClient()
bucket = client.get_bucket()
for blob in bucket.list_blobs(prefix='hscic/views/vw__'):
blob.delete()
else:
assert 'BQ_NONCE' in os.environ, 'Set BQ_NONCE to reuse BQ data'
ImportLog.objects.create(category='prescribing',
current_at='2015-10-01')
# Create view tables and indexes
with open('frontend/management/commands/replace_matviews.sql') as f:
with connection.cursor() as c:
c.execute(f.read())
def test_the_lot(self):
client = Client('test')
schema = build_schema(
('a', 'INTEGER'),
('b', 'STRING'),
)
headers = ['a', 'b']
rows = [
(1, 'apple'),
(2, 'banana'),
(3, 'coconut'),
]
t1 = client.get_or_create_table('t1', schema)
t1_qname = t1.qualified_name
# Test Table.insert_rows_from_csv
t1.insert_rows_from_csv('gcutils/tests/test_table.csv')
self.assertEqual(sorted(t1.get_rows()), rows)
# Test Table.insert_rows_from_query
t2 = client.get_table('t2')
sql = 'SELECT * FROM {} WHERE a > 1'.format(t1_qname)
t2.insert_rows_from_query(sql)
self.assertEqual(sorted(t2.get_rows()), rows[1:])
# Test Client.query
sql = 'SELECT * FROM {} WHERE a > 2'.format(t1_qname)
results = client.query(sql)
self.assertEqual(sorted(results.rows), rows[2:])
# Test Client.query_into_dataframe
sql = 'SELECT * FROM {} WHERE a > 2'.format(t1_qname)
df = client.query_into_dataframe(sql)
self.assertEqual(df.values.tolist(), [list(rows[2])])
# Test TableExporter.export_to_storage and
# TableExporter.download_from_storage_and_unzip
t1_exporter = TableExporter(t1, self.storage_prefix + 'test_table-')
t1_exporter.export_to_storage()
with tempfile.NamedTemporaryFile(mode='r+') as f:
t1_exporter.download_from_storage_and_unzip(f)
f.seek(0)
reader = csv.reader(f)
data = [reader.next()] + sorted(reader)
self.assertEqual(data, [map(str, row) for row in [headers] + rows])
# Test Table.insert_rows_from_storage
storage_path = self.storage_prefix + 'test_table.csv'
self.upload_to_storage('gcutils/tests/test_table.csv', storage_path)
t2.insert_rows_from_storage(storage_path)
self.assertEqual(sorted(t2.get_rows()), rows)
# Test Client.create_storage_backed_table
storage_path = self.storage_prefix + 'test_table_headers.csv'
self.upload_to_storage(
'gcutils/tests/test_table_headers.csv',
storage_path
)
schema = [
{'name': 'a', 'type': 'integer'},
{'name': 'b', 'type': 'string'},
]
t3 = client.create_storage_backed_table(
't3',
schema,
storage_path
)
results = client.query('SELECT * FROM {}'.format(t3.qualified_name))
self.assertEqual(sorted(results.rows), rows)
self.upload_to_storage(
'gcutils/tests/test_table_headers_2.csv',
storage_path
)
results = client.query('SELECT * FROM {}'.format(t3.qualified_name))
self.assertEqual(sorted(results.rows), rows + [(4, u'damson')])
# Test Client.create_table_with_view
sql = 'SELECT * FROM {{project}}.{} WHERE a > 1'.format(t1_qname)
t4 = client.create_table_with_view('t4', sql, False)
results = client.query('SELECT * FROM {}'.format(t4.qualified_name))
self.assertEqual(sorted(results.rows), rows[1:])
# Test Client.insert_rows_from_pg
PCT.objects.create(code='ABC', name='CCG 1')
PCT.objects.create(code='XYZ', name='CCG 2')
def transformer(row):
return [ord(row[0][0]), row[1]]
t1.insert_rows_from_pg(PCT, ['code', 'name'], transformer)
self.assertEqual(sorted(t1.get_rows()), [(65, 'CCG 1'), (88, 'CCG 2')])
# Test Table.delete_all_rows
t1.delete_all_rows()
self.assertEqual(list(t1.get_rows()), [])
def test_the_lot(self):
client = Client("test")
archive_client = Client("archive")
orig_schema = build_schema(("a", "STRING"), ("b", "INTEGER"))
schema = build_schema(("a", "INTEGER"), ("b", "STRING"))
headers = ["a", "b"]
rows = [(1, "apple"), (2, "banana"), (3, "coconut")]
t1 = client.get_or_create_table("t1", orig_schema)
t1_qname = t1.qualified_name
# Test Table.insert_rows_from_csv
t1.insert_rows_from_csv("gcutils/tests/test_table.csv", schema)
self.assertEqual(sorted(t1.get_rows()), rows)
# Test Table.insert_rows_from_query
t2 = client.get_table("t2")
sql = "SELECT * FROM {} WHERE a > 1".format(t1_qname)
t2.insert_rows_from_query(sql)
self.assertEqual(sorted(t2.get_rows()), rows[1:])
# Test Client.query
sql = "SELECT * FROM {} WHERE a > 2".format(t1_qname)
results = client.query(sql)
self.assertEqual(sorted(results.rows), rows[2:])
# Test Client.query_into_dataframe
sql = "SELECT * FROM {} WHERE a > 2".format(t1_qname)
df = client.query_into_dataframe(sql)
self.assertEqual(df.values.tolist(), [list(rows[2])])
# Test TableExporter.export_to_storage and
# TableExporter.download_from_storage_and_unzip
t1_exporter = TableExporter(t1, self.storage_prefix + "test_table-")
t1_exporter.export_to_storage()
with tempfile.NamedTemporaryFile(mode="r+") as f:
t1_exporter.download_from_storage_and_unzip(f)
f.seek(0)
reader = csv.reader(f)
data = [next(reader)] + sorted(reader)
self.assertEqual(data,
[list(map(str, row)) for row in [headers] + rows])
# Test Table.insert_rows_from_storage
storage_path = self.storage_prefix + "test_table.csv"
self.upload_to_storage("gcutils/tests/test_table.csv", storage_path)
t2.insert_rows_from_storage(storage_path)
self.assertEqual(sorted(t2.get_rows()), rows)
# Test Client.create_storage_backed_table
storage_path = self.storage_prefix + "test_table_headers.csv"
self.upload_to_storage("gcutils/tests/test_table_headers.csv",
storage_path)
schema = build_schema(("a", "INTEGER"), ("b", "STRING"))
t3 = client.create_storage_backed_table("t3", schema, storage_path)
results = client.query("SELECT * FROM {}".format(t3.qualified_name))
self.assertEqual(sorted(results.rows), rows)
self.upload_to_storage("gcutils/tests/test_table_headers_2.csv",
storage_path)
results = client.query("SELECT * FROM {}".format(t3.qualified_name))
self.assertEqual(sorted(results.rows), rows + [(4, "damson")])
# Test Client.create_table_with_view
sql = "SELECT * FROM {{project}}.{} WHERE a > 1".format(t1_qname)
t4 = client.create_table_with_view("t4", sql, False)
results = client.query("SELECT * FROM {}".format(t4.qualified_name))
self.assertEqual(sorted(results.rows), rows[1:])
# Test Table.copy_to_new_dataset
t1.copy_to_new_dataset("archive")
t1_archived = archive_client.get_table("t1")
self.assertEqual(sorted(t1_archived.get_rows()), rows)
self.assertEqual(sorted(t1.get_rows()), rows)
# Test Table.move_to_new_dataset
t2.move_to_new_dataset("archive")
t2_archived = archive_client.get_table("t2")
self.assertEqual(sorted(t2_archived.get_rows()), rows)
with self.assertRaises(NotFound):
list(t2.get_rows())
# Test Client.insert_rows_from_pg
PCT.objects.create(code="ABC", name="CCG 1")
PCT.objects.create(code="XYZ", name="CCG 2")
def transformer(row):
return [ord(row[0][0]), row[1]]
t1.insert_rows_from_pg(
PCT,
build_schema(("code", "INTEGER"), ("name", "STRING")),
transformer=transformer,
)
self.assertEqual(sorted(t1.get_rows()), [(65, "CCG 1"), (88, "CCG 2")])
# Test Table.delete_all_rows
t1.delete_all_rows()
self.assertEqual(list(t1.get_rows()), [])