0

I want to create the effect of a cross apply in AWS EMR Hive. I've got a little sample code here that runs in SQL Server 2017.

with r as (
select 1 as d
union all
select 2 as d
)
select * from r
cross apply (select 'f' as u) e;

How can I run the equivalent of this in EMR Hive?

I've checked out the Lateral View documentation, but it all references explode, and I don't have an array.

ADataGMan
  • 449
  • 1
  • 7
  • 23

2 Answers2

1

Instead of do CROSS APPLY you may do CROSS JOIN in your case. F.e.:

SET hive.strict.checks.cartesian.product = false;

WITH r AS (
    SELECT 1 AS d
    UNION ALL
    SELECT 2 AS d
)
SELECT *
FROM r
CROSS JOIN (SELECT 'f' AS u) e;
Hubbitus
  • 5,161
  • 3
  • 41
  • 47
0

I ended up working around with by just adding an extra field with a single value and joining the two tables together on that to produce the same effect.

It ended up looking something like:

with d as (
select column, 'AreYouKiddingMe' as k from table
), e as (
select column2, 'AreYouKiddingMe' as k from table2
)
select * from d inner join e on d.k = e.k
ADataGMan
  • 449
  • 1
  • 7
  • 23