MySQL 테이블 엔진

MySQL 테이블 엔진

MySQL 엔진을 사용하면 원격 MySQL 서버에 저장된 데이터에 대해 SELECT와 INSERT 쿼리를 수행할 수 있어요.

출처: 문서

본문

테이블 생성하기

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = MySQL({host:port, database, table, user, password[, replace_query, on_duplicate_clause] | named_collection[, option=value [,..]]})
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300, ]
    [ enable_compression=false ]
;

CREATE TABLE 쿼리에 대한 자세한 설명은 해당 문서를 참고해요. 테이블 구조는 원본 MySQL 테이블 구조와 달라질 수 있어요.

• 컬럼 이름은 원본 MySQL 테이블과 같아야 하지만, 그중 일부 컬럼만 어떤 순서로든 사용할 수 있어요
• 컬럼 타입은 원본 MySQL 테이블과 달라질 수 있어요. ClickHouse는 값을 cast하여 ClickHouse 데이터 타입으로 변환하려 해요
• external_table_functions_use_nulls 설정은 Nullable 컬럼을 처리하는 방식을 정의해요. 기본값: 1. 0이면 테이블 함수는 Nullable 컬럼을 만들지 않고 null 대신 기본값을 삽입해요. 이는 배열 내부의 NULL 값에도 적용돼요

엔진 매개변수

• host:port — MySQL 서버 주소
• database — 원격 데이터베이스 이름
• table — 원격 테이블 이름, 또는 MySQL에 그대로 전달되는 쿼리(테이블 이름 대신 쿼리 전달 참고)
• user — MySQL 사용자
• password — 사용자 비밀번호
• replace_query — INSERT INTO 쿼리를 REPLACE INTO로 변환하는 플래그. replace_query=1이면 쿼리가 대체돼요
• on_duplicate_clause — INSERT 쿼리에 추가되는 ON DUPLICATE KEY on_duplicate_clause 표현식. 예: INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1, 여기서 on_duplicate_clause는 UPDATE c2 = c2 + 1이에요. ON DUPLICATE KEY 절과 함께 사용할 수 있는 on_duplicate_clause는 MySQL 문서에서 확인해요. on_duplicate_clause를 지정하려면 replace_query 매개변수에 0을 전달해야 해요. replace_query = 1과 on_duplicate_clause를 동시에 전달하면 ClickHouse가 예외를 발생시켜요

인자는 named collections로도 전달할 수 있어요. 이 경우 host와 port를 별도로 지정해야 해요. 이 접근 방식은 프로덕션 환경에 권장돼요.

=, !=, >, >=, <, <= 같은 단순한 WHERE 절은 MySQL 서버에서 실행돼요. 나머지 조건과 LIMIT 샘플링 제약은 MySQL에 대한 쿼리가 끝난 뒤에만 ClickHouse에서 실행돼요.

TLS/SSL

MySQL에 대한 암호화 연결의 자격 증명은 named collection 키(또는 키-값 인자)로 전달돼요.

Parameter	Description
ssl_ca_pem	MySQL 서버 인증서가 검증되는 CA 인증서의 내용
ssl_cert_pem	인증서 기반 인증을 위한 클라이언트 인증서의 내용
ssl_key_pem	ssl_cert_pem에 속하는 개인 키의 내용

값은 해당 PEM 파일의 내용이며, named collection이나 쿼리에 복사할 수 있어요. 비밀번호처럼 로그와 SHOW 쿼리에서 마스킹돼요. 같은 자격 증명을 서버의 파일 경로로도 줄 수 있어요. ssl_ca, ssl_cert, ssl_key로요 — 단 서버 구성 파일에 정의된 named collection에서만 가능하고, 그런 값은 쿼리에서 재정의할 수 없어요. 서버는 그 파일을 자신의 권한으로 열므로, SQL에서 경로를 받아들이면 MySQL 소스를 정의할 수 있는 모든 사용자가 로컬 파일시스템을 탐색하고, 스스로 읽을 권한이 없는 인증서와 키로 인증할 수 있게 돼요.

<named_collections>
    <mysql_creds>
        <host>mysql-host</host>
        <port>3306</port>
        <user>mysql_user</user>
        <password>****</password>
        <ssl_ca>/etc/clickhouse-server/mysql-ca.crt</ssl_ca>
    </mysql_creds>
</named_collections>

테이블 이름 대신 쿼리 전달하기

테이블 이름 대신 table 인자는 MySQL에 그대로 전달되는 SELECT 쿼리일 수 있어요. 테이블 구조는 쿼리 결과에서 추론돼요. 쿼리는 서브쿼리로 쓰거나 query 함수로 감쌀 수 있어요.

CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

이것은 조인, 집계나 다른 처리를 MySQL로 푸시다운하는 데 유용해요. 그런 테이블은 읽기 전용이에요: INSERT가 허용되지 않아요. 같은 문법은 mysql 테이블 함수에서도 지원돼요.

서브쿼리 형식 (SELECT ...)는 ClickHouse가 파싱하고 MySQL 방언(백틱 식별자 따옴표)으로 재직렬화한 다음 서버로 보내요. 따라서 유효한 ClickHouse SQL이어야 해요. ClickHouse가 파싱하지 않는 MySQL 고유 문법을 전달하려면 query('...') 형식을 사용하는데, 그 텍스트는 MySQL에 그대로 전송돼요.

