AWS GLUE SQL与右表的单行连接

发布于 2025-01-21 07:30:01 字数 1364 浏览 2 评论 0原文

IM试图在AWS胶水

表1中加入两个数据集（别名AF）：

ID	数据	创建
1	字符串1	2020-02-10
2	字符串2	2020-02-11
3	字符串3	2020-02-12

表2（别名MP）：

ID	数据	数据2	创建	freation_key
1	字符串1	JSON String	2020-02-10	2
String 2	String 2	JSON String	2020-02-11	3
3	String 3	JSON String	2020-02-12	3

我想做的是从表1中获取所有行并从表2中选择与外键相匹配的第一行。

这是我目前经历了一些问题后目前所拥有的，我发现我需要用汇总功能包装查询，以便让Spark知道只有1个元素可以匹配此子查询。

select af.id,af.data
       (select first(mp.data)
        from mp
        where af.id= mp.foreign_key
       ) as alias1,
       
       (select first(mp.data2)
        from mp
        where af.id= mp.foreign_key
       ) as alias2
from af 
having alias 1 is not null and alias2 is not null

但这是给我以下错误：

ParseException: mismatched input 'first' expecting {')', ',', '-'}(line 3, pos 15)

任何帮助将不胜感激！

原文

Im trying to join two datasets in AWS glue

Table 1(alias af):

id	data	created
1	string 1	2020-02-10
2	string 2	2020-02-11
3	string 3	2020-02-12

Table 2 (alias mp):

id	data	data2	created	foreign_key
1	string 1	json string	2020-02-10	2
2	string 2	json string	2020-02-11	3
3	string 3	json string	2020-02-12	3

What i want to do is get all rows from table 1 and select the first row from table 2 that matches the foreign key.

This is what I have currently after going through a few questions i found that i need to wrap the query with an aggregate function to let spark know that only 1 element will match this subquery.

select af.id,af.data
       (select first(mp.data)
        from mp
        where af.id= mp.foreign_key
       ) as alias1,
       
       (select first(mp.data2)
        from mp
        where af.id= mp.foreign_key
       ) as alias2
from af 
having alias 1 is not null and alias2 is not null

But this is giving me the following error:

ParseException: mismatched input 'first' expecting {')', ',', '-'}(line 3, pos 15)

Any help will be appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

雨巷深深 2025-01-28 07:30:01

我找到了一种适合我用例的解决方案。上面的评论是正确的，SQL以前很时髦。

Select af.*, mp.*
from af  join
     (select mp.*, row_number() over (partition by mp.fid order by mp.created_at) as seqnum
      from mp
     ) mp
     on af.id= mp.fid and seqnum = 1;

Ive found a solution that works for my use case. Comment above was right the SQL was funky before.

Select af.*, mp.*
from af  join
     (select mp.*, row_number() over (partition by mp.fid order by mp.created_at) as seqnum
      from mp
     ) mp
     on af.id= mp.fid and seqnum = 1;

回复收藏 0 原文

~没有更多了~