AWS ATHENA SQL视图根据条件有条件查询表
我试图创建一个带有1700多个分区的雅典娜桌子，每个分区都有100个存储桶（不是S3桶，而是雅典娜的桶，就像在高基数柱上的哈希一样，以加快查询加快查询）。不幸的是，我了解到雅典娜不支持超过100个分区，如果您使用他们的解决方法来支持100多个分区，则无法使用任何存储桶。那么，我想，如果我将每个分区的单独桌子做出了怎么办？然后，我可以给每个桌子自己的100桶。这个想法是，我将所有这些表藏在SQL视图下。当用户指定分区时，视图将选择正确的表，仅查询该表。这使我发布了这个问题。
我如何在雅典娜中进行有条件查询表的SQL视图？起初，我认为这很简单，但是现在我意识到不是。我可以天真地结合所有桌子，然后在联盟结果上运行条件，但这将违反分区的目的，因为我最终会一直阅读所有数据。我描述的是什么？
我想这样做，但是没有联盟：
CREATE VIEW example AS (
SELECT col1, col2, 1 as partition FROM partition1
UNION ALL
SELECT col1, col2, 2 as partition FROM partition2)
用户在哪里指定类似的内容：
SELECT col1, col2
FROM example
WHERE partition = 1
I tried to create an Athena table with 1700+ partitions where each partition has 100 buckets (not S3 buckets but Athena buckets, which are like hashes on top of high cardinality columns to speed up queries). Unfortunately I learned that Athena doesn't support more than 100 partitions, and if you use their workaround to support more than 100 partitions, you can't use any buckets. So then I thought, what if I made each partition its own separate table? Then I could give each table its own 100 buckets. The idea is that I would hide all of these tables under a SQL view. When the user specifies a partition, the view would pick the correct table, and query only that table. That led me to post this question.
How can I make a SQL view in Athena that conditionally queries tables? At first I thought it would be simple, but now I'm realizing it's not. I could naively union all the tables, and then run the condition on the unioned result, but that would defeat the purpose of the partitions because I would end up reading all of the data all the time. Is what I'm describing possible?
I want to do this but without the union:
CREATE VIEW example AS (
SELECT col1, col2, 1 as partition FROM partition1
UNION ALL
SELECT col1, col2, 2 as partition FROM partition2)
Where the user would specify something like:
SELECT col1, col2
FROM example
WHERE partition = 1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论