Multi-Engine

Move data between different engines within a single expression using into_backend().

import xorq.api as xo

# Connect to engines
pg = xo.postgres.connect_env()
db = xo.duckdb.connect()

# Load data from different sources
batting = pg.table("batting")
awards = xo.examples.awards_players.fetch(backend=db)

# Filter in respective engines
recent = batting.filter(batting.yearID == 2015)
nl_awards = awards.filter(awards.lgID == "NL")

# Move data to postgres for join
result = recent.join(
    nl_awards.into_backend(pg),
    ["playerID"]
)

result.execute()
playerID yearID stint teamID lgID G AB R H X2B ... IBB HBP SH SF GIDP awardID yearID_right lgID_right tie notes
0 pujolal01 2015 1 LAA AL 157 602 85 147 22 ... 10 6 0 3 15 Rookie of the Year 2001 NL
1 pujolal01 2015 1 LAA AL 157 602 85 147 22 ... 10 6 0 3 15 Silver Slugger 2001 NL 3B
2 pujolal01 2015 1 LAA AL 157 602 85 147 22 ... 10 6 0 3 15 Hank Aaron Award 2003 NL
3 pujolal01 2015 1 LAA AL 157 602 85 147 22 ... 10 6 0 3 15 Silver Slugger 2003 NL OF
4 wolfra02 2015 1 DET AL 8 0 0 0 0 ... 0 0 0 0 0 TSN All-Star 2003 NL LHP
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
273 harpebr03 2015 1 WAS NL 153 521 118 172 38 ... 15 5 0 4 15 Silver Slugger 2015 NL OF
274 gonzaca01 2015 1 COL NL 153 554 87 150 25 ... 6 1 1 6 11 Silver Slugger 2015 NL OF
275 mccutan01 2015 1 PIT NL 157 566 91 165 36 ... 12 12 0 9 9 Silver Slugger 2015 NL OF
276 bumgama01 2015 1 SFN NL 36 77 9 19 2 ... 0 0 1 0 1 Silver Slugger 2015 NL P
277 crawfbr01 2015 1 SFN NL 143 507 65 130 33 ... 9 11 0 4 18 Silver Slugger 2015 NL SS

278 rows × 27 columns

Key Features

  • Zero-copy transfers using Apache Arrow
  • Automatic optimization of data placement
  • Transparent movement between any supported engines

When to Use

  • Leverage engine strengths - Use DuckDB for local analysis, Postgres for joins
  • Combine data sources - Join tables from different databases
  • Optimize performance - Move small tables to where large tables live