Примеры использования ADB ClickHouse Connector

Дарья Барышева

В статье показаны примеры передачи данных между ADB и распределенной (distributed) таблицей в ADQM через ADB ClickHouse Connector. При выполнении примеров соблюдены следующие условия:

  • Кластер ADQM установлен согласно руководству Online-установка на следующих хостах: dev-adqm-01, dev-adqm-02, dev-adqm-03, dev-adqm-04.

  • База данных с именем default существует в ADQM.

  • Пользователь с именем default существует в ADQM.

  • Кластер default_cluster определен в секции remote_servers конфигурационного файла config.xml в ADQM. Для получения дополнительной информации можно обратиться к статье Типовой кластер в документации ADQM.

  • Все ноды кластера ADB имеют доступ к HTTP-порту 8123 на всех нодах кластера ADQM, через которые планируется производить загрузку данных.

Запись данных

Чтобы проверить запись данных из кластера ADB в ADQM, выполните следующие шаги:

  1. Подключитесь к базе данных default кластера ADQM под пользователем default (например, через clickhouse-client). Создайте последовательно следующие таблицы.

    • Локальная таблица test_local:

      CREATE TABLE test_local ON CLUSTER default_cluster
(
    id Int32,
    value_string String
)
ENGINE = ReplicatedMergeTree('/clickhouse/test/shard-{shard}', 'replica-{replica}')
ORDER BY id;

    • Распределенная таблица test_distr:

      CREATE TABLE test_distr ON CLUSTER default_cluster AS default.test_local
ENGINE = Distributed(default_cluster, default, test_local, rand());

  2. Подключитесь к базе данных adb кластера ADB под пользователем gpadmin (например, через psql). Создайте последовательно следующие объекты.

    • Сервер clickhouse_server:

      CREATE SERVER clickhouse_server
FOREIGN DATA WRAPPER tkh_fdw
OPTIONS (
	database 'default',
	hosts 'dev-adqm-01:8123, dev-adqm-02:8123, dev-adqm-03:8123, dev-adqm-04:8123',
	distribution_type 'random',
	lines_batch_size '100000',
	send_compressed 'true',
	send_delay '300',
	insert_distributed_sync 'true',
	use_staging 'true',
	staging_table_name_format '$_tmp_$');

    • Сопоставление пользователя gpadmin:

      CREATE USER MAPPING FOR gpadmin
SERVER clickhouse_server
OPTIONS (clickhouse_username 'default', clickhouse_password '');

    • Сторонняя таблица tkhemali_test:

      CREATE FOREIGN TABLE tkhemali_test (id INT, value_string TEXT)
SERVER clickhouse_server
OPTIONS (resource 'test_distr',
	hosts 'dev-adqm-01:8123',
        cluster 'default_cluster');
      ВАЖНО

      Поскольку на табличном уровне определена опция cluster, вся работа будет проводиться с хостами, полученными из метаданных кластера ADQM. Поэтому требуется перезаписать значение опции hosts, указанное для clickhouse_server на уровне сервера, и оставить только один хост в значении опции на уровне таблицы.

  3. Добавьте данные в таблицу ADB tkhemali_test:

    INSERT INTO tkhemali_test VALUES
(1, 'test1'),
(2, 'test2'),
(3, 'test3'),
(4, 'test4'),
(5, 'test5'),
(6, 'test6'),
(7, 'test7'),
(8,'test8'),
(9,'test9'),
(10,'test10');

    Результат:

    WARNING:  skipping "themali_test" --- cannot analyze this foreign table
INSERT 0 10

  4. Убедитесь, что данные, переданные из ADB, успешно добавлены в ADQM:

    SELECT * FROM test_distr;

    Результат:

    ┌─id─┬─value_string─┐
│  2 │ test2        │
│  5 │ test5        │
│  8 │ test8        │
│ 10 │ test10       │
└────┴──────────────┘
┌─id─┬─value_string─┐
│  1 │ test1        │
│  3 │ test3        │
│  4 │ test4        │
│  6 │ test6        │
│  7 │ test7        │
│  9 │ test9        │
└────┴──────────────┘
10 rows in set. Elapsed: 0.005 sec.

Чтение данных

ПРИМЕЧАНИЕ

ADB ClickHouse Connector поддерживает возможность чтения данных из ADQM/ClickHouse в ADB 6 начиная с версии 6.24.3.47. Однако, чтобы использовать стороннюю таблицу в ADB для чтения, в ней необходимо определить имена столбцов, точно совпадающие с соответствующими именами в таблице ADQM/ClickHouse.

Пример корректного чтения данных

Следующий пример демонстрирует чтение данных из кластера ADQM в ADB:

  1. Выполните шаги 1—​2, приведенные выше (если они не были пройдены ранее).

  2. Удалите данные из таблицы test_local в ADQM:

    ALTER TABLE default.test_local ON CLUSTER default_cluster DELETE WHERE true;

  3. Добавьте данные в таблицу ADQM test_distr:

    INSERT INTO test_distr VALUES
