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 │
├───────────┼────────┼───────┼────────┼────────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
│ alvarpe01 │   2015 │     1 │ PIT    │ NL     │   150 │   437 │    60 │   106 │    18 │     0 │    27 │    77 │     2 │     0 │    48 │   131 │     9 │     2 │     0 │     4 │     6 │
│ beltrad01 │   2015 │     1 │ TEX    │ AL     │   143 │   567 │    83 │   163 │    32 │     4 │    18 │    83 │     1 │     0 │    41 │    65 │     4 │     3 │     0 │     8 │    18 │
│ cabreme01 │   2015 │     1 │ CHA    │ AL     │   158 │   629 │    70 │   172 │    36 │     2 │    12 │    77 │     3 │     0 │    40 │    88 │     2 │     2 │     2 │    10 │    18 │
│ cabremi01 │   2015 │     1 │ DET    │ AL     │   119 │   429 │    64 │   145 │    28 │     1 │    18 │    76 │     1 │     1 │    77 │    82 │    15 │     3 │     0 │     2 │    19 │
│ crawfbr01 │   2015 │     1 │ SFN    │ NL     │   143 │   507 │    65 │   130 │    33 │     4 │    21 │    84 │     6 │     4 │    39 │   119 │     9 │    11 │     0 │     4 │    18 │
│ desmoia01 │   2015 │     1 │ WAS    │ NL     │   156 │   583 │    69 │   136 │    27 │     2 │    19 │    62 │    13 │     5 │    45 │   187 │     0 │     3 │     6 │     4 │     9 │
│ dickera01 │   2015 │     1 │ TOR    │ AL     │    33 │     3 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     1 │     0 │     0 │     1 │     0 │     0 │
│ fieldpr01 │   2015 │     1 │ TEX    │ AL     │   158 │   613 │    78 │   187 │    28 │     0 │    23 │    98 │     0 │     0 │    64 │    88 │    14 │    11 │     0 │     5 │    21 │
│ gallayo01 │   2015 │     1 │ TEX    │ AL     │    33 │     4 │     0 │     2 │     1 │     0 │     0 │     0 │     0 │     0 │     0 │     1 │     0 │     0 │     0 │     0 │     0 │
│ hudsoda01 │   2015 │     1 │ ARI    │ NL     │    64 │     1 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     1 │     0 │     0 │     0 │     0 │     0 │
│ …         │      … │     … │ …      │ …      │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │
└───────────┴────────┴───────┴────────┴────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