You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The datapackage descriptors we are currently generating to annotate the SQLite DBs which are derived from XBRL data are not valid. For example, in the ferc-xbrl-extractor environment running this command:
{
"version": "4.40.11",
"time": 0.001,
"errors": [],
"tasks": [
{
"resource": {
"path": "sqlite:////Users/zane/code/catalyst/pudl-work/output/ferc714_xbrl.sqlite",
"profile": "tabular-data-resource",
"name": "planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_table_03_2_instant",
"dialect": {
"table": "planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_table_03_2_instant"
},
"title": "03.2 - Schedule - Planning Area Hourly Demand and Forecast Summer and Winter Peak Demand and Annual Net Energy for Load, Table - instant",
"description": "ferc:SchedulePlanningAreaHourlyDemandAndForecastSummerAndWinterPeakDemandAndAnnualNetEnergyForLoadBAbstract",
"format": "sqlite",
"mediatype": "application/vnd.sqlite3",
"schema": {
"fields": [
{
"name": "entity_id",
"title": "Entity Identifier",
"type": "string",
"format": "default",
"description": "Unique identifier of respondent"
},
{
"name": "filing_name",
"title": "Filing Name",
"type": "string",
"format": "default",
"description": "Name of filing"
},
{
"name": "date",
"title": "Instant Date",
"type": "date",
"format": "default",
"description": "Date of instant period"
},
{
"name": "planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_axis",
"title": "Planning Area Hourly Demand and Forecast Summer and Winter Peak Demand and Annual Net Energy for Load [Axis]",
"type": "string",
"format": "default",
"description": "Typed dimension used to distinguish a set of related facts about planning area hourly demand and forecast summer and winter peak demand and annual net energy for load."
}
],
"primary_key": [
"entity_id",
"filing_name",
"date",
"planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_axis"
]
},
"scheme": "",
"hashing": "md5",
"stats": {
"hash": "",
"bytes": 0
}
},
"time": 0.001,
"scope": [],
"partial": false,
"errors": [
{
"code": "scheme-error",
"name": "Scheme Error",
"tags": [],
"note": "cannot create loader \"\". Try installing \"frictionless-\"",
"message": "The data source could not be successfully loaded: cannot create loader \"\". Try installing \"frictionless-\"",
"description": "Data reading error because of incorrect scheme."
}
],
"stats": {
"errors": 1
},
"valid": false
}
],
"stats": {
"errors": 1,
"tasks": 1
},
"valid": false
}
The problem?
I think the issue here is that we are using v4 of the frictionless package, and the ability to annotate SQLite DBs was only introduced in v5. Looking at the datapacakge.json file, I see that tie dialect field is invalid. In frictionless v5, it would need to say sql and and then point at the table within the sql dictionary. in previous versions it would describe the CSV dialect that's being used in the file that the path element points at. See this example of data package annotating an SQLite DB.
{
"path": "sqlite:////Users/zane/code/catalyst/pudl-work/output/ferc714_xbrl.sqlite",
"profile": "tabular-data-resource",
"name": "planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_table_03_2_instant",
"dialect": {
"table": "planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_table_03_2_instant"
},
"title": "03.2 - Schedule - Planning Area Hourly Demand and Forecast Summer and Winter Peak Demand and Annual Net Energy for Load, Table - instant",
"description": "ferc:SchedulePlanningAreaHourlyDemandAndForecastSummerAndWinterPeakDemandAndAnnualNetEnergyForLoadBAbstract",
"format": "sqlite",
"mediatype": "application/vnd.sqlite3",
"schema": {
"fields": [
{
"name": "entity_id",
"title": "Entity Identifier",
"type": "string",
"format": "default",
"description": "Unique identifier of respondent"
},
{
"name": "filing_name",
"title": "Filing Name",
"type": "string",
"format": "default",
"description": "Name of filing"
},
{
"name": "date",
"title": "Instant Date",
"type": "date",
"format": "default",
"description": "Date of instant period"
},
{
"name": "planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_axis",
"title": "Planning Area Hourly Demand and Forecast Summer and Winter Peak Demand and Annual Net Energy for Load [Axis]",
"type": "string",
"format": "default",
"description": "Typed dimension used to distinguish a set of related facts about planning area hourly demand and forecast summer and winter peak demand and annual net energy for load."
}
],
"primary_key": [
"entity_id",
"filing_name",
"date",
"planning_area_hourly_demand_and_forecast_summer_and_winter_peak_demand_and_annual_net_energy_for_load_axis"
]
}
}
Frictionless v4 can't interpret sqlite:// URL as path
As it is, the system sees the sqlite:// URL in the path and has no idea how to interpret it to find the data.
The sqlite:// path must be relative not absolute
In addition to being unable to interpret the sqlite:// URL as a path at all, the URL uses an absolute path rather than a relative path, which is invalid. It is invalid both in that it violates the frictionless data resource specification which says:
A “url-or-path” is a string with the following additional constraints:
MUST either be a URL or a POSIX path
URLs MUST be fully qualified. MUST be using either http or https scheme. (Absence of a scheme indicates MUST be a POSIX path)
POSIX paths (unix-style with / as separator) are supported for referencing local files, with the security restraint that they MUST be relative siblings or children of the descriptor. Absolute paths (/) and relative parent paths (…/) MUST NOT be used, and implementations SHOULD NOT support these path types.
In addition, the absolute path is simply wrong if you download our nightly build outputs since the path to which the descriptor and databases were written on the build server have no meaning on the user's machine:
If we want to annotate SQLite DBs, I think we need to update to Frictionless v5 and follow the datapackage standards for annotating SQL sources, including using a relative path to the DB from the location of the datapackage.json file.
We should be validating every datapackage that we output during the nightly builds and in the integration tests for this repository.
If we want to, I think we can annotate all of the SQLite DBs and Parquet outptus using a single large datpackage descriptor.
The text was updated successfully, but these errors were encountered:
@zaneselvans thanks for investigating this. We do have an integration test that is supposed to check for valid datapackage's, but clearly it needs to be overhauled. We currently only test the Ferc1 datapackage, and it uses the Package property metadata_valid to check for validity, which is maybe insufficient?
IIRC that check will probably only look at the Package-level metadata, and not recurse down into any of the resources which make up the package (which confused the heck out of me when I was first working with the datapackage validations).
This came up again in #242 and I tried to fix it with no luck. I've made the paths relative, but I've run into another error for all of the tables: Please provide "dialect.sql.table" for reading. I spent some time messing around with the dialect, but couldn't resolve the issue, so I'm going to leave this open for the time being.
The datapackage descriptors we are currently generating to annotate the SQLite DBs which are derived from XBRL data are not valid. For example, in the
ferc-xbrl-extractor
environment running this command:Results in a bunch of errors like:
Or if we try and validate a single resource and return the errors in JSON form:
The problem?
I think the issue here is that we are using v4 of the
frictionless
package, and the ability to annotate SQLite DBs was only introduced in v5. Looking at thedatapacakge.json
file, I see that tiedialect
field is invalid. Infrictionless
v5, it would need to saysql
and and then point at the table within thesql
dictionary. in previous versions it would describe the CSV dialect that's being used in the file that thepath
element points at. See this example of data package annotating an SQLite DB.Frictionless v4 can't interpret
sqlite://
URL as pathAs it is, the system sees the
sqlite://
URL in thepath
and has no idea how to interpret it to find the data.The
sqlite://
path must be relative not absoluteIn addition to being unable to interpret the
sqlite://
URL as a path at all, the URL uses an absolute path rather than a relative path, which is invalid. It is invalid both in that it violates the frictionless data resource specification which says:A “url-or-path” is a string with the following additional constraints:
In addition, the absolute path is simply wrong if you download our nightly build outputs since the path to which the descriptor and databases were written on the build server have no meaning on the user's machine:
What to do?
datapackage.json
file.The text was updated successfully, but these errors were encountered: