Repository navigation
Replies: 4 comments
|
@EranOfek , @yossishv , @chentishler , do you agree? |
|
Ingestion test of All figures below are averages per visit. Catalogue tables (53,700
Image tables (24
Disk per visit: Notes:
@chentishler, the four tables are in |
|
@chentishler, as in #1409 and #1413, I added a draft of the indexes at the end of the description (sections Indexes and Index choices by column), for you to approve or edit in place. It proposes 7 indexes: a |
|
@chentishler The scripts that create |
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
We want two new ClickHouse tables for the LAST v1 source catalogues:
src_visit, with one row per source in every coadd sub-image, andsrc_proc, with one row per source in every epoch sub-image (including the forced-photometry rows). Below is a first proposal in the same format as #1409 (image_visit): for every catalogue column, whether to keep it, its type, default value and needed resolution, plus the columns the ingestion adds. Please comment on the ✅/❌ choices and the types.Related: #1409 proposes the coadd image table
image_visit, and #1413 the epoch image tableimage_proc, in the same format. Once this proposal is settled, it will be used to create the actual ClickHouse tablessrc_visitandsrc_proc.What the proposal is based on
lv1.0.4): 624 coadd catalogues with 1,062,055 rows, and 12,216 epoch catalogues with 8,141,185 rows, of which 682,586 (8.4%) are forced-photometry rows. The forced rows of this sample were made before the error fix of Forced photometry underestimates flux errors of bright stars #1408; that does not change the schema.primary, which is an SQL keyword and becomesis_primary.Float32andFloat64), as in visit_images: a ClickHouse table for LAST v1 coadd headers #1409.src_visit(visit) andsrc_proc(proc), and the share of NaN values. A column found in only one of the catalogue types shows one line.Design-Database-Pipeline-ClickHouse.xlsx, tab Sources) has the column, and with which name and type.src_visitrows and 340 millionsrc_procrows per night, or about 1.4×10¹⁰ and 1.0×10¹¹ rows per year. The existing tablesvisit_src/proc_src(61 columns) take about 160 bytes per row on disk, so roughly 2 TB per year forsrc_visitand 16 TB per year forsrc_proc.src_procis where the cost is: it has 7.6 times more rows.Sorting and indexes
As in the existing source tables (
visit_src,proc_src,diff_src):PARTITION BY upix_par ORDER BY upix_hig, so that a position search reads only the nearby sky pixels. Additional indexes (for example onid_coadd/id_procandjd) will be defined by @chentishler.Choices already made
src_proc, marked by theforcedcolumn (0/1). They are 8.4% of the epoch rows. For themmiteris 0,psf_chi2dof,sn_1,sn_2andsn_iter1are NaN, andxpeak/ypeakhold the requested (fractional) position instead of a peak pixel. Queries on detections only addforced = 0.nodenumb,mountnum,camnum,cropid,fieldid) in the source rows. When they are needed, the source tables are joined with the image tables throughid_coadd/id_proc.upix_par,upix_low,upix_hig, as inimage_visit(diff_srcusesupix_partition,upix_high).Column table
Click a group to open it.
Position on the sky and in the image (13 columns)
raFloat64proc: 142 … 217
float64decFloat64proc: 8.81 … 72.3
float64xFloat32proc: -5.73 … 1726
float32yFloat32proc: -2.75 … 1721
float32xfullFloat32proc: -5.73 … 6394
x_fullyfullFloat32proc: -1.5 … 9580
y_fullxpeakFloat32proc: -5.73 … 1726
int16ypeakFloat32xpeakproc: -2.75 … 1721
int16x1Float32proc: -5.85 … 1722 (<1% NaN)
float32y1Float32proc: -2.85 … 1721 (<1% NaN)
float32x2Float32proc: -654060 … 454610 (<1% NaN)
float32y2Float32proc: -1.4e6 … 1.2e6 (<1% NaN)
float32xyFloat32proc: -322435 … 562198 (<1% NaN)
float32ra: J2000 RA from the PSF fitdec: J2000 Dec from the PSF fitx: PSF-fit position in the sub-imagexfull: PSF-fit position in the full (uncropped) imagexpeak: peak pixel; for forced rows the requested position (fractional, see note 3)x1: first momentx2: second momentsPSF photometry and detection (9 columns)
flux_psfFloat32proc: -4077 … 6.4e6 (<1% NaN)
float32mag_psfFloat32proc: 7.84 … 31.6 (2% NaN)
float32magerr_psfFloat32proc: 4.8e-5 … 40799 (2% NaN)
float32snFloat32proc: -12.7 … 22855 (<1% NaN)
float32psf_chi2dofFloat32proc: 0.15 … 2.2e7 (8% NaN)
float32sn_1Float32proc: -42.7 … 5251 (8% NaN)
float32sn_2Float32proc: 4 … 9.2e7 (8% NaN)
float32sn_iter1Float32proc: -41.1 … 25634 (8% NaN)
miterUInt8proc: 1 … 3 (8% NaN)
flux_psf: PSF-fit flux [e⁻]mag_psf: calibrated PSF magnitude (NaN when the flux is ≤ 0 or the image is not calibrated)magerr_psf: = 1.086 / |sn|sn: PSF-fit S/N = flux_psf / its errorpsf_chi2dof: PSF-fit χ²/dof (NaN for forced rows)sn_1: detection S/N with the sharp template (cosmic-ray test; NaN for forced rows)sn_2: detection S/N with the PSF-like template (NaN for forced rows)sn_iter1: first-iteration matched-filter S/N (multiExtractor identify multiple fake sources around galaxies #1113; NaN for forced rows)miter: extraction iteration that found the source, 1–3 (0 for forced rows)Aperture photometry (radii 3, 5, 6, 7 pix) (16 columns)
flux_aper_1Float32proc: -431527 … 1.8e6 (<1% NaN)
float32flux_aper_2Float32proc: -2.0e10 … 4.3e6 (1% NaN)
float32flux_aper_3Float32proc: -1.5e12 … 5.0e6 (1% NaN)
float32flux_aper_4Float32proc: -1.5e12 … 6.3e6 (10% NaN)
fluxerr_aper_1Float32proc: 8.1e-4 … 3.7e7 (5% NaN)
float32fluxerr_aper_2Float32proc: 6.7e-4 … 2.9e8 (9% NaN)
float32fluxerr_aper_3Float32proc: 6.7e-4 … 1.0e8 (11% NaN)
float32fluxerr_aper_4Float32proc: 6.9e-4 … 3.0e8 (18% NaN)
mag_aper_1Float32proc: 8.81 … 35.6 (5% NaN)
float32mag_aper_2Float32proc: 8.2 … 34.4 (9% NaN)
float32mag_aper_3Float32proc: 8.11 … 34.1 (11% NaN)
float32mag_aper_4Float32proc: 7.82 … 35.1 (18% NaN)
magerr_aper_1Float32proc: 8.8e-4 … 4.0e7 (5% NaN)
float32magerr_aper_2Float32proc: 7.3e-4 … 3.1e8 (9% NaN)
float32magerr_aper_3Float32proc: 7.3e-4 … 1.1e8 (11% NaN)
float32magerr_aper_4Float32proc: 7.5e-4 … 3.3e8 (18% NaN)
flux_aper_1: aperture flux [e⁻], radius 3 pixflux_aper_2: radius 5 pixflux_aper_3: radius 6 pixflux_aper_4: radius 7 pixmag_aper_3: the photometric zero point is fitted on this oneBackground (5 columns)
back_imFloat32proc: -64.3 … 33189 (<1% NaN)
float32var_imFloat32proc: 0.014 … 35627 (<1% NaN)
float32back_annulusFloat32proc: -9855 … 36502
float32std_annulusFloat32proc: 8.78 … 6.0e10
float32flux_xypeakFloat32proc: -2115 … 69423 (<1% NaN)
back_im: background map at the source [e⁻/pix]var_im: variance map at the sourceback_annulus: background in the 10–12 pix annulusstd_annulus: its standard deviationflux_xypeak: background-subtracted value of the peak pixelCalibration and colour (3 columns)
ab_zpFloat32proc: 21.5 … 22 (<1% NaN)
zp)airmassFloat32proc: 1 … 1.64
bp_rpFloat32ab_zp: AB zero point at the source positionairmass: airmass at the source position and timebp_rp: Gaia BP−RP of the matched star (onlysrc_visit; NaN if no match)Flags and matches (6 columns)
flagsUInt32proc: 0 … 9.2e7 (<1% NaN)
uint32is_primaryUInt8proc: 0 … 1
forcedUInt8is_forcedmergedcatmaskUInt32mergedcat Int32n_detUInt8n_det int16distmpFloat32float32flags: bit mask from the image mask at the source (bit names inBitDictionary)is_primary(catalogue columnprimary): 1 for the copy inside the crop's unique section, 0 for the copies in overlapping neighbours (Raising the Overlap flag for one of the overlapping image crops only #1180)forced: 1 for a forced-photometry row (onlysrc_proc; see note 2)mergedcatmask: bit mask of the external catalogues with a match (onlysrc_visit)n_det: number of epochs in which the source was detected (onlysrc_visit; NaN → 0)distmp: distance to the matched known minor planet [arcsec] (onlysrc_visit)Sky pixel (HEALPix, nested) (3 columns)
upix_parUInt16proc: 321 … 686
upix_partition uint64upix_lowUInt32proc: 329027 … 703163
uint64upix_higUInt64proc: 2.2e10 … 4.6e10
upix_high uint64upix_par: level 8 (~7° pixel); the partition keyupix_low: level 256 (~0.2°)upix_hig: level 65536 (~3″); the sort keyColumns added at ingestion
These columns are not in the catalogue table; the ingestion code adds them.
id_coadd(src_visit),id_proc(src_proc)UInt64ID_COADD,ID_PROC), the link toimage_visit(and to a futureimage_proc). The header holds it as text; convert the text directly to an integer, never through adouble(same rule as #1409).id_visit_src/id_proc_srcUInt32id_coadd/id_procit identifies a row.jdFloat64MIDJD: the middle of the exposure for an epoch, the middle of the coadd for a coadd. (The coadd headerJDis the time of the first epoch, so it is not used.)bjd,baryvelFloat64,Float32jd,ra,decas the current ingestion does (celestial.time.barycentricJD).ingestion_time_jdFloat64diff_srcandimage_visit.poisson_bias_magFloat321.0857 / sn²forsn > 0, NaN otherwise. Computed for every row, as decided in #1407; the corrected magnitude ismag_psf + poisson_bias_mag. The catalogues have noFLUXERR_PSFcolumn, butsnis exactlyFLUX_PSF / FLUXERR_PSF(in 80 sample catalogues, about 36,000 rows including forced ones,magerr_psf × |sn|= 1.0860 for every row), so this is the same quantity as1.0857·(FLUXERR_PSF/FLUX_PSF)²in #1407.Notes on specific choices
fluxerr_psfis in the 2024 sheet but not in the v1 catalogues. It equalsflux_psf / sn, so it is not stored.upix_par,upix_low,upix_higare already in every catalogue row, and they are the values the current ingestion computes (nested HEALPix at levels 8, 256 and 65536; 152,440 rows checked, all equal). The ingestion copies them instead of computing them again.xpeak/ypeakareFloat32, because in the forced rows they hold the requested position (fractional in all forced rows checked, and in no detected row); for detected rows they are whole numbers.MagType='mag'), so they are NaN when the flux is zero or negative.src_proccome from failed measurements:flux_aper_*down to −1.5×10¹²,std_annulusup to 6×10¹⁰,x2/y2up to ±1.4×10⁶.Float32holds them; they are a data-quality matter, not a reason to change types.Differences from the 2024 design sheet
flux_aper_4,fluxerr_aper_4,mag_aper_4,magerr_aper_4),sn_iter1,miter,flux_xypeak,ab_zp,airmass,bp_rp,is_primaryandpoisson_bias_mag.fluxerr_psf(note 1);sn_ext1,sn_ext2,sn_delta,sn_3,sn_4,sn_5(not produced by the v1 pipeline);nodenumb,mountnum,camnum,cropid(available through a join with the image tables, choice 2);id_raw_imandnepoch(proc_srconly; available throughid_proc);id_uniq_srcandid_cat_src(IDs for a future cross-match table, not filled by the catalogue ingestion).upix_parandupix_lowbecomeUInt16/UInt32(sheet:uint64);mergedcatmaskbecomesUInt32(sheet:Int32);n_detbecomesUInt8(sheet:int16; at most 20 epochs per visit).Indexes
Draft for @chentishler to approve or edit.
discussion2sqlreads this table intoCREATE TABLEand into an ALTER script for a table that already exists (types:minmax,set(N),bloom_filter,tokenbf_v1,ngrambf_v1; granularity in blocks of 8192 rows; an empty Table cell means both tables).idx_id_coaddid_coaddbloom_filter(0.01)upix_par, see the note below)idx_id_procid_procbloom_filter(0.01)upix_par, see the note below)idx_raraminmaxidx_decdecminmaxidx_upix_lowupix_lowminmaxidx_jdjdminmaxidx_ingestingestion_time_jdminmaxIndex choices by column
The tables are split by sky partition (
upix_par, 768 partitions) and sorted byupix_hig(~3″), so each block of 8192 rows holds one small patch of sky (about 10′×15′ invisit_src, 3′×5′ inproc_src) with all its observations from all nights. A position search is already fast through the sort key. The opposite holds for time: a block holds 6–55 nights spread over 100–840 days, sominmaxonjdor on the time-ordered image IDs skips few blocks. To find the sources of one image, abloom_filteron the image ID works: the image fills only about 1% of the blocks of its partition.Measured on the existing tables
last.visit_src(4.1×10¹⁰ rows) andlast.proc_src(2.0×10¹¹ rows), which have the same partitioning and sort key: 3 and 2 partitions of median size (32–41 million and 182–188 million rows; 4,000–5,000 and 22,000–23,000 blocks each). Share of a partition's blocks a query must read:visit_srcproc_srcbloom_filter(0.01)on the image IDbloom_filter(0.001)minmaxon the image ID or onjdminmaxoningestion_time_jdmag_psf < 12,minmaxsn > 100,minmaxThese are the existing tables after merging (about 4 parts per partition). The v1 tables will be filled by the reprocessing, which runs many nights in parallel and out of order, so the time figures may be worse than measured.
Index size and how to query one image. On a copy of one
proc_srcpartition (188 million rows, 4.0 GB for the 5 copied columns), thebloom_filter(0.01)on the image ID takes 134 MB, andbloom_filter(0.001)202 MB. At 10¹¹ rows per year that is about 70 GB (0.01) or 110 GB (0.001) per year forsrc_proc, under 0.7% of the table; forsrc_visitit is under 1 GB per year. ClickHouse reads this index for every partition the query does not exclude, so a query for the sources of one image should also give its sky partitions:upix_par IN (...), the partitions of the image corners (ra1…ra4,dec1…dec4inimage_visit/image_proc). An image near a partition border has sources in 2–4 partitions.Columns without an index (every ✅ column not in the table above; (visit)/(proc) marks a column of one table only):
upix_par,upix_higupix_paris the partition key andupix_higthe sort key (primary index).id_visit_src(visit),id_proc_src(proc)id_coadd/id_proc.bjdbjdfollowsjd(within ±8 min); time cuts usejd.x,y,xfull,yfull,xpeak,ypeak,x1,y1miter,flags,is_primary,mergedcatmask(visit),n_det(visit),forced(proc)x2,y2,xy,flux_psf,mag_psf,magerr_psf,sn,psf_chi2dof,sn_1,sn_2,sn_iter1,flux_aper_1,flux_aper_2,flux_aper_3,flux_aper_4,fluxerr_aper_1,fluxerr_aper_2,fluxerr_aper_3,fluxerr_aper_4,mag_aper_1,mag_aper_2,mag_aper_3,mag_aper_4,magerr_aper_1,magerr_aper_2,magerr_aper_3,magerr_aper_4,back_im,var_im,back_annulus,std_annulus,flux_xypeak,ab_zp,airmass,bp_rp(visit),distmp(visit),baryvel,poisson_bias_magmag_psf < 12selects about 1% of rows but 15–60% of the blocks;sn > 100reads 75–100% of the blocks (question 4).Questions for @chentishler:
minmaxonjd. WithPARTITION BY (upix_par, year)a time cut reads only that year's partitions, at 768 partitions per year of data; by month it would be about 9,200 per year, which is too many. Keepupix_paralone, as in the existing tables, or add the year?bloom_filter(0.01)adds about 1% of the blocks of a partition, as many as the image itself;0.001halves the blocks read for 50% more index. Which one?src_proc(16 TB per year).minmaxonmag_psfskips 40–85% of the blocks formag_psf < 12. Worth adding for bright-star queries?All reactions