NTD Ingestion – Remaining Tables in Dataset

[charlie-costanzo]

User story / feature request

Part of #3401

As a data engineer, I would like to ingest the data in the form of gcs blob storage and external tables for the remaining tables in the NTD dataset, extending the work found in scrape_ntd.py and annual_database_service.yml (found below).

Building upon the work completed in #3345

Existing NTD patterns:

• script/scrape_ntd.py
• airflow/dags/create_external_tables/ntd_data_products/annual_database_service.yml

General Cal-ITP Pipeline Patterns

• Data Pipelines Documentation
  • Adding a New Data Source
  • Previous PR Patterns

Acceptance Criteria

I can successfully access the data for the following tables from the https://www.transit.dot.gov/ntd/data-product website in the warehouse as external tables:

• Assets
• Expenses
• Fares/Funding
• Resources
• Security and Safety
• Service (previous years)

Notes

---

[erikamov]

External tables on branch 3403-ntd-api-external-tables

[erikamov]

• External tables for 2022 API data were tested and working. I am still reviewing field types for each column, but could be deployed as is now (using default STRING) and as we work on modeling we can keep changing types as needed.

• historical__major_safety_events and historical__nonmajor_safety_and_security_events external tables for Historical API data are failing because of the column names :@computed_region_8fe2_rd7y and :@computed_region_m2nu_4dib.
  @charlie-costanzo @mjumbewu have you seen this problem before?

  The full error:
  google.api_core.exceptions.BadRequest: 400 Error while reading table: cal-itp-data-infra-staging.external_ntd__annual_reporting.historical__major_safety_events, error message: JSON parsing error in row starting at position 0: JSON object specified for non-record field: latlon File: gs://calitp-ntd-api-products/major_safety_events/historical/dt=2024-09-18/execution_ts=2024-09-18T21:46:57.651684+00:00/historical__major_safety_events.jsonl.gz

• The external tables for XLSX Complete Monthly Ridership with Adjustments and Estimates Data are also failing, but I think it is just my local configuration to access the XLSX files/buckets.

[charlie-costanzo]

Hey @erikamov, thanks for the update!

• External tables for 2022 API data were tested and working. I am still reviewing field types for each column, but could be deployed as is now (using default STRING) and as we work on modeling we can keep changing types as needed.

This is great, I say we get these tables merged as soon as we can and keep iterating as necessary

• historical__major_safety_events and historical__nonmajor_safety_and_security_events external tables for Historical API data are failing because of the column names :@computed_region_8fe2_rd7y and :@computed_region_m2nu_4dib.

Hm, I'm assuming these column names aren't 'BigQuery safe'. If it will get things working, I say we just exclude these columns when listing the column names for the external table, since I don't think they're useful for analysis.

If that doesn't work, then I say we hold off on creating external tables out of these files at the moment since these tables weren't actually requested, I just added them for some unrelated testing and decided to keep them in place. We can always reintroduce them with proper handling, if we learn they are desirable.

• The external tables for XLSX Complete Monthly Ridership with Adjustments and Estimates Data are also failing, but I think it is just my local configuration to access the XLSX files/buckets.

Let me know if I can hep in any way with this one!

[erikamov]

I created the a PR with the 2022 external tables and it is ready to review: #3465
There are tones of columns so I it would take longer to keep reviewing each end point and checking the type of each column. So I think we can start having these tables with mostly columns as STRING and we can change the data types as needed.

I am still working on the XLSX tables.

[erikamov]

2022 External Tables were successfully created on cal-itp-data-infra.external_ntd__annual_reporting.

New PR created for NTD external tables for Complete Monthly Ridership with Adjustments and Estimates Data.

The remaining tables will not be created for now. If there is a need for those I kept the work on a branch. Those table will need some extra work to be created due to position of the column names and invalid characters on the name (see previous comments).

[erikamov]

Last PR merged. Next time the create_external_tables runs it will create those tables.

[erikamov]

Tables created and new fields will be added on the next create_external_tables DAG runs.

[erikamov]

DAG updated all tables successfully.