Update redshift-database tutorial

[sbailey]

The redshift-database tutorial in github specifies a "desi-custom" kernel, and it doesn't seem to work with our recent software releases. I tried 18.7, 18.11, 18.12, 19.2, and master and they all seem to fail for different reasons.

Please check and fix, preferably with the 19.2 kernel and reference run, or master + the reference run if needed. Problems could be a combination of:

• Genuine problems in the generated data, requiring a fix for the next software release (in that case, submit a new ticket for what needs to be fixed, and get the tutorial working with whatever the latest working software release is).
• the data are ok but the DB loading has an problem (e.g. the past case where unassigned fibers had TARGETID=-1 causing a uniqueness constraint to fail)
• it's just that the tutorial needs to be updated

[weaverba137]

@sbailey, I'm sure this is a simple fix, but could you please at least include the error messages you are seeing when you report problems, so I have some idea where to start?

[sbailey]

Starting point: the tutorial in github used the 18.11 reference_run (though note that the text needs to be updated to match) and it references a "desi-custom" kernel that we don't have at NERSC. I tried various combinations of code + reference run, and most had this error (19.2 kernel and the 18.11 reference run in this specific case):

INFO:redshift.py:364:load_file: Read data from /global/project/projectdirs/desi/datachallenge/reference_runs/18.11/targets/targets.fits HDU TARGETS
WARNING:redshift.py:375:load_file: 4 rows of bad data detected in column FLUX_R of /global/project/projectdirs/desi/datachallenge/reference_runs/18.11/targets/targets.fits.
WARNING:redshift.py:375:load_file: 4 rows of bad data detected in column FIBERFLUX_R of /global/project/projectdirs/desi/datachallenge/reference_runs/18.11/targets/targets.fits.
WARNING:redshift.py:375:load_file: 4 rows of bad data detected in column FIBERTOTFLUX_R of /global/project/projectdirs/desi/datachallenge/reference_runs/18.11/targets/targets.fits.
INFO:redshift.py:382:load_file: Integrity check complete on target.
INFO:redshift.py:390:load_file: Initial column conversion complete on target.
INFO:redshift.py:412:load_file: Column expansion complete on target.
INFO:redshift.py:418:load_file: Column conversion complete on target.
INFO:redshift.py:425:load_file: Converted columns into rows on target.
---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1169                         parameters,
-> 1170                         context)
   1171             elif not parameters and context.no_parameters:

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_executemany(self, cursor, statement, parameters, context)
    504     def do_executemany(self, cursor, statement, parameters, context=None):
--> 505         cursor.executemany(statement, parameters)
    506 

IntegrityError: NOT NULL constraint failed: target.priority_init

The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
<ipython-input-2-69152de0d9fd> in <module>()
     16 for h in ('BGS', 'ELG', 'LRG', 'QSO', 'STAR', 'WD'):
     17     db.update_truth(os.path.join(targetdir, 'truth.fits'), 'TRUTH_' + h)
---> 18 db.load_file(os.path.join(targetdir, 'targets.fits'), db.Target, hdu='TARGETS', expand={'DCHISQ': ('dchisq_psf', 'dchisq_rex', 'dchisq_dev', 'dchisq_exp', 'dchisq_comp',)})
     19 #
     20 # The redshift catalog.

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/code/desispec/0.28.0/lib/python3.6/site-packages/desispec-0.28.0-py3.6.egg/desispec/database/redshift.py in load_file(filepath, tcls, hdu, expand, convert, index, rowfilter, q3c, chunksize, maxrows)
    428                       for row in data_rows[k*chunksize:(k+1)*chunksize]]
    429         if len(data_chunk) > 0:
--> 430             engine.execute(tcls.__table__.insert(), data_chunk)
    431             log.info("Inserted %d rows in %s.",
    432                      min((k+1)*chunksize, finalrows), tn)

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   2073 
   2074         connection = self.contextual_connect(close_with_result=True)
