AWS Glue Joining Tables Produces No Data

Based on the information provided, there are a few potential reasons why your AWS Glue job might be producing no results when joining tables, even though the same join works in Athena:

1. Data format issues: Ensure that the data format in your S3 buckets is consistent and properly recognized by Glue. Sometimes, inconsistencies in delimiters, quote characters, or file encoding can cause problems when Glue tries to process the data.

2. Permissions: Verify that the IAM role associated with your Glue job has the necessary permissions to access and read data from both S3 buckets. Insufficient permissions could prevent Glue from accessing the data even though Athena can read it.

3. Join conditions: Double-check that the join conditions in your Glue job exactly match those used in your Athena query. Even small differences in column names or data types could lead to an empty result set.

4. Data types: Ensure that the data types of the columns used in the join are compatible and consistent between the two tables in the Glue Data Catalog.

5. Null values: If the columns used for joining contain null values, this could potentially cause issues in the Glue job. Verify that the key columns don't contain null values.

6. Glue job configuration: Review your Glue job settings, including any custom scripts or transformations, to ensure they're not inadvertently filtering out all results.

7. Data Catalog synchronization: Make sure your Glue Data Catalog is up-to-date with the latest schema and partitions of your S3 data. You might need to run a Glue Crawler to update the metadata if changes have been made to the underlying data.

To troubleshoot this issue:

1. Review your Glue job logs for any error messages or warnings that might provide additional insights.
2. Try running the Glue job with a small subset of data to see if the issue persists.
3. Verify the schema of both tables in the Glue Data Catalog to ensure they match your expectations.
4. If possible, try to replicate the exact query you're using in Athena within your Glue job to see if that produces results.

If the problem persists after checking these potential causes, you may need to examine your Glue job's code in more detail or consider reaching out to AWS support for further assistance.
Sources
Only column headers appearing in Athena | AWS re:Post
Troubleshoot rules and rulesets | AWS re:Post

I've resolved my own issue by switching to Transform - SQL Query and use back-tick around the field or column name. Because of the column name is timestamp and I am guessing that it is having trouble dealing with such a name.

select a.`email`, s.`serialNumber`, a.`timestamp`, a.`x`, a.`y`, a.`z` 
from accelerometer a 
join step s on s.`sensorReadingTime` = a.`timestamp`