Pandas 대신 SQL로 분석해보자 with DuckDB
DuckDB를 활용하여 SQL로 데이터 분석하는 법을 알아본다.
보통 데이터 분석은 Pandas로 시작한다. 하지만 데이터 크기가 커질수록 Pandas의 한계는 빠르게 느껴진다.
Single thread로 동작하기 때문에 성능이 느리고 메모리도 불필요하게 많이 사용한다.
이를 극복하기 위해 dask, modin, vaex, polars 등이 있지만 돌이켜보면 잘 사용하지 않았다.
실제 대량의 데이터는 Athena, BigQuery 등 SQL로 데이터 처리가 가능한 시스템에서 처리하기 때문인 것 같다.
DuckDB
DuckDB는 in-process SQL OLAP DBMS이라고 정의한다.
in-process란 의미는 in memory 분석 뒤에 데이터를 지우기 때문인 것 같다. .db파일로 저장해서 데이터를 관리할 수도 있다.
설치도 매우 간단하여 DB에 대한 큰 이해없이도 SQL 공부할 때 사용해도 괜찮을 듯 하다.
columnar-vectorized query execution engine을 사용하여 RDBMS보다 분석 성능이 더 빠르다고 한다. multi-core Parallel processing을 지원한다.
DuckDB에 대한 벤치마크는 다음 링크에서 확인할 수 있다.
https://duckdblabs.github.io/db-benchmark/
참고: pandas와 polars와의 비교 글
https://motherduck.com/blog/duckdb-versus-pandas-versus-polars/
아래는 성능 관련해서 알아둘 기본 설명이 되어있다.
https://duckdb.org/docs/guides/performance/environment.html
메모리 커넥션을 사용하더라도 연산 중 메모리를 초과하게 되면 로컬 디스크를 사용하여 계산한다.
temp_directory(기본값 .tmp )를 사용하며 max_temp_directory로 디스크 사이즈 제한도 둘 수 있다.
Tuning Workloads - Spilling to Disk
DubckDB 기본
설치는
pip install duckdb
duckdb는 r/w권한은 하나의 Process에서 접속만 가능하고 read_only로만 다중접속이 가능하다.
(r/w 또는 r/o 접속이 존재한 경우, 다른 process에서 r/w로는 접속할 수 없다. LOCK 상태)
import duckdb
# MEMORY 내에서만 활용
conn = duckdb.connect()
# 데이터를 저장
conn = duckdb.connect(database='db.duckdb', read_only=True)
# read only
conn = duckdb.connect(database='db.duckdb', read_only=True)
# process 종료
conn.close()
기본 사용법은 conn.sql( ... ) 안에 쿼리문을 사용하면 된다.
참고로, 컬럼이 많을 경우 EXCLUDE를 사용해서 컬럼을 빼는 기능을 사용할 수 있다.
res = conn.sql(f"""
SELECT * EXCLUDE(a, b)
FROM
WHERE
GROUP BY
ORDER BY
""")
# 리스트로 결과 저장
res_list = res.fetchall()
# 변수를 그대로 테이블처럼 사용가능하다.
conn.sql('SELECT * FROM res')
# pandas dataframe으로
res_df = res.df()
데이터 로딩
CSV, Parquet, JSON 등 여러 파일을 지원한다.
conn.sql(
f"""
CREATE TABLE new_table AS
SELECT * FROM read_csv_auto('/Users/TEST.csv', header = true);
"""
)
여러 파일을 읽을 수도 있다. ( 성능도 빠르다고 함 ) Reading Multiple Files
f"""
CREATE TABLE new_table AS
SELECT * FROM read_csv_auto([
'/Users/TEST1.csv',
'/Users/TEST2.csv']
, header = true);
"""
glob 패턴을 쉽게 활용할 수도 있다.
f"""
CREATE TABLE new_table AS
SELECT * FROM read_csv_auto([
'/Users/*.csv']
, header = true);
"""
엑셀 파일 읽기
엑셀 파일을 읽거나 저장할 때는 spatial extension을 설치해야 한다.
참고: Excel Import
conn.sql(f"""
INSTALL spatial;
LOAD spatial;
CREATE TABLE new_table AS
SELECT * FROM st_read('test_excel.xlsx');
)
참고: Pandas의 경우, 이런 식으로 각 데이터프레임을 합쳐주어야 한다.
import pandas as pd
import glob
files = glob.glob('/Users/*.csv')
df = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)
타 데이터프레임과 호환
DuckDB의 가장 큰 장점은 Pandas, Delta Table, Polars, Vaex의 데이터 프레임과 호환이 된다. Apache Arrow 포맷을 따르기 때문이다.
pandas의 경우, 데이터프레임 변수 이름을 그대로 테이블명으로 사용할 수 있다.
다른 데이터 프레임은 arrow_table로 변환하면 테이블명으로 사용할 수 있다.
df = pd.read_csv( ... )
conn.sql(
f"""
SELECT * FROM df LIMIT 10
"""
)
다양한 기능
https://duckdb.org/docs/sql/window_functions
aggregate 함수와 window function을 지원한다. 분석 SQL 공부하기에도 적합해 보인다.
Python을 사용할 경우, UDF도 지원한다. SQL로 처리하기 어려운 전처리도 일부 가능할 것으로 보인다.
https://duckdb.org/docs/api/python/function.html
Extension을 설치 후 로드하면 여러 기능을 추가로 사용할 수도 있다. Geospatial 쿼리도 지원한다.
https://duckdb.org/docs/extensions/overview
데이터 저장
conn.sql("SELECT * FROM df").write_csv("df.csv")
conn.sql("SELECT * FROM df").write_parquet("df.parquet")
# SQL 저장 방식 호환
conn.sql(f"""
COPY (SELECT * FROM df) TO '/df.parquet' (FORMAT 'parquet');
""")
엑셀 파일 저장
conn.sql(f"""
INSTALL spatial;
LOAD spatial;
COPY (SELECT * FROM new_table) TO '~/result.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
""")
쿼리 실행 결과를 바로 엑셀로 저장 시, HUGEINT 타입관련 오류가 날 수 있다.
아래처럼, SUM 함수를 사용해서 값이 커질 때 duckdb가 결과의 타입을 HUGEINT로 정의하는 것으로 보인다.
HUGEINT 타입은 duckdb에서 연산을 잘 수행하기 위한 타입인 것 같다.
conn.sql(f"""
COPY (
SELECT key, SUM(val)
FROM new_table
GROUP BY key
) TO '~/result.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
""")
NotImplementedException: Not implemented Error: Unsupported type for OGR: HUGEINT
이 때는, 직접 CAST 함수를 사용하여 타입을 BIGINT로 변경해주면 된다.
conn.sql(f"""
COPY (
SELECT key, CAST(SUM(val) AS BIGINT) AS sum_val
FROM new_table
GROUP BY key
) TO '~/result.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
""")
csv나 parquet 저장 시에는 BIGINT 타입으로 처리되어서 저장되는 것으로 보인다.
jupyter notebook 연결
https://duckdb.org/docs/guides/python/jupyter.html
간단하게 연결을 설정한 후 conn.sql() 함수 사용없이 쿼리문을 테스트할 수 있다.
%load_ext sql
duck_conn = duckdb.connect()
%sql duck_conn --alias duckdb
%%sql
CREATE OR REPLACE TABLE temp_table AS
SELECT * FROM read_csv_auto([
'/test.csv',
]
);
SELECT * FROM temp_table
DBeaver 연결
다양한 connector를 제공하는 DBeaver와도 연결이 가능하다.
MEMORY 버전과 .db 파일 연동 둘 다 가능하며, read_only 사용 시는 이미지처럼 driver properties에서 duckdb.read_only 값을 적용해주어야 한다.
https://duckdb.org/docs/guides/sql_editors/dbeaver.html
연결된 모습
기본 사용법만 확인해봤으며, 공식 문서를 보면 다양하게 활용하는 방법을 알 수 있다.
References :