Is there any limitation on the number of elements that can be inserted on the IN operator?
I am asking this because I have a hive partitioned table (connected to a bucket with JSON) that I need to query hourly to extract some information. In order to not re-process already processed files, I use one of the partition fields to as identifier on which IDs I already processed, so I can query with a NOT IN only the new ones.
I'll show an example. This is an example of the content of the bucket:
date=2021-05-15/id=ad9isjiodpa/file.jsonl
date=2021-05-15/id=sda0u9dsapo/file.jsonl
date=2021-05-15/id=adsi9ojdsds/file.jsonl
so I can make a query like this, to exclude those I already processed:
SELECT * FROM hive_table where id NOT IN ('ad9isjiodpa', 'sda0u9dsapo')
Usually this query process around 30GB per run, And everything works great, everyone is happy. The list usually don't have more than 2k elements.
Usually ...
last time the number of elements exceedeed 4k elements and this resulted in 2.6 TB of data processed. That was extremely unlikely and made me think that it actually processed ALL the files in the bucket (inside the timerange).
Is there some scenario, or documentation I didn't pay enough attention to? Do you know why it did process so much data? What did I do wrong?
The current fix I did is to split the list of elements in smaller chunks and do something like
SELECT * FROM hive_table where id NOT IN (<chunked_elemens1>) AND id NOT IN (<chunked_elemens2>) ...
Will this work?
Thank you very much in advance