Внешние словари из MSSQL

[Oleg Kamashev]

Уважаемые разработчики, подскажите, пожалуйста, можно ли где-то найти схемы xml *_dictionaries.xml дабы посмотреть все возможные варианты и параметры.

Конкретно интересует выкачка словарей из mssql.

Олег

[man...@gmail.com]

Есть возможность использовать словари из ODBC источника.

Указывается так:

<source>

    <odbc>
        <db>имя базы данных, можно не указывать</db>

        <table>имя таблицы</table>
        <connection_string>строка вида DSN=...</connection_string>

    </odbc>

</source>

Перед использованием, проверьте подключение к MSSQL по ODBC с помощью какой-нибудь программы (например, isql из пакета unixodbc).

Непосредственно с MSSQL ещё не пробовали использовать. Насколько я знаю, коллеги использовали с Oracle.

[Timur Shenkao]

Hello guys!

You mentioned in official documentation / blog that usage of ODBC driver is not recommended yet.
So, unofficially: have you tried to connect Tableau to ClickHouse?

[Oleg Kamashev]

Спасибо большое! Все получилось!

[Oleg Kamashev]

Точнее почти все получилось. Есть проблема с кодировкой.

Если смотреть через isql , русские буквы на месте.

client charset = UTF-8 везде проставлен в конфигах

А в ответе от кликхауза приходят '?????????'

[man...@gmail.com]

Hello guys!
You mentioned in official documentation / blog that usage of ODBC driver is not recommended yet.
So, unofficially: have you tried to connect Tableau to ClickHouse?

ODBC driver for ClickHouse is in development stage, and not suitable for usage. And we have not tried to connect Tableau to ClickHouse yet.

In this topic, people ask me a different thing.

Not ODBC driver for ClickHouse, but the ability of ClickHouse to load dictionaries from external sources, one of them is any database through ODBC.

https://clickhouse.yandex/reference_en.html#External dictionaries

Specifically, possibility for ClickHouse to load dictionaries from MS SQL Server using ODBC driver for MS SQL Server.

[man...@gmail.com]

В ODBC слегка запутанно сделана работа с Unicode... боюсь, надо воспроизводить в лабораторных условиях и разбираться.

Кстати, какой эффект, если вы используете не isql, а iusql?

[Oleg Kamashev]

При использовании iusql фигня всякая лезет вида

0@80=A:85 >-20  

[man...@gmail.com]

Я попробовал воспроизвести ситуацию в "лабораторных" условиях.
Удалось добиться, чтобы всё было Ок.

Создал в VirtualBox виртуальную машину с Windows 10, установил туда MS SQL Server 2014 Express.
В VirtualBox создал и подключил Host-only network adapter.
Указал MS SQL Server для работы порт 1433, открыл этот порт в firewall-е Windows.
Включил SQL Server and Windows Authentication, создал пользователя test с паролем test.

Создал базу данных test и таблицу dict в ней.

CREATE TABLE test (k INT NOT NULL, s NVARCHAR(255) NOT NULL, PRIMARY KEY (k))

Добавил для теста пару строк:

В Linux установил:

sudo apt-get install tdsodbc freetds-bin sqsh

$ cat /etc/freetds/freetds.conf
...

[MSSQL]
host = 192.168.56.101
port = 1433
tds version = 7.0
client charset = UTF-8

Обратите внимание на то, что версия 7.0 и на то, что UTF-8 написано через минус. Я не знаю, на что это влияет.

$ cat /etc/odbcinst.ini
...

[FreeTDS]
Description     = FreeTDS
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage       = 1
UsageCount      = 5

$ cat ~/.odbc.ini
...

[MSSQL]
Description     = FreeTDS
Driver          = FreeTDS
Servername      = MSSQL
Database        = test
UID             = test
PWD             = test
Port            = 1433


В ClickHouse создал файл /etc/clickhouse-server/mssql_dictionary.xml

$ cat mssql_dictionary.xml
<dictionaries>
    <comment>Не обязательный элемент с любым содержимым; полностью игнорируется.</comment>

    <!-- Можно задать произвольное количество разных словарей. -->
    <dictionary>
        <!-- Имя словаря. Под этим именем словарь будет доступен для использования. -->
        <name>test</name>

        <!-- Источник данных. -->
        <source>

            <odbc>
                <table>dict</table>
                <connection_string>DSN=MSSQL;UID=test;PWD=test</connection_string>
            </odbc>

        </source>

        <!-- Периодичность обновления для полностью загружаемых словарей. 0 - никогда не обновлять. -->
        <lifetime>
            <min>300</min>
            <max>360</max>
            <!-- Периодичность обновления выбирается равномерно-случайно между min и max,
                 чтобы размазать по времени нагрузку при обновлении словарей на большом количестве серверов. -->
        </lifetime>

        <layout>   <!-- Способ размещения в памяти. -->
            <flat />
        </layout>

        <!-- Структура. -->
        <structure>
            <!-- Описание столбца, являющегося идентификатором (ключём) словаря. -->
            <id>
                <!-- Имя столбца с идентификатором. -->
                <name>k</name>
            </id>

            <attribute>    <!-- id уже входит в атрибуты и дополнительно указывать его здесь не нужно. -->
                <!-- Имя столбца. -->
                <name>s</name>
                <!-- Тип столбца. (Как столбец понимается при загрузке.
                     В случае MySQL, в таблице может быть TEXT, VARCHAR, BLOB, но загружается всё как String) -->
                <type>String</type>
                <!-- Какое значение использовать для несуществующего элемента. В примере - пустая строка. -->
                <null_value></null_value>
            </attribute>

        </structure>
    </dictionary>
