On-demand
Hands-on
Workshop

DROP TABLE IF EXISTS ex_games;


CREATE EXTERNAL TABLE IF NOT EXISTS ex_games 
(src TEXT) 
URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/' 
OBJECT_PATTERN = 'help_center_assets/firebolt_sample_dataset/games.json'
TYPE = (JSON PARSE_AS_TEXT = TRUE);


DROP TABLE IF EXISTS ex_levels;


CREATE EXTERNAL TABLE IF NOT EXISTS ex_levels (
  LevelID INTEGER,
  GameID INTEGER,
  Level INTEGER,
  Name TEXT,
  LevelType TEXT,
  NextLevel INTEGER NULL,
  MinPointsToPass INTEGER,
  MaxPoints INTEGER,
  NumberOfLaps INTEGER,
  MaxPlayers INTEGER,
  MinPlayers INTEGER,
  PointsPerLap REAL,
  MusicTrack TEXT,
  SceneDetails TEXT,
  MaxPlayTimeSeconds INTEGER,
  LevelIcon TEXT
) 
URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/' 
OBJECT_PATTERN = 'help_center_assets/firebolt_sample_dataset/levels.csv'
TYPE = (CSV SKIP_HEADER_ROWS = 1);


DROP TABLE IF EXISTS ex_players;


CREATE EXTERNAL TABLE IF NOT EXISTS ex_players 
(src TEXT) 
URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/' 
OBJECT_PATTERN = 'help_center_assets/firebolt_sample_dataset/players.json'
TYPE = (JSON PARSE_AS_TEXT = TRUE);


DROP TABLE IF EXISTS ex_tournaments;


CREATE EXTERNAL TABLE IF NOT EXISTS ex_tournaments (
  TournamentID INTEGER,
  Name TEXT,
  GameID INTEGER,
  TotalPrizeDollars INTEGER,
  StartDateTime TIMESTAMP,
  EndDateTime TIMESTAMP,
  RulesDefinition TEXT
) 
URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/' 
OBJECT_PATTERN = 'help_center_assets/firebolt_sample_dataset/tournaments.csv'
TYPE = (CSV SKIP_HEADER_ROWS = 1);


DROP TABLE IF EXISTS ex_playstats;


CREATE EXTERNAL TABLE IF NOT EXISTS ex_playstats (
  "GameID" INTEGER,
  "PlayerID" INTEGER,
  "Timestamp" TIMESTAMP,
  "SelectedCar" TEXT,
  "CurrentLevel" INTEGER,
  "CurrentSpeed" REAL,
  "CurrentPlayTime" BIGINT,
  "CurrentScore" BIGINT,
  "Event" TEXT,
  "ErrorCode" TEXT
) 
URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/' 
OBJECT_PATTERN = 'help_center_assets/firebolt_sample_dataset/playstats/*.parquet'
TYPE = (PARQUET);


DROP TABLE IF EXISTS ex_rankings;


CREATE EXTERNAL TABLE IF NOT EXISTS ex_rankings (
  "GameID" INTEGER,
  "PlayerID" INTEGER,
  "MaxLevel" INTEGER,
  "TotalScore" BIGINT,
  "PlaceWon" INTEGER
) 
URL = 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/' 
OBJECT_PATTERN = 'help_center_assets/firebolt_sample_dataset/rankings/*.parquet'
TYPE = (PARQUET);
	

DROP TABLE IF EXISTS games CASCADE;


