Can hive sql do the same job like this?

[Neil Xu <ne...@gmail.com>]

In oracle,

select a.str1,
          b.str2
from ( select str1 from table1) a,
     ( select str2 from  table2) b
where instr(a.str1,a.str2) > 0

I know that 'from a, b' is not supported in hive, but table1 and table2 have
no common keys, if 'join' is used, it will be a FULL OUTER JOIN,
when table1 and table2 have large number of tuples, the task will probably
fail

so, is there any better way to do the job? Thanks in advance


Neil

Re: Can hive sql do the same job like this?

[wd <wd...@wdicc.com>]

join_table:
    table_reference JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON equality_expression ( AND equality_expression )*

equality_expression:
    expression = expression


Hive only support equality join.

2010/10/9 Neil Xu <ne...@gmail.com>

> In oracle,
>
> select a.str1,
>           b.str2
> from ( select str1 from table1) a,
>      ( select str2 from  table2) b
> where instr(a.str1,a.str2) > 0
>
> I know that 'from a, b' is not supported in hive, but table1 and table2
> have no common keys, if 'join' is used, it will be a FULL OUTER JOIN,
> when table1 and table2 have large number of tuples, the task will probably
> fail
>
> so, is there any better way to do the job? Thanks in advance
>
>
> Neil
>