Looking for a quick and easy way to query an Amazon Redshift Database Cluster? I was and the first place I turned was to my favorite tool for this kind of thing, Linqpad. I was a bit dismayed to find that none has developed, that I could find, a Linqpad database driver for Redshift. Small note, there are a few Postresql options and Redshift is supposed to be Postresql compatible however, none of them seemed to work for Redshift.
Giving credit to the author of this article describing the use of Linqpad for connections to MS Access, I made a few tweaks and boom, I have a working way to connect to and query Redshift. So in the pay it forward spirit, I thought I'd share.
// PREREQUISITES:
// (1) Copy and paste this entire block of code into a Linqpad query window,
// no connection needed, and change language to C# Statement(s).
// (2) To use the .NET ODBC assembly, you'll have to press F4 then click on the
// "Additional Namespace Imports" tab. Add "System.Data.Odbc".
// (3) Install the Amazon Redshift ODBC Driver 2.x (x64).
// (4) Update the query settings below.
// ***************** Update Settings Below *****************
string endpoint = " <endpoint> ";
string database = " <database_name> ";
string user = " <username> ";
string pass = " <password> ";
string port = "5439"; // Default Redshift port
string table = "";
string query = "SELECT * FROM " + table;
// ***************** End Update Settings *******************
string connectionString = $"Driver={{Amazon Redshift (x64)}}; Server={endpoint}; Database={database}; UID={user}; PWD={pass}; Port={port};";
using(OdbcConnection connection = new OdbcConnection(connectionString))
{
Console.WriteLine($"Connecting to [{endpoint}]...");
try
{
if (query.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
{
using (OdbcDataAdapter adapter = new OdbcDataAdapter(query, connection))
{
DataSet data = new DataSet();
adapter.Fill(data, table);
Console.WriteLine($"Found [{data.Tables[0].Rows.Count}] rows");
data.Dump();
}
}
else
{
connection.Open();
using (OdbcCommand command = new OdbcCommand(query, connection))
{
var impactedRows = command.ExecuteNonQuery();
Console.WriteLine($"[{impactedRows}] rows impacted");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
NOTICE: All thoughts/statements in this article are mine alone and do not represent those of Amazon or Amazon Web services. All referenced AWS services and service names are the property of AWS. Although I have made every effort to ensure that the information in this article was correct at writing, I do not assume and hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions.