From 72890999e24d4cb585752f3f00dab620f7bd3cba 2017-07-04 14:37:44 From: Bart Scheers Date: 2017-07-04 14:37:44 Subject: [PATCH] Running LSST baseline queries in MonetDB --- diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000000000000000000000000000000000000..0d20b6487c61e7d1bde93acf4a14b7a89083a16d --- /dev/null +++ b/.gitignore @@ -0,0 +1 @@ +*.pyc diff --git a/README.rst b/README.rst new file mode 100644 index 0000000000000000000000000000000000000000..3baa311136537079a16cf4efb2eca0c693e85e2a --- /dev/null +++ b/README.rst @@ -0,0 +1,39 @@ +Running LSST baseline queries in MonetDB +======================================== + +See blog post ... for further details. + +You need to have several LSST csv files (with artificial data), +which can be requested from the LSST DM team. + +MonetDB +------- +Run the installation script ``install_monetdb.sh``, +start the daemon and create a database instance. + +Loading +------- + +The python load script ``load_lsst_data.py`` defines the +database schema with table and partition defintions +and loads the csv files into the database. + +Running queries +--------------- + +Two shell scripts run the queries in a hot and cold mode +``hotloop.queries.sh`` and ``coldloop.queries.sh``, resp. +The averages and standard deviations +of ten runs are reported in the log files + +Plotting results +---------------- + +Use the python script ``plot_baselines.py`` to plot a bar chart +of the results in comparison with the S15 MySQL results. + +More info +--------- + +For more info send an e-mail to bscheers at cwi.nl. + diff --git a/coldloop.queries.sh b/coldloop.queries.sh new file mode 100755 index 0000000000000000000000000000000000000000..89a51fda06618bd3446b272b891b2e745da00a1a --- /dev/null +++ b/coldloop.queries.sh @@ -0,0 +1,116 @@ +# /bin/bash +# +#+---------------------------------------------------------------------+ +#| | +#| Script to run LSST baseline queries in MonetDB in hot mode. | +#| | +#| This script assumes the database exists and is loaded with the | +#| 2.0 TB csv files kindly provided by F. Jammes. Run the python | +#| iscript load_lsst_data.py to load the database. | +#| | +#| The SQL file name tells corresponds to the query number, i.e. | +#| query = {q1, q2, ..., q13} | +#| | +#| The timing results are written to a log file with these variables | +#| in its name as well, including the node name. | +#| | +#| Then use the baq_node_results.py script to plot the timings. | +#| | +#| Run it as | +#| ./hotloop.queries.sh $dbname $dbversion $dbfarm $sqldir | +#| | +#| Bart Scheers (CWI, 2016) | +#+---------------------------------------------------------------------+ + +# We are in the "cold" loop script +temp=cold + +dbname=$1 +dbversion=$2 +dbfarm=$3 +sqldir=$4 + +if [ ! -d $sqldir ]; then + echo "Directory $sqldir does not exist." + exit 64 +fi +sqlfiles=($sqldir/q*.sql) + +logdir=${sqldir}/log +if [ ! -d $logdir ]; then + echo "Directory $logdir does not exist." + exit 64 +fi + +echo +echo "################:" +echo "# Running node : $( hostname -a )" +echo "# Using database: $dbname" +echo "# version: $dbversion" +echo "# which: $( which mserver5 )" +echo "# Using dbfarm : $dbfarm" +echo "# SQL dir : ${sqldir}" +echo "# Log dir : ${logdir}" +echo "################:" + +# Needed for stripping the mclient milliseconds timing strings +suffms="ms" + +# Here we iterate through the sqlfiles in the sqldir +for ((i=0; i<${#sqlfiles[@]}; i++)); do + sqlfile=${sqlfiles[$i]} + echo "Working on sqlfile: ${sqlfile}" + # get the file name + IFS='/' read -r -a qf <<< "$sqlfile" + # strip to the .sql extension + IFS='.' read -ra q <<< "${qf[-1]}" + query=${q[0]} + qlog=${logdir}/${query}.${temp}.$( hostname -a ).${dbversion}.log + echo "Logging query times in: ${qlog}" + + START=1 + END=10 + echo "Running cold bulk associations ${END} times..." + + for (( c=$START; c<=$END; c++ )) + do + + echo "Run $c" + monetdb stop -a + monetdbd stop $dbfarm + echo "Stopping monetdbd..." + running="$( pgrep -u${USER} -l | grep monetdbd )" + sp='/-\|' + #printf ' ' + while [ -n "$running" ]; do + running="$( pgrep -u${USER} -l | grep monetdbd )" + #printf '\b%.1s' "$sp" + sp=${sp#?}${sp%???} + sleep 1 + done + echo "monetdbd stopped" + echo 3 | sudo tee /proc/sys/vm/drop_caches + #echo "UNFORTUNATELY: 'echo 3 | sudo tee /proc/sys/vm/drop_caches' DOES NOT WORK" + # We forward output to /dev/null, since mother script might not close + # when we use 2>&1 | tee log + monetdbd start $dbfarm > /dev/null 2>&1 + monetdb start $dbname + echo "monetdb and $dbname started" + echo "Cold query run time:" + res=($( mclient -d$dbname -ftimer -ims < $sqlfile)) + echo "${res[@]}" + + # strip the ms + tms=${res[-1]%$suffms} + + echo "${tms} milliseconds" + + if [ $c -eq 1 ]; then + echo "${c};${tms}" > $qlog + else + echo "${c};${tms}" >> $qlog + fi + done +done + +echo "READY. Cold bulk associations ran ${END} times." diff --git a/hotloop.queries.sh b/hotloop.queries.sh new file mode 100755 index 0000000000000000000000000000000000000000..be315c8248f65ebb15e6674741f765bfb03e5ea0 --- /dev/null +++ b/hotloop.queries.sh @@ -0,0 +1,118 @@ +#/bin/bash +# +#+---------------------------------------------------------------------+ +#| | +#| Script to run LSST baseline queries in MonetDB in hot mode. | +#| | +#| This script assumes the database exists and is loaded with the | +#| 2.0 TB csv files kindly provided by F. Jammes. Run the python | +#| iscript load_lsst_data.py to load the database. | +#| | +#| The SQL file name tells corresponds to the query number, i.e. | +#| query = {q1, q2, ..., q13} | +#| | +#| The timing results are written to a log file with these variables | +#| in its name as well, including the node name. | +#| | +#| Then use the baq_node_results.py script to plot the timings. | +#| | +#| Run it as | +#| ./hotloop.queries.sh $dbname $dbversion $dbfarm $sqldir | +#| | +#| Bart Scheers (CWI, 2016) | +#+---------------------------------------------------------------------+ + +# We are in the "hot" loop script +temp=hot + +dbname=$1 +dbversion=$2 +dbfarm=$3 +sqldir=$4 + +if [ ! -d $sqldir ]; then + echo "Directory $sqldir does not exist." + exit 64 +fi +sqlfiles=($sqldir/q*.sql) + +logdir=${sqldir}/log +if [ ! -d $logdir ]; then + echo "Directory $logdir does not exist." + exit 64 +fi + +echo +echo "################:" +echo "# Running node : $( hostname -a )" +echo "# Using database: $dbname" +echo "# version: $dbversion" +echo "# which: $( which mserver5 )" +echo "# Using dbfarm : $dbfarm" +echo "# SQL dir : ${sqldir}" +echo "# Log dir : ${logdir}" +echo "################:" + +# Needed for stripping the mclient milliseconds timing strings +suffms="ms" + +# Here we iterate through the sqlfiles in the sqldir +for ((i=0; i<${#sqlfiles[@]}; i++)); do + sqlfile=${sqlfiles[$i]} + echo "Working on sqlfile: ${sqlfile}" + # get the file name + IFS='/' read -r -a qf <<< "$sqlfile" + # strip to the .sql extension + IFS='.' read -ra q <<< "${qf[-1]}" + query=${q[0]} + qlog=${logdir}/${query}.${temp}.$( hostname -a ).${dbversion}.log + echo "Logging query times in: ${qlog}" + + monetdb stop -a + monetdbd stop $dbfarm + echo "Stopping monetdbd..." + running="$( pgrep -u${USER} -l | grep monetdbd )" + sp='/-\|' + #printf ' ' + while [ -n "$running" ]; do + running="$( pgrep -u${USER} -l | grep monetdbd )" + #printf '\b%.1s' "$sp" + sp=${sp#?}${sp%???} + sleep 1 + done + echo "monetdbd stopped" + echo 3 | sudo tee /proc/sys/vm/drop_caches + #echo "UNFORTUNATELY: 'echo 3 | sudo tee /proc/sys/vm/drop_caches' DOES NOT WORK" + # We forward output to /dev/null, since mother script might not close + # when we use 2>&1 | tee log + monetdbd start $dbfarm > /dev/null 2>&1 + monetdb start $dbname + echo "monetdbd and monetdb started; $dbname running" + mclient -d$dbname -ftimer -ims < $sqlfile + echo "First query ran" + + START=1 + END=10 + echo "Running hot bulk associations ${END} times..." + + for (( c=$START; c<=$END; c++ )) + do + echo "${query}; run: $c" + res=($( mclient -d$dbname -ftimer -ims < $sqlfile)) + echo "${res[@]}" + + # strip the ms + tms=${res[-1]%$suffms} + + echo "${tms} milliseconds" + + if [ $c -eq 1 ]; then + echo "${c};${tms}" > $qlog + else + echo "${c};${tms}" >> $qlog + fi + + done +done + +echo "READY. Hot bulk associations ran ${END} times." diff --git a/init_db.sh b/init_db.sh new file mode 100755 index 0000000000000000000000000000000000000000..3e9ef2c1e2e2719248455f089561857ff0d22114 --- /dev/null +++ b/init_db.sh @@ -0,0 +1,15 @@ +#! /bin/bash +function init_db { + + db=$1 + monetdb stop $db + monetdb destroy -f $db + monetdb create $db + #monetdb set nthreads=1 $db + monetdb set embedpy=yes $db + monetdb release $db + monetdb start $db + +} + +init_db $1 diff --git a/install_monetdb.sh b/install_monetdb.sh new file mode 100755 index 0000000000000000000000000000000000000000..6b65508bbf6b9daadcd7830b9b04194e7ca9c750 --- /dev/null +++ b/install_monetdb.sh @@ -0,0 +1,42 @@ +#!/bin/bash + +################################################################################ +# # +# This script installs the specified MonetDB release. # +# Provide the script with the release name, version number and directory # +# where the database will be installed. # +# # +# Run as (for example): # +# ./install_monetdb.sh Dec2016-SP5 11.25.23 /scratch/myname/databases # +# # +################################################################################ + +rname=$1 +mname=${rname} +mversion=$2 +mdbname=MonetDB-${mname} +mdbversion=MonetDB-${mversion} +dbdir=$3 + +mkdir -p $dbdir +cd $dbdir +rm -Rf $mdbname +# Used for releases: +wget http://www.monetdb.org/downloads/sources/${mname}/${mdbversion}.tar.bz2 +# Used for test-releases: +#wget http://dev.monetdb.org/downloads/testing/sources/${rname}/${mdbversion}.tar.bz2 +tar -xvjf ${mdbversion}.tar.bz2 + +mkdir -p $dbdir/$mdbname/build +cd $dbdir/$mdbname/build +# On Ubuntu this has to run with --disable-strict --disable-geom, others may use --enable-strict +# To copy the ubuntu environment we use these settings (although the latter are for Fedora) +#../$mdbversion/configure --prefix=$dbdir/$mdbname --disable-optimize --enable-debug --enable-assert --disable-strict --disable-geom 2>&1 | tee configure.output.log +../../$mdbversion/configure --prefix=$dbdir/$mdbname --enable-optimize --disable-debug --disable-assert --disable-strict --disable-geom --enable-fits 2>&1 | tee configure.output.log +make 2>&1 | tee make.output.log +make install + +echo +echo "Don't forget to set the PATHs in your ~/.bashrc" +echo "READY" + diff --git a/load_lsst_data.py b/load_lsst_data.py new file mode 100644 index 0000000000000000000000000000000000000000..19ccc0e7a8eafbc04c9638ef2543870acf8ddbce --- /dev/null +++ b/load_lsst_data.py @@ -0,0 +1,97 @@ +import os, sys, time, logging +import glob, socket, subprocess +from os.path import basename + +from pymonetdb.sql.connections import Connection as connect +from pymonetdb.exceptions import Error as DBError + +import lsst_schema_queries as sch +import lsst_queries as q + +####################################### + +def main(): + dbport = 53000 + dbrelease = "Dec2016" + dbversion = "SP5" + dbname = "lsst_napels" + + node = socket.gethostname().split('.')[0] + + logfile = 'log/' + node + "_lsst_" + dbrelease + '_' + dbversion + "_" + time.strftime("%Y%m%d-%H%M%S") + ".log" + logging.basicConfig(filename=logfile, filemode='w', format='%(message)s', level=logging.INFO) + logging.info('Started') + print "DB (%s) logging in file %s" % (dbname, logfile) + + csvdir = "/export/scratch2/bscheers/lsst/csvs" + csvfiles = sorted(glob.glob(csvdir + '/S*_*.csv')) + print "len(csvfiles) = %s" % (len(csvfiles)) + + p = subprocess.Popen(["./init_db.sh", dbname], stdout=subprocess.PIPE) + output, err = p.communicate() + print "O:",output + if err is not None: + print "E:",err + + hname = 'localhost' + uname = 'monetdb' + pword = 'monetdb' + + tstart = time.time() + + conn = connect(hostname=hname, port=dbport, username=uname, password=pword, database=dbname) + + fcnt = 0 + scnt, fscnt, ocnt, ofocnt = 0, 0, 0, 0 + for f in csvfiles: + fcnt += 1 + t0 = time.time() + tabname = basename(f)[:-4].lower() + print "====>\n#### Loading CSV file nr %s: %s ####" % (fcnt, basename(f)) + if tabname.startswith("source_"): + scnt += 1 + sch.create_table(conn, basename(f), q=1) + q.load_csv(conn, f, q=2) # executes queries 2 & 3 + if scnt == 1: + sch.create_merge_table(conn, basename(f), q=4) + sch.alter_merge_table_add_pkey(conn, f, q=5) + sch.alter_merge_table_add_table(conn, basename(f), q=6) + elif tabname.startswith("forcedsource_"): + fscnt += 1 + sch.create_table(conn, basename(f), q=7) + q.load_csv(conn, f, q=8) + if fscnt == 1: + sch.create_merge_table(conn, basename(f), q=10) + sch.alter_merge_table_add_pkey(conn, f, q=11) + sch.alter_merge_table_add_table(conn, basename(f), q=12) + elif tabname.startswith("object_"): + ocnt += 1 + sch.create_table(conn, basename(f), q=13) + q.load_csv(conn, f, q=14) + if ocnt == 1: + sch.create_merge_table(conn, basename(f), q=16) + sch.alter_merge_table_add_pkey(conn, f, q=17) + sch.alter_merge_table_add_table(conn, basename(f), q=18) + elif tabname.startswith("objectfulloverlap_"): + ofocnt += 1 + sch.create_table(conn, basename(f), q=19) + q.load_csv(conn, f, q=20) + if ofocnt == 1: + sch.create_merge_table(conn, basename(f), q=22) + sch.alter_merge_table_add_pkey(conn, f, q=23) + sch.alter_merge_table_add_table(conn, basename(f), q=24) + else: + sys.exit("No valid tabname: %s" % (tabname)) + + print "# L : Total copy-into time for this file: %s seconds" % (str(round(time.time() - t0, 3))) + print "# L : Total processing time: %s seconds" % (str(round(time.time() - tstart, 3))) + + conn.close() + print "# DB: Connection closed" + print "\n#### Total elapsed time: %s seconds ####\n" % (str(round(time.time() - tstart, 3))) + logging.info('Ended') + + print "Check log file: %s" % (logfile) + +if __name__ == '__main__': + main() diff --git a/log/.gitignore b/log/.gitignore new file mode 100644 index 0000000000000000000000000000000000000000..5e7d2734cfc60289debf74293817c0a8f572ff32 --- /dev/null +++ b/log/.gitignore @@ -0,0 +1,4 @@ +# Ignore everything in this directory +* +# Except this file +!.gitignore diff --git a/lsst_queries.py b/lsst_queries.py new file mode 100644 index 0000000000000000000000000000000000000000..bddc42a84b0b855e232a1f5fa1aee5aecaa8d45d --- /dev/null +++ b/lsst_queries.py @@ -0,0 +1,95 @@ +import time, logging +from os.path import basename + +from pymonetdb.exceptions import Error as DBError + +def loadcsv_drop_pkey(conn, csvfile, q): + """ + """ + tabname = basename(csvfile)[:-4].lower() + pkey = tabname + "_id_pkey" + + with open('loadcsv_drop_pkey.sql', 'r') as f: + qcsv = f.read() + params = {'tabname': tabname + ,'pkey': pkey} + query = qcsv % (params) + #print "Query:\n%s" % (query) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + print "# AL: (Q%s: %s ms) Altered table %s dropped pkey %s" % (q, t, tabname, pkey) + logging.info("L;I?;Q%s;T%s;N0" % (q, t)) + cursor.close() + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def alter_tabname_add_pkey(conn, csvfile, q): + """Set a primary key for the table + """ + tabname = basename(csvfile)[:-4].lower() + if tabname.startswith("source_"): + pkey = "id" + elif tabname.startswith("forcedsource_"): + pkey = "deepSourceId,scienceCcdExposureId" + elif tabname.startswith("object_"): + pkey = "deepSourceId" + elif tabname.startswith("objectfulloverlap_"): + return + + with open('sql/alter_tabname_add_pkey.sql', 'r') as f: + qcsv = f.read() + params = {'tabname': tabname + ,'pkey': pkey} + query = qcsv % (params) + #print "query = %s" % (query) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + print "# AT: (Q%s: %s ms) Altered table %s added pkey" % (q, t, tabname) + logging.info("L;I0;Q%s;T%s;N0" % (q, t)) + cursor.close() + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def load_csv_copyinto(conn, csvfile, q): + """Use copy into to load the csv file + """ + tabname = basename(csvfile)[:-4].lower() + with open('sql/loadcsv_copyinto.sql', 'r') as f: + qcsv = f.read() + params = {'tabname': tabname + ,'csvfile': csvfile} + query = qcsv % (params) + #print "Query:\n%s" % (query) + + try: + cursor = conn.cursor() + t0 = time.time() + n = cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + print "# CI: (Q%s: %s ms) Copied %s records into table %s" % (q, t, n, tabname) + logging.info("L;I0;Q%s;T%s;N%s" % (q, t, n)) + cursor.close() + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def load_csv(conn, csvfile, q): + """Load the csvfile, then set the primary key + """ + #loadcsv_drop_pkey(conn, csvfile, q+=?) + load_csv_copyinto(conn, csvfile, q) + alter_tabname_add_pkey(conn, csvfile, q+1) + diff --git a/lsst_schema_queries.py b/lsst_schema_queries.py new file mode 100644 index 0000000000000000000000000000000000000000..481b7c2b0fccc6547b267a31e000897511c8d89b --- /dev/null +++ b/lsst_schema_queries.py @@ -0,0 +1,116 @@ +import sys, time, logging, subprocess +from os.path import basename + +from pymonetdb.sql.connections import Connection as connect +from pymonetdb.exceptions import Error as DBError + +def create_table(conn, csvfile, q): + """General function to create either a source, forcedsource, + object or objectfulloverlap table. The corresponding sql is + executed. + """ + + tabname = basename(csvfile)[:-4].lower() + fname = tabname.split("_")[0] + sqlfile = "sql/create_" + fname + ".sql" + with open(sqlfile, 'r') as f: + qu = f.read() + params = {'tabname': tabname} + query = qu % (params) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + cursor.close() + print "# CT: (Q%s: %s ms) Created table: %s" % (q, t, tabname) + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def create_merge_table(conn, csvfile, q): + """General function to create a merge table + """ + + tabname = basename(csvfile)[:-4].lower() + merge_tabname = tabname.split("_")[0] + with open('sql/create_merge_tabname.sql', 'r') as f: + qu = f.read() + params = {'merge_tabname': merge_tabname + ,'tabname': tabname} + query = qu % (params) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + cursor.close() + print "# CM: (Q%s: %s ms) Created merge table %s based upon: %s" \ + % (q, t, merge_tabname, tabname) + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def alter_merge_table_add_table(conn, csvfile, q): + """Add a (sub)table to the merge table + """ + + tabname = basename(csvfile)[:-4].lower() + merge_tabname = tabname.split("_")[0] + with open('sql/alter_merge_table_add_table.sql', 'r') as f: + qu = f.read() + params = {'merge_tabname': merge_tabname + ,'tabname': tabname} + query = qu % (params) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + cursor.close() + print "# AM: (Q%s: %s ms) Alter merge table %s add table %s" \ + % (q, t, merge_tabname, tabname) + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def alter_merge_table_add_pkey(conn, csvfile, q): + """Add a primary key to the merge table + """ + + tabname = basename(csvfile)[:-4].lower() + merge_tabname = tabname.split("_")[0] + if tabname.startswith("source_"): + pkey = "id" + elif tabname.startswith("forcedsource_"): + pkey = "deepSourceId,scienceCcdExposureId" + elif tabname.startswith("object_"): + pkey = "deepSourceId" + elif tabname.startswith("objectfulloverlap_"): + return + # NOTE: this sql is being used in load csv part as well (lsst_queries.py) + with open('sql/alter_tabname_add_pkey.sql', 'r') as f: + qu = f.read() + params = {'tabname': merge_tabname + ,'pkey': pkey} + query = qu % (params) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + cursor.close() + print "# AM: (Q%s: %s ms) Alter merge table %s add primary key (%s)" \ + % (q, t, merge_tabname, pkey) + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + diff --git a/plot_baselines.py b/plot_baselines.py new file mode 100644 index 0000000000000000000000000000000000000000..c895be85719c6794ccf2d9166f9b23a17a078d5e --- /dev/null +++ b/plot_baselines.py @@ -0,0 +1,145 @@ +#!/usr/bin/python + +import matplotlib as mpl +mpl.use('Agg') +import os, errno, time, sys, pylab +from scipy import * +from scipy import optimize +from scipy import special +from scipy import stats +#import numpy as np +import matplotlib.cm as cm +import matplotlib.ticker as ticker +from datetime import datetime +import logging, csv +from os.path import basename, exists + +plotdir = os.path.abspath('.') + '/results' +if not os.path.exists(plotdir): + os.makedirs(plotdir) + print "Created plotdir: %s" % (plotdir) +logdir = os.path.abspath('.') + '/log' + +def stdev(v): + """ + Return the Bessel corrected standard deviation of a series of measurements + """ + return sqrt(len(v) * (mean(square(v)) - mean(v)**2) / (len(v) - 1)) + +def mysql_querytimes(): + """ + """ + # The s15 mysql query timing results taken from + # https://confluence.lsstcorp.org/display/DM/S15+Large+Scale+Tests + # Values on website are reported in seconds + counts = [47.75, 40.99, 48.33] + shortrunning = [0.09, 0.33] + fullscans = [247.61, 244.24, 1088.09, 1077.38, 898.61] + joins = [1381.44, 1291.38] + neighbor = [676.02] + mysqls15 = asarray(counts + shortrunning + fullscans + joins + neighbor) + #tms = [ts * 1000 for ts in mysqls15] + return mysqls15 + +def monetdb_querytimes(qlog): + """ + """ + # From the qlog name, we can deduce the query number + # and run type + qtime = [] + with open(qlog, 'rb') as csvfile: + qreader = csv.reader(csvfile, delimiter=';') + for row in qreader: + run = int(row[0]) + qtime.append(float(row[1])) # unit is ms + #for i in range(len(qtime)): + # print "%s: qtime = %s" % (i, qtime[i]) + return mean(qtime), stdev(qtime) + +def plot_results(): + """ + """ + + logdir = 'sql/s15/log' + plotdir = '.' + plotfiles = [] + + node = "stones10" + dbversion = "dec2016sp1" + + # We have hot and cold runs + runtype = ['hot', 'cold'] + #runtype = ['hot'] + # We divide the queries in five groups + counts = ['q01', 'q02', 'q03'] + shortrunning = ['q04', 'q05'] + fullscans = ['q06', 'q07', 'q08', 'q09', 'q10'] + joins = ['q11', 'q12'] + neighbor = ['q13'] + queries = asarray(counts + shortrunning + fullscans + joins + neighbor) + mysqls15 = mysql_querytimes() + + monetdbs15 = {} + for temp in runtype: + monetdbs15[temp] = {} + monetdbs15[temp]['qt_avg'] = [] + monetdbs15[temp]['qt_std'] = [] + for qnr in queries: + qlog = logdir + "/%s.%s.%s.%s.log" % (qnr, temp, node, dbversion) + #print "qlog = %s" % (qlog) + if not exists(qlog): + sys.exit("Run timings. Log file %s does not exist (yet)." % (qlog)) + qt_avg, qt_std = monetdb_querytimes(qlog) + print "%s qnr: %s: qt_avg = %s; qt_std = %s" % (temp, qnr, qt_avg, qt_std) + monetdbs15[temp]['qt_avg'].append(qt_avg) + monetdbs15[temp]['qt_std'].append(qt_std) + mhot_t = [t * 0.001 for t in monetdbs15['hot']['qt_avg']] + mhot_s = [s * 0.001 for s in monetdbs15['hot']['qt_std']] + mcold_t = [t * 0.001 for t in monetdbs15['cold']['qt_avg']] + mcold_s = [s * 0.001 for s in monetdbs15['cold']['qt_std']] + + #print "monetdbs15 = %s" % (monetdbs15) + fig = pylab.figure(figsize=(28,8)) + ax = fig.add_subplot(111) + + width = 0.2 + ekw = dict(ecolor='k', lw=2, capsize=5, capthick=2) + r1 = ax.bar(arange(len(mysqls15)), mysqls15, width, color='gray' \ + ,error_kw=ekw,label='MySQL') + r2 = ax.bar(arange(len(mcold_t)) + width, mcold_t, width, yerr=mcold_s, color='dodgerblue' \ + ,error_kw=ekw,label='MonetDB cold') + r3 = ax.bar(arange(len(mhot_t)) + 2 * width, mhot_t, width, yerr=mhot_s, color='red' \ + ,error_kw=ekw,label='MonetDB hot') + ax.set_ylabel('Time [s]', fontsize='25') + ax.set_xticks(arange(len(mysqls15)) + width) + ax.set_xticklabels([q.upper() for q in queries]) + #ax.set_ylim(ymin=10, ymax=35000) + ax.set_ylim(ymin=0.001, ymax=10000) + ax.set_yscale("log") + #ax.legend(loc='upper right') + ax.legend(loc='upper left', fontsize='25') + + for i in range(len(ax.get_xticklabels())): + #ax.get_xticklabels()[i].set_size('xx-large') + ax.get_xticklabels()[i].set_size('25') + for i in range(len(ax.get_yticklabels())): + #ax.get_yticklabels()[i].set_size('xx-large') + ax.get_yticklabels()[i].set_size('25') + pylab.grid(True) + + fname = 'baseline_lsst_queries.eps' + + plotfile = plotdir + '/' + fname + plotfiles.append(plotfile) + pylab.savefig(plotfile, dpi=400, bbox_inches='tight') + print plotfile + + return plotfile + +def main(): + #query = sys.argv[1] + plot_results() + +if __name__ == '__main__': + main() + diff --git a/sql/alter_merge_table_add_table.sql b/sql/alter_merge_table_add_table.sql new file mode 100644 index 0000000000000000000000000000000000000000..54640bbebcdd4a9ebc7cafa35fa7bc4462b51488 --- /dev/null +++ b/sql/alter_merge_table_add_table.sql @@ -0,0 +1 @@ +ALTER TABLE %(merge_tabname)s ADD TABLE %(tabname)s; diff --git a/sql/alter_tabname_add_pkey.sql b/sql/alter_tabname_add_pkey.sql new file mode 100644 index 0000000000000000000000000000000000000000..68b598055fb97c7182d285ce89a5a4138ca846b8 --- /dev/null +++ b/sql/alter_tabname_add_pkey.sql @@ -0,0 +1 @@ +ALTER TABLE %(tabname)s ADD PRIMARY KEY (%(pkey)s); diff --git a/sql/create_forcedsource.sql b/sql/create_forcedsource.sql new file mode 100644 index 0000000000000000000000000000000000000000..d75d747006f8189c560ed365938b2c9a6af7efca --- /dev/null +++ b/sql/create_forcedsource.sql @@ -0,0 +1,17 @@ +CREATE TABLE %(tabname)s + (deepSourceId BIGINT NOT NULL + ,scienceCcdExposureId BIGINT NOT NULL + ,psfFlux float DEFAULT NULL + ,psfFluxSigma float DEFAULT NULL + ,flagBadMeasCentroid BOOLEAN NOT NULL + ,flagPixEdge BOOLEAN NOT NULL + ,flagPixInterpAny BOOLEAN NOT NULL + ,flagPixInterpCen BOOLEAN NOT NULL + ,flagPixSaturAny BOOLEAN NOT NULL + ,flagPixSaturCen BOOLEAN NOT NULL + ,flagBadPsfFlux BOOLEAN NOT NULL + ,chunkId INT NOT NULL + ,subChunkId INT NOT NULL +-- ,PRIMARY KEY (deepSourceIdscienceCcdExposureId) + ) +; diff --git a/sql/create_merge_tabname.sql b/sql/create_merge_tabname.sql new file mode 100644 index 0000000000000000000000000000000000000000..7ffa4579dc20870cf7b065dcafd0643925af6644 --- /dev/null +++ b/sql/create_merge_tabname.sql @@ -0,0 +1 @@ +CREATE MERGE TABLE %(merge_tabname)s AS SELECT * FROM %(tabname)s WITH NO DATA; diff --git a/sql/create_object.sql b/sql/create_object.sql new file mode 100644 index 0000000000000000000000000000000000000000..6839d53b4ef3e48dd5f1982f8d512936735dc03a --- /dev/null +++ b/sql/create_object.sql @@ -0,0 +1,241 @@ +CREATE TABLE %(tabname)s + (deepSourceId BIGINT NOT NULL + ,ra double NOT NULL + ,decl double NOT NULL + ,raVar double DEFAULT NULL + ,declVar double DEFAULT NULL + ,radeclCov double DEFAULT NULL + ,chunkId INT NOT NULL + ,subChunkId INT NOT NULL + ,u_psfFlux double DEFAULT NULL + ,u_psfFluxSigma double DEFAULT NULL + ,u_apFlux double DEFAULT NULL + ,u_apFluxSigma double DEFAULT NULL + ,u_modelFlux double DEFAULT NULL + ,u_modelFluxSigma double DEFAULT NULL + ,u_instFlux double DEFAULT NULL + ,u_instFluxSigma double DEFAULT NULL + ,u_apCorrection double DEFAULT NULL + ,u_apCorrectionSigma double DEFAULT NULL + ,u_shapeIx double DEFAULT NULL + ,u_shapeIy double DEFAULT NULL + ,u_shapeIxVar double DEFAULT NULL + ,u_shapeIyVar double DEFAULT NULL + ,u_shapeIxIyCov double DEFAULT NULL + ,u_shapeIxx double DEFAULT NULL + ,u_shapeIyy double DEFAULT NULL + ,u_shapeIxy double DEFAULT NULL + ,u_shapeIxxVar double DEFAULT NULL + ,u_shapeIyyVar double DEFAULT NULL + ,u_shapeIxyVar double DEFAULT NULL + ,u_shapeIxxIyyCov double DEFAULT NULL + ,u_shapeIxxIxyCov double DEFAULT NULL + ,u_shapeIyyIxyCov double DEFAULT NULL + ,u_extendedness double DEFAULT NULL + ,u_flagNegative BOOLEAN DEFAULT NULL + ,u_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,u_flagPixEdge BOOLEAN DEFAULT NULL + ,u_flagPixInterpAny BOOLEAN DEFAULT NULL + ,u_flagPixInterpCen BOOLEAN DEFAULT NULL + ,u_flagPixSaturAny BOOLEAN DEFAULT NULL + ,u_flagPixSaturCen BOOLEAN DEFAULT NULL + ,u_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,u_flagBadApFlux BOOLEAN DEFAULT NULL + ,u_flagBadModelFlux BOOLEAN DEFAULT NULL + ,u_flagBadInstFlux BOOLEAN DEFAULT NULL + ,u_flagBadCentroid BOOLEAN DEFAULT NULL + ,u_flagBadShape BOOLEAN DEFAULT NULL + ,g_psfFlux double DEFAULT NULL + ,g_psfFluxSigma double DEFAULT NULL + ,g_apFlux double DEFAULT NULL + ,g_apFluxSigma double DEFAULT NULL + ,g_modelFlux double DEFAULT NULL + ,g_modelFluxSigma double DEFAULT NULL + ,g_instFlux double DEFAULT NULL + ,g_instFluxSigma double DEFAULT NULL + ,g_apCorrection double DEFAULT NULL + ,g_apCorrectionSigma double DEFAULT NULL + ,g_shapeIx double DEFAULT NULL + ,g_shapeIy double DEFAULT NULL + ,g_shapeIxVar double DEFAULT NULL + ,g_shapeIyVar double DEFAULT NULL + ,g_shapeIxIyCov double DEFAULT NULL + ,g_shapeIxx double DEFAULT NULL + ,g_shapeIyy double DEFAULT NULL + ,g_shapeIxy double DEFAULT NULL + ,g_shapeIxxVar double DEFAULT NULL + ,g_shapeIyyVar double DEFAULT NULL + ,g_shapeIxyVar double DEFAULT NULL + ,g_shapeIxxIyyCov double DEFAULT NULL + ,g_shapeIxxIxyCov double DEFAULT NULL + ,g_shapeIyyIxyCov double DEFAULT NULL + ,g_extendedness double DEFAULT NULL + ,g_flagNegative BOOLEAN DEFAULT NULL + ,g_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,g_flagPixEdge BOOLEAN DEFAULT NULL + ,g_flagPixInterpAny BOOLEAN DEFAULT NULL + ,g_flagPixInterpCen BOOLEAN DEFAULT NULL + ,g_flagPixSaturAny BOOLEAN DEFAULT NULL + ,g_flagPixSaturCen BOOLEAN DEFAULT NULL + ,g_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,g_flagBadApFlux BOOLEAN DEFAULT NULL + ,g_flagBadModelFlux BOOLEAN DEFAULT NULL + ,g_flagBadInstFlux BOOLEAN DEFAULT NULL + ,g_flagBadCentroid BOOLEAN DEFAULT NULL + ,g_flagBadShape BOOLEAN DEFAULT NULL + ,r_psfFlux double DEFAULT NULL + ,r_psfFluxSigma double DEFAULT NULL + ,r_apFlux double DEFAULT NULL + ,r_apFluxSigma double DEFAULT NULL + ,r_modelFlux double DEFAULT NULL + ,r_modelFluxSigma double DEFAULT NULL + ,r_instFlux double DEFAULT NULL + ,r_instFluxSigma double DEFAULT NULL + ,r_apCorrection double DEFAULT NULL + ,r_apCorrectionSigma double DEFAULT NULL + ,r_shapeIx double DEFAULT NULL + ,r_shapeIy double DEFAULT NULL + ,r_shapeIxVar double DEFAULT NULL + ,r_shapeIyVar double DEFAULT NULL + ,r_shapeIxIyCov double DEFAULT NULL + ,r_shapeIxx double DEFAULT NULL + ,r_shapeIyy double DEFAULT NULL + ,r_shapeIxy double DEFAULT NULL + ,r_shapeIxxVar double DEFAULT NULL + ,r_shapeIyyVar double DEFAULT NULL + ,r_shapeIxyVar double DEFAULT NULL + ,r_shapeIxxIyyCov double DEFAULT NULL + ,r_shapeIxxIxyCov double DEFAULT NULL + ,r_shapeIyyIxyCov double DEFAULT NULL + ,r_extendedness double DEFAULT NULL + ,r_flagNegative BOOLEAN DEFAULT NULL + ,r_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,r_flagPixEdge BOOLEAN DEFAULT NULL + ,r_flagPixInterpAny BOOLEAN DEFAULT NULL + ,r_flagPixInterpCen BOOLEAN DEFAULT NULL + ,r_flagPixSaturAny BOOLEAN DEFAULT NULL + ,r_flagPixSaturCen BOOLEAN DEFAULT NULL + ,r_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,r_flagBadApFlux BOOLEAN DEFAULT NULL + ,r_flagBadModelFlux BOOLEAN DEFAULT NULL + ,r_flagBadInstFlux BOOLEAN DEFAULT NULL + ,r_flagBadCentroid BOOLEAN DEFAULT NULL + ,r_flagBadShape BOOLEAN DEFAULT NULL + ,i_psfFlux double DEFAULT NULL + ,i_psfFluxSigma double DEFAULT NULL + ,i_apFlux double DEFAULT NULL + ,i_apFluxSigma double DEFAULT NULL + ,i_modelFlux double DEFAULT NULL + ,i_modelFluxSigma double DEFAULT NULL + ,i_instFlux double DEFAULT NULL + ,i_instFluxSigma double DEFAULT NULL + ,i_apCorrection double DEFAULT NULL + ,i_apCorrectionSigma double DEFAULT NULL + ,i_shapeIx double DEFAULT NULL + ,i_shapeIy double DEFAULT NULL + ,i_shapeIxVar double DEFAULT NULL + ,i_shapeIyVar double DEFAULT NULL + ,i_shapeIxIyCov double DEFAULT NULL + ,i_shapeIxx double DEFAULT NULL + ,i_shapeIyy double DEFAULT NULL + ,i_shapeIxy double DEFAULT NULL + ,i_shapeIxxVar double DEFAULT NULL + ,i_shapeIyyVar double DEFAULT NULL + ,i_shapeIxyVar double DEFAULT NULL + ,i_shapeIxxIyyCov double DEFAULT NULL + ,i_shapeIxxIxyCov double DEFAULT NULL + ,i_shapeIyyIxyCov double DEFAULT NULL + ,i_extendedness double DEFAULT NULL + ,i_flagNegative BOOLEAN DEFAULT NULL + ,i_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,i_flagPixEdge BOOLEAN DEFAULT NULL + ,i_flagPixInterpAny BOOLEAN DEFAULT NULL + ,i_flagPixInterpCen BOOLEAN DEFAULT NULL + ,i_flagPixSaturAny BOOLEAN DEFAULT NULL + ,i_flagPixSaturCen BOOLEAN DEFAULT NULL + ,i_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,i_flagBadApFlux BOOLEAN DEFAULT NULL + ,i_flagBadModelFlux BOOLEAN DEFAULT NULL + ,i_flagBadInstFlux BOOLEAN DEFAULT NULL + ,i_flagBadCentroid BOOLEAN DEFAULT NULL + ,i_flagBadShape BOOLEAN DEFAULT NULL + ,z_psfFlux double DEFAULT NULL + ,z_psfFluxSigma double DEFAULT NULL + ,z_apFlux double DEFAULT NULL + ,z_apFluxSigma double DEFAULT NULL + ,z_modelFlux double DEFAULT NULL + ,z_modelFluxSigma double DEFAULT NULL + ,z_instFlux double DEFAULT NULL + ,z_instFluxSigma double DEFAULT NULL + ,z_apCorrection double DEFAULT NULL + ,z_apCorrectionSigma double DEFAULT NULL + ,z_shapeIx double DEFAULT NULL + ,z_shapeIy double DEFAULT NULL + ,z_shapeIxVar double DEFAULT NULL + ,z_shapeIyVar double DEFAULT NULL + ,z_shapeIxIyCov double DEFAULT NULL + ,z_shapeIxx double DEFAULT NULL + ,z_shapeIyy double DEFAULT NULL + ,z_shapeIxy double DEFAULT NULL + ,z_shapeIxxVar double DEFAULT NULL + ,z_shapeIyyVar double DEFAULT NULL + ,z_shapeIxyVar double DEFAULT NULL + ,z_shapeIxxIyyCov double DEFAULT NULL + ,z_shapeIxxIxyCov double DEFAULT NULL + ,z_shapeIyyIxyCov double DEFAULT NULL + ,z_extendedness double DEFAULT NULL + ,z_flagNegative BOOLEAN DEFAULT NULL + ,z_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,z_flagPixEdge BOOLEAN DEFAULT NULL + ,z_flagPixInterpAny BOOLEAN DEFAULT NULL + ,z_flagPixInterpCen BOOLEAN DEFAULT NULL + ,z_flagPixSaturAny BOOLEAN DEFAULT NULL + ,z_flagPixSaturCen BOOLEAN DEFAULT NULL + ,z_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,z_flagBadApFlux BOOLEAN DEFAULT NULL + ,z_flagBadModelFlux BOOLEAN DEFAULT NULL + ,z_flagBadInstFlux BOOLEAN DEFAULT NULL + ,z_flagBadCentroid BOOLEAN DEFAULT NULL + ,z_flagBadShape BOOLEAN DEFAULT NULL + ,y_psfFlux double DEFAULT NULL + ,y_psfFluxSigma double DEFAULT NULL + ,y_apFlux double DEFAULT NULL + ,y_apFluxSigma double DEFAULT NULL + ,y_modelFlux double DEFAULT NULL + ,y_modelFluxSigma double DEFAULT NULL + ,y_instFlux double DEFAULT NULL + ,y_instFluxSigma double DEFAULT NULL + ,y_apCorrection double DEFAULT NULL + ,y_apCorrectionSigma double DEFAULT NULL + ,y_shapeIx double DEFAULT NULL + ,y_shapeIy double DEFAULT NULL + ,y_shapeIxVar double DEFAULT NULL + ,y_shapeIyVar double DEFAULT NULL + ,y_shapeIxIyCov double DEFAULT NULL + ,y_shapeIxx double DEFAULT NULL + ,y_shapeIyy double DEFAULT NULL + ,y_shapeIxy double DEFAULT NULL + ,y_shapeIxxVar double DEFAULT NULL + ,y_shapeIyyVar double DEFAULT NULL + ,y_shapeIxyVar double DEFAULT NULL + ,y_shapeIxxIyyCov double DEFAULT NULL + ,y_shapeIxxIxyCov double DEFAULT NULL + ,y_shapeIyyIxyCov double DEFAULT NULL + ,y_extendedness double DEFAULT NULL + ,y_flagNegative BOOLEAN DEFAULT NULL + ,y_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,y_flagPixEdge BOOLEAN DEFAULT NULL + ,y_flagPixInterpAny BOOLEAN DEFAULT NULL + ,y_flagPixInterpCen BOOLEAN DEFAULT NULL + ,y_flagPixSaturAny BOOLEAN DEFAULT NULL + ,y_flagPixSaturCen BOOLEAN DEFAULT NULL + ,y_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,y_flagBadApFlux BOOLEAN DEFAULT NULL + ,y_flagBadModelFlux BOOLEAN DEFAULT NULL + ,y_flagBadInstFlux BOOLEAN DEFAULT NULL + ,y_flagBadCentroid BOOLEAN DEFAULT NULL + ,y_flagBadShape BOOLEAN DEFAULT NULL +-- ,PRIMARY KEY (deepSourceId) + ) +; + diff --git a/sql/create_objectfulloverlap.sql b/sql/create_objectfulloverlap.sql new file mode 100644 index 0000000000000000000000000000000000000000..a2e6e6a48cc449458cd706ed3c2414f11aee3387 --- /dev/null +++ b/sql/create_objectfulloverlap.sql @@ -0,0 +1,239 @@ +CREATE TABLE %(tabname)s + (deepSourceId BIGINT NOT NULL + ,ra double NOT NULL + ,decl double NOT NULL + ,raVar double DEFAULT NULL + ,declVar double DEFAULT NULL + ,radeclCov double DEFAULT NULL + ,chunkId INT NOT NULL + ,subChunkId INT NOT NULL + ,u_psfFlux double DEFAULT NULL + ,u_psfFluxSigma double DEFAULT NULL + ,u_apFlux double DEFAULT NULL + ,u_apFluxSigma double DEFAULT NULL + ,u_modelFlux double DEFAULT NULL + ,u_modelFluxSigma double DEFAULT NULL + ,u_instFlux double DEFAULT NULL + ,u_instFluxSigma double DEFAULT NULL + ,u_apCorrection double DEFAULT NULL + ,u_apCorrectionSigma double DEFAULT NULL + ,u_shapeIx double DEFAULT NULL + ,u_shapeIy double DEFAULT NULL + ,u_shapeIxVar double DEFAULT NULL + ,u_shapeIyVar double DEFAULT NULL + ,u_shapeIxIyCov double DEFAULT NULL + ,u_shapeIxx double DEFAULT NULL + ,u_shapeIyy double DEFAULT NULL + ,u_shapeIxy double DEFAULT NULL + ,u_shapeIxxVar double DEFAULT NULL + ,u_shapeIyyVar double DEFAULT NULL + ,u_shapeIxyVar double DEFAULT NULL + ,u_shapeIxxIyyCov double DEFAULT NULL + ,u_shapeIxxIxyCov double DEFAULT NULL + ,u_shapeIyyIxyCov double DEFAULT NULL + ,u_extendedness double DEFAULT NULL + ,u_flagNegative BOOLEAN DEFAULT NULL + ,u_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,u_flagPixEdge BOOLEAN DEFAULT NULL + ,u_flagPixInterpAny BOOLEAN DEFAULT NULL + ,u_flagPixInterpCen BOOLEAN DEFAULT NULL + ,u_flagPixSaturAny BOOLEAN DEFAULT NULL + ,u_flagPixSaturCen BOOLEAN DEFAULT NULL + ,u_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,u_flagBadApFlux BOOLEAN DEFAULT NULL + ,u_flagBadModelFlux BOOLEAN DEFAULT NULL + ,u_flagBadInstFlux BOOLEAN DEFAULT NULL + ,u_flagBadCentroid BOOLEAN DEFAULT NULL + ,u_flagBadShape BOOLEAN DEFAULT NULL + ,g_psfFlux double DEFAULT NULL + ,g_psfFluxSigma double DEFAULT NULL + ,g_apFlux double DEFAULT NULL + ,g_apFluxSigma double DEFAULT NULL + ,g_modelFlux double DEFAULT NULL + ,g_modelFluxSigma double DEFAULT NULL + ,g_instFlux double DEFAULT NULL + ,g_instFluxSigma double DEFAULT NULL + ,g_apCorrection double DEFAULT NULL + ,g_apCorrectionSigma double DEFAULT NULL + ,g_shapeIx double DEFAULT NULL + ,g_shapeIy double DEFAULT NULL + ,g_shapeIxVar double DEFAULT NULL + ,g_shapeIyVar double DEFAULT NULL + ,g_shapeIxIyCov double DEFAULT NULL + ,g_shapeIxx double DEFAULT NULL + ,g_shapeIyy double DEFAULT NULL + ,g_shapeIxy double DEFAULT NULL + ,g_shapeIxxVar double DEFAULT NULL + ,g_shapeIyyVar double DEFAULT NULL + ,g_shapeIxyVar double DEFAULT NULL + ,g_shapeIxxIyyCov double DEFAULT NULL + ,g_shapeIxxIxyCov double DEFAULT NULL + ,g_shapeIyyIxyCov double DEFAULT NULL + ,g_extendedness double DEFAULT NULL + ,g_flagNegative BOOLEAN DEFAULT NULL + ,g_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,g_flagPixEdge BOOLEAN DEFAULT NULL + ,g_flagPixInterpAny BOOLEAN DEFAULT NULL + ,g_flagPixInterpCen BOOLEAN DEFAULT NULL + ,g_flagPixSaturAny BOOLEAN DEFAULT NULL + ,g_flagPixSaturCen BOOLEAN DEFAULT NULL + ,g_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,g_flagBadApFlux BOOLEAN DEFAULT NULL + ,g_flagBadModelFlux BOOLEAN DEFAULT NULL + ,g_flagBadInstFlux BOOLEAN DEFAULT NULL + ,g_flagBadCentroid BOOLEAN DEFAULT NULL + ,g_flagBadShape BOOLEAN DEFAULT NULL + ,r_psfFlux double DEFAULT NULL + ,r_psfFluxSigma double DEFAULT NULL + ,r_apFlux double DEFAULT NULL + ,r_apFluxSigma double DEFAULT NULL + ,r_modelFlux double DEFAULT NULL + ,r_modelFluxSigma double DEFAULT NULL + ,r_instFlux double DEFAULT NULL + ,r_instFluxSigma double DEFAULT NULL + ,r_apCorrection double DEFAULT NULL + ,r_apCorrectionSigma double DEFAULT NULL + ,r_shapeIx double DEFAULT NULL + ,r_shapeIy double DEFAULT NULL + ,r_shapeIxVar double DEFAULT NULL + ,r_shapeIyVar double DEFAULT NULL + ,r_shapeIxIyCov double DEFAULT NULL + ,r_shapeIxx double DEFAULT NULL + ,r_shapeIyy double DEFAULT NULL + ,r_shapeIxy double DEFAULT NULL + ,r_shapeIxxVar double DEFAULT NULL + ,r_shapeIyyVar double DEFAULT NULL + ,r_shapeIxyVar double DEFAULT NULL + ,r_shapeIxxIyyCov double DEFAULT NULL + ,r_shapeIxxIxyCov double DEFAULT NULL + ,r_shapeIyyIxyCov double DEFAULT NULL + ,r_extendedness double DEFAULT NULL + ,r_flagNegative BOOLEAN DEFAULT NULL + ,r_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,r_flagPixEdge BOOLEAN DEFAULT NULL + ,r_flagPixInterpAny BOOLEAN DEFAULT NULL + ,r_flagPixInterpCen BOOLEAN DEFAULT NULL + ,r_flagPixSaturAny BOOLEAN DEFAULT NULL + ,r_flagPixSaturCen BOOLEAN DEFAULT NULL + ,r_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,r_flagBadApFlux BOOLEAN DEFAULT NULL + ,r_flagBadModelFlux BOOLEAN DEFAULT NULL + ,r_flagBadInstFlux BOOLEAN DEFAULT NULL + ,r_flagBadCentroid BOOLEAN DEFAULT NULL + ,r_flagBadShape BOOLEAN DEFAULT NULL + ,i_psfFlux double DEFAULT NULL + ,i_psfFluxSigma double DEFAULT NULL + ,i_apFlux double DEFAULT NULL + ,i_apFluxSigma double DEFAULT NULL + ,i_modelFlux double DEFAULT NULL + ,i_modelFluxSigma double DEFAULT NULL + ,i_instFlux double DEFAULT NULL + ,i_instFluxSigma double DEFAULT NULL + ,i_apCorrection double DEFAULT NULL + ,i_apCorrectionSigma double DEFAULT NULL + ,i_shapeIx double DEFAULT NULL + ,i_shapeIy double DEFAULT NULL + ,i_shapeIxVar double DEFAULT NULL + ,i_shapeIyVar double DEFAULT NULL + ,i_shapeIxIyCov double DEFAULT NULL + ,i_shapeIxx double DEFAULT NULL + ,i_shapeIyy double DEFAULT NULL + ,i_shapeIxy double DEFAULT NULL + ,i_shapeIxxVar double DEFAULT NULL + ,i_shapeIyyVar double DEFAULT NULL + ,i_shapeIxyVar double DEFAULT NULL + ,i_shapeIxxIyyCov double DEFAULT NULL + ,i_shapeIxxIxyCov double DEFAULT NULL + ,i_shapeIyyIxyCov double DEFAULT NULL + ,i_extendedness double DEFAULT NULL + ,i_flagNegative BOOLEAN DEFAULT NULL + ,i_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,i_flagPixEdge BOOLEAN DEFAULT NULL + ,i_flagPixInterpAny BOOLEAN DEFAULT NULL + ,i_flagPixInterpCen BOOLEAN DEFAULT NULL + ,i_flagPixSaturAny BOOLEAN DEFAULT NULL + ,i_flagPixSaturCen BOOLEAN DEFAULT NULL + ,i_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,i_flagBadApFlux BOOLEAN DEFAULT NULL + ,i_flagBadModelFlux BOOLEAN DEFAULT NULL + ,i_flagBadInstFlux BOOLEAN DEFAULT NULL + ,i_flagBadCentroid BOOLEAN DEFAULT NULL + ,i_flagBadShape BOOLEAN DEFAULT NULL + ,z_psfFlux double DEFAULT NULL + ,z_psfFluxSigma double DEFAULT NULL + ,z_apFlux double DEFAULT NULL + ,z_apFluxSigma double DEFAULT NULL + ,z_modelFlux double DEFAULT NULL + ,z_modelFluxSigma double DEFAULT NULL + ,z_instFlux double DEFAULT NULL + ,z_instFluxSigma double DEFAULT NULL + ,z_apCorrection double DEFAULT NULL + ,z_apCorrectionSigma double DEFAULT NULL + ,z_shapeIx double DEFAULT NULL + ,z_shapeIy double DEFAULT NULL + ,z_shapeIxVar double DEFAULT NULL + ,z_shapeIyVar double DEFAULT NULL + ,z_shapeIxIyCov double DEFAULT NULL + ,z_shapeIxx double DEFAULT NULL + ,z_shapeIyy double DEFAULT NULL + ,z_shapeIxy double DEFAULT NULL + ,z_shapeIxxVar double DEFAULT NULL + ,z_shapeIyyVar double DEFAULT NULL + ,z_shapeIxyVar double DEFAULT NULL + ,z_shapeIxxIyyCov double DEFAULT NULL + ,z_shapeIxxIxyCov double DEFAULT NULL + ,z_shapeIyyIxyCov double DEFAULT NULL + ,z_extendedness double DEFAULT NULL + ,z_flagNegative BOOLEAN DEFAULT NULL + ,z_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,z_flagPixEdge BOOLEAN DEFAULT NULL + ,z_flagPixInterpAny BOOLEAN DEFAULT NULL + ,z_flagPixInterpCen BOOLEAN DEFAULT NULL + ,z_flagPixSaturAny BOOLEAN DEFAULT NULL + ,z_flagPixSaturCen BOOLEAN DEFAULT NULL + ,z_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,z_flagBadApFlux BOOLEAN DEFAULT NULL + ,z_flagBadModelFlux BOOLEAN DEFAULT NULL + ,z_flagBadInstFlux BOOLEAN DEFAULT NULL + ,z_flagBadCentroid BOOLEAN DEFAULT NULL + ,z_flagBadShape BOOLEAN DEFAULT NULL + ,y_psfFlux double DEFAULT NULL + ,y_psfFluxSigma double DEFAULT NULL + ,y_apFlux double DEFAULT NULL + ,y_apFluxSigma double DEFAULT NULL + ,y_modelFlux double DEFAULT NULL + ,y_modelFluxSigma double DEFAULT NULL + ,y_instFlux double DEFAULT NULL + ,y_instFluxSigma double DEFAULT NULL + ,y_apCorrection double DEFAULT NULL + ,y_apCorrectionSigma double DEFAULT NULL + ,y_shapeIx double DEFAULT NULL + ,y_shapeIy double DEFAULT NULL + ,y_shapeIxVar double DEFAULT NULL + ,y_shapeIyVar double DEFAULT NULL + ,y_shapeIxIyCov double DEFAULT NULL + ,y_shapeIxx double DEFAULT NULL + ,y_shapeIyy double DEFAULT NULL + ,y_shapeIxy double DEFAULT NULL + ,y_shapeIxxVar double DEFAULT NULL + ,y_shapeIyyVar double DEFAULT NULL + ,y_shapeIxyVar double DEFAULT NULL + ,y_shapeIxxIyyCov double DEFAULT NULL + ,y_shapeIxxIxyCov double DEFAULT NULL + ,y_shapeIyyIxyCov double DEFAULT NULL + ,y_extendedness double DEFAULT NULL + ,y_flagNegative BOOLEAN DEFAULT NULL + ,y_flagBadMeasCentroid BOOLEAN DEFAULT NULL + ,y_flagPixEdge BOOLEAN DEFAULT NULL + ,y_flagPixInterpAny BOOLEAN DEFAULT NULL + ,y_flagPixInterpCen BOOLEAN DEFAULT NULL + ,y_flagPixSaturAny BOOLEAN DEFAULT NULL + ,y_flagPixSaturCen BOOLEAN DEFAULT NULL + ,y_flagBadPsfFlux BOOLEAN DEFAULT NULL + ,y_flagBadApFlux BOOLEAN DEFAULT NULL + ,y_flagBadModelFlux BOOLEAN DEFAULT NULL + ,y_flagBadInstFlux BOOLEAN DEFAULT NULL + ,y_flagBadCentroid BOOLEAN DEFAULT NULL + ,y_flagBadShape BOOLEAN DEFAULT NULL + ) +; diff --git a/sql/create_source.sql b/sql/create_source.sql new file mode 100644 index 0000000000000000000000000000000000000000..008009f15f72d57d454edc76fb3bff11673219a2 --- /dev/null +++ b/sql/create_source.sql @@ -0,0 +1,92 @@ +CREATE TABLE %(tabname)s + (id BIGINT NOT NULL + ,coord_ra double DEFAULT NULL + ,coord_decl double DEFAULT NULL + ,coord_htmId20 BIGINT DEFAULT NULL + ,parent BIGINT DEFAULT NULL + ,flags_badcentroid BOOLEAN NOT NULL + ,centroid_sdss_x double DEFAULT NULL + ,centroid_sdss_y double DEFAULT NULL + ,centroid_sdss_xVar double DEFAULT NULL + ,centroid_sdss_xyCov double DEFAULT NULL + ,centroid_sdss_yVar double DEFAULT NULL + ,centroid_sdss_flags BOOLEAN NOT NULL + ,flags_pixel_edge BOOLEAN NOT NULL + ,flags_pixel_interpolated_any BOOLEAN NOT NULL + ,flags_pixel_interpolated_center BOOLEAN NOT NULL + ,flags_pixel_saturated_any BOOLEAN NOT NULL + ,flags_pixel_saturated_center BOOLEAN NOT NULL + ,flags_pixel_cr_any BOOLEAN NOT NULL + ,flags_pixel_cr_center BOOLEAN NOT NULL + ,centroid_naive_x double DEFAULT NULL + ,centroid_naive_y double DEFAULT NULL + ,centroid_naive_xVar double DEFAULT NULL + ,centroid_naive_xyCov double DEFAULT NULL + ,centroid_naive_yVar double DEFAULT NULL + ,centroid_naive_flags BOOLEAN NOT NULL + ,centroid_gaussian_x double DEFAULT NULL + ,centroid_gaussian_y double DEFAULT NULL + ,centroid_gaussian_xVar double DEFAULT NULL + ,centroid_gaussian_xyCov double DEFAULT NULL + ,centroid_gaussian_yVar double DEFAULT NULL + ,centroid_gaussian_flags BOOLEAN NOT NULL + ,shape_sdss_Ixx double DEFAULT NULL + ,shape_sdss_Iyy double DEFAULT NULL + ,shape_sdss_Ixy double DEFAULT NULL + ,shape_sdss_IxxVar double DEFAULT NULL + ,shape_sdss_IxxIyyCov double DEFAULT NULL + ,shape_sdss_IxxIxyCov double DEFAULT NULL + ,shape_sdss_IyyVar double DEFAULT NULL + ,shape_sdss_IyyIxyCov double DEFAULT NULL + ,shape_sdss_IxyVar double DEFAULT NULL + ,shape_sdss_flags BOOLEAN NOT NULL + ,shape_sdss_centroid_x double DEFAULT NULL + ,shape_sdss_centroid_y double DEFAULT NULL + ,shape_sdss_centroid_xVar double DEFAULT NULL + ,shape_sdss_centroid_xyCov double DEFAULT NULL + ,shape_sdss_centroid_yVar double DEFAULT NULL + ,shape_sdss_centroid_flags BOOLEAN NOT NULL + ,shape_sdss_flags_unweightedbad BOOLEAN NOT NULL + ,shape_sdss_flags_unweighted BOOLEAN NOT NULL + ,shape_sdss_flags_shift BOOLEAN NOT NULL + ,shape_sdss_flags_maxiter BOOLEAN NOT NULL + ,flux_psf double DEFAULT NULL + ,flux_psf_err double DEFAULT NULL + ,flux_psf_flags BOOLEAN NOT NULL + ,flux_psf_psffactor float DEFAULT NULL + ,flux_psf_flags_psffactor BOOLEAN NOT NULL + ,flux_psf_flags_badcorr BOOLEAN NOT NULL + ,flux_naive double DEFAULT NULL + ,flux_naive_err double DEFAULT NULL + ,flux_naive_flags BOOLEAN NOT NULL + ,flux_gaussian double DEFAULT NULL + ,flux_gaussian_err double DEFAULT NULL + ,flux_gaussian_flags BOOLEAN NOT NULL + ,flux_gaussian_psffactor float DEFAULT NULL + ,flux_gaussian_flags_psffactor BOOLEAN NOT NULL + ,flux_gaussian_flags_badcorr BOOLEAN NOT NULL + ,flux_sinc double DEFAULT NULL + ,flux_sinc_err double DEFAULT NULL + ,flux_sinc_flags BOOLEAN NOT NULL + ,centroid_record_x double DEFAULT NULL + ,centroid_record_y double DEFAULT NULL + ,classification_extendedness double DEFAULT NULL + ,aperturecorrection double DEFAULT NULL + ,aperturecorrection_err double DEFAULT NULL + ,refFlux double DEFAULT NULL + ,refFlux_err double DEFAULT NULL + ,objectId BIGINT NOT NULL + ,coord_raVar double DEFAULT NULL + ,coord_radeclCov double DEFAULT NULL + ,coord_declVar double DEFAULT NULL + ,exposure_id BIGINT NOT NULL + ,exposure_filter_id INT NOT NULL + ,exposure_time float DEFAULT NULL + ,exposure_time_mid double DEFAULT NULL + ,cluster_id BIGINT DEFAULT NULL + ,cluster_coord_ra double DEFAULT NULL + ,cluster_coord_decl double DEFAULT NULL +-- ,PRIMARY KEY (id) + ) +; + diff --git a/sql/loadcsv_copyinto.sql b/sql/loadcsv_copyinto.sql new file mode 100644 index 0000000000000000000000000000000000000000..3781c877fc6d5bfc5504129aa4b209ee50cfbf64 --- /dev/null +++ b/sql/loadcsv_copyinto.sql @@ -0,0 +1 @@ +COPY OFFSET 2 INTO %(tabname)s FROM '%(csvfile)s' USING DELIMITERS ';', '\n', '"' NULL AS 'NULL'; diff --git a/sql/s15/log/.gitignore b/sql/s15/log/.gitignore new file mode 100644 index 0000000000000000000000000000000000000000..5e7d2734cfc60289debf74293817c0a8f572ff32 --- /dev/null +++ b/sql/s15/log/.gitignore @@ -0,0 +1,4 @@ +# Ignore everything in this directory +* +# Except this file +!.gitignore diff --git a/sql/s15/q01.sql b/sql/s15/q01.sql new file mode 100644 index 0000000000000000000000000000000000000000..3d5c85627ecf3ab251d51eef1f50b0546b31694f --- /dev/null +++ b/sql/s15/q01.sql @@ -0,0 +1 @@ +select count(*) from Object; diff --git a/sql/s15/q02.sql b/sql/s15/q02.sql new file mode 100644 index 0000000000000000000000000000000000000000..6021a4d4b9672d7f84c8193b31fcbc83947154c0 --- /dev/null +++ b/sql/s15/q02.sql @@ -0,0 +1 @@ +select count(*) from Source; diff --git a/sql/s15/q03.sql b/sql/s15/q03.sql new file mode 100644 index 0000000000000000000000000000000000000000..3442169930f5ec3c8221699a132731faf509f500 --- /dev/null +++ b/sql/s15/q03.sql @@ -0,0 +1 @@ +select count(*) from ForcedSource; diff --git a/sql/s15/q04.sql b/sql/s15/q04.sql new file mode 100644 index 0000000000000000000000000000000000000000..ac3c2abc046820a5c27b6e975f9c2d2b5f2d1310 --- /dev/null +++ b/sql/s15/q04.sql @@ -0,0 +1 @@ +SELECT ra, decl FROM Object WHERE deepSourceId = 3306154155315676; diff --git a/sql/s15/q05.sql b/sql/s15/q05.sql new file mode 100644 index 0000000000000000000000000000000000000000..985030437c1d0ea5c6d6c87eef8a62964829f23a --- /dev/null +++ b/sql/s15/q05.sql @@ -0,0 +1,4 @@ +/*SELECT ra, decl FROM Object WHERE qserv_areaspec_box(0.95, 19.171, 1.0, 19.175);*/ +SELECT ra, decl FROM Object + WHERE decl between 19.171 and 19.175 + and ra between 0.95 and 1.0; diff --git a/sql/s15/q06.sql b/sql/s15/q06.sql new file mode 100644 index 0000000000000000000000000000000000000000..297b82332028b948f0bae2bb1fa34c4075a0caba --- /dev/null +++ b/sql/s15/q06.sql @@ -0,0 +1 @@ +select count(*) from Object where y_instFlux > 5; diff --git a/sql/s15/q07.sql b/sql/s15/q07.sql new file mode 100644 index 0000000000000000000000000000000000000000..3f1ab1244749d9a77783033745660869c9525696 --- /dev/null +++ b/sql/s15/q07.sql @@ -0,0 +1 @@ +select min(ra), max(ra), min(decl), max(decl) from Object; diff --git a/sql/s15/q08.sql b/sql/s15/q08.sql new file mode 100644 index 0000000000000000000000000000000000000000..7917ad181badd2495ba4f4a11d8356a0cc3693c7 --- /dev/null +++ b/sql/s15/q08.sql @@ -0,0 +1 @@ +select count(*) from Source where flux_sinc between 1 and 2; diff --git a/sql/s15/q09.sql b/sql/s15/q09.sql new file mode 100644 index 0000000000000000000000000000000000000000..30e2df2fa32f5f23d184c44728dc5479ecbe6a35 --- /dev/null +++ b/sql/s15/q09.sql @@ -0,0 +1 @@ +select count(*) from Source where flux_sinc between 2 and 3; diff --git a/sql/s15/q10.sql b/sql/s15/q10.sql new file mode 100644 index 0000000000000000000000000000000000000000..439a5bb02619a1bc585653060bf11e6c547cff9f --- /dev/null +++ b/sql/s15/q10.sql @@ -0,0 +1 @@ +select count(*) from ForcedSource where psfFlux between 0.1 and 0.2; diff --git a/sql/s15/q11.sql b/sql/s15/q11.sql new file mode 100644 index 0000000000000000000000000000000000000000..77d93a8128e26e17cc9e6b08d72744acd7a7ef58 --- /dev/null +++ b/sql/s15/q11.sql @@ -0,0 +1,3 @@ +select count(*) from Object o, Source s + WHERE o.deepSourceId=s.objectId + AND s.flux_sinc BETWEEN 0.13 AND 0.14; diff --git a/sql/s15/q12.sql b/sql/s15/q12.sql new file mode 100644 index 0000000000000000000000000000000000000000..835b2674cb8f6fc35c5c298a83cb4025306b6da5 --- /dev/null +++ b/sql/s15/q12.sql @@ -0,0 +1,3 @@ +select count(*) FROM Object o, ForcedSource f + WHERE o.deepSourceId=f.deepSourceId + AND f.psfFlux BETWEEN 0.13 AND 0.14; diff --git a/sql/s15/q13.sql b/sql/s15/q13.sql new file mode 100644 index 0000000000000000000000000000000000000000..90ad0cbfd66ca2e2ffbd167ec37ea84946a8b68d --- /dev/null +++ b/sql/s15/q13.sql @@ -0,0 +1,37 @@ +/*select count(*) from Object o1, Object o2 + where qserv_areaspec_box(90.299197, -66.468216, 98.762526, -56.412851) + and scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.015; + +From the black-box UDFs we cannot tell exactly how this traslates +to plain SQL + +Is it this: +select count(*) + from Object o1 + ,Object o2 + WHERE o1.ra BETWEEN 90.299197 AND 98.762526 + AND o1.decl BETWEEN -66.468216 AND -56.412851 + AND o1.decl BETWEEN o2.decl - 0.015 and o2.decl + 0.015 + AND o1.ra BETWEEN o2.ra - alpha(o2.decl, 0.015) AND o2.ra + alpha(o2.decl, 0.015) +; +or the one below? +*/ + +select count(*) + from Object o1 + ,(select decl - 0.015 as decl_min + ,decl + 0.015 as decl_max + ,ra - alpha(decl, 0.015) as ra_min + ,ra + alpha(decl, 0.015) as ra_max + from Object + where ra BETWEEN 90.299197 AND 98.762526 + and decl BETWEEN -66.468216 AND -56.412851 + ) o2 + WHERE o1.ra BETWEEN 90.299197 AND 98.762526 + AND o1.decl BETWEEN -66.468216 AND -56.412851 + AND o1.decl BETWEEN decl_min and decl_max + AND o1.ra BETWEEN ra_min AND ra_max +; + + +