Materiál k cvičení DBM1 týden #3, sezona 2023/2024

Ukládání dat do databáze

Ukázková data

Tento týden budou použita data z projektu Stormgate World, který sleduje odehrané hry v online hře Stormgate a sbírá různé statistiky.

📄 Datový zdroj: matches.csv

Data jsou v CSV formátu, což je běžný formát pro ukládání tabulkových dat. Jedna řádka odpovídá jednomu záznamu (hře). První řádka definuje názvy sloupců.

Načtení dat ze souboru do databáze

V předchozím cvičení se pracovalo s již vytvořenou ukázkovou databází a pouze se četl její obsah. Dnes bude ukázáno, jak přečíst data (z CSV formátu) a vložit je vhodným způsobem do databáze.

Vytvoření a naplnění tabulky přímo z CSV

Nejjednodušší variantou je v databázi vytvořit tabulku s identickou strukturou jako má CSV soubor. Toho lze docílit voláním příkazu CREATE TABLE foo AS SELECT * FROM read_csv_auto(bar.csv) využívajícím funkci read_csv_auto podporovanou v DuckDB.

※ Funkce read_csv_auto není standardní součástí SQL.


  

import java.sql.*;

...

public static void main(String[] args) throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:duckdb:ex.db");
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("CREATE TABLE matches AS SELECT * FROM read_csv_auto('data/matches.csv');");
    conn.close();
}

Výše uvedený kód vytvoří v databázi ex.db tabulku matches s identickou strukturou jako má soubor data/matches.csv.

Struktura tabulky a datové typy atributů jsou odvozeny automaticky.

Možné problémy:

※ Všimněte si použití stmt.executeUpdate() pro DDL SQL příkaz, což je změna oproti Select SQL dotazu, u kterého se volá stmt.executeQuery().

※ Nezapomeňte zavolat conn.close() ke správnému uzavření spojení.

Q1: Zkontrolujte, že obsah tabulky matches odpovídá obsahu souboru matches.csv.

Vytvoření tabulky s explicitním definováním struktury

Pokud potřebujeme větší kontrolu nad strukturou tabulky, můžeme explicitně definovat názvy sloupců a jejich datové typy klasickým zavoláním CREATE TABLE, jak znáte z KIV/DB1.

Vytvořme tabulku players obsahující 2 atributy player_id a name, které lze extrahovat z CSV souboru. Příslušný SQL DDL příkaz je:


  

CREATE TABLE players (
    id VARCHAR,
    name VARCHAR
);

Substitucí do předchozího Java kódu lze tabulku triviálně vytvořit.

Vložení dat po jednotlivých záznamech do existující tabulky

Pokud máme již vytvořenou tabulku, chceme do ní vkládat data po jednotlivých záznamech. Řešení bude tedy potřebovat načíst CSV soubor, iterovat po jednotlivých řádcích a volat vhodný SQL DML příkaz pro vložení záznamu.


  

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;

...

public static void main(String[] args) throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:duckdb:ex.db");

    try{
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("CREATE TABLE players (id VARCHAR, name VARCHAR);");
    } catch (SQLException e) {
        e.printStackTrace();
    }

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO players (id, name) VALUES (?, ?)");

    try{
        Reader reader = Files.newBufferedReader(Paths.get("data/matches.csv"));
        CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT.withFirstRecordAsHeader());
        for (CSVRecord record : csvParser) {
            String id = record.get("player1_id");
            String name = record.get("player1_name");
    
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
    
            System.out.println("Inserted: " + id + ", " + name);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    conn.close();
}

V kódu je nejprve založeno spojení k ex.db. Volání pro vytvoření tabulky players je obaleno try-catch blokem, protože při druhém spuštění by došlo k chybě, že tabulka již existuje.

Následně je vytvořen PreparedStatement obsahující šablonu SQL příkazu pro vložení záznamu. Tento příkaz bude opakovaně volán pro každý záznam v CSV souboru. Všimněte si parametrů ? v příkazu, které jsou později programově nahrazeny hodnotami z proměnných.

※ PreparedStatement je parametrizovaný dotaz vhodný pro opakované volání. Zároveň je to bezpečnější přístup než spojovat textové řetězce bez kontroly vstupu. 📚 SQL Injection

Zpracování vstupního souboru je opět obaleno v try-catch bloku, protože může dojít k chybě při čtení souboru. Soubor zpracovává knihovna Apache Commons CSV, kterou je potřeba přidat do projektu.


  

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-csv</artifactId>
  <version>1.10.0</version>
</dependency>

※ Ne každý CSV soubor dodržuje konvenci, že , se používá jako oddělovač. V některých případech může být použit ; nebo jiný znak. V takovém případě je potřeba upravit volání CSVFormat.DEFAULT.withFirstRecordAsHeader().

Z řádky jsou vybrány informace o identifikátoru a jménu hráče, které jsou následně vloženy do tabulky players. Do konzole je vypsáno potvrzení.

Q2: Kolik různých hráčů je ve vstupních datech? Kolik záznamů bylo vloženo do tabulky players? Jakým způsobem by bylo možné zajistit, aby se do tabulky players vložil každý hráč pouze jednou?

Q3: Upravte řešení, aby se do tabulky načítala data i o soupeři (druhém hráči) každého zápasu.

Nápověda V datech jsou sloupec `player1_id` a `player1_name`, pro prvního hráče. Analogicky jsou zde i sloupce `player2_id` a `player2_name` pro druhého hráče.

Vytvoření tabulky odvozením z existující tabulky

Formálně jen komentuji, že by se bylo možné vyhnout zpracování v Java a tabulky players vytvořit přímo v SQL pomocí CREATE TABLE ... AS SELECT ... FROM matches. Jelikož cvičení jsou zaměřena na práci s Java, tento přístup není dále rozveden.