Concepts

What is LETSQL?

LETSQL is a query engine with a pythonic dataframe interface, built on top of DataFusion that can be used to write multi-engine workflows.

What is not LETSQL?

Is not a dataframe library, while it provides a familiar pythonic dataframe interface, LETSQL is equipped with a query optimizer and can provide in-situ and federated query processing.

Why LETSQL?

By using LETSQL, you will:

• Reduce errors thanks to a better Pythonic UX.
• Accelerate the development process by lowering the cognitive burden induced by using multiple interacting data systems.
• Gain in security by providing in-situ processing (the data does not move).
• Improve performance by avoiding data transfer and redundant operations.
• Reduce costs by easily swapping to the cheapest tool available.

What is Multi-Engine?

What makes LETSQL stand-out against other Ibis backends, is that it can be use to build multi-engine workflows, by multi-engine it means that it can an Ibis expression involving multiple backends in an optimal manner, segmenting the expression and executing each part in-situ on the corresponding backend.

For the following example we are going to use an Ibis table from a Postgres connection and perform a join with an in-memory pandas DataFrame.

import pathlib

import ibis
import pandas as pd

import letsql as ls

ibis.options.interactive = True

# create the letsql connection
con = ls.connect()


# create the Ibis Postgres connection
pg = ibis.postgres.connect(
    host="localhost",
    port=5432,
    user="postgres",
    password="postgres",
    database="ibis_testing",
)


# register Postgres table
batting = con.register(pg.table("batting"), table_name="batting")

# register csv file
df = pd.read_csv("https://raw.githubusercontent.com/ibis-project/testing-data/master/csv/awards_players.csv")
awards_players = con.register(df, table_name="awards_players")

left = batting[batting.yearID == 2015]
right = awards_players[awards_players.lgID == "NL"].drop("yearID", "lgID").execute()

left.join(right, ["playerID"], how="semi")

/home/runner/work/letsql/letsql/.venv/lib/python3.12/site-packages/ibis/expr/types/relations.py:685: FutureWarning: Selecting/filtering arbitrary expressions in `Table.__getitem__` is deprecated and will be removed in version 10.0. Please use `Table.select` or `Table.filter` instead.
  warnings.warn(

┏━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┓
┃ playerID  ┃ yearID ┃ stint ┃ teamID ┃ lgID   ┃ G     ┃ AB    ┃ R     ┃ H     ┃ X2B   ┃ X3B   ┃ HR    ┃ RBI   ┃ SB    ┃ CS    ┃ BB    ┃ SO    ┃ IBB   ┃ HBP   ┃ SH    ┃ SF    ┃ GIDP  ┃
┡━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━┩
│ string    │ int64  │ int64 │ string │ string │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │
├───────────┼────────┼───────┼────────┼────────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
│ braunry02 │   2015 │     1 │ MIL    │ NL     │   140 │   506 │    87 │   144 │    27 │     3 │    25 │    84 │    24 │     4 │    54 │   115 │     4 │     4 │     0 │     3 │    20 │
│ buehrma01 │   2015 │     1 │ TOR    │ AL     │    32 │     7 │     0 │     1 │     0 │     0 │     0 │     0 │     0 │     0 │     1 │     3 │     0 │     0 │     1 │     0 │     0 │
│ carpema01 │   2015 │     1 │ SLN    │ NL     │   154 │   574 │   101 │   156 │    44 │     3 │    28 │    84 │     4 │     3 │    81 │   151 │     5 │     6 │     0 │     4 │     5 │
│ cuddymi01 │   2015 │     1 │ NYN    │ NL     │   117 │   379 │    44 │    98 │    18 │     1 │    10 │    41 │     2 │     0 │    24 │    88 │     0 │     4 │     0 │     1 │    13 │
│ degroja01 │   2015 │     1 │ NYN    │ NL     │    31 │    59 │     3 │    11 │     1 │     0 │     0 │     4 │     0 │     0 │     3 │    16 │     0 │     0 │     4 │     0 │     4 │
│ ethiean01 │   2015 │     1 │ LAN    │ NL     │   142 │   395 │    54 │   116 │    20 │     7 │    14 │    53 │     2 │     3 │    43 │    75 │     2 │     4 │     0 │     3 │    11 │
│ goldspa01 │   2015 │     1 │ ARI    │ NL     │   159 │   567 │   103 │   182 │    38 │     2 │    33 │   110 │    21 │     5 │   118 │   151 │    29 │     2 │     0 │     7 │    16 │
│ gordode01 │   2015 │     1 │ MIA    │ NL     │   145 │   615 │    88 │   205 │    24 │     8 │     4 │    46 │    58 │    20 │    25 │    91 │     2 │     2 │     6 │     5 │     6 │
│ greinza01 │   2015 │     1 │ LAN    │ NL     │    32 │    67 │     8 │    15 │     2 │     0 │     2 │     3 │     1 │     0 │     1 │    14 │     0 │     0 │     8 │     1 │     0 │
│ harvema01 │   2015 │     1 │ NYN    │ NL     │    29 │    65 │     1 │     7 │     2 │     0 │     1 │     7 │     0 │     0 │     0 │    31 │     0 │     0 │     1 │     0 │     2 │
│ …         │      … │     … │ …      │ …      │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │
└───────────┴────────┴───────┴────────┴────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