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 { }

  block ElectricVehiclesTableInterpreter oftype TableInterpreter {
    header: true;
    columns: [
      // 4. Here, a user-deifned value type is used to describe this column.
      // The capital letter indicates that the value type is not built-in
      // by convention. The value type itself is defined further below. 
      "VIN (1-10)" oftype VehicleIdentificationNumber10,
      "County" oftype text,
      "City" oftype text,
      "State" oftype UsStateCode, // We can just use the element as if it was defined in this file.
      "Postal Code" oftype text,
      "Model Year" oftype integer,
      "Make" oftype text,
      "Model" oftype text,
      "Electric Vehicle Type" oftype text,
      "Clean Alternative Fuel Vehicle (CAFV) Eligibility" oftype text,
      "Electric Range" oftype integer,
      "Base MSRP" oftype integer,
      "Legislative District" oftype text,
      "DOL Vehicle ID" oftype integer,
      "Vehicle Location" oftype text,
      "Electric Utility" oftype text,
      "2020 Census Tract" oftype text,
    ];
  }

  // 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: [
      "Electric Range"
    ];
    outputColumn: "Electric Range (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 oftype text {
  // 10. Value types can be further refined by providing constraints.
  constraints: [
    OnlyCapitalLettersAndDigits,
    ExactlyTenCharacters,
  ];
}

// 11. 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]*$/;

constraint ExactlyTenCharacters on text: value.length == 10;