Automate ALC Inspection Importing

[RFK250]

As an ALC administrative user of NRPTI, I want the ALC inspection data to be imported and published automatically so that I don't need to take manual steps to import data and can use my time more effectively.

As a user of NRCED, I want to see the most recent available data possible so that I have confidence that government is conducting the compliance and enforcement activities necessary to protect the environment.

As a member of the media using NRCED, I want to see the most recently available data possible so that I can include the relevant NRCED hyperlinks to records in the articles I write.

Notes:

• Currently, an ALC administrative user conducts manual queries of NRIS and then uploads them to NRCED. This is only done periodically, meaning that an inspection record created in NRIS today may not show up on NRCED for several months. This impacts the recency and relevancy of the data on NRCED.
• ALC inspection data is stored on NRIS. However, the NRIS Web Service applet does not have a configuration for exporting ALC inspection data the same way that it does for EPD inspections and EMLI inspections.
• The FLNR-NRIS-CSV model for importing from NRIS is likely the best analogue: a microservice (FME Server) sits in Zone B and queries NRIS (CVIS) nightly. It then pushes algorithmically transformed data to NRPTI in OpenShift. This eliminates the security concerns associated with a pull approach.
• The data transforms necessary are coded into the ALC-CSV importer.
• Data source: NRIS (CSV file)

Questions for Kyle

• Q: Is there API documentation for NRIS? Which endpoint do we need to hit for this ALC inspection data?

• A: Everything is in NRPTI GitHub.

• Q: What is an FMS server?

• A: A microservice that runs in Zone B (same security zone as legacy databases - COORS, CVIS). data transforms (names removed) then pushes to NRPTI API. Only FLNR NRIS-importer uses it now. Devs would need to:

• get access to FME server - (URL should be in GitHub documentation below)

• mirror FLNR approach Documentation:
  https://github.com/bcgov/NRPTI/pull/837 https://github.com/bcgov/NRPTI/issues?q=fme

ACTION: Kyle to work with ALC to solidify what needs to be done. To make sure their inspected property owner value (INC, LTD) - these are currently being mixed up. Nothing exists to determine the difference between company and individual names. - June 21 This has been addressed with their inspectors, who will stop the practice of mixing company and individual names in the Inspected Property Owner field. ALC staff will also retroactively update Inspected Property Owner values in the source system so that existing records are not mixed up either.

• Q: What is "periodically"?
• A: The goal is for users to be able to import on a daily basis. (Originally importing on quarterly basis because that was what they had capacity for).

[RFK250]

@jakemorr Over to you for prioritization. There are a few importers that could use automating, so it's probably worth creating an Epic for all of them. This one is likely the simplest because there's a working model to follow.

[RFK250]

Whoever tackles #1054 will likely gain the knowledge needed to write a ticket for this story.

[acatchpole]

• https://apps.nrs.gov.bc.ca/int/confluence/display/DEVGUILD/FME+Workbench+and+ETL+planning
• NRPT-733: import FLNRO inspections from FME csv output #837 https://github.com/bcgov/NRPTI/pull/837
• https://apps.nrs.gov.bc.ca/int/confluence/display/AR/2021-02-12+NRPTI+data+flow+options
• https://apps.nrs.gov.bc.ca/int/confluence/display/DEVGUILD/FME+-+Feature+Management+Engine
• https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Form/Workbench/workspaces_about.htm
• NRPTI-1095 Research Accessing FME Server https://github.com/bcgov/NRPTI/issues/1095

NRIDS Service ticket for GTS access https://apps.nrs.gov.bc.ca/int/jira/servicedesk/customer/portal/1/SD-108006 Possible link to person who can grant GTS access https://apps.nrs.gov.bc.ca/int/jira/browse/SD-72030

[sanjaytkbabu]

FME Web portal access creds are present in relevant links

[acatchpole]

Unknowns :

• What are the NRIS ALC endpoints/tables to hit? Ticket says info should be in NRPTI repo but cannot find (likely that the existing connection in FME Server is for the NRIS DB but need to know what tables to query)
• how to access the S3 bucket that the data is pushed to? Is this necessary or is it set up to push to NRCED?
• what does the data incoming from NRIS look like?
• what should the transformed data look like? (probably alc-csv-constants.ts shows required fields and expected headers)
• do i need any additional access? From FME Access Request page https://apps.nrs.gov.bc.ca/int/confluence/pages/viewpage.action?pageId=91199247 :
  1. GTS access (Cisco desktop with FME desktop apps) - Granted to acatchpole!
  2. FME Server - Have login credentials! (see Relevant Links doc in Team Chat files)
  3. NRM Hosted Database (NRIS ALC DB?) - unsure. Already accessing NRIS DB for FLNRO import so maybe already have this access
  4. BCGW (don't know what this is) - doc says should have access with GTS so probably - Granted?

[RFK250]

I think when my answer for the first bullet was transcribed, some context was lost in the translation. A better way of putting it is: whatever documentation is in the NRPTI GitHub is what's available - which doesn't necessarily mean it's all there.

That being said: if you are replicating the NRIS-FLNR-CSV approach (i.e. using FME server), then you should focus on the FME server side of things. That's what queries the data from NRIS, puts it into a CSV file, and pushes that file to the S3 bucket.

Thus (and I think you've already figured this out) if you configure FME server to create a CSV file with the same syntax that the current ALC-CSV importer expects, then you can just reuse the ALC-CSV importer as is - just point it to the location of the CSV file that FME server creates, and set up a cron job to trigger the importer once a night. Make sense?

[acatchpole]

Special views for NRO inspections were created for FLNR for a variety of business needs. Such views do not exist for ALC records. When querying for these records, it will be from a table that holds ALL records. Will need to filter to ALC and Inspection records. Data will also come as XML. Also, FMES will need new credentials to allow access to this view.

CVIS Record View (probably) : ENFPROD1 -- CORS.CORS_CV_ASSESSMENTS_XVW Previous research:

Process for getting FMES access to ALC inspections in CVIS:

1. Identify all required fields
2. Identify and get approval from data custodian for ALC records in NRIS
3. Create service request for access

KL&OS will see if it is appropriate for them to create a test document of what the extracted XML might look like so work on the necessary transformations within FME can begin.

[acatchpole]

SR Created -> https://apps.nrs.gov.bc.ca/int/jira/servicedesk/customer/portal/1/SD-112444

The current plan is to give the existing proxy account the additional permissions it needs to access the required views.

There may yet be some push back to being granted full access to CVIS. If there is, we will need to figure out how to request new views created that serve ONLY the ALC inspection records. In that case, there might also be some costs associated with this work.

[acatchpole]

It looks like views are getting made. Once that is done, we will either need to get permission to access those views granted to the existing FME service account (Proxy_FME_NRIS ), or have a new service account created.

And then it is a matter of setting up the extraction with FME. You will need GTS access which is access to a Windows VM with FME Workshop installed. Using the existing FLRNO FME file as a template(found here ), will need to edit so that:

• the appropriate view is queried
• new transformations take place that take XML from the query and turn into .csv with the fields the existing ALC .csv import process expects.
• the .csv is pushed to the existing S3 bucket with an appropriate name. 'nris-alc-csv' is what I assumed in the cronjob update I made.

This branch has changes that should handle the import from the S3 bucket, but the model for the ALC csv needs to be confirmed.

The links included further back in this ticket should provide all the other context needed for this ticket. especially the FME Confluence pages and Service Request 112444.

There is also an example ALC .csv (the kind they currently manually create and import) in the NRPTI Teams files (here). Note that not all of the fields in that file are actually consumed by NRPTI.