Skip to main content

library for downloading csv/xls into vertica

Project description

Vertica Loader

Высокоуровневое описание

Для упрощения загрузки плоских файлов формата xls, xlsx, csv в узел данных (вертику) был разработан данный Python-загрузчик, позволяющий загружать множество файлов в БД в одну итерацию. На вход он принимает несколько файлов (примеры в приложении к странице): Исходный файл с данными для загрузки Маппинг полей исходного файла на поля таблицы в БД Json-файл с метаданными Файл с настройками соединения с БД Файл с общей конфигурацией В текущем виде загрузчик представляет из себя Python-модуль, сканирующий заданную в конфигурационном файле папку на предмет наличия в ней json'ов с описанием источников. При их обнаружении загрузчик начинает последовательную обработку. Каждый json-файл содержит метку соединения (описанного в конфигурационном файле загрузчика), название файла с маппингом и название файла для непосредственной загрузки. Один json описывает один файл для загрузки, при этом один маппинг можно использовать для загрузки любого количества файлов. Загрузчик считывает файл с маппингом, находит там данные, относящиеся к текущему загружаемому файлу, выполняет переименование полей входного файла или их перестановку (в зависимости от заполнения маппинга), затем подключается к базе данных, чтобы сравнить типы данных в ней и в маппинге. При расхождении выдается ошибка, и загрузка файла не производится. При отсутствии расхождения выполняется чтение файла и его загрузка (в зависимости от указанного в json способа это производится через snapshot, insert или merge, см соответствующий раздел описания). Затем загрузчик выдает в лог количество принятых и отвергнутых базой строк и переходит к следующему файлу. При первом запуске в папку /log генерируются ddl для всех таблиц, в которые грузятся текущие файлы.

Установка

  1. Заберите свежую версию загрузчика из гитлаба https://gitlab.dev002.local/dwh/infra/vertica_loader/blob/MVP_UKD-1392/
  2. Скопируйте скрипты загрузчика в какую-нибудь папку. Отредактируйте файл конфигурации (VerticaLoaderConf.json) и файл с настройками подключения (connections.json): укажите свои параметры подключения к базе и пути к файлам для загрузки (подробное описание формата файлов ниже).
  3. Установите python 3.7.
  4. Через командную строку с помощью pip install установите библиотеки pandas, xlrd, vertica_python, croniter (для загрузки в PostgreSQL – также psycopg2 и sqlalchemy). При проблемах с корпоративным прокси скачать пакеты и установить вручную.
  5. Запустите консоль, перейдите в директорию с загрузчиком и выполните verticaLoad.py. При запуске для несуществующей в базе таблицы скрипт выведет код для ее создания. Создайте таблицу, затем запустите скрипт еще раз – данные должны загрузиться. [Vertica] Excel load - Файловая загрузка из .xlsx

Формат метаданных источников

{
   "source_test" :
   {
      "source_system" : "cloud",
      "file_name" : "liquidity.xlsx",
      "file_mask_format": "?liquidity*.xlsx",
      "file_type" : "xlsx",
      "fmd_file_name" : "fmd.xlsx",
      "sheets" : "*",
      "fmd_sheets": "MAPPING",
      "encoding" : "utf-8",
      "schedule" : "*/5 * * * *",
      "method" : "snapshot",
      "csv_delimiter": ";",
      "csv_quotation": 0,
      "quotechar": "~",
      "skiprows": -1,
      "read_columns": "B:AE, BC, BG",
      "filename_attr": "_FILENAME_",
      "load_datetime_attr": "_LOAD_DATETIME_"
 }
}

