examples/flight_demo_python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "253c2d66-1551-422e-bead-94140e0c224b",
   "metadata": {},
   "source": [
    "# Query SAP table and join with local data\n",
    "\n",
    "A first example demonstrates how to join two SAP tables with an external table. We’ll be using the [ABAP Flight Reference Scenario](https://help.sap.com/docs/ABAP_PLATFORM_NEW/fc4c71aa50014fd1b43721701471913d/a9d7c7c140a0408dbc5966c52d156b49.html), specifically joining the `SFLIGHT` and `SPFLI` tables which contain flight and flight schedule details respectively, with an external table `WEATHER` that holds weather information. We will extract flight information and associated temperatures at departure and arrival cities."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3898355-0196-4ee9-8ca4-46ce2eaf0de6",
   "metadata": {},
   "source": [
    "## Import DuckDB & load **ERPL** extension\n",
    "In the next cells we import duckdb. Then we install the ERPL extension and load it into the current DB-session. Via multiple SET-commands we configure the connection to our SAP development system. In our case we use the docker based [ABAP Platform Trial](https://hub.docker.com/r/sapse/abap-platform-trial). The credentials are set by default, details can be found in the documentation of the docker image. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8b7c5a3c-c33d-48f8-b510-62cc4927fe33",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bbfc19c4-3de5-4d86-b3e1-3fba37e6c320",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- Loading ERPL Trampoline Extension. --\n",
      "(The purpose of the extension is to extract dependencies and load the ERPL implementation)\n",
      "Saving ERPL SAP dependencies to '/home/jr/.duckdb/extensions/v0.9.2/linux_amd64' and loading them ... done\n",
      "ERPL extension extracted and saved to /home/jr/.duckdb/extensions/v0.9.2/linux_amd64.\n",
      "ERPL implementation extension installed from /home/jr/.duckdb/extensions/v0.9.2/linux_amd64/erpl_impl.duckdb_extension.\n",
      "ERPL implementation extension loaded. For instructions how to use it visit https://erpl.io\n"
     ]
    }
   ],
   "source": [
    "con = duckdb.connect(config={\"allow_unsigned_extensions\": \"true\"})\n",
    "con.install_extension(\"./erpl.duckdb_extension\");\n",
    "con.load_extension(\"erpl\");\n",
    "con.sql(\"\"\"\n",
    "SET sap_ashost = 'localhost';\n",
    "SET sap_sysnr = '00';\n",
    "SET sap_user = 'DEVELOPER';\n",
    "SET sap_password = 'ABAPtr1909';\n",
    "SET sap_client = '001';\n",
    "SET sap_lang = 'EN';\n",
    "\"\"\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dd12747d-7585-49a7-8148-5c9fdda0daf1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┐\n",
       "│   msg   │\n",
       "│ varchar │\n",
       "├─────────┤\n",
       "│ PONG    │\n",
       "└─────────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"PRAGMA sap_rfc_ping\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2b521f6-0505-4ca5-ad46-7d777336de42",
   "metadata": {},
   "source": [
    "If the loading of the extension was successful, we can find the exportet functions in the list of `duckdb_functions()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "57bb0c8d-329b-4231-aef8-37677f684811",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────┬─────────────┬──────────────────────┬───────────────┬───┬──────────┬──────────────┬─────────┐\n",
       "│ database_name │ schema_name │    function_name     │ function_type │ … │ internal │ function_oid │ example │\n",
       "│    varchar    │   varchar   │       varchar        │    varchar    │   │ boolean  │    int64     │ varchar │\n",
       "├───────────────┼─────────────┼──────────────────────┼───────────────┼───┼──────────┼──────────────┼─────────┤\n",
       "│ system        │ main        │ sap_read_table       │ table         │ … │ true     │         1412 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_invoke       │ table         │ … │ true     │         1400 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_show_funct…  │ table         │ … │ true     │         1402 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_show_group   │ table         │ … │ true     │         1404 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_describe_f…  │ table         │ … │ true     │         1406 │ NULL    │\n",
       "│ system        │ main        │ sap_show_tables      │ table         │ … │ true     │         1408 │ NULL    │\n",
       "│ system        │ main        │ sap_describe_fields  │ table         │ … │ true     │         1410 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_show        │ table         │ … │ true     │         1426 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_show_cubes  │ table         │ … │ true     │         1428 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_show_quer…  │ table         │ … │ true     │         1430 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_describe    │ table         │ … │ true     │         1432 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_begin       │ table         │ … │ true     │         1434 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_columns     │ table         │ … │ true     │         1436 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_rows        │ table         │ … │ true     │         1438 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_filter      │ table         │ … │ true     │         1440 │ NULL    │\n",
       "│ system        │ main        │ sap_bics_result      │ table         │ … │ true     │         1442 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_ping         │ pragma        │ … │ true     │         1398 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_set_trace_…  │ pragma        │ … │ true     │         1414 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_set_trace_…  │ pragma        │ … │ true     │         1416 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_set_maximu…  │ pragma        │ … │ true     │         1418 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_set_maximu…  │ pragma        │ … │ true     │         1420 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_set_ini_path │ pragma        │ … │ true     │         1422 │ NULL    │\n",
       "│ system        │ main        │ sap_rfc_reload_ini…  │ pragma        │ … │ true     │         1424 │ NULL    │\n",
       "├───────────────┴─────────────┴──────────────────────┴───────────────┴───┴──────────┴──────────────┴─────────┤\n",
       "│ 23 rows                                                                               14 columns (7 shown) │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT * FROM duckdb_functions() WHERE function_name LIKE '%sap%';\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bc18ff8-46e0-4d68-8a26-204eb0275a48",
   "metadata": {},
   "source": [
    "## Explore the schema of the relevant tables\n",
    "\n",
    "The ERPL extension provides the method `sap_describe_fields` to explore the data dictionary schema of the respective table. For exploring local data we also can use the `DESCRIBE` command to get the fields of e.g. a CSV-file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "54786655-b65e-4d44-a5d3-18785a321137",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬─────────┬────────────┬──────────────────────┬───┬─────────────┬───────────┬───────────┬──────────┐\n",
       "│   pos   │ is_key  │   field    │         text         │ … │ check_table │ ref_table │ ref_field │ language │\n",
       "│ varchar │ varchar │  varchar   │       varchar        │   │   varchar   │  varchar  │  varchar  │ varchar  │\n",
       "├─────────┼─────────┼────────────┼──────────────────────┼───┼─────────────┼───────────┼───────────┼──────────┤\n",
       "│ 0001    │ X       │ MANDT      │ Client               │ … │ T000        │           │           │ E        │\n",
       "│ 0002    │ X       │ CARRID     │ Airline Code         │ … │ SCARR       │           │           │ E        │\n",
       "│ 0003    │ X       │ CONNID     │ Flight Connection …  │ … │ SPFLI       │           │           │ E        │\n",
       "│ 0004    │ X       │ FLDATE     │ Flight date          │ … │             │           │           │ E        │\n",
       "│ 0005    │         │ PRICE      │ Airfare              │ … │             │ SFLIGHT   │ CURRENCY  │ E        │\n",
       "│ 0006    │         │ CURRENCY   │ Local currency of …  │ … │ SCURX       │           │           │ E        │\n",
       "│ 0007    │         │ PLANETYPE  │ Aircraft Type        │ … │ SAPLANE     │           │           │ E        │\n",
       "│ 0008    │         │ SEATSMAX   │ Maximum Capacity i…  │ … │             │           │           │ E        │\n",
       "│ 0009    │         │ SEATSOCC   │ Occupied Seats in …  │ … │             │           │           │ E        │\n",
       "│ 0010    │         │ PAYMENTSUM │ Total of current b…  │ … │             │ SFLIGHT   │ CURRENCY  │ E        │\n",
       "│ 0011    │         │ SEATSMAX_B │ Maximum Capacity i…  │ … │             │           │           │ E        │\n",
       "│ 0012    │         │ SEATSOCC_B │ Occupied Seats in …  │ … │             │           │           │ E        │\n",
       "│ 0013    │         │ SEATSMAX_F │ Maximum Capacity i…  │ … │             │           │           │ E        │\n",
       "│ 0014    │         │ SEATSOCC_F │ Occupied Seats in …  │ … │             │           │           │ E        │\n",
       "├─────────┴─────────┴────────────┴──────────────────────┴───┴─────────────┴───────────┴───────────┴──────────┤\n",
       "│ 14 rows                                                                               11 columns (8 shown) │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT * FROM sap_describe_fields('SFLIGHT');\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dc9a47be-3532-4985-bf21-7d4e6524f5a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬─────────┬───────────┬──────────────────────┬───┬──────────┬─────────────┬───────────┬───────────┬──────────┐\n",
       "│   pos   │ is_key  │   field   │         text         │ … │ decimals │ check_table │ ref_table │ ref_field │ language │\n",
       "│ varchar │ varchar │  varchar  │       varchar        │   │ varchar  │   varchar   │  varchar  │  varchar  │ varchar  │\n",
       "├─────────┼─────────┼───────────┼──────────────────────┼───┼──────────┼─────────────┼───────────┼───────────┼──────────┤\n",
       "│ 0001    │ X       │ MANDT     │ Client               │ … │ 000000   │ T000        │           │           │ E        │\n",
       "│ 0002    │ X       │ CARRID    │ Airline Code         │ … │ 000000   │ SCARR       │           │           │ E        │\n",
       "│ 0003    │ X       │ CONNID    │ Flight Connection …  │ … │ 000000   │             │           │           │ E        │\n",
       "│ 0004    │         │ COUNTRYFR │ Country Key          │ … │ 000000   │ SGEOCITY    │           │           │ E        │\n",
       "│ 0005    │         │ CITYFROM  │ Departure city       │ … │ 000000   │ SGEOCITY    │           │           │ E        │\n",
       "│ 0006    │         │ AIRPFROM  │ Departure airport    │ … │ 000000   │ SAIRPORT    │           │           │ E        │\n",
       "│ 0007    │         │ COUNTRYTO │ Country Key          │ … │ 000000   │ SGEOCITY    │           │           │ E        │\n",
       "│ 0008    │         │ CITYTO    │ Arrival city         │ … │ 000000   │ SGEOCITY    │           │           │ E        │\n",
       "│ 0009    │         │ AIRPTO    │ Destination airport  │ … │ 000000   │ SAIRPORT    │           │           │ E        │\n",
       "│ 0010    │         │ FLTIME    │ Flight time          │ … │ 000000   │             │           │           │ E        │\n",
       "│ 0011    │         │ DEPTIME   │ Departure time       │ … │ 000000   │             │           │           │ E        │\n",
       "│ 0012    │         │ ARRTIME   │ Arrival time         │ … │ 000000   │             │           │           │ E        │\n",
       "│ 0013    │         │ DISTANCE  │ Distance             │ … │ 000004   │             │ SPFLI     │ DISTID    │ E        │\n",
       "│ 0014    │         │ DISTID    │ Mass unit of dista…  │ … │ 000000   │             │           │           │ E        │\n",
       "│ 0015    │         │ FLTYPE    │ Flight type          │ … │ 000000   │             │           │           │ E        │\n",
       "│ 0016    │         │ PERIOD    │ Arrival n day(s) l…  │ … │ 000000   │             │           │           │ E        │\n",
       "├─────────┴─────────┴───────────┴──────────────────────┴───┴──────────┴─────────────┴───────────┴───────────┴──────────┤\n",
       "│ 16 rows                                                                                         11 columns (9 shown) │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT * FROM sap_describe_fields('SPFLI');\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "029fedd2-36b4-4c55-8bc1-57b0b1bf57c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐\n",
       "│ column_name │ column_type │  null   │   key   │ default │  extra  │\n",
       "│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │\n",
       "├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤\n",
       "│ FLDATE      │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ COUNTRY     │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ CITY        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ TEMPERATURE │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ CONDITION   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"DESCRIBE SELECT * FROM 'WEATHER.csv'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9931704c-3a8e-4bfd-b189-d3d25bfab20e",
   "metadata": {},
   "source": [
    "## Join tables query\n",
    "\n",
    "The actual SQL query joins the three tables and performs the following operations:\n",
    "- Retrieves flight details from `SFLIGHT` using ERPL's `sap_read_table`, aliasing it as `f`.\n",
    "- Again using ERPL's `sap_read_table` we join `SPFLI` (aliased as `s`) on `MANDT`, `CARRID`, and `CONNID` to get the flight's city of origin and destination.\n",
    "- Incorporates two instances of an external weather data CSV file, `w_from` and `w_to`, matching on flight date and respective cities' country and name for departure and arrival.\n",
    "- Rounds the temperature data to one decimal place for readability.\n",
    "- Orders the results by `CARRIER_ID`, `CONNECTION_ID`, and `FLIGHT_DATE`.\n",
    "- Limits the output to the first 25 rows for a concise view.\n",
    "\n",
    "The output of this query will provide a comprehensive view of the flights, including their departure and arrival cities, and the corresponding temperatures, thus offering valuable insights for flight operations analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "42646643-c993-43e4-b18e-d832df481746",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬─────────┬────────────┬───────────┬───────────┬──────────┬─────────┐\n",
       "│ CARRID  │ CONNID  │   FLDATE   │ CITY_FROM │ TEMP_FROM │ CITY_TO  │ TEMP_TO │\n",
       "│ varchar │ varchar │    date    │  varchar  │  double   │ varchar  │ double  │\n",
       "├─────────┼─────────┼────────────┼───────────┼───────────┼──────────┼─────────┤\n",
       "│ LH      │ 0400    │ 2016-11-18 │ FRANKFURT │      11.7 │ NEW YORK │    21.5 │\n",
       "│ LH      │ 0400    │ 2017-02-06 │ FRANKFURT │      18.5 │ NEW YORK │    13.8 │\n",
       "│ LH      │ 0400    │ 2017-04-27 │ FRANKFURT │      18.2 │ NEW YORK │    19.9 │\n",
       "│ LH      │ 0400    │ 2017-04-28 │ FRANKFURT │      22.3 │ NEW YORK │    18.7 │\n",
       "│ LH      │ 0400    │ 2017-05-28 │ FRANKFURT │      21.5 │ NEW YORK │    23.1 │\n",
       "│ LH      │ 0400    │ 2017-07-16 │ FRANKFURT │      20.7 │ NEW YORK │    28.1 │\n",
       "│ LH      │ 0400    │ 2017-10-04 │ FRANKFURT │      13.2 │ NEW YORK │    27.1 │\n",
       "│ LH      │ 0400    │ 2017-12-23 │ FRANKFURT │      27.6 │ NEW YORK │    19.4 │\n",
       "└─────────┴─────────┴────────────┴───────────┴───────────┴──────────┴─────────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"\"\"\n",
    "SELECT \n",
    "  f.CARRID,\n",
    "  f.CONNID,\n",
    "  f.FLDATE,\n",
    "  s.CITYFROM as CITY_FROM,\n",
    "  ROUND(w_from.TEMPERATURE, 1) as TEMP_FROM,\n",
    "  s.CITYTO as CITY_TO,\n",
    "  ROUND(w_to.TEMPERATURE, 1) as TEMP_TO,\n",
    "  FROM sap_read_table('SFLIGHT') AS f\n",
    "  JOIN sap_read_table('SPFLI') AS s \n",
    "      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\n",
    "  JOIN 'WEATHER.csv' AS w_from\n",
    "      ON (f.FLDATE = w_from.FLDATE AND s.COUNTRYFR = w_from.COUNTRY AND s.CITYFROM = w_from.CITY)\n",
    "  JOIN 'WEATHER.csv' AS w_to\n",
    "      ON (f.FLDATE = w_to.FLDATE AND s.COUNTRYTO = w_to.COUNTRY AND s.CITYTO = w_to.CITY)\n",
    "  ORDER BY 1, 2, 3\n",
    "  LIMIT 25\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "026382f1-62a6-4e88-9ee5-3c574e0da0e6",
   "metadata": {},
   "source": [
    "# Call BAPIS directly\n",
    "In a second example, we call BAPIs of the flight scenario directly via RFC."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccfc5ab1-19f6-409b-8b17-c55c5cd39c13",
   "metadata": {},
   "source": [
    "### Explore the API of a BAPI\n",
    "First we search relevant BAPIS stemming from the flights example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "1471d1fe-c6c4-407e-bbaf-de1d6be11b2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────────────────────┬────────────────────┬─────────┬─────────┬─────────────────────────────┐\n",
       "│           FUNCNAME            │     GROUPNAME      │  APPL   │  HOST   │            STEXT            │\n",
       "│            varchar            │      varchar       │ varchar │ varchar │           varchar           │\n",
       "├───────────────────────────────┼────────────────────┼─────────┼─────────┼─────────────────────────────┤\n",
       "│ BAPI_FLIGHT_CHECKAVAILIBILITY │ SAPBC_BAPI_SFLIGHT │         │         │ Check flight availability   │\n",
       "│ BAPI_FLIGHT_GETDETAIL         │ SAPBC_BAPI_SFLIGHT │         │         │ Find details about a flight │\n",
       "│ BAPI_FLIGHT_GETLIST           │ SAPBC_BAPI_SFLIGHT │         │         │ Find list of flights        │\n",
       "│ BAPI_FLIGHT_SAVEREPLICA       │ SAPBC_BAPI_SFLIGHT │         │         │ Save replicated flight data │\n",
       "└───────────────────────────────┴────────────────────┴─────────┴─────────┴─────────────────────────────┘"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT * FROM sap_rfc_search_function(FUNCNAME='BAPI_FLIGHT*') ORDER BY 1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4acf06ae-f6b9-4133-8946-8432ff84c78a",
   "metadata": {},
   "source": [
    "With `sap_rfc_invoke` (as the function name implies) we can invoke functions and get return values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "939cfefb-6906-46e8-840d-891deafa4e22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────┬───────────────────┬───────────┬────────────┬───┬────────────┬───────────────┬─────────┬──────────┐\n",
       "│ AIRLINEID │      AIRLINE      │ CONNECTID │ FLIGHTDATE │ … │  ARRDATE   │     PRICE     │  CURR   │ CURR_ISO │\n",
       "│  varchar  │      varchar      │  varchar  │    date    │   │    date    │ decimal(12,4) │ varchar │ varchar  │\n",
       "├───────────┼───────────────────┼───────────┼────────────┼───┼────────────┼───────────────┼─────────┼──────────┤\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2016-11-18 │ … │ 2016-11-18 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-02-06 │ … │ 2017-02-06 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-04-27 │ … │ 2017-04-27 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-04-28 │ … │ 2017-04-28 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-05-28 │ … │ 2017-05-28 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-07-16 │ … │ 2017-07-16 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-10-04 │ … │ 2017-10-04 │      666.0000 │ EUR     │ EUR      │\n",
       "│ LH        │ Lufthansa         │ 0400      │ 2017-12-23 │ … │ 2017-12-23 │      666.0000 │ EUR     │ EUR      │\n",
       "│ AA        │ American Airlines │ 0017      │ 2016-11-15 │ … │ 2016-11-15 │      422.9400 │ USD     │ USD      │\n",
       "│ AA        │ American Airlines │ 0017      │ 2017-02-03 │ … │ 2017-02-03 │      422.9400 │ USD     │ USD      │\n",
       "│ ·         │       ·           │  ·        │     ·      │ · │     ·      │          ·    │  ·      │  ·       │\n",
       "│ ·         │       ·           │  ·        │     ·      │ · │     ·      │          ·    │  ·      │  ·       │\n",
       "│ ·         │       ·           │  ·        │     ·      │ · │     ·      │          ·    │  ·      │  ·       │\n",
       "│ JL        │ Japan Airlines    │ 0407      │ 2017-04-26 │ … │ 2017-04-26 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0407      │ 2017-07-15 │ … │ 2017-07-15 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0407      │ 2017-10-03 │ … │ 2017-10-03 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0407      │ 2017-12-22 │ … │ 2017-12-22 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0408      │ 2016-11-18 │ … │ 2016-11-19 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0408      │ 2017-02-06 │ … │ 2017-02-07 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0408      │ 2017-04-27 │ … │ 2017-04-28 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0408      │ 2017-07-16 │ … │ 2017-07-17 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0408      │ 2017-10-04 │ … │ 2017-10-05 │   106136.0000 │ JPY     │ JPY      │\n",
       "│ JL        │ Japan Airlines    │ 0408      │ 2017-12-23 │ … │ 2017-12-24 │   106136.0000 │ JPY     │ JPY      │\n",
       "├───────────┴───────────────────┴───────────┴────────────┴───┴────────────┴───────────────┴─────────┴──────────┤\n",
       "│ 94 rows (20 shown)                                                                      14 columns (8 shown) │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT * FROM sap_rfc_invoke('BAPI_FLIGHT_GETLIST', path='/FLIGHT_LIST')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e0eea7d-d847-49cb-b334-0d7db5b423bd",
   "metadata": {},
   "source": [
    "With the method `sap_rfc_describe_function('$BAPI_NAME')` one can explore the API of a RFC function. The method returns four columns:\n",
    "\n",
    "- `NAME`: Containing the full name of the BAPI, typically this is the same as the argument of the function.\n",
    "- `IMPORT`: Contains a list with description of all input types. Have a look especially at the required flag. This parameters have to be provided.\n",
    "- `EXPORT`: Also list with export parameters.\n",
    "- `CHANGING`: Are so called in/out parameters, which can be input as well as output.\n",
    "- `TABLES`: This are parameters in form of tables (which are lists of structs in DuckDB). Tables can also have in/out direction.\n",
    "\n",
    "In the previous example we used the `path` parameter of the invoke function to select the `FLIGHT_LIST` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "2447701d-e45d-4d3e-876b-c37853b66867",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/json": [
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_IMPORT",
        "LENGTH": 3,
        "NAME": "AIRLINE",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Select airline",
        "TYPE": "RFCTYPE_CHAR"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_IMPORT",
        "LENGTH": 28,
        "NAME": "DESTINATION_FROM",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Select departure city",
        "TYPE": "RFCTYPE_STRUCTURE"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_IMPORT",
        "LENGTH": 28,
        "NAME": "DESTINATION_TO",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Select destination",
        "TYPE": "RFCTYPE_STRUCTURE"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_IMPORT",
        "LENGTH": 4,
        "NAME": "MAX_ROWS",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Maximum Number of Lines of Hits",
        "TYPE": "RFCTYPE_INT"
       }
      ],
      "text/plain": [
       "<IPython.core.display.JSON object>"
      ]
     },
     "metadata": {
      "application/json": {
       "expanded": false,
       "root": "IMPORT"
      }
     },
     "output_type": "display_data"
    },
    {
     "data": {
      "application/json": [
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_TABLES",
        "LENGTH": 19,
        "NAME": "DATE_RANGE",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Selection range for flight date",
        "TYPE": "RFCTYPE_TABLE"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_TABLES",
        "LENGTH": 990,
        "NAME": "EXTENSION_IN",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Import customer enhancements",
        "TYPE": "RFCTYPE_TABLE"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_TABLES",
        "LENGTH": 990,
        "NAME": "EXTENSION_OUT",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Export customer enhancements",
        "TYPE": "RFCTYPE_TABLE"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_TABLES",
        "LENGTH": 121,
        "NAME": "FLIGHT_LIST",
        "OPTIONAL": true,
        "PARAMETERTEXT": "List of flights",
        "TYPE": "RFCTYPE_TABLE"
       },
       {
        "DECIMALS": 0,
        "DEFAULTVALUE": "",
        "DIRECTION": "RFC_TABLES",
        "LENGTH": 548,
        "NAME": "RETURN",
        "OPTIONAL": true,
        "PARAMETERTEXT": "Return Messages",
        "TYPE": "RFCTYPE_TABLE"
       }
      ],
      "text/plain": [
       "<IPython.core.display.JSON object>"
      ]
     },
     "metadata": {
      "application/json": {
       "expanded": false,
       "root": "TABLES"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import JSON\n",
    "\n",
    "desc = con.sql(\"SELECT * FROM sap_rfc_describe_function('BAPI_FLIGHT_GETLIST')\").df()\n",
    "#display(desc.iloc[0])\n",
    "display(JSON(desc[\"IMPORT\"][0], root=\"IMPORT\"))\n",
    "display(JSON(desc[\"TABLES\"][0], root=\"TABLES\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "a14f9a57-9017-4004-804e-1a8776f40655",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'NAME': 'AIRLINEID',\n",
       "  'TYPE': 'RFCTYPE_CHAR',\n",
       "  'DIRECTION': 'RFC_IMPORT',\n",
       "  'LENGTH': 3,\n",
       "  'DECIMALS': 0,\n",
       "  'DEFAULTVALUE': '',\n",
       "  'PARAMETERTEXT': 'Airline Code',\n",
       "  'OPTIONAL': False},\n",
       " {'NAME': 'CONNECTIONID',\n",
       "  'TYPE': 'RFCTYPE_NUM',\n",
       "  'DIRECTION': 'RFC_IMPORT',\n",
       "  'LENGTH': 4,\n",
       "  'DECIMALS': 0,\n",
       "  'DEFAULTVALUE': '',\n",
       "  'PARAMETERTEXT': 'Flight connection code',\n",
       "  'OPTIONAL': False},\n",
       " {'NAME': 'FLIGHTDATE',\n",
       "  'TYPE': 'RFCTYPE_DATE',\n",
       "  'DIRECTION': 'RFC_IMPORT',\n",
       "  'LENGTH': 8,\n",
       "  'DECIMALS': 0,\n",
       "  'DEFAULTVALUE': '',\n",
       "  'PARAMETERTEXT': 'Departure date',\n",
       "  'OPTIONAL': False}]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\"SELECT * FROM sap_rfc_describe_function('BAPI_FLIGHT_GETDETAIL')\").fetchone()[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "094d3ccc-4f23-457c-aa0a-09c4ee04e261",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ADDITIONAL_INFO': {'FLIGHTTIME': 444,\n",
       "  'DISTANCE': Decimal('6162.0000'),\n",
       "  'UNIT': 'KM',\n",
       "  'UNIT_ISO': 'KMT',\n",
       "  'PLANETYPE': 'A340-600',\n",
       "  'FLIGHTTYPE': ''},\n",
       " 'AVAILIBILITY': {'ECONOMAX': 330,\n",
       "  'ECONOFREE': 10,\n",
       "  'BUSINMAX': 30,\n",
       "  'BUSINFREE': 0,\n",
       "  'FIRSTMAX': 20,\n",
       "  'FIRSTFREE': 0},\n",
       " 'FLIGHT_DATA': {'AIRLINEID': 'LH',\n",
       "  'AIRLINE': 'Lufthansa',\n",
       "  'CONNECTID': '0400',\n",
       "  'FLIGHTDATE': datetime.date(2016, 11, 18),\n",
       "  'AIRPORTFR': 'FRA',\n",
       "  'CITYFROM': 'FRANKFURT',\n",
       "  'AIRPORTTO': 'JFK',\n",
       "  'CITYTO': 'NEW YORK',\n",
       "  'DEPTIME': datetime.time(10, 10),\n",
       "  'ARRTIME': datetime.time(11, 34),\n",
       "  'ARRDATE': datetime.date(2016, 11, 18),\n",
       "  'PRICE': Decimal('666.0000'),\n",
       "  'CURR': 'EUR',\n",
       "  'CURR_ISO': 'EUR'},\n",
       " 'EXTENSION_IN': [],\n",
       " 'EXTENSION_OUT': [],\n",
       " 'RETURN': [{'TYPE': 'S',\n",
       "   'ID': 'BC_IBF',\n",
       "   'NUMBER': '000',\n",
       "   'MESSAGE': 'Method was executed',\n",
       "   'LOG_NO': '',\n",
       "   'LOG_MSG_NO': '000000',\n",
       "   'MESSAGE_V1': '',\n",
       "   'MESSAGE_V2': '',\n",
       "   'MESSAGE_V3': '',\n",
       "   'MESSAGE_V4': '',\n",
       "   'PARAMETER': '',\n",
       "   'ROW': 0,\n",
       "   'FIELD': '',\n",
       "   'SYSTEM': 'A4HCLNT001'}]}"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res = con.sql(\"SELECT * FROM sap_rfc_invoke('BAPI_FLIGHT_GETDETAIL', {'AIRLINEID': 'LH', 'CONNECTIONID': '0400', 'FLIGHTDATE': '2016-11-18'::DATE })\").df()\n",
    "res.to_dict(orient=\"records\")[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df7995c5-3feb-45ab-8fd0-0a13d2f09c7d",
   "metadata": {},
   "source": [
    "# Appendix"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e94d3879-da72-4237-a9e7-c53ed367a330",
   "metadata": {},
   "source": [
    "## Creating artifical weather information\n",
    "Of course we did not use real weather information (just in case you were confused), but used the following code to create the CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e1b2920e-4db2-4347-aabb-cf75c2151b4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy.random as npr\n",
    "\n",
    "df_weather = con.sql(\"\"\"\n",
    "SELECT DISTINCT\n",
    "  f.FLDATE,\n",
    "  s.COUNTRYFR as COUNTRY,\n",
    "  s.CITYFROM as CITY,\n",
    "  FROM sap_read_table('SFLIGHT') as f\n",
    "  JOIN sap_read_table('SPFLI') as s \n",
    "      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\n",
    "UNION\n",
    "SELECT DISTINCT\n",
    "  f.FLDATE,\n",
    "  s.COUNTRYTO as COUNTRY,\n",
    "  s.CITYTO as CITY\n",
    "  FROM sap_read_table('SFLIGHT') as f\n",
    "  JOIN sap_read_table('SPFLI') as s \n",
    "      ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)\n",
    "\"\"\").to_df()\n",
    "\n",
    "weather_descriptions = [\n",
    "    \"clear sky\",\n",
    "    \"few clouds\",\n",
    "    \"scattered clouds\",\n",
    "    \"broken clouds\",\n",
    "    \"shower rain\",\n",
    "    \"rain\",\n",
    "    \"thunderstorm\",\n",
    "    \"snow\",\n",
    "    \"mist\",\n",
    "    \"thunderstorm with light rain\",\n",
    "    \"thunderstorm with rain\",\n",
    "    \"thunderstorm with heavy rain\",\n",
    "    \"light thunderstorm\",\n",
    "    \"thunderstorm\",\n",
    "    \"heavy thunderstorm\",\n",
    "    \"ragged thunderstorm\",\n",
    "    \"thunderstorm with light drizzle\",\n",
    "    \"thunderstorm with drizzle\",\n",
    "    \"thunderstorm with heavy drizzle\",\n",
    "    \"light intensity drizzle\",\n",
    "    \"drizzle\",\n",
    "    \"heavy intensity drizzle\",\n",
    "    \"light intensity drizzle rain\",\n",
    "    \"drizzle rain\",\n",
    "    \"heavy intensity drizzle rain\",\n",
    "    \"shower rain and drizzle\",\n",
    "    \"heavy shower rain and drizzle\",\n",
    "    \"shower drizzle\",\n",
    "    \"light rain\",\n",
    "    \"moderate rain\",\n",
    "    \"heavy intensity rain\",\n",
    "    \"very heavy rain\",\n",
    "    \"extreme rain\",\n",
    "    \"freezing rain\",\n",
    "    \"light intensity shower rain\",\n",
    "    \"shower rain\",\n",
    "    \"heavy intensity shower rain\",\n",
    "    \"ragged shower rain\",\n",
    "    \"light snow\",\n",
    "    \"snow\",\n",
    "    \"heavy snow\",\n",
    "    \"sleet\",\n",
    "    \"light shower sleet\",\n",
    "    \"shower sleet\",\n",
    "    \"light rain and snow\",\n",
    "    \"rain and snow\",\n",
    "    \"light shower snow\",\n",
    "    \"shower snow\",\n",
    "    \"heavy shower snow\"\n",
    "]\n",
    "\n",
    "df_weather[\"TEMPERATURE\"] = npr.normal(loc=20., scale=5., size=len(df_weather))\n",
    "df_weather[\"CONDITION\"] = npr.choice(weather_descriptions, size=len(df_weather))\n",
    "\n",
    "df_weather.to_csv(\"./WEATHER.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2663540d-9e48-4c94-a785-5223637044fd",
   "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}