loadData.sql 5.47 KB
Newer Older
kcchik's avatar
Add sql  
kcchik committed
1
2
3
4
drop table if exists ImdbMovies;
create table ImdbMovies (
  movieID char(9),
  title varchar(256),
kcchik's avatar
sql    
kcchik committed
5
  originalTitle varchar(256),
kcchik's avatar
Add sql  
kcchik committed
6
  year int,
kcchik's avatar
sql    
kcchik committed
7
  releaseDate datetime,
kcchik's avatar
Add sql  
kcchik committed
8
9
  genre varchar(32),
  duration int,
Daniel Kim's avatar
Daniel Kim committed
10
11
  country varchar(128),
  language varchar(128),
kcchik's avatar
Add sql  
kcchik committed
12
13
  director varchar(64),
  writer varchar(64),
kcchik's avatar
sql    
kcchik committed
14
  productionCompany varchar(128),
kcchik's avatar
Add sql  
kcchik committed
15
  actors varchar(128),
kcchik's avatar
sql    
kcchik committed
16
17
  description text,
  score float,
kcchik's avatar
Add sql  
kcchik committed
18
19
  votes int,
  budget varchar(32),
kcchik's avatar
sql    
kcchik committed
20
21
  grossIncomeUSA varchar(32),
  grossIncomeWorldwide varchar(32),
kcchik's avatar
Add sql  
kcchik committed
22
  metascore float,
kcchik's avatar
sql    
kcchik committed
23
24
  userScore float,
  criticScore float
kcchik's avatar
Add sql  
kcchik committed
25
);
Daniel Kim's avatar
Daniel Kim committed
26
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IMDb movies.csv' ignore into table ImdbMovies
kcchik's avatar
Add sql  
kcchik committed
27
28
  fields terminated by ','
  enclosed by '"'
Daniel Kim's avatar
Daniel Kim committed
29
  escaped by ''
kcchik's avatar
Add sql  
kcchik committed
30
31
  lines terminated by '\n'
  ignore 1 lines;
kcchik's avatar
sql    
kcchik committed
32
update ImdbMovies set releaseDate = null where cast(releaseDate as char(20)) = '0000-00-00 00:00:00';
kcchik's avatar
kcchik committed
33
34
35
update ImdbMovies set budget = '$ 0' where budget = '';
update ImdbMovies set grossIncomeUSA = '$ 0' where grossIncomeUSA = '';
update ImdbMovies set grossIncomeWorldwide = '$ 0' where grossIncomeWorldwide = '';
kcchik's avatar
Add sql  
kcchik committed
36
37
38
39

drop table if exists ImdbNames;
create table ImdbNames (
  personID char(9),
Daniel Kim's avatar
Daniel Kim committed
40
41
  name varchar(64),
  birthName varchar(256),
kcchik's avatar
Add sql  
kcchik committed
42
  height int,
kcchik's avatar
sql    
kcchik committed
43
44
45
46
47
48
49
50
  bio text,
  birthDetails varchar(256),
  dateOfBirth datetime,
  placeOfBirth varchar(256),
  deathDetails varchar(256),
  dateOfDeath datetime,
  placeOfDeath varchar(128),
  reasonOfDeath varchar(128),
kcchik's avatar
Add sql  
kcchik committed
51
52
  spouses int,
  divorces int,
kcchik's avatar
sql    
kcchik committed
53
  spousesWithChildren int,
kcchik's avatar
Add sql  
kcchik committed
54
55
  children int
);
Daniel Kim's avatar
Daniel Kim committed
56
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IMDb names.csv' ignore into table ImdbNames
kcchik's avatar
Add sql  
kcchik committed
57
58
59
60
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;
kcchik's avatar
sql    
kcchik committed
61
62
update ImdbNames set dateOfBirth = null where cast(dateOfBirth as char(20)) = '0000-00-00 00:00:00';
update ImdbNames set dateOfDeath = null where cast(dateOfDeath as char(20)) = '0000-00-00 00:00:00';
kcchik's avatar
Add sql  
kcchik committed
63

kcchik's avatar
kcchik committed
64
65
66
67
68
69
70
71
72
73
74
75
-- drop table if exists ImdbRatings;
-- create table ImdbRatings (
--   movieID char(9),
--   weightedScore float,
--   votes int,
--   score float
-- );
-- load data infile '/var/lib/mysql-files/03-Movies/IMDb ratings.csv' ignore into table ImdbRatings
--   fields terminated by ','
--   enclosed by '"'
--   lines terminated by '\n'
--   ignore 1 lines;
kcchik's avatar
imdb    
kcchik committed
76
77
78
79
80
81
82
83
84
85

drop table if exists ImdbTitlePrincipals;
create table ImdbTitlePrincipals (
  movieID char(9),
  ordering int,
  personID char(9),
  category varchar(32),
  job varchar(256),
  characters varchar(512)
);
Daniel Kim's avatar
Daniel Kim committed
86
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IMDb title_principals.csv' ignore into table ImdbTitlePrincipals
kcchik's avatar
imdb    
kcchik committed
87
88
89
90
91
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;