CREATE DIMENSION TABLE IF NOT EXISTS games (
  GameID INTEGER,
  Title TEXT,
  Abbreviation TEXT,
  Series TEXT,
  Version NUMERIC(10, 2),
  GameDescription TEXT,
  Category TEXT,
  LaunchDate DATE,
  Author TEXT,
  SupportedPlatforms ARRAY (TEXT),
  GameConfiguration TEXT,
  SOURCE_FILE_NAME TEXT,
  SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX GameID,
Title;


DROP TABLE IF EXISTS levels CASCADE;


CREATE DIMENSION TABLE IF NOT EXISTS levels (
  LevelID INTEGER,
  GameID INTEGER,
  Level INTEGER,
  Name TEXT,
  LevelType TEXT,
  NextLevel INTEGER NULL,
  MinPointsToPass INTEGER,
  MaxPoints INTEGER,
  NumberOfLaps INTEGER,
  MaxPlayers INTEGER,
  MinPlayers INTEGER,
  PointsPerLap REAL,
  MusicTrack TEXT,
  SceneDetails TEXT,
  MaxPlayTimeSeconds INTEGER,
  LevelIcon BYTEA,
  SOURCE_FILE_NAME TEXT,
  SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX LevelID;


DROP TABLE IF EXISTS players CASCADE;


CREATE DIMENSION TABLE IF NOT EXISTS players (
  PlayerID INTEGER,
  Nickname TEXT,
  Email TEXT,
  AgeCategory TEXT,
  Platforms ARRAY (TEXT),
  RegisteredOn PGDATE,
  IsSubscribedToNewsletter BOOLEAN,
  InternalProbabilityToWin DOUBLE PRECISION,
  SOURCE_FILE_NAME TEXT,
  SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX PlayerID,
Nickname,
AgeCategory,
RegisteredOn;


DROP TABLE IF EXISTS tournaments CASCADE;


CREATE DIMENSION TABLE IF NOT EXISTS tournaments (
  TournamentID INTEGER,
  Name TEXT,
  GameID INTEGER,
  TotalPrizeDollars INTEGER,
  StartDateTime TIMESTAMPNTZ,
  EndDateTime TIMESTAMPNTZ,
  RulesDefinition TEXT,
  SOURCE_FILE_NAME TEXT,
  SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX TournamentID;


DROP TABLE IF EXISTS rankings CASCADE;


CREATE FACT TABLE IF NOT EXISTS rankings (
  GameID INTEGER,
  PlayerID INTEGER,
  MaxLevel INTEGER,
  TotalScore BIGINT,
  PlaceWon INTEGER,
  TournamentID INTEGER,
  SOURCE_FILE_NAME TEXT,
  SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX GameID,
TournamentID,
PlayerID;


DROP TABLE IF EXISTS playstats CASCADE;


CREATE FACT TABLE IF NOT EXISTS playstats (
  GameID INTEGER,
  PlayerID INTEGER,
  StatTime TIMESTAMPNTZ,
  SelectedCar TEXT,
  CurrentLevel INTEGER,
  CurrentSpeed REAL,
  CurrentPlayTime BIGINT,
  CurrentScore BIGINT,
  Event TEXT,
  ErrorCode TEXT,
  TournamentID INTEGER,
  SOURCE_FILE_NAME TEXT,
  SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX GameID,
TournamentID,
PlayerID,
StatTime;


DROP AGGREGATING INDEX IF EXISTS playstats_agg_idx;


CREATE AGGREGATING INDEX playstats_agg_idx ON playstats (
  TournamentID,
  SelectedCar,
  COUNT(DISTINCT PlayerID)
);


DROP AGGREGATING INDEX IF EXISTS playstats_agg_idx_errors;


CREATE AGGREGATING INDEX playstats_agg_idx_errors ON playstats (
TournamentID, 
ErrorCode, 
COUNT(ErrorCode)
);

DROP TABLE IF EXISTS players_200million;

CREATE DIMENSION TABLE IF NOT EXISTS players_200million (
  playerid integer,
  nickname text,
  email text,
  agecategory text,
  platforms array (text),
  registeredon pgdate,
  issubscribedtonewsletter boolean,
  internalprobabilitytowin double precision,
  source_file_name text,
  source_file_timestamp timestampntz
) PRIMARY INDEX PlayerID, Nickname, AgeCategory, RegisteredOn;

	

INSERT INTO games
SELECT JSON_EXTRACT(src, '/GameID', 'INT') AS GameID
	,JSON_EXTRACT(src, '/Title', 'TEXT') AS Title
	,JSON_EXTRACT(src, '/Abbreviation', 'TEXT') AS Abbreviation
	,JSON_EXTRACT(src, '/Series', 'TEXT') AS Series
	,JSON_EXTRACT(src, '/Version', 'FLOAT')::DECIMAL(10, 2) AS Version
	,JSON_EXTRACT(src, '/Description', 'TEXT') AS "Description"
	,JSON_EXTRACT(src, '/Category', 'TEXT') AS Category
	,JSON_EXTRACT(src, '/LaunchDate', 'TEXT')::DATE AS LaunchDate
	,JSON_EXTRACT(src, '/Author', 'TEXT') AS Author
	,NEST(JSON_EXTRACT(platforms_unnest, 'Name', 'TEXT')) AS SupportedPlatforms
	,JSON_EXTRACT_RAW(src, '/GameConfiguration') AS GameConfiguration
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM ex_games UNNEST(JSON_EXTRACT_ARRAY_RAW(src, '/SupportedPlatforms') AS platforms_unnest)
WHERE SOURCE_FILE_TIMESTAMP > (
		SELECT COALESCE(MAX(SOURCE_FILE_TIMESTAMP), '1980-01-01'::TIMESTAMP)
		FROM games
		)
GROUP BY src
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP;

SELECT COUNT(*)
FROM games;--1

INSERT INTO levels
SELECT LevelID
	,GameID
	,LEVEL
	,Name
	,LevelType
	,NextLevel
	,MinPointsToPass
	,MaxPoints
	,NumberOfLaps
	,MaxPlayers
	,MinPlayers
	,PointsPerLap
	,MusicTrack
	,replace(SceneDetails, '''', '"')
	,MaxPlayTimeSeconds
	,DECODE(REPLACE(LevelIcon, '"', ''), 'BASE64')
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM ex_levels
WHERE SOURCE_FILE_TIMESTAMP > (
		SELECT COALESCE(MAX(SOURCE_FILE_TIMESTAMP), '1980-01-01'::TIMESTAMP)
		FROM levels
		);

SELECT COUNT(*)
FROM levels;--10

INSERT INTO players
SELECT JSON_EXTRACT(src, '/playerID', 'INT') AS PlayerID
	,JSON_EXTRACT(src, '/details/nickname', 'TEXT') AS Nickname
	,JSON_EXTRACT(src, '/details/email', 'TEXT') AS Email
	,JSON_EXTRACT(src, '/details/ageCategory', 'TEXT') AS AgeCategory
	,JSON_EXTRACT(src, '/details/platforms', 'ARRAY(TEXT)') AS Platforms
	,JSON_EXTRACT(src, '/details/registeredOn', 'TEXT')::PGDATE AS RegisteredOn
	,
	--temporary until we have TEXT to BOOL casting
	CASE 
		WHEN LOWER(JSON_EXTRACT_RAW(src, '/details/isSubscribedToNewsletter')::TEXT) = 'true'
			THEN 1::BOOLEAN
		ELSE 0::BOOLEAN
		END AS IsSubscribedToNewsletter
	,JSON_EXTRACT_RAW(src, '/details/internalProbabilityToWin')::DOUBLE AS InternalProbabilityToWin
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM ex_players
WHERE SOURCE_FILE_TIMESTAMP > (
		SELECT COALESCE(MAX(SOURCE_FILE_TIMESTAMP), '1980-01-01'::TIMESTAMP)
		FROM players
		);

SELECT COUNT(*)
FROM players;--5,420

INSERT INTO tournaments
SELECT *
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM ex_tournaments
WHERE SOURCE_FILE_TIMESTAMP > (
		SELECT COALESCE(MAX(SOURCE_FILE_TIMESTAMP), '1980-01-01'::TIMESTAMP)
		FROM tournaments
		);

SELECT COUNT(*)
FROM tournaments;--157

INSERT INTO rankings
SELECT *
	,REPLACE(SPLIT('/', source_file_name) [4], 'TournamentID=', '')
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM ex_rankings
WHERE SOURCE_FILE_TIMESTAMP > (
		SELECT COALESCE(MAX(SOURCE_FILE_TIMESTAMP), '1980-01-01'::TIMESTAMP)
		FROM rankings
		);

SELECT COUNT(*)
FROM rankings;--49,298

INSERT INTO playstats
SELECT *
	,REPLACE(SPLIT('/', source_file_name) [4], 'TournamentID=', '')
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM ex_playstats
WHERE SOURCE_FILE_TIMESTAMP > (
		SELECT COALESCE(MAX(SOURCE_FILE_TIMESTAMP), '1980-01-01'::TIMESTAMP)
		FROM playstats
		);

SELECT COUNT(*)
FROM playstats;--1,036,270,144

INSERT INTO players_200million (
	playerid
	,nickname
	,email
	,agecategory
	,platforms
	,registeredon
	,issubscribedtonewsletter
	,internalprobabilitytowin
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
	)
SELECT playerid + (n - 1) * 5420
	,nickname
	,email
	,agecategory
	,platforms
	,registeredon
	,issubscribedtonewsletter
	,internalprobabilitytowin
	,SOURCE_FILE_NAME
	,SOURCE_FILE_TIMESTAMP
FROM players
CROSS JOIN (
	SELECT n
	FROM GENERATE_SERIES(1, 35000, 1) s(n)
	);