Amazon Redshift ODBC driver is a powerful tool that enables access to live Amazon Redshift data directly from any application from anywhere. The ODBC driver interface reads, writes, and updates Amazon Redshift data. As Amazon Redshift announces an open source ODBC driver you can gain enhanced visibility and can contribute to driver development.
Amazon Redshift with binary protocol support helps you to send data from the Amazon Redshift cluster to the ODBC driver in binary format. And this provides a decrease in data sent over the wire which will result in faster end-to-end query performance for queries returning large result sets. Binary protocol support is enabled by default for all users.
In this article, we will dive deep into ODBC Driver, Amazon Redshift, and Amazon Redshift ODBC driver, and its installation.
ODBC Driver
Open Database Connectivity (ODBC) is an interface by Microsoft for applications to access data from the Database Management System (DBMS). ODBC driver makes end-users link applications to the DBMS of their choice. Also, ODBC provides interoperability between a single application and different DBMS.
Amazon Redshift
Amazon Redshift is a fully managed data warehouse that is a part of AWS consulting Services to handle large-scale data sets and database migration. AWS Redshift also handles the analytic workload of data sets stored by DBMS. To launch a data warehouse first you have to create Amazon Redshift Cluster, then upload the data set and perform data query analysis. Key features of Amazon Redshift are:
- Supports VPC so users can launch clusters within VPC and access clusters using a virtual networking environment
- Data sets to the Redshift table can be encrypted and configured.
- The connection between the client and Redshift is encrypted using SSL
- Nodes are easily scalable as per requirements and storage capacity is also scalable without affecting the performance.
- Amazon Redshift is cost-effective.
Amazon Redshift ODBC driver
Amazon Redshift ODBC driver is used by applications to directly access Amazon Redshift data. This driver provides you with a live connection to read, write, and update Amazon Redshift data.
Installation of Amazon Redshift ODBC driver on Linux
Minimum requirements for a computer to install the driver are:
- Root access on a computer and one of the following ODBC driver manager installed
– iODBC latest version
– unixODBC latest version - Disc space of 150 MB
- Any one of the distributions
– Red Hat® Enterprise Linux® (RHEL) 7 or 8
– CentOS 7 or 8
– SUSE Linux Enterprise Server (SLES) 12 or 15
– Debian 8 or 9
– Ubuntu 18.04 or 20.04
– Oracle Linux 7.5
Following are the steps to download and install Amazon Redshift ODBC driver version 2.x
- Download the driver: 64-bit RPM driver version 2.0.0.1. This will execute both 32-bit and 64-bit applications. But make sure that the 32-bit driver is used for 32-bit applications and the 32-bit driver for 32-bit applications. And also driver bitness should match that of client application bitness.
- Go to the downloads and run the downloaded package. Here you may have commands to run and you have to run the command depending on your Linux distribution.For RHEL and CentOS operating systems you have to run the command:
yum –nogpgcheck localinstall RPMFileName
For SUSE Linux Enterprise Server, you have to run the command:
zypper install RPMFileName
Replace RPMFileName with RPM package file name. Installed Amazon Redshift ODBC driver files are located in the /opt/amazon/redshiftodbc directory.
Configuring ODBC Driver Manager
After the installation, the next step is configuring the ODBC driver manager. ODBC driver manager on your machine is configured to work with the driver as follows:
- Specifying ODBC Driver Manager on Linux: Make sure that your machine is using the correct ODBC driver manager to load the driver. For this, you have to set the LD_LIBRARY_PATH environment variable to include the paths to the ODBC driver manager libraries.
- Specifying the locations of the Driver Configuration File :For iODBC follow the steps1. Set ODBCINI to the full path and file name of the odbc.ini file.2. Set ODBCINSTINI to the full path and file name of the odbcinst.ini file.3. Set AMAZONREDSHIFTODBCINI to the full path and file name of the amazon.redshiftodbc.ini file.For unixODBC follow the steps1. Set ODBCINI to the full path and file name of the odbc.ini file.2. Set ODBCSYSINI to the full path of the directory containing the odbcinst.ini file.3. Set AMAZONREDSHIFTODBCINI to the full path and file name of the amazon.redshiftodbc.ini file.
Conclusion
Amazon Redshift ODBC driver is now open source and this supports the binary protocol and enhances the performance. For better results, you have to upgrade your driver to the latest version.