네 개의 문으로
들어온다

제1장에서는 간단한 DuckDB 데이터베이스를 만들고 테이블을 적재하는 법을 보았다. 그러나 현실 세계의 데이터는 CSV, 엑셀, Parquet, 데이터베이스 서버처럼 서로 다른 원천과 파일 형식에서 온다. 제2장은 먼저 DuckDB 데이터베이스를 만드는 여러 방법을 익히고, 이어서 다양한 데이터 원천으로 그것을 채우는 법을 익힌다. 장을 마칠 때에는 각 데이터 원천을 다루는 방법과, 그것들을 상대할 때의 요령과 함정이 정리된다.

제1장이 구조에 관한 장이었다면 제2장은 반입에 관한 장이다. 아무리 잘 지은 건물이라도 물건이 들어오지 않으면 창고에 지나지 않는다. 그리고 물건을 들이는 일에는 늘 잡음이 따른다. 머리글이 없는 워크시트, 데이터처럼 생긴 헤더, 열 개수가 어긋난 표, 비밀번호가 걸린 파일. 이 장의 가치는 매끄러운 성공담이 아니라 이런 잡음의 목록에 있다.

DuckDB 데이터베이스를 만드는 가장 간단한 방법은 duckdb 모듈의 connect() 함수를 쓰는 것이다.

import duckdb

conn = duckdb.connect()

connect() 함수는 DuckDBPyConnection 객체를 돌려준다. 기본적으로 이 문장은 수정 가능한 메모리 데이터베이스를 연다. 아래와 같이 명시적으로 쓴 것과 같다.

conn = duckdb.connect(':memory:')

저장 장치에 남는 DuckDB 데이터베이스를 만들려면 database 인수에 데이터베이스 이름을 준다. 예컨대 mydb.duckdb다. 파일 이름의 확장자는 무엇이든 원하는 대로 쓸 수 있다.

conn = duckdb.connect(database = 'mydb.duckdb', read_only = False)

세 가지 선택지는 성격이 뚜렷이 갈린다. 임시 분석에는 메모리, 세션을 넘겨 남길 것이 있으면 파일, 여러 프로세스가 같은 파일을 동시에 읽어야 하면 읽기 전용이다. 터를 닦는 방식이 곧 그 집의 수명을 정한다.

DuckDB는 여러 데이터 원천과 파일 형식을 지원한다. 이 장에서 다루는 것은 CSV 파일, Parquet 파일, 엑셀 파일, MySQL 데이터베이스 네 가지다.

가장 흔한 데이터 원천 파일 형식 하나가 CSV 파일이다. 표 형식 데이터를 저장하고 교환하는 흔하고 단순한 방식이다. CSV 파일의 성질은 셋이다.

답사 자료 — 2015 Flight Delays and Cancellations

이 장에서 쓰는 자료는 Kaggle의 2015 Flight Delays and Cancellations 데이터셋이다. 라이선스는 CC0: Public Domain이다. 세 개의 CSV 파일로 이루어진다.

• airlines.csv 미국 항공사 목록
• airports.csv 미국 공항 목록
• flights.csv 2015년 여러 항공사의 항공편 상세 내역

데이터셋을 내려받았으면 개별 파일을 풀어 Jupyter Notebook과 같은 폴더에 둔다. 이 절에서는 CSV 파일을 DuckDB 데이터베이스로 적재하는 두 가지 방법, 곧 SQL 질의 방법과 register 방법을 익히고, DuckDB에서 CSV로 내보내는 일까지 다룬다.

SQL 질의 방법

첫째 방법은 SQL 질의 방법이다. CREATE TABLE 문과 SELECT FROM 문을 함께 써서 연결 객체의 execute() 메서드로 CSV 파일을 적재한다.

import duckdb

conn = duckdb.connect()

conn.execute('''
    CREATE TABLE flights
    as
    SELECT
        *
    FROM read_csv_auto('flights.csv')
''')

read_csv_auto() 함수는 CSV 파일을 적재하는 가장 간단한 방법이다. CSV 헤더의 올바른 구성을 알아내려 시도하고, 파일 내 열의 데이터 유형을 자동으로 추론한다. CSV 파일에 헤더가 있으면 그 헤더에서 찾은 이름으로 열 이름을 정한다. 없으면 column0, column1, column2 같은 기본 이름이 붙는다.

