electric-vehicles

// SPDX-FileCopyrightText: 2023 Friedrich-Alexander-Universitat Erlangen-Nurnberg
//
// SPDX-License-Identifier: AGPL-3.0-only

// Example 2: Electric Vehicles
// Learning goals:
// - Understand further core concepts transforms and value types
// - Understand how to construct a pipeline with multiple sinks
// - Understand the use of runtime parameters

// 0. We can use elements defined in other files with the "use" syntax.
// In this case, we use the value type UsStateCode when later specifying the table column value types.
use {
  UsStateCode
} from './state-codes.jv';


// 1. This Jayvee model describes a pipeline
// from a CSV file in the web
// to a SQLite file and a PostgreSQL db sink.
pipeline ElectricVehiclesPipeline {
  // See here for meta-data of the data source
  // https://catalog.data.gov/dataset/electric-vehicle-population-data/resource/fa51be35-691f-45d2-9f3e-535877965e69

  // 2. At the top of a pipeline, we describe the
  // structure of the pipeline. The first part until
  // the ElectricRangeTransformer is a linear sequence
  // of blocks. From there we can see a split into two
  // parallel sequences that load the data in to two
  // different sinks.
  ElectricVehiclesHttpExtractor
    -> ElectricVehiclesTextFileInterpreter
    -> ElectricVehiclesCSVInterpreter
    -> ElectricVehiclesTableInterpreter
    -> ElectricRangeTransformer;

  ElectricRangeTransformer
    -> ElectricVehiclesSQLiteLoader;

  ElectricRangeTransformer
    -> ElectricVehiclesPostgresLoader;

  // 3. After the pipeline structure, we define the blocks used.
  block ElectricVehiclesHttpExtractor oftype HttpExtractor {
    url: "https://data.wa.gov/api/views/f6w7-q2d2/rows.csv?accessType=DOWNLOAD";
  }

  block ElectricVehiclesTextFileInterpreter oftype TextFileInterpreter { }

  block ElectricVehiclesCSVInterpreter oftype CSVInterpreter { }

  valuetype ElectricVehicle {
    property vin oftype VehicleIdentificationNumber10;
    property county oftype text;
    property city oftype text;
    property state oftype UsStateCode;
    property postal oftype text;
    property modelYear oftype integer;
    property make oftype text;
    property model oftype text;
    property evType oftype text;
    property cafvEligibility oftype text;
    property electricRange oftype integer;
    property baseMSRP oftype integer;
    property legislativeDistrict oftype text;
    property dolID oftype integer;
    property location oftype text;
    property utility oftype text;
    property censusTract oftype text;
  }

  transform ElectricVehicleParser {
    from r oftype Collection<text>;
    to ev oftype ElectricVehicle;

    ev: {
      vin: r cellInColumn "VIN (1-10)",
      county: asText (r cellInColumn "County"),
      city: asText (r cellInColumn "City"),
      state: asText (r cellInColumn "State"),
      postal: asText (r cellInColumn "Postal Code"),
      modelYear: asInteger (r cellInColumn "Model Year"),
      make: asText (r cellInColumn "Make"),
      model: asText (r cellInColumn "Model"),
      evType: asText (r cellInColumn "Electric Vehicle Type"),
      cafvEligibility: asText (r cellInColumn "Clean Alternative Fuel Vehicle (CAFV) Eligibility"),
      electricRange: asInteger (r cellInColumn "Electric Range"),
      baseMSRP: asInteger (r cellInColumn "Base MSRP"),
      legislativeDistrict: asText (r cellInColumn "LegislativeDistrict"),
      dolID: asInteger (r cellInColumn "DOL Vehicle ID"),
      location: asText (r cellInColumn "Vehicle Location"),
      utility: asText (r cellInColumn "Electric Utility"),
      censusTract: asText (r cellInColumn "2020 Census Tract"),
    };
  }


  block ElectricVehiclesTableInterpreter oftype TableInterpreter {
    header: true;
    columns: ElectricVehicle;
    parseWith: ElectricVehicleParser;
  }

  // 5. This block describes the application of a transform function
  // taking a column as input and adding another computed column.
  // The applied transform function is defined below and referenced
  // by the "use" property.
  block ElectricRangeTransformer oftype TableTransformer {
    inputColumns: [
      "electricRange"
    ];
    outputColumn: "electricRange (km)";
    uses: MilesToKilometers;
  }

  // 6. Here, we define a transform function, taking parameters
  // as input ("from" keyword), and producing an output ("to" keyword).
  // Inputs and outputs have to be further described by a value type.
  transform MilesToKilometers {
    from miles oftype decimal;
    to kilometers oftype integer;

    // 7. In order to express what the transform function does,
    // we assign an expression to the output. Values from the input and output of the transform can be referred to by name.
    kilometers: round (miles * 1.609344);
  }

  block ElectricVehiclesSQLiteLoader oftype SQLiteLoader {
    table: "ElectricVehiclePopulationData";
    file: "./electric-vehicles.sqlite";
  }

  block ElectricVehiclesPostgresLoader oftype PostgresLoader {
    // 8. The requires keyword allows us to define runtime parameters.
    // These values have to be provided as environment variables when interpreting the Jayvee model.
    host: requires DB_HOST;
    port: requires DB_PORT;
    username: requires DB_USERNAME;
    password: requires DB_PASSWORD;
    database: requires DB_DATABASE;
    table: "ElectricVehiclePopulationData";
  }
}

// 9. Below the pipeline, we model user-define value types.
// We give them a speaking name and provide a base value type
// that this value type builts on. User-defined value types always place additional constraints on existing value types.
valuetype VehicleIdentificationNumber10 {
  property id oftype text;
  // 10. Value types can be further refined by providing constraints.
  constraint capitalized: OnlyCapitalLettersAndDigits on id;
  // 11. Constraints can also be defined inside the value type. This constraint
  // ensures that all ids have a length of 10 characters
  constraint exactlyTenCharacters: lengthof id == 10;
}

// 12. This constraint works on text value types and requires values
// to match a given regular expression in order to be valid.
constraint OnlyCapitalLettersAndDigits on text: value matches /^[A-Z0-9]*$/;