MySQL 확장
MySQL 확장
mysql 확장은 DuckDB가 실행 중인 MySQL 인스턴스에서 데이터를 직접 읽고 쓸 수 있게 해줘요. MySQL 테이블에서 DuckDB 테이블로, 또는 그 반대로 데이터를 로드할 수 있답니다. 함께 알아볼까요?
출처: 문서
본문
mysql 확장은 DuckDB가 실행 중인 MySQL 인스턴스에서 데이터를 직접 읽고 쓸 수 있게 해줘요. 데이터는 기저 MySQL 데이터베이스에서 직접 쿼리할 수 있어요. MySQL 테이블에서 DuckDB 테이블로, 또는 그 반대로 데이터를 로드할 수 있어요.
설치와 로드 (Installing and Loading)
mysql 확장을 설치하려면 다음을 실행하세요:
INSTALL mysql;
확장은 첫 사용 시 자동으로 로드돼요. 수동으로 로드하고 싶다면 다음을 실행하세요:
LOAD mysql;
MySQL에서 데이터 읽기 (Reading Data from MySQL)
MySQL 데이터베이스를 DuckDB에서 접근 가능하게 하려면 mysql 또는 mysql_scanner 타입과 함께 ATTACH 명령을 사용하세요:
ATTACH 'host=localhost user=root port=0 database=mysql' AS mysqldb (TYPE mysql);
USE mysqldb;
구성 (Configuration)
연결 문자열은 key=value 쌍의 집합으로 MySQL에 연결하는 방법의 파라미터를 결정해요. 제공되지 않은 옵션은 아래 표에 따라 기본값으로 대체돼요. 연결 정보는 환경 변수로도 지정할 수 있어요. 옵션이 명시적으로 제공되지 않으면 MySQL 확장은 환경 변수에서 읽으려고 해요.
| Setting | Default | Environment variable |
|---|---|---|
| database | NULL | MYSQL_DATABASE |
| host | localhost | MYSQL_HOST |
| password | MYSQL_PWD | |
| port | 0 | MYSQL_TCP_PORT |
| socket | NULL | MYSQL_UNIX_PORT |
| user | current user | MYSQL_USER |
| ssl_mode | preferred | |
| ssl_ca | ||
| ssl_capath | ||
| ssl_cert | ||
| ssl_cipher | ||
| ssl_crl | ||
| ssl_crlpath | ||
| ssl_key |
Secrets로 구성 (Configuring via Secrets)
MySQL 연결 정보는 secrets로도 지정할 수 있어요. 다음 구문으로 secret을 만들 수 있어요.
CREATE SECRET (
TYPE mysql,
HOST '127.0.0.1',
PORT 0,
DATABASE mysql,
USER 'mysql',
PASSWORD ''
);
ATTACH가 호출될 때 secret의 정보가 사용돼요. 연결 문자열을 비워 두면 secret에 저장된 모든 정보를 사용할 수 있어요.
ATTACH '' AS mysql_db (TYPE mysql);
연결 문자열을 사용해 개별 옵션을 재정의할 수도 있어요. 예를 들어 같은 자격 증명을 사용하면서 다른 데이터베이스에 연결하려면 데이터베이스 이름만 다음과 같이 재정의할 수 있어요.
ATTACH 'database=my_other_db' AS mysql_db (TYPE mysql);
기본적으로 생성된 secret은 임시예요. secret은 [CREATE PERSISTENT SECRET 명령]({% link docs/current/configuration/secrets_manager.md %}#persistent-secrets)을 사용해 영속화할 수 있어요. 영속 secret은 세션 간에 사용할 수 있어요.
여러 secret 관리 (Managing Multiple Secrets)
여러 MySQL 데이터베이스 인스턴스에 대한 연결을 관리하려면 명명된 secret을 사용할 수 있어요. secret은 생성 시 이름을 붙일 수 있어요.
CREATE SECRET mysql_secret_one (
TYPE mysql,
HOST '127.0.0.1',
PORT 0,
DATABASE mysql,
USER 'mysql',
PASSWORD ''
);
그러면 ATTACH의 SECRET 파라미터로 secret을 명시적으로 참조할 수 있어요.
ATTACH '' AS mysql_db_one (TYPE mysql, SECRET mysql_secret_one);
SSL 연결 (SSL Connections)
ssl 연결 파라미터를 사용해 SSL 연결을 만들 수 있어요. 지원되는 파라미터의 설명은 아래와 같아요.
| Setting | Description |
|---|---|
| ssl_mode | 서버 연결에 사용할 보안 상태: disabled, required, verify_ca, verify_identity or preferred (기본값: preferred) |
| ssl_ca | Certificate Authority (CA) 인증서 파일의 경로 이름 |
| ssl_capath | 신뢰할 수 있는 SSL CA 인증서 파일이 들어 있는 디렉토리의 경로 이름 |
| ssl_cert | 클라이언트 공개 키 인증서 파일의 경로 이름 |
| ssl_cipher | SSL 암호화에 허용되는 암호 목록 |
| ssl_crl | 인증서 폐기 목록이 들어 있는 파일의 경로 이름 |
| ssl_crlpath | 인증서 폐기 목록이 들어 있는 파일이 있는 디렉토리의 경로 이름 |
| ssl_key | 클라이언트 개인 키 파일의 경로 이름 |
MySQL 테이블 읽기 (Reading MySQL Tables)
MySQL 데이터베이스의 테이블은 일반 DuckDB 테이블처럼 읽을 수 있지만, 기저 데이터는 쿼리 시점에 MySQL에서 직접 읽혀요.
SHOW ALL TABLES;
| name |
|---|
| signed_integers |
SELECT * FROM signed_integers;
| t | s | m | i | b |
|---|---|---|---|---|
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
| 127 | 32767 | 8388607 | 2147483647 | 9223372036854775807 |
| NULL | NULL | NULL | NULL | NULL |
특히 큰 테이블의 경우, 시스템이 MySQL에서 테이블을 계속 다시 읽는 것을 막기 위해 MySQL 데이터베이스의 사본을 DuckDB에 만드는 것이 바람직할 수 있어요.
표준 SQL을 사용해 MySQL에서 DuckDB로 데이터를 복사할 수 있어요, 예를 들어:
CREATE TABLE duckdb_table AS FROM mysqlscanner.mysql_table;
MySQL에 데이터 쓰기 (Writing Data to MySQL)
MySQL에서 데이터를 읽는 것 외에도, 표준 SQL 쿼리로 테이블을 만들고, MySQL에 데이터를 넣고, MySQL 데이터베이스를 수정할 수 있어요.
이를 통해 예를 들어 MySQL 데이터베이스에 저장된 데이터를 Parquet로 내보내거나, Parquet 파일의 데이터를 MySQL로 읽어 들이는 데 DuckDB를 사용할 수 있어요.
아래는 MySQL에 새 테이블을 만들고 데이터를 로드하는 간단한 예시예요.
ATTACH 'host=localhost user=root port=0 database=mysqlscanner' AS mysql_db (TYPE mysql);
CREATE TABLE mysql_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO mysql_db.tbl VALUES (42, 'DuckDB');
MySQL 테이블에 대한 많은 연산이 지원돼요. 이 모든 연산은 MySQL 데이터베이스를 직접 수정하고, 이후 연산의 결과는 MySQL을 사용해 읽을 수 있어요.
수정을 원하지 않으면 ATTACH를 READ_ONLY 속성으로 실행해서 기저 데이터베이스의 수정을 막을 수 있어요. 예를 들어:
ATTACH 'host=localhost user=root port=0 database=mysqlscanner' AS mysql_db (TYPE mysql, READ_ONLY);
지원 연산 (Supported Operations)
아래는 지원되는 연산 목록이에요.
CREATE TABLE
CREATE TABLE mysql_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO
INSERT INTO mysql_db.tbl VALUES (42, 'DuckDB');
SELECT
SELECT * FROM mysql_db.tbl;
| id | name |
|---|---|
| 42 | DuckDB |
COPY
COPY mysql_db.tbl TO 'data.parquet';
COPY mysql_db.tbl FROM 'data.parquet';
[COPY FROM DATABASE 문]({% link docs/current/sql/statements/copy.md %}#copy-from-database--to)으로 데이터베이스의 전체 사본을 만들 수도 있어요:
COPY FROM DATABASE mysql_db TO my_duckdb_db;
UPDATE
UPDATE mysql_db.tbl
SET name = 'Woohoo'
WHERE id = 42;
DELETE
DELETE FROM mysql_db.tbl
WHERE id = 42;
ALTER TABLE
ALTER TABLE mysql_db.tbl
ADD COLUMN k INTEGER;
DROP TABLE
DROP TABLE mysql_db.tbl;
CREATE VIEW
CREATE VIEW mysql_db.v1 AS SELECT 42;
CREATE SCHEMA와 DROP SCHEMA
CREATE SCHEMA mysql_db.s1;
CREATE TABLE mysql_db.s1.integers (i INTEGER);
INSERT INTO mysql_db.s1.integers VALUES (42);
SELECT * FROM mysql_db.s1.integers;
| i |
|---|
| 42 |
DROP SCHEMA mysql_db.s1;
트랜잭션 (Transactions)
CREATE TABLE mysql_db.tmp (i INTEGER);
BEGIN;
INSERT INTO mysql_db.tmp VALUES (42);
SELECT * FROM mysql_db.tmp;
이것은 다음을 반환해요:
| i |
|---|
| 42 |
ROLLBACK;
SELECT * FROM mysql_db.tmp;
이것은 빈 테이블을 반환해요.
DDL 문장은 MySQL에서 트랜잭셔널하지 않아요.
MySQL에서 SQL 쿼리 실행 (Running SQL Queries in MySQL)
mysql_query 테이블 함수 (The mysql_query Table Function)
mysql_query 테이블 함수는 연결된 데이터베이스 안에서 임의의 읽기 쿼리를 실행할 수 있게 해줘요. mysql_query는 쿼리를 실행할 연결된 MySQL 데이터베이스의 이름과, 실행할 SQL 쿼리를 받아요. 쿼리 결과가 반환돼요. 작은따옴표 문자열은 작은따옴표를 두 번 반복해서 이스케이프돼요.
mysql_query(attached_database::VARCHAR, query::VARCHAR)
예를 들어:
ATTACH 'host=localhost database=mysql' AS mysqldb (TYPE mysql);
SELECT * FROM mysql_query('mysqldb', 'SELECT * FROM cars LIMIT 3');
mysql_execute 함수 (The mysql_execute Function)
mysql_execute 함수는 MySQL에서 임의의 쿼리를 실행할 수 있게 해주며, 데이터베이스의 스키마와 내용을 업데이트하는 문장도 포함해요.
ATTACH 'host=localhost database=mysql' AS mysqldb (TYPE mysql);
CALL mysql_execute('mysqldb', 'CREATE TABLE my_table (i INTEGER)');
설정 (Settings)
| Name | Description | Default |
|---|---|---|
mysql_bit1_as_boolean |
BIT(1) 컬럼을 BOOLEAN으로 변환할지 여부 |
true |
mysql_debug_show_queries |
DEBUG SETTING: MySQL로 보내는 모든 쿼리를 stdout에 출력 | false |
mysql_enable_filter_pushdown |
필터 푸시다운(술어 분석기 없이)을 사용할지 여부 | true |
mysql_enable_transactions |
MySQL 연결에서 START TRANSACTION / COMMIT / ROLLBACK을 실행할지 여부 |
true |
mysql_incomplete_dates_as_nulls |
월이나 일이 0인 DATE를 NULL로 반환할지 여부 |
false |
mysql_pool_acquire_mode |
풀에서 연결을 획득하는 방법: force (풀 한도를 무시하고 항상 연결), wait (하나가 사용 가능할 때까지 차단), 또는 try (사용 가능한 것이 없으면 즉시 실패) |
force |
mysql_pool_connection_idle_timeout_millis |
닫히기 전에 연결이 캐시에서 유휴 상태로 있을 수 있는 최대 시간(밀리초) | 60000 |
mysql_pool_connection_max_lifetime_millis |
풀링된 연결이 처음 열린 이후의 최대 나이(밀리초). 초과하면 연결이 캐시로 반환되는 대신 닫혀요 (0은 비활성화) |
0 |
mysql_pool_enable_reaper_thread |
풀을 주기적으로 스캔하고 만료된 연결을 제거하는 전용 스레드를 실행할지 여부 | true |
mysql_pool_enable_thread_local_cache |
더 빠른 같은 스레드 연결 재사용을 위한 스레드 로컬 연결 캐싱 활성화 | false |
mysql_pool_size |
MySQL 카탈로그당 최대 연결 수 | automatic (CPU 수 기준) |
mysql_pool_wait_timeout_millis |
풀에서 연결을 기다릴 때의 타임아웃(밀리초) | 30000 |
mysql_session_time_zone |
MySQL 서버에 새로 열린 연결에 설정할 세션 시간대 | '' |
mysql_time_as_time |
MySQL의 TIME 컬럼을 DuckDB의 TIME으로 변환할지 여부 |
false |
mysql_tinyint1_as_boolean |
TINYINT(1) 컬럼을 BOOLEAN으로 변환할지 여부 |
true |
스키마 캐시 (Schema Cache)
MySQL에서 스키마 데이터를 계속 가져오는 것을 피하기 위해 DuckDB는 테이블 이름, 컬럼 등 같은 스키마 정보를 캐시해요. MySQL 인스턴스에 대한 다른 연결을 통해(예: 테이블에 새 컬럼이 추가되는 등) 스키마에 변경이 있으면 캐시된 스키마 정보가 오래될 수 있어요. 이 경우 mysql_clear_cache 함수를 실행해 내부 캐시를 비울 수 있어요.
CALL mysql_clear_cache();