data-sources/wikipedia-wikidata/import_wikipedia.sh

   1 #!/bin/bash
   2
   3 psqlcmd() {
   4      psql --quiet wikiprocessingdb |& \
   5      grep -v 'does not exist, skipping' |& \
   6      grep -v 'violates check constraint' |& \
   7      grep -vi 'Failing row contains'
   8 }
   9
  10 mysql2pgsqlcmd() {
  11      ./mysql2pgsql.perl --nodrop /dev/stdin /dev/stdout
  12 }
  13
  14 download() {
  15      echo "Downloading $1"
  16      wget --quiet --no-clobber --tries=3 "$1"
  17 }
  18
  19
  20 # languages to process (refer to List of Wikipedias here: https://en.wikipedia.org/wiki/List_of_Wikipedias)
  21 # requires Bash 4.0
  22 readarray -t LANGUAGES < languages.txt
  23
  24
  25
  26 echo "====================================================================="
  27 echo "Create wikipedia calculation tables"
  28 echo "====================================================================="
  29
  30 echo "CREATE TABLE linkcounts (
  31         language text,
  32         title    text,
  33         count    integer,
  34         sumcount integer,
  35         lat      double precision,
  36         lon      double precision
  37      );"  | psqlcmd
  38
  39 echo "CREATE TABLE wikipedia_article (
  40         language    text NOT NULL,
  41         title       text NOT NULL,
  42         langcount   integer,
  43         othercount  integer,
  44         totalcount  integer,
  45         lat double  precision,
  46         lon double  precision,
  47         importance  double precision,
  48         title_en    text,
  49         osm_type    character(1),
  50         osm_id      bigint
  51       );" | psqlcmd
  52
  53 echo "CREATE TABLE wikipedia_redirect (
  54         language   text,
  55         from_title text,
  56         to_title   text
  57      );" | psqlcmd
  58
  59
  60
  61
  62
  63 echo "====================================================================="
  64 echo "Download individual wikipedia language tables"
  65 echo "====================================================================="
  66
  67
  68 for i in "${LANGUAGES[@]}"
  69 do
  70     echo "Language: $i"
  71
  72     # english is the largest
  73     # 1.7G  enwiki-latest-page.sql.gz
  74     # 6.2G  enwiki-latest-pagelinks.sql.gz
  75     # 355M  enwiki-latest-langlinks.sql.gz
  76     # 128M  enwiki-latest-redirect.sql.gz
  77
  78     # example of smaller languge turkish
  79     #  53M  trwiki-latest-page.sql.gz
  80     # 176M  trwiki-latest-pagelinks.sql.gz
  81     # 106M  trwiki-latest-langlinks.sql.gz
  82     # 3.2M  trwiki-latest-redirect.sql.gz
  83
  84     download https://dumps.wikimedia.org/${i}wiki/latest/${i}wiki-latest-page.sql.gz
  85     download https://dumps.wikimedia.org/${i}wiki/latest/${i}wiki-latest-pagelinks.sql.gz
  86     download https://dumps.wikimedia.org/${i}wiki/latest/${i}wiki-latest-langlinks.sql.gz
  87     download https://dumps.wikimedia.org/${i}wiki/latest/${i}wiki-latest-redirect.sql.gz
  88 done
  89
  90
  91
  92
  93
  94 echo "====================================================================="
  95 echo "Import individual wikipedia language tables"
  96 echo "====================================================================="
  97
  98 for i in "${LANGUAGES[@]}"
  99 do
 100     echo "Language: $i"
 101
 102     # We pre-create the table schema. This allows us to
 103     # 1. Skip index creation. Most queries we do are full table scans
 104     # 2. Add constrain to only import namespace=0 (wikipedia articles)
 105     # Both cuts down data size considerably (50%+)
 106
 107     echo "Importing ${i}wiki-latest-pagelinks"
 108
 109     echo "DROP TABLE IF EXISTS ${i}pagelinks;" | psqlcmd
 110     echo "CREATE TABLE ${i}pagelinks (
 111        pl_from            int  NOT NULL DEFAULT '0',
 112        pl_namespace       int  NOT NULL DEFAULT '0',
 113        pl_title           text NOT NULL DEFAULT '',
 114        pl_from_namespace  int  NOT NULL DEFAULT '0'
 115     );" | psqlcmd
 116
 117     time \
 118       gzip -dc ${i}wiki-latest-pagelinks.sql.gz | \
 119       sed "s/\`pagelinks\`/\`${i}pagelinks\`/g" | \
 120       mysql2pgsqlcmd | \
 121       grep -v '^CREATE INDEX ' | \
 122       psqlcmd
 123
 124
 125
 126
 127     echo "Importing ${i}wiki-latest-page"
 128
 129     # autoincrement serial8 4byte
 130     echo "DROP TABLE IF EXISTS ${i}page;" | psqlcmd
 131     echo "CREATE TABLE ${i}page (
 132        page_id             int NOT NULL,
 133        page_namespace      int NOT NULL DEFAULT '0',
 134        page_title          text NOT NULL DEFAULT '',
 135        page_restrictions   text NOT NULL,
 136        page_is_redirect    smallint NOT NULL DEFAULT '0',
 137        page_is_new         smallint NOT NULL DEFAULT '0',
 138        page_random         double precision NOT NULL DEFAULT '0',
 139        page_touched        text NOT NULL DEFAULT '',
 140        page_links_updated  text DEFAULT NULL,
 141        page_latest         int NOT NULL DEFAULT '0',
 142        page_len            int NOT NULL DEFAULT '0',
 143        page_content_model  text DEFAULT NULL,
 144        page_lang           text DEFAULT NULL
 145      );" | psqlcmd
 146
 147     time \
 148       gzip -dc ${i}wiki-latest-page.sql.gz | \
 149       sed "s/\`page\`/\`${i}page\`/g" | \
 150       mysql2pgsqlcmd | \
 151       grep -v '^CREATE INDEX ' | \
 152       psqlcmd
 153
 154
 155
 156
 157     echo "Importing ${i}wiki-latest-langlinks"
 158
 159     echo "DROP TABLE IF EXISTS ${i}langlinks;" | psqlcmd
 160     echo "CREATE TABLE ${i}langlinks (
 161        ll_from   int  NOT NULL DEFAULT '0',
 162        ll_lang   text NOT NULL DEFAULT '',
 163        ll_title  text NOT NULL DEFAULT ''
 164     );" | psqlcmd
 165
 166     time \
 167       gzip -dc ${i}wiki-latest-langlinks.sql.gz | \
 168       sed "s/\`langlinks\`/\`${i}langlinks\`/g" | \
 169       mysql2pgsqlcmd | \
 170       grep -v '^CREATE INDEX ' | \
 171       psqlcmd
 172
 173
 174
 175
 176
 177     echo "Importing ${i}wiki-latest-redirect"
 178
 179     echo "DROP TABLE IF EXISTS ${i}redirect;" | psqlcmd
 180     echo "CREATE TABLE ${i}redirect (
 181        rd_from       int   NOT NULL DEFAULT '0',
 182        rd_namespace  int   NOT NULL DEFAULT '0',
 183        rd_title      text  NOT NULL DEFAULT '',
 184        rd_interwiki  text  DEFAULT NULL,
 185        rd_fragment   text  DEFAULT NULL
 186     );" | psqlcmd
 187
 188     time \
 189       gzip -dc ${i}wiki-latest-redirect.sql.gz | \
 190       sed "s/\`redirect\`/\`${i}redirect\`/g" | \
 191       mysql2pgsqlcmd | \
 192       grep -v '^CREATE INDEX ' | \
 193       psqlcmd
 194 done
 195
 196
 197
 198
 199
 200 echo "====================================================================="
 201 echo "Process language tables and associated pagelink counts"
 202 echo "====================================================================="
 203
 204
 205 for i in "${LANGUAGES[@]}"
 206 do
 207     echo "Language: $i"
 208
 209     echo "CREATE TABLE ${i}pagelinkcount
 210           AS
 211           SELECT pl_title AS title,
 212                  COUNT(*) AS count,
 213                  0::bigint as othercount
 214           FROM ${i}pagelinks
 215           WHERE pl_namespace = 0
 216           GROUP BY pl_title
 217           ;" | psqlcmd
 218
 219     echo "INSERT INTO linkcounts
 220           SELECT '${i}',
 221                  pl_title,
 222                  COUNT(*)
 223           FROM ${i}pagelinks
 224           WHERE pl_namespace = 0
 225           GROUP BY pl_title
 226           ;" | psqlcmd
 227
 228     echo "INSERT INTO wikipedia_redirect
 229           SELECT '${i}',
 230                  page_title,
 231                  rd_title
 232           FROM ${i}redirect
 233           JOIN ${i}page ON (rd_from = page_id)
 234           WHERE page_namespace = 0
 235             AND rd_namespace = 0
 236           ;" | psqlcmd
 237
 238 done
 239
 240
 241 for i in "${LANGUAGES[@]}"
 242 do
 243     for j in "${LANGUAGES[@]}"
 244     do
 245         echo "UPDATE ${i}pagelinkcount
 246               SET othercount = ${i}pagelinkcount.othercount + x.count
 247               FROM (
 248                 SELECT page_title AS title,
 249                        count
 250                 FROM ${i}langlinks
 251                 JOIN ${i}page ON (ll_from = page_id)
 252                 JOIN ${j}pagelinkcount ON (ll_lang = '${j}' AND ll_title = title)
 253               ) AS x
 254               WHERE x.title = ${i}pagelinkcount.title
 255               ;" | psqlcmd
 256     done
 257
 258     echo "INSERT INTO wikipedia_article
 259           SELECT '${i}',
 260                  title,
 261                  count,
 262                  othercount,
 263                  count + othercount
 264           FROM ${i}pagelinkcount
 265           ;" | psqlcmd
 266 done
 267
 268
 269
 270
 271
 272 echo "====================================================================="
 273 echo "Calculate importance score for each wikipedia page"
 274 echo "====================================================================="
 275
 276 echo "UPDATE wikipedia_article
 277       SET importance = LOG(totalcount)/LOG((SELECT MAX(totalcount) FROM wikipedia_article))
 278       ;" | psqlcmd
 279
 280
 281
 282
 283
 284 echo "====================================================================="
 285 echo "Clean up intermediate tables to conserve space"
 286 echo "====================================================================="
 287
 288 for i in "${LANGUAGES[@]}"
 289 do
 290     echo "DROP TABLE ${i}pagelinks;"     | psqlcmd
 291     echo "DROP TABLE ${i}page;"          | psqlcmd
 292     echo "DROP TABLE ${i}langlinks;"     | psqlcmd
 293     echo "DROP TABLE ${i}redirect;"      | psqlcmd
 294     echo "DROP TABLE ${i}pagelinkcount;" | psqlcmd
 295 done
 296
 297 echo "all done."