Duckdb is a free open-source package. The entire source code is freely available on GitHub. I am an old person who did not know about json files and how to manipulate them. But as I came across Duckdb the connection was made. Duckdb is an in memory database. I don’t need permanent tables, I just want something to read the json easily so I can play around with the data. So this restic command (after a bunch of variables have been set)
.${RESTICEXE} snapshots --json -r $RESTIC_REPOSITORY --cache-dir $CACHEDIR | Out-File -FilePath $RESTIC_SNAPSHOTS_FILE
Then run the duckdb command:
CREATE TABLE restic_c_snapshots AS
SELECT * FROM 'C:\Users\X\Documents\utilities\restic\sample output\restic_C_snapshots.json';
The create table and importing of the json data happens so quickly I often run the duckdb command “select count(*) from restic_c_snapshots;” just to confirm that any data was imported.
Sql commands such as “desc restic_c_snapshots” will list the table name, column names and column types. In the snapshots table is a column called “summary” which is the structure containing the summary data of the snapshot.
By default duckdb shows a limited number of rows to the terminal so use the command “.maxrows 99999” to get many more rows.
select cast (summary.backup_start as timestamp), minute(age(cast (summary.backup_end as timestamp), cast (summary.backup_start as timestamp))) as 'elapsed minutes', printf('%,d', summary.data_added) from restic_c_snapshots order by 2;
will list the start datetime, elapsed minutes, data_added for each snapshot.
select date_diff('day', max(cast (summary.backup_end as timestamp)), current_date ) from restic_c_snapshots order by 1;
will list the number of days since the last backup! The next commands will bring the ls .json output into duckdb.
.${RESTICEXE} ls $snapid -r $RESTIC_REPOSITORY | Out-File -FilePath $RESTIC_LS_FILE
CREATE TABLE ls_1 AS
SELECT * FROM 'C:\Users\X\Documents\utilities\restic\sample output\ls_1.json';
CREATE or REPLACE MACRO pretty_print_size(size) AS
CASE
WHEN size > 1_000_000_000 then printf('%.3f GB', size / 1_000_000_000)
WHEN size > 1_000_000 then printf('%.3f MB', size / 1_000_000)
WHEN size > 1_000 then printf('%.3f KB', size / 1_000)
ELSE printf('%d ', size )
END;
This lists large files which I might want to delete from disk.
select path, name, size, pretty_print_size(size) from ls_1 where size > 1_000_000_000 order by 3;
I found that there is a file /C/Windows/MEMORY.DMP which Windows created that is 2Gb when there was a crash. I deleted it.
Restic users who backup multiple systems will want to select / match the hostname and username.
“.quit” will exit out of duckdb and no files will be saved to disk.
To create restic commands from duckdb use the select command
such as
select 'restic ls --recursive ' || id from restic_c_snapshots order by 1;
copy the one line that you want and paste into your terminal to get output. Hope this help you get easier access to custom reports by using sql. Add any additional comments if this help you.