TABLE_NOT_FOUND on Athena query with SQL Server using Lambda and JDBC

Question

I'm using federated queries in AWS Athena, querying data on SQL server.

When Connecting to Athena, I am able to visualise, the list of tables and their schema. (show tables & describe [Table Name] are working) But getting error TABLE_NOT_FOUND when running select queries.

Why ?

SQL server is on premise.

Accepted Answer

This was a user rights issue : 
1°) SQL Server username needs to have usermapping with db_datareader & public on SQL Server database
2°) SQL Server username used by Lambda function should be able to read in table : SYS.DM_DB_PARTITION_STATS :

```
USE [database_name];
GRANT VIEW DATABASE STATE TO [user_name];
```

3°) maque sure that schema to be read (ex : "dbo") has issqluser column value at "1" in table : sys.sysusers

Answer

it may be related with the JDBC Driver you are using or the Connection String 
try to write queries like database.schema.tablename for SQL Server 
like database.dbo.tbl_name

TABLE_NOT_FOUND on Athena query with SQL Server using Lambda and JDBC

Relevant content