ODBC 확장

ODBC 확장

odbc_scanner 확장은 다른 데이터베이스(그들의 ODBC 드라이버 사용)에 연결해서, [odbc_query]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_query)로 쿼리를 실행하거나 [odbc_copy]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_copy) 함수로 DuckDB에서 데이터를 복사할 수 있게 해줘요. 함께 살펴볼까요?

출처: 문서

본문

DuckDB는 또한 [ODBC 클라이언트]({% link docs/current/clients/odbc/overview.md %})를 제공하는데, 이를 통해 다른 애플리케이션이 ODBC를 통해 DuckDB에 연결할 수 있어요.

odbc_scanner 확장은 다른 데이터베이스(그들의 ODBC 드라이버 사용)에 연결해서, [odbc_query]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_query)로 쿼리를 실행하거나 [odbc_copy]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_copy) 함수로 DuckDB에서 데이터를 복사할 수 있게 해줘요. 확장은 odbc 별칭으로도 사용할 수 있어요.

설치와 로드 (Installing and Loading)

Linux와 macOS에서는 확장이 unixODBC 드라이버 매니저가 설치되어 있어야 해요. 설치 지침은 아래를 참고하세요.

확장은 자동으로 설치될 수 있지만, 다음과 같이 수동으로 로드해야 해요:

LOAD odbc;

사용 예시 (Usage Example)

-- load extension
LOAD odbc;

-- open ODBC connection to a remote DB
SET VARIABLE conn = odbc_connect('Driver={Oracle driver};DBQ=//127.0.0.1:1521/XE', 'scott', 'tiger');

-- simple query
FROM odbc_query(getvariable('conn'), 'SELECT SYSTIMESTAMP FROM DUAL');

-- query with parameters
FROM odbc_query(getvariable('conn') 
    'SELECT CAST(? AS NVARCHAR2(2)) || CAST(? AS VARCHAR2(5)) FROM DUAL',
    params = row('🦆', 'quack'));

-- copy data into remote DB
FROM odbc_copy(getvariable('conn'),
    source_file = 'https://blobs.duckdb.org/nl_stations.csv',
    dest_table = 'NL_TRAIN_STATIONS',
    create_table = true);

-- close connection
SELECT odbc_close(getvariable('conn'));

야간 버전 설치 (Installing the Nightly Version)

ODBC 확장은 버전 독립적인 DuckDB C API로 빌드돼요. DuckDB 버전 1.2.0이나 그보다 새로운 버전에, 같은 바이너리(특정 플랫폼용, 예: windows_amd64)를 설치하고 로드할 수 있어요.

DuckDB 야간 저장소에 게시된 가장 최근 변경 사항이 있는 바이너리는 다음과 같이 설치할 수 있어요:

INSTALL 'http://nightly-extensions.duckdb.org/v1.2.0/⟨platform⟩/odbc_scanner.duckdb_extension.gz';

버전 1.2.0이 포함된 URL은 DuckDB의 더 새로운 버전을 실행 중이더라도 사용해야 해요.

여기서 ⟨platform⟩{:.language-sql .highlight}은 다음 중 하나예요:

• linux_amd64
• linux_arm64
• linux_amd64_musl
• linux_arm64_musl
• osx_amd64
• osx_arm64
• windows_amd64
• windows_arm64

설치된 확장을 최신 야간 버전으로 업데이트하려면:

FORCE INSTALL 'http://nightly-extensions.duckdb.org/v1.2.0/⟨platform⟩/odbc_scanner.duckdb_extension.gz';

설치된 버전(커밋 ID)은 다음 쿼리로 확인할 수 있어요:

FROM duckdb_extensions()
WHERE extension_name = 'odbc_scanner';

특정 커밋에서 빌드된 버전을 설치하려면:

FORCE INSTALL 'http://nightly-extensions.duckdb.org/odbc_scanner/⟨7_character_commit_id⟩/v1.2.0/⟨platform⟩/odbc_scanner.duckdb_extension.gz';

DBMS별 타입 지원 상태 (Support Status of DBMS-Specific Types)

Tier 1:

• Oracle: types coverage status
• SQL Server: types coverage status
• DB2: types coverage status

Tier 2:

• PostgreSQL: 기본 타입 커버
• MySQL/MariaDB: 기본 타입 커버
• Firebird: types coverage status

Tier 3:

• Snowflake: types coverage status
• ClickHouse: 기본 타입 커버
• Spark: 기본 타입 커버
• Arrow Flight SQL: 기본 타입 커버

