3

I've recently joined a company with a mixed set of databases that include a Redshift cluster and some SQL databases. I'd like to use a single IDE to access both for analytical reporting, so I don't have to switch between tools. I'm currently using workbench, which works, but it's not clicking with me.

I do like Azure Data Studio, but it's SQL Server and Postgres only. Given the similarities between Redshift and Postgres, I thought I'd see if I could connect using the Postgres driver.

I've installed the Postgres extension and can "connect" to the database. However when I try to explore the database using the tree view, I get the error message 'Cannot Expand Node'. When I run a simple query that works in workbench, e.g.

    Select * from [server].[database].[table]

I get the following Error message:

Started executing query at Line 1

cursors can only be used within the transaction that created them.

Total execution time: 00:00:00.019

I know I'm trying to do something that shouldn't be done. And if I can't, I can't. But has anyone here managed to get a redshift connection going in Azure Data Studio?

FWIW, I've come across a GitHub Repository that may be a Redshift driver for data studio - but this looks like a clone of the Postgres driver, with no activity since march (not even renaming the 'Postgres' titles to Redshift)... and therefore I'm dubious.

Felix Schwarz
  • 2,938
  • 4
  • 28
  • 41
EILNP
  • 33
  • 1
  • 3
  • 1
    Redshift is based on a old version of Postgresql and they have diverged significantly since then. I doubt you will get a good experience trying to use a tool and driver designed specifically for Postgresql. I'd suggest finding a tool that supports standard JDBC drivers, which will work with just about any database platform e.g. DBeaver https://dbeaver.io/ - then download the supported Redshift JDBC driver from AWS. – Nathan Griffiths Aug 21 '19 at 04:50
  • @NathanGriffiths - thank you, I suspect that too. I use workbench and KNIME (both JDBC) but will try beaver as haven't clicked with workbench. I'm pretty certain the answer is 'no', but it's an unasked question that I thought worth just sticking out there. – EILNP Aug 22 '19 at 07:45

2 Answers2

0

Not a direct answer, but I had the same issue using ADS (Azure Data Studio).

The best solution I found for querying a Redshift Database in a desktop app like ADS was DBeaver. It was pretty much a plug and play experience - Redshift was listed among the default connection options, and when first trying to connect DBeaver automatically identified the required drivers, then installed them for you with just a click. It also has a nice 'object explorer' accordion, so you can see the schemas/tables/views etc.

Much, much easier than AWS' recommended solution - SQL Workbench/J - which was both a faff to set up, and not nearly as user friendly straight out of the box

Simple_Jon
  • 11
  • 1
0

I struggled with using temp tables with Redshift in Azure data studio. You can explicitly start and end a transaction, thereby allowing to bypass this problem. You can do something like:

BEGIN;

Select * from [server].[database].[table];

SELECT [column] 
INTO TEMP TABLE #ReallyTempTable 
FROM [server].[database].[table];

SELECT *
FROM  #ReallyTempTable ;

END;

At the time of writing, you cannot perform the last select statement in azure data studio without wrapping it in a transaction.

DavidM
  • 1
  • 1