Glue crawled tables showing in glue's table (with the right schema) but don't show in athena, explicitly query results in hive format error

Question

I crawled a postgres database using JDBC crawler and it successfully created tables and is showing them in glue > database > "db_name" > Tables. It have fetched in columns all right.

Not athena show 0/zero tables for this database and if I write a query using 'db_name'.'table_name', it gives following error.

HIVE_UNSUPPORTED_FORMAT: Unable to create input format
This query ran against the "facset-loader" database, unless qualified by the query. Please post the error message on our forum  or contact customer support  with Query Id: fc373521-3501-442d-b5a2-eb583007bcc1.

But how come the created tables have format errors when they show perfectly in the glue.

Answer

I understand that you have a table created by crawler but when you tried to query the table from Athena, you were getting the HIVE_UNSUPPORTED_FORMAT: Unable to create input format. The steps that you have mentioned would be applicable only if the table that you want to query is present in s3 but your data source is postgres database. This must be the reason why you were unable to view the table in Athena. As you have mentioned that you are trying to query a data source which is a postgres database, you will have to follow a different type of mechanism for the sake of running queries against your postgres database in Athena. This mechanism known as the Athena Federated Querying. Please have a look at this documentation to know more about Federated queries in Athena.

I have found a third party blog which has detailed steps that you need to follow in order to query postgre database from Athena. In the blog, it is highlighted that in order to view the table in Athena console, you will have to add a new environment variable to the Lambda function.

Please try following this and see whether it works for you or not.

I hope the information provided by me would be of use to you.

I have found a third party [blog](https://www.dataengineeringacademy.com/2020/12/how-to-query-postgresql-with-athena/) which has detailed steps that you need to follow in order to query postgre database from Athena. In the blog, it is highlighted that in order to view the table in Athena console, you will have to add a new environment variable to the Lambda function.

Please try following this and see whether it works for you or not.

I hope the information provided by me would be of use to you.

Glue crawled tables showing in glue's table (with the right schema) but don't show in athena, explicitly query results in hive format error

관련 콘텐츠