주변 ClickHouse 쿼리의 외부 WHERE, LIMIT, 집계 등은 전달된 쿼리로 푸시다운되지 않아요 — 전체 쿼리 결과를 가져온 뒤 ClickHouse에서 적용돼요. MySQL에서 읽는 데이터를 제한하려면 필터를 전달된 쿼리 안에 넣어요. external_table_strict_query = 1이면 이 테이블의 컬럼에 대한 외부 필터는 로컬로 적용되는 대신 예외로 거부돼요. 전달된 쿼리로 푸시할 수 없기 때문이에요. 이 검사는 최상위 WHERE 조건과 최상위 AND의 각 결합(conjunct)을 다뤄요. 이 테이블의 컬럼에 대한 PREWHERE는 이 설정의 대상이 아니에요: 이 테이블 엔진은 PREWHERE를 지원하지 않으며, 설정과 관계없이 그런 쿼리는 ILLEGAL_PREWHERE로 거부돼요. 검사는 필터를 푸시다운할 수 있는 곳에서만 실행돼요: 이 테이블이 쿼리의 유일한 테이블일 때, INNER JOIN의 어느 쪽일 때, 또는 외부 조인의 보존(preserving) 쪽(LEFT JOIN의 왼쪽, RIGHT JOIN의 오른쪽)일 때요. LEFT/RIGHT JOIN의 비보존 쪽과 FULL JOIN의 어느 쪽에서는 아무것도 푸시다운되지 않고 검사도 되지 않으므로, 이 테이블의 컬럼에 대한 필터는 strict 모드에서도 조인 후 로컬로 적용돼요. 검사가 실행되는 곳에서 주변 쿼리에 조인된 다른 테이블을 참조하는 조건은 푸시다운되지 않고 검사에서 제외돼요. 조인된 쪽만 참조하든 OR 같은 하나의 비-AND 표현식 안에서 이 테이블과 섞든 상관없이요. 그런 조건은 평소 ClickHouse 평가 시점(조인 후 WHERE, 그 전 PREWHERE)을 유지하고 거부되지 않아요.

|로 나열되는 여러 복제본을 지원해요. 예:

CREATE TABLE test_replicas (id UInt32, name String, age UInt32, money UInt32) ENGINE = MySQL(`mysql{2|3|4}:3306`, 'clickhouse', 'test_replicas', 'root', 'clickhouse');

사용 예시

MySQL에서 테이블 만들기:

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `int_nullable` INT NULL DEFAULT NULL,
    ->   `float` FLOAT NOT NULL,
    ->   `float_nullable` FLOAT NULL DEFAULT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)

mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)

mysql> select * from test;
+------+----------+-----+----------+
| int_id | int_nullable | float | float_nullable |
+------+----------+-----+----------+
|      1 |         NULL |     2 |           NULL |
+------+----------+-----+----------+
1 row in set (0,00 sec)

일반 인자를 사용해 ClickHouse에서 테이블 만들기:

CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL('localhost:3306', 'test', 'test', 'bayonet', '123')

또는 named collections 사용:

CREATE NAMED COLLECTION creds AS
        host = 'localhost',
        port = 3306,
        database = 'test',
        user = 'bayonet',
        password = '123';
CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL(creds, table='test')

MySQL 테이블에서 데이터 검색:

SELECT * FROM mysql_table

┌─float_nullable─┬─int_id─┐
│           ᴺᵁᴸᴸ │      1 │
└────────────────┴────────┘

설정

기본 설정은 연결조차 재사용하지 않으므로 그다지 효율적이지 않아요. 이 설정들은 서버가 초당 실행하는 쿼리 수를 늘릴 수 있게 해줘요.

connection_auto_close

쿼리 실행 후 연결을 자동으로 닫을 수 있게 해줘요. 즉, 연결 재사용을 비활성화해요.

가능한 값:

• 1 — 자동 연결 종료가 허용되어 연결 재사용이 비활성화됨
• 0 — 자동 연결 종료가 허용되지 않아 연결 재사용이 활성화됨

기본값: 1.

connection_max_tries

장애 조치(failover)가 있는 풀의 재시도 횟수를 설정해요.

가능한 값:

• 양의 정수
• 0 — 장애 조치가 있는 풀에 대한 재시도가 없음

기본값: 3.

connection_pool_size

연결 풀의 크기 (모든 연결이 사용 중이면 쿼리는 일부 연결이 해제될 때까지 대기해요).

가능한 값:

• 양의 정수

기본값: 16.

connection_wait_timeout

(이미 connection_pool_size개의 활성 연결이 있을 때) 사용 가능한 연결을 기다리는 타임아웃(초), 0 - 기다리지 않음.

가능한 값:

• 양의 정수

기본값: 5.

connect_timeout

연결 타임아웃(초).

가능한 값:

• 양의 정수

기본값: 10.

read_write_timeout

읽기/쓰기 타임아웃(초).

가능한 값:

• 양의 정수

기본값: 300.

enable_compression

MySQL 프로토콜 연결에 대한 압축을 활성화해요.

기본값: false.

이 설정은 다음에 적용돼요.

• MySQL 테이블 엔진
• MySQL 데이터베이스 엔진
• mysql 테이블 함수
• MySQL 통합에 사용되는 named collections

활성화하면 ClickHouse가 연결에 대해 압축을 요청해요.

예시:

CREATE TABLE mysql_engine_compression
(
    id UInt32,
    name String,
    age UInt32,
    money UInt32
)
ENGINE = MySQL('mysql80:3306', 'clickhouse', 'test_table', 'root', 'password')
SETTINGS enable_compression = 1;

함께 보기

• mysql 테이블 함수
• 딕셔너리 소스로 MySQL 사용하기

더 알아보기 (Learn more)

• mysql 테이블 함수
• MySQL을 딕셔너리 소스로 사용