</dictionaries>


Затем выполняю в clickhouse-client:

$ clickhouse-client
ClickHouse client version 1.1.53971.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.53971.

:) SELECT number, dictGetString('test', 's', number) AS res FROM system.numbers LIMIT 3

SELECT
    number,
    dictGetString('test', 's', number) AS res
FROM system.numbers
LIMIT 3

┌─number─┬─res────┐
│      0 │        │
│      1 │ Тест   │
│      2 │ Abcdef │
└────────┴────────┘

3 rows in set. Elapsed: 0.004 sec.

Как видите, строка "Тест" отобразилась нормально.


Выводы:

- может быть, у вас конфигурация FreeTDS немного другая;
- может быть, вы используете не NVARCHAR, а что-то другое.

[man...@gmail.com]

Возможно, ещё пригодится то, что при загрузке внешних словарей можно указывать выражения (это не отражено в документации):

<attribute>
	<name>attr</name>
	<type>UInt64</type>
	<expression>a + b</expression>
	<null_value>0</null_value>
</attribute>

В запросе за данными будет написано: a + b AS attr.
То есть, если необходимо - можно сделать какой-нибудь CAST.

[Oleg Kamashev]

Спасибо большое. Да там был не nvarchar, а varchar. 

Я правда обошел эту проблему сделав синхронизацию в таблицу со стороны MSSQL, а внешний словарь уже поднимаю из таблицы clickhouse.

[Stepan Semiokhin]

Не подскажите по внешним словарям такой момент: если у меня в словаре 4 поля и нужен JOIN по 3-м из них (не только по id) в зависимости от ситуации, такой кейс внешние словари не поддерживают и лучше просто выгрузить словарь, как обычную таблицу?

[man...@gmail.com]

Три поля - это три разных ключа для словаря, или же это один составной ключ из трёх полей?
Внешние словари можно использовать и в том, и в другом случае.

В первом случае:
- объявите три разных словаря с одним источником и разными ключами.

Во втором случае:
- составной ключ для внешних словарей поддерживается, но документация недописана; если надо - допишем.

Что лучше использовать - словарь или JOIN?
- лучше использовать словарь, если не нужны нетривиальные запросы над соединяемой таблицей.


понедельник, 18 июля 2016 г., 13:35:45 UTC+3 пользователь Stepan Semiokhin написал:

[Stepan Semiokhin]

Спасибо вам большое за подробные ответы!

У меня первый случай, но, думаю, документация по составному ключу для словаря была бы многим полезна.

вторник, 19 июля 2016 г., 8:46:30 UTC+3 пользователь man...@gmail.com написал:

[Stepan Semiokhin]

Настроил на машине ODBC для Постреса, но при попытке запроса получаю:

DB::Exception. Code 20. DB::Exception: RecordSet contains 0 columns while 20 expected

Не подскажете, с чем это может быть связано?

Подключаясь через isql по той же DNS, все запросы выполняются корректно...

[man...@gmail.com]

Какой layout у вашего словаря?
Если hashed или flat, то посмотрите в логе ClickHouse сервера, какой отправляется запрос. Всё ли с ним в порядке?
Результат у этого запроса непустой?

[Stepan Semiokhin]

Я разобрался, этот момент, мне кажется, тоже стоит указать в документации (и в принципе про ODBC подключение написать), проблема была в следующем:

в odbc.ini я указал идентификатор подключения со всеми настройками (юзер, пароль, хост, имя базы, порт, etc) и в том числе указал имя базы в dictionary.xml .

Получалось, что подключившись уже к нужной базе запрос вызывался в формате 

... FROM db.table;

Что в случае ODBC-подключения приводило к ошибке. Сразу не обратил на это внимания.

После исправления словарь работает отлично, большое спасибо!

вторник, 19 июля 2016 г., 18:53:52 UTC+3 пользователь man...@gmail.com написал:

[Stepan Semiokhin]

А не подскажете, можно ли как-то получить все значения из словаря? Какого-нибудь аналога * в качестве Id нет?

вторник, 19 июля 2016 г., 18:53:52 UTC+3 пользователь man...@gmail.com написал:

Какой layout у вашего словаря?

[man...@gmail.com]

Как получить все атрибуты?
- сейчас никак кроме явного перечисления;
В принципе, можно было бы добавить функцию, возвращающую кортеж.

Как получить все значения одного атрибута?
- если id идут более-менее подряд, то так:

SELECT dictGetSomething('dict', 'attr', number) FROM system.numbers LIMIT 1000000


среда, 27 июля 2016 г., 11:12:09 UTC+3 пользователь Stepan Semiokhin написал: