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/.cache/pypoetry/virtualenvs/letsql-uJiiVu6z-py3.10/lib/python3.10/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 │
│ arenano01 │   2015 │     1 │ COL    │ NL     │   157 │   616 │    97 │   177 │    43 │     4 │    42 │   130 │     2 │     5 │    34 │   110 │    13 │     4 │     0 │    11 │    17 │
│ arrieja01 │   2015 │     1 │ CHN    │ NL     │    33 │    79 │     5 │    12 │     1 │     1 │     2 │     2 │     0 │     0 │     1 │    45 │     0 │     0 │     3 │     0 │     1 │
│ axforjo01 │   2015 │     1 │ COL    │ NL     │    60 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │
│ barneda01 │   2015 │     1 │ LAN    │ NL     │     2 │     4 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │     0 │
│ barneda01 │   2015 │     2 │ TOR    │ AL     │    15 │    23 │     4 │     7 │     1 │     0 │     2 │     4 │     0 │     0 │     1 │     2 │     0 │     0 │     2 │     0 │     0 │
│ beltrad01 │   2015 │     1 │ TEX    │ AL     │   143 │   567 │    83 │   163 │    32 │     4 │    18 │    83 │     1 │     0 │    41 │    65 │     4 │     3 │     0 │     8 │    18 │
│ beltrca01 │   2015 │     1 │ NYA    │ AL     │   133 │   478 │    57 │   132 │    34 │     1 │    19 │    67 │     0 │     0 │    45 │    85 │     2 │     2 │     0 │     6 │    12 │
│ bournmi01 │   2015 │     1 │ CLE    │ AL     │    95 │   289 │    29 │    71 │    12 │     1 │     0 │    19 │    13 │     5 │    29 │    76 │     0 │     0 │     7 │     1 │     2 │
│ bournmi01 │   2015 │     2 │ ATL    │ NL     │    46 │   136 │    10 │    30 │     3 │     1 │     0 │    11 │     4 │     2 │    17 │    31 │     0 │     0 │     1 │     2 │     3 │
│ …         │      … │     … │ …      │ …      │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │     … │
└───────────┴────────┴───────┴────────┴────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