Question 199728 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Is there a way to query the current host/connection details or similar environment-differentiating info using SQL in Redshift?

I’m trying to conditionally deploy environment variables in a Redshift database, but I can’t find how to check what connection (host name etc) I’m currently on. I have the database name using select current_database() but can’t see a similar thing to query for the connection data. Postgres’s inet_server_addr() is also not supported in Redshift to find data like this.

Edit: the actual question was more about finding out which environment the SQL is being executed in, but I’d got hung up on finding out the connection string or similar, whereas just getting the ID of the currently connected cluster would satisfy this requirement. I’ve updated the question title to reflect this.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I later have been told about a way to tell which Redshift cluster you were on - select current_namespace - which returns a UUID that can then be used in combination with the current_database() function to reliably determine which environment a script is being run in. You will just need to know what cluster you’re on before creating the scripts initially by querying for what the IDs are for each environment, and then use those in the script as required.


  2. 0

    A Redshift server is dynamically allocated virtual machine which is running on a hypervisor which is running on a physical server. The network connection for Redshift is a virtual network card with IP address that works through the physical card on the server and its own IP address. There is likely a number of network devices rerouting your request to Redshift – gateways, DNS servers, network tunnel, connection pooling servers, and/or other equipment.

    Redshift internally doesn’t know how you addressed the database. In fact you can have multiple copies of the same database on different Redshift clusters that work exactly the same. This sort of infrastructure as a service is a value of cloud computing.

    You need to look locally to find how you connected to the database. Finding this will be dependent what you are using to connect to Redshift. "Database URL" or "conninfo" might be leads for finding what you need but you will need to look at the documentation for your tool.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search