Page MenuHomePhabricator
Create Task
Maniphest T196564

DB schemas (production changes and test DB) and SQL commands to run for new banner and LP impression data from EL
Closed, ResolvedPublic2 Estimated Story Points
Actions

Assigned To
AndyRussG
Authored By
AndyRussG
Jun 6 2018, 3:49 PM
Tags
Referenced Files
None
Subscribers
Aklapper
AndyRussG
• DStrine
Ejegg
gerritbot
jgleeson
kai.nissen
View All 9 Subscribers

Description

Here's what we need:

  1. Specify what (if any) new columns and tables may be needed in pgehres, and make the changes
  2. Create a database for ingressing test data during an initial testing phase
  3. Ensure a seamless switchover from the current pipeline to the new data from EventLogging

Let's plan here the exact SQL commands to run and plan exactly how and when to run them.

Requirements:

  • No data loss or corruption due to switchover.
  • Existing tools used on the production database should work without changes.

Thanks!!!

Related Objects
Search...

Event Timeline

AndyRussG triaged this task as Medium priority.Jun 6 2018, 3:49 PM
AndyRussG created this task.
AndyRussG mentioned this in T195594: New scripts to ingress data from Kafkatee into MySQL.
AndyRussG removed Ejegg as the assignee of this task.Jun 6 2018, 3:54 PM
AndyRussG added a subscriber: Ejegg.
• DStrine moved this task from Triage to Current Sprint on the Fundraising-Backlog board.Jun 6 2018, 4:14 PM
• DStrine added a project: Fundraising Sprint Matt Damon to head up Space Force.Jun 18 2018, 8:21 PM
AndyRussG moved this task from Backlog to Doing on the Fundraising Sprint Matt Damon to head up Space Force board.Jun 21 2018, 4:05 PM
AndyRussG claimed this task.Jun 22 2018, 2:54 PM
• Vvjjkkii renamed this task from DB schemas (production changes and test DB) and SQL commands to run for new banner and LP impression data from EL to 7ibaaaaaaa.Jul 1 2018, 1:06 AM
• Vvjjkkii removed AndyRussG as the assignee of this task.
• Vvjjkkii raised the priority of this task from Medium to High.
• Vvjjkkii added projects: CheckUser, Connected-Open-Heritage-Batch-uploads (RAÄ-KMB_1_2017-02), Tamil-Sites, Gamepress, Hashtags, Jade, KartoEditor, Language-2018-Apr-June, New-Editor-Experiences, Mail, TCB-Team (now WMDE-TechWish).
• Vvjjkkii updated the task description. (Show Details)
• Vvjjkkii removed the point value for this task.
• Vvjjkkii removed subscribers: Aklapper, gerritbot.
CommunityTechBot renamed this task from 7ibaaaaaaa to DB schemas (production changes and test DB) and SQL commands to run for new banner and LP impression data from EL.Jul 2 2018, 8:08 AM
CommunityTechBot assigned this task to AndyRussG.
CommunityTechBot lowered the priority of this task from High to Medium.
CommunityTechBot set the point value for this task to 2.
CommunityTechBot updated the task description. (Show Details)
CommunityTechBot removed projects: TCB-Team (now WMDE-TechWish), Mail, New-Editor-Experiences, Language-2018-Apr-June, KartoEditor, Jade, Hashtags, Gamepress, Tamil-Sites, Connected-Open-Heritage-Batch-uploads (RAÄ-KMB_1_2017-02), CheckUser.
CommunityTechBot added subscribers: Aklapper, gerritbot.
AndyRussG added a comment.Jul 2 2018, 6:26 PM

Proposed plan:

Only one change to the production database: rename squidlog table to files_processed. (For other DB cleanup tasks, see T198641, T198639 and T198635. Those changes are not needed for the Kafka pipeline overhaul.) We'll make this change at the same time as we switch the new scripts from the testing database to the production one.

SQL to create the testing database:

CREATE DATABASE new_kafka_testing;

-- Adjust permissions as required (details to be determined)

USE new_kafka_testing;