execute() 메서드는 DuckDBPyConnection 객체를 돌려준다. 적재된 결과를 살펴보려면 그 객체에 df() 메서드를 불러 pandas DataFrame을 받는다.

import duckdb

conn = duckdb.connect()

conn.execute('''
    CREATE TABLE flights
    as
    SELECT
        *
    FROM read_csv_auto('flights.csv')
''').df()

이 코드에서 DuckDB는 메모리 저장을 쓰도록 구성되어 있고 flights 테이블은 메모리에 만들어진다. 그런데 CSV 파일을 DuckDB로 적재했다 해도, 나중에 질의하거나 가공하기 전까지 DuckDB는 모든 것을 메모리에 완전히 올리지 않는다. 테이블 자체가 디스크가 아니라 메모리에 저장되어 있더라도, 필요한 시점에만 데이터를 메모리로 들여와 메모리 사용을 최적화한다. 이것이 지연 적재(lazy loading)다. df() 메서드를 부르는 순간 DuckDB는 테이블의 내용을 pandas DataFrame으로 적재한다. 따라서 실무에서는 질의의 최종 결과를 살펴볼 때만 df()를 써야 한다.

여기가 이 절에서 가장 중요한 한 문장이다. df()는 결과를 보여 주는 창이 아니라, 메모리로 실체화하라는 명령이다. 창을 열 때마다 짐이 들어온다는 사실을 잊으면 제1장에서 애써 아낀 4GB가 되돌아온다.

축약 — SELECT 절도 함수도 생략할 수 있다

SELECT 절은 선택적이며 read_csv_auto() 함수도 그렇다. CSV 파일에서 모든 열을 가져오려면 둘 다 없이 SQL 문을 다시 쓰면 된다.

conn.execute('''
    CREATE TABLE flights
    as
    FROM 'flights.csv'
''').df()

conn.execute('''
    DROP TABLE IF EXISTS flights;
    CREATE TABLE flights
    as
    FROM 'flights.csv'
''').df()

conn.execute('''
    CREATE OR REPLACE TABLE flights
    as
    FROM 'flights.csv'
''').df()

CSV 파일이 이 파일처럼 크다면 일부만 적재하고 싶을 수 있다. 예컨대 LIMIT 절로 처음 1,000행만 적재한다.

conn.execute('''
    DROP TABLE IF EXISTS flights;
    CREATE TABLE flights
    as
    FROM read_csv_auto('flights.csv')
    LIMIT 1000
''').df()

데이터베이스에 만들어진 테이블을 확인하려면 SHOW TABLES 질의를 쓴다.

display(conn.execute('SHOW TABLES').df())

flights 테이블의 내용을 보려면 execute() 메서드에 SELECT 문을 준다.

display(conn.execute('SELECT * FROM flights').df())

COPY 방식 — 손으로 테이블을 짓고 붓는다

CSV 파일을 적재하는 또 한 가지 방법은 테이블을 직접 만들고 COPY 문으로 데이터를 붓는 것이다.

conn.execute('''
    CREATE TABLE airports(
        IATA_CODE VARCHAR, AIRPORT VARCHAR, CITY VARCHAR,
        STATE VARCHAR, COUNTRY VARCHAR, LATITUDE VARCHAR,
        LONGITUDE VARCHAR);
    COPY airports FROM 'airports.csv' (AUTO_DETECT true);
''')

display(conn.execute('SELECT * FROM airports').df())

이 방법을 쓸 때는 CSV 파일의 열 개수와 테이블의 열 개수가 일치해야 한다. 또한 각 열의 내용이 테이블에 지정한 열 유형으로 변환될 수 있어야 한다. 그렇지 않으면 오류가 발생한다.

대안은 read_csv() 함수의 names 매개변수에 열 이름을 지정하는 것이다.

conn.execute('''
    DROP TABLE IF EXISTS airports;
    CREATE TABLE airports
    AS
    FROM
    read_csv('airports.csv',
        names=['IATA_CODE', 'AIRPORT', 'CITY',
               'STATE', 'COUNTRY', 'LATITUDE',
               'LONGITUDE'
    ])
''')

