About cross-database joins #10070
Unanswered
vmsaipreeth
asked this question in
Q&A
Replies: 1 comment 2 replies
-
Hi @vmsaipreeth -- First, it's worth noting that in this context, when we say "database" we mean "a collection of tables" -- that PR was ensuring that you can do things like join tables that exist in two separate catalogs. Here's an example for Trino: >>> con = ibis.trino.connect(
...: user="user",
...: auth="",
...: host="localhost",
...: port=8080,
...: database="memory",
...: schema="default",
...: )
>>> con.list_tables()
['array_types',
'astronauts',
'awards_players',
'batting',
'diamonds',
'functional_alltypes',
'json_t',
'map',
'struct',
'topk',
'win']
>>> con.list_tables(database="tpch.sf1")
['customer',
'lineitem',
'nation',
'orders',
'part',
'partsupp',
'region',
'supplier']
>>> astronauts = con.table("astronauts")
>>> customer = con.table("customer", database="tpch.sf1")
>>> astronauts.join(customer, astronauts.id == customer.custkey)
┏━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━┓
┃ id ┃ number ┃ nationwide_number ┃ name ┃ … ┃
┡━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━┩
│ int64 │ int64 │ int64 │ string │ … │
├───────┼────────┼───────────────────┼─────────────────────────┼───┤
│ 1 │ 1 │ 1 │ Gagarin, Yuri │ … │
│ 2 │ 2 │ 2 │ Titov, Gherman │ … │
│ 3 │ 3 │ 1 │ Glenn, John H., Jr. │ … │
│ 4 │ 3 │ 1 │ Glenn, John H., Jr. │ … │
│ 5 │ 4 │ 2 │ Carpenter, M. Scott │ … │
│ 6 │ 5 │ 2 │ Nikolayev, Andriyan │ … │
│ 7 │ 5 │ 2 │ Nikolayev, Andriyan │ … │
│ 8 │ 6 │ 4 │ Popovich, Pavel │ … │
│ 9 │ 6 │ 4 │ Popovich, Pavel │ … │
│ 10 │ 7 │ 3 │ Schirra, Walter M., Jr. │ … │
│ … │ … │ … │ … │ … │
└───────┴────────┴───────────────────┴─────────────────────────┴───┘ |
Beta Was this translation helpful? Give feedback.
2 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hi team
sql: enable cross-database joins (#9849) (c3ff6ae)
Would like to know more in-depth and internal understanding on the above feature especially for trino. It would be great if you can show an example and walk me thru the internal workings on how cross-database joins happen here.
TIA!
Beta Was this translation helpful? Give feedback.
All reactions