This is very bad for us since we want to offloadĪll work to Athena and we do not want to retrieve all table data. Regular SQL Server ODBCĭestinations query behavior is to send 'select *' to a linked serverĪnd do filtering inside SQL Server. There is one very important thing that you need to know. Querying Data from AWS Athena Using SQL Server Management Studio and We can query the data files from any script or stored procedure.

Let’s create database in Athena query editor.ĮXEC _addlinkedserver =N'DWH_ATHENA', _addlinkedsrvlogin and with the AWS access key and secret key and now Linked server to Athena inside SQL Server Create External table in Athena service, pointing to the folder which holds.Using a columnar format called PARQUET is faster. I am using a CSV file format as an example in this tip, although Query the data using AWS Athena, data which is stored in a csv file, located on In several cases, usingĮTL because it projects your schema on the data files In the Athena query editor, either launching it from the AWS web services UI, AWSĪPIs or accessing it as an ODBC data source. Uses Apache Hive to create, alter and drop tables.

There are also differences such as you can get the same rich compliance standards of Amazon Redshift.The Athena service is built on the top of Presto, distributed SQL engine and also Athena and Redshift Spectrum query optimizers are completely different. This pattern serves to separate compute and storage, enabling independent scaling of both to match the use case without having to pay disproportionately for value. A common pattern for Redshift Spectrum is to run queries that span both the frequently accessed “hot” data stored locally in Amazon Redshift and the “warm/cold” data stored cost-effectively in Amazon S3. Whereas Redshift Spectrum is more like a secondary car and Redshift is your primary car. Athena is more like rent-a-car for adhoc/on-demand data explorations as and when needed without needing to spin up a cluster etc. They are not meant to replace each other but rather meant for different workloads. As far as Spectrum goes, you will find that Spectrum follows pretty much the same syntax as Redshift except things like you cannot do DML operations on Spectrum tables due to the external table.įor the second part of your question, I would make sure that customer is aware when to use Athena versus Spectrum.

I would refer to Presto documentation here under 'SQL Language' and 'SQL Statement Syntax'. Based on the demands of your queries and Redshift cluster configuration, Redshift Spectrum scales automatically in an intelligent fashion.ģ/ Regarding query syntax difference between Athena and Redshift Spectrum, yes.Īthena's query engine is Apache Presto and hence, it follows query syntax of Apache Presto. The number of Redshift Spectrum compute nodes that a query uses depends on the Redshift node type and the overall workload. Amazon Redshift Spectrum owns managed compute layer independent of your Redshift cluster. Such as parquet file format, Snappy compression, proper partitioning on S3 to help with query access patterns/filters, type of queries such as ORDER BY, DISTINCT which cannot be pushed down to Spectrum compute layer etc. It is hard to quantify such metrics as every customer workload is different.ġ/ Depends on a variety of factors as noted in the best practices blog. I would go through the Redshift Spectrum best practices blog here and plan to run some tests.

Athena vs redshift