result = conn.execute('''
    SELECT COUNT(*) AS column_count
    FROM information_schema.columns
    WHERE table_name = 'airports';
''').fetchall()

CSV 파일의 실제 데이터 유형과 무관하게 모든 열을 문자열 유형으로 다루고자 한다면, read_csv() 함수의 all_varchar 매개변수를 true로 지정한다.

conn.execute('''
    DROP TABLE IF EXISTS airports;
    CREATE TABLE airports
    AS
    FROM read_csv('airports.csv', all_varchar=true)
''')

register() 방법 — 등록만 하고 복사하지 않는다

CSV 파일을 DuckDB로 적재하는 또 한 가지 방법은 연결 객체의 register() 메서드다. register()는 CSV 파일이나 그 밖의 외부 데이터 원천을 메모리 상의 가상 테이블로 적재하게 해 준다. DuckDB 테이블을 명시적으로 만들거나 데이터를 복사해 넣을 필요가 없다. 유연성과 외부 데이터에 대한 임시 접근이 필요한 상황에서 유용하다.

다음 코드는 SELECT 문과 read_csv() 함수를 함께 써서 airlines.csv 파일의 내용을 적재하고 pandas DataFrame으로 돌려받는다.

airlines = conn.execute('''
    SELECT
        *
    FROM read_csv('airlines.csv',
                  Header = True,
                  Columns = {'IATA_CODE': 'VARCHA…
''').df()
airlines

airlines = conn.execute('''
    SELECT
        *
    FROM read_csv_auto('airlines.csv')
''').df()

airlines

airlines DataFrame이 적재되었으면 register() 메서드로 그 테이블을 DuckDB 데이터베이스에 연결해야 한다.

conn.register("airlines", airlines)

airlines 테이블이 올바르게 적재되었는지는 SELECT 문으로 확인한다. 출력은 도판 2-6과 같다.

연결 객체로 CSV 파일을 적재하고 싶지 않다면, pandas의 read_csv() 함수로 CSV를 읽은 뒤 register() 메서드를 쓰는 더 전통적인 방식을 택할 수도 있다.

import pandas as pd

# load the CSV using pandas
df_airlines = pd.read_csv("airlines.csv")

# associate the DataFrame with the DuckDB database
conn.register("airlines", df_airlines)

CSV로 내보내기

여기까지는 CSV 파일을 DuckDB 데이터베이스로 들이는 이야기였다. 반대로 DuckDB 데이터베이스의 데이터를 CSV 파일로 내보내려면 COPY 문을 쓴다.

conn.execute('''
    COPY
        (SELECT IATA_CODE, LATITUDE, LONGITUDE FROM airports)
    TO
    'airports_location.csv' WITH (HEADER 1, DELIM…
''')

HEADER 1 인수는 CSV 파일이 헤더 행, 곧 열 이름을 포함해야 한다는 뜻이다. 이 코드는 세 개의 열을 가진 airports_location.csv 파일을 만든다.

IATA_CODE,LATITUDE,LONGITUDE
ABE,40.65236,-75.4404
ABI,32.41132,-99.6819
ABQ,35.04022,-106.60919
ABR,45.44906,-98.42183
ABY,31.53552,-84.19447
...

데이터를 DuckDB로 전혀 들이지 않은 채 파일의 일부만 다른 파일로 복사하려면, 파일에서 직접 데이터를 읽으며 복사할 행 수를 지정하면 된다.

conn.execute('''
    COPY
        (SELECT
            IATA_CODE, LATITUDE, LONGITUDE
         FROM 'airports.csv'
         LIMIT 10)
    TO
    'airports_location.csv' WITH (HEADER 1, DELIM…
''')

연결을 다 썼으면 닫는 것을 잊지 않는다.

conn.close()

데이터 과학자들 사이에서 인기를 얻고 있는 또 하나의 파일 형식이 Parquet이다. Parquet, 곧 Apache Parquet은 복잡한 데이터의 빠른 처리를 지원하도록 설계된 파일 형식이다. Apache Hadoop 라이선스를 따르는 오픈소스 형식이며 대부분의 Hadoop 처리 프레임워크와 호환된다. Parquet은 자기 서술적(self-describing)이다. 스키마와 구조를 포함한 메타데이터가 각 파일 안에 내장된다. 더 중요한 점은, Parquet이 데이터를 행이 아니라 열로 저장한다는 것이다.

