opening_app_games_ddl.sql

CREATE EXTERNAL TABLE `games`(
  `event` string COMMENT 'from deserializer', 
  `site` string COMMENT 'from deserializer', 
  `white` string COMMENT 'from deserializer', 
  `black` string COMMENT 'from deserializer', 
  `result` string COMMENT 'from deserializer', 
  `utc_date` string COMMENT 'from deserializer', 
  `utc_time` string COMMENT 'from deserializer', 
  `white_elo` int COMMENT 'from deserializer', 
  `black_elo` int COMMENT 'from deserializer', 
  `white_rating_diff` int COMMENT 'from deserializer', 
  `black_rating_diff` int COMMENT 'from deserializer', 
  `eco` string COMMENT 'from deserializer', 
  `opening` string COMMENT 'from deserializer', 
  `time_control` string COMMENT 'from deserializer', 
  `termination` string COMMENT 'from deserializer', 
  `moves` string COMMENT 'from deserializer')
PARTITIONED BY ( 
  `extracted_at` string)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
  'paths'='black,black_elo,black_rating_diff,eco,event,moves,opening,result,site,termination,time_control,utc_date,utc_time,white,white_elo,white_rating_diff') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://chess-data-lake-opening/app/games/'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='opening_crawler', 
  'averageRecordSize'='399', 
  'classification'='json', 
  'compressionType'='none', 
  'objectCount'='15', 
  'partition_filtering.enabled'='true', 
  'recordCount'='8594', 
  'sizeKey'='3441838', 
  'typeOfData'='file')