Linux 또는 macOS에 unixODBC 드라이버 매니저 설치 (Installing unixODBC Driver Manager on Linux or macOS)

Linux에서는 시스템 패키지 매니저로 unixODBC를 설치할 수 있어요. Linux 배포판에 따라 다음 설치 명령 중 하나를 사용할 수 있어요.

Debian, Ubuntu:

sudo apt-get install unixodbc

RHEL, Alma, Rocky, Amazon, Fedora:

sudo dnf install unixODBC

Alpine:

sudo apk add unixodbc

macOS에서는 Homebrew 패키지 매니저로 unixODBC를 설치할 수 있어요:

brew install unixodbc

Rosetta 변환기에서 레거시 x86_64 ODBC 드라이버를 사용하려면 unixODBC를 x86_64 버전의 Homebrew로 설치해야 해요:

arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
/usr/local/bin/brew install unixodbc

연결 문자열 예시 (Connection String Examples)

ODBC 연결은 데이터 소스 이름을 DSN=data_source1_name 형태로 사용하거나, 구성된 데이터 소스 없이 Driver={Driver name};parameter1=values1;... 형태로 만들 수 있어요.

[odbc_list_drivers]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_list_drivers)와 [odbc_list_data_sources]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_list_data_sources) 함수로 사용 가능한 드라이버와 데이터 소스를 찾을 수 있어요.

구성된 데이터 소스가 없는 연결 문자열 예시:

Oracle:

Driver={Oracle in instantclient_23_0};DBQ=//127.0.0.1:1521/XE;UID=scott;PWD=tiger;

SQL Server:

Driver={ODBC Driver 18 for SQL Server};Server=tcp:127.0.0.1,1433;UID=sa;PWD=pwd;TrustServerCertificate=Yes;Database=test_db;

DB2:

Driver={IBM DB2 ODBC DRIVER};HostName=127.0.0.1;Port=50000;Database=testdb;UID=db2inst1;PWD=pwd;

PostgreSQL:

Driver={PostgreSQL Unicode};Server=127.0.0.1;Port=5432;Username=postgres;Password=postgres;Database=test_db;

MySQL/MariaDB:

Driver={MariaDB ODBC 3.1 Driver};SERVER=127.0.0.1;PORT=3306;USER=root;PASSWORD=root;DATABASE=test_db;

Firebird:

Driver={Firebird ODBC Driver};Database=127.0.0.1/3050:C:/path/to/test.fdb;UID=SYSDBA;PWD=pwd;CHARSET=UTF8;

Snowflake:

Driver={SnowflakeDSIIDriver};Server=foobar-ab12345.snowflakecomputing.com;Database=SNOWFLAKE_SAMPLE_DATA;UID=username;PWD=pwd;

ClickHouse:

Driver={ClickHouse ODBC Driver (Unicode)};Server=127.0.0.1;Port=8123;

Spark:

Driver={Simba Spark ODBC Driver};Host=127.0.0.1;Port=10000;

Arrow Flight SQL (Dremio ODBC + GizmoSQL):

Driver={Dremio Flight SQL ODBC Driver};Host=127.0.0.1;Port=31337;UID=gizmosql_username;PWD=gizmosql_password;useEncryption=true;

쿼리 파라미터 (Query Parameters)

준비된 문장으로 DuckDB 쿼리를 실행하면 클라이언트 코드에서 입력 파라미터를 전달할 수 있어요. 확장은 이런 입력 파라미터를 ODBC API를 통해 원격 데이터베이스의 쿼리로 전달할 수 있게 해줘요.

[odbc_query]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_query) 함수에 params 또는 params_handle 명명 인자를 사용해서 쿼리 파라미터를 전달하는 두 가지 방법이 지원돼요.

params 인자는 STRUCT 값을 입력으로 받아요. Struct 필드 이름은 무시되므로 row() 함수로 STRUCT 값을 인라인으로 만들 수 있어요:

FROM odbc_query(
    getvariable('conn'),
    '
      SELECT CAST(? AS VARCHAR2(3)) || CAST(? AS VARCHAR2(3)) FROM DUAL
    ', 
    params = row(?, ?))

이 쿼리를 duckdb_prepare()로 준비하고 duckdb_bind_value()로 foo와 bar VARCHAR 값을 바인딩하고 duckdb_execute_prepared()로 실행하면 - 입력 파라미터 foo와 bar가 원격 DB의 ODBC 쿼리로 전달돼요.

