[Author Prev][Author Next][Thread Prev][Thread Next][Author Index][Thread Index]

[tor-commits] [metrics-web/master] Cut off too recent dates from several CSV files.

To: tor-commits@xxxxxxxxxxxxxxxxxxxx
Subject: [tor-commits] [metrics-web/master] Cut off too recent dates from several CSV files.
From: karsten@xxxxxxxxxxxxxx
Date: Wed, 7 Mar 2018 17:56:23 +0000 (UTC)
Delivered-to: archiver@xxxxxxxx
Delivery-date: Wed, 07 Mar 2018 12:56:38 -0500
List-archive: <http://lists.torproject.org/pipermail/tor-commits/>
List-help: <mailto:tor-commits-request@lists.torproject.org?subject=help>
List-id: "auto: code repository commits" <tor-commits.lists.torproject.org>
List-post: <mailto:tor-commits@lists.torproject.org>
List-subscribe: <https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-commits>, <mailto:tor-commits-request@lists.torproject.org?subject=subscribe>
List-unsubscribe: <https://lists.torproject.org/cgi-bin/mailman/options/tor-commits>, <mailto:tor-commits-request@lists.torproject.org?subject=unsubscribe>
Patch-author: Karsten Loesing <karsten.loesing@xxxxxxx>
Sender: "tor-commits" <tor-commits-bounces@xxxxxxxxxxxxxxxxxxxx>

commit dc50c9ec1e58698d7a97553203ccd6c28c043f67
Author: Karsten Loesing <karsten.loesing@xxxxxxx>
Date:   Tue Mar 6 17:27:15 2018 +0100

    Cut off too recent dates from several CSV files.
    
    With this patch we're cutting off the last (1) day from:
     - servers.csv (2 -> 1)
     - ipv6servers.csv (2 -> 1)
    
    Before this patch we already cut off the last (1) day from:
     - advbwdist.csv (1 -> 1)
    
    With this patch we're cutting off the last three (3) days from:
     - bandwidth.csv (4 -> 3)
    
    With this patch we're cutting off the last two (2) days from:
     - clients.csv (0 -> 2)
     - userstats-combined.csv (0 -> 2)
     - connbidirect2.csv (0 -> 2)
     - torperf-1.1.csv (0 -> 2)
    
    With the previous patch we started cutting off the last two (2) days
    from:
     - hidserv.csv (0 -> 2)
    
    In a future patch we might start cutting off days from:
     - webstats.csv (0 -> ?)
    
    Fixes more of #25196.
---
 .../metrics/stats/connbidirect/Main.java           |  5 +++
 src/main/sql/clients/init-userstats.sql            |  6 ++++
 src/main/sql/ipv6servers/init-ipv6servers.sql      |  2 +-
 src/main/sql/legacy/tordir.sql                     | 36 +++++++++++-----------
 src/main/sql/onionperf/init-onionperf.sql          |  2 ++
 5 files changed, 32 insertions(+), 19 deletions(-)