(1, 'test1'),
(2, 'test2'),
(3, 'test3'),
(4, 'test4'),
(5, 'test5'),
(6, 'test6'),
(7, 'test7'),
(8,'test8'),
(9,'test9'),
(10,'test10');

    Результат:

    Query id: 430a158f-1374-470d-a8c2-7b4dabdefbc7

Ok.

10 rows in set. Elapsed: 0.048 sec.

  4. Убедитесь, что данные, добавленные в ADQM, могут быть успешно прочитаны из ADB:

    SELECT * FROM tkhemali_test ORDER BY id;

    Результат:

     id | value_string
----+--------------
  1 | test1
  2 | test2
  3 | test3
  4 | test4
  5 | test5
  6 | test6
  7 | test7
  8 | test8
  9 | test9
 10 | test10
(10 rows)

Пример с ошибкой

Приведенный ниже пример показывает, что чтение данных из ADQM/ClickHouse в ADB невозможно в случае, если имена столбцов в сторонней таблице ADB и исходной таблице ADQM/ClickHouse не совпадают:

  1. Выполните все шаги из примера с корректным чтением данных (если они не выполнены ранее).

  2. Убедитесь, что чтение данных в ADB при текущей структуре сторонней таблицы работает успешно:

    SELECT * FROM tkhemali_test ORDER BY id;

    Результат:

     id | value_string
----+--------------
  1 | test1
  2 | test2
  3 | test3
  4 | test4
  5 | test5
  6 | test6
  7 | test7
  8 | test8
  9 | test9
 10 | test10
(10 rows)

  3. Переименуйте часть столбцов сторонней таблицы tkhemali_test в ADB:

    ALTER FOREIGN TABLE tkhemali_test RENAME value_string TO wrong_string;

  4. Повторно проверьте возможность чтения данных из таблицы tkhemali_test в ADB:

    SELECT * FROM tkhemali_test ORDER BY id;

    В результате возвращается ошибка:

    ERROR:  CH easy datum. HTTP error: 404; CH message: Code: 47. DB::Exception: Missing columns: 'wrong_string' while processing query: 'SELECT id, wrong_string FROM default.test_local', required columns: 'id' 'wrong_string', maybe you meant: 'id'. (UNKNOWN_IDENTIFIER) (version 23.3.2.37) (ch_easy_datum.c:142)  (seg0 slice1 192.0.2.182:10000 pid=6414) (ch_easy_datum.c:142)
ПРИМЕЧАНИЕ

Если имена совпадают только у части столбцов сторонней таблицы в ADB и исходной таблицы в ADQM/ClickHouse — запрос успешно отрабатывает, пока в результирующем наборе данных используются только указанные столбцы.

Использование SSL/TLS

ПРИМЕЧАНИЕ

  • До ADB 6.30.0 в коннекторе ADB ClickHouse поддерживался SSL только для операций INSERT. Начиная с версии 6.30.0 SSL также можно использовать с SELECT.

  • Операции INSERT с SSL поддерживаются как для локальных, так и для распределенных таблиц ClickHouse. Операции SELECT с SSL поддерживаются только для локальных таблиц.

Перед использованием коннектора ADB ClickHouse с SSL выполните действия, описанные в разделе Запись данных, чтобы попробовать обычное соединение, создать локальную таблицу на стороне ClickHouse, а также сторонний сервер и сопоставление пользователя — на стороне ADB.

Настройка SSL/TLS в ClickHouse

Информацию о настройке TLS на стороне сервера см. в документации ClickHouse. Если вы используете ADQM, обратитесь к документации ADQM.

В качестве примера команды ниже создают сертификат сервера, подписанный собственным удостоверяющим центром (CA). В production-средах используйте сертификаты, подписанные доверенным CA.

  1. Сгенерируйте закрытый ключ и сохраните его в файл ca.key:

    $ openssl genrsa -out ca.key 2048

  2. Создайте самоподписанный сертификат CA:

    $ openssl req -x509 -subj "/CN=MyClickHouseCA" -nodes -key ca.key -days 1095 -out ca.crt

  3. Сгенерируйте ключ сервера и создайте запрос на подпись сертификата (Certificate Signing Request, CSR). В качестве Common Name (CN) укажите адрес сервера ClickHouse, по которому он доступен для хостов ADB (в этом примере — val-adqm-1.ru-central1.internal). Этот же адрес будет использоваться ниже в качестве hosts в сторонних таблицах ADB:

    $ openssl req -newkey rsa:2048 -nodes \
  -subj "/CN=val-adqm-1.ru-central1.internal" \
  -addext "subjectAltName = DNS:val-adqm-1.ru-central1.internal" \
  -keyout server.key -out server.csr

  4. Подпишите CSR сервера с помощью CA, чтобы получить сертификат сервера:

    $ openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365

  5. Переместите сертификат и ключ сервера в директорию, доступную ClickHouse:

    $ mkdir /etc/clickhouse-server/certs
