Skip to content

Instantly share code, notes, and snippets.

@indraniel

indraniel/4-identify-and-cleanup-corrupt-cadd-jobs.py

Last active June 8, 2017 21:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save indraniel/7908d127ab30c8e2b8e4b2346c925205 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save indraniel/7908d127ab30c8e2b8e4b2346c925205 to your computer and use it in GitHub Desktop.
Download ZIP
scripts to undo/expunge and redo postvqsr38 pipeline steps (BIO-2310)
Raw
4-identify-and-cleanup-corrupt-cadd-jobs.py
#!/usr/bin/env python
from __future__ import print_function, division
import os, sys, re, subprocess, time, datetime, shutil
def log(msg):
timestamp = datetime.datetime.now().strftime("%Y-%m-%d %T")
print('[-- {} --] {}'.format(timestamp, msg), file=sys.stderr)
def touch(fname, times=None):
with open(fname, 'a'):
os.utime(fname, times)
def natural_key(string_):
"""See http://www.codinghorror.com/blog/archives/001018.html"""
return [int(s) if s.isdigit() else s for s in re.split(r'(\d+)', string_)]
def ensure_vcf_copy_over(logfile):
text = None
with open(logfile, 'r') as f:
text = f.read()
if 'No variants to process' in text:
return True
return False
def get_chrom(region):
(chrom, interval) = region.split(':')
return chrom
def nuke_step_10_lcr(region, dirname):
step_label = '10-Low-Confidence-Region-annotation'
stepdir = os.path.join(os.path.dirname(os.path.dirname(dirname)), step_label)
final_dir = os.path.join(stepdir, region)
scratchdir = os.path.join(final_dir, 'scratch')
logfile = os.path.join(final_dir, 'LCR.annotation.{}.log'.format(region))
final_anno_vcf = os.path.join(final_dir, 'b38.LCR.annotated.c{}.vcf.gz'.format(region))
final_anno_tbi = os.path.join(final_dir, 'b38.LCR.annotated.c{}.vcf.gz.tbi'.format(region))
print("\t----> Nuking Step: {}".format(step_label))
print("\t----> Step Dir: {}".format(final_dir))
delete_files = (
final_anno_vcf,
final_anno_tbi,
)
for f in delete_files:
if os.path.exists(f):
print("\t----> Nuking {}".format(f))
os.remove(f)
if os.path.exists(scratchdir):
print("\t----> Nuking scratchdir: {}".format(scratchdir))
shutil.rmtree(scratchdir)
if os.path.exists(logfile):
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
datestr = time.strftime("%Y-%m-%d", yesterday.timetuple())
new_logname = 'LCR.annotation.{}.log.old.{}'.format(region, datestr)
new_logfile = os.path.join(final_dir, new_logname)
print("\t----> Rename '{}' to '{}'".format(logfile, new_logfile))
os.rename(logfile, new_logfile)
def nuke_step_11_linsight(region, dirname):
step_label = '11-LINSIGHT-annotation'
stepdir = os.path.join(os.path.dirname(os.path.dirname(dirname)), step_label)
final_dir = os.path.join(stepdir, region)
scratchdir = os.path.join(final_dir, 'scratch')
logfile = os.path.join(final_dir, 'LINSIGHT.annotation.{}.log'.format(region))
final_anno_vcf = os.path.join(final_dir, 'b38.LINSIGHT.annotated.c{}.vcf.gz'.format(region))
final_anno_tbi = os.path.join(final_dir, 'b38.LINSIGHT.annotated.c{}.vcf.gz.tbi'.format(region))
print("\t----> Nuking Step: {}".format(step_label))
print("\t----> Step Dir: {}".format(final_dir))
delete_files = (
final_anno_vcf,
final_anno_tbi,
)
for f in delete_files:
if os.path.exists(f):
print("\t----> Nuking {}".format(f))
os.remove(f)
if os.path.exists(scratchdir):
print("\t----> Nuking scratchdir: {}".format(scratchdir))
shutil.rmtree(scratchdir)
if os.path.exists(logfile):
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
datestr = time.strftime("%Y-%m-%d", yesterday.timetuple())
new_logname = 'LINSIGHT.annotation.{}.log.old.{}'.format(region, datestr)
new_logfile = os.path.join(final_dir, new_logname)
print("\t----> Rename '{}' to '{}'".format(logfile, new_logfile))
os.rename(logfile, new_logfile)
def nuke_step_12_concat_vcfs(region, dirname):
step_label = '12-concat-vcfs'
stepdir = os.path.join(os.path.dirname(os.path.dirname(dirname)), step_label)
chrom = get_chrom(region)
final_dir = os.path.join(stepdir, chrom)
logfile = os.path.join(final_dir, 'concatenate.{}.log'.format(chrom))
final_anno_vcf = os.path.join(final_dir, 'concatenated.c{}.vcf.gz'.format(chrom))
final_anno_tbi = os.path.join(final_dir, 'concatenated.c{}.vcf.gz.tbi'.format(chrom))
delete_files = (
final_anno_vcf,
final_anno_tbi,
)
print("\t----> Nuking Step: {} ({})".format(step_label, chrom))
print("\t----> Step Dir: {}".format(final_dir))
for f in delete_files:
if os.path.exists(f):
print("\t----> Nuking {}".format(f))
os.remove(f)
if os.path.exists(logfile):
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
datestr = time.strftime("%Y-%m-%d", yesterday.timetuple())
new_logname = 'concatentate.{}.log.old.{}'.format(chrom, datestr)
new_logfile = os.path.join(final_dir, new_logname)
print("\t----> Rename '{}' to '{}'".format(logfile, new_logfile))
os.rename(logfile, new_logfile)
def move_logfile(dirname, logfile, region):
if os.path.exists(logfile):
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
datestr = time.strftime("%Y-%m-%d", yesterday.timetuple())
new_logname = 'cadd.annotation.{}.log.old.{}'.format(region, datestr)
new_logfile = os.path.join(dirname, new_logname)
print("\t----> Rename '{}' to '{}'".format(logfile, new_logfile))
os.rename(logfile, new_logfile)
def set_clean_flag(dirname):
manual_file = os.path.join(dirname, 'manual-clean')
touch(manual_file)
def exists_clean_flag(dirname):
manual_file = os.path.join(dirname, 'manual-clean')
verdict = True if os.path.exists(manual_file) else False
return verdict
def nuke_scratch_dir(region, dirname):
scratchdir = os.path.join(dirname, 'scratch')
logfile = os.path.join(dirname, 'cadd.annotation.{}.log'.format(region))
final_anno_vcf = os.path.join(dirname, 'b38.cadd.annotated.c{}.vcf.gz'.format(region))
final_anno_tbi = os.path.join(dirname, 'b38.cadd.annotated.c{}.vcf.gz.tbi'.format(region))
delete_files = (
final_anno_vcf,
final_anno_tbi,
)
for f in delete_files:
if os.path.exists(f):
print("\t----> Nuking {}".format(f))
os.remove(f)
if os.path.exists(scratchdir):
print("\t----> Nuking scratchdir: {}".format(scratchdir))
shutil.rmtree(scratchdir)
move_logfile(dirname, logfile, region)
# nuke downstream steps
print("\t----> Nuking downstream steps")
nuke_step_10_lcr(region, dirname)
nuke_step_11_linsight(region, dirname)
nuke_step_12_concat_vcfs(region, dirname)
set_clean_flag(dirname)
def nuke_cadd_outputs_and_onwards(region, dirname):
scratchdir = os.path.join(dirname, 'scratch')
logfile = os.path.join(dirname, 'cadd.annotation.{}.log'.format(region))
final_anno_vcf = os.path.join(dirname, 'b38.cadd.annotated.c{}.vcf.gz'.format(region))
final_anno_tbi = os.path.join(dirname, 'b38.cadd.annotated.c{}.vcf.gz.tbi'.format(region))
b38_nosample_cadd_anno_vcf = os.path.join(scratchdir, 'b38.cadd.nosamples.vcf.gz')
b38_nosample_cadd_anno_tbi = os.path.join(scratchdir, 'b38.cadd.nosamples.vcf.gz.tbi')
b37_cadd_anno_vcf = os.path.join(scratchdir, 'grc37.cadd.vcf.gz')
b37_cadd_anno_tbi = os.path.join(scratchdir, 'grc37.cadd.vcf.gz.tbi')
cadd_out_tsv = os.path.join(scratchdir, 'cadd-annotation.tsv.gz')
delete_files = (
final_anno_vcf,
final_anno_tbi,
b38_nosample_cadd_anno_vcf,
b38_nosample_cadd_anno_tbi,
b37_cadd_anno_vcf,
b37_cadd_anno_tbi,
cadd_out_tsv,
)
for f in delete_files:
if os.path.exists(f):
print("\t----> Nuking {}".format(f))
os.remove(f)
move_logfile(dirname, logfile, region)
# nuke downstream steps
print("\t----> Nuking downstream steps")
nuke_step_10_lcr(region, dirname)
nuke_step_11_linsight(region, dirname)
nuke_step_12_concat_vcfs(region, dirname)
set_clean_flag(dirname)
def nuke_merge_cadd_outputs_and_onwards(region, dirname):
scratchdir = os.path.join(dirname, 'scratch')
logfile = os.path.join(dirname, 'cadd.annotation.{}.log'.format(region))
final_anno_vcf = os.path.join(dirname, 'b38.cadd.annotated.c{}.vcf.gz'.format(region))
final_anno_tbi = os.path.join(dirname, 'b38.cadd.annotated.c{}.vcf.gz.tbi'.format(region))
b38_nosample_cadd_anno_vcf = os.path.join(scratchdir, 'b38.cadd.nosamples.vcf.gz')
b38_nosample_cadd_anno_tbi = os.path.join(scratchdir, 'b38.cadd.nosamples.vcf.gz.tbi')
b37_cadd_anno_vcf = os.path.join(scratchdir, 'grc37.cadd.vcf.gz')
b37_cadd_anno_tbi = os.path.join(scratchdir, 'grc37.cadd.vcf.gz.tbi')
delete_files = (
final_anno_vcf,
final_anno_tbi,
b38_nosample_cadd_anno_vcf,
b38_nosample_cadd_anno_tbi,
b37_cadd_anno_vcf,
b37_cadd_anno_tbi,
)
for f in delete_files:
if os.path.exists(f):
print("\t----> Nuking {}".format(f))
os.remove(f)
move_logfile(dirname, logfile, region)
# nuke downstream steps
print("\t----> Nuking downstream steps")
nuke_step_10_lcr(region, dirname)
nuke_step_11_linsight(region, dirname)
nuke_step_12_concat_vcfs(region, dirname)
set_clean_flag(dirname)
def check_scratch_dir(dirname, region, scratchdir, logfile):
if (not os.path.exists(scratchdir)) and (not ensure_vcf_copy_over(logfile)):
msg = ("[warn] -- {} -- has no scratch dir "
"and not a vcf copy over case!\n"
"Investigate '{}'")
print(msg.format(region, dirname))
def check_large_log_file(dirname, region, logfile, logit=True):
size = os.path.getsize(logfile)
one_gb = 1 * 1024 * 1024 * 1024 # 1gb in bytes
if size >= one_gb:
if logit:
msg = "===> {} : LARGE LOG FILE".format(region)
print(msg)
return True
return False
def check_cadd_input_output_line_match(region, scratchdir):
(cadd_input_file, cadd_output_file) = ('grc37.vcf.gz', 'cadd-annotation.tsv.gz')
cadd_input = os.path.join(scratchdir, cadd_input_file)
cadd_output = os.path.join(scratchdir, cadd_output_file)
if not os.path.exists(cadd_input):
msg = "===> {} : MISSING CADD INPUT ({})".format(region, cadd_input_file)
print(msg)
return True
if not os.path.exists(cadd_output):
msg = "===> {} : MISSING CADD INPUT ({})".format(region, cadd_output_file)
print(msg)
return True
bcftools = os.path.join(
'/gscmnt/gc2802/halllab/idas',
'software/local/bin',
'bcftools'
)
cmd = "{bcftools} view -H {grc37} | wc -l".format(bcftools=bcftools, grc37=cadd_input)
cadd_input_num_lines = subprocess.check_output(cmd, shell=True, executable="/bin/bash")
cadd_input_num_lines = int(cadd_input_num_lines.rstrip())
cmd = "zcat {tsv} | tail -n +3 | wc -l".format(tsv=cadd_output)
cadd_output_num_lines = subprocess.check_output(cmd, shell=True, executable="/bin/bash")
cadd_output_num_lines = int(cadd_output_num_lines.rstrip())
if cadd_input_num_lines != cadd_output_num_lines:
msg = "===> {} : CADD CORRUPTION ({} : {} | {} : {}) ".format(
region,
cadd_input_file,
cadd_input_num_lines,
cadd_output_file,
cadd_output_num_lines
)
print(msg)
return True
return False
def check_horrible_in_logs(region, logfile):
text = None
with open(logfile, 'r') as f:
text = f.read()
if 'HORRIBLE' in text:
msg = "===> {} : HORRIBLE CASE ".format(region)
print(msg)
return True
return False
def process_region_dir(dirname, files):
region = os.path.basename(dirname)
scratchdir = os.path.join(dirname, 'scratch')
logfile = os.path.join(dirname, 'cadd.annotation.{}.log'.format(region))
# warn potentially odd cases
# check_scratch_dir(dirname, region, scratchdir, logfile)
# skip empty region directories
if (not os.path.exists(logfile)) and (len(files) == 0) and (not os.path.exists(scratchdir)):
msg = "===> {} : DIRECTORY EMPTY".format(region)
print(msg)
return
# skip vcf copy over cases
if (not check_large_log_file(dirname, region, logfile, logit=False)) \
and ( ensure_vcf_copy_over(logfile) ):
msg = "===> {} : VCF COPY OVER CASE".format(region)
print(msg)
return
if exists_clean_flag(dirname):
msg = "===> {} : PREVIOUSLY CLEANED UP DIRECTORY".format(region)
print(msg)
return
if check_large_log_file(dirname, region, logfile):
print("===> {} : Nuking ({})".format(region, "nuke_scratch_dir"))
nuke_scratch_dir(region, dirname)
elif check_cadd_input_output_line_match(region, scratchdir):
print("===> {} : Nuking ({})".format(region, "nuke_cadd_outputs_and_onwards"))
nuke_cadd_outputs_and_onwards(region, dirname)
elif check_horrible_in_logs(region, logfile):
print("===> {} : Nuking ({})".format(region, "nuke_merge_cadd_outputs_and_onwards"))
nuke_merge_cadd_outputs_and_onwards(region, dirname)
def process_cadd_output_dir():
root = os.path.join(
'/gscmnt/gc2758/analysis/ccdg_gatk_callsets',
'eocad_aa_2017.05.18/post-vqsr',
'9-cadd-annotation',
)
print('root: {}'.format(root))
for (i, (dirname, subdirs, files)) in enumerate(os.walk(root)):
subdirs.sort(key=natural_key)
if dirname == root: continue
if 'scratch' in subdirs:
subdirs[:] = [ d for d in subdirs if d != 'scratch' ]
print('-- [{}]: {} --'.format(i, dirname))
process_region_dir(dirname, files)
def main():
log("Starting Inspection/Purge")
process_cadd_output_dir()
log("All Done!")
if __name__ == "__main__":
main()
Raw
6-record-backup-and-update-workflow-db.sh
#!/bin/bash
BASE=/gscmnt/gc2802/halllab/idas/jira/BIO-2310
OUTDIR=${BASE}/data/derived/6-record-backup-and-update-workflow-db
WORKFLOW_DB=/gscuser/idas/yaps2/lsf-jobs-BIO-2310.db
BEFORE_CORRUPTION_FIX_DB=/gscuser/idas/yaps2/lsf-jobs-BIO-2310.db.2017.06.07.00.01
if [[ ! -e ${BEFORE_CORRUPTION_FIX_DB} ]]; then
echo "Making a backup of the BIO-2310 workflow db : ${WORKFLOW_DB}"
cp -v ${WORKFLOW_DB} ${BEFORE_CORRUPTION_FIX_DB};
fi
if [[ ! -e ${OUTDIR} ]]; then
echo "Creating output directory : ${OUTDIR}"
mkdir -p ${OUTDIR}
fi
echo "Recording original state of the corrupted entries"
sqlite3 -separator $'\t' -header ${BEFORE_CORRUPTION_FIX_DB} \
< ${BASE}/bin/note-relevant-cadd-and-post-steps-statuses.sql \
2>&1 \
> ${OUTDIR}/note-relevant-cadd-and-post-steps-statuses-before-fixing.sql.out
sqlite3 ${WORKFLOW_DB} \
< ${BASE}/bin/fix-db-corrupted-cases.sql \
2>&1 \
> ${OUTDIR}/fix-db-corrupted-cases.sql.out
sqlite3 -separator $'\t' -header ${WORKFLOW_DB} \
< ${BASE}/bin/note-relevant-cadd-and-post-steps-statuses.sql \
2>&1 \
> ${OUTDIR}/note-relevant-cadd-and-post-steps-statuses-after-fixing.sql.out
Raw
fix-db-corrupted-cases.sql
update task
set _status = 'failed',
successful = 0,
started_on = null,
finished_on = null
where id in
(
select t.id
from task t
join stage s on s.id = t.stage_id
where
(
s.name = '9-cadd-annotation'
and t.uid in
(
'chr1:1-9999999',
'chr1:10000000-19999999',
'chr1:120000000-129999999',
'chr1:140000000-149999999',
'chr1:150000000-159999999',
'chr1:190000000-199999999',
'chr1:220000000-229999999',
'chr2:1-9999999',
'chr2:30000000-39999999',
'chr2:70000000-79999999',
'chr2:80000000-89999999',
'chr2:90000000-99999999',
'chr2:240000000-242193529',
'chr3:1-9999999',
'chr3:50000000-59999999',
'chr3:90000000-99999999',
'chr3:190000000-198295559',
'chr4:1-9999999',
'chr4:10000000-19999999',
'chr4:40000000-49999999',
'chr4:50000000-59999999',
'chr4:190000000-190214555',
'chr5:20000000-29999999',
'chr5:40000000-49999999',
'chr5:50000000-59999999',
'chr5:120000000-129999999',
'chr6:1-9999999',
'chr6:20000000-29999999',
'chr6:30000000-39999999',
'chr6:50000000-59999999',
'chr6:60000000-69999999',
'chr6:90000000-99999999',
'chr6:100000000-109999999',
'chr6:150000000-159999999',
'chr6:160000000-169999999',
'chr6:170000000-170805979',
'chr7:1-9999999',
'chr7:50000000-59999999',
'chr7:60000000-69999999',
'chr7:70000000-79999999',
'chr7:100000000-109999999',
'chr7:140000000-149999999',
'chr7:150000000-159345973',
'chr8:1-9999999',
'chr8:10000000-19999999',
'chr8:20000000-29999999',
'chr8:40000000-49999999',
'chr8:50000000-59999999',
'chr8:140000000-145138636',
'chr9:1-9999999',
'chr9:40000000-49999999',
'chr9:110000000-119999999',
'chr9:130000000-138394717',
'chr10:1-9999999',
'chr10:30000000-39999999',
'chr10:40000000-49999999',
'chr10:130000000-133797422',
'chr11:1-9999999',
'chr11:10000000-19999999',
'chr11:40000000-49999999',
'chr11:50000000-59999999',
'chr11:70000000-79999999',
'chr12:30000000-39999999',
'chr12:100000000-109999999',
'chr12:110000000-119999999',
'chr13:10000000-19999999',
'chr13:30000000-39999999',
'chr13:90000000-99999999',
'chr13:110000000-114364328',
'chr14:10000000-19999999',
'chr14:20000000-29999999',
'chr14:100000000-107043718',
'chr15:10000000-19999999',
'chr15:20000000-29999999',
'chr15:80000000-89999999',
'chr15:90000000-99999999',
'chr16:10000000-19999999',
'chr16:30000000-39999999',
'chr17:1-9999999',
'chr17:20000000-29999999',
'chr17:30000000-39999999',
'chr17:40000000-49999999',
'chr17:70000000-79999999',
'chr18:10000000-19999999',
'chr18:20000000-29999999',
'chr18:40000000-49999999',
'chr18:80000000-80373285',
'chr19:1-9999999',
'chr19:30000000-39999999',
'chr19:40000000-49999999',
'chr19:50000000-58617616',
'chr20:1-9999999',
'chr20:20000000-29999999',
'chr20:30000000-39999999',
'chr20:40000000-49999999',
'chr20:60000000-64444167',
'chr21:1-9999999',
'chr21:10000000-19999999',
'chr21:40000000-46709983',
'chr22:10000000-19999999',
'chr22:40000000-49999999',
'chrX:1-9999999',
'chrX:50000000-59999999',
'chrX:60000000-69999999',
'chrX:70000000-79999999',
'chrX:80000000-89999999',
'chrX:110000000-119999999',
'chrX:130000000-139999999',
'chrX:140000000-149999999',
'chrX:150000000-156040895',
'chrY:10000000-19999999',
'chrY:20000000-29999999'
)
)
or
(
s.name = '10-Low-Confidence-Region-annotation'
and t.uid in
(
'chr1:1-9999999',
'chr1:10000000-19999999',
'chr1:120000000-129999999',
'chr1:140000000-149999999',
'chr1:150000000-159999999',
'chr1:190000000-199999999',
'chr1:220000000-229999999',
'chr2:1-9999999',
'chr2:30000000-39999999',
'chr2:70000000-79999999',
'chr2:80000000-89999999',
'chr2:90000000-99999999',
'chr2:240000000-242193529',
'chr3:1-9999999',
'chr3:50000000-59999999',
'chr3:90000000-99999999',
'chr3:190000000-198295559',
'chr4:1-9999999',
'chr4:10000000-19999999',
'chr4:40000000-49999999',
'chr4:50000000-59999999',
'chr4:190000000-190214555',
'chr5:20000000-29999999',
'chr5:40000000-49999999',
'chr5:50000000-59999999',
'chr5:120000000-129999999',
'chr6:1-9999999',
'chr6:20000000-29999999',
'chr6:30000000-39999999',
'chr6:50000000-59999999',
'chr6:60000000-69999999',
'chr6:90000000-99999999',
'chr6:100000000-109999999',
'chr6:150000000-159999999',
'chr6:160000000-169999999',
'chr6:170000000-170805979',
'chr7:1-9999999',
'chr7:50000000-59999999',
'chr7:60000000-69999999',
'chr7:70000000-79999999',
'chr7:100000000-109999999',
'chr7:140000000-149999999',
'chr7:150000000-159345973',
'chr8:1-9999999',
'chr8:10000000-19999999',
'chr8:20000000-29999999',
'chr8:40000000-49999999',
'chr8:50000000-59999999',
'chr8:140000000-145138636',
'chr9:1-9999999',
'chr9:40000000-49999999',
'chr9:110000000-119999999',
'chr9:130000000-138394717',
'chr10:1-9999999',
'chr10:30000000-39999999',
'chr10:40000000-49999999',
'chr10:130000000-133797422',
'chr11:1-9999999',
'chr11:10000000-19999999',
'chr11:40000000-49999999',
'chr11:50000000-59999999',
'chr11:70000000-79999999',
'chr12:30000000-39999999',
'chr12:100000000-109999999',
'chr12:110000000-119999999',
'chr13:10000000-19999999',
'chr13:30000000-39999999',
'chr13:90000000-99999999',
'chr13:110000000-114364328',
'chr14:10000000-19999999',
'chr14:20000000-29999999',
'chr14:100000000-107043718',
'chr15:10000000-19999999',
'chr15:20000000-29999999',
'chr15:80000000-89999999',
'chr15:90000000-99999999',
'chr16:10000000-19999999',
'chr16:30000000-39999999',
'chr17:1-9999999',
'chr17:20000000-29999999',
'chr17:30000000-39999999',
'chr17:40000000-49999999',
'chr17:70000000-79999999',
'chr18:10000000-19999999',
'chr18:20000000-29999999',
'chr18:40000000-49999999',
'chr18:80000000-80373285',
'chr19:1-9999999',
'chr19:30000000-39999999',
'chr19:40000000-49999999',
'chr19:50000000-58617616',
'chr20:1-9999999',
'chr20:20000000-29999999',
'chr20:30000000-39999999',
'chr20:40000000-49999999',
'chr20:60000000-64444167',
'chr21:1-9999999',
'chr21:10000000-19999999',
'chr21:40000000-46709983',
'chr22:10000000-19999999',
'chr22:40000000-49999999',
'chrX:1-9999999',
'chrX:50000000-59999999',
'chrX:60000000-69999999',
'chrX:70000000-79999999',
'chrX:80000000-89999999',
'chrX:110000000-119999999',
'chrX:130000000-139999999',
'chrX:140000000-149999999',
'chrX:150000000-156040895',
'chrY:10000000-19999999',
'chrY:20000000-29999999'
)
)
or
(
s.name = '11-LINSIGHT-annotation'
and t.uid in
(
'chr1:1-9999999',
'chr1:10000000-19999999',
'chr1:120000000-129999999',
'chr1:140000000-149999999',
'chr1:150000000-159999999',
'chr1:190000000-199999999',
'chr1:220000000-229999999',
'chr2:1-9999999',
'chr2:30000000-39999999',
'chr2:70000000-79999999',
'chr2:80000000-89999999',
'chr2:90000000-99999999',
'chr2:240000000-242193529',
'chr3:1-9999999',
'chr3:50000000-59999999',
'chr3:90000000-99999999',
'chr3:190000000-198295559',
'chr4:1-9999999',
'chr4:10000000-19999999',
'chr4:40000000-49999999',
'chr4:50000000-59999999',
'chr4:190000000-190214555',
'chr5:20000000-29999999',
'chr5:40000000-49999999',
'chr5:50000000-59999999',
'chr5:120000000-129999999',
'chr6:1-9999999',
'chr6:20000000-29999999',
'chr6:30000000-39999999',
'chr6:50000000-59999999',
'chr6:60000000-69999999',
'chr6:90000000-99999999',
'chr6:100000000-109999999',
'chr6:150000000-159999999',
'chr6:160000000-169999999',
'chr6:170000000-170805979',
'chr7:1-9999999',
'chr7:50000000-59999999',
'chr7:60000000-69999999',
'chr7:70000000-79999999',
'chr7:100000000-109999999',
'chr7:140000000-149999999',
'chr7:150000000-159345973',
'chr8:1-9999999',
'chr8:10000000-19999999',
'chr8:20000000-29999999',
'chr8:40000000-49999999',
'chr8:50000000-59999999',
'chr8:140000000-145138636',
'chr9:1-9999999',
'chr9:40000000-49999999',
'chr9:110000000-119999999',
'chr9:130000000-138394717',
'chr10:1-9999999',
'chr10:30000000-39999999',
'chr10:40000000-49999999',
'chr10:130000000-133797422',
'chr11:1-9999999',
'chr11:10000000-19999999',
'chr11:40000000-49999999',
'chr11:50000000-59999999',
'chr11:70000000-79999999',
'chr12:30000000-39999999',
'chr12:100000000-109999999',
'chr12:110000000-119999999',
'chr13:10000000-19999999',
'chr13:30000000-39999999',
'chr13:90000000-99999999',
'chr13:110000000-114364328',
'chr14:10000000-19999999',
'chr14:20000000-29999999',
'chr14:100000000-107043718',
'chr15:10000000-19999999',
'chr15:20000000-29999999',
'chr15:80000000-89999999',
'chr15:90000000-99999999',
'chr16:10000000-19999999',
'chr16:30000000-39999999',
'chr17:1-9999999',
'chr17:20000000-29999999',
'chr17:30000000-39999999',
'chr17:40000000-49999999',
'chr17:70000000-79999999',
'chr18:10000000-19999999',
'chr18:20000000-29999999',
'chr18:40000000-49999999',
'chr18:80000000-80373285',
'chr19:1-9999999',
'chr19:30000000-39999999',
'chr19:40000000-49999999',
'chr19:50000000-58617616',
'chr20:1-9999999',
'chr20:20000000-29999999',
'chr20:30000000-39999999',
'chr20:40000000-49999999',
'chr20:60000000-64444167',
'chr21:1-9999999',
'chr21:10000000-19999999',
'chr21:40000000-46709983',
'chr22:10000000-19999999',
'chr22:40000000-49999999',
'chrX:1-9999999',
'chrX:50000000-59999999',
'chrX:60000000-69999999',
'chrX:70000000-79999999',
'chrX:80000000-89999999',
'chrX:110000000-119999999',
'chrX:130000000-139999999',
'chrX:140000000-149999999',
'chrX:150000000-156040895',
'chrY:10000000-19999999',
'chrY:20000000-29999999'
)
)
or
(
s.name = '12-concat-vcfs'
and t.uid in
(
'chr1',
'chr2',
'chr3',
'chr4',
'chr5',
'chr6',
'chr7',
'chr8',
'chr9',
'chr10',
'chr11',
'chr12',
'chr13',
'chr14',
'chr15',
'chr16',
'chr17',
'chr18',
'chr19',
'chr20',
'chr21',
'chr22',
'chrX',
'chrY'
)
)
order by t.uid
)
;
Raw
note-relevant-cadd-and-post-steps-statuses.sql
select s.name,
t.id,
t.uid,
t.drm_jobID,
t._status,
t.successful,
t.started_on,
t.submitted_on,
t.finished_on
from task t
join stage s on s.id = t.stage_id
where
(
s.name = '9-cadd-annotation'
and t.uid in
(
'chr1:1-9999999',
'chr1:10000000-19999999',
'chr1:120000000-129999999',
'chr1:140000000-149999999',
'chr1:150000000-159999999',
'chr1:190000000-199999999',
'chr1:220000000-229999999',
'chr2:1-9999999',
'chr2:30000000-39999999',
'chr2:70000000-79999999',
'chr2:80000000-89999999',
'chr2:90000000-99999999',
'chr2:240000000-242193529',
'chr3:1-9999999',
'chr3:50000000-59999999',
'chr3:90000000-99999999',
'chr3:190000000-198295559',
'chr4:1-9999999',
'chr4:10000000-19999999',
'chr4:40000000-49999999',
'chr4:50000000-59999999',
'chr4:190000000-190214555',
'chr5:20000000-29999999',
'chr5:40000000-49999999',
'chr5:50000000-59999999',
'chr5:120000000-129999999',
'chr6:1-9999999',
'chr6:20000000-29999999',
'chr6:30000000-39999999',
'chr6:50000000-59999999',
'chr6:60000000-69999999',
'chr6:90000000-99999999',
'chr6:100000000-109999999',
'chr6:150000000-159999999',
'chr6:160000000-169999999',
'chr6:170000000-170805979',
'chr7:1-9999999',
'chr7:50000000-59999999',
'chr7:60000000-69999999',
'chr7:70000000-79999999',
'chr7:100000000-109999999',
'chr7:140000000-149999999',
'chr7:150000000-159345973',
'chr8:1-9999999',
'chr8:10000000-19999999',
'chr8:20000000-29999999',
'chr8:40000000-49999999',
'chr8:50000000-59999999',
'chr8:140000000-145138636',
'chr9:1-9999999',
'chr9:40000000-49999999',
'chr9:110000000-119999999',
'chr9:130000000-138394717',
'chr10:1-9999999',
'chr10:30000000-39999999',
'chr10:40000000-49999999',
'chr10:130000000-133797422',
'chr11:1-9999999',
'chr11:10000000-19999999',
'chr11:40000000-49999999',
'chr11:50000000-59999999',
'chr11:70000000-79999999',
'chr12:30000000-39999999',
'chr12:100000000-109999999',
'chr12:110000000-119999999',
'chr13:10000000-19999999',
'chr13:30000000-39999999',
'chr13:90000000-99999999',
'chr13:110000000-114364328',
'chr14:10000000-19999999',
'chr14:20000000-29999999',
'chr14:100000000-107043718',
'chr15:10000000-19999999',
'chr15:20000000-29999999',
'chr15:80000000-89999999',
'chr15:90000000-99999999',
'chr16:10000000-19999999',
'chr16:30000000-39999999',
'chr17:1-9999999',
'chr17:20000000-29999999',
'chr17:30000000-39999999',
'chr17:40000000-49999999',
'chr17:70000000-79999999',
'chr18:10000000-19999999',
'chr18:20000000-29999999',
'chr18:40000000-49999999',
'chr18:80000000-80373285',
'chr19:1-9999999',
'chr19:30000000-39999999',
'chr19:40000000-49999999',
'chr19:50000000-58617616',
'chr20:1-9999999',
'chr20:20000000-29999999',
'chr20:30000000-39999999',
'chr20:40000000-49999999',
'chr20:60000000-64444167',
'chr21:1-9999999',
'chr21:10000000-19999999',
'chr21:40000000-46709983',
'chr22:10000000-19999999',
'chr22:40000000-49999999',
'chrX:1-9999999',
'chrX:50000000-59999999',
'chrX:60000000-69999999',
'chrX:70000000-79999999',
'chrX:80000000-89999999',
'chrX:110000000-119999999',
'chrX:130000000-139999999',
'chrX:140000000-149999999',
'chrX:150000000-156040895',
'chrY:10000000-19999999',
'chrY:20000000-29999999'
)
)
or
(
s.name = '10-Low-Confidence-Region-annotation'
and t.uid in
(
'chr1:1-9999999',
'chr1:10000000-19999999',
'chr1:120000000-129999999',
'chr1:140000000-149999999',
'chr1:150000000-159999999',
'chr1:190000000-199999999',
'chr1:220000000-229999999',
'chr2:1-9999999',
'chr2:30000000-39999999',
'chr2:70000000-79999999',
'chr2:80000000-89999999',
'chr2:90000000-99999999',
'chr2:240000000-242193529',
'chr3:1-9999999',
'chr3:50000000-59999999',
'chr3:90000000-99999999',
'chr3:190000000-198295559',
'chr4:1-9999999',
'chr4:10000000-19999999',
'chr4:40000000-49999999',
'chr4:50000000-59999999',
'chr4:190000000-190214555',
'chr5:20000000-29999999',
'chr5:40000000-49999999',
'chr5:50000000-59999999',
'chr5:120000000-129999999',
'chr6:1-9999999',
'chr6:20000000-29999999',
'chr6:30000000-39999999',
'chr6:50000000-59999999',
'chr6:60000000-69999999',
'chr6:90000000-99999999',
'chr6:100000000-109999999',
'chr6:150000000-159999999',
'chr6:160000000-169999999',
'chr6:170000000-170805979',
'chr7:1-9999999',
'chr7:50000000-59999999',
'chr7:60000000-69999999',
'chr7:70000000-79999999',
'chr7:100000000-109999999',
'chr7:140000000-149999999',
'chr7:150000000-159345973',
'chr8:1-9999999',
'chr8:10000000-19999999',
'chr8:20000000-29999999',
'chr8:40000000-49999999',
'chr8:50000000-59999999',
'chr8:140000000-145138636',
'chr9:1-9999999',
'chr9:40000000-49999999',
'chr9:110000000-119999999',
'chr9:130000000-138394717',
'chr10:1-9999999',
'chr10:30000000-39999999',
'chr10:40000000-49999999',
'chr10:130000000-133797422',
'chr11:1-9999999',
'chr11:10000000-19999999',
'chr11:40000000-49999999',
'chr11:50000000-59999999',
'chr11:70000000-79999999',
'chr12:30000000-39999999',
'chr12:100000000-109999999',
'chr12:110000000-119999999',
'chr13:10000000-19999999',
'chr13:30000000-39999999',
'chr13:90000000-99999999',
'chr13:110000000-114364328',
'chr14:10000000-19999999',
'chr14:20000000-29999999',
'chr14:100000000-107043718',
'chr15:10000000-19999999',
'chr15:20000000-29999999',
'chr15:80000000-89999999',
'chr15:90000000-99999999',
'chr16:10000000-19999999',
'chr16:30000000-39999999',
'chr17:1-9999999',
'chr17:20000000-29999999',
'chr17:30000000-39999999',
'chr17:40000000-49999999',
'chr17:70000000-79999999',
'chr18:10000000-19999999',
'chr18:20000000-29999999',
'chr18:40000000-49999999',
'chr18:80000000-80373285',
'chr19:1-9999999',
'chr19:30000000-39999999',
'chr19:40000000-49999999',
'chr19:50000000-58617616',
'chr20:1-9999999',
'chr20:20000000-29999999',
'chr20:30000000-39999999',
'chr20:40000000-49999999',
'chr20:60000000-64444167',
'chr21:1-9999999',
'chr21:10000000-19999999',
'chr21:40000000-46709983',
'chr22:10000000-19999999',
'chr22:40000000-49999999',
'chrX:1-9999999',
'chrX:50000000-59999999',
'chrX:60000000-69999999',
'chrX:70000000-79999999',
'chrX:80000000-89999999',
'chrX:110000000-119999999',
'chrX:130000000-139999999',
'chrX:140000000-149999999',
'chrX:150000000-156040895',
'chrY:10000000-19999999',
'chrY:20000000-29999999'
)
)
or
(
s.name = '11-LINSIGHT-annotation'
and t.uid in
(
'chr1:1-9999999',
'chr1:10000000-19999999',
'chr1:120000000-129999999',
'chr1:140000000-149999999',
'chr1:150000000-159999999',
'chr1:190000000-199999999',
'chr1:220000000-229999999',
'chr2:1-9999999',
'chr2:30000000-39999999',
'chr2:70000000-79999999',
'chr2:80000000-89999999',
'chr2:90000000-99999999',
'chr2:240000000-242193529',
'chr3:1-9999999',
'chr3:50000000-59999999',
'chr3:90000000-99999999',
'chr3:190000000-198295559',
'chr4:1-9999999',
'chr4:10000000-19999999',
'chr4:40000000-49999999',
'chr4:50000000-59999999',
'chr4:190000000-190214555',
'chr5:20000000-29999999',
'chr5:40000000-49999999',
'chr5:50000000-59999999',
'chr5:120000000-129999999',
'chr6:1-9999999',
'chr6:20000000-29999999',
'chr6:30000000-39999999',
'chr6:50000000-59999999',
'chr6:60000000-69999999',
'chr6:90000000-99999999',
'chr6:100000000-109999999',
'chr6:150000000-159999999',
'chr6:160000000-169999999',
'chr6:170000000-170805979',
'chr7:1-9999999',
'chr7:50000000-59999999',
'chr7:60000000-69999999',
'chr7:70000000-79999999',
'chr7:100000000-109999999',
'chr7:140000000-149999999',
'chr7:150000000-159345973',
'chr8:1-9999999',
'chr8:10000000-19999999',
'chr8:20000000-29999999',
'chr8:40000000-49999999',
'chr8:50000000-59999999',
'chr8:140000000-145138636',
'chr9:1-9999999',
'chr9:40000000-49999999',
'chr9:110000000-119999999',
'chr9:130000000-138394717',
'chr10:1-9999999',
'chr10:30000000-39999999',
'chr10:40000000-49999999',
'chr10:130000000-133797422',
'chr11:1-9999999',
'chr11:10000000-19999999',
'chr11:40000000-49999999',
'chr11:50000000-59999999',
'chr11:70000000-79999999',
'chr12:30000000-39999999',
'chr12:100000000-109999999',
'chr12:110000000-119999999',
'chr13:10000000-19999999',
'chr13:30000000-39999999',
'chr13:90000000-99999999',
'chr13:110000000-114364328',
'chr14:10000000-19999999',
'chr14:20000000-29999999',
'chr14:100000000-107043718',
'chr15:10000000-19999999',
'chr15:20000000-29999999',
'chr15:80000000-89999999',
'chr15:90000000-99999999',
'chr16:10000000-19999999',
'chr16:30000000-39999999',
'chr17:1-9999999',
'chr17:20000000-29999999',
'chr17:30000000-39999999',
'chr17:40000000-49999999',
'chr17:70000000-79999999',
'chr18:10000000-19999999',
'chr18:20000000-29999999',
'chr18:40000000-49999999',
'chr18:80000000-80373285',
'chr19:1-9999999',
'chr19:30000000-39999999',
'chr19:40000000-49999999',
'chr19:50000000-58617616',
'chr20:1-9999999',
'chr20:20000000-29999999',
'chr20:30000000-39999999',
'chr20:40000000-49999999',
'chr20:60000000-64444167',
'chr21:1-9999999',
'chr21:10000000-19999999',
'chr21:40000000-46709983',
'chr22:10000000-19999999',
'chr22:40000000-49999999',
'chrX:1-9999999',
'chrX:50000000-59999999',
'chrX:60000000-69999999',
'chrX:70000000-79999999',
'chrX:80000000-89999999',
'chrX:110000000-119999999',
'chrX:130000000-139999999',
'chrX:140000000-149999999',
'chrX:150000000-156040895',
'chrY:10000000-19999999',
'chrY:20000000-29999999'
)
)
or
(
s.name = '12-concat-vcfs'
and t.uid in
(
'chr1',
'chr2',
'chr3',
'chr4',
'chr5',
'chr6',
'chr7',
'chr8',
'chr9',
'chr10',
'chr11',
'chr12',
'chr13',
'chr14',
'chr15',
'chr16',
'chr17',
'chr18',
'chr19',
'chr20',
'chr21',
'chr22',
'chrX',
'chrY'
)
)
order by s.name, t.uid
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment