AWS Athena Returning Zero Records from Tables Created from GLUE Crawler input csv from S3

user_6077057 0
Viewed 15259

Part One :

I tried glue crawler to run on dummy csv loaded in s3 it created a table but when I try view table in athena and query it it shows Zero Records returned.

But the demo data of ELB in Athena works fine.

Part Two (Scenario:)

Suppose I Have a excel file and data dictionary of how and what format data is stored in that file , I want that data to be dumped in AWS Redshift What would be best way to achieve this ?

amazon-redshift amazon-athena aws-glue amazon-web-services csv
8 Answers

I was indeed providing the S3 folder path instead of the filename and still couldn't get Athena to return any records ("Zero records returned", "Data scanned: 0KB").

Turns out the problem was that the input files (my rotated log files automatically uploaded to S3 from Elastic Beanstalk) start with underscore (_), e.g. _var_log_nginx_rotated_access.log1534237261.gz! Apparently that's not allowed.

user_5942110

The structure of the s3 bucket / folder is very important :

s3://<bucketname>/<data-folder>/
    /<type-1-[CSVs|Parquets etc]>/<files.[csv or parquet]>
    /<type-2-[CSVs|Parquets etc]>/<files.[csv or parquet]>
    ...
    /<type-N-[CSVs|Parquets etc]>/<files.[csv or parquet]>

and specify in the "include path" of the Glue Crawler:

s3://<bucketname e.g my-s3-bucket-ewhbfhvf>/<data-folder e.g data>
user_33564240

Solution: Select path of folder even if within folder you have many files. This will generate one table and data will be displayed.

user_56615940

So in many such cases using EXCLUDE PATTERN in Glue Crawler helps me.

This is sure that instead of directly pointing the crawler to the file, we should point it to the directory and even by doing so when we do not get any records, Exclude Pattern comes to rescue. You will have to devise some pattern by which only the file which u want gets crawled and rest are excluded. (suggesting to do this instead of creating different directories for each file and most of the times in production bucket, doing such changes is not feasible )

I was having data in S3 bucket ! There were multiple directories and inside each directory there were snappy parquet file and json file. The json file was causing the issue.

So i ran the crawler on the master directory that was containing many directories and in the EXCLUDE PATTERN i gave - * / *.json

And this time, it did no create any table for the json file and i was able to see the records of the table using Athena.

for reference - https://docs.aws.amazon.com/glue/latest/dg/define-crawler.html

user_99152310

Pointing glue crawler to the S3 folder and not the acutal file did the trick.

user_155863580
Related