CREATE TABLE `files_processed` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  filename        VARCHAR(128)  NOT NULL,
  impressiontype  VARCHAR(128)  NOT NULL,
  timestamp       TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY (filename)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `project` (
  id        SMALLINT(3)   UNSIGNED AUTO_INCREMENT,
  project   VARCHAR(128)  NOT NULL,

  PRIMARY KEY(id),
  UNIQUE KEY(project)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `language` (
  id        SMALLINT(3)   UNSIGNED AUTO_INCREMENT,
  language  VARCHAR(128)  DEFAULT '' NOT NULL,
  iso_code  VARCHAR(24)   NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (iso_code)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `country` (
  id        SMALLINT(3)   UNSIGNED AUTO_INCREMENT,
  country   VARCHAR(128)  DEFAULT '' NOT NULL,
  iso_code  VARCHAR(8) NOT NULL,

  PRIMARY KEY(id),
  UNIQUE KEY (iso_code)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `bannerimpressions` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  timestamp       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
  banner          VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  campaign        VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  project_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  language_id     SMALLINT(3)   UNSIGNED DEFAULT NULL,
  country_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  count           MEDIUMINT(11) DEFAULT 0,

  PRIMARY KEY (id),
  UNIQUE KEY(timestamp, banner, campaign, project_id, language_id, country_id),
  INDEX (timestamp),
  INDEX (banner),
  INDEX (campaign),
  INDEX (project_id),
  INDEX (language_id),
  INDEX (country_id)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `landingpageimpression_raw` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  timestamp       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
  squid_id        SMALLINT(11)  UNSIGNED DEFAULT NULL,
  squid_sequence  INT(11)       UNSIGNED DEFAULT NULL,
  utm_source      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_campaign    VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_medium      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_key         VARCHAR(128)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  landingpage     VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  project_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  language_id     SMALLINT(3)   UNSIGNED DEFAULT NULL,
  country_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  processed       TINYINT(1)    DEFAULT 0,

  PRIMARY KEY (id),
  INDEX (timestamp),
  INDEX (utm_source),
  INDEX (utm_campaign),
  INDEX (utm_medium),
  INDEX (utm_key),
  INDEX (landingpage),
  INDEX (project_id),
  INDEX (language_id),
  INDEX (country_id),
  INDEX (processed),
  UNIQUE (squid_id, squid_sequence, timestamp)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `donatewiki_unique` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  timestamp       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
  utm_source      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_campaign    VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  contact_id      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  link_id         VARCHAR(128)  CHARACTER SET utf8 DEFAULT '' NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY utm_source (utm_source, contact_id)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

SQL to rename squidlog and create a view for the old name, to run when we switch the new script to production:

use pgehres;
RENAME TABLE squidlog TO files_processed;
CREATE VIEW squidlog AS SELECT id, filename, impressiontype, timestamp FROM files_processed;
-- Fix permissions for renamed table and view
AndyRussG moved this task from Doing to Review on the Fundraising Sprint Matt Damon to head up Space Force board.Jul 2 2018, 6:26 PM
AndyRussG mentioned this in T198752: Queries and maybe scripts to verify equivalence of data in new-Kafka-pipeline-testing and pgehres production databases.Jul 3 2018, 7:21 PM
Ejegg moved this task from Review to Pending Deployment on the Fundraising Sprint Matt Damon to head up Space Force board.Jul 6 2018, 12:23 AM
• DStrine added a project: Fundraising Sprint Naming Sprints Is Not Important.Jul 11 2018, 9:26 PM
AndyRussG moved this task from Backlog to Pending Deployment on the Fundraising Sprint Naming Sprints Is Not Important board.Jul 11 2018, 10:49 PM
• DStrine added a project: Fundraising Sprint Owls.Jul 24 2018, 8:14 PM
AndyRussG mentioned this in T200721: pgehres: Remove unused columns.Jul 30 2018, 7:40 PM
• DStrine added a project: Fundraising Sprint Pluto is still a planet.Aug 7 2018, 8:42 PM
• DStrine added a project: Fundraising Sprint Queue is pronounced GJif.Aug 21 2018, 8:47 PM
AndyRussG moved this task from Backlog to Doing on the Fundraising Sprint Queue is pronounced GJif board.Sep 4 2018, 7:53 PM
AndyRussG added a comment.EditedSep 4 2018, 8:11 PM

Updated SQL to create the testing database, as per FRUEC's sql/create_tables.sql.

CREATE DATABASE fruec_testing;

-- Adjust permissions as required (details to be determined)

USE fruec_testing;

CREATE TABLE `files` (
  id               INT(11)       UNSIGNED AUTO_INCREMENT,
  filename         VARCHAR(128)  NOT NULL,
  impressiontype   VARCHAR(15)   NOT NULL,
  timestamp        TIMESTAMP     NOT NULL,
  directory        VARCHAR(256)  NOT NULL,
  sample_rate      INT(2)        UNSIGNED DEFAULT NULL,
  status           ENUM('processing', 'consumed') NOT NULL,
  consumed_events  INT           UNSIGNED DEFAULT NULL,
  ignored_events   INT           UNSIGNED DEFAULT NULL,
  invalid_events    INT           UNSIGNED DEFAULT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (filename),
  INDEX (timestamp),
  INDEX (status)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `project` (
  id        SMALLINT(3)   UNSIGNED AUTO_INCREMENT,
  project   VARCHAR(128)  NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (project)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `language` (
  id        SMALLINT(3)   UNSIGNED AUTO_INCREMENT,
  iso_code  VARCHAR(24)   NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (iso_code)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `country` (
  id        SMALLINT(3)   UNSIGNED AUTO_INCREMENT,
  iso_code  VARCHAR(8)    NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (iso_code)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `bannerimpressions` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  timestamp       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
  banner          VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  campaign        VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  project_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  language_id     SMALLINT(3)   UNSIGNED DEFAULT NULL,
  country_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  count           MEDIUMINT(11) DEFAULT 0,
  file_id         INT(11)       UNSIGNED NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (timestamp, banner, campaign, project_id, language_id, country_id),
  INDEX (timestamp),
  INDEX (banner),
  INDEX (campaign),
  INDEX (project_id),
  INDEX (language_id),
  INDEX (country_id),
  INDEX (file_id)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `landingpageimpression_raw` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  timestamp       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
  utm_source      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_campaign    VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_medium      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_key         VARCHAR(128)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  landingpage     VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  project_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  language_id     SMALLINT(3)   UNSIGNED DEFAULT NULL,
  country_id      SMALLINT(3)   UNSIGNED DEFAULT NULL,
  file_id         INT(11)       UNSIGNED NOT NULL,

  PRIMARY KEY (id),
  INDEX (timestamp),
  INDEX (utm_source),
  INDEX (utm_campaign),
  INDEX (utm_medium),
  INDEX (utm_key),
  INDEX (landingpage),
  INDEX (project_id),
  INDEX (language_id),
  INDEX (country_id),
  INDEX (file_id)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;

CREATE TABLE `donatewiki_unique` (
  id              INT(11)       UNSIGNED AUTO_INCREMENT,
  timestamp       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
  utm_source      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  utm_campaign    VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  contact_id      VARCHAR(255)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  link_id         VARCHAR(128)  CHARACTER SET utf8 DEFAULT '' NOT NULL,
  file_id         INT(11)       UNSIGNED NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY utm_source (utm_source, contact_id),
  INDEX (file_id)
) DEFAULT CHARACTER SET = utf8 ENGINE = InnoDB;
• DStrine added a project: Fundraising Sprint Raw data can give you salmonella.Sep 4 2018, 8:37 PM
AndyRussG moved this task from Backlog to Doing on the Fundraising Sprint Raw data can give you salmonella board.Sep 5 2018, 1:54 AM
AndyRussG added a comment.Sep 5 2018, 1:58 PM

So, the SQL to create the testing database should just be whatever's in sql/create_tables.sql in FRUEC.

For the commands to update pgehres, I'm using this GitHub gist, so we can track changes. (I haven't tested that code yet... I'll confirm here when I have.)

AndyRussG added a comment.Sep 6 2018, 3:26 PM

Tested the update script locally--works so far!

AndyRussG moved this task from Doing to Review on the Fundraising Sprint Raw data can give you salmonella board.Sep 6 2018, 3:30 PM
• DStrine added a project: Fundraising Sprint Sasquatches can't find us either.Sep 18 2018, 8:37 PM
AndyRussG moved this task from Backlog to Review on the Fundraising Sprint Sasquatches can't find us either board.Sep 18 2018, 9:20 PM
• DStrine added a project: Fundraising Sprint They Live.Oct 2 2018, 8:12 PM
AndyRussG moved this task from Backlog to Review on the Fundraising Sprint They Live board.Oct 3 2018, 2:40 PM
• DStrine added a project: Fundraising Sprint USB stands for underhanded socket bureaucracy.Oct 16 2018, 8:29 PM
AndyRussG moved this task from Backlog to Review on the Fundraising Sprint USB stands for underhanded socket bureaucracy board.Oct 17 2018, 2:52 PM
• DStrine added a project: Fundraising Sprint Vestigial tails shoot from the hip.Oct 30 2018, 8:42 PM
AndyRussG moved this task from Backlog to Review on the Fundraising Sprint Vestigial tails shoot from the hip board.Oct 31 2018, 4:18 PM
• DStrine moved this task from Current Sprint to Sprint +3 on the Fundraising-Backlog board.Nov 13 2018, 8:55 PM
• DStrine moved this task from Sprint +3 to Q4 FY21-22 on the Fundraising-Backlog board.May 8 2019, 8:24 PM
AndyRussG mentioned this in T183978: [Epic] Fundraising kafkatee changes.May 21 2019, 7:34 PM
• DStrine moved this task from Q4 FY21-22 to Current Sprint on the Fundraising-Backlog board.Jun 11 2019, 7:58 PM
• DStrine added a project: Fundraising Sprint Land before Timeouts.Jun 11 2019, 8:01 PM
• DStrine added a project: Fundraising Sprint Men In Slack.Jun 25 2019, 8:44 PM
AndyRussG moved this task from Backlog to Review on the Fundraising Sprint Men In Slack board.Jun 26 2019, 3:58 PM
• DStrine added a project: Fundraising Sprint Never Ending Query.Jul 9 2019, 8:37 PM
AndyRussG moved this task from Backlog to Pending Deployment on the Fundraising Sprint Never Ending Query board.Jul 10 2019, 5:28 AM
AndyRussG moved this task from Pending Deployment to Review on the Fundraising Sprint Never Ending Query board.
jgleeson subscribed.Jul 16 2019, 4:28 PM

I think we're happy this is done as it's a prereq for FRUEC?

jgleeson moved this task from Review to Pending Deployment on the Fundraising Sprint Never Ending Query board.Jul 16 2019, 4:28 PM
• DStrine added a project: Fundraising Sprint Office  .Jul 23 2019, 8:30 PM
AndyRussG moved this task from Backlog to Review on the Fundraising Sprint Office   board.Aug 1 2019, 5:07 PM
AndyRussG moved this task from Review to Pending Deployment on the Fundraising Sprint Office   board.Aug 4 2019, 6:57 AM
• DStrine added a project: Fundraising Sprint Princess Mongodb.Aug 6 2019, 9:09 PM
AndyRussG moved this task from Backlog to Pending Deployment on the Fundraising Sprint Princess Mongodb board.Aug 8 2019, 4:38 PM
• DStrine added a project: Fundraising Sprint Quick and the Deadlocked.Aug 20 2019, 8:44 PM
AndyRussG moved this task from Backlog to Pending Deployment on the Fundraising Sprint Quick and the Deadlocked board.Aug 21 2019, 4:12 AM
Jgreen mentioned this in T231249: puppetize and configure fruec banner log parser/db injector script .Aug 26 2019, 7:51 PM
• DStrine added a project: Fundraising Sprint Rocky Horror Presentation Layer.Sep 3 2019, 8:24 PM
AndyRussG moved this task from Backlog to Pending Deployment on the Fundraising Sprint Rocky Horror Presentation Layer board.Sep 4 2019, 2:29 PM
• DStrine added a project: Fundraising Sprint Sysadmin Kane.Sep 17 2019, 8:14 PM
AndyRussG moved this task from Backlog to Pending Deployment on the Fundraising Sprint Sysadmin Kane board.Sep 17 2019, 8:50 PM
Ejegg moved this task from Current Sprint to Q4 FY21-22 on the Fundraising-Backlog board.Oct 1 2019, 8:39 PM
AndyRussG added a comment.Oct 7 2019, 7:08 AM

This needs updating due to this schema change: https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/FRUEC/+/541155

AndyRussG moved this task from Q4 FY21-22 to Triage on the Fundraising-Backlog board.Oct 7 2019, 7:08 AM
• DStrine moved this task from Triage to Sprint +1 on the Fundraising-Backlog board.Oct 7 2019, 7:52 PM
AndyRussG mentioned this in T234958: FRUEC: New SQL to modify existing pgehres database.Oct 8 2019, 4:32 PM
AndyRussG closed this task as Resolved.Oct 8 2019, 5:12 PM

I'm closing this task, as the initial work is done, and the remaining work is now tracked separately.

  • SQL to set up the testing database have been applied on production. It's the same as what is required for FRUEC developer setup, now in create_tables.sql in the FRUEC repo.
  • T234958 is for a needed update to the SQL to modify the production database (pgehres).
  • T234969 is for applying the SQL to modify the production database.

Thanks!!

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL