ODBC 익스텐션 함수

ODBC 익스텐션 함수 (ODBC Extension Functions)

ODBC 익스텐션이 제공하는 함수 목록이에요. ODBC 연결을 열고 쿼리하고, 트랜잭션을 관리하고, 데이터 소스와 드라이버를 나열하는 등의 작업을 SQL에서 처리할 수 있어요.

출처: 문서

본문

• odbc_begin_transaction
• odbc_bind_params
• odbc_close
• odbc_commit
• odbc_connect
• odbc_copy
• odbc_create_params
• odbc_list_data_sources
• odbc_list_drivers
• odbc_query
• odbc_rollback

odbc_begin_transaction

odbc_begin_transaction(conn_handle BIGINT) -> VARCHAR

지정된 연결에 SQL_ATTR_AUTOCOMMIT 속성을 SQL_AUTOCOMMIT_OFF로 설정해 사실상 암시적 트랜잭션을 시작해요. 이러한 연결에서 트랜잭션을 완료하려면 odbc_commit 또는 odbc_rollback을 호출해야 해요. 완료는 이 연결에서 또 다른 암시적 트랜잭션을 시작해요. 자세한 내용은 Transaction management를 참고해요.

파라미터 (Parameters):

• conn_handle (BIGINT): odbc_connect로 만든 ODBC 연결 핸들

반환 (Returns):

항상 NULL (VARCHAR) 반환.

예시 (Example):

SELECT odbc_begin_transaction(getvariable('conn'))

odbc_bind_params

odbc_bind_params(conn_handle BIGINT, params_handle BIGINT, params STRUCT) -> BIGINT

지정된 파라미터 핸들에 지정된 파라미터 값을 바인딩해요. 2단계 파라미터 바인딩에서만 필요해요. 자세한 내용은 Query parameters를 참고해요.

파라미터 (Parameters):

• conn_handle (BIGINT): odbc_connect로 만든 ODBC 연결 핸들
• params_handle (BIGINT): odbc_create_params로 만든 파라미터 핸들
• params (STRUCT): 파라미터 값

반환 (Returns):

두 번째 인자로 전달된 것과 같은 파라미터 핸들(BIGINT).

예시 (Example):

SELECT odbc_bind_params(getvariable('conn'), getvariable('params1'), row(42, 'foo'))

odbc_close

odbc_close(conn_handle BIGINT) -> VARCHAR

원격 DB에 대한 지정된 ODBC 연결을 닫아요. 이미 닫혀 있으면 에러를 던지지 않아요.

파라미터 (Parameters):

• conn_handle (BIGINT): odbc_connect로 만든 ODBC 연결 핸들

반환 (Returns):

항상 NULL (VARCHAR) 반환.

예시 (Example):

SELECT odbc_close(getvariable('conn'))

odbc_commit

odbc_commit(conn_handle BIGINT) -> VARCHAR

지정된 연결에서 SQL_COMMIT 인자로 SQLEndTran을 호출해 현재 트랜잭션을 완료해요. 이 호출 전에 이 연결에서 odbc_begin_transaction을 호출해야 완료가 효과가 있어요. 자세한 내용은 Transaction management를 참고해요.

파라미터 (Parameters):

• conn_handle (BIGINT): odbc_connect로 만든 ODBC 연결 핸들

반환 (Returns):

항상 NULL (VARCHAR) 반환.

예시 (Example):

SELECT odbc_commit(getvariable('conn'))

odbc_connect

odbc_connect(conn_string VARCHAR) -> BIGINT

odbc_connect(conn_string VARCHAR, username VARCHAR, password VARCHAR) -> BIGINT

원격 DB에 대한 ODBC 연결을 엽니다.

username과 password(위치) 파라미터가 지정되면 UID와 PWD로 연결 문자열에 추가돼요.

파라미터 (Parameters):

• conn_string (VARCHAR): Driver Manager에 전달되는 ODBC 연결 문자열.

반환 (Returns):

VARIABLE에 넣을 수 있는 연결 핸들. 연결은 자동으로 닫히지 않으며, odbc_close로 닫아야 해요.

예시 (Example):

SET VARIABLE conn = odbc_connect('Driver={Oracle Driver};DBQ=//127.0.0.1:1521/XE;UID=scott;PWD=tiger')

SET VARIABLE conn = odbc_connect('Driver={Oracle Driver};DBQ=//127.0.0.1:1521/XE', 'scott', 'tiger')

odbc_copy

odbc_copy(conn_handle BIGINT, [, <optional named parameters>]) -> TABLE

odbc_copy(conn_string VARCHAR, [, <optional named parameters>]) -> TABLE

DuckDB가 접근할 수 있는 파일이나 테이블에서 원격 DB로 행을 복사해요.

경고 (Warning) Python Relational API에서 odbc_copy를 사용할 때.

odbc_copy는 복사된 2048행마다 하나의 행을 반환하는 테이블 함수예요. Python에서 duckdb.sql()로 사용하면 Lazy Evaluation이 일어나요. 그래서 실행을 트리거하는 메서드가 결과 릴레이션에 호출되고 모든 결과 행이 소비될 때까지 어떤 행도 복사되지 않아요.

파라미터 (Parameters):

• conn_handle_or_string (BIGINT 또는 VARCHAR), 다음 중 하나:
  • odbc_connect로 만든 ODBC 연결 핸들
  • 일회성 쿼리를 위한 ODBC 연결 문자열. 이 경우 새 ODBC 연결이 열리고 쿼리가 끝나면 자동으로 닫혀요

선택적 명명된 파라미터 (source):

소스 쿼리는 odbc_copy가 호출되는 인스턴스와 별개의 DB 인스턴스를 사용해 실행돼요. 따라서 source_query는 기존 인메모리 테이블을 참조할 수 없고 현재 열려 있는 DuckDB 파일을 열 수 없어요. 해결 방법으로, 복잡한 소스 쿼리의 경우 쿼리 결과를 먼저 로컬 Parquet 파일로 내보낸 다음 그 파일에 odbc_copy를 실행하는 것이 좋아요.

• source_conn_string (VARCHAR, 기본: :memory:): 소스 DB에 대한 DuckDB 연결 문자열. 예: ducklake:postgres:postgresql://username:***@127.0.0.1:5432/lake1
• source_file (VARCHAR): DuckDB로 읽을 (원격 또는 로컬) Parquet, CSV, JSON 파일 경로. 예: https://blobs.duckdb.org/nl_stations.csv. source_query='SELECT * FROM '<source_file>'과 동일
• source_query (VARCHAR): 데이터를 읽을 DuckDB SQL 쿼리. 예: FROM nl_train_stations
• source_queries (LIST(VARCHAR)): 하나씩 실행되는 여러 DuckDB SQL 쿼리. 마지막 쿼리가 복사할 결과 집합을 반환해야 하고, 이전 쿼리의 결과는 버려지며, 모든 쿼리의 결과는 메모리에 구체화돼요. 예:

source_queries=[
  'CREATE SECRET s (TYPE s3 [...])',
  'FROM nl_train_stations'
],

• source_limit (UBIGINT, 기본: 0): 한 번에 소스 쿼리/파일에서 읽을 레코드 수. 이 옵션이 지정되면 소스 쿼리는 LIMIT <limit> OFFSET <offset>을 추가해 여러 번 실행돼요. 2048 이상이어야 하고 2048이 나머지 없이 나눠질 수 있어야 해요

선택적 명명된 파라미터 (destination):

• dest_table (VARCHAR): 원격 DB의 대상 테이블 이름. INSERT와 CREATE TABLE 쿼리에 사용됨. dest_query가 지정되면 지정할 수 없음; DB마다 대소문자 구분과 기본 대소문자 규칙이 달라서 대상 테이블 이름을 대문자 TAB1로 지정하거나 따옴표 형태 "tab1"(또는 스키마 이름 포함 "schema1"."tab1")으로 지정해야 할 수 있어요
• dest_query (VARCHAR): 각 소스 배치에 대해 원격 DB에서 실행될 쿼리. ODBC 파라미터 플레이스홀더 ?의 수가 source_columns_count * batch_size와 같아야 함. dest_table이 지정되면 지정할 수 없음. 예: CALL import_city(?,?,?,?)
• dest_query_single (VARCHAR): batch_size>0이고 마지막 소스 배치에서 읽은 행 수가 batch_size보다 적을 때만 사용됨. 이 경우 dest_query 대신 사용. ODBC 파라미터 플레이스홀더 ?의 수가 source_columns_count와 같아야 해요