$ mv ca.crt server.crt server.key /etc/clickhouse-server/certs/

  6. Установите следующие разрешения для файлов сертификатов:

    $ chown -R clickhouse:clickhouse /etc/clickhouse-server/certs
$ chmod 600 /etc/clickhouse-server/certs/server.key
$ chmod 644 /etc/clickhouse-server/certs/server.crt /etc/clickhouse-server/certs/ca.crt

  7. Создайте конфигурационный файл для SSL:

    $ vi /etc/clickhouse-server/config.d/ssl.xml

  8. Укажите в нем порт для HTTPS-соединений и путь к созданным сертификату и ключу:

    <clickhouse>
    <https_port>8443</https_port>
    <openSSL>
        <server>
            <certificateFile>/etc/clickhouse-server/certs/server.crt</certificateFile>
            <privateKeyFile>/etc/clickhouse-server/certs/server.key</privateKeyFile>
        </server>
    </openSSL>
</clickhouse>

  9. Перезапустите ClickHouse:

    $ sudo systemctl restart clickhouse-server

  10. Скопируйте полученный выше файл ca.crt на мастер-хост и каждый сегментный хост ADB.

Перед использованием коннектора ADB ClickHouse с SSL можно отправить запрос через cURL, чтобы установить HTTPS-канал между хостом ADB и сервером ClickHouse и таким образом проверить корректность описанных выше настроек. На любом хосте ADB выполните команду:

$ curl --cacert /home/gpadmin/certs/ca.crt -u default: 'https://val-adqm-1.ru-central1.internal:8443/?query=SELECT%20version()'

Сервер должен вернуть версию ClickHouse, например:

25.8.16.34

SSL без проверки сертификата

При настройке, описанной ниже, шифрование TLS включено для передачи данных между ADB и ClickHouse, но коннектор не проверяет сертификат сервера.

  1. В ADB создайте стороннюю таблицу с параметрами ssl=true и sslmode=none в опции clickhouse_properties:

    CREATE FOREIGN TABLE tkhemali_sslmode_none (id INT, value_string TEXT)
SERVER clickhouse_server
OPTIONS (
    resource 'test_local',
    hosts 'val-adqm-1.ru-central1.internal:8443',
    clickhouse_properties 'ssl=true;sslmode=none'
);

  2. Чтобы убедиться, что команды INSERT используют SSL, добавьте данные в таблицу test_local с помощью созданной сторонней таблицы:

    INSERT INTO tkhemali_sslmode_none (id, value_string) VALUES
    (1, 'ssl none test 1'),
    (2, 'ssl none test 2'),
    (3, 'ssl none test 3');

    Результат:

    WARNING:  skipping "tkhemali_sslmode_none" --- cannot analyze this foreign table
INSERT 0 3

  3. Чтобы убедиться, что команды SELECT используют SSL, выполните запрос к этой таблице:

    SELECT * FROM tkhemali_sslmode_none;

    Результат:

     id |  value_string
----+-----------------
  1 | ssl none test 1
  2 | ssl none test 2
  3 | ssl none test 3
(3 rows)

SSL с проверкой сертификата

При настройке, описанной ниже, шифрование TLS включено и коннектор выполняет проверку сертификата.

  1. Создайте стороннюю таблицу с опцией clickhouse_properties, указав в ней ssl=true, sslmode=strict, а для параметра sslrootcert укажите путь файла сертификата:

    CREATE FOREIGN TABLE tkhemali_sslmode_strict (id INT, value_string TEXT)
SERVER clickhouse_server
OPTIONS (
    resource 'test_local',
    hosts 'val-adqm-1.ru-central1.internal:8443',
    clickhouse_properties 'ssl=true;sslmode=strict;sslrootcert=/home/gpadmin/certs/ca.crt'
);

  2. Чтобы убедиться, что команды INSERT используют SSL, добавьте данные в таблицу test_local с помощью созданной сторонней таблицы:

    INSERT INTO tkhemali_sslmode_strict (id, value_string) VALUES
    (1, 'strict test 1'),
    (2, 'strict test 2'),
    (3, 'strict test 3');

    Результат:

    WARNING:  skipping "tkhemali_sslmode_strict" --- cannot analyze this foreign table
INSERT 0 3

  3. Чтобы убедиться, что команды SELECT используют SSL, выполните запрос к этой таблице:

    SELECT * FROM tkhemali_sslmode_strict ORDER BY value_string DESC LIMIT 3;

    Результат:

     id | value_string
----+---------------
  3 | strict test 3
  2 | strict test 2
  1 | strict test 1
(3 rows)
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней