Querying ATP matches using DuckDB

queries.sql

-- Fails because of weird date
CREATE TABLE players AS 
select *
from 'atp_players.csv';

-- all varchar
CREATE TABLE players1 AS 
select *
from read_csv_auto('atp_players.csv', ALL_VARCHAR=TRUE);

DESCRIBE players1;

SELECT count(*) FROM players1;

-- sample all rows
CREATE TABLE players2 AS 
select *
from read_csv_auto('atp_players.csv', SAMPLE_SIZE=-1);

DESCRIBE players2;

SELECT count(*) FROM players2;

-- manual schema
CREATE TABLE players3 AS 
SELECT * FROM read_csv('atp_players.csv', 
header=True, 
columns={'player_id': 'INTEGER', 'name_first': 'VARCHAR', 'name_last': 'VARCHAR', 'hand': 'VARCHAR', 'dob': 'VARCHAR', 'ioc': 'VARCHAR', 'height': 'INTEGER', 'wikidata_id': 'VARCHAR'
});

DESCRIBE players3;

SELECT count(*) FROM players3;

-- ignore errors
CREATE TABLE players4 AS 
select *
from read_csv_auto('atp_players.csv', IGNORE_ERRORS=TRUE);

DESCRIBE players4;

SELECT count(*) FROM players4;