LAST DB image and source tables ingestion - EranOfek/AstroPack GitHub Wiki

This page describes how the LAST v1 database tables image_visit, image_proc, src_visit and src_proc are created and filled. Three functions in pipeline.last.insertDB do it:

Function What it does
discussion2sql Builds the CREATE TABLE statement and the column list of a table from its design discussion: by default the columns only, optionally also the indexes and projections.
insertArchiveImageHeaders2DB Fills image_visit (coadd image headers) and image_proc (epoch image headers).
insertArchiveSourceCatalogs2DB Fills src_visit (coadd catalogues) and src_proc (epoch catalogues).

The design of each table (which header keywords or catalogue columns become columns, their types and default values) is decided in an AstroPack discussion: #1409 image_visit, #1413 image_proc and #1411 src_visit and src_proc. The functions read the column list from the discussion text itself, so the table, the ingestion and the discussion always agree. Which pipeline versions added or redefined columns is listed in LAST DB columns by pipeline version.

Status (2026 Oct 26): the four tables exist in the test database last_test on euclid. Their CREATE TABLE scripts (columns only) are in +insertDB/sql; the indexes and projections are created separately.

Workflow

  1. Freeze the discussion text. Save the body of the discussion as a markdown file (for example image_visit_1409.md). Production runs should always use such a frozen file: the discussion may change later, and euclid has no gh to fetch it.
  2. Create the table with discussion2sql and run the SQL in ClickHouse. By default the script has the columns and the table key only; the indexes and projections are added separately (with 'Indexes',true the function writes them from the discussion text, also as an ALTER script for a table that already exists).
  3. Fill the table with the ingestion function, giving it the same frozen file. Before inserting, the function compares the column names of the file with those of the table and stops if they differ.

discussion2sql

[SQL, Spec, Info] = pipeline.last.insertDB.discussion2sql(Source, ...)

Source is a discussion number (the text is fetched with gh) or the name of a markdown file with the text.

