dbt_sakila_dw.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNDMuiL+C3jie3oBN/OAjz8",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/rubuntu/uaa-417-sistemas-de-gestion-de-bases-de-datos-avanzados/blob/main/dbt_sakila_dw.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%capture\n",
        "# Instalar los paquetes necesarios\n",
        "!pip install ipython-sql  prettytable==0.7.2 psycopg2-binary dbt-core dbt-postgres pivottablejs\n",
        "\n",
        "# Actualizar los paquetes e instalar PostgreSQL\n",
        "!apt-get update -y\n",
        "!apt-get install postgresql postgresql-contrib -y"
      ],
      "metadata": {
        "id": "lpsdSAhz3Oe_"
      },
      "execution_count": 24,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Iniciar el servicio de PostgreSQL\n",
        "import os\n",
        "os.system('service postgresql start')\n",
        "\n",
        "# Cambiar la contraseña del usuario 'postgres'\n",
        "!sudo -u postgres psql -c \"ALTER USER postgres PASSWORD 'postgres';\"\n",
        "\n",
        "# Crear una nueva base de datos llamada 'pagila'\n",
        "!sudo -u postgres createdb pagila\n",
        "\n",
        "# Crear una nueva base de datos llamada 'pagila_dw'\n",
        "!sudo -u postgres createdb pagila_dw\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wq_VQdwHtCY5",
        "outputId": "5f17d302-ed13-41dc-bcc2-8e566e738ab1"
      },
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "ALTER ROLE\n",
            "createdb: error: database creation failed: ERROR:  database \"pagila\" already exists\n",
            "createdb: error: database creation failed: ERROR:  database \"pagila_dw\" already exists\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Descargar el script de creación del esquema de Pagila\n",
        "!wget https://raw.githubusercontent.com/devrimgunduz/pagila/master/pagila-schema.sql\n",
        "\n",
        "# Descargar el script de inserción de datos de Pagila\n",
        "!wget https://raw.githubusercontent.com/devrimgunduz/pagila/master/pagila-data.sql\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aMpPWkaBtIAR",
        "outputId": "6817be7c-14a4-4809-9108-17df43753f40"
      },
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2024-12-06 19:08:54--  https://raw.githubusercontent.com/devrimgunduz/pagila/master/pagila-schema.sql\n",
            "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.108.133, 185.199.109.133, 185.199.110.133, ...\n",
            "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.108.133|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 52422 (51K) [text/plain]\n",
            "Saving to: ‘pagila-schema.sql.1’\n",
            "\n",
            "pagila-schema.sql.1 100%[===================>]  51.19K  --.-KB/s    in 0.01s   \n",
            "\n",
            "2024-12-06 19:08:54 (3.93 MB/s) - ‘pagila-schema.sql.1’ saved [52422/52422]\n",
            "\n",
            "--2024-12-06 19:08:54--  https://raw.githubusercontent.com/devrimgunduz/pagila/master/pagila-data.sql\n",
            "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.108.133, 185.199.109.133, 185.199.110.133, ...\n",
            "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.108.133|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 3310905 (3.2M) [text/plain]\n",
            "Saving to: ‘pagila-data.sql.1’\n",
            "\n",
            "pagila-data.sql.1   100%[===================>]   3.16M  --.-KB/s    in 0.08s   \n",
            "\n",
            "2024-12-06 19:08:54 (38.3 MB/s) - ‘pagila-data.sql.1’ saved [3310905/3310905]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Ejecutar el script de creación del esquema\n",
        "!sudo -u postgres psql pagila < pagila-schema.sql\n",
        "\n",
        "# Ejecutar el script de inserción de datos\n",
        "!sudo -u postgres psql pagila < pagila-data.sql\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mtWfIURytXCL",
        "outputId": "a2ebf37e-c90a-4bd2-e67b-57c2eb52aad8"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "SET\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "SET\n",
            " set_config \n",
            "------------\n",
            " \n",
            "(1 row)\n",
            "\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "ALTER SCHEMA\n",
            "ERROR:  type \"bıgınt\" already exists\n",
            "ALTER DOMAIN\n",
            "ERROR:  type \"mpaa_rating\" already exists\n",
            "ALTER TYPE\n",
            "ERROR:  type \"year\" already exists\n",
            "ALTER DOMAIN\n",
            "ERROR:  function \"_group_concat\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"film_in_stock\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"film_not_in_stock\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"get_customer_balance\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"inventory_held_by_customer\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"inventory_in_stock\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"last_day\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"last_updated\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  relation \"customer_customer_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "SET\n",
            "SET\n",
            "ERROR:  relation \"customer\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  function \"rewards_report\" already exists with same argument types\n",
            "ALTER FUNCTION\n",
            "ERROR:  function \"group_concat\" already exists with same argument types\n",
            "ALTER AGGREGATE\n",
            "ERROR:  relation \"actor_actor_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"actor\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"category_category_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"category\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"film_film_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"film\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"film_actor\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"film_category\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"actor_info\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"address_address_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"address\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"city_city_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"city\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"country_country_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"country\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"customer_list\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"film_list\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"inventory_inventory_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"inventory\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"language_language_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"language\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"nicer_but_slower_film_list\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_payment_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_01\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_02\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_03\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_04\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_05\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_06\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"payment_p2022_07\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"rental_rental_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"rental\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"rental_by_category\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"sales_by_film_category\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"staff_staff_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"staff\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"store_store_id_seq\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"store\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"sales_by_store\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  relation \"staff_list\" already exists\n",
            "ALTER TABLE\n",
            "ERROR:  \"payment_p2022_01\" is already a partition\n",
            "ERROR:  \"payment_p2022_02\" is already a partition\n",
            "ERROR:  \"payment_p2022_03\" is already a partition\n",
            "ERROR:  \"payment_p2022_04\" is already a partition\n",
            "ERROR:  \"payment_p2022_05\" is already a partition\n",
            "ERROR:  \"payment_p2022_06\" is already a partition\n",
            "ERROR:  \"payment_p2022_07\" is already a partition\n",
            "ERROR:  multiple primary keys for table \"actor\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"address\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"category\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"city\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"country\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"customer\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"film_actor\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"film_category\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"film\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"inventory\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"language\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"rental\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"staff\" are not allowed\n",
            "ERROR:  multiple primary keys for table \"store\" are not allowed\n",
            "ERROR:  relation \"film_fulltext_idx\" already exists\n",
            "ERROR:  relation \"idx_actor_last_name\" already exists\n",
            "ERROR:  relation \"idx_fk_address_id\" already exists\n",
            "ERROR:  relation \"idx_fk_city_id\" already exists\n",
            "ERROR:  relation \"idx_fk_country_id\" already exists\n",
            "ERROR:  relation \"idx_fk_film_id\" already exists\n",
            "ERROR:  relation \"idx_fk_inventory_id\" already exists\n",
            "ERROR:  relation \"idx_fk_language_id\" already exists\n",
            "ERROR:  relation \"idx_fk_original_language_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_01_customer_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_01_staff_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_02_customer_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_02_staff_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_03_customer_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_03_staff_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_04_customer_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_04_staff_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_05_customer_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_05_staff_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_06_customer_id\" already exists\n",
            "ERROR:  relation \"idx_fk_payment_p2022_06_staff_id\" already exists\n",
            "ERROR:  relation \"idx_fk_store_id\" already exists\n",
            "ERROR:  relation \"idx_last_name\" already exists\n",
            "ERROR:  relation \"idx_store_id_film_id\" already exists\n",
            "ERROR:  relation \"idx_title\" already exists\n",
            "ERROR:  relation \"idx_unq_manager_staff_id\" already exists\n",
            "ERROR:  relation \"idx_unq_rental_rental_date_inventory_id_customer_id\" already exists\n",
            "ERROR:  relation \"payment_p2022_01_customer_id_idx\" already exists\n",
            "ERROR:  relation \"payment_p2022_02_customer_id_idx\" already exists\n",
            "ERROR:  relation \"payment_p2022_03_customer_id_idx\" already exists\n",
            "ERROR:  relation \"payment_p2022_04_customer_id_idx\" already exists\n",
            "ERROR:  relation \"payment_p2022_05_customer_id_idx\" already exists\n",
            "ERROR:  relation \"payment_p2022_06_customer_id_idx\" already exists\n",
            "ERROR:  relation \"rental_category\" already exists\n",
            "ERROR:  trigger \"film_fulltext_trigger\" for relation \"film\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"actor\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"address\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"category\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"city\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"country\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"customer\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"film\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"film_actor\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"film_category\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"inventory\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"language\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"rental\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"staff\" already exists\n",
            "ERROR:  trigger \"last_updated\" for relation \"store\" already exists\n",
            "ERROR:  constraint \"address_city_id_fkey\" for relation \"address\" already exists\n",
            "ERROR:  constraint \"city_country_id_fkey\" for relation \"city\" already exists\n",
            "ERROR:  constraint \"customer_address_id_fkey\" for relation \"customer\" already exists\n",
            "ERROR:  constraint \"customer_store_id_fkey\" for relation \"customer\" already exists\n",
            "ERROR:  constraint \"film_actor_actor_id_fkey\" for relation \"film_actor\" already exists\n",
            "ERROR:  constraint \"film_actor_film_id_fkey\" for relation \"film_actor\" already exists\n",
            "ERROR:  constraint \"film_category_category_id_fkey\" for relation \"film_category\" already exists\n",
            "ERROR:  constraint \"film_category_film_id_fkey\" for relation \"film_category\" already exists\n",
            "ERROR:  constraint \"film_language_id_fkey\" for relation \"film\" already exists\n",
            "ERROR:  constraint \"film_original_language_id_fkey\" for relation \"film\" already exists\n",
            "ERROR:  constraint \"inventory_film_id_fkey\" for relation \"inventory\" already exists\n",
            "ERROR:  constraint \"inventory_store_id_fkey\" for relation \"inventory\" already exists\n",
            "ERROR:  constraint \"payment_p2022_01_customer_id_fkey\" for relation \"payment_p2022_01\" already exists\n",
            "ERROR:  constraint \"payment_p2022_01_rental_id_fkey\" for relation \"payment_p2022_01\" already exists\n",
            "ERROR:  constraint \"payment_p2022_01_staff_id_fkey\" for relation \"payment_p2022_01\" already exists\n",
            "ERROR:  constraint \"payment_p2022_02_customer_id_fkey\" for relation \"payment_p2022_02\" already exists\n",
            "ERROR:  constraint \"payment_p2022_02_rental_id_fkey\" for relation \"payment_p2022_02\" already exists\n",
            "ERROR:  constraint \"payment_p2022_02_staff_id_fkey\" for relation \"payment_p2022_02\" already exists\n",
            "ERROR:  constraint \"payment_p2022_03_customer_id_fkey\" for relation \"payment_p2022_03\" already exists\n",
            "ERROR:  constraint \"payment_p2022_03_rental_id_fkey\" for relation \"payment_p2022_03\" already exists\n",
            "ERROR:  constraint \"payment_p2022_03_staff_id_fkey\" for relation \"payment_p2022_03\" already exists\n",
            "ERROR:  constraint \"payment_p2022_04_customer_id_fkey\" for relation \"payment_p2022_04\" already exists\n",
            "ERROR:  constraint \"payment_p2022_04_rental_id_fkey\" for relation \"payment_p2022_04\" already exists\n",
            "ERROR:  constraint \"payment_p2022_04_staff_id_fkey\" for relation \"payment_p2022_04\" already exists\n",
            "ERROR:  constraint \"payment_p2022_05_customer_id_fkey\" for relation \"payment_p2022_05\" already exists\n",
            "ERROR:  constraint \"payment_p2022_05_rental_id_fkey\" for relation \"payment_p2022_05\" already exists\n",
            "ERROR:  constraint \"payment_p2022_05_staff_id_fkey\" for relation \"payment_p2022_05\" already exists\n",
            "ERROR:  constraint \"payment_p2022_06_customer_id_fkey\" for relation \"payment_p2022_06\" already exists\n",
            "ERROR:  constraint \"payment_p2022_06_rental_id_fkey\" for relation \"payment_p2022_06\" already exists\n",
            "ERROR:  constraint \"payment_p2022_06_staff_id_fkey\" for relation \"payment_p2022_06\" already exists\n",
            "ERROR:  constraint \"rental_customer_id_fkey\" for relation \"rental\" already exists\n",
            "ERROR:  constraint \"rental_inventory_id_fkey\" for relation \"rental\" already exists\n",
            "ERROR:  constraint \"rental_staff_id_fkey\" for relation \"rental\" already exists\n",
            "ERROR:  constraint \"staff_address_id_fkey\" for relation \"staff\" already exists\n",
            "ERROR:  constraint \"staff_store_id_fkey\" for relation \"staff\" already exists\n",
            "ERROR:  constraint \"store_address_id_fkey\" for relation \"store\" already exists\n",
            "REVOKE\n",
            "GRANT\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "SET\n",
            " set_config \n",
            "------------\n",
            " \n",
            "(1 row)\n",
            "\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "SET\n",
            "ERROR:  duplicate key value violates unique constraint \"actor_pkey\"\n",
            "DETAIL:  Key (actor_id)=(1) already exists.\n",
            "CONTEXT:  COPY actor, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"country_pkey\"\n",
            "DETAIL:  Key (country_id)=(1) already exists.\n",
            "CONTEXT:  COPY country, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"city_pkey\"\n",
            "DETAIL:  Key (city_id)=(1) already exists.\n",
            "CONTEXT:  COPY city, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"address_pkey\"\n",
            "DETAIL:  Key (address_id)=(1) already exists.\n",
            "CONTEXT:  COPY address, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"category_pkey\"\n",
            "DETAIL:  Key (category_id)=(1) already exists.\n",
            "CONTEXT:  COPY category, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"store_pkey\"\n",
            "DETAIL:  Key (store_id)=(0) already exists.\n",
            "CONTEXT:  COPY store, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"customer_pkey\"\n",
            "DETAIL:  Key (customer_id)=(1) already exists.\n",
            "CONTEXT:  COPY customer, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"language_pkey\"\n",
            "DETAIL:  Key (language_id)=(1) already exists.\n",
            "CONTEXT:  COPY language, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"film_pkey\"\n",
            "DETAIL:  Key (film_id)=(1) already exists.\n",
            "CONTEXT:  COPY film, line 1: \"1\tACADEMY DINOSAUR\tA Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The C...\"\n",
            "ERROR:  duplicate key value violates unique constraint \"film_actor_pkey\"\n",
            "DETAIL:  Key (actor_id, film_id)=(1, 1) already exists.\n",
            "CONTEXT:  COPY film_actor, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"film_category_pkey\"\n",
            "DETAIL:  Key (film_id, category_id)=(56, 11) already exists.\n",
            "CONTEXT:  COPY film_category, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"inventory_pkey\"\n",
            "DETAIL:  Key (inventory_id)=(1) already exists.\n",
            "CONTEXT:  COPY inventory, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"staff_pkey\"\n",
            "DETAIL:  Key (staff_id)=(0) already exists.\n",
            "CONTEXT:  COPY staff, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"rental_pkey\"\n",
            "DETAIL:  Key (rental_id)=(2) already exists.\n",
            "CONTEXT:  COPY rental, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_01_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-01-29 01:58:52.222594+00, 16051) already exists.\n",
            "CONTEXT:  COPY payment_p2022_01, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_02_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-02-03 01:49:30.663659+00, 16056) already exists.\n",
            "CONTEXT:  COPY payment_p2022_02, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_03_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-03-02 19:51:40.813503+00, 16053) already exists.\n",
            "CONTEXT:  COPY payment_p2022_03, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_04_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-04-15 12:28:07.452161+00, 16066) already exists.\n",
            "CONTEXT:  COPY payment_p2022_04, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_05_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-05-20 15:54:02.174545+00, 16055) already exists.\n",
            "CONTEXT:  COPY payment_p2022_05, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_06_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-06-21 07:41:50.707316+00, 16050) already exists.\n",
            "CONTEXT:  COPY payment_p2022_06, line 1\n",
            "ERROR:  duplicate key value violates unique constraint \"payment_p2022_07_pkey\"\n",
            "DETAIL:  Key (payment_date, payment_id)=(2022-07-19 06:32:02.911937+00, 16061) already exists.\n",
            "CONTEXT:  COPY payment_p2022_07, line 1\n",
            " setval \n",
            "--------\n",
            "    200\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "    605\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "     16\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "    600\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "    109\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "    599\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "   1000\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "   4581\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "      6\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "  32098\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "  16049\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "   1500\n",
            "(1 row)\n",
            "\n",
            " setval \n",
            "--------\n",
            "    500\n",
            "(1 row)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import psycopg2\n",
        "\n",
        "# Configuración de conexión\n",
        "postgres_config = {\n",
        "    'user': 'postgres',\n",
        "    'password': 'postgres',\n",
        "    'host': 'localhost',\n",
        "    'port': '5432',\n",
        "    'database': 'pagila',\n",
        "}\n",
        "\n",
        "# Conexión a la base de datos\n",
        "conn = psycopg2.connect(**postgres_config)\n",
        "cursor = conn.cursor()\n",
        "\n",
        "# Listar las tablas en el esquema público\n",
        "cursor.execute(\"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';\")\n",
        "tables = cursor.fetchall()\n",
        "print(\"Tablas en la base de datos pagila:\")\n",
        "for table in tables:\n",
        "    print(table[0])\n",
        "\n",
        "cursor.close()\n",
        "conn.close()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cp8P5Jy1tcNI",
        "outputId": "d2e84d9b-c846-400a-9eae-0e585e2eaf85"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Tablas en la base de datos pagila:\n",
            "film_actor\n",
            "address\n",
            "city\n",
            "actor\n",
            "actor_info\n",
            "category\n",
            "country\n",
            "customer\n",
            "customer_list\n",
            "film_list\n",
            "nicer_but_slower_film_list\n",
            "film\n",
            "film_category\n",
            "inventory\n",
            "language\n",
            "sales_by_film_category\n",
            "rental\n",
            "sales_by_store\n",
            "staff_list\n",
            "payment\n",
            "store\n",
            "payment_p2022_02\n",
            "payment_p2022_07\n",
            "payment_p2022_04\n",
            "payment_p2022_05\n",
            "staff\n",
            "payment_p2022_01\n",
            "payment_p2022_03\n",
            "payment_p2022_06\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Proyecto dbt"
      ],
      "metadata": {
        "id": "_-ydpTOntj5Q"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Crear un directorio para el proyecto dbt\n",
        "!mkdir pagila_dw_project\n",
        "%cd pagila_dw_project\n",
        "\n",
        "# Inicializar el proyecto dbt\n",
        "#!dbt init pagila_dw_project\n",
        "\n",
        "# Crear el directorio de modelos\n",
        "!mkdir models\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7fQPeNS-tgzp",
        "outputId": "ae3002d2-7525-4f84-9de0-284b5194043b"
      },
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "mkdir: cannot create directory ‘pagila_dw_project’: File exists\n",
            "/content/pagila_dw_project/pagila_dw_project\n",
            "mkdir: cannot create directory ‘models’: File exists\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Crear el directorio .dbt si no existe\n",
        "!mkdir -p /root/.dbt\n",
        "\n",
        "# Escribir el archivo profiles.yml\n",
        "profiles_yml = \"\"\"\n",
        "pagila_dw_project:\n",
        "  target: dev\n",
        "  outputs:\n",
        "    dev:\n",
        "      type: postgres\n",
        "      host: localhost\n",
        "      user: postgres\n",
        "      password: postgres\n",
        "      port: 5432\n",
        "      dbname: pagila\n",
        "      schema: dw\n",
        "      threads: 4\n",
        "      keepalives_idle: 0\n",
        "\"\"\"\n",
        "\n",
        "with open('/root/.dbt/profiles.yml', 'w') as f:\n",
        "    f.write(profiles_yml)\n"
      ],
      "metadata": {
        "id": "ktHh_Zxuts-B"
      },
      "execution_count": 30,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "sources_yml = \"\"\"\n",
        "version: 2\n",
        "\n",
        "sources:\n",
        "  - name: pagila\n",
        "    schema: public\n",
        "    tables:\n",
        "      - name: actor\n",
        "      - name: address\n",
        "      - name: category\n",
        "      - name: city\n",
        "      - name: country\n",
        "      - name: customer\n",
        "      - name: film\n",
        "      - name: film_actor\n",
        "      - name: film_category\n",
        "      - name: inventory\n",
        "      - name: language\n",
        "      - name: payment\n",
        "      - name: rental\n",
        "      - name: staff\n",
        "      - name: store\n",
        "\"\"\"\n",
        "\n",
        "with open('models/sources.yml', 'w') as f:\n",
        "    f.write(sources_yml)\n"
      ],
      "metadata": {
        "id": "5r0vG9RYtyAh"
      },
      "execution_count": 31,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dim_pelicula_sql = \"\"\"\n",
        "{{ config(materialized='table') }}\n",
        "\n",
        "\n",
        "\n",
        "WITH film_base AS (\n",
        "    SELECT\n",
        "        film_id,\n",
        "        title AS titulo,\n",
        "        description AS descripcion,\n",
        "        release_year AS anio_lanzamiento,\n",
        "        language_id,\n",
        "        rental_duration AS duracion_renta,\n",
        "        rental_rate AS tasa_renta,\n",
        "        length AS duracion,\n",
        "        replacement_cost AS costo_reemplazo,\n",
        "        rating AS clasificacion,\n",
        "        special_features AS caracteristicas_especiales,\n",
        "        last_update AS fecha_ultima_actualizacion\n",
        "    FROM {{ source('pagila', 'film') }}\n",
        "),\n",
        "idioma AS (\n",
        "    SELECT\n",
        "        language_id,\n",
        "        name AS idioma\n",
        "    FROM {{ source('pagila', 'language') }}\n",
        "),\n",
        "categoria AS (\n",
        "    SELECT\n",
        "        fc.film_id,\n",
        "        c.name AS categoria\n",
        "    FROM {{ source('pagila', 'film_category') }} AS fc\n",
        "    JOIN {{ source('pagila', 'category') }} AS c ON fc.category_id = c.category_id\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    ROW_NUMBER() OVER (ORDER BY fb.film_id) AS pelicula_key,\n",
        "    fb.film_id,\n",
        "    fb.titulo,\n",
        "    fb.descripcion,\n",
        "    fb.anio_lanzamiento,\n",
        "    i.idioma,\n",
        "    fb.duracion_renta,\n",
        "    fb.tasa_renta,\n",
        "    fb.duracion,\n",
        "    fb.costo_reemplazo,\n",
        "    fb.clasificacion,\n",
        "    fb.caracteristicas_especiales,\n",
        "    c.categoria,\n",
        "    fb.fecha_ultima_actualizacion\n",
        "FROM film_base AS fb\n",
        "LEFT JOIN idioma AS i ON fb.language_id = i.language_id\n",
        "LEFT JOIN categoria AS c ON fb.film_id = c.film_id\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "with open('models/dim_pelicula.sql', 'w') as f:\n",
        "    f.write(dim_pelicula_sql)\n"
      ],
      "metadata": {
        "id": "povBn0hFt2YR"
      },
      "execution_count": 32,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dim_cliente_sql = \"\"\"\n",
        "{{ config(materialized='table') }}\n",
        "\n",
        "\n",
        "\n",
        "SELECT\n",
        "    ROW_NUMBER() OVER (ORDER BY customer_id) AS cliente_key,\n",
        "    c.customer_id,\n",
        "    c.first_name AS nombre,\n",
        "    c.last_name AS apellido,\n",
        "    c.email,\n",
        "    c.active AS activo,\n",
        "    a.address AS direccion,\n",
        "    ci.city AS ciudad,\n",
        "    co.country AS pais,\n",
        "    c.create_date AS fecha_creacion,\n",
        "    c.last_update AS fecha_ultima_actualizacion\n",
        "FROM {{ source('pagila', 'customer') }} AS c\n",
        "JOIN {{ source('pagila', 'address') }} AS a ON c.address_id = a.address_id\n",
        "JOIN {{ source('pagila', 'city') }} AS ci ON a.city_id = ci.city_id\n",
        "JOIN {{ source('pagila', 'country') }} AS co ON ci.country_id = co.country_id\n",
        "\"\"\"\n",
        "\n",
        "with open('models/dim_cliente.sql', 'w') as f:\n",
        "    f.write(dim_cliente_sql)\n"
      ],
      "metadata": {
        "id": "rK8fqLkhuJiw"
      },
      "execution_count": 33,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dim_empleado_sql = \"\"\"\n",
        "{{ config(materialized='table') }}\n",
        "\n",
        "\n",
        "\n",
        "SELECT\n",
        "    ROW_NUMBER() OVER (ORDER BY s.staff_id) AS empleado_key,\n",
        "    s.staff_id,\n",
        "    s.first_name AS nombre,\n",
        "    s.last_name AS apellido,\n",
        "    s.email,\n",
        "    s.store_id AS tienda_id,\n",
        "    s.active AS activo,\n",
        "    s.username AS nombre_usuario,\n",
        "    s.last_update AS fecha_ultima_actualizacion\n",
        "FROM {{ source('pagila', 'staff') }} AS s\n",
        "\"\"\"\n",
        "\n",
        "with open('models/dim_empleado.sql', 'w') as f:\n",
        "    f.write(dim_empleado_sql)\n"
      ],
      "metadata": {
        "id": "xK0HfNyWuMyI"
      },
      "execution_count": 34,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dim_tienda_sql = \"\"\"\n",
        "{{ config(materialized='table') }}\n",
        "\n",
        "\n",
        "\n",
        "SELECT\n",
        "    ROW_NUMBER() OVER (ORDER BY st.store_id) AS tienda_key,\n",
        "    st.store_id,\n",
        "    st.manager_staff_id AS gerente_staff_id,\n",
        "    a.address AS direccion,\n",
        "    ci.city AS ciudad,\n",
        "    co.country AS pais,\n",
        "    a.postal_code AS codigo_postal,\n",
        "    a.phone AS telefono,\n",
        "    st.last_update AS fecha_ultima_actualizacion\n",
        "FROM {{ source('pagila', 'store') }} AS st\n",
        "JOIN {{ source('pagila', 'address') }} AS a ON st.address_id = a.address_id\n",
        "JOIN {{ source('pagila', 'city') }} AS ci ON a.city_id = ci.city_id\n",
        "JOIN {{ source('pagila', 'country') }} AS co ON ci.country_id = co.country_id\n",
        "\"\"\"\n",
        "\n",
        "with open('models/dim_tienda.sql', 'w') as f:\n",
        "    f.write(dim_tienda_sql)\n"
      ],
      "metadata": {
        "id": "ZGpqKTYzvGzY"
      },
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dim_tiempo_sql = \"\"\"\n",
        "{{ config(materialized='table') }}\n",
        "\n",
        "\n",
        "\n",
        "WITH date_range AS (\n",
        "    SELECT\n",
        "        MIN(DATE(rental_date)) AS start_date,\n",
        "        MAX(DATE(rental_date)) AS end_date\n",
        "    FROM {{ source('pagila', 'rental') }}\n",
        "),\n",
        "\n",
        "dates AS (\n",
        "    SELECT\n",
        "        generate_series(\n",
        "            (SELECT start_date FROM date_range),\n",
        "            (SELECT end_date FROM date_range),\n",
        "            interval '1 day'\n",
        "        )::date AS fecha\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    ROW_NUMBER() OVER (ORDER BY fecha) AS tiempo_key,\n",
        "    fecha,\n",
        "    EXTRACT(DAY FROM fecha)::INT AS dia,\n",
        "    EXTRACT(MONTH FROM fecha)::INT AS mes,\n",
        "    EXTRACT(YEAR FROM fecha)::INT AS anio,\n",
        "    EXTRACT(QUARTER FROM fecha)::INT AS trimestre,\n",
        "    TO_CHAR(fecha, 'Day') AS nombre_dia,\n",
        "    TO_CHAR(fecha, 'Month') AS nombre_mes,\n",
        "    CASE WHEN EXTRACT(ISODOW FROM fecha) IN (6,7) THEN TRUE ELSE FALSE END AS es_fin_de_semana\n",
        "FROM dates\n",
        "ORDER BY fecha\n",
        "\"\"\"\n",
        "\n",
        "with open('models/dim_tiempo.sql', 'w') as f:\n",
        "    f.write(dim_tiempo_sql)\n"
      ],
      "metadata": {
        "id": "8SVysKDMvVLH"
      },
      "execution_count": 36,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "hechos_alquileres_sql = \"\"\"\n",
        "{{ config(materialized='table') }}\n",
        "\n",
        "\n",
        "\n",
        "SELECT\n",
        "    ROW_NUMBER() OVER (ORDER BY r.rental_id) AS alquiler_key,\n",
        "    r.rental_id,\n",
        "    dp.pelicula_key,\n",
        "    dc.cliente_key,\n",
        "    de.empleado_key,\n",
        "    dt.tienda_key,\n",
        "    dtm.tiempo_key,\n",
        "    EXTRACT(EPOCH FROM (r.return_date - r.rental_date))/60 AS duracion_alquiler,\n",
        "    p.amount AS monto\n",
        "FROM {{ source('pagila', 'rental') }} AS r\n",
        "JOIN {{ source('pagila', 'payment') }} AS p ON r.rental_id = p.rental_id\n",
        "JOIN {{ source('pagila', 'inventory') }} AS i ON r.inventory_id = i.inventory_id\n",
        "JOIN {{ ref('dim_pelicula') }} AS dp ON i.film_id = dp.film_id\n",
        "JOIN {{ ref('dim_cliente') }} AS dc ON r.customer_id = dc.customer_id\n",
        "JOIN {{ ref('dim_empleado') }} AS de ON r.staff_id = de.staff_id\n",
        "JOIN {{ ref('dim_tienda') }} AS dt ON i.store_id = dt.store_id\n",
        "JOIN {{ ref('dim_tiempo') }} AS dtm ON DATE(r.rental_date) = dtm.fecha\n",
        "\"\"\"\n",
        "\n",
        "with open('models/hechos_alquileres.sql', 'w') as f:\n",
        "    f.write(hechos_alquileres_sql)"
      ],
      "metadata": {
        "id": "j5Eflp41vysv"
      },
      "execution_count": 37,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dbt_project_yml = \"\"\"\n",
        "name: 'pagila_dw_project'\n",
        "version: '1.0'\n",
        "config-version: 2\n",
        "\n",
        "profile: 'pagila_dw_project'\n",
        "\n",
        "model-paths: [\"models\"]\n",
        "\n",
        "target-path: \"target\"\n",
        "\n",
        "clean-targets:\n",
        "  - \"target\"\n",
        "  - \"dbt_modules\"\n",
        "\n",
        "models:\n",
        "  pagila_dw_project:\n",
        "    #+schema: public\n",
        "    dim_pelicula:\n",
        "      +materialized: table\n",
        "    dim_cliente:\n",
        "      +materialized: table\n",
        "    dim_empleado:\n",
        "      +materialized: table\n",
        "    dim_tienda:\n",
        "      +materialized: table\n",
        "    dim_tiempo:\n",
        "      +materialized: table\n",
        "    hechos_alquileres:\n",
        "      +materialized: table\n",
        "\"\"\"\n",
        "\n",
        "with open('dbt_project.yml', 'w') as f:\n",
        "    f.write(dbt_project_yml)\n"
      ],
      "metadata": {
        "id": "zZwBiVBmyy4w"
      },
      "execution_count": 38,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Volvemos al directorio del proyecto\n",
        "%cd /content/pagila_dw_project\n",
        "\n",
        "# Ejecutar dbt\n",
        "!dbt run --profiles-dir /root/.dbt --project-dir .\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nTkvHs30xIj3",
        "outputId": "164f1c58-2868-4d3c-c54a-b533e3a2f077"
      },
      "execution_count": 39,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "/content/pagila_dw_project\n",
            "\u001b[0m19:09:00  Running with dbt=1.8.9\n",
            "\u001b[0m19:09:01  Registered adapter: postgres=1.8.2\n",
            "\u001b[0m19:09:01  Found 6 models, 15 sources, 428 macros\n",
            "\u001b[0m19:09:01  \n",
            "\u001b[0m19:09:02  Concurrency: 4 threads (target='dev')\n",
            "\u001b[0m19:09:02  \n",
            "\u001b[0m19:09:02  1 of 6 START sql table model dw.dim_cliente .................................... [RUN]\n",
            "\u001b[0m19:09:02  2 of 6 START sql table model dw.dim_empleado ................................... [RUN]\n",
            "\u001b[0m19:09:02  3 of 6 START sql table model dw.dim_pelicula ................................... [RUN]\n",
            "\u001b[0m19:09:02  4 of 6 START sql table model dw.dim_tiempo ..................................... [RUN]\n",
            "\u001b[0m19:09:03  1 of 6 OK created sql table model dw.dim_cliente ............................... [\u001b[32mSELECT 599\u001b[0m in 1.06s]\n",
            "\u001b[0m19:09:03  2 of 6 OK created sql table model dw.dim_empleado .............................. [\u001b[32mSELECT 1500\u001b[0m in 1.04s]\n",
            "\u001b[0m19:09:03  4 of 6 OK created sql table model dw.dim_tiempo ................................ [\u001b[32mSELECT 191\u001b[0m in 1.03s]\n",
            "\u001b[0m19:09:03  3 of 6 OK created sql table model dw.dim_pelicula .............................. [\u001b[32mSELECT 2367\u001b[0m in 1.01s]\n",
            "\u001b[0m19:09:03  5 of 6 START sql table model dw.dim_tienda ..................................... [RUN]\n",
            "\u001b[0m19:09:03  5 of 6 OK created sql table model dw.dim_tienda ................................ [\u001b[32mSELECT 500\u001b[0m in 0.15s]\n",
            "\u001b[0m19:09:03  6 of 6 START sql table model dw.hechos_alquileres .............................. [RUN]\n",
            "\u001b[0m19:09:03  6 of 6 OK created sql table model dw.hechos_alquileres ......................... [\u001b[32mSELECT 37982\u001b[0m in 0.40s]\n",
            "\u001b[0m19:09:03  \n",
            "\u001b[0m19:09:03  Finished running 6 table models in 0 hours 0 minutes and 2.01 seconds (2.01s).\n",
            "\u001b[0m19:09:03  \n",
            "\u001b[0m19:09:03  \u001b[32mCompleted successfully\u001b[0m\n",
            "\u001b[0m19:09:03  \n",
            "\u001b[0m19:09:03  Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import psycopg2\n",
        "\n",
        "conn = psycopg2.connect(\n",
        "    dbname=\"pagila\",\n",
        "    user=\"postgres\",\n",
        "    password=\"postgres\",\n",
        "    host=\"localhost\",\n",
        "    port=\"5432\"\n",
        ")\n",
        "\n",
        "cursor = conn.cursor()\n",
        "cursor.execute(\"\"\"\n",
        "    SELECT table_schema, table_name\n",
        "    FROM information_schema.tables\n",
        "    WHERE table_name LIKE 'dim_%' OR table_name LIKE 'hechos_%';\n",
        "\"\"\")\n",
        "tables = cursor.fetchall()\n",
        "for schema, table in tables:\n",
        "    print(f\"Schema: {schema}, Table: {table}\")\n",
        "\n",
        "cursor.close()\n",
        "conn.close()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lz3oj1iMYBLa",
        "outputId": "e1484380-47cb-413f-f6da-c4748a3c3d22"
      },
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Schema: dw, Table: dim_cliente\n",
            "Schema: dw, Table: dim_empleado\n",
            "Schema: dw, Table: dim_pelicula\n",
            "Schema: dw, Table: dim_tiempo\n",
            "Schema: dw, Table: dim_tienda\n",
            "Schema: dw, Table: hechos_alquileres\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%load_ext sql\n",
        "%sql postgresql://postgres:postgres@localhost/pagila"
      ],
      "metadata": {
        "id": "9kgHG3VFfcc6",
        "outputId": "94bf39c9-2055-4840-f0fc-5a714c201f5d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "The sql extension is already loaded. To reload it, use:\n",
            "  %reload_ext sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "select * from dw.dim_pelicula limit 5;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 441
        },
        "id": "ccKzHyalf7JA",
        "outputId": "171f03e6-fee4-4459-c5a5-fdca0a818042"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://postgres:***@localhost/pagila\n",
            "5 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 1, 'ACADEMY DINOSAUR', 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies', 2012, 'English             ', 6, Decimal('0.99'), 86, Decimal('20.99'), 'PG', ['Deleted Scenes', 'Behind the Scenes'], 'Games', datetime.datetime(2022, 9, 10, 16, 46, 3, 905795, tzinfo=datetime.timezone.utc)),\n",
              " (2, 1, 'ACADEMY DINOSAUR', 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies', 2012, 'English             ', 6, Decimal('0.99'), 86, Decimal('20.99'), 'PG', ['Deleted Scenes', 'Behind the Scenes'], 'New', datetime.datetime(2022, 9, 10, 16, 46, 3, 905795, tzinfo=datetime.timezone.utc)),\n",
              " (3, 1, 'ACADEMY DINOSAUR', 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies', 2012, 'English             ', 6, Decimal('0.99'), 86, Decimal('20.99'), 'PG', ['Deleted Scenes', 'Behind the Scenes'], 'Travel', datetime.datetime(2022, 9, 10, 16, 46, 3, 905795, tzinfo=datetime.timezone.utc)),\n",
              " (4, 2, 'ACE GOLDFINGER', 'A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China', 2023, 'English             ', 3, Decimal('4.99'), 48, Decimal('12.99'), 'G', ['Trailers', 'Deleted Scenes'], 'Action', datetime.datetime(2022, 9, 10, 16, 46, 3, 905795, tzinfo=datetime.timezone.utc)),\n",
              " (5, 2, 'ACE GOLDFINGER', 'A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China', 2023, 'English             ', 3, Decimal('4.99'), 48, Decimal('12.99'), 'G', ['Trailers', 'Deleted Scenes'], 'New', datetime.datetime(2022, 9, 10, 16, 46, 3, 905795, tzinfo=datetime.timezone.utc))]"
            ],
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>pelicula_key</th>\n",
              "        <th>film_id</th>\n",
              "        <th>titulo</th>\n",
              "        <th>descripcion</th>\n",
              "        <th>anio_lanzamiento</th>\n",
              "        <th>idioma</th>\n",
              "        <th>duracion_renta</th>\n",
              "        <th>tasa_renta</th>\n",
              "        <th>duracion</th>\n",
              "        <th>costo_reemplazo</th>\n",
              "        <th>clasificacion</th>\n",
              "        <th>caracteristicas_especiales</th>\n",
              "        <th>categoria</th>\n",
              "        <th>fecha_ultima_actualizacion</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>1</td>\n",
              "        <td>1</td>\n",
              "        <td>ACADEMY DINOSAUR</td>\n",
              "        <td>A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies</td>\n",
              "        <td>2012</td>\n",
              "        <td>English             </td>\n",
              "        <td>6</td>\n",
              "        <td>0.99</td>\n",
              "        <td>86</td>\n",
              "        <td>20.99</td>\n",
              "        <td>PG</td>\n",
              "        <td>[&#x27;Deleted Scenes&#x27;, &#x27;Behind the Scenes&#x27;]</td>\n",
              "        <td>Games</td>\n",
              "        <td>2022-09-10 16:46:03.905795+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>2</td>\n",
              "        <td>1</td>\n",
              "        <td>ACADEMY DINOSAUR</td>\n",
              "        <td>A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies</td>\n",
              "        <td>2012</td>\n",
              "        <td>English             </td>\n",
              "        <td>6</td>\n",
              "        <td>0.99</td>\n",
              "        <td>86</td>\n",
              "        <td>20.99</td>\n",
              "        <td>PG</td>\n",
              "        <td>[&#x27;Deleted Scenes&#x27;, &#x27;Behind the Scenes&#x27;]</td>\n",
              "        <td>New</td>\n",
              "        <td>2022-09-10 16:46:03.905795+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>3</td>\n",
              "        <td>1</td>\n",
              "        <td>ACADEMY DINOSAUR</td>\n",
              "        <td>A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies</td>\n",
              "        <td>2012</td>\n",
              "        <td>English             </td>\n",
              "        <td>6</td>\n",
              "        <td>0.99</td>\n",
              "        <td>86</td>\n",
              "        <td>20.99</td>\n",
              "        <td>PG</td>\n",
              "        <td>[&#x27;Deleted Scenes&#x27;, &#x27;Behind the Scenes&#x27;]</td>\n",
              "        <td>Travel</td>\n",
              "        <td>2022-09-10 16:46:03.905795+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>4</td>\n",
              "        <td>2</td>\n",
              "        <td>ACE GOLDFINGER</td>\n",
              "        <td>A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China</td>\n",
              "        <td>2023</td>\n",
              "        <td>English             </td>\n",
              "        <td>3</td>\n",
              "        <td>4.99</td>\n",
              "        <td>48</td>\n",
              "        <td>12.99</td>\n",
              "        <td>G</td>\n",
              "        <td>[&#x27;Trailers&#x27;, &#x27;Deleted Scenes&#x27;]</td>\n",
              "        <td>Action</td>\n",
              "        <td>2022-09-10 16:46:03.905795+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>5</td>\n",
              "        <td>2</td>\n",
              "        <td>ACE GOLDFINGER</td>\n",
              "        <td>A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China</td>\n",
              "        <td>2023</td>\n",
              "        <td>English             </td>\n",
              "        <td>3</td>\n",
              "        <td>4.99</td>\n",
              "        <td>48</td>\n",
              "        <td>12.99</td>\n",
              "        <td>G</td>\n",
              "        <td>[&#x27;Trailers&#x27;, &#x27;Deleted Scenes&#x27;]</td>\n",
              "        <td>New</td>\n",
              "        <td>2022-09-10 16:46:03.905795+00:00</td>\n",
              "    </tr>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 42
        }
      ]
    }
  ]
}