Назначение решения

Данный проект решает задачу автоматического парсинга json-структур (файлов) и перенос содержащихся в них данных в таблицы реляционной БД. Структура БД формируется автоматически на основе анализа структуры json файла.
Каких-либо ограничений на структуры обрабатываемых json’ов не накладывается.

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

Решение может быть использовано как для однократной загрузки json-файла в БД, так и для регулярной загрузки файлов сходной структуры (например, в озеро данных). Если от загрузки к загрузке структура файлов может несколько изменяться, то эти изменения будут обработаны автоматически таким образом, что обновленная структура БД будет сопоставима с ее предыдущими версиями (структура БД расширяется, но не сокращается).

Комплексные внутренние json объекты обрабатываемого файла трансформируются в реляционные таблицы, а входящие в состав объектов элементарные пары ключ-значение определяют структуру таблиц. Если значением какого-то ключа является объект, то формируется еще одна таблица и т.д. Таблицы связываются между собой посредством добавления специализированных полей, позволяющих реализовать связи вида “один-ко-многим”. Эта связь хорошо подходит для json-структур, поскольку каждый вложенный объект имеет не более одного родительского объекта и может содержать неограниченное число дочерних объектов.

Если вложенные объекты объединены в массив, то каждый элемент массива анализируется на предмет его структуры. Для всех элементов массива, имеющих общую структуру создается единственная таблица. Для Элементов массива, имеющих разную структуру создаются разные таблицы. Однако при этом осуществляется анализ различий в структурах этих таблиц. Если набор полей одной таблицы является подмножеством другой, то создается единственная таблица с более широким набором полей.

Общее описание реализации

Генерация структуры таблиц

Имена и колонок в них максимально соответствуют наименованиям ключей тегов исходного json файла. При этом наименование ключа тега json файла анализируется на предмет соответствия правилам наименования артефактов используемой БД. В случае несоответствия выполняются необходимые преобразования наименования ключа json тега для приведения его к корректному наименованию артефакта БД (сокращается размер строк до требуемого, цифры при необходимости заменяются на буквы и т.п.). р На основе анализа json файла формируется еще один файл (так же в формате json), описывающий структуру БД, куда будут помещены данные. Этот файл содержит массив объектов, которые описывают таблицы БД связывают таблицы БД с тегами исходного json файла.
Каждый элемент этого массива содержит:

1. путь к тегу исходного файла;
2. наименование таблицы, куда будут помещены данные этого объекта;
3. наименование родительской таблицы;
4. список ключей объекта и соответствующих им полей в таблице.

В каждую таблицу вставляется три дополнительных поля

1. iiiddd – глобально уникальный идентификатор строки в таблице.
2. parent_iiiddd — идентификатор родительской записи (в другой таблице)
3. job_iiiddd — идентификатор задания, выполнившего загрузку. Всем данным, загруженным из одного файла, будет присвоен одинаковый идентификатор задания.

iiiddd и parent_iiiddd заполняются автоматически. Значение для job_iiiddd задается пользователем при инициализации новой процедуры загрузки.

Выглядит это так (пример для одного объекта):

{"tag":  
{ 	"tagPath": ["root"],   
		"parentTAGSQLName": "",   
		"tagName": "root",  
		"sqlName": "root",  
		"attrs": [  
			{  
				"attrName": "iiiddd",  
				"attrSqlName": "iiiddd"  
			},  
			{ 	"attrName": "parent_iiiddd",  
				"attrSqlName": "parent_iiiddd"  
			},  
			{  
				"attrName": "job_iiiddd",  
				"attrSqlName": "job_iiiddd" 
			},  
			{ 
				"attrName": "dateUploadSession",
				"attrSqlName": "dateUploadSession" 
			}, 
			{ 
				"attrName": "addressee", 
				"attrSqlName": "addressee" 
			}, 
			{ 
				"attrName": "sender", 
				"attrSqlName": "sender" }, 
			{ 
				"attrName": "protocolVersion", 
				"attrSqlName": "protocolVersion" 
			}, 
			{ 
				"attrName": "dateSendingPackage",
				"attrSqlName": "dateSendingPackage" 
			} 
		] 
	}
}

Указанная выше структура, описывающая соответствие между тегами исходного json файла и таблицами БД сохраняется в файле, путь к которому указывает пользователь библиотеки. Файл можно отредактировать, поменяв названия таблицы и их полей. Для того, что бы при последующей загрузке подобного файла данные изменения не потерялись, этот файл сохраняется и передается как параметр при очередной загрузке. Если, очередной json-файл содержит расширенный набор атрибутов по сравнению с предыдущей загрузкой, то они будут учтены, а выходной файл описывающий структур БД и ее связь со входным json’ом, будет являться объединением, как текущего, так и предыдущего описания.

Генерация DDL утверждений для создания таблиц