Argument Default Meaning
DbName 'last_test' Database name in the CREATE TABLE statement.
TableName [] Table name; if empty, taken from the text ("a new ClickHouse table, name").
TableTag "" For a discussion that proposes two tables in one column list (#1411): "visit" or "proc". Needs TableName.
Engine 'MergeTree' Table engine.
OrderBy [] Sorting key; if empty, taken from the text ("ORDER BY name"). A "PARTITION BY name" in the text is used too.
OutFile [] If given, the SQL is also written to this file.
Indexes false Which parts of the scripts to write. false: the columns (types, default values), PARTITION BY and ORDER BY only. true: also the indexes and projections of the text, in CREATE TABLE and in the ALTER script.
IndexFile [] With Indexes true: if given, the ALTER script that adds the indexes and projections to an existing table is written to this file.
Materialize true Add MATERIALIZE lines to the ALTER script, which build the indexes for the rows already in the table; false: only rows inserted later are indexed.
Repo 'EranOfek/AstroPack' Repository of the discussion.

Outputs: the SQL text; Spec, a table with one row per column (Column, Keyword, Type, Default, Kind, SecPart); and Info (TableName, OrderBy, PartitionBy, Text, and with Indexes true Indexes, Projections and AlterSQL, the ALTER script).

What it reads from the text:

  • Every row of the form | `KEYWORD` | ✅ | `Type` | default | … becomes a column named after the keyword in lower case; ❌ rows are skipped. An image section (CCDSEC, …, type "4 × Int16") becomes four columns <name>_xmin, _xmax, _ymin, _ymax.
  • Columns computed at ingestion: in #1409/#1413 the rows marked "(new, …)"; in #1411 the table "Columns added at ingestion", where `a` (`src_visit`) keeps a column for that table only and of `a` / `b` the name containing the table tag is kept.
  • With TableTag, a column row is kept if its range cell names that table ("visit:" or "proc:") or names neither, and catalogue columns renamed because their name is an SQL keyword (primary → is_primary) keep the catalogue name.
  • With Indexes true: the optional tables "### Indexes" (| `name` | `expression` | `type` | granularity | table | purpose |) and "### Projections" (| `name` | `SELECT … ORDER BY col` | table | purpose |). A row with an empty table cell is used for every table of the discussion; every column named in them must be a column of the table.

Examples:

% image tables, from frozen texts (columns only)
SQL = pipeline.last.insertDB.discussion2sql('image_visit_1409.md', 'OutFile','image_visit.sql');
SQL = pipeline.last.insertDB.discussion2sql('image_proc_1413.md',  'OutFile','image_proc.sql');
% source tables: two tables from one discussion
SQL = pipeline.last.insertDB.discussion2sql('src_tables_1411.md', 'TableName','src_visit', 'TableTag',"visit", 'OutFile','src_visit.sql');
SQL = pipeline.last.insertDB.discussion2sql('src_tables_1411.md', 'TableName','src_proc',  'TableTag',"proc",  'OutFile','src_proc.sql');
% with the indexes and projections of the text, and the ALTER script for a table that already exists
SQL = pipeline.last.insertDB.discussion2sql('image_visit_1409.md', 'OutFile','image_visit.sql', 'Indexes',true, 'IndexFile','image_visit_indexes.sql');
% the live discussion text (needs gh), for a quick look
[SQL, Spec] = pipeline.last.insertDB.discussion2sql(1409);

insertArchiveImageHeaders2DB and insertArchiveSourceCatalogs2DB

Result = pipeline.last.insertDB.insertArchiveImageHeaders2DB(RootDir, ...)
Result = pipeline.last.insertDB.insertArchiveSourceCatalogs2DB(RootDir, ...)

Both go through the visit folders under RootDir (<RootDir>/proc/HHMMSSv<N>), one visit at a time:

  1. Read the visit folder:
    • image_visit: the headers of the 24 coadd_Image FITS files.
    • image_proc: the 480 epoch headers from the visit's proc_Head_1.mat; for older visits (before lv1.1.0), HDU 3 of the proc_Cat FITS files.
    • src_visit: the 24 coadd_Cat FITS catalogues and their headers (HDU 3).
    • src_proc: the epoch catalogues and headers from proc_Cat_1.mat and proc_Head_1.mat; for older visits, the proc_Cat FITS files.
  2. Build the columns: select and convert the columns of the discussion, and add the computed ones:
    • image tables: ingestion_time_jd, and in image_proc id_coadd (the coadd with the same CROPID whose ID_PROCF…ID_PROCL range contains the epoch ID_PROC);
    • source tables: id_coadd / id_proc (from the header), id_visit_src / id_proc_src (row number in the sub-image catalogue), jd (header MIDJD), bjd and baryvel (barycentric JD and velocity of every source), ingestion_time_jd and poisson_bias_mag (1.0857 / sn², #1407).
  3. Insert the rows of the visit, one insert per table.
  4. Stamp the visit's .status file with "Injected into the <table> table".

A visit whose .status already has the stamp of a table is skipped for that table. A visit folder without a .status file is skipped with a warning in the log. A visit that cannot be read is skipped with its error message (in the log and in Result); a DB error stops the run. Only the latest reduction of each visit is used (the largest N of HHMMSSv<N>, if N ≥ MinVersion).

Argument Default Meaning
ProcDirTemplate '/proc/*' Visit folders under RootDir, e.g. 'LAST*/2026/*/*/proc/*' for a whole archive.
MinVersion 1 Lowest reduction number used (0 = DemonLAST, from 1 = PipelineDemon).
ExcludeFolder '_re' Skip visit folders whose path contains it.
Tables both tables e.g. "image_visit" to fill one table only.
VisitSpec, ProcSpec (images) / Spec (sources) 1409, 1413 / 1411 Discussion number or frozen markdown file. Use the file the table was created from.
VisitTemplate, ProcTemplate, ProcHeadMat, ProcCatMat, VisitHDU, ProcHDU, HeadHDU see the help File names and HDUs of the products.
DbHost, DbPort, DbUser, DbName 'euclid', 9000, 'default', 'last_test' Database. On euclid itself use 'localhost'.
PassKey, AstroDBPassFile 'last', '~/.astropack/Passwords.yml' Where the DB password is taken from.
StatusStampVisit, StatusStampProc "Injected into the … table" The .status stamps.
RemoteUser 'euclid' The stamps are written with su - <RemoteUser>; empty = write directly (run as the owner of the folders).
StampStatus true false: insert without stamping .status, for tests on archive folders. A later run inserts these visits again.
DryRun false true: read and build the columns, but do not insert or stamp; Result then holds the built columns.
MaxVisits Inf Stop after this many inserted visits.
Logger [] A logger from pipeline.last.insertDB.ingestLogger; empty = a new one with the default log file (see Logging below).

Result has one element per visit: Dir, Nvisit and Nproc (rows for the two tables), Status ("injected", "dry run" or "read error: …"), Time (seconds of each stage: reading, building the columns, bjd for the source tables, insert and stamping, separately for the two tables), and with DryRun the built columns in Visit and Proc. Each visit is also reported on one line of the log with its row counts and stage times.

Logging

Both functions write a log through AstroPack's MsgLogger, to the screen and to one file per night (noon to noon UTC): ~/matlab/log/last/insertDB/<yyyy>/<yyyy-mm-dd>/insertDB_tables.log, shared by the two functions. Each line has the process number, the time, the level and the function name:

[774187] 2026-10-26 06:53:48.372 > [INF] insertArchiveSourceCatalogs2DB start on euclid...: RootDir ..., tables src_visit,src_proc in localhost:9000/last_test; spec ...; DryRun 0, StampStatus 1, MaxVisits Inf
[774187] 2026-10-26 06:53:56.787 > [INF] insertArchiveSourceCatalogs2DB injected .../proc/212940v1: 44152 coadd, 317451 epoch rows; read 1.22+2.30 s, convert 0.09+0.27 s, bjd 1.56+0.68 s, insert 0.10+0.42 s, stamp 0.01 s
[774187] 2026-10-26 06:53:56.796 > [WRN] insertArchiveSourceCatalogs2DB skipped .../proc/213000v1: no .status
[774187] 2026-10-26 06:53:56.806 > [INF] insertArchiveSourceCatalogs2DB end: 1 visits injected, 0 dry run, 0 skipped (read errors), 1 without .status, 0 done before; 44152 coadd + 317451 epoch rows; 8.4 s
  • Info: the start of a run (host, folders, tables, database, design files, options), the number of visit folders, one line per inserted visit (or dry-run visit), and the totals at the end.
  • Warning: a visit folder without .status, or a visit that cannot be read (with the error and where it happened).
  • Error: a failure before the first visit (design file, DB connection, table check) or a DB or stamping error that stops the run; the totals so far are written before the error is passed on.
  • Verbose (file only): each .status stamp.

The levels shown on the screen and written to the file are those of config/MsgLogger.yml (by default Info and above on the screen, everything in the file). To write elsewhere, or to share one file between several runs:

L = pipeline.last.insertDB.ingestLogger('LogDir','/home/euclid/ingest_logs', 'Console',false);
pipeline.last.insertDB.insertArchiveImageHeaders2DB(RootDir, ..., 'Logger',L);
pipeline.last.insertDB.insertArchiveSourceCatalogs2DB(RootDir, ..., 'Logger',L);

The functions write the lines with pipeline.last.insertDB.ingestLog, not with MsgLogger.msgLog, because of the problems described in issue #1456.

Examples:

W = '/home/euclid/ingest_timing/';     % folder with the frozen discussion texts (here the test folder on euclid)
% dry run of one visit: nothing is inserted or stamped
R = pipeline.last.insertDB.insertArchiveImageHeaders2DB('/bigdata3/projects/last/data/LAST.01.04.02/2026/10/20/', ...
        'DryRun',true, 'MaxVisits',1, 'VisitSpec',[W 'image_visit_1409.md'], 'ProcSpec',[W 'image_proc_1413.md']);
% a test on archive folders, on euclid: insert into last_test without stamping .status
R = pipeline.last.insertDB.insertArchiveSourceCatalogs2DB('/bigdata3/projects/last/data/LAST.01.04.02/2026/10/20/', ...
        'DbHost','localhost', 'StampStatus',false, 'Spec',[W 'src_tables_1411.md']);
% a whole archive, with .status stamps
pipeline.last.insertDB.insertArchiveImageHeaders2DB('/bigdata3/projects/last/data/', 'ProcDirTemplate','LAST*/2026/*/*/proc/*', ...
        'DbHost','localhost', 'VisitSpec',[W 'image_visit_1409.md'], 'ProcSpec',[W 'image_proc_1413.md']);

Notes

  • Run time (euclid, one camera-night of 79 visits, 2026 Oct 20; average per visit): image tables 4.0 s, source tables 7.4 s (3.2 s and 5.5 s when the files are already in the disk cache). Most of it is reading the files, mainly loading the epoch MAT files; the insert is under 10%. Details: #1411.
  • Re-reductions: the tables are MergeTree, which keeps duplicate rows. If a visit is reduced again (a new v<N> folder) after it was inserted, its rows are inserted again; the old rows have to be deleted by hand.
  • Missing keywords: a keyword or catalogue column that a product does not have (for example a keyword added in a later pipeline version) gets the column default ('', NaN or 0).
  • MATLAB path: the source-table function needs the INPOP ephemeris and the Earth-orientation file of the AstroPack data folder (~/matlab/data/SolarSystem) for bjd and baryvel.
  • IDs are read from the header text directly into UInt64, never through a double, which would lose the lowest digits.
⚠️ **GitHub.com Fallback** ⚠️