Advertisement
Guest User

Untitled

a guest
Jul 20th, 2017
51
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.44 KB | None | 0 0
  1. DROP TABLE IF EXISTS session_access_staging ;
  2.  
  3. CREATE TABLE session_access_staging (
  4.   first_access INT NOT NULL,
  5.   last_access INT NOT NULL,
  6.   content_name VARCHAR(128) NOT NULL,
  7.   client_ip VARCHAR(20) NOT NULL,
  8.   session_id VARCHAR(64) NOT NULL,
  9.   bytes INT NOT NULL
  10. ) ENGINE=InnoDB;
  11.  
  12. LOAD DATA LOCAL INFILE 'simple.in' INTO TABLE session_access_staging FIELDS TERMINATED BY ' ' IGNORE 1 LINES;
  13.  
  14.  
  15. DROP TABLE IF EXISTS content_dim ;
  16.  
  17. CREATE TABLE content_dim (
  18.   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  19.   content_name VARCHAR(255) NOT NULL,
  20.   account_name VARCHAR(255) NOT NULL
  21. ) ENGINE=InnoDB;
  22.  
  23. LOAD DATA LOCAL INFILE 'account-dim.in' INTO TABLE content_dim FIELDS TERMINATED BY ' ' (content_name, account_name);
  24.  
  25. DROP TABLE IF EXISTS prov_fact;
  26.  
  27. CREATE TABLE prov_fact (
  28.   content_sk INT NOT NULL,
  29.   bytes INT NOT NULL
  30. ) ENGINE=InnoDB;
  31.  
  32.  
  33. DROP TABLE IF EXISTS pre_prov_fact ;
  34.  
  35. CREATE TABLE pre_prov_fact (
  36.   content_name VARCHAR(128) NOT NULL,
  37.   bytes INT NOT NULL
  38. ) ENGINE=InnoDB;
  39.  
  40.  
  41. INSERT INTO pre_prov_fact(content_name, bytes)
  42.   SELECT
  43.     session_access_staging.content_name,
  44.     SUM(session_access_staging.bytes)
  45.   FROM
  46.     session_access_staging
  47.   GROUP BY
  48.     session_access_staging.content_name;
  49.  
  50.  
  51. INSERT INTO prov_fact(content_sk, bytes)
  52.   SELECT
  53.     content_dim.id,
  54.     pre_prov_fact.bytes
  55.   FROM
  56.     content_dim,
  57.     pre_prov_fact
  58.   WHERE
  59.     pre_prov_fact.content_name = content_dim.content_name;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement