init_db.sql

CREATE DATABASE IF NOT EXISTS myanimelist_db;

USE myanimelist_db;


CREATE EXTERNAL TABLE IF NOT EXISTS score (
    user_id INT,
    anime_id INT,
    rating INT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = ',',
    "quoteChar"     = '\"',
    "escapeChar"    = '\\'
)
TBLPROPERTIES ("skip.header.line.count"="1")
LOCATION '/dataset/score';


CREATE EXTERNAL TABLE IF NOT EXISTS anime (
    anime_id INT,
    name STRING,
    english_name STRING,
    other_name STRING,
    score FLOAT,
    genres STRING,
    synopsis STRING,
    type STRING,
    episodes FLOAT,
    aired STRING,
    premiered STRING,
    status STRING,
    producers STRING,
    licensors STRING,
    studios STRING,
    source STRING,
    duration STRING,
    rating STRING,
    rank FLOAT,
    popularity INT,
    favorites INT,
    scored_by FLOAT,
    members INT,
    image_url STRING
    anime_id INT,
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = ',',
    "quoteChar"     = '\"',
    "escapeChar"    = '\\'
)
TBLPROPERTIES ("skip.header.line.count"="1")
LOCATION '/dataset/anime';

CREATE EXTERNAL TABLE IF NOT EXISTS users (
    mal_id INT,
    username STRING,
    gender STRING,
    birthday TIMESTAMP,
    location STRING,
    joined TIMESTAMP,
    days_watched FLOAT,
    mean_score FLOAT,
    watching FLOAT,
    completed FLOAT,
    on_hold FLOAT,
    dropped FLOAT,
    plan_to_watch FLOAT,
    total_entries FLOAT,
    rewatched FLOAT,
    episodes_watched FLOAT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = ',',
    "quoteChar"     = '\"',
    "escapeChar"    = '\\'
)
TBLPROPERTIES ("skip.header.line.count"="1")
LOCATION '/dataset/users';