Is there a way for qsv sqlp --infer-len 0 to compute the schema once then reuse for other queries?

[rzmk]

I'm curious if there's a way to cache the inferred schema from --infer-len 0 when using qsv sqlp and reuse it for sqlp queries. For example I want to run multiple queries on a file but I use --infer-len 0 to infer the schema however if I want to run more queries I might still have to use --infer-len 0 to get the proper inference which could take a while on larger files. A way to save the schema to a file and reuse instead of --infer-len 0 for subsequent queries may be useful to make the time shorter for the command.

[jqnatividad]

Have you tried using a SQL script?

qsv/tests/test_sqlp.rs

Lines 932 to 967 in 17cc6eb

	#[test]
	// #[ignore = "temporarily disable due to a bug in polars aliasing"]
	fn sqlp_boston311_sql_script() {
	let wrk = Workdir::new("sqlp_boston311_sql_script");
	let test_file = wrk.load_test_file("boston311-100.csv");
	wrk.create_from_string(
	"test.sql",
	r#"create table temp_table as select * from "boston311-100" where ontime = 'OVERDUE';
	create table temp_table2 as select * from temp_table limit 10;
	-- we already got what we needed from temp_table into temp_table2
	-- so we can truncate temp_table. Otherwise, the memory taken by temp_table
	-- won't be released until the end of the script
	truncate temp_table;
	select ward,count(*) as cnt from temp_table2 group by ward order by cnt desc, ward asc;"#,
	);
	let mut cmd = wrk.command("sqlp");
	cmd.arg(&test_file).arg("test.sql");
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["ward", "cnt"],
	svec!["Ward 3", "2"],
	svec![" ", "1"],
	svec!["04", "1"],
	svec!["3", "1"],
	svec!["Ward 13", "1"],
	svec!["Ward 17", "1"],
	svec!["Ward 19", "1"],
	svec!["Ward 21", "1"],
	svec!["Ward 6", "1"],
	];
	assert_eq!(got, expected);
	}

sqlp will do --infer-len for each table as it loads it into the session, and you can refer to the same CSV multiple times in the script.

Furthermore, you can materialize the result of a query using a create <TEMP_TABLE_NAME> as select and "cache" the result of a previous query as a table you can use later in the script.

[jqnatividad]

You can now create and reuse polars schemas with qsv 0.137.0, using the sqlp --cache-schema option:

qsv/src/cmd/sqlp.rs

Lines 190 to 197 in 0c206c5

	--cache-schema Create and cache Polars schema JSON files.
	If specified and the schema file/s do not exist, it will save the
	inferred schemas in JSON format. Each schema file will have the same
	file stem as the corresponding input file, with the extension ".pschema.json"
	(data.csv's Polars schema file will be data.pschema.json)
	If the file/s exists, it will load the schema instead of inferring it
	(ignoring --infer-len) and attempt to use it for each corresponding
	Polars "table" with the same file stem.

[jqnatividad]

For reference, here's what the polars schema looks like after serializing it to JSON for the NYC 311 data we use in our benchmarks.

{
  "fields": {
    "Unique Key": "Int64",
    "Created Date": "String",
    "Closed Date": "String",
    "Agency": "String",
    "Agency Name": "String",
    "Complaint Type": "String",
    "Descriptor": "String",
    "Location Type": "String",
    "Incident Zip": "String",
    "Incident Address": "String",
    "Street Name": "String",
    "Cross Street 1": "String",
    "Cross Street 2": "String",
    "Intersection Street 1": "String",
    "Intersection Street 2": "String",
    "Address Type": "String",
    "City": "String",
    "Landmark": "String",
    "Facility Type": "String",
    "Status": "String",
    "Due Date": "String",
    "Resolution Description": "String",
    "Resolution Action Updated Date": "String",
    "Community Board": "String",
    "BBL": "Int64",
    "Borough": "String",
    "X Coordinate (State Plane)": "Int64",
    "Y Coordinate (State Plane)": "Int64",
    "Open Data Channel Type": "String",
    "Park Facility Name": "String",
    "Park Borough": "String",
    "Vehicle Type": "String",
    "Taxi Company Borough": "String",
    "Taxi Pick Up Location": "String",
    "Bridge Highway Name": "String",
    "Bridge Highway Direction": "String",
    "Road Ramp": "String",
    "Bridge Highway Segment": "String",
    "Latitude": "Float64",
    "Longitude": "Float64",
    "Location": "String"
  }
}

So it's really just inferring the datatype of each column...

stats has this info and more, so we can really just derive the polars schema from the stats-cache - thus #2225