El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Magatzem de dades amb PostgreSQL

Següent ►
►

En aquest tema, explorarem com utilitzar PostgreSQL per a la creació i gestió d'un magatzem de dades. Un magatzem de dades és una base de dades dissenyada per a la consulta i anàlisi de grans volums de dades històriques. A diferència de les bases de dades transaccionals, que estan optimitzades per a operacions de lectura i escriptura ràpides, els magatzems de dades estan optimitzats per a consultes complexes i anàlisi de dades.

Objectius del tema

  • Entendre els conceptes bàsics d'un magatzem de dades.
  • Aprendre a dissenyar un esquema de magatzem de dades.
  • Implementar un magatzem de dades utilitzant PostgreSQL.
  • Optimitzar el rendiment del magatzem de dades.

Conceptes bàsics del magatzem de dades

Característiques principals

  1. Integració de dades: Recopila dades de diverses fonts.
  2. Històric de dades: Emmagatzema dades històriques per a l'anàlisi a llarg termini.
  3. Optimització per a consultes: Dissenyat per a consultes complexes i anàlisi de dades.
  4. No volàtil: Les dades no es modifiquen un cop s'han carregat al magatzem.

Components principals

  • ETL (Extracció, Transformació i Càrrega): Procés de recopilació de dades de diverses fonts, transformació de les dades en un format adequat i càrrega al magatzem de dades.
  • Esquema de magatzem de dades: Estructura de la base de dades que inclou taules de fets i taules de dimensions.
  • OLAP (Processament Analític en Línia): Tecnologia que permet realitzar consultes complexes i anàlisi de dades.

Disseny d'un esquema de magatzem de dades

Esquema estrella

L'esquema estrella és un dels dissenys més comuns per a magatzems de dades. Consta d'una taula central de fets i diverses taules de dimensions que es connecten a la taula de fets.

Taula de fets

  • Emmagatzema dades quantitatives (mètriques) que es volen analitzar.
  • Conté claus foranes que es relacionen amb les taules de dimensions.

Taules de dimensions

  • Emmagatzemen dades descriptives que proporcionen context a les dades de la taula de fets.
  • Contenen claus primàries que es relacionen amb les claus foranes de la taula de fets.

Exemple d'esquema estrella

Taula de fets: sales

id date_id product_id store_id quantity total_amount
1 20230101 101 1 5 100.00

Taula de dimensions: date

date_id date year month day
20230101 2023-01-01 2023 1 1

Taula de dimensions: product

product_id name category price
101 Widget A Gadgets 20.00

Taula de dimensions: store

store_id name location
1 Store 1 City A

Implementació d'un magatzem de dades amb PostgreSQL

Creació de taules

-- Taula de fets
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    date_id INT,
    product_id INT,
    store_id INT,
    quantity INT,
    total_amount NUMERIC,
    FOREIGN KEY (date_id) REFERENCES date(date_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (store_id) REFERENCES store(store_id)
);

-- Taula de dimensions: date
CREATE TABLE date (
    date_id INT PRIMARY KEY,
    date DATE,
    year INT,
    month INT,
    day INT
);

-- Taula de dimensions: product
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    category VARCHAR(255),
    price NUMERIC
);

-- Taula de dimensions: store
CREATE TABLE store (
    store_id INT PRIMARY KEY,
    name VARCHAR(255),
    location VARCHAR(255)
);

Inserció de dades

-- Inserir dades a la taula de dimensions: date
INSERT INTO date (date_id, date, year, month, day) VALUES
(20230101, '2023-01-01', 2023, 1, 1);

-- Inserir dades a la taula de dimensions: product
INSERT INTO product (product_id, name, category, price) VALUES
(101, 'Widget A', 'Gadgets', 20.00);

-- Inserir dades a la taula de dimensions: store
INSERT INTO store (store_id, name, location) VALUES
(1, 'Store 1', 'City A');

-- Inserir dades a la taula de fets: sales
INSERT INTO sales (date_id, product_id, store_id, quantity, total_amount) VALUES
(20230101, 101, 1, 5, 100.00);

Optimització del rendiment

Índexs

