TLDR - using EXECUTE AT with Athena via linked server. I used https://www.mssqltips.com/sqlservertip/6191/query-aws-athena-data-from-sql-server to get it set up and it works! But there are some issues.
1) Partition your tables in Athena. Better performance if you have a decent amount of data
2) This means you can't just do "select from <linkedserver>...tablename", as that doesn't tell Athena to do partition elimination
3) Once you're using OPENQUERY, if you run into issues because it doesn't know what to expect, you can use CAST or CONVERT to force metadata (aka put "cast(fielda as varchar(500))" if it thinks the field is only 255 characters.
4) That doesn't help with over varchar(8000). You need to move to EXECUTE AT instead, but that seems better then OPENQUERY as it appears to just pass the data back - I got rid of my CASTs in the athena query. Plus, dynamic SQL is easier! You will have to enable RPC OUT for the Linked Server.
5) I couldn't do INSERT INTO EXECUTE AT until I disabled Distributed Transactions in that Linked Server.
Update 2021/06/04 - and there's something screwy with EXECUTE AT. It runs without the CAST statements and doesn't complain , but doesn't appear to save everything - I have a handful of rows (out of 100k, mind you) that don't match up - one of the fields is lopped off. It's the most annoying thing, since my other option is SQL Workbench which WORKS but isn't easily automated. After futzing with it for a bit, I wound up moving over to a pure Powershell solution - I'll post that in a few.
No comments:
Post a Comment