-- Create Objects to test Spectrum User Defined Data Handling.

--0- in Glue create metadata catalog databse "spectrum_db_uddh"

--1- Create the External Schema

create external schema schema_spectrum_uddh
from data catalog
database 'spectrum_db_uddh'
iam_role 'arn:aws:iam::YourAccount:role/YourRole'
create external database if not exists;

--2- Create the external Table 

CREATE EXTERNAL TABLE schema_spectrum_uddh.soccer_league
(
  league_rank smallint,
  prev_rank   smallint,
  club_name   varchar(15),
  league_name varchar(20),
  league_off  decimal(6,2),
  league_def  decimal(6,2),
  league_spi  decimal(6,2),
  league_nspi smallint
)
ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\n\l'
stored as textfile
LOCATION 's3://uddh-soccer/league/'
table properties ('skip.header.line.count'='1','data_cleansing_enabled'='true');