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/
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)
# process 종료
conn.close()
기본 사용법은 conn.sql( ... ) 안에 쿼리문을 사용하면 된다.
res = conn.sql(f"""
SELECT
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);
"""
참고: 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');
""")
DBeaver 연결
다양한 connector를 제공하는 DBeaver와도 연결이 가능하다.
MEMORY 버전과 .db 파일 연동 둘 다 가능하며, read_only 사용 시는 이미지처럼 driver properties에서 duckdb.read_only 값을 적용해주어야 한다.
https://duckdb.org/docs/guides/sql_editors/dbeaver.html
연결된 모습
기본 사용법만 확인해봤으며, 공식 문서를 보면 다양하게 활용하는 방법을 알 수 있다.
References :