def conditional_constraint_and_index_reconstructor(options):
if 'measure' in options and options['measure']:
# This is an optimisation that only makes sense when we're
# updating the entire table.
yield
else:
yield utils.constraint_and_index_reconstructor('frontend_measurevalue')
def conditional_constraint_and_index_reconstructor(options):
if 'measure' in options and options['measure']:
# This is an optimisation that only makes sense when we're
# updating the entire table.
yield
else:
yield utils.constraint_and_index_reconstructor('frontend_measurevalue')
def test_reconstructor_does_work(self):
from django.db import connection
from common.utils import constraint_and_index_reconstructor
with connection.cursor() as cursor:
# Set up a table
cursor.execute("CREATE TABLE firmness (id integer PRIMARY KEY)")
cursor.execute("""
CREATE TABLE tofu (
id integer PRIMARY KEY,
brand varchar,
firmness_id integer REFERENCES firmness (id))
""")
cursor.execute("CLUSTER tofu USING tofu_pkey")
cursor.execute("CREATE INDEX ON tofu (brand)")
with constraint_and_index_reconstructor('tofu'):
cursor.execute(
"SELECT count(*) FROM pg_indexes WHERE tablename = 'tofu'"
)
self.assertEqual(cursor.fetchone()[0], 0)
cursor.execute(
"SELECT count(*) FROM pg_indexes WHERE tablename = 'tofu'"
)
self.assertEqual(cursor.fetchone()[0], 2)
self.assertEqual(_cluster_count(cursor), 1)
def test_reconstructor_works_even_when_exception_thrown(self):
with connection.cursor() as cursor:
# Set up a table
cursor.execute("CREATE TABLE firmness (id integer PRIMARY KEY)")
cursor.execute("""
CREATE TABLE tofu (
id integer PRIMARY KEY,
brand varchar,
firmness_id integer REFERENCES firmness (id))
""")
cursor.execute("CLUSTER tofu USING tofu_pkey")
cursor.execute("CREATE INDEX ON tofu (brand)")
class BadThingError(Exception):
pass
with self.assertRaises(BadThingError):
with constraint_and_index_reconstructor("tofu"):
raise BadThingError(
"3.6 roentgen; not great, not terrible")
cursor.execute(
"SELECT count(*) FROM pg_indexes WHERE tablename = 'tofu'")
self.assertEqual(cursor.fetchone()[0], 2)
self.assertEqual(_cluster_count(cursor), 1)
def conditional_constraint_and_index_reconstructor(options):
if options["measure"]:
# This is an optimisation that only makes sense when we're
# updating the entire table.
yield
else:
with utils.constraint_and_index_reconstructor("frontend_measurevalue"):
yield
def test_reconstructor_does_work(self):
from django.db import connection
from common.utils import constraint_and_index_reconstructor
start_count = Measure.objects.count()
with connection.cursor() as cursor:
cursor.execute("SELECT COUNT(*) FROM pg_indexes")
old_count = cursor.fetchone()[0]
with constraint_and_index_reconstructor('frontend_measurevalue'):
Measure.objects.all().delete()
cursor.execute("SELECT COUNT(*) FROM pg_indexes")
new_count = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM pg_indexes")
after_count = cursor.fetchone()[0]
self.assertLess(Measure.objects.count(), start_count)
self.assertLess(new_count, old_count)
self.assertEqual(old_count, after_count)
def fill_views(self):
paths = []
if self.view:
for path in self.view_paths:
if self.view in path:
paths.append(path)
break
else:
paths = self.view_paths
pool = Pool(processes=len(paths))
pool_results = []
prescribing_date = ImportLog.objects.latest_in_category(
'prescribing').current_at.strftime('%Y-%m-%d')
for view in paths:
if self.view and self.view not in view:
continue
# Perform bigquery parts of operation in parallel
result = pool.apply_async(
query_and_export, [self.dataset, view, prescribing_date])
pool_results.append(result)
pool.close()
pool.join() # wait for all worker processes to exit
for result in pool_results:
tablename, gcs_uri = result.get()
f = download_and_unzip(gcs_uri)
copy_str = "COPY %s(%s) FROM STDIN "
copy_str += "WITH (FORMAT CSV)"
fieldnames = f.readline().split(',')
with connection.cursor() as cursor:
with utils.constraint_and_index_reconstructor(tablename):
self.log("Deleting from table...")
cursor.execute("DELETE FROM %s" % tablename)
self.log("Copying CSV to postgres...")
try:
cursor.copy_expert(copy_str % (
tablename, ','.join(fieldnames)), f)
except Exception:
import shutil
shutil.copyfile(f.name, "/tmp/error")
raise
f.close()
self.log("-------------")
def fill_views(self):
paths = []
if self.view:
for path in self.view_paths:
if self.view in path:
paths.append(path)
break
else:
paths = self.view_paths
pool = Pool(processes=len(paths))
pool_results = []
prescribing_date = ImportLog.objects.latest_in_category(
'prescribing').current_at.strftime('%Y-%m-%d')
for view in paths:
if self.view and self.view not in view:
continue
# Perform bigquery parts of operation in parallel
result = pool.apply_async(
query_and_export, [self.dataset, view, prescribing_date])
pool_results.append(result)
pool.close()
pool.join() # wait for all worker processes to exit
for result in pool_results:
tablename, gcs_uri = result.get()
f = download_and_unzip(gcs_uri)
copy_str = "COPY %s(%s) FROM STDIN "
copy_str += "WITH (FORMAT CSV)"
fieldnames = f.readline().split(',')
with connection.cursor() as cursor:
with utils.constraint_and_index_reconstructor(tablename):
self.log("Deleting from table...")
cursor.execute("DELETE FROM %s" % tablename)
self.log("Copying CSV to postgres...")
try:
cursor.copy_expert(copy_str % (
tablename, ','.join(fieldnames)), f)
except Exception:
import shutil
shutil.copyfile(f.name, "/tmp/error")
raise
f.close()
self.log("-------------")
def download_and_import(self, table):
'''Download table from storage and import into local database.
We sort the downloaded file with `sort` rather than in BigQuery,
because we hit resource limits when we try to do so. See #698 and #711
for discussion.
'''
table_id = table.table_id
storage_prefix = 'hscic/views/{}-'.format(table_id)
exporter = TableExporter(table, storage_prefix)
raw_file = tempfile.NamedTemporaryFile()
raw_path = raw_file.name
sorted_file = tempfile.NamedTemporaryFile()
sorted_path = sorted_file.name
self.log('Downloading {} to {}'.format(table_id, raw_path))
exporter.download_from_storage_and_unzip(raw_file)
self.log('Sorting {} to {}'.format(table_id, sorted_path))
cmd = 'head -1 {} > {}'.format(raw_path, sorted_path)
subprocess.check_call(cmd, shell=True)
field_names = sorted_file.readline().strip().split(',')
cmd = generate_sort_cmd(table_id, field_names, raw_path, sorted_path)
subprocess.check_call(cmd, shell=True)
copy_sql = "COPY {}({}) FROM STDIN WITH (FORMAT CSV)".format(
table_id, ','.join(field_names))
with connection.cursor() as cursor:
with utils.constraint_and_index_reconstructor(table_id):
self.log("Deleting from table %s..." % table_id)
cursor.execute("DELETE FROM %s" % table_id)
self.log("Copying CSV to %s..." % table_id)
cursor.copy_expert(copy_sql, sorted_file)
raw_file.close()
sorted_file.close()
def conditional_constraint_and_index_reconstructor(enabled):
if not enabled:
yield
else:
with utils.constraint_and_index_reconstructor("frontend_measurevalue"):
yield