0. Índice de contenidos.
- Introducción
- Entorno
- Creación de base de datos
- Creación de tablas.
- Carga de datos.
- Consulta de datos.
6.1 Consultas sencillas.
6.2 Consultas de dificultad intermedia.
6.3 Consultas más complejas.
6.3.1 Consulta sobre propiedades multivalor fechadas. - Conclusiones.
1. Introducción
En los últimos meses la palabra IA y, especialmente ChatGPT se encuentra en boca de todo el mundo, desde apocalípticos que piensan que provocará el fin del mundo con máquinas dominando a humanos, hasta gente que lo ve como una oportunidad para mejorar la productividad en sus trabajos, pasando por equidistantes que lo ven como la enésima moda pasajera.
En este tutorial vamos a intentar sacar provecho de las oportunidades que nos ofrece a día de hoy ChatGPT para facilitarnos esos trabajos en ocasiones repetitivos y tediosos de escritura de SQLs.
Para utilizar ChatGPT debemos tener cuenta en (https://openai.com/). En caso de no tenerla, nos podemos registrar desde la pagina principal de OpenAI y utilizar cualquiera de los mecanismos de login rápido que ofrecen.
Una vez estamos logueados, debemos indicar con qué modelo queremos interactuar, en nuestro caso con ChatGPT
Somos redirigidos a una pantalla donde tenemos un prompt disponible para comenzar a realizarle preguntas.
2. Entorno
El tutorial está escrito usando el siguiente entorno:
- Hardware: Portátil MacBook Pro 13′ (Apple M1 Pro, 32GB DDR4).
- Sistema Operativo: Mac OS Ventura 13.3.1
3. Creación base de datos
Tomaremos como base de datos para este tutorial PostgreSQL 15, levantamos un contenedor Docker con una base de datos llamada ia-test y password pass y nos conectamos a ella.
docker run --name postgres-ia-test -e POSTGRES_DB=ia-test -e POSTGRES_PASSWORD=pass -d -p5432:5432 postgres:15.2-alpine3.17
E informamos a ChatGPT que ésta será la base de datos que debe utilizar.
4. Creación de tablas
Vamos a comenzar con un ejemplo sencillo, le indicamos que queremos persistir información acerca de personas y sus vehículos.
CREATE TABLE person (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phone TEXT,
address TEXT
);
CREATE TABLE vehicle (
id SERIAL PRIMARY KEY,
make TEXT NOT NULL,
model TEXT NOT NULL,
year INTEGER NOT NULL,
person_id INTEGER REFERENCES person(id) ON DELETE CASCADE
);
Lo primero que nos llama la atención es que toma decisiones sobre los campos que debe de tener cada tabla, su tipo y su obligatoriedad, vamos a detallarle qué campos queremos en cada tabla y por otro lado darle unas premisas que debe de tener siempre en cuenta (id generados mediante secuencia y creación de índice en todas las foregins)
CREATE SEQUENCE person_id_seq START 1;
CREATE TABLE person (
id INTEGER PRIMARY KEY DEFAULT nextval('person_id_seq'),
name TEXT NOT NULL,
surname TEXT,
nif TEXT NOT NULL,
city TEXT NOT NULL,
UNIQUE (nif)
);
CREATE INDEX person_id_idx ON person(id);
CREATE SEQUENCE vehicle_id_seq START 1;
CREATE TABLE vehicle (
id INTEGER PRIMARY KEY DEFAULT nextval('vehicle_id_seq'),
brand TEXT NOT NULL,
model TEXT NOT NULL,
year INTEGER NOT NULL,
color TEXT NOT NULL,
person_id INTEGER NOT NULL REFERENCES person(id) ON DELETE CASCADE
);
CREATE INDEX vehicle_person_id_idx ON vehicle(person_id);
CREATE INDEX vehicle_id_idx ON vehicle(id);
Tras indicarle cómo queremos que nos genere las tablas, ChatGPT nos genera un nuevo script teniendo en cuenta nuestras premisas. Cabe destacar que es capaz de inferir que el campo nif debe ser único.
También vemos que genera un índice adicional para las PKs que no es necesario, en este caso deberíamos indicarle que esta práctica no es necesaria para que la tenga en cuenta para futuras creaciones.
Ejecutamos los scripts que nos ha generado en nuestra base de datos local con el fin de comenzar a explotar información.
5. Carga de de datos
Con las tablas ya creadas, le vamos a pedir que nos genere un script para popular ambas tablas, con 5 personas y 25 vehículos.
-- Insert 5 people
INSERT INTO person (name, surname, nif, city) VALUES
('John', 'Doe', '12345678A', 'New York'),
('Jane', 'Doe', '23456789B', 'London'),
('Bob', 'Smith', '34567890C', 'Paris'),
('Alice', 'Jones', '45678901D', 'Berlin'),
('Mike', 'Johnson', '56789012E', 'Tokyo');
-- Insert 25 vehicles, making sure each person has at least one vehicle
INSERT INTO vehicle (brand, model, year, color, person_id) VALUES
('Honda', 'Civic', 2010, 'Blue', 1),
('Toyota', 'Corolla', 2012, 'Red', 2),
('Ford', 'Mustang', 2018, 'Black', 3),
('Chevrolet', 'Camaro', 2016, 'Yellow', 4),
('BMW', 'M3', 2014, 'White', 5),
('Mercedes-Benz', 'S-Class', 2020, 'Silver', 1),
('Audi', 'A4', 2015, 'Gray', 2),
('Volkswagen', 'Golf', 2013, 'Green', 3),
('Nissan', 'Altima', 2011, 'Orange', 4),
('Subaru', 'Impreza', 2009, 'Purple', 5),
('Mazda', 'MX-5', 2017, 'Brown', 1),
('Hyundai', 'Sonata', 2019, 'Beige', 2),
('Kia', 'Optima', 2013, 'Bronze', 3),
('Volvo', 'XC90', 2016, 'Copper', 4),
('Tesla', 'Model S', 2021, 'Red', 5),
('Jeep', 'Wrangler', 2015, 'Green', 1),
('Porsche', '911', 2019, 'White', 2),
('Lamborghini', 'Aventador', 2018, 'Yellow', 3),
('Ferrari', '488', 2016, 'Black', 4),
('Bugatti', 'Chiron', 2020, 'Blue', 5),
('Rolls-Royce', 'Phantom', 2017, 'Silver', 1),
('Bentley', 'Continental GT', 2015, 'Brown', 2),
('Maserati', 'Quattroporte', 2014, 'Gray', 3),
('Aston Martin', 'DB11', 2018, 'Purple', 4),
('McLaren', '720S', 2021, 'Orange', 5);
Como curiosidad, es capaz de detectar el formato de un NIF, pero los valores que genera no son válidos. Incluso si le indicamos una url donde puede generar valores de ejemplo continúa generando valores erróneos.
Este ejemplo es muy sencillo, pero abre la puerta a indicarle a ChatGPT cuál es nuestro modelo de datos a partir de un script de creación de tablas y pedirle que genere scripts para popular ciertas tablas de cara a lanzar tests de integración o construir juegos de datos para entornos de prueba. Cuanta más información le demos en el prompt, más podremos afinar la carga de datos y más tiempo nos ahorraremos escribiendo scripts.
6. Consulta de datos
Una vez que tenemos nuestras dos tablas con datos vamos a comenzar a generar consultas, desde las más sencillas hasta consultas más tediosas que requieren bastante esfuerzo y tiempo por nuestra parte.
6.1 Consultas sencillas
Consultas para obtener todas las personas y vehículos ordenadas por atributos, sin problema 😉
SELECT *
FROM person
ORDER BY name, surname;
SELECT *
FROM vehicle
ORDER BY brand, model, color;
Vamos a complicar la consulta para obtener cuántos vehículos existen de cada color en cada ciudad.
SELECT city, color, COUNT(*) AS num_vehicles
FROM person
INNER JOIN vehicle ON person.id = vehicle.person_id
GROUP BY city, color;
6.2 Consultas de dificultad intermedia
Le preguntamos ahora por las ciudades que tienen algún vehículo blanco pero no tienen ningún vehículo negro a partir de 2018:
SELECT DISTINCT p.city
FROM person p
INNER JOIN vehicle v1 ON p.id = v1.person_id AND v1.color = 'white'
LEFT JOIN vehicle v2 ON p.id = v2.person_id AND v2.color = 'black' AND v2.year > 2018
WHERE v2.person_id IS NULL;
Vamos un paso más allá y queremos conocer el número de vehículos posteriores a 2015 por ciudad y color, y para cada registro conocer el año más antiguo de un vehículo de ese color en cualquier ciudad:
SELECT p.city, v.color, COUNT(*) AS num_vehicles, MIN(v2.year) AS oldest_vehicle
FROM person p
INNER JOIN vehicle v ON p.id = v.person_id
LEFT JOIN vehicle v2 ON v.color = v2.color
WHERE v.year > 2015 AND v2.year > 0
GROUP BY p.city, v.color;
6.3 Consultas más complejas
Por último vamos a pedirle que nos muestre por cada año entre el más antiguo y moderno de los años de los vehículos, el numero de vehículos agrupados por color que existen en cada ciudad, excluyendo aquellas ciudades que no tengan vehículos para ese año. Esta ya es una consulta que nos llevaría un buen rato escribirla y aquí la tenemos de forma inmediata:
WITH years AS (
SELECT generate_series(
(SELECT MIN(year) FROM vehicle),
(SELECT MAX(year) FROM vehicle)
) AS year
),
city_years AS (
SELECT p.city, v.year, v.color, COUNT(*) AS num_vehicles
FROM person p
INNER JOIN vehicle v ON p.id = v.person_id
GROUP BY p.city, v.year, v.color
),
city_years_filled AS (
SELECT y.year, c.city, COALESCE(cy.color, '') AS color, COALESCE(cy.num_vehicles, 0) AS num_vehicles
FROM years y
CROSS JOIN (SELECT DISTINCT city FROM person) c
LEFT JOIN city_years cy ON y.year = cy.year AND c.city = cy.city
WHERE cy.num_vehicles IS NOT NULL
)
SELECT year, city, color, num_vehicles
FROM city_years_filled
ORDER BY year, city, color;
6.3.1 Consulta sobre propiedades multivalor fechadas
Vamos a plantearle una nueva situación para explotar propiedades multivalor fechadas, hemos creado una tabla en la que almacenar las certificaciones asociadas a las personas de la tabla person de los ejemplos anteriores.
CREATE TABLE person_certification_dated
(
id BIGINT PRIMARY KEY,
person_id BIGINT NOT NULL,
certification_name VARCHAR(200) NOT NULL,
active BOOLEAN NOT NULL,
start_day DATE NOT NULL,
CONSTRAINT fk_person_cert_dated_person FOREIGN KEY (person_id) REFERENCES person (id)
);
El campo start_day indica la fecha a partir de la cual se asocia la certificación a la persona, y el campo active indica si la certificación está activa o no, por ejemplo, pensemos que la persona con id 1 se certifica el 1 de enero de 2020 en Java 1.8, siendo una certificación que se debe renovar anualmente y no lo hace hasta el 3 de marzo de 2023, se representaría de la siguiente forma:
person_id | certification_name | active | start_day 1 Java 1.8 true 2020-01-01 1 Java 1.8 false 2021-01-01 1 Java 1.8 true 2023-03-03
Le pedimos que nos genere una consulta para obtener las certificaciones activas para una persona a una fecha dada y el resultado no es el esperado, no es capaz de darse cuenta que tiene que quedarse con las certificaciones cuyo último cambio de estado anterior a la fecha dada sea al estado activo:
SELECT certification_name
FROM person_certification_dated
WHERE person_id = 1 AND active = true AND start_day
Le explicamos cómo funciona la tabla con un ejemplo y volvemos a solicitar que nos genere la consulta, en este caso sí lo hace correctamente
SELECT certification_name
FROM person_certification_dated
WHERE person_id = 1
AND active = TRUE
AND start_day
Este ejemplo demuestra que son necesarios ciertos conocimientos para validar el resultado devuelto por ChatGPT y pone de manifiesto la necesidad de definir buenos promts para obtener los resultados esperados. El rol de prompt engineering cada vez cobrará más protagonismo.
7. Conclusiones
Hemos visto cómo ChatGPT nos puede ahorrar tiempo y esfuerzo cómo desarrolladores a la hora de trabajar con base de datos, cuánto más claro y detallado sea el input más preciso será el resultado. Las posibilidades que aparecen son enormes:
- Generar todas las tablas del modelo de datos a partir de una definición funcional donde se encuentre detallada cada tabla con sus características.
- Obtener consultas para complicados informes a partir una definición en lenguaje natural.
- Construir juegos de datos en tiempo record con distintas casuísticas para cubrir todas las posibilidades en entornos de testing (integración, end to end, QA …)
Pero, ¿y qué ocurre con perfiles no técnicos? Aquí las ventajas son aún mayores, aparece la posibilidad de que una persona sin conocimiento de SQL pueda obtener datos y generar informes más o menos complejos, simplemente pidiéndole a ChatGPT que le genere las consultas SQL apropiadas dándole como input la definición del modelo de datos. ¿Cuántas llamadas y/o emails se ahorrarían entre Negocio y Desarrollo?
Podemos ir más allá y pensar en un plugin de ChatGPT para nuestro cliente de base de datos o IDE, que se conecte a la base de datos y no tengamos que darle input acerca del modelo de datos, simplemente pedirle consultas en lenguaje natural, ¿os animáis a desarrollarlo?
