You are browsing a read-only backup copy of Wikitech. The live site can be found at wikitech.wikimedia.org

Analytics/Systems/Reportupdater: Difference between revisions

From Wikitech-static
Jump to navigation Jump to search
imported>Neil P. Quinn-WMF
(→‎WMF-specific: Update link)
imported>Btullis
 
(3 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
#REDIRECT [[Data Engineering/Systems/Reportupdater]]
reportupdater is a Python program that periodically executes given SQL queries (or scripts with SQL-like output) and appends their results into timeline TSV report files. '''If you have crons on any of the analytics data machines harvesting data you can likely benefit from using reportupdater. Think of it as a more "robust" way to execute cron jobs.'''
 
Some datasets generated by reportupdater: https://analytics.wikimedia.org/datasets/periodic/reports/metrics/
 
Some of its features are:
* Detects which data points are missing in the reports and executes the corresponding queries to update those missing values.
* Supports different report granularities: daily, weekly and monthly.
* Allows parametrizing the query (or script) to generate several reports, one for each value of the parameter.
* Generates consistent and comprehensive logs with different levels.
* It is robust against interrupted or parallel execution, so report files don't get corrupted.
* You can collect data from the MediaWiki databases, the EventLogging database and from the Hive cluster.
 
== Download ==
Get code from: [https://gerrit.wikimedia.org/r/#/admin/projects/analytics/reportupdater gerrit], code also mirrored to [https://github.com/wikimedia/analytics-reportupdater Wikimedia's Github].
 
== Dependencies ==
You need python 2.7 to run reportupdater.
To install dependencies, <code>cd</code> into reportupdater's root directory and execute <code>pip install -r requirements.txt</code>.
 
== Queries and scripts ==
You should write a set of either SQL queries or executable scripts (or both combined) to be run by reportupdater. Regardless of which you choose, you must write code that returns a single data point per metric.  For example, if splitting by wiki then the metric path should include <code>{wiki}</code> and the output would include exactly one row for each value of the <code>wiki</code> column.  Multiline output requires the <code>funnel</code> report flag. Then reportupdater will execute it for each specified date interval to build the whole timeline report. There are a couple conventions that your code should follow:
 
=== SQL Query conventions ===
# Use the placeholders <code>{from_timestamp}</code> and <code>{to_timestamp}</code> to timebound the results, for example: <code>WHERE timestamp >= '{from_timestamp}' AND timestamp < '{to_timestamp}'</code> (note that ''from_timestamp'' should be inclusive, while ''to_timestamp'' should be exclusive). Their format is YYYYMMDD000000.
# The first column of the results must be <code>DATE('{from_timestamp}') AS date</code>. This is an unnecessary limitation and might be removed in the future, but for now it's like that <code>:/</code>.
# There is no restriction on the number, format or contents of the subsequent columns.
 
=== Script conventions ===
# The script file MUST be marked as EXECUTABLE, otherwise it will give a cryptic permission denied error
# The first 2 parameters passed to the script are <code>start_date</code> and <code>end_date</code>, their format is YYYY-MM-DD. Use them to timebound the results (note that ''start_date'' should be inclusive, while ''end_date'' should be exclusive).
# The output of the report must be in TSV format, and must contain a first row with a header.
# The first column must be equal to ''start_date'' parameter (consider naming it ''date''). This is an unnecessary limitation and might be removed in the future, but for now it's like that <code>:/</code>.
# There is no restriction on the number, format or contents of the subsequent columns.
 
=== Where to put these files? ===
You should put all queries and scripts inside the same dedicated directory. When executing reportupdater you'll pass that directory as an argument and reportupdater will know where to find your code. If you're wanting to host your datasets at https://analytics.wikimedia.org/datasets/periodic/reports/metrics/, put your queries and scripts in a repo and then ask the Analytics team to clone them to wherever they clone them to.
 
=== Sample queries executed by other teams ===
Against eventlogging database:
 
[https://github.com/wikimedia/analytics-discovery-stats https://github.com/wikimedia/analytics-discovery-stats]
 
== Config file ==
You also need to write a YAML config file that will tell reportupdater how to run the reports. There are four main sections in the config file:
 
=== The ''databases'' section ===
If you're not using SQL queries, you can skip this section. The databases section specifies which databases to connect. Usually, it should include the host, the port, the credentials file and the database name, like this:
<pre>
databases:
    any_db_id:
        host: analytics-slave.eqiad.wmnet
        port: 3306
        creds_file: /etc/mysql/conf.d/research-client.cnf
        db: log
</pre>
But if your queries are hitting the analytics replicas (former dbstore1002 or analytics-store) the configuration should be different. The analytics replicas have been restructured for performance and maintainability, and they are now divided in shards. RU provides a specific configuration option, so you don't need to deal with shards. You should set `auto_find_db_shard` to true, and drop `host` and `port`, like this:
<pre>
databases:
    analytics_replica:
        auto_find_db_shard: true
        creds_file: /some/path/to/.my.cnf
        db: enwiki
</pre>
Gotcha: if the table that you're querying belongs to an extension (i.e. echo tables), it will be in a separate shard called x1. So you have to indicate it by setting `use_x1` to true:
<pre>
databases:
    analytics_replica:
        auto_find_db_shard: true
        use_x1: true
        creds_file: /some/path/to/.my.cnf
        db: enwiki
</pre>
Now, if you are using the `explode_by` feature, i.e. with `explode_by: wiki`, then a single query is connecting to several databases. The way to specify that would be to set `wiki_db_placeholder` to the name of the placeholder you're exploding the database names (in this case `wiki`). Also, you should remove the `db` parameter:
<pre>
databases:
    analytics_replica:
        auto_find_db_shard: true
        wiki_db_placeholder: wiki
        creds_file: /some/path/to/.my.cnf
</pre>
 
=== The ''graphite'' section ===
It specifies which graphite server to send stats to.  It should include the host and the port.  It can optionally include a dictionary of lookups, in which the key will be an identifier for a value in a metric execution.  For example, if your metric explodes by wiki, you can provide a lookup dictionary for the identifier "wiki".  This could be used to translate the wiki database name, like enwiki, to the wiki hostname, like en.wikipedia.  The value in the lookups dictionary is a filename to load as that lookup.  Or, if you'd like to translate values from a column in your report, you can provide a dictionary for "column1" for example.
<pre>
graphite:
    host: some.graphite.host
    port: 1234
    lookups:
        wiki: /some/path/to/wiki-lookup.yaml
        column1: /some/path/to/column1-lookup.yaml
</pre>
If you're not sending stats to graphite, you can skip this section.
 
=== The ''reports'' section ===
It describes the reports to be executed by reportupdater. For each report, there are three mandatory fields you must provide: the report id, the granularity and the start date; and there are some optional fields you can also specify, like this:
<pre>
reports:
    anyReportId:
        granularity: days
        starts: 2016-01-01
    anyOtherReportId:
        granularity: months
        starts: 2014-06-15
        type: script
        lag: 10800
    yetAnotherReportId:
        granularity: weeks
        starts: 2000-01-01
        max_data_points: 10
        explode_by:
            dimension1: valueA, valueB, valueC
            dimension2: valueK, valueL
    graphiteReportId:
        granularity: days
        funnel: true
        explode_by:
            wiki: enwiki, dewiki, eswiki
        graphite:
            path: "{_metric}.{column0}.{wiki}"
            metrics:
                "graphite.metric.name.prefix1": column1ToSendAsValue
                "graphite.metric.name.prefix2": column2ToSendAsValue
</pre>
 
; The report id : It must match the name of the custom executable file. For example, if you're using a SQL query named <code>random_report.sql</code>, then the report id must be <code>random_report</code> (without .sql). If you're using a script, then name it the same as the report id. The report id also dictates the name of the generated report: if you use the id <code>random_report</code>, the resulting report will be named <code>random_report.tsv</code>.
 
; granularity : <code>days</code>, <code>weeks</code> or <code>months</code>. Depending on this parameter, reportupdater will execute your queries/scripts every day, week or month; passing the corresponding date range to them. Mandatory.
 
; starts : Indicates the first date to be computed (inclusive). Its format should be YYYY-MM-DD. Mandatory.
 
; type : <code>sql</code> or <code>script</code>. If ''sql'', reportupdater will interpret your custom executable as SQL code. If ''script'', it will execute your custom code via the command line. Optional. Default: <code>sql</code>.
 
; lag : Positive integer. The time to wait (in seconds) to start the execution of the query/script after the desired time interval has finished. It can be useful to wait until the source data is loaded into the database. Optional. Default: 0.
 
; db : String. Should be one of the database identifiers specified in the ''databases'' section. Indicates which database should reportupdater connect to in order to execute the query. It only works with ''sql'' reports, if your report is of type ''script'', this option is ignored. Optional. Default: see ''defaults'' section.
 
; funnel : <code>true</code> or <code>false</code>. If true, the program will assume that the results of the query/script contain more than one line. If false it will assume only one-line results. This option should be named ''multiline'' instead of ''funnel'', which is a terrible name. Optional. Default: <code>false</code>.
 
; max_data_points : Positive integer. The maximum of data points the report will contain. Note it depends on the granularity: If you have a weekly granularity and set ''max_data_points'' to 10, your report will hold the data for the last 10 weeks. Optional. If not set, the report will hold all data points since the specified ''starts'' parameter.
 
; execute : String. You may want to have two or more reports using the same query or script. You can do so by assigning a query or script name to the ''execute'' field of a report. Optional. If it is not set, reportupdater will execute the query/script that matches the report identifier.
 
; explode_by : Dictionary<string: list> (in YAML format). If you want to have N identical reports, one for each section of your product, you can configure it by using ''explode_by''. For example: <code>wiki: enwiki, dewiki, jawiki, frwiki</code> would generate 4 reports, one for each wiki. Note that you would have to include the <code>{wiki}</code> placeholder in your SQL query to slice its results. If you're using a script, all ''explode_by'' values will be passed as arguments to the script (right after the date arguments) in alphabetical order. If you want, you can specify a file path instead of the value list; if you do so, reportupdater will read that file to get the value list (separate values using <code>/n</code>).
 
; graphite : Dictionary.  If you want to send your report results to graphite, you must configure the metric names and values that will be sent.  In this section, you define two things.  First is a path template string.  This is a python string template that will be filled in with values from a single report result row.  This string will be formatted with values from the row itself (keyed by column name), values from the explode by section (keyed by the explode by key), and the special {_metric} described next.  Second is a dictionary of metrics to send.  The key in this dictionary will be substituted in the path string where you use {_metric}.  And the value is the column to use for the actual value to send to graphite.  For a complete example using graphite, see https://gerrit.wikimedia.org/r/#/c/322007/6/reports/config.yaml.
 
=== The ''defaults'' section ===
Any value that you assign here will apply to all reports. If a report also has a value for that same key, it'll override the default value.
 
The examples below show a config with defaults compared to an equivalent config without defaults:
defaults:
    type: script
    starts: 2018-01-01
    explode_by:
        wiki: wikis.txt
daily_report:
    granularity: days
weekly_report:
    granularity: weeks
monthly_report:
    granularity: months
<pre>
daily_report:
    type: script
    starts: 2018-01-01
    granularity: days
    explode_by:
        wiki: wikis.txt
 
weekly_report:
    type: script
    starts: 2018-01-01
    granularity: weeks
    explode_by:
        wiki: wikis.txt
 
monthly_report:
    type: script
    starts: 2018-01-01
    granularity: months
    explode_by:
        wiki: wikis.txt
</pre>
 
== How to execute? ==
Use the ''update_reports.py'' executable in reportupdater's root directory.
 
See source: [https://github.com/wikimedia/analytics-limn-mobile-data/blob/master/reportupdater/update_reports.py]
<pre>
usage: update_reports.py [-h] [--config-path CONFIG_PATH]
                        [--wikis_path WIKIS_PATH] [-l LOG_LEVEL]
                        query_folder output_folder
 
Periodically execute SQL queries or scripts and write/update the results into
TSV files.
 
positional arguments:
  query_folder          Folder with *.sql files and scripts.
  output_folder        Folder to write the TSV files to.
 
optional arguments:
  -h, --help            show this help message and exit
  --config-path CONFIG_PATH
                        Yaml configuration file. Default:
                        <query_folder>/config.yaml.
  -l LOG_LEVEL, --log-level LOG_LEVEL
                        (debug|info|warning|error|critical)
</pre>
You'll find it useful as well to create a cron job that executes reportupdater periodically, for example every hour.
 
=== Re-runs ===
Sometimes, at the time of the queries, the data sources are incomplete or corrupt. In those cases, reportupdater might generate wrong values for a given date. If this happens, you can use the re-run functionality of reportupdater: ''rerun_reports.py'' executable in the root directory.
<pre>
usage: rerun_reports.py [-h] [--config-path CONFIG_PATH] [-r REPORT]
                        query_folder start_date end_date
 
Mark reports to be re-run for a given date range.
 
positional arguments:
  query_folder          Folder with *.sql files and scripts.
  start_date            Start of the date range to be rerun (YYYY-MM-DD,
                        inclusive).
  end_date              End of the date range to be rerun (YYYY-MM-DD,
                        exclusive).
 
optional arguments:
  -h, --help            show this help message and exit
  --config-path CONFIG_PATH
                        Yaml configuration file. Default:
                        <query_folder>/config.yaml.
  -r REPORT, --report REPORT
                        Report to be re-run. Several reports can be specified
                        like this. If none is specified, all reports listed in
                        the config file are marked for re-run.
</pre>
 
Note that the execution of ''rerun_reports.py'' does not re-run the reports per se. It only marks given reports and dates so that ''update_reports.py'' can re-run them in its next execution. Please, do not alter report files manually! Doing so can result in permission problems.
 
Example: rerunning all history for the active-boards report from the limn-flow-data repository, in the flow folder:
 
python /srv/reportupdater/reportupdater/rerun_reports.py -r active-boards /srv/reportupdater/jobs/limn-flow-data/flow 2009-11-08 2017-12-11
 
== WMF-specific ==
 
=== How to test? ===
To test your queries against the real database, just run the queries there before submiting  a code patch.
 
To test on any [[Analytics/Systems/Clients|analytics client machine]]:
 
* you need both repositories cloned: reportupdater-queries and reportupdater
* get the reportupdater-queries changeset you are interested in testing
* if you're testing Hive queries, make sure you've authenticated through kinit
* if you're testing queries to MariaDB databases, make sure the credentials file is pointed correctly
* /usr/bin/python3 /path/to/reportupdater/update_reports.py -l info /path/to/reportupdater-queries/<your directory>/ ~/reportupdater-output
* It might be necessary to execute using the analytics user, if you do so, give permits to the reportupdater-queries repo, so that the pid file can be created.
 
=== How to productionize? ===
# Add your queries/scripts and config file to a new folder within [https://gerrit.wikimedia.org/r/#/admin/projects/analytics/reportupdater-queries this repo], and ask someone in the Analytics team to review.
# Ask the Analytics team to puppetize the execution of your reportupdater instance, or create a puppet patch yourself adding your reportupdater job in [https://github.com/wikimedia/puppet/tree/production/modules/profile/manifests/reportupdater], and ask someone in the Analytics team to review. There are two puppet modules that run jobs one for mysql one for hive.
 
== Troubleshooting ==
=== Where are logs ?===
For reports running on cron, logs are written to /srv/reportupdater/log.  For reports running on systemd timers, [[Analytics/Systems/Managing_systemd_timers|logs are accessed as described here]].
 
=== Where is puppet code?===
 
[https://github.com/wikimedia/puppet/tree/production/modules/reportupdater https://github.com/wikimedia/puppet/tree/production/modules/reportupdater]
 
[https://github.com/wikimedia/puppet/blob/production/modules/role/manifests/statistics/cruncher.pp https://github.com/wikimedia/puppet/blob/production/modules/role/manifests/statistics/cruncher.pp]
 
=== Support ===
reportupdater is maintained by [https://www.mediawiki.org/wiki/Analytics WMF's Analytics team].
 
== Next features ==
* Today the queries and scripts have to output a first column with the date. However this is not necessary and can be removed.
* Also the configuration option is_funnel could be removed and reportupdater could identify automatically if the queries return 1 row or multiple rows and handle it properly.
* Make all report config fields defaultable in the defaults section, like db.

Latest revision as of 14:26, 12 January 2023

Redirect to:

Retrieved from "https://wikitech-static.wikimedia.org/w/index.php?title=Analytics/Systems/Reportupdater&oldid=596619"