Download the setup and install it on your machine.įigure 3 – Installing AWS Athena ODBC Connector In my case, I am going to proceed with the ODBC 1.1.12 for Windows 64-bit version. This can be easily downloaded from the official page of Athena.Īppropriate version for your computer.
In order to set up the linked server, we would first need to download and install the connector driver for SQL Server to connect to the AWS Athena service. For this purpose, we need to first create a linked server in SQL Server and then we will use the OPENQUERY service to query data stored in the S3 buckets. Now that we have our Athena service up and running, we can set up our SQL Server instance and configure it to query Setting up the ODBC Driver for Amazon Athena
If you are using an AWS Account that is handled by an administrator, you would need to get the credentials from the Administrator. This can be done by navigating to the IAM service on the console and then generating the security credentials from the Users tab.
We will look into how to connect SQL Server to this Athena service and run similar queries from the SQL Server instance.Īlso, in order to query the Athena service from SQL Server, we need to generate the Access Key and the Secret Key from the AWS console. Once you have created an instance, you are free to query the data using standard SQL queries as follows.įigure 2 – Creating an AWS Athena instance and querying itĪs of now, the part of setting up the AWS Athena service and the CSV files in S3 has been over. You can follow this article, Getting started with Amazon Athena and S3, where I haveĮxplained in a step-by-step manner how to create an instance in Athena and query data from an S3 bucket. Search for the Athena service and then create an environment that can read the CSV files from the S3 bucket. Once the CSV file has been uploaded, the next step is to move to the Athena service to set up the schema and query service. Under this directory, you can upload your CSV file as follows. Create an S3 bucket of your choice and then create a directory under it. Let us now head over to the AWS console and upload a small CSV file that can be queried by Athena. The only condition in such a case would be that all the CSV files maintain a similar column structure. This is also possible in case there is more than one CSV file.
In such a scenario, AWS Athena provides a service that can be used to first create a schema of the CSV file, and then use general ANSI SQL scripts to query the data stored in those CSV files. This CSV file cannot be read by any SQL engine without being imported into the database server directly. Imagine you have a CSV file that contains data in tabular format.
Also, as a pre-requisite for this tutorial, we would need to have an AWS account valid and some knowledge about Amazon Athena.Īmazon Athena is an interactive query service provided by Amazon that can be used to connect to S3 and run ANSI SQL queries.
For the purpose of this demonstration, we are going to use SQL Server that has been installed on-premises. SQL Server can be installed on-premises or on popular cloud services like Azure or AWS. SQL Server is one of the most popular relational database management systems developed by Microsoft. In this article, we are going to use SQL Server to query data that resides in Amazon S3 buckets with the help of Amazon Athena.