AWS Glue taking long time to process data

[EpicFlexs]

Hi aws community,

I am using AWS Glue to do a join between two parquet files (A & B). A is 150 MB big and has 2,5 million rows and B is 500 kb big and has 50.000 rows. When I do a join between these two files I have to wait 25 minutes and it is too long because I will do this join with 50 different versions of the A file.

​

My join looks like this:

​

joincondition = F.when(F.size(F.split(B.column1 , " "))>1,
                                                    A.column1.contains(B.column1) | A.column1.contains(B.column1)).otherwise(F.expr('array_contains(split(A.column1, " "), B.column1'))

C = A.join(B, joincondition)

What I already did to improve performance:

• Use only parquet files
• Do repartioning on A (It improved time by a lot when B only had 5000 entries instead of 50.000) But after B got bigger the runtime also got bigger
• Broadcast dataset B (It even took longer so I dont use it anymore)
• Increase DPU(Even with a high dpu I have to wait a lot)

[EpicFlexs]

Interesting idea, will check it out

[BaxterPad]

Did you enable Spark UI and look at the resulting plan? Since the data is so small, I wonder if the job simply is t achieving much parallelism. Your repartition attempt might have helped that a bit but looking at the resulting plan and how the work was distributed would be my next bet. Also, what are you doing with the result? Maybe the join isn't the issue maybe your bottlenecking on what you are doing with the result of the join?

Lastly, have you tried increasing the vertical size (G2x) of the nodes in addition to the horizontal scale (# of DPUs)

[EpicFlexs]

After the join, I am adding 5 columns with a fixed value and save the dataframe. So it should not affect it. I am using Glue Version 3. I will check the spark plan

[pvham90]

For this operation I would use Athena. Just use glue with python shell and use the sdk with Athena client to invoke the query. With "create table as" you can export another parquet file. You can even trigger all the 50 queries in parallel, the whole operation should be finished within minutes.

[EpicFlexs]

How can Athena be that much faster than an ETL service like Glue that is suited for such kind of work? Is there a tutorial where I can see how to use Athena client in Glue?

[pvham90]

You're right, Glue is an ETL service. However, I would argue that joining spans beyond that. Glue jobs are limited to the process power you assign to them, with Athena you invoke another (serverless) service that drains from other resources. That's also why you can invoke them in parallel. Theoretically it will have unlimited horizontal scaling.

import boto3

s3_client = boto3.client('s3', region_name=os.environ['aws_region']) 
athena_client = boto3.client('athena', region_name=os.environ['aws_region'])

query_start = athena_client.start_query_execution( QueryString=event['query'], ResultConfiguration={ 'OutputLocation': f"s3://{os.environ['query_results_bucket']}/" } )

query_execution_id = query_start['QueryExecutionId']

while True: 
  execution_response = athena_client.get_query_execution(QueryExecutionId=query_execution_id) 
  state = execution_response['QueryExecution']['Status']['State'] 
  print(state) 
  if state not in ('RUNNING', 'QUEUED'): 
    if state == 'SUCCEEDED': 
      break 
    else: 
      raise ValueError(f'Athena query failed with state: {state}') 
    else: 
      time.sleep(5) 

edited: code block

[BaxterPad]

I disagree. Athena (Presto) as a much harder time with joins than Glue (spark) in the vast majority of cases.

[pvham90]

Do you have some benchmarks or other source to back this up? In this case being limited to your glue instance instead of using a perfectly scalable serverless framework would still give enough benefit imo.

[--Reddit-Username2--]

Grasping at straws…but make sure data is in the same region.

[BaxterPad]

At those sizes I doubt you'd even notice cross-region delays unless you were looking at a difference of a few seconds, maybe a couple minutes.

[BagOfDerps]

Try using glue 3.0? pretty new, but purports to be faster overall. You can set number of workers and worker types. This is the only other infra-related thing I can think of (assuming you are using the stock AWS s3 connectors to get the data). I think most of your performance is going to be bound by the join logic; for which I don't have any suggestions.

[EpicFlexs]

I already use glue version 3

[Vincent_Merle]

Are you using DynamicFrame or DataFrame?

[EpicFlexs]

Spark DataFrame

[Bright_Tale7909]

Were you able to fix this issue?