diff --git a/src/main/java/org/torproject/metrics/stats/connbidirect/Main.java b/src/main/java/org/torproject/metrics/stats/connbidirect/Main.java
index 1911472..4474966 100644
--- a/src/main/java/org/torproject/metrics/stats/connbidirect/Main.java
+++ b/src/main/java/org/torproject/metrics/stats/connbidirect/Main.java
@@ -429,6 +429,8 @@ public class Main {
       SortedSet<RawStat> rawStats) {
     DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
     dateFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
+    String yesterday = dateFormat.format(System.currentTimeMillis()
+        - ONE_DAY_IN_MILLIS);
     SortedMap<String, List<Short>> fractionsByDateAndDirection
         = new TreeMap<>();
     final String[] directions = new String[] { "read", "write", "both" };
@@ -436,6 +438,9 @@ public class Main {
       if (rawStat.fingerprint != null) {
         String date = dateFormat.format(rawStat.dateDays
             * ONE_DAY_IN_MILLIS);
+        if (date.compareTo(yesterday) >= 0) {
+          continue;
+        }
         short[] fractions = new short[] { rawStat.fractionRead,
             rawStat.fractionWrite, rawStat.fractionBoth };
         for (int i = 0; i < directions.length; i++) {
diff --git a/src/main/sql/clients/init-userstats.sql b/src/main/sql/clients/init-userstats.sql
index 733a516..38521f2 100644
--- a/src/main/sql/clients/init-userstats.sql
+++ b/src/main/sql/clients/init-userstats.sql
@@ -681,6 +681,9 @@ CREATE OR REPLACE VIEW estimated AS SELECT
   -- request statistics.
   WHERE a.frac BETWEEN 0.1 AND 1.0
 
+  -- Skip estimates that are as recent as yesterday or newer.
+  AND a.date < current_date - 1
+
   -- Order results.
   ORDER BY date DESC, node, version, transport, country;
 
@@ -749,6 +752,9 @@ CREATE OR REPLACE VIEW combined AS SELECT
   -- request statistics.
   WHERE a.frac BETWEEN 0.1 AND 1.0
 
+  -- Skip estimates that are as recent as yesterday or newer.
+  AND a.date < current_date - 1
+
   -- Order results.
   ORDER BY date DESC;
 
diff --git a/src/main/sql/ipv6servers/init-ipv6servers.sql b/src/main/sql/ipv6servers/init-ipv6servers.sql
index a5d56f3..8ed9372 100644
--- a/src/main/sql/ipv6servers/init-ipv6servers.sql
+++ b/src/main/sql/ipv6servers/init-ipv6servers.sql
@@ -99,7 +99,7 @@ WITH included_statuses AS (
   GROUP BY DATE(valid_after), server
   HAVING COUNT(status_id) >= 12
   AND DATE(valid_after)
-    < (SELECT MAX(DATE(valid_after)) FROM included_statuses) - 1
+    < (SELECT MAX(DATE(valid_after)) FROM included_statuses)
 )
 SELECT DATE(valid_after) AS valid_after_date, server,
   CASE WHEN server = 'relay' THEN guard_relay ELSE NULL END AS guard_relay,
diff --git a/src/main/sql/legacy/tordir.sql b/src/main/sql/legacy/tordir.sql
index e0ea651..5d254f6 100644
--- a/src/main/sql/legacy/tordir.sql
+++ b/src/main/sql/legacy/tordir.sql
@@ -720,7 +720,7 @@ CREATE VIEW stats_servers AS
   (SELECT date, NULL AS flag, NULL AS country, NULL AS version,
   NULL AS platform, TRUE AS ec2bridge, NULL AS relays,
   avg_running_ec2 AS bridges FROM bridge_network_size
-  WHERE date < current_date - 1)
+  WHERE date < current_date)
 UNION ALL
   (SELECT COALESCE(network_size.date, bridge_network_size.date) AS date,
   NULL AS flag, NULL AS country, NULL AS version, NULL AS platform,
@@ -729,57 +729,57 @@ UNION ALL
   FULL OUTER JOIN bridge_network_size
   ON network_size.date = bridge_network_size.date
   WHERE COALESCE(network_size.date, bridge_network_size.date) <
-  current_date - 1)
+  current_date)
 UNION ALL
   (SELECT date, 'Exit' AS flag, NULL AS country, NULL AS version,
   NULL AS platform, NULL AS ec2bridge, avg_exit AS relays,
-  NULL AS bridges FROM network_size WHERE date < current_date - 1)
+  NULL AS bridges FROM network_size WHERE date < current_date)
 UNION ALL
   (SELECT date, 'Guard' AS flag, NULL AS country, NULL AS version,
   NULL AS platform, NULL AS ec2bridge, avg_guard AS relays,
-  NULL AS bridges FROM network_size WHERE date < current_date - 1)
+  NULL AS bridges FROM network_size WHERE date < current_date)
 UNION ALL
   (SELECT date, 'Fast' AS flag, NULL AS country, NULL AS version,
   NULL AS platform, NULL AS ec2bridge, avg_fast AS relays,
-  NULL AS bridges FROM network_size WHERE date < current_date - 1)
+  NULL AS bridges FROM network_size WHERE date < current_date)
 UNION ALL
   (SELECT date, 'Stable' AS flag, NULL AS country, NULL AS version,
   NULL AS platform, NULL AS ec2bridge, avg_stable AS relays,
-  NULL AS bridges FROM network_size WHERE date < current_date - 1)
+  NULL AS bridges FROM network_size WHERE date < current_date)
 UNION ALL
   (SELECT date, 'HSDir' AS flag, NULL AS country, NULL AS version,
   NULL AS platform, NULL AS ec2bridge, avg_hsdir AS relays,
-  NULL AS bridges FROM network_size WHERE date < current_date - 1)
+  NULL AS bridges FROM network_size WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, CASE WHEN country != 'zz' THEN country
   ELSE '??' END AS country, NULL AS version, NULL AS platform,
   NULL AS ec2bridge, relays, NULL AS bridges FROM relay_countries
