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?

https://github.com/jqnatividad/qsv/blob/17cc6eb9bcac3cdd6d7aa053ca6f821e8c91d3ea/tests/test_sqlp.rs#L932-L967

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:

https://github.com/jqnatividad/qsv/blob/0c206c5b6a1a141f873da34f3e007da91e14d979/src/cmd/sqlp.rs#L190-L197

[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