Ingest-Bronze.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "a1418882-6359-4921-8c1a-9394eea8af35",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\categories.csv categories\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\customers.csv customers\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\employees.csv employees\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\orderdetails.csv orderdetails\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\orders.csv orders\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\products.csv products\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\shippers.csv shippers\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\suppliers.csv suppliers\n"
     ]
    }
   ],
   "source": [
    "import duckdb\n",
    "import glob\n",
    "\n",
    "con = duckdb.connect()\n",
    " \n",
    "#con.execute(\"INSTALL postgres;\")\n",
    "con.execute(\"LOAD postgres;\")\n",
    " \n",
    "con.sql(\"ATTACH 'host=localhost port=5432 dbname=postgres user=postgres password=postgres' AS postgres (TYPE postgres);\")\n",
    "arquivos = glob.glob(\"C:\\\\dbt_pipelines\\\\scripts e dados\\\\*.csv\")\n",
    "\n",
    "for arquivo in arquivos:\n",
    "    item = arquivo.split('\\\\')\n",
    "    ultimo = item[-1]\n",
    "    tabela = ultimo.split('.')\n",
    "    arquivo = arquivo.replace('\\\\' ,'\\\\\\\\').replace('[','')\n",
    "    con.sql(f\"CREATE TABLE postgres.bronze.{tabela[0]} AS SELECT * FROM '{arquivo}';\")\n",
    "    print(arquivo, '- tabela: '+tabela[0]+ 'criada')\n",
    "\n",
    " con.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "4bd79b83-55c0-4263-8233-ad728d9adcbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['C:\\\\dbt_pipelines\\\\scripts e dados\\\\categories.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\customers.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\employees.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\orderdetails.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\orders.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\products.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\shippers.csv', 'C:\\\\dbt_pipelines\\\\scripts e dados\\\\suppliers.csv']\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\categories.csv categories\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\customers.csv customers\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\employees.csv employees\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\orderdetails.csv orderdetails\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\orders.csv orders\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\products.csv products\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\shippers.csv shippers\n",
      "C:\\\\dbt_pipelines\\\\scripts e dados\\\\suppliers.csv suppliers\n"
     ]
    }
   ],
   "source": [
    "import glob\n",
    "arquivos = glob.glob(\"C:\\\\dbt_pipelines\\\\scripts e dados\\\\*.csv\")\n",
    "print(arquivos)\n",
    "for arquivo in arquivos:\n",
    "    item = arquivo.split('\\\\')\n",
    "    ultimo = item[-1]\n",
    "    tabela = ultimo.split('.')\n",
    "    arquivo = arquivo.replace('\\\\' ,'\\\\\\\\')\n",
    "    print(arquivo, tabela[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea911d60-a794-4d17-b3f8-27899606ed51",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}