About cross-database joins

[vmsaipreeth]

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!

[gforsyth]

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. │ … │
│     … │      … │                 … │ …                       │ … │
└───────┴────────┴───────────────────┴─────────────────────────┴───┘

[vmsaipreeth]

Thanks for the clarification, @gforsyth . Appreciate the explanation.

so, is this feature bound to joins only? or can we insert/create tables too?

Lets say, for example, I have the below trino connection through ibis:

con = ibis.trino.connect(
     user="user",
     auth="",
     host="localhost",
     port=8080,
     database="memory",
     schema="default" )
    
data = con.sql("SQL Query")

the above connection session is with memory catalog and default schema in trino.

is there a way to use the same connection session (con) and insert/create table under memory_2 catalog and default_2 schema in trino ?

[gforsyth]

Sure. I'm going to use Ibis terminology, because schema is criminally overloaded as a word.

catalog: a collection of database
database: a collection of table

So your target is the catalog called memory_2 and the database called default_2

If you have an Ibis expression expr (which could be based on another table, or could be a memtable, etc...) you can do:

con.create_table("new_table_name", expr, database="memory_2.default_2")

or you can specify the location as a tuple of strings:

con.create_table("new_table_name", expr, database=("memory_2", "default_2"))