Файлы с метаданными следует записывать в кодировке utf8 с расширением .json. Путь к папке с файлами следует прописать в VerticaLoaderConf.json, сами json-файлы могут иметь любые названия. source_system: целевая система (база). Должна быть описана в файле с настройками подключения (connections.json).

  • file_name: имя файла (без пути), который загружаем. Пока файлы ищутся в одной директории, указанной в общей конфигурации (VerticaLoaderConf.json).
  • file_type: xlsx или csv
  • fmd_file_name: имя файла (без пути), содержащего маппинг(и).
  • sheets: имя листа Excel-файла, который нужно грузить. Если указано "*", грузим первый. Вписать можно только один лист. Если нужно загрузить несколько листов из одного файла, под них нужно создать несколько json-файлов.
  • fmd_sheets: название листа с маппингом в соответствующем файле.
  • enconding: кодировка csv. По умолчанию 'utf8'/
  • schedule: зарезервировано на будущие версии.
  • method: принимает значения snapshot, insert, merge. Snapshot транкейтит целевую таблицу и копирует в нее данные, insert копирует без предварительной очистки, merge мерджит по ключевым полям.
  • quotechar:
  • skiprows: количество строк, которое нужно пропустить до начала заголовка. По умолчанию -1, что означает ноль пропущенных строк. Если в файле одна пустая строка перед заголовком, значение должно быть 1, и так далее.
  • read_columns: позволяет выбрать диапазон загружаемых колонок (инклюзивно) – например, если первые несколько столбцов не заполнены или заполнены мусором. Также можно применять для выборочной загрузки столбцов, когда некоторые из них пользователю не нужны. Разрешается указание нескольких диапазонов через запятую. Если строка с параметром отсутствует, пытаемся парсить все.
  • filename_attr - имя столбца, куда в целевой таблице будет записано имя файла. Если в маппинге не указаны поля метаданных, этот параметр не учитывается. Если в маппинге поля метаданных указаны, а здесь - нет, будет использовано имя по умолчанию для совместимости - FILENAME. Имя здесь должно совпадать с именем в маппинге.
  • load_datetime_attr - имя столбца, куда в целевой таблице будет записано время загрузки текущего батча. Если в маппинге не указаны поля метаданных, этот параметр не учитывается. Если в маппинге поля метаданных указаны, а здесь - нет, будет использовано имя по умолчанию для совместимости - LOAD_DATETIME. Имя здесь должно совпадать с именем в маппинге.
  • csv_delimiter: разделитель записей в csv. По умолчанию ;
  • csv_quotation: оборачивание записей csv в кавычки. ПОКА НЕ ИМПЛЕМЕНТИРОВАНО
  • file_mask_format: параметр указывать только в случаях, когда необходима маска для имени файла. Допускается применение ? (заменяет один неизвестный символ) и * (заменяет произвольное количество неизвестных символов). В случае обнаружения нескольких попадающих под маску файлов загрузится самый старый. Пустая строка или отсутствующий параметр - маска применяться не будет, загрузчик начнет искать точное соответствие имени файла. Проверить корректность формата файла можно по ссылке http://jsonlint.com

Формат маппинга

Образец заполненного маппинга – во вложении (fmd.xlsx).

  • file_source: имя файла с данными (с расширением)
  • file_attribute_name: имя атрибута в файле
  • file_attribute_number: порядковый номер атрибута в файле, альтернатива file_attribute_name. Если файл содержит одинаковые или пустые названия атрибутов, необходимо заполнять только порядковые номера, если не содержит – только file_attribute_name.
  • Schema: название схемы в базе (должна существовать до загрузки)
  • Table: название таблицы в базе (должна существовать до загрузки)
  • Column: название атрибута в базе
  • DataType: тип данных атрибута
  • Sheet: название листа в исходном файле
  • PK: первичный ключ, опционально
  • NotNull: признак ненулевого поля, опционально
  • OrderBy: сортировка, опционально
  • SegmentedBy:
  • is_metadata: признак метаданных, необходимо указывать "1" для технических полей FILENAME и LOAD_DATETIME.