세 개의 열을 가진 DataFrame을 놓고 생각해 본다. 이 DataFrame을 CSV 파일로 저장하면 행 기반 저장을 쓴다. CSV 파일이 DataFrame으로 적재될 때 각 행이 하나씩 적재되며, 각 행은 서로 다른 세 가지 데이터 유형을 담는다. 반면 Parquet은 열 기반 저장을 쓴다. 각 열의 데이터가 특정 데이터 유형을 갖는 하나의 열로 정리된다.

Parquet 파일 적재

이 절의 예에서는 Parquet 파일이 따로 없으므로, 가지고 있는 CSV 파일로 하나 만든다. airlines.csv를 pandas DataFrame으로 적재한 뒤 fastparquet 엔진으로 Parquet 형식으로 저장한다. fastparquet은 Parquet 파일을 읽고 쓰는 빠르고 효율적인 엔진을 제공하는 오픈소스 파이썬 라이브러리다. 쓰려면 pip으로 설치한다.

$ pip install fastparquet

import pandas as pd

df_airports = pd.read_csv("airports.csv")
df_airports.to_parquet('airports.parquet', engine='fastparquet')

Parquet 파일을 DuckDB 데이터베이스로 적재하려면 read_parquet() 함수를 쓴다.

import duckdb

conn = duckdb.connect()
conn.execute('''
    CREATE TABLE airports
    as
    SELECT * FROM read_parquet('airports.parquet')
    LIMIT 100
''')

마지막 100행을 적재하려면 ORDER BY 1 DESC 문으로 첫 열을 기준으로 내림차순 정렬한 뒤 처음 100행을 적재한다. 결과적으로 Parquet 파일의 마지막 100행을 가져오는 것이 된다. 정렬을 뒤집어 앞에서 자른다는 이 발상은 소박하지만, 파일 끝을 직접 겨눌 수 없는 상황에서 쓸 만한 우회로다.

conn.execute('''
    INSERT INTO airports
    SELECT * FROM read_parquet('airports.parquet')
    ORDER BY 1 DESC
    LIMIT 100
''')

display(conn.execute('SELECT * FROM airports').df())

Parquet 파일을 DuckDB 데이터베이스의 기존 테이블로 적재하려면 COPY FROM 문을 쓴다.

conn.execute('''
    COPY airports
    FROM 'airports.parquet' (FORMAT PARQUET);
''')

Parquet으로 내보내기

DuckDB의 테이블을 Parquet 파일로 내보내려면 다음 질의를 쓴다.

conn.execute('''
    COPY airports
    TO
    'airports_all.parquet' (FORMAT PARQUET);
''')

일부 행만 내보내려면 LIMIT을 쓴다.

conn.execute('''
    COPY
        (SELECT * FROM airports LIMIT 100)
    TO
    'airports_100.parquet' (FORMAT PARQUET);
''')

DuckDB에서 Parquet으로 데이터를 내보내는 일은 효율적인 저장, 빠른 질의, 빅데이터 도구와의 상호운용성이 요구되는 상황에서 유용하다. 열 지향 데이터에 최적화된 구조와 압축 지원 덕분에, Parquet은 클라우드 저장, 데이터 레이크, 분석 작업 부하, 기계학습 파이프라인에서 으레 선택되는 형식이다.

엑셀은 모든 산업과 여러 직종에서 널리 쓰이는 다재다능한 스프레드시트 응용이다. 프로그래머든 아니든 어떤 방식으로든 엑셀을 써 본 사람이 대다수일 것이다.

이 절의 실습에는 두 개의 워크시트, airports와 airlines를 가진 엑셀 파일을 쓴다. airports 워크시트의 내용은 airports.csv에서, airlines 워크시트의 내용은 airlines.csv에서 가져온 것이다.

엑셀 스프레드시트에서 데이터를 적재하려면 spatial 확장을 써야 한다. 이 확장은 DuckDB에서 지리공간 데이터 처리를 지원한다. 지리공간 확장으로 스프레드시트를 읽는다는 것은 처음 보면 뜻밖의 조합인데, DuckDB가 엑셀을 읽는 데 GDAL/OGR 라이브러리를 쓰기 때문이다. 이 우회의 흔적은 뒤에 나오는 환경 변수 이름에도 그대로 남아 있다.

먼저 엑셀 워크시트를 DuckDB 데이터베이스로 적재한다. 다음 코드는 airports 워크시트를 airports라는 DuckDB 테이블로 적재한다.

import duckdb

conn = duckdb.connect()

conn.execute('INSTALL spatial')
conn.execute('LOAD spatial')
conn.execute('''
    CREATE TABLE airports
    as
    SELECT * FROM st_read('airports_and_airlines.xlsx', layer='airports')
''')
display(conn.execute('SELECT * FROM airports').df())

설명할 점이 몇 가지 있다.

• 확장의 설치 시점 엑셀 데이터를 들이기 전에 spatial 확장을 설치하고 적재해야 한다. 이 일은 한 번만 하면 된다. DuckDB를 제거할 때까지 확장은 기억된다.
• st_read() 엑셀 스프레드시트에서 읽어 오는 함수다. 적재할 워크시트는 layer 인수로 지정한다.
• 제약 DuckDB는 현재 비밀번호로 보호된 엑셀 스프레드시트를 읽지 못한다. 파일에 비밀번호가 걸려 있다면 먼저 해제해야 한다.

머리글을 다루는 세 가지 태도 — OGR_XLSX_HEADERS

워크시트 첫 행의 값이 자동으로 감지되어 테이블의 열 이름으로 쓰인다. 다만 이 동작은 OGR_XLSX_HEADERS 환경 변수로 통제할 수 있다. DuckDB가 엑셀 파일을 읽는 데 쓰는 GDAL/OGR 라이브러리의 일부다.

import os

os.environ['OGR_XLSX_HEADERS'] = 'DISABLE'   # 첫 행을 머리글로 쓰지 않는다
os.environ['OGR_XLSX_HEADERS'] = 'FORCE'     # 첫 행을 머리글로 강제한다
os.environ['OGR_XLSX_HEADERS'] = 'AUTO'      # 기본값. 드라이버가 판단한다

이제 airlines 워크시트를 DuckDB로 적재해 본다.

conn.execute('''
    CREATE TABLE airlines
    AS
    SELECT * FROM st_read('airports_and_airlines.xlsx', layer='airlines')
''')
display(conn.execute('SELECT * FROM airlines').df())

테이블에 원하는 열 이름을 확실히 주려면, 먼저 원하는 열 이름과 데이터 유형으로 테이블을 만들고 INSERT 문으로 엑셀 스프레드시트의 데이터를 적재한다.

conn = duckdb.connect()

conn.execute('INSTALL spatial')
conn.execute('LOAD spatial')
conn.execute('''
    CREATE TABLE airlines (
        IATA_CODE STRING,
        AIRLINES STRING
    );
    INSERT INTO airlines
    SELECT * FROM st_read('airports_and_airlines.xlsx', layer='airlines')
''')
display(conn.execute('SELECT * FROM airlines').df())

엑셀 데이터를 적재할 때 쓸 수 있는 또 하나의 환경 변수는 OGR_XLSX_FIELD_TYPES다. 기본적으로 엑셀 스프레드시트를 해석할 때 DuckDB는 파일의 데이터 유형을 자동으로 감지한다. 모든 데이터 유형을 문자열로 강제하려면 이 환경 변수를 STRING으로 설정한다.

os.environ['OGR_XLSX_FIELD_TYPES'] = 'STRING'

엑셀로 내보내기

다른 파일 형식과 마찬가지로 DuckDB 테이블을 엑셀 형식으로 내보낼 수 있다.

conn.execute('''
    COPY airlines
    TO 'airlines.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx')
''')

이 장에서 마지막으로 다루는 것은 MySQL이다. 원천 데이터가 MySQL 같은 데이터베이스 서버에 저장되어 있는 경우가 매우 흔하므로, MySQL에 있는 데이터를 DuckDB로 적재할 수 있으면 유용하다.

이 절의 예는 다음을 전제한다.

