Using Linqpad to Query Amazon Redshift Database Clusters

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 http://forum.linqpad.net/discussion/384/how-to-connect-to-and-query-a-ms-access-database-mdb-and-accdb  describing the use of Linqpad for connections to MS Access, I made a few 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",
//     no quotes, on a single line and click OK.
// (3) Install the x86 Amazon Redshift ODBC Driver (http://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-windows.html). The 
//     x64 driver does not work
// (4) Update the query settings.

// ************************************************ Update Settings Below ************************************************
string endpoint = "";
string database = "";
string user = "";
string pass = "";
string port = ""; //Default is 5493

string table = "";
string query = "SELECT * FROM " + table; //Optionally Update Query
// ************************************************ End Update Settings Section ************************************************

// ************************************************ Do Not Modify Below ************************************************
string connectionString = "Driver={Amazon Redshift (x86)}; Server="+endpoint+"; Database="+database+"; UID="+user+"; PWD="+pass+"; Port="+port;

using(OdbcConnection connection = new OdbcConnection(connectionString)) 
{
 Console.WriteLine("Connecting to ["+connectionString+"]");
 try
 {
  Console.WriteLine("Executing query ["+query+"]");
  
  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());
 }
}
// ************************************************ End Do Not Modify Section ************************************************

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, whether such errors or omissions result from negligence, accident, or any other cause.
Labels: , ,