We are running two webservers: one with mysql backend and one with qserv backend.

Webserv with mysql backend

It runs on lsst-dev, port 8661.

The catalog: smm_bremerton.

Reference catalog: database: sdss_dr9, table: fink_v5b

The measurements/forced sources tables are tiny. The reference catalog contains ~100 million rows, 82 bytes per row

Example Database Queries

List tables

curl 'http://localhost:8661/db/v0/query?sql=SHOW+TABLES+IN+smm_bremerton'
{"results": [["deepCoadd_det"], ["deepCoadd_forced_src"], ["deepCoadd_meas"], ["deepCoadd_mergeDet"], ["deepCoadd_ref"], ["icMatch"], ["icSrc"], ["run_deepCoadd_det"], ["run_deepCoadd_forced_src"], ["run_deepCoadd_meas"], ["run_deepCoadd_mergeDet"], ["run_deepCoadd_ref"], ["run_icMatch"], ["run_icSrc"], ["run_src"], ["run_srcMatch"], ["src"], ["srcMatch"]], "metadata": {"columnDefs": [{"type": "string", "name": "Tables_in_smm_bremerton"}]}}

Get row count for a table

curl 'http://localhost:8661/db/v0/query?sql=SELECT+COUNT(*)+FROM+smm_bremerton.deepCoadd_det;'
{"results": [[16542]], "metadata": {"columnDefs": [{"type": "long", "name": "COUNT(*)"}]}}

Show columns for a table

curl 'http://localhost:8661/db/v0/query?sql=desc+smm_bremerton.deepCoadd_det'
{"results": [["id", "bigint(20)", "NO", "", "None", ""], ["coord_ra", "double", "YES", "", "None", ""], ["coord_dec", "double", "YES", "", "None", ""], ["parent", "bigint(20)", "NO", "", "None", ""], ["flags_negative", "bit(1)", "NO", "", "None", ""]], "metadata": {"columnDefs": [{"type": "string", "name": "Field"}, {"type": "string", "name": "Type"}, {"type": "string", "name": "Null"}, {"type": "string", "name": "Key"}, {"type": "string", "name": "Default"}, {"type": "string", "name": "Extra"}]}}

Get forces sources for a given object

curl 'http://localhost:8661/db/v0/query?sql=select+objectId+from+smm_bremerton.deepCoadd_forced_src+where+objectId=992137445383;'
{"results": [[992137445383], [992137445383], [992137445383]], "metadata": {"columnDefs": [{"type": "long", "name": "objectId"}]}}

Get first 10 forces sources

curl 'http://localhost:8661/db/v0/query?sql=select+objectId+from+smm_bremerton.deepCoadd_forced_src+limit+10'
{"results": [[992137445377], [992137445378], [992137445379], [992137445380], [992137445381], [992137445382], [992137445383], [992137445384], [992137445385], [992137445386]], "metadata": {"columnDefs": [{"type": "long", "name": "objectId"}]}}-bash-4.1$ curl 'http://localhost:8661/db/v0/query?sql=select+objectId+from+smm_bremerton.deepCoadd_forced_src+limit+10'

Area search

curl 'http://localhost:8661/db/v0/query?sql=select+objectId,coord_ra,coord_dec+from+smm_bremerton.deepCoadd_forced_src+where+scisql_s2PtInBox(coord_ra,coord_dec,320.05,0.457,320.06,0.46)'
{"results": [[992137445522, 320.05165481864566, 0.4575557699286176], [992137445543, 320.0568092843354, 0.4579601965250946], [992137445560, 320.05896034427695, 0.45882620540907126], [992137445588, 320.05472908074375, 0.4598766596235431], [992137445522, 320.05165481864566, 0.4575557699286176], [992137445543, 320.0568092843354, 0.4579601965250946], [992137445560, 320.05896034427695, 0.45882620540907126], [992137445588, 320.05472908074375, 0.4598766596235431], [992137445522, 320.05165481864566, 0.4575557699286176], [992137445543, 320.0568092843354, 0.4579601965250946], [992137445560, 320.05896034427695, 0.45882620540907126], [992137445588, 320.05472908074375, 0.4598766596235431]], "metadata": {"columnDefs": [{"type": "long", "name": "objectId"}, {"type": "float", "name": "coord_ra"}, {"type": "float", "name": "coord_dec"}]}}

Etc. You can basically run any query that you would run on lsst10.

Example Image Queries

curl 'http://localhost:8661/image/v0/raw?ra=359.195&dec=-0.1055&filter=r' > 1.FITS
curl 'http://localhost:8661/image/v0/raw/cutoutPixel?ra=359.195&dec=-0.1055&filter=r&width=30.0&height=45.0' > 2.FITS
curl 'http://localhost:8661/image/v0/raw/cutoutPixel?ra=359.195&dec=-0.1055&filter=r&width=30.0&height=45.0' > 3.FITS
curl 'http://localhost:8661/image/v0/deepCoadd?ra=19.36995&dec=-0.3146&filter=r' > 4.FITS
curl 'http://localhost:8661/image/v0/deepCoadd/cutout?ra=19.36995&dec=-0.3146&filter=r&width=115&height=235' > 5.FITS
curl 'http://localhost:8661/image/v0/deepCoadd/cutoutPixel?ra=19.36995&dec=-0.3146&filter=r&width=115&height=235' > 6.FITS

Etc. Basically, if we can locate the image through /nfs on lsst-dev we will display it.

