Ahhhh! I didn't get it - The checksum is computed once er argument set, and don't change if the dates worked by script change. This means I can get the checksum manually and set it up in the cron as a manual parameter :)
Thanks mforns for clarification :)

One note about hadoop blobs: HDFS stores files split in chunks, with those not collocated. If we use transfer.py on local files after having brought them back from HDFS to the local machine, then yes, I'm assuming it would work. And I actually think the best would be to make transfer.py stream from standard-input so that files don't need to be moved from HDFS to local-filesystem, then to remote. Now the most efficient way to transfer data "hadoop-style" would be to prevent the hop to the local machine using transfer.py and stream it directly to its destination from the various datanodes - The main concern I can see here is that access is needed for all hadoop workers.

I support the idea. I however have no clue of how much formality must be put in place so that we can keep your access. @leila is probably the best person to engage with in that regard.

Hi @Isaac, I have generated some parquet data here /user/joal/wmf/data/wmf/wikidata/item_page_link/20190204 with the following query:

spark.sql("SET spark.sql.shuffle.partitions=128")
val wikidataParquetPath = "/user/joal/wmf/data/wmf/mediawiki/wikidata_parquet/20190204"
spark.read.parquet(wikidataParquetPath).createOrReplaceTempView("wikidata")

Strategy for deleting raw data would be to use the new refinery-drop-older-than script:

refinery-drop-older-than \
    --base-path=/wmf/data/raw/mediawiki/xmldumps/pages_meta_history \
    --path-format='(?P<year>[0-9]{4})(?P<month>[0-9]{2})01' \
    --older-than=90

@mforns : I'd like your view on how to automate getting the checksum to automate the job :)

Taking advantage of currently existing strategy and job for mediawiki-oriented snapshots, I have provided a patch alowing to keep 6 parquet snapshots. It's probably more than needed, but prevents having to change the drop-script.

Many thanks @ArielGlenn :)

Works for me :) I assume the system would work similarly to the existing XML dumps, meaning that dumps would be generated in the same date folder (1st, 8th, 15th, 22nd of every month for instance), one after the other, providing information on availability in a json file?

@Neil_P._Quinn_WMF Hi !
We have planned to release change_tags raw table next month (february snapshot, released at beginning of March). The data will however probably not be integrated into mediawiki_history before the following snapshot (maybe, but there is a high risk that not). Will it be possible for ou to use the raw data to generate your report?

@ArielGlenn : Could we decide on regular day-in-month patterns for the various entity-dumps that need to be generated?
Here is my suggestion::

just to make sure I have the correct sequence of actions in mind:

• Update event.navigationtiming hive table so that deviceMemory field is a double
• Backfill refine job for the NavigationTiming schema as much as possible
• Update not-backfilled data to make deviceMemory a double even without recovering the lost information (preventing querying failure)

Is that correct?

I imagine we would add entropy-stats tables generated hourly (for hourly datasets). The entropy-generation code could (and should!) be generic and reusable, and the alarming mechanism as well I guess.

Hey @Pchelolo - I think talking to the traffic team should be the way to go here.
I ran a query to get results for x_cache fields for 1 hour of webrequest (in spark):

spark.sql("select x_cache, count(1) as c from wmf.webrequest where webrequest_source = 'text' and year = 2019 and month = 2 and day = 14 and hour = 7 group by x_cache order by c desc").show(50, false)

It should appear in February snapshot, generated in March, yes :)

@diego :
This has worked for me (takes some time to compute and needs a bunch of resources). I hope it's close enough to what you want :) :

spark.sql("SET spark.sql.shuffle.partitions=512")
val wikidataParquetPath = "/user/joal/wmf/data/wmf/mediawiki/wikidata_parquet/20181001"
spark.read.parquet(wikidataParquetPath).createOrReplaceTempView("wikidata")

More info on this db: It contains sqooped data from tlwiki as naming suggests (number of revision coherent with recent snapshot). Data format is not optimal (hive-oriented, not even avro) and data is old compared to the one currently provided. I suggest we drop it.

I wonder if the indformation present in the table mentioned is the same as the one we could extract from site-links in the wikidata items. @diego : Could you please triple check that? If it is the case, this task is another one in need of wikidata-json dumps being productionized :)

Arf - Will try to be clearer: Instead of getting schema data from reading json and double read in case the schema you get from json is not cast-able to expected schema, could we gather the "real" schema from the schema repo and convert it to spark-schema, then read the json from that? I know you'd always prefer not to, but now that we're back to double-reading, maybe it'd make more sense? (for schemas with a big number of events, double reading will be expensive !)

Hey @Milimetric - Could we add "sum_edit_counts" to the existing dataset instead of creating a new one ?

For the record @Milimetric : spilled files are the temporary files generated between steps when data doesn't fit in memory (they're called spilled because you first fill in memory, and it spills out to disk). For big jobs, those represent a lot of data and IOs.

I must say I also pushed for stopping double reading, and continue to think so.
I wonder if having a first refine step gathering schemas and converting json to spark schemas wouldn't make more sense here (and actually apply schema changes in hive from schema change detection only, before even reading).

I like it, thanks @elukey !

@Ottomata: Double reading is way to go when we have schema discrepancies that can't be solve through casting (struct -> map).
See test ran below:

case class TestMap(
    k1: String = "v1",
    k2: String = "v2",
    k3: String = "v3",
    k4: String = "v4",
    k5: String = "v5"
)

I confirm the fix :) Closing this task.

Thanks @Halfak for the double check :) Also, If you like hive speed, try Spark :D
Massive thanks again to @Marostegui for handling the death of the dbstore1002 beast :)

