tiempo - insertar datos en tablas relacionadas mysql java

Con Pentaho Kettle, ¿cómo puedo cargar varias tablas de una sola tabla y mantener la integridad referencial?

Question

Necesidad de cargar datos de un solo archivo con más de 100,000 registros en varias tablas en MySQL manteniendo las relaciones definidas en el archivo / tablas; es decir, las relaciones ya coinciden. La solución debería funcionar con la última versión de MySQL y debe usar el motor InnoDB; MyISAM no admite claves externas.

Soy completamente nuevo en el uso de la integración de datos Pentaho (también conocido como Kettle) y se agradecería cualquier puntero.

Podría agregar que es un requisito que las restricciones de clave externa NO estén deshabilitadas. Como entiendo que si hay algún error con la integridad referencial de la base de datos, MySQL no verificará la integridad referencial cuando las restricciones de la clave externa se activen nuevamente. FUENTE: 5.1.4. Variables del sistema del servidor - foreign_key_checks

Todos los enfoques deben incluir algunos de validación y una estrategia de reversión en caso de que una inserción falle, o no logre mantener la integridad referencial.

Nuevamente, soy completamente nuevo en esto y hago todo lo posible para proporcionar la mayor cantidad de información posible, si tiene alguna pregunta o solicitud de aclaración, solo avíseme.

Si puede publicar el XML desde los archivos kjb y ktr (trabajos / transformaciones), sería SUPER. Incluso podría buscar todos los comentarios / respuestas que hayas hecho en cualquier parte y votarlos ... :-) ... realmente, es muy importante para mí encontrar una respuesta para esto.

¡Gracias!

DATOS DE MUESTRA: Para elaborar mejor un ejemplo, asumamos que estoy tratando de cargar un archivo que contiene el nombre del empleado, las oficinas que ocuparon en el pasado y su historial de títulos de trabajo separados por una pestaña.

Expediente:

EmployeeName<tab>OfficeHistory<tab>JobLevelHistory
John Smith<tab>501<tab>Engineer
John Smith<tab>601<tab>Senior Engineer
John Smith<tab>701<tab>Manager
Alex Button<tab>601<tab>Senior Assistant
Alex Button<tab>454<tab>Manager

NOTA: La base de datos de una sola tabla está completamente normalizada (como puede ser una sola tabla) y, por ejemplo, en el caso de "John Smith" solo hay un John Smith; lo que significa que no hay duplicados que puedan dar lugar a conflictos en la integridad referencial.

El esquema de la base de datos MyOffice tiene las siguientes tablas:

Employee (nId, name)
Office (nId, number)
JobTitle (nId, titleName)
Employee2Office (nEmpID, nOfficeId)
Employee2JobTitle (nEmpId, nJobTitleID)

Así que en este caso. Las tablas deben verse como:

Employee
1 John Smith
2 Alex Button

Office
1 501
2 601
3 701
4 454

JobTitle
1 Engineer
2 Senior Engineer
3 Manager
4 Senior Assistant

Employee2Office
1 1
1 2
1 3
2 2
2 4

Employee2JobTitle
1 1
1 2
1 3
2 4
2 3

Aquí está el DDL de MySQL para crear la base de datos y las tablas:

create database MyOffice2;

use MyOffice2;

CREATE TABLE Employee (
      id MEDIUMINT NOT NULL AUTO_INCREMENT,
      name CHAR(50) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB;

CREATE TABLE Office (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  office_number INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE JobTitle (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  title CHAR(30) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE Employee2JobTitle (
  employee_id MEDIUMINT NOT NULL,
  job_title_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (job_title_id) REFERENCES JobTitle(id),
  PRIMARY KEY (employee_id, job_title_id)
) ENGINE=InnoDB;

CREATE TABLE Employee2Office (
  employee_id MEDIUMINT NOT NULL,
  office_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (office_id) REFERENCES Office(id),
  PRIMARY KEY (employee_id, office_id)
) ENGINE=InnoDB;

Mis notas en respuesta a la respuesta seleccionada:

DEBERES:

1. (a) Use los datos de muestra, cree un CSV cambiando <TAB> a delimitado por comas.
2. (b) Instale MySQL y cree una base de datos de muestra utilizando la muestra MySQL DDL
3. (c) Instale Kettle (está basado en Java y se ejecutará en cualquier cosa que ejecute Java)
4. (d) Descargar el archivo KTR

Flujo de datos por paso: (Mis notas)

1. Abra el archivo KTR en Kettle, haga doble clic en la "entrada del archivo CSV" y busque el archivo CSV que creó. El delimitador ya debe estar configurado a coma. Luego haga clic en Aceptar.
2. Haga doble clic en "Insertar empleados" y seleccione el conector DB, luego siga estas instrucciones para Crear una nueva conexión de base de datos

Answer 1

Reuní una transformación de muestra (haga clic con el botón derecho y elija guardar enlace) en función de lo que proporcionó. El único paso en el que me siento un poco incierto es en las últimas entradas de la tabla. Básicamente estoy escribiendo los datos de unión en la tabla y dejándolos fallar si ya existe una relación específica.

Nota:

Esta solución no cumple realmente con "Todos los enfoques deben incluir algunos de validación y una estrategia de reversión en caso de que una inserción falle o no mantenga la integridad referencial". criterios, aunque probablemente no fallará. Si realmente desea configurar algo complejo, podemos hacerlo, pero esto definitivamente debería hacer que comience con estas transformaciones.

Flujo de datos por paso

1. Comenzamos leyendo en tu archivo. En mi caso lo convertí a CSV pero la pestaña también está bien.

2. Ahora vamos a insertar los nombres de los empleados en la tabla Empleado usando una combination lookup/update . Después de la inserción, agregamos el id empleado a nuestro flujo de datos como id y eliminamos el EmployeeName de EmployeeName de la secuencia de datos.

3. Aquí solo estamos usando el paso Seleccionar valores para cambiar el nombre del campo de id a employee_id

4. Inserte los títulos de los trabajos como lo hicimos con los empleados y agregue la identificación del título a nuestro flujo de datos, eliminando también el JobLevelHistory del mismo.

5. Simple cambio de nombre del título id a title_id (vea el paso 3)

6. Inserte oficinas, obtenga identificaciones, elimine OfficeHistory de la transmisión.

7. Simple cambio de nombre del ID de office a office_id (vea el paso 3)

8. Copie los datos del último paso en dos flujos con los valores employee_id,office_id y employee_id,title_id respectivamente.

9. Utilice una inserción de tabla para insertar los datos de unión. Lo tengo seleccionado para ignorar los errores de inserción, ya que podría haber duplicados y las restricciones de PK harán que algunas filas fallen.

Tablas de salida