Are you trying to set up an Amazon Redshift ODBC Driver connection? Have you looked all over the internet to achieve it? If yes, then this blog will answer all your queries. ODBC (Open Database Connectivity) is an interface by Microsoft. You can use it to connect your application to a database.
ODBC can be used with Redshift to manipulate your Redshift data from an application. Business Intelligence (BI), analytics, and data reporting can be done with an Amazon Redshift ODBC driver connection.
In this blog, you will go through ODBC technology, Amazon Redshift, and the steps to set up an Amazon Redshift ODBC driver connection in detail.
What is ODBC Technology?
ODBC is the most widely supported interface for connecting applications with data. All kinds of BI, Reporting, ETL, Database and Analytics tools offer the ability to read and write data via ODBC connectivity.
An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data.
What is Amazon Redshift?
Amazon Redshift is a fast and fully managed cloud data warehouse that allows a simple and cost-effective process to analyze data using standard SQL and the existing Business Intelligence (BI) tools.
What is an Amazon Redshift ODBC Driver?
It enables Business Intelligence (BI), analytics, and reporting on data in Amazon Redshift. The driver complies with the ODBC 3.80 data standard and adds important functionality such as Unicode, as well as 32-bit and 64-bit support for high-performance computing environments on all platforms.
It is a powerful tool that allows you to connect with live Amazon Redshift data directly from any applications that support ODBC connectivity. One can use the ODBC connection to connect Amazon Redshift with third-party client SQL tools and applications.
To achieve it, you will have to follow these 2 steps:
- Set up the connection on your client’s computer or Amazon EC2 instance.
- Configure the ODBC Driver Connection.
The remaining part of this article is dedicated to the configuration of the ODBC Driver.
Features of Amazon Redshift ODBC Driver
- Based on PostgreSQL 8.0.2 and designed specifically for connecting straight to Amazon Redshift.
- SQL-92 capabilities on Amazon Redshift NoSQL data are enabled.
- Automatic schema building, flexible querying, and other features of flexible NoSQL flattening
- For real-time data access, connect to live Amazon Redshift data.
- SQL queries have full support for data aggregation and complicated JOINS.
- Integration with top BI, reporting, and ETL tools, as well as custom applications, is seamless.
How to Install and Configure ODBC Driver
In this section of the blog, you will go through the configuration of ODBC step by step.
Prerequisites
Before starting configuring the ODBC Driver, you need to take care of the following requirements:
- Be sure about the OS that you are using whether it is Windows, Linux, or macOS.
- Determine whether the SQL client tool is 32 bits or 64 bits.
- If there is more than one SQL client tool in your system, make sure all the settings are for an appropriate SQL client tool.
Further system requirements and compatibility details can be found on the following link.
If you don’t have the ODBC driver downloaded yet, check this link to download it.
a) ODBC Installation and Configuration on Windows
Step 1: ODBC Installation on Windows
ODBC Installation on Windows can be done by following the below steps:
- Follow the instructions on the wizard.
- Select the options according to the system requirement. Check the buttons, e.g. for a 64-bit machine, check the Driver for win64, and so on.
- In the next step, if you have the activation key, i.e. the purchased version of the ODBC connector, you can provide that otherwise, you can go for a trial.
- Click Next.
- Click install, then click Finish.
After installation completes, now is the time to configure the Driver.
Step 2: ODBC Configuration on Windows
After the Driver installs, it can be configured through the Control Panel by going into Administrative Tools and finding out the ODBC data source.
Select the ODBC 64-bit or 32-bit data source according to the system compatibility.
After that choose one of the following:
- To create a DSN that only the user currently logged into Windows can use, click the User DSN tab.
- To create a DSN that all users who log in to Windows can use, click the System DSN tab.
Select ODBC driver and click on Finish.
As you click on Finish, the Amazon Redshift ODBC Driver will pop up. Fill out the emerged box and mention the Data Source Name in the box. Mention the description at your convenience. Mention the Server name, the endpoint of the server hosting the database that you want to access, User ID, and Password. By default, the Port is 5439.
Click OK and complete the configuration part. You can also switch to the Advanced Settings to specify a more detailed connection string.
b) ODBC Installation and Configuration on Linux using RPM File
ODBC Installation on Linux using RPM File
ODBC Installation on Linux can be done by following the below steps:
Step 1: The bitness of your driver must be matched with the bitness of the client application. You can use the following guideline to check the equivalency between both drivers.
- 32-bit Driver:
AmazonRedshiftODBC-32-bit-[Version]-[Release].i686.rpm
- 64-bit Driver:
AmazonRedshiftODBC-64-bit-[Version]-[Release].x86_64.rpm
Placeholders in filenames mentioned above, are described as follows:
- [Version] represents the version number of the driver.
- [Release] represents the release number for this version of the driver.
Step 2: You need to log in as a root user.
Step 3: Go to the folder containing the driver’s RPM package.
Step 4: Based on the Linux distribution you are using, run the commands given below from the command line.
- For Linux distribution, Red Hat Enterprise Linux or CentOS, run the following command. The placeholder for [RPMFileName] is the file name of the RPM package.
yum --nogpgcheck localinstall [RPMFileName]
- For SUSE Linux Enterprise Server, run the following command, where [RPMFileName] represents the file name of the RPM package.
zypper install [RPMFileName]
After installation is finished, the Amazon Redshift ODBC Driver files are installed in the /opt/amazon/redshiftodbc directory.
Configuration of ODBC Driver manager on Linux
In order to ensure the ODBC driver manager on your machine is set up to work with the Amazon Redshift ODBC Driver, follow the below steps:
Step 1: You need to set the library path environment variable to make certain that your machine uses the right ODBC driver manager.
Step 2: In case, the configuration files are not found at the default locations as expected by the ODBC driver manager, then environment variables need to be set in order to ensure that the driver manager detects those files and uses them.
A similar type of ODBC configuration can be found for macOS on the following Amazon Redshift ODBC official documentation.
Data Types Supported
| Amazon Redshift Data Types | ODBC Data Types |
| INTEGER | SQL_INTEGER |
| REAL | SQL_REAL |
| DATE | SQL_TYPE_DATE |
| TEXT | SQL_WVARCHAR |
| SMALLINT | SQL_SMALLINT |
Where can Amazon Redshift ODBC Driver be used?
Amazon Redshift ODBC Driver can be used for multiple purposes like connecting to ETL, database, BI Reporting tool, User Applications, and Microsoft applications. Let’s understand the use cases one by one.
a) Amazon ODBC Driver for connecting ETL and Reporting Tools
The most extensively used interface for connecting programs to data is ODBC. ODBC connectivity can be used to read and write data in a variety of BI, Reporting, ETL, Database, and Analytics technologies.
- Cognos BI
- Crystal Reports
- FileMaker Pro
- FoxPro
- Microsoft Access
- Microsoft Excel
- MicroStrategy
- Oracle DB
- Power Map
- Power Pivot
- QlikView
- SAP Business Objects
- Sharepoint Services
- Tableau (certified)
b) ODBC Driver for User Applications
ODBC driver is compatible with all crucial development technologies, including Python, PHP, FoxPro, and more. Developers are using the Amazon Redshift ODBC in their tech stack to quickly develop desktop, web, and mobile applications that interconnect with real-time Amazon Redshift data.
c) OBBC Driver for Microsoft Office tools
Applications like Microsoft Excel, Word can access the data in real-time through the ODBC driver. With the Amazon Redshift ODBC driver, Microsoft Word allows users to directly access Amazon Redshift live data through powerful features such as mail merge, eliminating the hassle of complex import/export procedures.
Conclusion
- In this blog, you have learned about the Amazon Redshift ODBC drivers in detail.
- Amazon Redshift ODBC connector is useful as it connects the real-time Redshift data with any application that supports ODBC connectivity.
- But, if you are looking for a hassle-free and fully automated solution, then try Hevo.
Want to take Hevo for a spin? Sign Up or a 14-day free trial and experience the feature-rich Hevo suite firsthand.
Share your experience of using the Amazon Redshift ODBC driver in the comment section below.
Arsalan is a research analyst at Hevo and a data science enthusiast with over two years of experience in the field. He completed his B.tech in computer science with a specialization in Artificial Intelligence and finds joy in sharing the knowledge acquired with data practitioners. His interest in data analysis and architecture drives him to write nearly a hundred articles on various topics related to the data industry.
