Follow up on JHU data

[dajmcdon]

Actual Behavior:

Calling (via R package)

covidcast_signal("jhu-csse","deaths_incidence_num", geo_type = "state")

Produces many warnings, all of the form:

Fetching deaths_incidence_num from jhu-csse for 20200322 in geography '*': no results

From 1/22--2/19, there are 51 states in the API with data. From 7/28-10/16, there are 52 states. In between, nearly all data is missing. The only available data in between are 19 unique dates with values for MO. All of these MO values have issue data 10/15.

[krivard]

Confirmed; this is due to me not updating the is_latest_issue column when I dropped the bad issues on Saturday. @korlaxxalrok and/or @melange396 can you take a look and either repair the db directly or propose a fix?

I take it back; you're talking about a num signal. Investigating...

[krivard]

Curious results.

These are the queries I ran on Saturday:

MariaDB [epidata]> delete from covidcast where source="jhu-csse" and `signal`="confirmed_incidence_prop" and geo_type="state" and issue>=20201007;
Query OK, 35219 rows affected (5 min 30.71 sec)

MariaDB [epidata]> delete from covidcast where source="jhu-csse" and `signal` in ("confirmed_cumulative_prop", "confirmed_7dav_cumulative_prop", "confirmed_incidence_prop", "confirmed_7dav_incidence_prop", "deaths_cumulative_prop", "deaths_7dav_cumulative_prop", "deaths_incidence_prop", "deaths_7dav_incidence_prop") and geo_type="state" and issue>=20201007;
Query OK, 245674 rows affected (15 min 53.07 sec)

Neither query should have impacted any of the num signals.

Nevertheless,

MariaDB [epidata]> select time_value,sum(is_latest_issue) from covidcast where source="jhu-csse" and `signal`="deaths_incidence_num" and time_value>20200219 and time_value<20200230 and geo_type="state" group by time_value;
+------------+----------------------+
| time_value | sum(is_latest_issue) |
+------------+----------------------+
|   20200220 |                    0 |
|   20200221 |                    0 |
|   20200222 |                    0 |
|   20200223 |                    0 |
|   20200224 |                    0 |
|   20200225 |                    0 |
|   20200226 |                    0 |
|   20200227 |                    0 |
|   20200228 |                    0 |
|   20200229 |                    0 |
+------------+----------------------+

We would have expected sum(is_latest_issue) to be 1 52 for each time_value, so something has to have horked up that column assignment.

[melange396]

we have fill_is_latest_issue.py which can fix that column... i can add some stuff to it to narrow it down to just the JHU data and split up by time_value and/or signal in the interest of being kind to the DBMS.

do we want to run this for just source="jhu-csse" and geo_type="state" and 20200220 <= time_value <= 20200727? seeing as how @krivard 's SQL statements shouldn't have caused this problem, perhaps we should not assume its limited to this subset of the data?

i would also like to propose storing the ids that need to be set to is_latest_issue, then committing those writes in a transaction outside of the large JOIN operations. i could be mistaken, but i think this should spend less time with a lock on the table BUT we will need to make sure no other writes happen during this time because consistency. comments greatly appreciated.

[krivard]

I think we want to run it for all of source="jhu-csse". My hypothesis is that the problem in the num signals was introduced during the cache repopulation job I ran on Friday, which was done for all signals and geo levels outside of Automation's purview. There was a collision when Automation started up an ingestion run while JHU was still working through the archive diff, which modifies the receiving directory in place at the same time that Automation is trying to read files out of the same directory. I thought I had caught it in time with a suspend signal, but it looks like the Automation job failed. If that happened before the is_latest_issue column update could be run, that would explain the problems in num, and it would mean that the prop problems would extend beyond state.

I like the idea of doing the update in a separate transaction from the JOINs. JHU is currently on hold*, so there shouldn't be any conflicting writes even if the CSV import job runs while we're doing the fill. If we want to be 100% sure, we can pause Automation while the fill is running. @korlaxxalrok thoughts?

*(it ran this morning because I'd only pushed it forward to Tuesday, but I'm about to clear away what it left in receiving. I've pushed the next run to Saturday morning; before EOD Friday we should either push it forward again or re-sync the cache)

[korlaxxalrok]

Is it worth trying to simulate this on staging? Maybe load the jhu data and run through it?

[krivard]

That would be wise. Do you think we can get through that with enough time to complete the fix on prod by Friday morning?

[korlaxxalrok]

Might be possible to do this with Mariabackup. We'd basically be doing a physical restore from production. That may be also be generally useful so that whatever we end up doing more of in staging has relatively refreshed data to work with.

[korlaxxalrok]

I can look to see how possible it would be to get it in there. Some of the time needed will be taking the backup and then moving it around and all that. Not sure how big it will be. I'll check.

[korlaxxalrok]

I'm copying over a backup now. Will see if I can have it in place by eod/early tomorrow (in case it takes forever).