-  WHERE date < current_date - 1)
+  WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, NULL AS country, version, NULL AS platform,
   NULL AS ec2bridge, relays, NULL AS bridges FROM relay_versions
-  WHERE date < current_date - 1)
+  WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, NULL AS country, NULL AS version,
   'Linux' AS platform, NULL AS ec2bridge, avg_linux AS relays,
-  NULL AS bridges FROM relay_platforms WHERE date < current_date - 1)
+  NULL AS bridges FROM relay_platforms WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, NULL AS country, NULL AS version,
   'Darwin' AS platform, NULL AS ec2bridge, avg_darwin AS relays,
-  NULL AS bridges FROM relay_platforms WHERE date < current_date - 1)
+  NULL AS bridges FROM relay_platforms WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, NULL AS country, NULL AS version,
   'BSD' AS platform, NULL AS ec2bridge, avg_bsd AS relays,
-  NULL AS bridges FROM relay_platforms WHERE date < current_date - 1)
+  NULL AS bridges FROM relay_platforms WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, NULL AS country, NULL AS version,
   'Windows' AS platform, NULL AS ec2bridge, avg_windows AS relays,
-  NULL AS bridges FROM relay_platforms WHERE date < current_date - 1)
+  NULL AS bridges FROM relay_platforms WHERE date < current_date)
 UNION ALL
   (SELECT date, NULL AS flag, NULL AS country, NULL AS version,
   'Other' AS platform, NULL AS ec2bridge, avg_other AS relays,
-  NULL AS bridges FROM relay_platforms WHERE date < current_date - 1)
-ORDER BY 1, 2, 3, 4, 5, 6;
+  NULL AS bridges FROM relay_platforms WHERE date < current_date)
+ORDER BY date, flag, country, version, platform, ec2bridge;
 
 -- View for exporting bandwidth statistics.
 CREATE VIEW stats_bandwidth AS
@@ -797,7 +797,7 @@ CREATE VIEW stats_bandwidth AS
   AND bandwidth_flags.isexit = bwhist_flags.isexit
   AND bandwidth_flags.isguard = bwhist_flags.isguard
   WHERE COALESCE(bandwidth_flags.date, bwhist_flags.date) <
-  current_date - 3)
+  current_date - 2)
 UNION ALL
   (SELECT COALESCE(total_bandwidth.date, total_bwhist.date, u.date)
   AS date, NULL AS isexit, NULL AS isguard,
@@ -818,6 +818,6 @@ UNION ALL
   AND bwp / bwd <= 3) u
   ON COALESCE(total_bandwidth.date, total_bwhist.date) = u.date
   WHERE COALESCE(total_bandwidth.date, total_bwhist.date, u.date) <
-  current_date - 3)
-ORDER BY 1, 2, 3;
+  current_date - 2)
+ORDER BY date, isexit, isguard;
 
diff --git a/src/main/sql/onionperf/init-onionperf.sql b/src/main/sql/onionperf/init-onionperf.sql
index 9207bcf..557eabb 100644
--- a/src/main/sql/onionperf/init-onionperf.sql
+++ b/src/main/sql/onionperf/init-onionperf.sql
@@ -69,6 +69,7 @@ SELECT DATE(start) AS date,
     AND readbytes < filesize THEN 1 ELSE NULL END) AS failures,
   COUNT(*) AS requests
 FROM measurements
+WHERE DATE(start) < current_date - 1
 GROUP BY date, filesize, source, server
 UNION
 SELECT DATE(start) AS date,
@@ -83,6 +84,7 @@ SELECT DATE(start) AS date,
     AND readbytes < filesize THEN 1 ELSE NULL END) AS failures,
   COUNT(*) AS requests
 FROM measurements
+WHERE DATE(start) < current_date - 1
 GROUP BY date, filesize, 3, server) sub
 ORDER BY date, filesize, source, server;
 

_______________________________________________
tor-commits mailing list
tor-commits@xxxxxxxxxxxxxxxxxxxx
https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-commits

Prev by Author: [tor-commits] [collector/release] Prepare for 1.5.1 release.
Next by Author: [tor-commits] [metrics-web/master] Give up on reshape in favor of tidyr.
Previous by thread: [tor-commits] [metrics-web/master] Cut off too recent dates from hidserv.csv.
Next by thread: [tor-commits] [metrics-web/master] Add comment section to CSV file headers.
Index(es):
- Author
- Thread