feat: Onboard Travel Sustainability dataset (schema update) (#298)

GoogleCloudPlatform · Feb 22, 2022 · 7a13daa · 7a13daa
1 parent 7a009f6
commit 7a13daa
Show file tree

Hide file tree

Showing 4 changed files with 104 additions and 34 deletions.
diff --git a/datasets/travel_sustainability/infra/flight_emissions_pipeline.tf b/datasets/travel_sustainability/infra/flight_emissions_pipeline.tf
@@ -16,15 +16,10 @@
 
 
 resource "google_bigquery_table" "travel_sustainability_flight_emissions" {
- project = var.project_id
- dataset_id = "travel_sustainability"
- table_id = "flight_emissions"
-
+ project = var.project_id
+ dataset_id = "travel_sustainability"
+ table_id = "flight_emissions"
  description = "Flight emissions data"
-
-
-
-
  depends_on = [
  google_bigquery_dataset.travel_sustainability
  ]

diff --git a/datasets/travel_sustainability/infra/metadata_pipeline.tf b/datasets/travel_sustainability/infra/metadata_pipeline.tf
@@ -16,15 +16,10 @@
 
 
 resource "google_bigquery_table" "travel_sustainability_metadata" {
- project = var.project_id
- dataset_id = "travel_sustainability"
- table_id = "metadata"
-
+ project = var.project_id
+ dataset_id = "travel_sustainability"
+ table_id = "metadata"
  description = "Metadata about the dataset"
-
-
-
-
  depends_on = [
  google_bigquery_dataset.travel_sustainability
  ]

diff --git a/datasets/travel_sustainability/pipelines/flight_emissions/flight_emissions_dag.py b/datasets/travel_sustainability/pipelines/flight_emissions/flight_emissions_dag.py
@@ -42,35 +42,83 @@
  skip_leading_rows=1,
  write_disposition="WRITE_TRUNCATE",
  schema_fields=[
+ {
+ "name": "carrier",
+ "type": "STRING",
+ "mode": "REQUIRED",
+ "description": "IATA code of the airline operating the flight",
+ },
+ {
+ "name": "flight_number",
+ "type": "STRING",
+ "mode": "REQUIRED",
+ "description": "Flight number assigned by the operating airline",
+ },
+ {
+ "name": "departure_date",
+ "type": "DATE",
+ "mode": "REQUIRED",
+ "description": "Departure date of the flight",
+ },
  {
  "name": "origin",
  "type": "STRING",
  "mode": "REQUIRED",
- "description": "IATA code for origin airport",
+ "description": "IATA airport code of the origin",
  },
  {
  "name": "destination",
  "type": "STRING",
  "mode": "REQUIRED",
- "description": "IATA code for destination airport",
+ "description": "IATA airport code of the destination",
  },
  {
- "name": "aircraft_model",
- "type": "STRING",
+ "name": "economy_co2e_grams_per_pax",
+ "type": "INTEGER",
+ "mode": "REQUIRED",
+ "description": "Estimated CO2e in grams for one passenger in economy cabin including non-CO2 effects",
+ },
+ {
+ "name": "premium_economy_co2e_grams_per_pax",
+ "type": "INTEGER",
+ "mode": "REQUIRED",
+ "description": "Estimated CO2e in grams for one passenger in premium economy cabin including non-CO2 effects",
+ },
+ {
+ "name": "business_co2e_grams_per_pax",
+ "type": "INTEGER",
+ "mode": "REQUIRED",
+ "description": "Estimated CO2e in grams for one passenger in business cabin including non-CO2 effects",
+ },
+ {
+ "name": "first_co2e_grams_per_pax",
+ "type": "INTEGER",
+ "mode": "REQUIRED",
+ "description": "Estimated CO2e in grams for one passenger in first cabin including non-CO2 effects",
+ },
+ {
+ "name": "economy_co2_grams_per_pax",
+ "type": "INTEGER",
+ "mode": "REQUIRED",
+ "description": "Estimated CO2 in grams for one passenger in economy cabin excluding non-CO2 effects",
+ },
+ {
+ "name": "premium_economy_co2_grams_per_pax",
+ "type": "INTEGER",
  "mode": "REQUIRED",
- "description": "IATA code for aircraft model",
+ "description": "Estimated CO2 in grams for one passenger in premium economy cabin excluding non-CO2 effects",
  },
  {
- "name": "co2e_total_grams",
+ "name": "business_co2_grams_per_pax",
  "type": "INTEGER",
  "mode": "REQUIRED",
- "description": "Total grams of CO2e estimated for the flight including non-CO2 effects",
+ "description": "Estimated CO2 in grams for one passenger in business cabin excluding non-CO2 effects",
  },
  {
- "name": "co2_total_grams",
+ "name": "first_co2_grams_per_pax",
  "type": "INTEGER",
  "mode": "REQUIRED",
- "description": "Total grams of CO2 estimated for the flight",
+ "description": "Estimated CO2 in grams for one passenger in first cabin excluding non-CO2 effects",
  },
  ],
  )

diff --git a/datasets/travel_sustainability/pipelines/flight_emissions/pipeline.yaml b/datasets/travel_sustainability/pipelines/flight_emissions/pipeline.yaml
@@ -72,26 +72,58 @@ dag:
  write_disposition: "WRITE_TRUNCATE"
 
  schema_fields:
+ - name: "carrier"
+ type: "STRING"
+ mode: "REQUIRED"
+ description: "IATA code of the airline operating the flight"
+ - name: "flight_number"
+ type: "STRING"
+ mode: "REQUIRED"
+ description: "Flight number assigned by the operating airline"
+ - name: "departure_date"
+ type: "DATE"
+ mode: "REQUIRED"
+ description: "Departure date of the flight"
  - name: "origin"
  type: "STRING"
  mode: "REQUIRED"
- description: "IATA code for origin airport"
+ description: "IATA airport code of the origin"
  - name: "destination"
  type: "STRING"
  mode: "REQUIRED"
- description: "IATA code for destination airport"
- - name: "aircraft_model"
- type: "STRING"
+ description: "IATA airport code of the destination"
+ - name: "economy_co2e_grams_per_pax"
+ type: "INTEGER"
+ mode: "REQUIRED"
+ description: "Estimated CO2e in grams for one passenger in economy cabin including non-CO2 effects"
+ - name: "premium_economy_co2e_grams_per_pax"
+ type: "INTEGER"
+ mode: "REQUIRED"
+ description: "Estimated CO2e in grams for one passenger in premium economy cabin including non-CO2 effects"
+ - name: "business_co2e_grams_per_pax"
+ type: "INTEGER"
+ mode: "REQUIRED"
+ description: "Estimated CO2e in grams for one passenger in business cabin including non-CO2 effects"
+ - name: "first_co2e_grams_per_pax"
+ type: "INTEGER"
+ mode: "REQUIRED"
+ description: "Estimated CO2e in grams for one passenger in first cabin including non-CO2 effects"
+ - name: "economy_co2_grams_per_pax"
+ type: "INTEGER"
+ mode: "REQUIRED"
+ description: "Estimated CO2 in grams for one passenger in economy cabin excluding non-CO2 effects"
+ - name: "premium_economy_co2_grams_per_pax"
+ type: "INTEGER"
  mode: "REQUIRED"
- description: "IATA code for aircraft model"
- - name: "co2e_total_grams"
+ description: "Estimated CO2 in grams for one passenger in premium economy cabin excluding non-CO2 effects"
+ - name: "business_co2_grams_per_pax"
  type: "INTEGER"
  mode: "REQUIRED"
- description: "Total grams of CO2e estimated for the flight including non-CO2 effects"
- - name: "co2_total_grams"
+ description: "Estimated CO2 in grams for one passenger in business cabin excluding non-CO2 effects"
+ - name: "first_co2_grams_per_pax"
  type: "INTEGER"
  mode: "REQUIRED"
- description: "Total grams of CO2 estimated for the flight"
+ description: "Estimated CO2 in grams for one passenger in first cabin excluding non-CO2 effects"
 
  graph_paths:
  - "flight_emissions_gcs_to_bq"