Примечания: Один файл может содержать неограниченное количество маппингов (подряд, на одном листе). Типы данных должны быть заполнены заглавными буквами. Технические поля следует добавлять после атрибутов каждого файла, называть их LOAD_DATETIME и FILENAME, а также указывать значение “1” в поле is_metadata. Поля типа BOOLEAN не должны содержать значения "да/нет", "yes/no" и так далее. Если есть необходимость загружать подобные строки, следует использовать тип VARCHAR(n). Для полей типа VARCHAR(n) нужно указывать размерность с учетом того, что русские символы занимают по 2 байта на символ. Не следует указывать размерность VARCHAR по максимальной длине строки в конкретном файле – лучше проанализировать атрибут и выяснить, какого рода данные в нем хранятся и какую максимальную размерность могут принимать. При чтении csv можно указывать только имена заголовков, которые надо считывать. Т.е. если в файле 10 колонок, можно заполнить маппинг для 5, и загрузится лишь 5.

Формат файла настроек соединения

Образец файла – во вложении (connections.json).

{
   "cloud" :
   {
      "host" : "s001cd-db-vr01.dev002.local",
      "db" : "devdb",
      "userType" : "explicit",
      "user" : "хххх",
      "passwordType" : "explicit",
      "password" : "ххххх",
      "passwordEncryption": 1,
      "rejected_table_name": "STG_VERTICA_LOADER.REJ_DATA"
   }
}
  • host: адрес сервера БД
  • db: имя БД
  • userType: env или explicit
  • user: логин пользователя
  • passwordType: может принимать значение explicit и env. В кспд использовать режим explicit запрещено.
  • password: пароль пользователя
  • passwordEncryption: если 1, то пароль должен быть закодирован в base64, если 0, то в открытом виде
  • rejected_table_name: название таблицы в БД, куда попадут не прогрузившиеся записи с сообщением об ошибке. Если таблица не создана (и у пользователя нет прав на ее создание) при записи произойдет ошибка, поэтому в случае невозможности создания rejected-таблицы этот атрибут из настроек следует исключить. При использовании режима explicit для указания пароля и логина следует использовать параметры user и password. Для режима env необходимо заполнить переменные среды именем пользователя и паролем, а затем указать названия этих переменных в параметрах userEnvVar и passwordEnvVar. В одном файле с настройками соединения можно задать много соединений с уникальными именами.

Формат файла общей конфигурации

Образец файла – во вложении (VerticaLoaderConf.json).

{
      "paths": {"1" : {"path" : "/Users/dio/Downloads/vertica_loader/num_test",
       "data_path": "/Users/dio/Downloads/vertica_loader/num_test",
       "fmd_path": "/Users/dio/Downloads/vertica_loader/num_test"},
      "2":  {"path" : "/Users/dio/Downloads/vertica_loader/counterparty",
       "data_path": "/Users/dio/Downloads/vertica_loader/counterparty",
       "fmd_path": "/Users/dio/Downloads/vertica_loader/counterparty"}},
      "log_dir": "/Users/dio/Downloads/logs",

      "log_level": "debug",
      "move_loaded_files": "false",
      "blocker_path": "lock_db.json"

}
  • path: путь к папке с метаданными (json-файлами)
  • data_path: путь к папке с данными для загрузки
  • fmd_path: путь к папке с маппингом.
  • log_level: детализация логирования. debug, info, error. По умолчанию в пользовательской версии выставлен error с минимальной информативностью.
  • move_loaded_files: при true файлы будут перемещаться в success/failed после загрузки. При false файлы останутся на месте. По умолчанию true.
  • log_dir: директория, в которую будет писаться лог. В подпапку /dir запишутся ddl для загружаемых таблиц
  • blocker_path - путь к локальной бд, хранящей список загружающихся в данный момент файлов. Таким образом предотвращается одновременная загрузка тех же самых данных. Все эти файлы могут лежать и в одной папке. Запуск загрузки из airflow и других внешних систем Запуск из внешних систем осуществляется через скрипт verticaConExt.py, в котором загрузчику передаются данные, штатно содержащиеся в VerticaLoaderConf.json и connections.json.

Контакты

Project details


Release history Release notifications

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for ukd-vertica-loader, version 0.0.12
Filename, size File type Python version Upload date Hashes
Filename, size ukd_vertica_loader-0.0.12-py3-none-any.whl (24.5 kB) File type Wheel Python version py3 Upload date Hashes View
Filename, size ukd_vertica_loader-0.0.12.tar.gz (27.4 kB) File type Source Python version None Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page