Mxc4yU9i

1-- DATABASE CREATION
2CREATE DATABASE IF NOT EXISTS MOVIELENS_1M_DB COMMENT "MovieLens";
3
4-- USER TABLE CREATION
5CREATE EXTERNAL TABLE IF NOT EXISTS MOVIELENS_1M_DB.USERS(
6  USER_ID     INT COMMENT     'USER ID ANONYMIZED',
7  GENDER      STRING  COMMENT 'M=MALE, F=FEMALE',
8  AGE         INT COMMENT     '7 TERM AGE',
9  OCCUPATION  INT  COMMENT    'USER OCCUPATION CODE',
10  ZIPCODE     INT COMMENT     'POSTAL CODE - US BASED'
11)
12COMMENT 'USER DEMOGRAPHIC INFO'
13-- DEFINE SERIALIZADOR ESPECÍFICO 
14ROW FORMAT 
15  SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
16  WITH SERDEPROPERTIES ('field.delim'='::')
17STORED AS TEXTFILE
18LOCATION '/user/data/movielens-dataset/'
19TBLPROPERTIES ('CREATOR'='MARKU','CREATED_AT'='2017-11-16');
20
21-- MOVIES TABLE CREATION
22CREATE EXTERNAL TABLE IF NOT EXISTS MOVIELENS_1M_DB.MOVIES(
23  MOVIE_ID    INT           COMMENT 'MOVIE IDENTIFICATION',
24  TITLE       STRING        COMMENT 'MOVIE TITLE, INCLUDING YEAR',
25  GENRE       ARRAY<STRING> COMMENT 'LIST OF GENRES'
26)
27COMMENT 'MOVIES LIST'
28-- DEFINE SERIALIZADOR ESPECÍFICO
29ROW FORMAT 
30  SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
31  WITH SERDEPROPERTIES ('field.delim'='::','collection.delim'='|')
32STORED AS TEXTFILE
33LOCATION '/user/data/movielens-dataset/'
34TBLPROPERTIES ('CREATOR'='MARKU','CREATED_AT'='2017-11-16');
35
36-- RATINGS TABLE CREATION
37CREATE EXTERNAL TABLE IF NOT EXISTS MOVIELENS_1M_DB.RATINGS(
38  USER_ID     INT COMMENT 'USER IDENTIFICATION',
39  MOVIE_ID    INT COMMENT 'MOVIE IDENTIFICATION',
40  RATING      INT COMMENT 'MOVIE RATING BY USER',
41  TIMESTAMP   INT COMMENT 'WHEN THE RATING WAS MADE'
42)
43COMMENT 'RATINGS TABLE - LOOKS FOR MOVIES AND USERS TABLES'
44-- DEFINE SERIALIZADOR ESPECÍFICO
45ROW FORMAT 
46  SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
47  WITH SERDEPROPERTIES ('field.delim'='::')
48STORED AS TEXTFILE
49LOCATION '/user/data/movielens-dataset/'
50TBLPROPERTIES ('CREATOR'='MARKU','CREATED_AT'='2017-11-16');