@jcrespo

Sorry for the confusion, but I've added some lines to the white-list file.

If this happens again, I'll think of another way of storing this white-llist, to avoid more confusion.

Just wanted to add this note here, too.
The activation in production of this auto-purging should wait until we notify all schema owners with the date this will happen.

@jcrespo
I know it's early to know when this can happen. But as soon as you have an estimate, could you please pass it to me, so I can start reaching out to the schema owners to notify that the auto-purging will be activated?
Thanks a lot!

We should implement the auto-purging per schema, not per revision. Because otherwise, every time someone edits a schema, we'd revert it back to the default purging strategy.

So the white-list, instead of:

TABLE_NAME                COLUMN_NAME
EchoInteraction_5539940   clientValidated
EchoInteraction_5539940   event_action
...

Should be:

SCHEMA_NAME       COLUMN_NAME
EchoInteraction   clientValidated
EchoInteraction   event_action
...

Here's the updated white-list TSV:

When auto-purging i.e. the table EchoInteraction_5539940, we should query the white-list for EchoInteraction and keep the data for all white-listed columns. Note that the white-list may contain column names that do not belong to EchoInteraction_5539940, because they belong to other revisions of the same schema. But this should be OK, those can be ignored.

we still need to decide what and how

If it is very dynamic, I would recommend setting a table with table names, that way an event could read it and actuate depending on that. Alternatively, and maybe a better option would be to maintain a list of tables on puppet for a cron job- that would have better visibility.

@jcrespo
I think you're right, the latter seems to have better visibility and also permits to CR changes, which will be good, given that we'll be changing security settings. Let me know if I can help you with that.

@jcrespo

I finished updating the white-list. Sorry for the delay, I needed to make sure that changes to schemas between 2015's audit and today were included and that the respective schema owners were notified and agreed with the purging.

The file is here:

How to understand this file:

• It's a TSV file.
• It has 2 columns: schema and field.
• It is a white-list, meaning that all schema-field pairs listed are to be kept indefinitely in the database. The rest of schema-field pairs not listed are to be auto-purged after 90 days.
• The schema column does not include the revision, just the schema name. This means the white-list applies to all revisions of a given schema. This is done on purpose, so that new revisions of a white-listed schema will continue to be considered. Otherwise, every time a schema got modified, we would need to add its fields to the white-list.
• Note that new schemas or new fields in existing schemas will be automatically auto-purged after 90 days by default. If a schema owner wants their schema or fields to be kept indefinitely, they can contact analytics and we'll update the white-list.

Please create a change review on puppet/operations (anywhere, I will move it somewhere else) instead of attaching it here, I will take it from here.

Change 298721 had a related patch set uploaded (by Mforns):
Add white-list for EventLogging auto-purging

https://gerrit.wikimedia.org/r/298721

@jcrespo

Please, see above the patch I created. When the file has a permanent location in puppet, I will add some documentation on EventLogging's Wikitech page. Thanks!

@jcrespo
I did some additions to the white list in the gerrit change set. But I still need the confirmation of 1 schema owner. Please do not activate the purging until I get that confirmation. I will post on this task and let you know.

@jcrespo
Ok, it's confirmed. We can proceed :]

@Nuria, @mforns, I have updated the documentation at wikitech:Analytics/EventLogging to reflect these changes. It would also be good to update the more detailed docs at wikitech:Analytics/EventLoging/Data retention and auto-purging.

One question: the main event logging page says that all EL data in Hadoop is purged after 90 days. Is this correct, and if so, are there any plans to apply the whitelist to that data?

@Neil_P._Quinn_WMF @Nuria
Thanks Neil for spotting this. I updated the docs on data retention and auto-purging.

One question: the main event logging page says that all EL data in Hadoop is purged after 90 days. Is this correct, and if so, are there any plans to apply the whitelist to that data?

Yes, the autopurging scripts for Hadoop are in place already and they purge all the data after 90 days. About applying the white list to Hadoop, this has been discussed a couple times, but not prioritized. If there is a use case for it, we definitely can consider it.

Change 356383 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] role::mariadb::analytics::custom_repl_slave: add eventlogging_cleaner.py

https://gerrit.wikimedia.org/r/356383

Change 356383 merged by Elukey:
[operations/puppet@production] role::mariadb::analytics::custom_repl_slave: add eventlogging_cleaner.py

https://gerrit.wikimedia.org/r/356383

Change 364691 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] role::mariadb::analytics::custom_repl_slave: deploy the EL whitelist

https://gerrit.wikimedia.org/r/364691

Change 364691 merged by Elukey:
[operations/puppet@production] role::mariadb::analytics::custom_repl_slave: deploy the EL whitelist

https://gerrit.wikimedia.org/r/364691

Change 298721 abandoned by Elukey:
Add white-list for EventLogging auto-purging

https://gerrit.wikimedia.org/r/298721

Hi!

What's the status of this?

In T108850#3820300, @Marostegui wrote:

Hi!

What's the status of this?

Going to be done before EOQ :)

Awesome! Thanks! :-)

Change 398869 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] profile::mariadb::misc::el::master: apply data sanitization policies

https://gerrit.wikimedia.org/r/398869

Change 398869 merged by Elukey:
[operations/puppet@production] profile::mariadb::misc::el::master: apply data sanitization policies

https://gerrit.wikimedia.org/r/398869

Change 399149 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] profile::mariadb::misc::eventlogging: fix group/user dependencies

https://gerrit.wikimedia.org/r/399149

Change 399149 merged by Elukey:
[operations/puppet@production] profile::mariadb::misc::eventlogging: fix group/user dependencies

https://gerrit.wikimedia.org/r/399149

Change 399153 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] eventlogging_purging_whitelist.tsv: remove old table

https://gerrit.wikimedia.org/r/399153

Change 399153 merged by Elukey:
[operations/puppet@production] eventlogging_purging_whitelist.tsv: remove old table

https://gerrit.wikimedia.org/r/399153

Mentioned in SAL (#wikimedia-operations) [2017-12-19T16:34:39Z] <elukey> manually started eventlogging cleaner on db1107 to purge/sanitize data up to 90 days ago (tmux is running for user eventlogcleaner) - T108850