def run(self):
app.logger.info('Starting Canvas schema creation job...')
canvas_path = get_s3_canvas_daily_path()
if not s3.get_keys_with_prefix(canvas_path):
canvas_path = get_s3_canvas_daily_path(datetime.now() -
timedelta(days=1))
if not s3.get_keys_with_prefix(canvas_path):
raise BackgroundJobError(
'No timely Canvas data found, aborting')
else:
app.logger.info('Falling back to yesterday\'s Canvas data')
external_schema = app.config['REDSHIFT_SCHEMA_CANVAS']
s3_prefix = 's3://' + app.config['LOCH_S3_BUCKET'] + '/'
s3_canvas_data_url = s3_prefix + canvas_path
s3_canvas_data_path_current_term = s3_prefix + berkeley.s3_canvas_data_path_current_term(
)
redshift.drop_external_schema(external_schema)
resolved_ddl = resolve_sql_template(
'create_canvas_schema.template.sql',
loch_s3_canvas_data_path_today=s3_canvas_data_url,
loch_s3_canvas_data_path_current_term=
s3_canvas_data_path_current_term,
)
if redshift.execute_ddl_script(resolved_ddl):
verify_external_schema(external_schema, resolved_ddl)
return 'Canvas schema creation job completed.'
else:
raise BackgroundJobError('Canvas schema creation job failed.')
def test_auto_terms(self, app, current_term_index):
all_term_ids = set(berkeley.reverse_term_ids(include_future_terms=True, include_legacy_terms=True))
canvas_integrated_term_ids = set(berkeley.reverse_term_ids())
assert canvas_integrated_term_ids < all_term_ids
assert berkeley.current_term_id() == '2182'
assert berkeley.future_term_id() == '2188'
assert berkeley.s3_canvas_data_path_current_term() == 'canvas-data/term/spring-2018'
def generate_external_catalog(self, external_schema, schema_df):
canvas_path = self.generate_canvas_path()
canvas_tables = schema_df.table_name.unique()
s3_canvas_data_url = 's3://' + app.config[
'LOCH_S3_BUCKET'] + '/' + canvas_path
s3_requests_url = 's3://{}/{}'.format(
app.config['LOCH_S3_BUCKET'],
berkeley.s3_canvas_data_path_current_term())
external_table_ddl = ''
for table in canvas_tables:
table_columns = schema_df.loc[schema_df['table_name'] ==
table].reset_index()
storage_descriptor_df = table_columns[[
'transformed_column_name', 'glue_type'
]]
create_ddl = 'CREATE EXTERNAL TABLE {}.{}\n(\n'.format(
external_schema, table)
storage_descriptors = ''
for index in storage_descriptor_df.index:
storage_descriptors = '{} {} {}'.format(
storage_descriptors,
storage_descriptor_df['transformed_column_name'][index],
storage_descriptor_df['glue_type'][index],
)
if (index != (len(storage_descriptor_df.index) - 1)):
storage_descriptors = storage_descriptors + ',\n'
table_properties = '\n) \nROW FORMAT DELIMITED FIELDS \nTERMINATED BY \'\t\' \nSTORED AS TEXTFILE'
if (table != 'requests'):
table_location = '\nLOCATION \'{}/{}\''.format(
s3_canvas_data_url, table)
else:
table_location = '\nLOCATION \'{}/{}\''.format(
s3_requests_url, table)
external_table_ddl = '{}\n{}{}{}{};\n\n'.format(
external_table_ddl,
create_ddl,
storage_descriptors,
table_properties,
table_location,
)
# For debugging process, export to external_table_ddl to file to get a well formed SQL template for canvas-data
return external_table_ddl
def run(self, cleanup=True):
job_id = self.generate_job_id()
app.logger.info(f'Starting Canvas snapshot resync job... (id={job_id})')
md = metadata.get_failures_from_last_sync()
if not md['failures']:
return f"No failures found for job_id {md['job_id']}, skipping resync."
app.logger.info(f"Found {len(md['failures'])} failures for job_id {md['job_id']}, attempting resync.")
failures = 0
successes = 0
for failure in md['failures']:
if cleanup and failure['destination_url']:
destination_key = failure['destination_url'].split(app.config['LOCH_S3_BUCKET'] + '/')[1]
if s3.delete_objects([destination_key]):
metadata.delete_canvas_snapshots([destination_key])
else:
app.logger.error(f'Could not delete failed snapshot from S3 (url={failure.destination_url})')
metadata.create_canvas_sync_status(
job_id=job_id,
filename=failure['filename'],
canvas_table=failure['canvas_table'],
# The original signed source URL will remain valid if the resync job is run within an hour of the sync job.
# TODO Add logic to fetch a new signed URL from the Canvas Data API for older jobs.
source_url=failure['source_url'],
)
# Regenerate the S3 key, since the failed job may not have progressed far enough to store a destination URL in its metadata.
if failure['canvas_table'] == 'requests':
key_components = [berkeley.s3_canvas_data_path_current_term(), failure['canvas_table'], failure['filename']]
else:
key_components = [get_s3_canvas_daily_path(), failure['canvas_table'], failure['filename']]
key = '/'.join(key_components)
response = dispatch('sync_file_to_s3', data={'canvas_sync_job_id': job_id, 'url': failure['source_url'], 'key': key})
if not response:
app.logger.error('Failed to dispatch S3 resync of snapshot ' + failure['filename'])
metadata.update_canvas_sync_status(job_id, key, 'error', details=f'Failed to dispatch: {response}')
failures += 1
else:
app.logger.info('Dispatched S3 resync of snapshot ' + failure['filename'])
successes += 1
return f'Canvas snapshot resync job dispatched to workers ({successes} successful dispatches, {failures} failures).'
def run(self, cleanup=True):
job_id = self.generate_job_id()
app.logger.info(f'Starting Canvas snapshot sync job... (id={job_id})')
snapshot_response = canvas_data.get_snapshots()
if not snapshot_response:
raise BackgroundJobError(
'Error retrieving Canvas data snapshots, aborting job.')
snapshots = snapshot_response.get('files', [])
def should_sync(snapshot):
# For tables other than requests, sync all snapshots.
# For the requests table, sync snapshots that are partial or later than the configured cutoff date.
def after_cutoff_date(url):
match = re.search('requests/(20\d{6})', url)
return match is not None and (
match[1] >=
app.config['LOCH_CANVAS_DATA_REQUESTS_CUTOFF_DATE'])
return snapshot['table'] != 'requests' or snapshot[
'partial'] is True or after_cutoff_date(snapshot['url'])
snapshots_to_sync = [s for s in snapshots if should_sync(s)]
app.logger.info(
f'Will sync {len(snapshots_to_sync)} of {len(snapshots)} available files from Canvas Data.'
)
success = 0
failure = 0
for snapshot in snapshots_to_sync:
metadata.create_canvas_sync_status(
job_id=job_id,
filename=snapshot['filename'],
canvas_table=snapshot['table'],
source_url=snapshot['url'],
)
if snapshot['table'] == 'requests':
key_components = [
berkeley.s3_canvas_data_path_current_term(),
snapshot['table'], snapshot['filename']
]
else:
key_components = [
get_s3_canvas_daily_path(), snapshot['table'],
snapshot['filename']
]
key = '/'.join(key_components)
response = dispatch('sync_file_to_s3',
data={
'canvas_sync_job_id': job_id,
'url': snapshot['url'],
'key': key
})
if not response:
app.logger.error('Failed to dispatch S3 sync of snapshot ' +
snapshot['filename'])
metadata.update_canvas_sync_status(
job_id,
key,
'error',
details=f'Failed to dispatch: {response}')
failure += 1
else:
app.logger.info('Dispatched S3 sync of snapshot ' +
snapshot['filename'])
success += 1
if cleanup:
app.logger.info('Will remove obsolete snapshots from S3.')
current_snapshot_filenames = [
s['filename'] for s in snapshots_to_sync
]
requests_prefix = berkeley.s3_canvas_data_path_current_term(
) + '/requests'
delete_result = s3.delete_objects_with_prefix(
requests_prefix, whitelist=current_snapshot_filenames)
if not delete_result:
app.logger.error('Cleanup of obsolete snapshots failed.')
return f'Canvas snapshot sync job dispatched to workers ({success} successful dispatches, {failure} failures).'