Back to Top

Connect to Amazon Redshift from ArcGIS

In this topic

  1. Install and configure drivers
  2. Create database connection files
  3. Register with the hosting server
  4. Next steps

To connect to an Amazon Redshift cloud data warehouse, install and configure client files to implement communication between ArcGIS and Redshift and create database connection files from ArcGIS Pro. To publish web layers, the database connection files must be registered with the ArcGIS Enterprise portal's hosting server site. For best performance, deploy ArcGIS clients in the Amazon cloud in the same region as the data warehouse.

Typically, IT professionals install the ArcGIS software and the drivers required to connect to the data warehouse. They also configure network, firewall, and other communication channels necessary to allow communication between ArcGIS and the cloud data warehouse.

Next, the administrator of the cloud data warehouse creates accounts and grants the accounts privileges to access specific tables in the cloud data warehouse. The administrator can create a connection from ArcGIS Pro to the cloud data warehouse and share the connection file with publishers, or the administrator can provide publishers with the credentials necessary to allow them to create their own connection files.

To publish ArcGIS Server web services that reference data in the cloud data warehouse, publishers must register the database connection file with the ArcGIS GIS Server site that acts as the ArcGIS Enterprise portal's hosting server.

Install and configure drivers

To connect to Redshift from ArcGIS Pro, you must install the latest Amazon Redshift 64-bit ODBC driver on each ArcGIS Pro machine. To publish a web layer that contains data from Redshift, you must install the Amazon Redshift 64-bit ODBC driver on each machine in the ArcGIS Enterprise portal's hosting server site. Use the latest Amazon Redshift 64-bit ODBC driver supported for the operating system of the machines on which the hosting server site is running.

Download the Amazon Redshift ODBC driver from the Amazon Web Services (AWS) website, and install it on all ArcGIS client machines that will connect to Redshift. See the Amazon Redshift management guide for instructions to install and configure the ODBC driver.

Alter the init_user_param.sh script (Linux only)

If the portal's hosting server runs on Linux machines, you must install the drivers on each machine in the GIS Server site and configure the init_user_param.sh script on each machine to access the drivers.

Alter the init_user_param.sh script installed with ArcGIS Server to reference the drivers. You can access this script by browsing to the <ArcGIS Server installation directory>/arcgis/server/usr directory.

  1. Ensure that the ArcGIS Server installation owner on each machine has at least read and execute permissions on the data warehouse client libraries.
  2. Open the init_user_param.sh script in a text editor.
  3. Remove the comment marks (#) from the line beginning with export, replace <Location_to_ODBC_driver_manager_libraries> with the location of the ODBC driver on the ArcGIS Server machine, and replace <Location_to_amazon.redshiftodbc.ini> with the location of the amazon.redshiftodbc.ini file on the machine.

    # -----------------------------------------------------------------------
# Modify this section to configure Amazon Redshift ODBC connector
# 
# -----------------------------------------------------------------------
export LIB_ODBC_DRIVER_MANAGER=<Location_to_ODBC_driver_manager_libraries>/libodbc.so.2
export AMAZONREDSHIFTODBCINI=<Location_to_amazon.redshiftodbc.ini>/amazon.redshiftodbc.ini

  4. Save and close the script.
  5. For the changes you made in the init_user_param.sh file to take effect, you must restart ArcGIS Server.

    To do this, run the stopserver.sh script, then run the startserver.sh script on each machine in the ArcGIS Server site.

    ./stopserver.sh

    ./startserver.sh

Create database connection files

In ArcGIS Pro, use the New Database Connection dialog box to create a connection to Redshift.

  1. Open the Catalog pane in ArcGIS Pro.
  2. Right-click Databases or a folder under Folders and click New Database Connection.
  3. Choose Redshift from the Database Platform drop-down list.
  4. Type the fully qualified name of the server hosting the Redshift database in the Server text box.

    Provide the hosting server in the Server field.

    The name is in the format <cluster>.<server>.<region>.redshift.amazon.com. For example, if the database cluster is named mycluster, the server is named myserver, and it is deployed in Amazon region us-east-2, the server name is mycluster.myserver.us-east-2.redshift.amazon.com.

  5. Choose an authentication type.

    The type you use determines what authentication information you must provide.

    • Standard—Type the username and password for the credentials to use for authentication. If you will use the database connection file to publish web layers or use in geoprocessing models, check the Save User/Password check box.

      Standard authentication for a connection

    • AWS IAM Credentials—Type the username for a database user in the DB User field, and provide the access key and secret access key you configured for the IAM role in the Access Key ID and Secret Access Key fields, respectively. Consult the AWS documentation for instructions to configure IAM roles.

      IAM credential authentication for a connection

  6. Choose the database to connect to from the Database drop-down list.
  7. Type a name for the connection file in the Connection File Name field.

    As you provide connection information, a default name is created. You can use the default, or type a more descriptive file name.

    This is the name that will appear in the Catalog pane and view, and the name of the .sde file stored on disk.

  8. Optionally, expand the Additional Properties section, choose the additional parameter that you require to connect that is in addition to those required in the previous steps, and type the value for that option.
    • Port—If Redshift communicates over a nondefault port, choose this property and type the correct port in the Value field.

      For example, if the Redshift instance communicates through port 5440, choose Port from the Property drop-down menu and type 5440 in the Value field.

    • LogLevel—Enables logging for the ODBC driver, and sets the logging level. Specify the appropriate Redshift ODBC LogLevel code in the Value field.
    • LogPath—If you enable logging with the LogLevel property, define the folder location to which the log files will be written.

    If you require a property that is not available from the drop-down menu, you can type the property in the Property field and provide the appropriate value for it.

    Read the Amazon Redshift documentation for information about additional and optional ODBC connection parameters.

  9. Click OK to create the connection file.

A database connection appears under Databases in the Catalog pane, and a connection file (.sde) is created in the ArcGIS Pro project directory.

Register with the hosting server

To allow ArcGIS Server sites to access the data, use the database connection file you created to add a registered data store in ArcGIS Pro or add a data store item in the portal.

Next steps

After you configure the ArcGIS clients to connect and create a database connection file, you and other organization members can do the following:

  • Analysts and publishers create a query layer in a map in ArcGIS Pro.

Feedback on this topic?

In this topic
  1. Install and configure drivers
  2. Create database connection files
  3. Register with the hosting server
  4. Next steps