이 접근 방식의 문제는 DuckDB는 duckdb_execute_prepared()가 호출되기 전에 (외부 쿼리에 지정된) 파라미터 타입을 해석할 수 없다는 점이에요 - 그런 타입은 이후 duckdb_execute_prepared() 호출에서 다를 수 있고 이러한 타입을 명시적으로 지정할 방법이 없어요.

이것은 duckdb_execute_prepared()가 호출될 때마다 원격 DB에서 내부 쿼리를 다시 준비하게 만들어요.

이 문제를 피하려면 [odbc_query]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_query)에 params_handle 명명 인자를 사용하는 2단계 파라미터 바인딩을 사용할 수 있어요:

-- create parameters handle
SET VARIABLE params = odbc_create_params();

-- when 'duckdb_prepare()' is called, the inner query will be prepared in the remote DB
FROM odbc_query(
    getvariable('conn'),
    '
      SELECT CAST(? AS VARCHAR2(3)) || CAST(? AS VARCHAR2(3)) FROM DUAL
    ', 
    params_handle = getvariable('params'));

-- now we can repeatedly bind new parameters to the handle using 'odbc_bind_params()'
-- and call 'duckdb_execute_prepared()' to run the prepared query with
-- these new parameters in remote DB
SELECT odbc_bind_params(getvariable('conn'), getvariable('params'), row(?, ?));

파라미터 핸들은 준비된 문장에 묶여 있으며, 문장이 파괴될 때 해제돼요.

연결과 동시성 (Connections and Concurrency)

DuckDB는 멀티스레드 실행 엔진을 사용해 쿼리의 일부를 병렬로 실행해요. ODBC 드라이버는 서로 다른 스레드의 같은 연결을 동시에 사용하는 것을 지원할 수도 있고 아닐 수도 있어요. 가능한 동시성 문제를 막기 위해 확장은 여러 스레드에서 같은 연결을 사용하는 것을 허용하지 않아요. 예를 들어 다음 쿼리:

FROM odbc_query(getvariable('conn'), 'SELECT ''foo'' col1 FROM DUAL')
UNION ALL
FROM odbc_query(getvariable('conn'), 'SELECT ''bar'' col1 FROM DUAL');

는 다음으로 실패해요:

Invalid Input Error:
'odbc_query' error: ODBC connection not found on global init, id: 139760181976192

이것은 여러 ODBC 연결을 사용해서 피할 수 있어요:

FROM odbc_query(getvariable('conn1'), 'SELECT ''foo'' col1 FROM DUAL')
UNION ALL
FROM odbc_query(getvariable('conn2'), 'SELECT ''bar'' col1 FROM DUAL');

또는 threads DuckDB 옵션을 1로 설정해서 멀티스레드 실행을 비활성화할 수 있어요.

트랜잭션 관리 (Transaction Management)

ODBC 사양에 따르면 원격 DB에 대한 연결은 기본적으로 auto-commit 모드가 활성화되어 있을 것으로 기대돼요.

일반적으로 BEGIN TRANSACTION/COMMIT/ROLLBACK 같은 트랜잭션 명령은 SQL 명령으로 ODBC를 통해 보내지지 않아야 해요. 그렇게 하면 특정 드라이버에서 지원되거나 지원되지 않을 수 있어요. 대신 ODBC는 트랜잭션을 관리하는 API를 제공해요.

이 API는 다음 함수들로 노출돼요:

• [odbc_begin_transaction]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_begin_transaction)
• [odbc_commit]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_commit)
• [odbc_rollback]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_rollback)

연결에서 [odbc_begin_transaction]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_begin_transaction)이 호출되면 이 연결의 auto-commit 모드가 비활성화되고 암시적 트랜잭션이 시작돼요. 현재 이런 연결에서 auto-commit을 다시 활성화하는 것은 지원되지 않아요.

트랜잭션이 시작된 후에는 [odbc_commit]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_commit) 또는 [odbc_rollback]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_rollback)을 호출해서 이 트랜잭션을 완료해요. 완료가 수행된 후 이 연결에서 새 암시적 트랜잭션이 자동으로 시작돼요.

성능 (Performance)

ODBC는 고성능 API가 아니에요. [odbc_query]({% link docs/current/core_extensions/odbc/functions.md %}#odbc_query)는 행마다 여러 API 호출을 사용하고 모든 VARCHAR 값에 대해 UCS-2에서 UTF-8 변환을 수행해요. 게다가 쿼리 처리는 엄격히 단일 스레드예요.

성능과만 관련된 이슈를 제출할 때는 예를 들어 pyodbc를 사용한 비슷한 시나리오에서 성능을 확인하세요.

더 알아보기 (Learn more)