aboutsummaryrefslogtreecommitdiffstats
path: root/sql/random_queries.md
blob: 572b4f9078482c632ba3d10e0e0109da8f2bc83f (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193

Basic stats (2019-09-23):

    SELECT COUNT(*) FROM cdx WHERE NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex);
    => 28,023,760
    => Time: 253897.213 ms (04:13.897)

    SELECT COUNT(DISTINCT sha1hex) FROM cdx WHERE NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex);
    => 22,816,087
    => Time: 287097.944 ms (04:47.098)

    SELECT COUNT(*) FROM grobid.
    => 56,196,992

    SELECT COUNT(DISTINCT sha1hex) FROM cdx;
    => 64,348,277
    => Time: 572383.931 ms (09:32.384)

    SELECT COUNT(*) FROM cdx;
    => 74,796,777

    SELECT mimetype, COUNT(*) FROM cdx GROUP BY mimetype ORDER BY COUNT(*) DESC;
    => Time: 189067.335 ms (03:09.067)

                mimetype        |  count   
        ------------------------+----------
         application/pdf        | 51049905
         text/html              | 24841846
         text/xml               |   524682
         application/postscript |    81009
        (4 rows)

Time: 189067.335 ms (03:09.067)

    SELECT status_code, COUNT(*) FROM grobid GROUP BY status_code ORDER BY count(*) DESC;

         status_code |  count   
        -------------+----------
                 200 | 56196992

    compare with older sandcrawler/output-prod/2019-05-28-1920.35-statuscodecount:

        200     49567139
        400     3464503
        409     691917
        500     247028
        503     123

    SELECT row_to_json(cdx) FROM cdx LIMIT 5;

    SELECT row_to_json(r) FROM (
        SELECT url, datetime FROM cdx
    ) r
    LIMIT 5;

More stats (2019-12-27):

    SELECT mimetype, COUNT(*) FROM file_meta GROUP BY mimetype ORDER BY COUNT(*) DESC LIMIT 20;

    SELECT SUM(size_bytes) FROM file_meta;

"Last 24 hour progress":

    # "problem domains" and statuses
    SELECT domain, status, COUNT((domain, status))
    FROM (SELECT status, updated, substring(terminal_url FROM '[^/]+://([^/]*)') AS domain FROM ingest_file_result) t1
    WHERE t1.domain != ''
        AND t1.status != 'success'
        AND t1.updated >= NOW() - '1 day'::INTERVAL
    GROUP BY domain, status
    ORDER BY COUNT DESC
    LIMIT 10;

    # "what type of errors"
    SELECT ingest_type, status, COUNT(*)
    FROM ingest_file_result
    WHERE updated >= NOW() - '1 day'::INTERVAL
    GROUP BY ingest_type, status
    ORDER BY COUNT DESC
    LIMIT 25;

    # "throughput per day for last N days"
    SELECT ingest_type,
           date(updated),
           COUNT(*) as total,
           COUNT(CASE status WHEN 'success' THEN 1 ELSE null END) as success
    FROM ingest_file_result
    WHERE updated >= NOW() - '1 month'::INTERVAL
    GROUP BY ingest_type, date(updated)
    ORDER BY date(updated) DESC;

## Parse URLs

One approach is to do regexes, something like:

    SELECT substring(column_name FROM '[^/]+://([^/]+)/') AS domain_name FROM table_name;

Eg:

    SELECT DISTINCT(domain), COUNT(domain)
        FROM (select substring(base_url FROM '[^/]+://([^/]*)') as domain FROM ingest_file_result) t1
        WHERE t1.domain != ''
        GROUP BY domain
        ORDER BY COUNT DESC 
        LIMIT 10;

Or:

    SELECT domain, status, COUNT((domain, status))
        FROM (SELECT status, substring(terminal_url FROM '[^/]+://([^/]*)') AS domain FROM ingest_file_result) t1
        WHERE t1.domain != ''
            AND t1.status != 'success'
        GROUP BY domain, status
        ORDER BY COUNT DESC
        LIMIT 10;

Can also do some quick lookups for a specific domain and protocol like:

    SELECT *
    FROM ingest_file_result
    WHERE terminal_url LIKE 'https://insights.ovid.com/%'
    LIMIT 10;

For a given DOI prefix:

    SELECT *
    FROM ingest_file_result
    WHERE base_url LIKE 'https://doi.org/10.17223/a%'
    AND status = 'no-pdf-link'
    LIMIT 10;

    SELECT status, count(*)
    FROM ingest_file_result
    WHERE base_url LIKE 'https://doi.org/10.17223/%'
    GROUP BY status
    ORDER BY count(*) DESC;

## Bulk Ingest

Show bulk ingest status on links *added* in the past week:

    SELECT ingest_file_result.ingest_type, ingest_file_result.status, COUNT(*)
    FROM ingest_file_result
    LEFT JOIN ingest_request
        ON ingest_file_result.ingest_type = ingest_request.ingest_type
        AND ingest_file_result.base_url = ingest_request.base_url
    WHERE ingest_request.created >= NOW() - '30 day'::INTERVAL
        AND ingest_request.link_source = 'unpaywall'
    GROUP BY ingest_file_result.ingest_type, ingest_file_result.status
    ORDER BY COUNT DESC
    LIMIT 25;

Top *successful* domains:

    SELECT domain, status, COUNT((domain, status))
    FROM (
        SELECT
            ingest_file_result.ingest_type,
            ingest_file_result.status,
            substring(ingest_file_result.terminal_url FROM '[^/]+://([^/]*)') AS domain
        FROM ingest_file_result
        LEFT JOIN ingest_request
            ON ingest_file_result.ingest_type = ingest_request.ingest_type
            AND ingest_file_result.base_url = ingest_request.base_url
        WHERE ingest_request.created >= NOW() - '7 day'::INTERVAL
            AND ingest_request.link_source = 'unpaywall'
    ) t1
    WHERE t1.domain != ''
        AND t1.status = 'success'
    GROUP BY domain, status
    ORDER BY COUNT DESC
    LIMIT 20;

Summarize non-success domains for the same:

    SELECT domain, status, COUNT((domain, status))
    FROM (
        SELECT
            ingest_file_result.ingest_type,
            ingest_file_result.status,
            substring(ingest_file_result.terminal_url FROM '[^/]+://([^/]*)') AS domain
        FROM ingest_file_result
        LEFT JOIN ingest_request
            ON ingest_file_result.ingest_type = ingest_request.ingest_type
            AND ingest_file_result.base_url = ingest_request.base_url
        WHERE ingest_request.created >= NOW() - '7 day'::INTERVAL
            AND ingest_request.link_source = 'unpaywall'
    ) t1
    WHERE t1.domain != ''
        AND t1.status != 'success'
    GROUP BY domain, status
    ORDER BY COUNT DESC
    LIMIT 20;