As part of the work the Analytics does on providing stats over (almost) all wikis, we can provide a list of orphan-revisions (no associated page_id, or no associated user_id/user_text even if rev_delete < 4). Please ping us if needed :)

sqoop for actor and comment tables just finished and we should use the new hardware next month, ,so no problem fir me either :)

I suggest using hdfs://wmf/data/archive/sqldumps as a base for sql-dumps, with content-oriented subfolders, leading to hdfs://wmf/data/archive/sqldumps/mep_word_persistence/staging.sql.
Would that be ok for you @elukey ?

@Marostegui : Indeed I started the job later (comment time is almostsynchronous).

@Marostegui : Sqoop finished yesterday at 23:31 UTC with expected number of rows. Maybe the db crash was unrelated?
@Halfak : Data is available for vetting in your hdfs user folder: /user/halfak/mep_word_persistence. I also created the related hive table:

use halfak;
CREATE EXTERNAL TABLE mep_word_persistence (  
  rev_id BIGINT,
  rev_timestamp STRING,
  page_id BIGINT,
  page_namespace BIGINT,
  page_title STRING,
  user_id BIGINT,
  user_text STRING,
  comment STRING,
  minor BOOLEAN,
  sha1 STRING,
  revisions_processed BIGINT,
  non_self_processed BIGINT,
  seconds_possible BIGINT,
  tokens_added BIGINT,
  persistent_tokens BIGINT,
  non_self_persistent_tokens BIGINT,
  censored BOOLEAN,
  non_self_censored BOOLEAN,
  sum_log_persisted DOUBLE,
  sum_log_non_self_persisted DOUBLE,
  sum_log_seconds_visible DOUBLE
) STORED AS PARQUET LOCATION "/user/halfak/mep_word_persistence";

Let's triple check the data is correct before calling it done :)

Job started with the command:

sudo -u hdfs sqoop import \
  -D mapred.job.name='sqoop-staging-mep_word_persistence' \
  --username research \
  --password-file /user/hdfs/mysql-analytics-research-client-pw.txt \
  --connect jdbc:mysql://analytics-store/staging \
  --query "select rev_id, convert(rev_timestamp using utf8) rev_timestamp, page_id, page_namespace, convert(page_title using utf8) page_title, user_id, convert(user_text using utf8) user_text, convert(comment using utf8) comment, minor, convert(sha1 using utf8) sha1, revisions_processed, non_self_processed, seconds_possible, tokens_added, persistent_tokens, non_self_persistent_tokens, censored, non_self_censored, sum_log_persisted, sum_log_non_self_persisted, sum_log_seconds_visible from mep_word_persistence where \$CONDITIONS" \
  --target-dir /user/joal/mep_word_persistence \
  --num-mappers 5 \
  --split-by rev_id \
  --as-parquetfile \
  --map-column-java "minor=Boolean,censored=Boolean,non_self_censored=Boolean,sum_log_persisted=Double,sum_log_non_self_persisted=Double,sum_log_seconds_visible=Double"

I hope 5 parallel workers is not too much ...

Did a quick test, it's working for me (one of them) :)
Thanks a milion @Marostegui and !@elukey

Doc available here: https://wikitech.wikimedia.org/wiki/Analytics/Systems/Cluster/Spark#Spark_tuning_for_big_jobs

If we upgrade, let's move to 2.4.0?

For analytics-machines, should we use hdfs as an archive store?
And, if we archive, how long should we keep the archives?

Thanks @nettrom_WMF for the follow up :)
I'll try to include that in the next bunch of big changes I'm working on for mediawiki-history :)

Hi folks - Sorry for late answer, I was at WMF all-hands last week and did not check tasks.
I have started work work on having the wikidata-json dumps imported on the cluster, and while some data is available for ad-hoc analysis (see hdfs:///user/joal/wmf/data/wmf/mediawiki/wikidata_parquet), this dataset is not updated on a regular basis (not production-ready).
I however think that for a manual update every 3 month, it could be easy.
@GoranSMilovanovic - What do you think?

I had in mind that one of the reason for using avro originally was data size. Currently the mediawiki_ApiAction takes ~1T per month and the mediawiki_CirrusSearchRequestSet ~2T per month. A small test with hive showed me that the growth factor from avro to json would be of ~4.5 for mediawiki_CirrusSearchRequestSet and ~3 for mediawiki_ApiAction (if the events don't change ). This would lead to ~12T monthly, which is largely acceptable for HDFS.
Last but not least: What about kafka?

To discuss with the team: Do we want to drop the column or nullifying the field would be enough? For webrequest, since data is dropped after 2 month, we can first nullify then drop for real. However for pageview, since data goes back in time, I think nullification must be done.

I have a suggestion. We could set the <timeout>XXX</timeout> control in oozie coordinators, replacing XXX with number of seconds before the materialized job times-out. When a job times-out it is marked as failed by oozie, sending us a failure email. I think an error email would make react stronger, as it means data will be missing. Also, the action might just be to re-run the job, which is simple in hue. Finally, this is the approach we have in webrequest-load, which has proven succesfull.

Turnilo needed a patch (webrequest_sampled_128 datasource had intrspection disabled), and I manually updated the columns in superset (automagic column scan failed...)

It's not present in the wiki-list we sqoop: https://github.com/wikimedia/analytics-refinery/blob/master/static_data/mediawiki/grouped_wikis/labs_grouped_wikis.csv
Providing a patch now.

Bug found and corrected (patches above).
Data is available now and the rerun problem should be solved.

Hi @WMDE-leszek - core data has not been computed et (usually done around the 9th of the following month).
I'll be sure to have an eye on data showing up for month 12 and rerun the job if needed.