CTHUB - VIN Decode ICBC Historical Data

[danclancy85]

Describe the task
Enable a one-time decoding of all distinct VINs stored in the historical ICBC dataset within the CTHUB development environment using both Vinpower and VPIC services.

Purpose
The business area needs all of the VINs in the historical ICBC data set decoded to support goal of having this historical data set cleaned, formatted, and decoded and ready for data science investigations.

Acceptance Criteria

• All distinct VINs (e.g. VINs that have not previously been decoded) from the ICBC table in the CTHUB dev environment are extracted.
• VINs are decoded using Vinpower
• VIN decoding results are stored within existing existing Vinpower table within CTHUB

Development Checklist

• Write a django command file that decodes vins that are not already decoded using vinpower (see django/api/management/commands)
• Merge file to dev and run from openshift

Additional context

• This is intended to be a one-time action on the historical data set.
• There is an existing batch_decode function that uses the instance of vinpower we have to decode instead of hitting their API

[tim738745]

I think the historical ICBC dataset is the "icbc" table inside the cthub dev database; if this is the case, that table has over 128 million records in it the last time I checked, so whoever works on it will probably have to stream the data while processing it; loading it all into memory at once will probably cause issues. I'm not sure if Django ORM has a streaming feature; if not, you can probably use psycopg server-side cursors (https://www.psycopg.org/docs/usage.html#server-side-cursors).

[JulianForeman]

After talking to Tim, more conversation with the BA around VPIC is needed, it may not be feasible to decode using that service.

[danclancy85]

@JulianForeman is this a usage limit from VPIC? From speaking with Katia, she estimated only a few million distinct VINs that need to be decoded (e.g. some VINs are only 1 digit, some VINs have already been decoded). Want me to connect you with them to discuss the details?