Example Metaserv Queries

Only some dummy metadata is currently loaded. You can try things like:

List levels

curl 'http://localhost:8661/meta/v0/db'
{"result": ["L2"]}

List databases

curl 'http://localhost:8661/meta/v0/db/L2'
{"results": [["metaServ_baselineSchema"]]}

Describe info about one database

curl 'http://localhost:8661/meta/v0/db/L2/metaServ_baselineSchema'
{"result": [1, "/dummy", 1, "db", "L2", "DR1", "3.2.4", "metaServ_baselineSchema", "LSST baseline schema - schema we expect to use in production.", 1, null, null, null, null, null, null, null, null, null, 1, "metaServ_baselineSchema", "lsst10.ncsa.illinois.edu", 3306]}

List tables in a database

curl 'http://localhost:8661/meta/v0/db/L2/metaServ_baselineSchema/tables'
{"results": [["ApertureBins"], ["CcdVisit"], ["CcdVisitMetadata"], ["DiaForcedSource"], ["DiaObject"], ["DiaObject_To_Object_Match"], ["DiaSource"], ["ForcedSource"], ["LeapSeconds"], ["Object"], ["Object_APMean"], ["Object_Extra"], ["Object_NonPeriodic"], ["Object_Periodic"], ["RawAmpExposure"], ["RawAmpExposureMetadata"], ["RawCcdExposure"], ["RawCcdExposureMetadata"], ["RawExposure"], ["SSObject"], ["Source"], ["Source_APMean"], ["Visit"], ["VisitMetadata"], ["Visit_To_RawExposure"], ["ZZZ_Schema_Description"], ["prv_Amp"], ["prv_Ccd"], ["prv_Filter"], ["prv_Fpa"], ["prv_InputDataSet"], ["prv_Node"], ["prv_ProcHistory"], ["prv_Raft"], ["prv_Run"], ["prv_Snapshot"], ["prv_Task"], ["prv_Task2TaskExecution"], ["prv_Task2TaskGraph"], ["prv_TaskExecution"], ["prv_TaskGraph"], ["prv_TaskGraph2Run"], ["prv_cnf_Amp"], ["prv_cnf_Ccd"], ["prv_cnf_Filter"], ["prv_cnf_Fpa"], ["prv_cnf_InputDataSet"], ["prv_cnf_Node"], ["prv_cnf_Raft"], ["prv_cnf_Run"], ["prv_cnf_Task"], ["prv_cnf_Task2TaskExecution"], ["prv_cnf_Task2TaskGraph"], ["prv_cnf_TaskExecution"], ["prv_cnf_TaskGraph"], ["prv_cnf_TaskGraph2Run"], ["sdqa_ImageStatus"], ["sdqa_Metric"], ["sdqa_Rating_CcdVisit"], ["sdqa_Rating_ForAmpVisit"], ["sdqa_Threshold"]]}

Show schema for one table

curl 'http://localhost:8661/meta/v0/db/L2/metaServ_baselineSchema/tables/Source_APMean/schema'
{"result": ["Source_APMean", "CREATE TABLE `Source_APMean` (\n `sourceId` bigint(20) NOT NULL,\n `binN` tinyint(4) NOT NULL,\n `sbMean` float NOT NULL,\n `sbSigma` float NOT NULL,\n KEY `IDX_SourceAPMean_sourceId` (`sourceId`),\n KEY `FK_SourceAPMean_ApertureBins` (`binN`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1"]}

Note, this works with dummy data I loaded to metaserv, it is not working with the actual schemas we have on lsst10 database.

Webserv with Qserv backend

It runs on lsst-dev, port 8662.

The catalog: bremerton.

Example Database Queries

Get first 10 forces sources

curl 'http://localhost:8662/db/v0/query?sql=select+objectId+from+bremerton.deepCoadd_forced_src+limit+10'
{"results": [[992137445377], [992137445377], [992137445377], [992137445378], [992137445378], [992137445378], [992137445379], [992137445379], [992137445379], [992137445380]], "metadata": {"columnDefs": [{"type": "long", "name": "objectId"}]}}

Area search

curl 'http://localhost:8662/db/v0/query?sql=select+objectId,coord_ra,coord_dec+from+bremerton.deepCoadd_forced_src+where+qserv_areaSpec_box(320.05,0.457,320.06,0.46)'
{"results": [[992137445522, 320.051654818646, 0.457555769928618], [992137445543, 320.056809284335, 0.457960196525095], [992137445588, 320.054729080744, 0.459876659623543], [992137445560, 320.058960344277, 0.458826205409071], [992137445588, 320.054729080744, 0.459876659623543], [992137445543, 320.056809284335, 0.457960196525095], [992137445522, 320.051654818646, 0.457555769928618], [992137445560, 320.058960344277, 0.458826205409071], [992137445560, 320.058960344277, 0.458826205409071], [992137445543, 320.056809284335, 0.457960196525095], [992137445588, 320.054729080744, 0.459876659623543], [992137445522, 320.051654818646, 0.457555769928618]], "metadata": {"columnDefs": [{"type": "long", "name": "objectId"}, {"type": "float", "name": "coord_ra"}, {"type": "float", "name": "coord_dec"}]}}

To-dos

  • find a way to distinguish whether user should use scisql_ or qserv_ UDFs.
  • revisit stringifying timestamps, datetimes and binary data
  • No labels