def download_csv(count_sql, copy_sql, filename, job_id, fast, verbose):
if fast:
count = None
printf({
"msg":
"Skipping count checks. Writing file: {}".format(filename),
"job":
job_id,
"f":
"Download"
})
else:
count = execute_sql_statement(count_sql, True, verbose)[0]["count"]
printf({
"msg": "Writing {} to this file: {}".format(count, filename),
"job": job_id,
"f": "Download"
})
# It is preferable to not use shell=True, but this command works. Limited user-input so risk is low
subprocess.Popen('psql "${{DATABASE_URL}}" -c {}'.format(copy_sql),
shell=True).wait()
if not fast:
download_count = count_rows_in_csv_file(filename,
has_header=True,
safe=False)
if count != download_count:
msg = "Mismatch between CSV and DB rows! Expected: {} | Actual {} in: {}"
printf({
"msg": msg.format(count, download_count, filename),
"job": job_id,
"f": "Download"
})
raise SystemExit(1)
return count
def download_csv(count_sql, copy_sql, filename, job_id, verbose):
count = execute_sql_statement(count_sql, True, verbose)[0]['count']
printf({
'msg':
'Writing {} transactions to this file: {}'.format(count, filename),
'job':
job_id,
'f':
'Download'
})
# It is preferable to not use shell=True, but this command works. Limited user-input so risk is low
subprocess.Popen('psql "${{DATABASE_URL}}" -c {}'.format(copy_sql),
shell=True).wait()
download_count = count_rows_in_csv_file(filename,
has_header=True,
safe=False)
if count != download_count:
msg = 'Mismatch between CSV and DB rows! Expected: {} | Actual {} in: {}'
printf({
'msg': msg.format(count, download_count, filename),
'job': job_id,
'f': 'Download'
})
raise SystemExit(1)
return count
def create_local_file(self, award_type, source, agency_code,
generate_since):
""" Generate complete file from SQL query and S3 bucket deletion files, then zip it locally """
logger.info('Generating CSV file with creations and modifications')
# Create file paths and working directory
timestamp = datetime.strftime(datetime.now(), '%Y%m%d%H%M%S%f')
working_dir = '{}_{}_delta_gen_{}/'.format(settings.CSV_LOCAL_PATH,
agency_code, timestamp)
if not os.path.exists(working_dir):
os.mkdir(working_dir)
source_name = '{}_{}_Delta_{}'.format(
agency_code, award_type, datetime.strftime(date.today(), '%Y%m%d'))
source_path = os.path.join(working_dir, '{}.csv'.format(source_name))
# Create a unique temporary file with the raw query
raw_quoted_query = generate_raw_quoted_query(
source.row_emitter(None)) # None requests all headers
csv_query_annotated = self.apply_annotations_to_sql(
raw_quoted_query, source.human_names)
(temp_sql_file,
temp_sql_file_path) = tempfile.mkstemp(prefix='bd_sql_', dir='/tmp')
with open(temp_sql_file_path, 'w') as file:
file.write('\\copy ({}) To STDOUT with CSV HEADER'.format(
csv_query_annotated))
logger.info('Generated temp SQL file {}'.format(temp_sql_file_path))
# Generate the csv with \copy
cat_command = subprocess.Popen(['cat', temp_sql_file_path],
stdout=subprocess.PIPE)
try:
subprocess.check_output([
'psql', '-o', source_path, os.environ['DOWNLOAD_DATABASE_URL'],
'-v', 'ON_ERROR_STOP=1'
],
stdin=cat_command.stdout,
stderr=subprocess.STDOUT)
except subprocess.CalledProcessError as e:
logger.exception(e.output)
raise e
# Append deleted rows to the end of the file
self.add_deletion_records(source_path, working_dir, award_type,
agency_code, source, generate_since)
if count_rows_in_csv_file(source_path, has_header=True, safe=True) > 0:
# Split the CSV into multiple files and zip it up
zipfile_path = '{}{}.zip'.format(settings.CSV_LOCAL_PATH,
source_name)
logger.info('Creating compressed file: {}'.format(
os.path.basename(zipfile_path)))
split_and_zip_csvs(zipfile_path, source_path, source_name)
else:
zipfile_path = None
os.close(temp_sql_file)
os.remove(temp_sql_file_path)
shutil.rmtree(working_dir)
return zipfile_path
def parse_source(source, columns, download_job, working_dir, piid, assistance_id, zip_file_path, limit):
"""Write to csv and zip files using the source data"""
d_map = {
"d1": "contracts",
"d2": "assistance",
"treasury_account": "treasury_account",
"federal_account": "federal_account",
}
if download_job and download_job.monthly_download:
# Use existing detailed filename from parent file for monthly files
# e.g. `019_Assistance_Delta_20180917_%s.csv`
source_name = strip_file_extension(download_job.file_name)
elif source.is_for_idv or source.is_for_contract:
file_name_pattern = VALUE_MAPPINGS[source.source_type]["download_name"]
source_name = file_name_pattern.format(piid=slugify_text_for_file_names(piid, "UNKNOWN", 50))
elif source.is_for_assistance:
file_name_pattern = VALUE_MAPPINGS[source.source_type]["download_name"]
source_name = file_name_pattern.format(assistance_id=slugify_text_for_file_names(assistance_id, "UNKNOWN", 50))
else:
source_name = "{}_{}_{}".format(
source.agency_code, d_map[source.file_type], VALUE_MAPPINGS[source.source_type]["download_name"]
)
source_query = source.row_emitter(columns)
source.file_name = "{}.csv".format(source_name)
source_path = os.path.join(working_dir, source.file_name)
write_to_log(message="Preparing to download data as {}".format(source_name), download_job=download_job)
# Generate the query file; values, limits, dates fixed
temp_file, temp_file_path = generate_temp_query_file(source_query, limit, source, download_job, columns)
start_time = time.perf_counter()
try:
# Create a separate process to run the PSQL command; wait
psql_process = multiprocessing.Process(target=execute_psql, args=(temp_file_path, source_path, download_job))
psql_process.start()
wait_for_process(psql_process, start_time, download_job)
# Log how many rows we have
write_to_log(message="Counting rows in CSV", download_job=download_job)
try:
download_job.number_of_rows += count_rows_in_csv_file(filename=source_path, has_header=True)
except Exception:
write_to_log(message="Unable to obtain CSV line count", is_error=True, download_job=download_job)
download_job.save()
# Create a separate process to split the large csv into smaller csvs and write to zip; wait
zip_process = multiprocessing.Process(
target=split_and_zip_csvs, args=(zip_file_path, source_path, source_name, download_job)
)
zip_process.start()
wait_for_process(zip_process, start_time, download_job)
download_job.save()
except Exception as e:
raise e
finally:
# Remove temporary files
os.close(temp_file)
os.remove(temp_file_path)
def download_csv(count_sql, copy_sql, filename, job_id, verbose):
count = execute_sql_statement(count_sql, True, verbose)[0]['count']
printf({'msg': 'Writing {} transactions to this file: {}'.format(count, filename), 'job': job_id, 'f': 'Download'})
# It is preferable to not use shell=True, but this command works. Limited user-input so risk is low
subprocess.Popen('psql "${{DATABASE_URL}}" -c {}'.format(copy_sql), shell=True).wait()
download_count = count_rows_in_csv_file(filename, has_header=True, safe=False)
if count != download_count:
msg = 'Mismatch between CSV and DB rows! Expected: {} | Actual {} in: {}'
printf({
'msg': msg.format(count, download_count, filename),
'job': job_id,
'f': 'Download'})
raise SystemExit(1)
return count
def create_local_file(self, award_type, source, agency_code, generate_since):
""" Generate complete file from SQL query and S3 bucket deletion files, then zip it locally """
logger.info('Generating CSV file with creations and modifications')
# Create file paths and working directory
timestamp = datetime.strftime(datetime.now(), '%Y%m%d%H%M%S%f')
working_dir = '{}_{}_delta_gen_{}/'.format(settings.CSV_LOCAL_PATH, agency_code, timestamp)
if not os.path.exists(working_dir):
os.mkdir(working_dir)
source_name = '{}_{}_Delta_{}'.format(agency_code, award_type, datetime.strftime(date.today(), '%Y%m%d'))
source_path = os.path.join(working_dir, '{}.csv'.format(source_name))
# Create a unique temporary file with the raw query
raw_quoted_query = generate_raw_quoted_query(source.row_emitter(None)) # None requests all headers
csv_query_annotated = self.apply_annotations_to_sql(raw_quoted_query, source.human_names)
(temp_sql_file, temp_sql_file_path) = tempfile.mkstemp(prefix='bd_sql_', dir='/tmp')
with open(temp_sql_file_path, 'w') as file:
file.write('\\copy ({}) To STDOUT with CSV HEADER'.format(csv_query_annotated))
# Generate the csv with \copy
cat_command = subprocess.Popen(['cat', temp_sql_file_path], stdout=subprocess.PIPE)
subprocess.check_output(['psql', '-o', source_path, os.environ['DOWNLOAD_DATABASE_URL'], '-v',
'ON_ERROR_STOP=1'], stdin=cat_command.stdout, stderr=subprocess.STDOUT)
# Append deleted rows to the end of the file
self.add_deletion_records(source_path, working_dir, award_type, agency_code, source, generate_since)
if count_rows_in_csv_file(source_path, has_header=True, safe=True) > 0:
# Split the CSV into multiple files and zip it up
zipfile_path = '{}{}.zip'.format(settings.CSV_LOCAL_PATH, source_name)
logger.info('Creating compressed file: {}'.format(os.path.basename(zipfile_path)))
split_and_zip_csvs(zipfile_path, source_path, source_name)
else:
zipfile_path = None
os.close(temp_sql_file)
os.remove(temp_sql_file_path)
shutil.rmtree(working_dir)
return zipfile_path
def controller(self):
download_queue = Queue() # Queue for jobs whch need a csv downloaded
es_ingest_queue = Queue(
20) # Queue for jobs which have a csv and are ready for ES ingest
job_id = 0
for fy in self.config['fiscal_years']:
for awd_cat_idx in AWARD_DESC_CATEGORIES.keys():
job_id += 1
index = self.config['index_name']
filename = '{dir}{fy}_transactions_{type}.csv'.format(
dir=self.config['directory'],
fy=fy,
type=awd_cat_idx.replace(' ', ''))
new_job = DataJob(job_id, index, fy, awd_cat_idx, filename)
if os.path.exists(filename):
# This is mostly for testing. If previous CSVs still exist skip the download for that file
if self.config['stale']:
new_job.count = count_rows_in_csv_file(filename,
has_header=True,
safe=False)
printf({
'msg':
'Using existing file: {} | count {}'.format(
filename, new_job.count),
'job':
new_job.name,
'f':
'Download'
})
# Add job directly to the Elasticsearch ingest queue since the CSV exists
es_ingest_queue.put(new_job)
continue
else:
os.remove(filename)
download_queue.put(new_job)
printf({'msg': 'There are {} jobs to process'.format(job_id)})
process_list = []
process_list.append(
Process(name='Download Proccess',
target=download_db_records,
args=(download_queue, es_ingest_queue, self.config)))
process_list.append(
Process(name='ES Index Process',
target=es_data_loader,
args=(ES, download_queue, es_ingest_queue, self.config)))
process_list[0].start() # Start Download process
if self.config['provide_deleted']:
process_list.append(
Process(name='S3 Deleted Records Scrapper Process',
target=deleted_transactions,
args=(ES, self.config)))
process_list[-1].start() # start S3 csv fetch proces
while process_list[-1].is_alive():
printf({
'msg':
'Waiting to start ES ingest until S3 deletes are complete'
})
sleep(7)
process_list[1].start() # start ES ingest process
while True:
sleep(10)
if process_guarddog(process_list):
raise SystemExit(1)
elif all([not x.is_alive() for x in process_list]):
printf({
'msg':
'All ETL processes completed execution with no error codes'
})
break
if self.config['swap']:
printf({'msg': 'Closing old indices and adding aliases'})
swap_aliases(ES, self.config['index_name'])
if self.config['snapshot']:
printf({'msg': 'Taking snapshot'})
take_snapshot(ES, self.config['index_name'],
settings.ES_REPOSITORY)
def controller(self):
download_queue = Queue() # Queue for jobs whch need a csv downloaded
es_ingest_queue = Queue(20) # Queue for jobs which have a csv and are ready for ES ingest
job_id = 0
for fy in self.config['fiscal_years']:
for awd_cat_idx in AWARD_DESC_CATEGORIES.keys():
job_id += 1
index = self.config['index_name']
filename = '{dir}{fy}_transactions_{type}.csv'.format(
dir=self.config['directory'],
fy=fy,
type=awd_cat_idx.replace(' ', ''))
new_job = DataJob(job_id, index, fy, awd_cat_idx, filename)
if os.path.exists(filename):
# This is mostly for testing. If previous CSVs still exist skip the download for that file
if self.config['stale']:
new_job.count = count_rows_in_csv_file(filename, has_header=True, safe=False)
printf({
'msg': 'Using existing file: {} | count {}'.format(filename, new_job.count),
'job': new_job.name,
'f': 'Download'})
# Add job directly to the Elasticsearch ingest queue since the CSV exists
es_ingest_queue.put(new_job)
continue
else:
os.remove(filename)
download_queue.put(new_job)
printf({'msg': 'There are {} jobs to process'.format(job_id)})
process_list = []
process_list.append(Process(
name='Download Proccess',
target=download_db_records,
args=(download_queue, es_ingest_queue, self.config)))
process_list.append(Process(
name='ES Index Process',
target=es_data_loader,
args=(ES, download_queue, es_ingest_queue, self.config)))
process_list[0].start() # Start Download process
if self.config['provide_deleted']:
process_list.append(Process(
name='S3 Deleted Records Scrapper Process',
target=deleted_transactions,
args=(ES, self.config)))
process_list[-1].start() # start S3 csv fetch proces
while process_list[-1].is_alive():
printf({'msg': 'Waiting to start ES ingest until S3 deletes are complete'})
sleep(7)
process_list[1].start() # start ES ingest process
while True:
sleep(10)
if process_guarddog(process_list):
raise SystemExit(1)
elif all([not x.is_alive() for x in process_list]):
printf({'msg': 'All ETL processes completed execution with no error codes'})
break
if self.config['swap']:
printf({'msg': 'Closing old indices and adding aliases'})
swap_aliases(ES, self.config['index_name'])
if self.config['snapshot']:
printf({'msg': 'Taking snapshot'})
take_snapshot(ES, self.config['index_name'], settings.ES_REPOSITORY)
def parse_source(source, columns, download_job, working_dir, start_time,
message, zipfile_path, limit):
"""Write to csv and zip files using the source data"""
d_map = {
'd1': 'contracts',
'd2': 'assistance',
'treasury_account': 'treasury_account',
'federal_account': 'federal_account'
}
if download_job and download_job.monthly_download:
# Use existing detailed filename from parent file for monthly files
# e.g. `019_Assistance_Delta_20180917_%s.csv`
source_name = strip_file_extension(download_job.file_name)
else:
source_name = '{}_{}_{}'.format(
source.agency_code, d_map[source.file_type],
VALUE_MAPPINGS[source.source_type]['download_name'])
source_query = source.row_emitter(columns)
source_path = os.path.join(working_dir, '{}.csv'.format(source_name))
# Generate the query file; values, limits, dates fixed
temp_file, temp_file_path = generate_temp_query_file(
source_query, limit, source, download_job, columns)
start_time = time.perf_counter()
try:
# Create a separate process to run the PSQL command; wait
psql_process = multiprocessing.Process(target=execute_psql,
args=(
temp_file_path,
source_path,
download_job,
))
psql_process.start()
wait_for_process(psql_process, start_time, download_job, message)
# The process below modifies the download job and thus cannot be in a separate process
# Assuming the process to count the number of lines in a CSV file takes less than DOWNLOAD_VISIBILITY_TIMEOUT
# in case the visibility times out before then
if message:
message.change_visibility(
VisibilityTimeout=DOWNLOAD_VISIBILITY_TIMEOUT)
# Log how many rows we have
try:
download_job.number_of_rows += count_rows_in_csv_file(
filename=source_path, has_header=True)
except Exception as e:
write_to_log(message="Unable to obtain CSV line count",
is_error=True,
download_job=download_job)
download_job.save()
# Create a separate process to split the large csv into smaller csvs and write to zip; wait
zip_process = multiprocessing.Process(target=split_and_zip_csvs,
args=(zipfile_path, source_path,
source_name, download_job))
zip_process.start()
wait_for_process(zip_process, start_time, download_job, message)
download_job.save()
except Exception as e:
raise e
finally:
# Remove temporary files
os.close(temp_file)
os.remove(temp_file_path)