Загрузка данных с помощью JDBC

Загрузка данных

Для загрузки данных с помощью JDBC, вам необходимо подключиться к одной из поддерживаемых баз данных с помощью SQL-запроса. При этом вы можете сделать это двумя способами:

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

В загружаемых таблицах названия полей не должны содержать следующие символы:

точка;
двоеточие;
квадратные скобки.

Для загрузки данных необходимо выполнить следующие шаги:

В рабочей области щелкните по названию набора данных, в который вы будете загружать таблицы, либо создайте новый набор и выберите его:
В открывшемся окне нажмите Данные > Выбрать источник данных:
В появившемся диалоге кликните JDBC и нажмите кнопку Выбрать:
В открывшемся окне выберите Новое подключение и заполните все поля (см. пример ниже):
- Адрес сервера в формате mysql://{адрес сервера}:{порт} или postgresql://{адрес сервера}:{порт};
- База данных - название базы данных;
- Имя пользователя, под которым вы будете подключаться к базе данных;
- Пароль для подключения. В пароле не допускаются следующие символы:
  - обратная косая черта (\);
  - апостроф (')
  - обратный апостроф (`)
- Название таблицы – укажите в этом поле название для добавляемой таблицы. Поддерживаются кириллические и латинские символы, цифры, а также спецсимволы (кроме одинарных и двойных кавычек);
- Текст запроса - текст отправляемого запроса. Например, select * from dimproduct
  Пример заполнения полей:
  
  Рекомендуется писать ключевые слова SQL в нижнем регистре. Например, from вместо FROM.
  Вы также можете задать произвольное JDBC-подключение. Для этого необходимо переключиться в режим работы со строкой, включив опцию Строка подключения, ввести необходимую строку и указать пароль:
  
  Строки подключения можно также обновить при редактировании подключений или загрузчиков:
Для проверки подключения нажмите Проверить в правом верхнем углу формы:

При успешном подключении система оповестит об этом:

Вы также можете просмотреть данные, которые вы собираетесь загрузить. Для этого нажмите Предварительный просмотр таблицы в нижней части диалога:
После заполнения всех полей нажмите Загрузить в правом нижнем углу.
Данные загрузятся на платформу Visiology и вы увидите таблицу на экране построения модели данных. В нашем примере у нас получилось следующее:

Если данные не загрузились, нажмите F12 на клавиатуре, чтобы проверить прошла ли загрузка успешно. При успешной загрузке в консоли отобразится появится запись “Операция загрузки прошла успешно!”:
Информация для администратора: администратор платформы может установить таймаут для загрузки данных, по истечении которого загрузка прекратится. Для установки таймаута необходимо сделать следующее:
- Добавьте в файл /var/lib/visiology/scripts/v3/env-files/fe.env требуемое значение для таймаута в переменную RefreshDataJob__Timeout:
  RefreshDataJob__Timeout=03:00:00
- Перезапустите платформу: /var/lib/visiology/scripts/run.sh --restart
Подобным образом добавьте еще несколько таблиц, данные которых вы хотите проанализировать:

В панели Данные таблицы сортируются по алфавиту (или по возрастанию, если названия начинаются с чисел).

Для удобства пользователей платформа запоминает подключения к базам данных. При добавлении дополнительных таблиц в модель данных вы можете воспользоваться сохраненными подключениями. В этом случае вам не придется заново указывать параметры подключения. Все, что вам нужно будет сделать – это выбрать подключение из списка, указать название таблицы и написать текст запроса:

Перед использованием существующего подключения рекомендуется проверить его работоспособность. Это позволит избежать ситуаций, когда вы отправляете сложный запрос, но соединение оказывается нерабочим из-за того, что изменилась база данных, или обновился логин или пароль.

Чтобы выполнить проверку, необходимо в окне создания запроса нажать на кнопку с тремя точками напротив требуемого подключения и выбрать пункт Проверить подключение в выпадающем меню. Система оповестит вас о результате проверки:

Булевые значения, содержащиеся в добавляемых таблицах, обрабатываются и записываются в базу данных как строковые значения. При необходимости передачи булевых значений в базу данных, вы можете изменить текст запроса, явно указав 1 или 0. Ниже пример запроса для PostgreSQL (для других СУБД он может быть другим):

select
  case
    when bool_column is true
    then 1 else 0
  end as bool_column
from bool_table

Булевые значения, содержащиеся в добавляемых таблицах, обрабатываются и записываются в базу данных как строковые значения. При необходимости передачи булевых значений в базу данных, вы можете изменить текст запроса, явно указав 1 или 0. Ниже пример запроса для PostgreSQL (для других СУБД он может быть другим):

select
  case
    when bool_column is true
    then 1 else 0
  end as bool_column
from bool_table

При загрузке данных с помощью JDBC следует учитывать, что если в запросе используется FULL JOIN, который объединяет данные таблиц, то в результате слияния могут появиться пустые ячейки, которые заполняются значением null. При этом, если в предложении SELECT используется колонка, которая не допускает использование значений null, то данные не загрузятся.

В данной ситуации, для успешной загрузки данных, в запросе необходимо использовать функцию coalesce, которая заменит null на пустую строку в поле. Например, для загрузки колонки brandname с отсутствующими значениями, в запросе необходимо указать select coalesce(brandname, '''') вместо select brandname.
Пример запроса:

select * from jdbc( 'jdbc:postgresql://84.252.140.141:5432/postgres?user=postgres&password=', 'postgres', 'select coalesce(brandname, ''''), brands.key as "bk", salesamount, facts.key as "fk" from brands full join facts on brands.key = facts.key;');

При отправке SQL-запроса вы можете добавлять комментарии. Поддержка комментариев реализована с учетом диалектов наиболее популярных баз данных:

MySQL
MariaDB
PostgreSQL
SQL Server
Oracle
SQLit
DB2
HSQLDB
Teradata
Greenplum
SAP HANA
ClickHouse

При этом для некоторых наименее популярных баз данных комментарии могут не поддерживаться.

Пример запроса с комментарием:

SELECT * FROM delivery; /* Загружает все данные из таблицы "delivery". */

Редактирование загрузчиков

Параметры запроса, которые использовались для загрузки таблицы, сохраняются в системе в виде загрузчиков. В дальнейшем, при необходимости, вы можете отредактировать эти параметры, например, изменив пароль, текст запроса и т.д. Данная возможность позволяет избежать необходимости создавать запрос заново, если, например, параметры подключения к базе данных изменились.

Для редактирования загрузчика (т.е. параметры для загрузки конкретной таблицы) необходимо выполнить следующие шаги:

Откройте набор данных, в котором вы хотите отредактировать загрузчик. В нашем примере это Магазин:
Нажмите кнопку с тремя точками напротив таблицы, загрузчик которой вы хотите отредактировать, и выберите Редактировать загрузчик в выпадающем меню:
В открывшемся диалоге внесите необходимые изменения и нажмите Сохранить:

Теперь при обновлении набора данных будут использованы обновленные параметры.
Подобным образом вы можете обновить параметры загрузчика на экране создания дашборда, нажав на кнопку с тремя точками напротив требуемой таблицы и выбрав Редактировать загрузчик в выпадающем меню:

Изменение набора полей с помощью SQL-запроса никак не отражается на построенных дашбордах (за исключением ситуации, когда вы удалите колонку, данные которой используются в одном из виджетов).
При редактировании строки запроса не рекомендуется использовать звёздочку, т.к. использование этого символа подразумевает, что структура таблицы не менялась с момента создания загрузчика. Если структура изменилась, визуализация данных может нарушиться. Указывайте все столбцы явно. Например:
Select ОбщаяСтоимость, Клиенты, Календарь, Скидки, Продажи, ID, Возврат, Магазины, from Факт.Продажи

Редактирование и удаление подключений

В отличие от загрузчиков, параметры которых относятся к конкретной таблице, редактирование подключения коснется всех загрузчиков, которые используют эту базу данных. Например, вы можете воспользоваться данной функциональностью, если хотите заменить базу данных для всех загрузчиков. Для редактирования подключения выполните следующие шаги:

Откройте набор данных, в котором вы хотите отредактировать подключение и нажмите Данные > Подключения в левом верхнем углу:
В появившемся окне нажмите кнопку с изображением карандаша напротив подключения, который вы хотите отредактировать:
В открывшемся диалоге внесите необходимые изменения и нажмите Сохранить:

Теперь при обновлении набора данных будут использованы обновленные параметры.

При необходимости удалить подключение, просто нажмите кнопку с изображением корзины напротив подключения, которое вы хотите удалить, а затем подтвердите удаление в всплывающем диалоге:

Просмотр загруженных таблиц

Загруженные таблицы также отображаются в панели Поля в правой стороне экрана:

Вы можете развернуть каждую таблицу, чтобы просмотреть список ее полей. Для этого нажмите на стрелку слева от названия таблицы:

Чтобы свернуть сразу все таблицы, нажмите кнопку Свернуть все в верхней части панели Данные:

Поля таблиц сортируются по алфавиту. Если название полей начинается с цифры, то сортировка происходит по возрастанию.

Для просмотра данных в загруженных таблицах перейдите в режим просмотра данных, нажав на соответствующую кнопку в левой стороне экрана, и выберите таблицу, данные которой вы хотите просмотреть:

Обратите внимание, что для каждого столбца указан тип используемых данных.

Вы также можете создавать дополнительные данные на основе существующих данных с помощью формул DAX и затем визуализировать их. Чтобы узнать как это сделать, перейти в раздел Создание мер.