Ibis Impala JOIN problem with relabel/name 'column AS newName'

Question

When you use the Ibis API to query impala, for some reason Ibis API forces it to become a subquery (when you join 4-5 tables it suddenly becomes super slow). It simply won't join normally, due to column name overlap problem on joins. I want a way to quickly rename the columns perhaps, isn't that's how SQL usually works?

i0 = impCon.table('shop_inventory')
s0 = impCon.table('shop_expenditure')
s0 = s0.relabel({'element_date': 'spend_element_date', 'element_shop_item': 'spend_shop_item'})
jn = i0.inner_join(s0, [i0['element_date'] == s0['spend_element_date'], i0['element_shop_item'] == s0['spend_shop_item']])
jn.materialize()
jn.execute(limit=900)

Then you have IBIS generating SQL that is SUBQUERYING it without me suggesting it:

SELECT *
FROM (
  SELECT `element_date`, `element_shop_item`, `element_address`, `element_expiration`,
         `element_category`, `element_description` 
  FROM dbp.`shop_inventory`
) t0
  INNER JOIN (
    SELECT `element_shop_item` AS `spend_shop_item`, `element_comm` AS `spend_comm`,
           `element_date` AS `spend_date`, `element_amount`,
           `element_spend_type`, `element_shop_item_desc`
    FROM dbp.`shop_spend`
  ) t1
    ON (`element_shop_item` = t1.`spend_shop_item`) AND
       (`element_category` = t1.`spend_category`) AND
       (`element_subcategory` = t1.`spend_subcategory`) AND
       (`element_comm` = t1.`spend_comm`) AND
       (`element_date` = t1.`spend_date`)
LIMIT 900

Why is this so difficult?

It should be ideally as simple as:

jn = i0.inner_join(s0, [s0['element_date'].as('spend_date') == i0['element_date']]

to generate a single: SELECT s0.element_date as spend_date, i0.element_date INNER JOIN s0 dbp.shop_spend ON s0.spend_date == i0.element_date

right?

Are we not ever allowed to have same column names on tables that are being joined? I am pretty sure in raw SQL you can just use "X AS Y" without having to need subquery.

score 2 · Answer 1 · edited Jun 15 '19 at 22:16

I spent the last few hours struggling with this same issue. A better solution I found is to do the following. Join keeping the variable names the same. Then, before you materialize, only select a subset of the variables such that there isn't any overlap.

So in your code it would look something like this:

jn = i0.inner_join(s0, [i0['element_date'] == s0['element_date'], i0['element_shop_item'] == s0['element_shop_item']])

expr = jn[i0, s0['variable_of_interest_1'],s0['variable_of_interest_2']]
expr.materialize()

See here for more resources https://docs.ibis-project.org/sql.html

Ibis Impala JOIN problem with relabel/name 'column AS newName'

1 Answers1