Els índexs poden millorar significativament el rendiment de les consultes en un magatzem de dades.

-- Crear índexs a les claus foranes de la taula de fets
CREATE INDEX idx_sales_date_id ON sales(date_id);
CREATE INDEX idx_sales_product_id ON sales(product_id);
CREATE INDEX idx_sales_store_id ON sales(store_id);

Particionament

El particionament de taules pot ajudar a gestionar grans volums de dades dividint una taula gran en taules més petites.

-- Crear una taula particionada per any
CREATE TABLE sales_partitioned (
    id SERIAL PRIMARY KEY,
    date_id INT,
    product_id INT,
    store_id INT,
    quantity INT,
    total_amount NUMERIC
) PARTITION BY RANGE (date_id);

-- Crear particions per any
CREATE TABLE sales_2023 PARTITION OF sales_partitioned
    FOR VALUES FROM (20230101) TO (20240101);

Exercici pràctic

Exercici

  1. Crea un esquema estrella per a un magatzem de dades que emmagatzemi informació sobre les vendes de llibres. Inclou taules de dimensions per a date, book i store.
  2. Inserta dades a les taules de dimensions i a la taula de fets.
  3. Crea índexs per a les claus foranes de la taula de fets.
  4. Implementa particionament per any a la taula de fets.

Solució

-- Taula de fets: book_sales
CREATE TABLE book_sales (
    id SERIAL PRIMARY KEY,
    date_id INT,
    book_id INT,
    store_id INT,
    quantity INT,
    total_amount NUMERIC,
    FOREIGN KEY (date_id) REFERENCES date(date_id),
    FOREIGN KEY (book_id) REFERENCES book(book_id),
    FOREIGN KEY (store_id) REFERENCES store(store_id)
);

-- Taula de dimensions: date
CREATE TABLE date (
    date_id INT PRIMARY KEY,
    date DATE,
    year INT,
    month INT,
    day INT
);

-- Taula de dimensions: book
CREATE TABLE book (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author VARCHAR(255),
    genre VARCHAR(255),
    price NUMERIC
);

-- Taula de dimensions: store
CREATE TABLE store (
    store_id INT PRIMARY KEY,
    name VARCHAR(255),
    location VARCHAR(255)
);

-- Inserir dades a les taules de dimensions
INSERT INTO date (date_id, date, year, month, day) VALUES
(20230101, '2023-01-01', 2023, 1, 1);

INSERT INTO book (book_id, title, author, genre, price) VALUES
(201, 'Book A', 'Author A', 'Fiction', 15.00);

INSERT INTO store (store_id, name, location) VALUES
(1, 'Store 1', 'City A');

-- Inserir dades a la taula de fets
INSERT INTO book_sales (date_id, book_id, store_id, quantity, total_amount) VALUES
(20230101, 201, 1, 3, 45.00);

-- Crear índexs a les claus foranes de la taula de fets
CREATE INDEX idx_book_sales_date_id ON book_sales(date_id);
CREATE INDEX idx_book_sales_book_id ON book_sales(book_id);
CREATE INDEX idx_book_sales_store_id ON book_sales(store_id);

-- Crear una taula particionada per any
CREATE TABLE book_sales_partitioned (
    id SERIAL PRIMARY KEY,
    date_id INT,
    book_id INT,
    store_id INT,
    quantity INT,
    total_amount NUMERIC
) PARTITION BY RANGE (date_id);

-- Crear particions per any
CREATE TABLE book_sales_2023 PARTITION OF book_sales_partitioned
    FOR VALUES FROM (20230101) TO (20240101);

Conclusió

En aquest tema, hem après els conceptes bàsics d'un magatzem de dades i com dissenyar i implementar un magatzem de dades utilitzant PostgreSQL. Hem explorat l'esquema estrella, la creació de taules, la inserció de dades, l'optimització del rendiment mitjançant índexs i particionament, i hem realitzat un exercici pràctic per reforçar els conceptes apresos. Amb aquests coneixements, estàs preparat per crear i gestionar magatzems de dades eficients amb PostgreSQL.

◄ Anterior
◄
Següent ►
►

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2024. Tots els drets reservats