Далее на основе описания соответствия между объектами обрабатываемого файла и артефактами БД генерируются DDL утверждения, позволяющие создать таблицы БД. Генерируются скрипты для создания таблиц и ограничений в виде внешних ключей. Эти DDL утверждения можно сохранить в файл или получить в виде строки. Скрипты генерируются таким образом, что они корректно обрабатывают ситуацию, когда создаваемый объект уже есть в БД. Ошибок при этом не возникает, ранее созданные артефакты БД не изменяются и сохраняются.

Генерация SQL утверждений для занесения данных в таблицы

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

Выполнение DDL/SQL утверждений

Проект, помимо описанного выше, содержит модуль, позволяющий исполнять DDL/SQL скрипты. Для его использования требуется описать параметры подключения к бд в json файле следующей структуры:

{ 
	"host" : "localhost", 
	"port" : "5432", 
	"database" : "nafta", 
	"schema" : "public", 
	"user" : "postgres", 
	"password" : "XXXXXX" 
}

Использование библиотеки

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

<dependency>
<groupId>io.github.kiouri</groupId>
<artifactId>jsondb</artifactId>
<version>0.0.2</version>
</dependency>

nexus repositori manager
maven central repository

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

В пакете com.kiouri.jsontodb.usagesamples содержаться примеры использования данного решения

com.kiouri.jsontodb.usagesamples.sample1
Пример является пошаговым, максимально детальным примером, описывающим работу с приложением и сохраняющим в виде файлов все описанные выше результаты.

com.kiouri.jsontodb.usagesamples.sample2
Пример является сокращенной версией предыдущего — промежуточные результаты в виде файлов не сохраняются.

com.kiouri.jsontodb.usagesamples.sample3
Пример показывает, как можно организовать обработку набора json файлов, расположенных в одной директории.
Внимание! После обработки файлы удаляются.

Описание использования на примере sample1

com.kiouri.jsontodb.usagesamples.sample1
Далее будет подробно рассмотрен упоминавшийся выше пример sample1, включенный в состав библиотеки. Код примера находится в пакете com.kiouri.jsontodb.usagesamples.sample1 Полнофункциональный пример разбит на четыре шага, функционал каждого из которых инкапсулирован в соответствующем классе.

Шаг 1: Генерация описания структуры БД

com.kiouri.jsontodb.usagesamples.sample1.Step11
Класс Step11 примера предназначен для формирования структуры (в json формате), описывающей связь json объектов исходного файла с таблицами и полями БД. За реализацию этого функционала отвечает класс TagsDescListGenerator.
Создание экземпляра класса TagsDescListGenerator:

TagsDescListGenerator tagsTreeGenerator = new TagsDescListGenerator(new ToPostgresNameConvertor);  

В качестве параметра конструктору этого класса передается экземпляр класса ToPostgresNameConvertor. Этот класс реализует интерфейс IToDBNameConvertor, состоящий из одного метода:

public String nameToDBName(String inString);

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

tagsTreeGenerator.generateTagsDescFileFromJsonFiles(pathToSrcJson, pathToDescList, pathToDescList);

Предпоследний параметр конструктора указывает то, в какую выходную директорию будет сохранен результирующий файл.
Последний параметр содержит информацию о том, где находится файл, являющийся результатом предыдущего сеанса работы. Если предыдущего сеанса не было (первичная обработка json файла данного формата), то этому параметру присваивается значение null. В данном примере параметрам, отвечающим за входной и выходной файлы присвоены одинаковые значения. В процессе работы вновь создаваемый файл будет перезаписывать предыдущий. В примере файл результата записывается в ту же директорию, где находится анализируемый json файл.
При необходимости можно переименовать содержащиеся в выходном файле наименования таблиц и полей. Если будет выполнятся повторная обработка файла подобной структуры, то для того, чтобы изменения в наименованиях были бы сохранены и повторно применены, требуется сохранить отредактированный файл и передать путь к нему в качестве параметра упомянутому выше методу generateTagsDescFileFromJsonFiles класса TagsDescListGenerator.

Шаг 2: Генерация DDL утверждений для создания артефактов БД

com.kiouri.jsontodb.usagesamples.sample1.Step12
Класс Step12 примера предназначен для формирования ddl скриптов, отвечающих за формирование необходимых артефактов БД (таблиц и внешних ключей).
За формирование DDL скриптов отвечает класс DDLGenerator. Для создания его экземпляра используется конструктор, которому в качестве параметра передается экземпляр класса, реализующего интерфейс IDdlDBStatementGenerator.

DdlPostgresStatementGenerator ddlStatementGenerator = new DdlPostgresStatementGenerator();
DDLGenerator ddlGenerator = new DDLGenerator(ddlStatementGenerator);

Класс, реализующий интерфейс IDdlDBStatementGenerator отвечает за генерацию ddl скриптов, предназначенных для создания таблиц и внешних ключей с учетом специфики используемой БД. Интерфейс содержит два метода.
Метод

