How to load geospatial data in Amazon Redshift
Import the data to Redshift

Now that we have the CSV file in S3, we are going to load the data into Redshift using the COPY command. We have different options for executing SQL commands in Redshift; some of them are the following:

We can use the psycopg driver for PostgreSQL. Although Redshift is not completely PostgreSQL-compatible, we can use this driver to connect to Redshift and execute SQL statements. This option only allows us to use traditional database authentication.

Another option is using the Redshift ODBC driver with the pyodbc module. This is a similar solution to psycopg, with Redshift-specific features that also provides only traditional database authentication.

A better option would be to use the Python Redshift connector. It is Redshift specific, developed by AWS, and supports Redshift specific features like IAM authentication.

The option we have finally chosen is the Redshift Data API. It makes asynchronous HTTP calls using the boto3 package. The code is a bit more complex (you need to execute the statement, wait for it to end and then retrieve the results) but it provides IAM and Secrets Manager authentication and avoids the need for managing a connection.

To use the Redshift Data API, we need our credentials to have the AmazonRedshiftDataFullAccess policy attached. Use the AWS IAM console to attach the policy as explained above.

Before we can load the data we need to create the table where it will be imported. We need to prepare the SQL statement with the column name and data types from the input file. Fiona provides a schema property that we can use to map the Python data types as read from Fiona to Redshift data types. Please take a look at the get_create_table_statement and get_field_mappings functions to understand the process.

To execute SQL statements on Redshift we need to provide the database credentials to access the database. The script uses credentials stored in AWS Secrets Manager. This is one of the recommended options for connecting to Redshift and it provides really useful functionalities for rotating, managing and retrieving database credentials. We also need to provide the ARN of this secret.

Once we have created the table, we can import the data using the COPY command. In addition to the database credentials, we need to attach a role to our Redshift cluster that allows read access to S3. Once we have attached this role, we will use the Amazon Resource Name (ARN) of the role in the COPY command.

You can see below the syntax for the execute_statement function. Besides the cluster identifier and the database, we specify the ARN of the secret with the database credentials and the SQL statement. In the SQL statement we need to indicate the path to the CSV file in S3 and the IAM role attached to the Redshift cluster with read access to S3.

Please take a look at the full source code to see how we can check if the SQL statement execution was successful and additional options that we need to use like the TIMEFORMAT parameter in the COPY command to interpret date/time fields correctly.
['Borja Muñoz']
['Redshift', 'Geospatial', 'Geojson', 'AWS']