kcchik's avatar
Add sql  
kcchik committed
92
93
94
95
96
97
98
99
100
101
drop table if exists MojoBudgetData;
create table MojoBudgetData (
  movieID char(9),
  title varchar(256),
  year int,
  director varchar(64),
  writer varchar(64),
  producer varchar(64),
  composer varchar(64),
  cinematography varchar(64),
kcchik's avatar
sql    
kcchik committed
102
103
104
105
  actor1 varchar(64),
  actor2 varchar(64),
  actor3 varchar(64),
  actor4 varchar(64),
kcchik's avatar
kcchik committed
106
107
108
109
  budget bigint,
  domestic bigint,
  international bigint,
  worldwide bigint,
kcchik's avatar
Add sql  
kcchik committed
110
  mpaa varchar(16),
kcchik's avatar
sql    
kcchik committed
111
112
113
114
115
  runtime varchar(16),
  genre1 varchar(16),
  genre2 varchar(16),
  genre3 varchar(16),
  genre4 varchar(16),
kcchik's avatar
Add sql  
kcchik committed
116
117
  link varchar(64)
);
Daniel Kim's avatar
Daniel Kim committed
118
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Mojo_budget_data.csv' ignore into table MojoBudgetData
kcchik's avatar
Add sql  
kcchik committed
119
120
121
122
123
124
125
126
127
128
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;

drop table if exists MojoBudgetUpdate;
create table MojoBudgetUpdate (
  movieID char(9),
  title varchar(256),
  year int,
kcchik's avatar
sql    
kcchik committed
129
  trivia text,
kcchik's avatar
Add sql  
kcchik committed
130
  mpaa varchar(16),
kcchik's avatar
sql    
kcchik committed
131
132
  releaseDate datetime,
  runtime varchar(16),
kcchik's avatar
Add sql  
kcchik committed
133
134
135
136
137
138
  distributor varchar(64),
  director varchar(64),
  writer varchar(64),
  producer varchar(64),
  composer varchar(64),
  cinematography varchar(64),
kcchik's avatar
sql    
kcchik committed
139
140
141
142
  actor1 varchar(64),
  actor2 varchar(64),
  actor3 varchar(64),
  actor4 varchar(64),
kcchik's avatar
kcchik committed
143
144
145
146
  budget bigint,
  domestic bigint,
  international bigint,
  worldwide bigint,
kcchik's avatar
sql    
kcchik committed
147
148
149
150
  genre1 varchar(16),
  genre2 varchar(16),
  genre3 varchar(16),
  genre4 varchar(16),
kcchik's avatar
Add sql  
kcchik committed
151
152
  html varchar(64)
);
Daniel Kim's avatar
Daniel Kim committed
153
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Mojo_budget_update.csv' ignore into table MojoBudgetUpdate
kcchik's avatar
Add sql  
kcchik committed
154
155
156
157
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;
kcchik's avatar
sql    
kcchik committed
158
update MojoBudgetUpdate set releaseDate = null where cast(releaseDate as char(20)) = '0000-00-00 00:00:00';
kcchik's avatar
Add sql  
kcchik committed
159

kcchik's avatar
sql    
kcchik committed
160
161
drop table if exists MoviesMetadata;
create table MoviesMetadata (
kcchik's avatar
Add sql  
kcchik committed
162
  adult bool,
kcchik's avatar
sql    
kcchik committed
163
  belongsToCollection text,
kcchik's avatar
Add sql  
kcchik committed
164
  budget int,
kcchik's avatar
sql    
kcchik committed
165
  genres text,
kcchik's avatar
Add sql  
kcchik committed
166
167
168
  homepage varchar(256),
  id int,
  movieID char(9),
kcchik's avatar
sql    
kcchik committed
169
170
171
  language varchar(8),
  originalTitle varchar(256),
  overview text,
kcchik's avatar
Add sql  
kcchik committed
172
  popularity float,
kcchik's avatar
sql    
kcchik committed
173
174
175
176
  posterPath varchar(64),
  productionCompanies text,
  productionCountries text,
  releaseDate datetime,
kcchik's avatar
kcchik committed
177
  revenue bigint,
kcchik's avatar
Add sql  
kcchik committed
178
  runtime int,
kcchik's avatar
sql    
kcchik committed
179
  spokenLanguages text,
kcchik's avatar
Add sql  
kcchik committed
180
  status varchar(16),
kcchik's avatar
sql    
kcchik committed
181
  tagline text,
kcchik's avatar
Add sql  
kcchik committed
182
183
  title varchar(256),
  video bool,
kcchik's avatar
sql    
kcchik committed
184
185
  score float,
  votes int
kcchik's avatar
Add sql  
kcchik committed
186
);
Daniel Kim's avatar
Daniel Kim committed
187
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/movies_metadata.csv' ignore into table MoviesMetadata
kcchik's avatar
sql    
kcchik committed
188
189
190
191
192
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  ignore 1 lines;
update MoviesMetadata set releaseDate = null where cast(releaseDate as char(20)) = '0000-00-00 00:00:00';
kcchik's avatar
Add sql  
kcchik committed
193

kcchik's avatar
kcchik committed
194
195
196
197
198
199
200
201
202
203
204
-- drop table if exists MoviesCredits;
-- create table MoviesCredits (
--   cast text,
--   crew text,
--   id int
-- );
-- load data infile '/var/lib/mysql-files/03-Movies/credits.csv' ignore into table MoviesCredits
--   fields terminated by ','
--   enclosed by '"'
--   lines terminated by '\n'
--   ignore 1 lines;