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는 값을 ClickHouse 데이터 타입으로 형 변환하려고 시도합니다.
• external_table_functions_use_nulls 설정은 널 허용 컬럼을 처리하는 방식을 정의합니다. 기본값은 1입니다. 값이 0이면 테이블 함수는 널 허용 컬럼을 만들지 않고 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에서 예외가 발생합니다.

인수는 이름이 지정된 컬렉션을 사용해 전달할 수도 있습니다. 이 경우 host와 port는 별도로 지정해야 합니다. 이 방식은 프로덕션 환경에 권장됩니다.

=, !=, >, >=, <, <=와 같은 단순한 WHERE 절은 MySQL 서버에서 실행됩니다.

나머지 조건과 LIMIT 샘플링 제약은 MySQL 쿼리가 완료된 후에만 ClickHouse에서 실행됩니다.

MySQL 암호화 연결의 자격 증명은 명명된 컬렉션 키(또는 키-값 인수)로 전달됩니다.

값은 해당 PEM 파일의 내용이며, 명명된 컬렉션 또는 쿼리에 복사할 수 있습니다. 비밀번호와 마찬가지로 로그와 SHOW 쿼리에서는 마스킹됩니다.

동일한 자격 증명은 서버의 파일 경로로 ssl_ca, ssl_cert, ssl_key에 지정할 수도 있습니다. 단, 서버 구성 파일에 정의된 명명된 컬렉션에서만 가능하며, 이러한 값은 쿼리에서 재정의할 수 없습니다. 서버는 자체 권한으로 이러한 파일을 열므로, SQL에서 경로를 허용하면 MySQL source를 정의할 수 있는 모든 사용자가 로컬 파일 시스템을 탐색하고, 직접 읽을 권한이 없는 인증서와 키를 사용해 인증할 수 있게 됩니다.

<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 테이블 함수에서도 지원됩니다.

여러 레플리카를 지원하며, |로 구분해 나열해야 합니다. 예를 들면 다음과 같습니다.

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')

또는 이름이 지정된 컬렉션:

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 │
└────────────────┴────────┘

기본 설정은 연결조차 재사용하지 않아 효율이 그다지 높지 않습니다. 이러한 설정을 사용하면 서버가 초당 처리할 수 있는 쿼리 수를 늘릴 수 있습니다.

쿼리 실행 후 연결을 자동으로 닫아, 연결 재사용을 비활성화할 수 있습니다.

가능한 값:

• 1 — 연결 자동 닫기가 허용되며, 연결 재사용이 비활성화됩니다
• 0 — 연결 자동 닫기가 허용되지 않으며, 연결 재사용이 활성화됩니다

기본값: 1.

페일오버를 지원하는 풀의 재시도 횟수를 설정합니다.

가능한 값:

• 양의 정수
• 0 — 페일오버를 지원하는 풀에 대해 재시도하지 않습니다.

기본값: 3.

연결 풀의 크기입니다(모든 연결이 사용 중인 경우, 일부 연결이 해제될 때까지 쿼리는 대기합니다).

가능한 값:

• 양의 정수.

기본값: 16.

사용 가능한 연결이 생길 때까지 대기하는 시간 제한(초)입니다(이미 connection_pool_size개의 연결이 활성 상태인 경우). 0이면 대기하지 않습니다.

Possible values:

• 양의 정수.

기본값: 5.

연결 시간 제한(초)입니다.

가능한 값:

• 양의 정수.

기본값: 10.

읽기-쓰기 타임아웃(초)입니다.

가능한 값:

• 양의 정수.

기본값: 300.

MySQL 프로토콜 연결에서 압축을 활성화합니다.

기본값: false.

이 설정은 다음에 적용됩니다.

• MySQL 테이블 엔진;
• MySQL 데이터베이스 엔진;
• mysql 테이블 함수;
• MySQL 통합에 사용되는 이름이 지정된 컬렉션.

활성화되면 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을 딕셔너리 소스로 사용하는 방법