선택적 명명된 파라미터 (create table):

• create_table (BOOLEAN, 기본: FALSE): 소스 쿼리의 컬럼 이름과 컬럼 타입을 사용해 대상 원격 DB에 테이블을 만들지 여부. 사실상 CTAS(create table as select) 구현
• column_types (MAP(VARCHAR, VARCHAR)): create_table=TRUE가 지정될 때 소스 DuckDB 타입과 대상 RDBMS 타입 사이의 타입 매핑을 제공/재정의할 수 있게 해줌. 예:

create_table=TRUE,
column_types=MAP {
    'DUCKDB_TYPE_VARCHAR': 'VARCHAR2(10)',
    'DUCKDB_TYPE_DECIMAL': 'NUMBER({typmod1},{typmod2})'}

• column_quotes (VARCHAR, 기본: "): 생성된 CREATE TABLE과 INSERT 쿼리에서 컬럼 이름을 인용하는 데 사용할 인용 문자(또는 문자열)
• commit_after_create_table (BOOLEAN, 기본: FALSE): CREATE TABLE 실행 후 COMMIT을 발행할지 여부. Firebird에서는 자동으로 활성화됨

선택적 명명된 파라미터 (query parameters handling):

• decimal_params_as_chars (BOOLEAN, 기본: false): DECIMAL 파라미터를 VARCHAR로 전달
• integral_params_as_decimals (BOOLEAN, 기본: false): (부호 없는) TINYINT, SMALLINT, INTEGER, BIGINT 파라미터를 SQL_C_NUMERIC으로 전달.

선택적 명명된 파라미터 (other):

• batch_size (UINTEGER, 기본: 16): 원격 DB에 대한 단일 SQLExecute ODBC 호출에서 삽입(또는 dest_query의 경우 실행)할 레코드 수. 허용 값: 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048
• use_insert_all (BOOLEAN, 기본: FALSE): INSERT ... VALUES (...), (...), ... (...) 배치 삽입 대신 INSERT ALL 배치 삽입 쿼리 생성. Oracle에서는 자동으로 활성화됨
• use_insert_union (BOOLEAN, 기본: FALSE): INSERT ... VALUES (...), (...), ... (...) 배치 삽입 대신 INSERT ... SELECT FROM ... UNION ALL ... 배치 삽입 쿼리 생성. Firebird에서는 자동으로 활성화됨
• dummy_table_name (VARCHAR): INSERT ALL과 INSERT UNION 쿼리에 사용할 더미 테이블 이름. Oracle은 dual
• copy_in_transaction (BOOLEAN, 기본: TRUE): 이 복사 호출에 대해 원격 DB에서 트랜잭션 시작. 모든 행이 처리되면 커밋, 에러 시 롤백
• max_records_in_transaction (UBIGINT, 기본: 0): 지정 시 지정된 수의 행이 처리된 후마다 원격 트랜잭션을 커밋하게 함
• close_connection (BOOLEAN, 기본: false): 함수 호출이 완료된 후 전달된 연결을 닫음. odbc_copy의 일회성 호출과 함께 사용하기 위한 것

반환 (Returns):

다음 컬럼의 테이블:

• completed (BOOLEAN): 이 출력 행이 결과 집합의 마지막 행인지 여부
• rows_processed (UBIGINT): 소스에서 읽은 행 수
• elapsed_seconds (FLOAT): 복사 프로세스 시작 후 경과한 초 수
• rows_per_second (FLOAT): 1초에 처리된 행 수
• table_ddl (VARCHAR): 복사 프로세스를 시작하기 전에 원격 DB에서 실행된 생성된 CREATE TABLE 쿼리

소스에서 읽은 2048행마다 하나의 결과 행이 출력돼요. completed=TRUE와 non-null table_ddl 값은 마지막 행만 가져요(create_table=TRUE가 지정된 경우에만).

예시 (Examples):

FROM odbc_copy(getvariable('conn'),
  source_file='https://blobs.duckdb.org/nl_stations.csv',
  dest_table='NL_TRAIN_STATIONS',
  create_table=TRUE)

FROM odbc_copy(getvariable('conn'),
  source_conn_string='ducklake:postgres:postgresql://username:***@127.0.0.1:5432/lake1',
  source_queries=[
    'CREATE SECRET s (TYPE s3 [...])',
    'FROM nl_train_stations'
  ],
  dest_table='NL_TRAIN_STATIONS',
  create_table=TRUE,
  batch_size=32,
  max_records_in_transaction=42);

odbc_create_params

odbc_create_params() -> BIGINT

파라미터 핸들을 만든다. 2단계 파라미터 바인딩에서만 필요해요. 자세한 내용은 Query parameters를 참고해요.

파라미터 (Parameters):

없음.

반환 (Returns):

파라미터 핸들(BIGINT). 핸들이 odbc_query로 전달되면 기본 준비된 문(prepared statement)에 연결되고 문이 닫힐 때 자동으로 닫혀요.

예시 (Example):

SET VARIABLE params1 = odbc_create_params()

odbc_list_data_sources

odbc_list_data_sources() -> TABLE(name VARCHAR, description VARCHAR, type VARCHAR)

OS에 등록된 ODBC 데이터 소스 목록을 반환해요. 드라이버 매니저 호출 SQLDataSources를 사용해요.

파라미터 (Parameters):

없음.

반환 (Returns):

다음 컬럼의 테이블:

• name (VARCHAR): 데이터 소스 이름
• description (VARCHAR): 데이터 소스 설명
• type (VARCHAR): 데이터 소스 타입. USER 또는 SYSTEM

예시 (Example):

FROM odbc_list_data_sources()

odbc_list_drivers

odbc_list_drivers() -> TABLE(description VARCHAR, attributes MAP(VARCHAR, VARCHAR))

OS에 등록된 ODBC 드라이버 목록을 반환해요. 드라이버 매니저 호출 SQLDrivers를 사용해요.

파라미터 (Parameters):

없음.

반환 (Returns):

다음 컬럼의 테이블:

• description (VARCHAR): 드라이버 설명
• attributes (MAP(VARCHAR, VARCHAR)): name->value 맵으로서의 드라이버 속성

예시 (Example):

FROM odbc_list_drivers()

odbc_query

odbc_query(conn_handle BIGINT, query VARCHAR[, <optional named parameters>]) -> TABLE

odbc_query(conn_string VARCHAR, query VARCHAR[, <optional named parameters>]) -> TABLE

원격 DB에서 지정된 쿼리를 실행하고 쿼리 결과 테이블을 반환해요.

파라미터 (Parameters):

• conn_handle_or_string (BIGINT 또는 VARCHAR), 다음 중 하나:
  • odbc_connect로 만든 ODBC 연결 핸들
  • 일회성 쿼리를 위한 ODBC 연결 문자열. 이 경우 새 ODBC 연결이 열리고 쿼리가 끝나면 자동으로 닫혀요
• query (VARCHAR): 원격 DBMS로 전달되는 SQL 쿼리

쿼리 파라미터를 전달하는 데 사용할 수 있는 선택적 명명된 파라미터:

• params (STRUCT): 원격 DBMS에 전달할 쿼리 파라미터
• params_handle (BIGINT): odbc_create_params로 만든 파라미터 핸들. 2단계 파라미터 바인딩에서만 사용. 자세한 내용은 Query parameters를 참고해요.

타입 매핑을 바꿀 수 있는 선택적 명명된 파라미터:

익스텐션은 쿼리 파라미터가 어떻게 전달되고 결과 데이터가 어떻게 처리되는지 바꾸는 데 사용할 수 있는 여러 옵션을 지원해요. 알려진 DB에 대해 이 옵션들은 자동으로 설정돼요. 자동 구성을 재정의하기 위해 odbc_query 함수에 명명된 파라미터로 전달할 수도 있어요:

• decimal_columns_as_chars (BOOLEAN, 기본: false): DECIMAL 값을 VARCHAR로 읽되 클라이언트에 반환하기 전에 다시 DECIMAL로 파싱
• decimal_columns_precision_through_ard (BOOLEAN, 기본: false): DECIMAL을 읽을 때 "Application Row Descriptor"를 통해 precision과 scale 지정
• decimal_columns_as_ard_type (BOOLEAN, 기본: false): DECIMAL을 읽을 때 SQL_C_NUMERIC 대신 SQL_ARD_TYPE 사용
• decimal_params_as_chars (BOOLEAN, 기본: false): DECIMAL 파라미터를 VARCHAR로 전달
• integral_params_as_decimals (BOOLEAN, 기본: false): (부호 없는) TINYINT, SMALLINT, INTEGER, BIGINT 파라미터를 SQL_C_NUMERIC으로 전달.
• reset_stmt_before_execute (BOOLEAN, 기본: false): 실행 전에 준비된 문을 재설정(SQLFreeStmt(h, SQL_CLOSE) 사용)
• time_params_as_ss_time2 (BOOLEAN, 기본: false): TIME 파라미터를 SQL Server의 TIME2 값으로 전달
• timestamp_columns_as_timestamp_ns (BOOLEAN, 기본: false): TIMESTAMP-류(TIMESTAMP WITH LOCAL TIME ZONE, DATETIME2, TIMESTAMP_NTZ 등) 컬럼을 나노초 정밀도(소수 9자리)로 읽기
• timestamp_columns_with_typename_date_as_date (BOOLEAN, 기본: false): 타입 이름이 DATE인 TIMESTAMP 컬럼을 DuckDB DATE로 읽기
• timestamp_max_fraction_precision (UTINYINT, 기본: 9): 나노초 정밀도의 TIMESTAMP 컬럼을 읽을 때 사용할 최대 소수 자리 수
• timestamp_params_as_sf_timestamp_ntz (BOOLEAN, 기본: false): TIMESTAMP 파라미터를 Snowflake의 TIMESTAMP_NTZ로 전달
• timestamptz_params_as_ss_timestampoffset (BOOLEAN, 기본: false): TIMESTAMP_TZ 파라미터를 SQL Server의 DATETIMEOFFSET으로 전달
• var_len_data_single_part (BOOLEAN, 기본: false): 긴 VARCHAR 또는 VARBINARY 값을 단일 읽기로 읽기(드라이버가 Variable-Length Data를 부분적으로 검색을 지원하지 않을 때 사용)
• var_len_params_long_threshold_bytes (UINTEGER, 기본: 4000): 이후부터 SQL_WVARCHAR 파라미터를 SQL_WLONGVARCHAR로 전달하는 길이 임계값
• enable_columns_binding (BOOLEAN, 기본: false): 고정 크기 컬럼에 SQLGetData 대신 SQLBindCol 사용 허용 여부

기타 선택적 명명된 파라미터:

• ignore_exec_failure (BOOLEAN, 기본: false): 원격 DB에서 실행되는 쿼리가 성공적으로 준비될 수 있지만 실행 시점에(예: 테이블 존재 같은 스키마 상태 때문에) 실패할 수도 있을 때, 이 플래그를 사용해 쿼리 실행 실패 시 에러를 던지지 않을 수 있음. 쿼리 실행이 실패하면 빈 결과 집합이 반환돼요.
• close_connection (BOOLEAN, 기본: false): 함수 호출 완료 후 전달된 연결을 닫음. odbc_query의 일회성 호출과 함께 사용하기 위한 것. 예:

FROM odbc_query(
   odbc_connect('Driver={Oracle Driver};DBQ=//127.0.0.1:1521/XE', 'scott', 'tiger'),
   'SELECT 42 FROM dual',
   close_connection=TRUE);

반환 (Returns):

쿼리 결과가 담긴 테이블.

예시 (Example):

FROM odbc_query(getvariable('conn'), 
  'SELECT CAST(? AS NVARCHAR2(2)) || CAST(? AS VARCHAR2(5)) FROM dual',
  params=row('🦆', 'quack')
)

odbc_rollback

odbc_rollback(conn_handle BIGINT) -> VARCHAR

지정된 연결에서 SQL_ROLLBACK 인자로 SQLEndTran을 호출해 현재 트랜잭션을 완료해요. 이 호출 전에 이 연결에서 odbc_begin_transaction을 호출해야 완료가 효과가 있어요. 자세한 내용은 Transaction management를 참고해요.

파라미터 (Parameters):

• conn_handle (BIGINT): odbc_connect로 만든 ODBC 연결 핸들

반환 (Returns):

항상 NULL (VARCHAR) 반환.

예시 (Example):

SELECT odbc_rollback(getvariable('conn'))

더 알아보기 (Learn more)

• ODBC 익스텐션 — 연결·파라미터·트랜잭션 관리 전반.