-> 2075         return connection.execute(statement, *multiparams, **params)
   2076 
   2077     def scalar(self, statement, *multiparams, **params):

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    946             raise exc.ObjectNotExecutableError(object)
    947         else:
--> 948             return meth(self, multiparams, params)
    949 
    950     def _execute_function(self, func, multiparams, params):

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    267     def _execute_on_connection(self, connection, multiparams, params):
    268         if self.supports_execution:
--> 269             return connection._execute_clauseelement(self, multiparams, params)
    270         else:
    271             raise exc.ObjectNotExecutableError(self)

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1058             compiled_sql,
   1059             distilled_params,
-> 1060             compiled_sql, distilled_params
   1061         )
   1062         if self._has_events or self.engine._has_events:

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201 
   1202         if self._has_events or self.engine._has_events:

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1411                 util.raise_from_cause(
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )
   1415             else:

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    184             value.__cause__ = cause
    185         if value.__traceback__ is not tb:
--> 186             raise value.with_traceback(tb)
    187         raise value
    188 

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1168                         statement,
   1169                         parameters,
-> 1170                         context)
   1171             elif not parameters and context.no_parameters:
   1172                 if self.dialect._has_events:

/global/common/software/desi/cori/desiconda/20180709-1.2.6-spec/conda/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_executemany(self, cursor, statement, parameters, context)
    503 
    504     def do_executemany(self, cursor, statement, parameters, context=None):
--> 505         cursor.executemany(statement, parameters)
    506 
    507     def do_execute(self, cursor, statement, parameters, context=None):

IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: target.priority_init [SQL: 'INSERT INTO target (release, brickid, brickname, brick_objid, morphtype, ra, dec, ra_ivar, dec_ivar, dchisq_psf, dchisq_rex, dchisq_dev, dchisq_exp, dchisq_comp, flux_g, flux_r, flux_z, flux_w1, flux_w2, flux_ivar_g, flux_ivar_r, flux_ivar_z, flux_ivar_w1, flux_ivar_w2, mw_transmission_g, mw_transmission_r, mw_transmission_z, mw_transmission_w1, mw_transmission_w2, nobs_g, nobs_r, nobs_z, fracflux_g, fracflux_r, fracflux_z, fracmasked_g, fracmasked_r, fracmasked_z, fracin_g, fracin_r, fracin_z, allmask_g, allmask_r, allmask_z, psfdepth_g, psfdepth_r, psfdepth_z, galdepth_g, galdepth_r, galdepth_z, fracdev, fracdev_ivar, shapedev_r, shapedev_r_ivar, shapedev_e1, shapedev_e1_ivar, shapedev_e2, shapedev_e2_ivar, shapeexp_r, shapeexp_r_ivar, shapeexp_e1, shapeexp_e1_ivar, shapeexp_e2, shapeexp_e2_ivar, fiberflux_g, fiberflux_r, fiberflux_z, fibertotflux_g, fibertotflux_r, fibertotflux_z, ref_id, gaia_phot_g_mean_mag, gaia_phot_g_mean_flux_over_error, gaia_phot_bp_mean_mag, gaia_phot_bp_mean_flux_over_error, gaia_phot_rp_mean_mag, gaia_phot_rp_mean_flux_over_error, gaia_astrometric_excess_noise, gaia_duplicated_source, parallax, parallax_ivar, pmra, pmra_ivar, pmdec, pmdec_ivar, brightstarinblob, ebv, photsys, targetid, desi_target, bgs_target, mws_target, subpriority, hpxpixel) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ((9999, 488659, '1526p285', 0, 'EXP ', 152.4978485107422, 28.408809661865234, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 16.09772300720215, 28.7934513092041, 42.85902786254883, 33.36859130859375, 25.591564178466797, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7545953392982483, 0.29916098713874817, 0.9354498982429504, 0.9560464024543762, 0.9749687314033508, 0.996187150478363, 0.9976567029953003, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.9714908599853516, 52018.12109375, 0.3237842321395874, 2.4894924091765555e+17, 0.10086371749639511, 166199.734375, 1.695696473121643, 3.098234176635742, 4.726201057434082, 1.695696473121643, 3.098234176635742, 4.726201057434082, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.022541597485542297, 'S', 288230398217945088, 1152921504606846976, 131586, 0, 0.3568103559416339, 5261), (9999, 488658, '1523p285', 1, 'DEV ', 152.40225219726562, 28.43646240234375, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 90.11798858642578, 184.66262817382812, 301.1165771484375, 270.7568664550781, 192.89874267578125, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7545765042304993, 0.29915371537208557, 0.9321490526199341, 0.9537726640701294, 0.9736603498458862, 0.9959855675697327, 0.9975327253341675, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 1.0, 0.0, 3.1728429794311523, 96601.546875, 0.05746058002114296, 2.8273491148297103e+20, 0.13335759937763214, 1376962.125, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 8.10999584197998, 17.16741371154785, 29.049846649169922, 8.10999584197998, 17.16741371154785, 29.049846649169922, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.023735709488391876, 'S', 288230398217945089, 1152921504606846976, 131586, 0, 0.568604199296773, 5261), (9999, 488658, '1523p285', 2, 'REX ', 152.38270568847656, 28.428730010986328, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 10.246813774108887, 21.87872314453125, 33.94467544555664, 29.530611038208008, 20.334270477294922, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7545267939567566, 0.2991345226764679, 0.9310829043388367, 0.953037679195404, 0.9732370972633362, 0.9959203600883484, 0.9974925518035889, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.6344906091690063, 13692.08203125, 0.0, 0.0, 0.0, 0.0, 1.3950544595718384, 3.052152156829834, 4.869853496551514, 1.3950544595718384, 3.052152156829834, 4.869853496551514, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.02412230521440506, 'S', 288230398217945090, 1152921504606846976, 131586, 0, 0.9329254586401731, 5261), (9999, 487390, '1526p282', 3, 'REX ', 152.7200164794922, 28.32263946533203, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 4.866122722625732, 11.972004890441895, 20.438461303710938, 20.707250595092773, 17.912580490112305, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.754564642906189, 0.29914912581443787, 0.9302952885627747, 0.9524945616722107, 0.9729242920875549, 0.9958720803260803, 0.9974628686904907, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.6859385371208191, 60716.265625, 0.0, 0.0, 0.0, 0.0, 1.6744358539581299, 4.290088653564453, 7.6673784255981445, 1.6744358539581299, 4.290088653564453, 7.6673784255981445, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.02440819889307022, 'S', 288230398217945091, 1152921504606846976, 65793, 0, 0.5880792227876986, 5261), (9999, 487390, '1526p282', 4, 'REX ', 152.75033569335938, 28.370037078857422, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 6.632730007171631, 15.018413543701172, 24.129465103149414, 19.661819458007812, 12.702428817749023, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.754763662815094, 0.29922589659690857, 0.9296985864639282, 0.9520829916000366, 0.9726871252059937, 0.9958354830741882, 0.9974403977394104, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.5423256754875183, 57870.56640625, 0.0, 0.0, 0.0, 0.0, 2.7036244869232178, 6.4018683433532715, 10.816449165344238, 2.7036244869232178, 6.4018683433532715, 10.816449165344238, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.02462494559586048, 'S', 288230398217945092, 1152921504606846976, 65793, 0, 0.17543431523839492, 5261), (9999, 486117, '1524p280', 5, 'EXP ', 152.55130004882812, 28.12346839904785, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 4.090408802032471, 14.306994438171387, 29.584917068481445, 45.93244934082031, 38.651702880859375, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7536880373954773, 0.29881104826927185, 0.9203853011131287, 0.9456477761268616, 0.9689738750457764, 0.9952616691589355, 0.9970873594284058, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2.203531265258789, 4171.45166015625, 0.03928520530462265, 1867774842372096.0, 0.35615596175193787, 30481.431640625, 0.3576124906539917, 1.27470862865448, 2.6941983699798584, 0.3576124906539917, 1.27470862865448, 2.6941983699798584, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.028026076033711433, 'S', 288230398217945093, 1152921504606846976, 65793, 0, 0.3655313045016235, 5261), (9999, 487390, '1526p282', 6, 'DEV ', 152.6802978515625, 28.179832458496094, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 8.717318534851074, 26.65909194946289, 52.678157806396484, 68.17051696777344, 51.3319091796875, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7540304064750671, 0.29894310235977173, 0.9222020506858826, 0.9469047784805298, 0.969700038433075, 0.9953740239143372, 0.9971565008163452, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 1.0, 0.0, 0.8572824597358704, 20022.142578125, 0.10677320510149002, 333073673617408.0, 0.12159992754459381, 18184.173828125, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2.5458266735076904, 8.146329879760742, 16.937957763671875, 2.5458266735076904, 8.146329879760742, 16.937957763671875, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.02735993079841137, 'S', 288230398217945094, 1152921504606846976, 131586, 0, 0.9511830621245244, 5261), (9999, 487390, '1526p282', 7, 'REX ', 152.7872314453125, 28.192785263061523, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 3.7336440086364746, 12.043473243713379, 25.54196548461914, 41.78341293334961, 31.527860641479492, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7542009949684143, 0.29900890588760376, 0.9211333990097046, 0.9461655020713806, 0.969273030757904, 0.995307981967926, 0.9971158504486084, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.7476310729980469, 29394.662109375, 0.0, 0.0, 0.0, 0.0, 0.4648111164569855, 1.534567952156067, 3.3427071571350098, 0.4648111164569855, 1.534567952156067, 3.3427071571350098, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.027751604095101357, 'S', 288230398217945095, 1152921504606846976, 65793, 0, 0.16704683354563044, 5261)  ... displaying 10 of 50000 total bound parameter sets ...  (9999, 489918, '1505p287', 73, 'EXP ', 150.62860107421875, 28.846633911132812, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 33.038124084472656, 83.62114715576172, 151.85191345214844, 156.5404815673828, 105.21444702148438, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.753903865814209, 0.2988942861557007, 0.9454969167709351, 0.9629511833190918, 0.9789336919784546, 0.9967966079711914, 0.998031497001648, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2.448697805404663, 234034.375, -0.007581037934869528, 1.389766079226239e+23, 0.02925814688205719, 935237.875, 2.263221502304077, 5.815425395965576, 10.74710464477539, 2.263221502304077, 5.815425395965576, 10.74710464477539, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.018932709470391273, 'S', 288230398314414153, 1152921504606846976, 131586, 0, 0.20038828049405466, 5284), (9999, 491182, '1507p290', 74, 'REX ', 150.66876220703125, 28.931087493896484, 100000000.0, 100000000.0, 0.0, 100.0, 200.0, 300.0, 400.0, 3.9731950759887695, 10.705737113952637, 17.48809051513672, 28.678274154663086, 22.746755599975586, 1438.599853515625, 329.5641784667969, 62.79716110229492, 0.7542377710342407, 0.2990230619907379, 0.9514783024787903, 0.9670504927635193, 0.9812817573547363, 0.99715656042099, 0.998252809047699, 2, 2, 2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 52.48074722290039, 7.726805686950684, 1.8706821203231812, 57.54399490356445, 13.182567596435547, 2.5118863582611084, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.2529327869415283, 18379.90625, 0.0, 0.0, 0.0, 0.0, 0.7542810440063477, 2.0925073623657227, 3.5339815616607666, 0.7542810440063477, 2.0925073623657227, 3.5339815616607666, -1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0, 0, 0.016802361235022545, 'S', 288230398314414154, 1152921504606846976, 65793, 0, 0.49559071659276355, 5284))] (Background on this error at: http://sqlalche.me/e/gkpj)

18.11 kernel+18.11 reference run, and 19.2 kernel + 18.7 reference run were similar.

The 19.2 kernel + 19.2 reference run was different:

INFO:redshift.py:621:load_fiberassign: Using tile file search path: /global/project/projectdirs/desi/datachallenge/reference_runs/19.2/fiberassign/tile_*.fits.
ERROR:redshift.py:624:load_fiberassign: No tile files found!

I fixed master to work with both tile_*.fits (old) and tile-*.fits (new). Then master kernel + 19.2 reference run has the same "NOT NULL constraint failed"

I had at least one other combination that got beyond the loading and then later failed on the join example not returning any rows, but I haven't been able to reproduce what combination that was.

[weaverba137]

Thank you that's useful. Looks to me like a change in the data model, which wouldn't be surprising.

[weaverba137]

I can't remember, is it normal for exposures.fits to have NaN values for RA, DEC?

WARNING:redshift.py:375:load_file: 24 rows of bad data detected in column RA of /global/project/projectdirs/desi/datachallenge/reference_runs/19.2/survey/exposures.fits.
WARNING:redshift.py:375:load_file: 24 rows of bad data detected in column DEC of /global/project/projectdirs/desi/datachallenge/reference_runs/19.2/survey/exposures.fits.

[dkirkby]

I don't think so. Do you know where this exposures.fits came from?

[weaverba137]

@dkirkby : /global/project/projectdirs/desi/datachallenge/reference_runs/19.2/survey/exposures.fits

[weaverba137]

@sbailey, it is technically impossible to update the tutorial to work with 19.2, because that would require changes to desispec, which aren't in 19.2. The best I could do would be to get it working with reference_run 19.2 and desimodules/master.

[weaverba137]

@sbailey, could you also tell me more about the new tile file data model? I see several new HDUs.

[weaverba137]

@sbailey, I'm not going to try to guess which of the 107 columns in the FIBERASSIGN table are not duplicated from other tables. Could you please point those out to me?

[weaverba137]

And it looks like the exception was triggered by the absence of a PRIORITY column, possibly others, but that one was immediately obvious.

[sbailey]

The RA/DEC NaNs are from the arc and flat calibration exposures (FLAVOR='arc' or 'flat'). While inconvenient, the concepts of RA/DEC don't really apply when taking a calibration exposure pointing at the dome screen, so NaN isn't a completely crazy quantity either.

Options:

• declare that this DB is for tracking science exposures and filter out any calibration exposures before loading.
• replace calibration exposure NaN with some other magic value, like -999.
• something else?
• although the telescope does happen to be pointing at some RA,DEC while it is taking a dome arc/flat exposure, I don't think we should require ICS to fill in a meaningful RA,DEC value in those cases. This applies even more to testslit exposures taken with calibration lamps in the shack (completely bypassing the telescope).

For the tutorial, it's fine to fix this in desispec master and make the tutorial work with desimodules/master + reference run 19.2.

PRIORITY: target files should now have PRIORITY_INIT, and the mtl has both PRIORITY_INIT and PRIORITY (potentially different N years into the survey, but not in the minitest).

Fiberassign tile files:

• I think you only need to load the FIBERASSIGN table and can ignore the rest.
• SKY_MONITOR are the assignments for fibers going to the sky background monitor imagers used by the exposure time calculator, not the spectrographs.
• TARGETS is passing forward target information, redundant with what you are already loading from the targets catalog.
• POTENTIAL_ASSIGNMENTS is for low-level debugging of alternate universes for what other targets fiberassign could have picked. In previous incarnations this HDU was called POTENTIAL, and it might go back to that again (or something shorter...)
• FASSIGN, FTARGETS, and FAVAIL are passed forwards from an earlier intermediate product.
• All of this I hope will be cleaned up in a future pass (which means yet another datamodel change...)

[weaverba137]

OK, but the MTL is a different file, right? So that would never actually get loaded, so the only column that matters is PRIORITY_INIT.

[weaverba137]

OK, actually, it looks like FIBERASSIGN has both PRIORITY and PRIORITY_INIT. Just trying to read these columns is causing serious eyestrain. How about this: Can we agree that the existing columns in the database are all that are needed, and I'll fix the order of the columns?