• 서버 컴퓨터에 MySQL 서버 인스턴스가 실행 중이다.
• 데이터 My_DB라는 데이터베이스가 있고 그 안에 airlines라는 테이블 하나가 있다.
• 계정 MySQL 서버에 user1이라는 계정이 있고 비밀번호는 “password”다. 이 계정은 My_DB 데이터베이스와 그 테이블에 접근할 권한을 가진다.

수동 방식 — 여섯 걸음

MySQL의 테이블을 DuckDB로 적재하려면 다음 단계를 밟는다.

• 1 DuckDB 연결을 만든다.
• 2 MySQL 연결을 만든다.
• 3 MySQL 서버에서 데이터를 가져온다.
• 4 MySQL 테이블과 같은 스키마로 DuckDB 데이터베이스에 테이블을 만든다.
• 5 MySQL에서 얻은 각 행을 순회하며 DuckDB 테이블에 삽입한다.
• 6 MySQL과 DuckDB 연결을 닫는다.

파이썬으로 MySQL 데이터베이스에 접속하려면 mysql-connector-python 라이브러리를 설치해야 한다.

!pip install mysql-connector-python

import mysql.connector
import duckdb

# MySQL connection information
mysql_host     = 'localhost'
mysql_user     = 'user1'
mysql_password = 'password'
mysql_database = 'My_DB'
mysql_table    = 'airlines'

# create a DuckDB connection
duckdb_conn = duckdb.connect()

# connect to MySQL
mysql_conn = mysql.connector.connect(
    host = mysql_host,
    user = mysql_user,
    password = mysql_password,
    database = mysql_database
)

# create a cursor for MySQL
mysql_cursor = mysql_conn.cursor()

# query data from MySQL
mysql_query = f'SELECT * FROM {mysql_table}'
mysql_cursor.execute(mysql_query)

# create a DuckDB table with the same schema as MySQL
duckdb_create_table_query = \
    f'CREATE TABLE airlines (IATA_CODE VARCHAR(2…
duckdb_conn.execute(duckdb_create_table_query)

# get column names from MySQL result
mysql_columns = [column[0] for column in mysql_cursor.description]

# fetch data from MySQL and insert into DuckDB table
duckdb_insert_query = \
    f'INSERT INTO airlines VALUES ({", ".join(["…
for row in mysql_cursor.fetchall():
    duckdb_conn.execute(duckdb_insert_query, row)

# query the data in DuckDB
display(duckdb_conn.execute('SELECT * FROM airlines').df())

# close the MySQL and DuckDB connections
mysql_cursor.close()
mysql_conn.close()
duckdb_conn.close()

mysql 확장 방식 — 서버를 그대로 붙인다

대안으로 mysql 확장을 써서 MySQL의 데이터베이스에 있는 테이블을 DuckDB로 직접 적재할 수 있다. 여섯 걸음이 ATTACH 한 문장으로 줄어든다.

import duckdb

# create a DuckDB connection
conn = duckdb.connect()

# install and load the MySQL extension
conn.execute('INSTALL mysql')
conn.execute('LOAD mysql')

# define MySQL connection parameters
mysql_host     = 'localhost'
mysql_user     = 'user1'
mysql_password = 'password'
mysql_database = 'My_DB'
mysql_table    = 'airlines'
mysql_port     = 3306

# create a MySQL connection
mysql_connection = \
    f'mysql://{mysql_user}:{mysql_password}@{mysq…

# attach the MySQL database with authentication
attach_command = f'''
    ATTACH 'host={mysql_host}
    user={mysql_user}
    password={mysql_password}
    port={mysql_port}
    database={mysql_database}'
    AS mysqldb (TYPE MYSQL);
'''
conn.execute(attach_command)
conn.execute('USE mysqldb;')

display(conn.execute(f'''
    SELECT * FROM {mysql_table}
''').df())

display(conn.execute(f'''
    show tables
''').df())

# close the DuckDB connection
conn.close()

어느 쪽이 나은가

원저자가 제시하는 일반적 판단 기준은 다음과 같다.

데이터에 자주 접근하고 분석하는 대부분의 사용 사례에서는, 효율성과 사용의 편의 덕에 mysql 확장이 더 나은 선택이 될 것이다.