public String generateCreateTableIfNotExistsDDL(String tableName, List<String> columns);

отвечает за создание таблиц. Метод

public String createForeignkey (String childTable, String parentTable);

отвечает за создание внешних ключей. Непосредственно для генерации ddl скриптов используется метод:

ddlGenerator.generateDDLFile(pathToDescList, pathToGeneratedDDL); 

Методу передаются два параметра:

1. Путь к сгенерированному на предыдущем шаге файлу, описывающему связь тегов обрабатываемого json файла и таблиц БД.
2. Путь к файлу, содержащему ddl скрипты, являющийся результатом работы описываемого метода.
   В данном примере выходной файл записывается в ту же директорию, где расположен обрабатываемый json файл.
   В составе библиотеки Имеется разновидность данного метода, возвращающая результат в виде строки. DDL утверждения генерируются таким образом, что существующие в БД артефакты не затрагиваются, новые артефакты создаются. Пример скрипта по созданию таблицы:

CREATE TABLE IF NOT EXISTS documents(
id VARCHAR PRIMARY KEY);
ALTER TABLE documents
ADD COLUMN IF NOT EXISTS parent_id VARCHAR;
ALTER TABLE documents
ADD COLUMN IF NOT EXISTS job_id VARCHAR;
ALTER TABLE documents
ADD COLUMN IF NOT EXISTS documentType VARCHAR;
ALTER TABLE documents
ADD COLUMN IF NOT EXISTS myLineNumber VARCHAR;

Шаг 3: Выполнение DDL скриптов

com.kiouri.jsontodb.usagesamples.sample1.Step13
Класс Step13 примера предназначен для исполнения ddl скриптов. Параметры подключения к БД описываются в файле DBConfig.json. Формат этого файла приведен выше. Исполнение ddl/sql скриптов осуществляется с использованием класса SQLScriptExecutor. Он создается посредством конструктора, которому в качестве параметра передается экземпляр класса, реализующего интерфейс IDBConnProducer.

IDBConnProducer postgresConnProducer = new PostgresConnProducer();
SQLScriptExecutor sqlScriptExecutor = new SQLScriptExecutor(postgresConnProducer);

Класс, реализующий данный интерфейс предназначен для создания jdbc подключения к БД. Интерфейс IDBConnProducerсодержит два метода:

public Connection getConnection(String pathToJsonConfig)    
  throws IOException, ClassNotFoundException, SQLException;

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

public Connection getConnection(String pathToJsonConfig)    
  throws IOException, ClassNotFoundException, SQLException;

Создание jdbc подключения на базе параметров, подключения, передаваемых непосредственно в качестве параметров метода.

Собственно, исполнение ddl скрипта

sqlScriptExecutor.executeSQLScriptFromFile(pathToGeneratedDDL, pathToDBConfig);

Первый параметр pathToGeneratedDDL это путь к файлу, содержащему скрипты, которые надлежит исполнить. Второй параметр pathToDBConfig путь к конфигурационному файлу, содержащему параметры подключения.

SQLScriptExecutor включает в свой состав метод, позволяющий выполнять скрипты не из файла, а из строки, переданной в качестве параметра.

Шаг 4: Подготовка SQL скриптов для занесения данных в БД

com.kiouri.jsontodb.usagesamples.sample1.Step14
Класс Step14 примера предназначен для подготовки SQL скриптов, обеспечивающих загрузку данных в БД. Генерацию скриптов обеспечивает класс DataLoader. В примере для генерации скриптов используется метод:

public void generateInsSQLStmtsFromFileToFile(String pathToSrcJson, String pathToDescList, String pathToGeneratedSQL, String jobID) throws Exception    

Данный метод принимает на вход следующие параметры:
pathToSrcJson - путь к загружаемому json файлу pathToDescList - путь к файлу описывающему структуру БД в привязке к json объектам анализируемого файла (этот файл был получен на шаге 1 этого примера).
pathToGeneratedSQL - путь к результирующему SQL файлу, содержащему генерируемые SQL скрипты для добавления данных в БД. jobID - Идентификатор job'а которым маркируются все данные, загружаемые посредством sql утверждений, сгенерированных нна данном этапе. Ранее упоминалось, что все таблицы дополняются полем метаданных job_iiiddd. В это поле и заносится соответствующая информация, передаваемая в параметре jobID описываемого метода.

Шаг 5: Выполнение SQL скриптов для занесения данных в БД

com.kiouri.jsontodb.usagesamples.sample1.Step5
Данный шаг примера аналогичен описанному выше шагу 3, на котором исполняются ddl скрипты. В данном случае исполняются sql скрипты, сгенерированные на предыдущем шаге.

SQLScriptExecutor sqlScriptExecutor = new SQLScriptExecutor(postgresConnProducer);   
sqlScriptExecutor.executeSQLScriptFromFile(pathToGeneratedSQL, pathToDBConfig);