7621

Python - Dynamic variable in Redshift SQL Query

Question:

I'm developing in a python environment and I want to call an sql query using psycopg2

Lets say I have the following UNLOAD command in an .sql file:

UNLOAD ( ' Some SQL Query ' ) TO 's3://%PATH%' ...

In the sql file the %PATH% should be explicit like: 'folder1/folder3/file_name'.

But I want the python program to set this %PATH% in runtime. which means that the .sql file containts something like %PATH% and will be set only in runtime.

Any idea of how to do it?

Answer1:

You simply specify a <a href="https://docs.python.org/3/library/string.html#format-string-syntax" rel="nofollow">replacement field</a> in your SQL file, and the use a format command.

Create your file like this

UNLOAD ('Some SQL Query') TO 's3://{bucket}/{key}'

And use this file in python like

template = open('file1.sql', 'r').read() query = template.format(bucket='mybucket', key='folder/file.csv')

Answer2:

Implementing it this way will give you a tough time.

The best way to do is to dump the file at a static location:

UNLOAD ( ' Some SQL Query ' ) TO 's3://path/to/static/s3_bucket' ...

and then use (via a shellscript / or opt for a suitable command for any other script)

aws s3 mv $source $destination

Here, you may pass any value for $destination which can be easily populated during run-time.

<blockquote>

In short, you've dumped the file in s3 at a fixed location (using UNLOAD) and moved it to the location of your choice or a location populated at run time (using aws s3 mv...)

</blockquote>

Answer3:

You would not be able to set up the UNLOAD path dynamically at runtime, however you could put your SQL statement in a something like a shell/python script where you can create variables with the path you'd like and then pass them into the query.

<a href="https://github.com/awslabs/amazon-redshift-utils/blob/master/src/UnloadCopyUtility/redshift-unload-copy.py" rel="nofollow">This</a> UNLOAD utility by AWS will get you started if you decide to go with a python script.

Recommend

  • How to convert complex JSON to CSV using JQ 1.4
  • Find element and add class (jQuery)
  • What happens if mysql_insert_id called form different places and different browsers at the same time
  • How to grab a string that is between 2 strings
  • How do I chomp a string if I have Perl 4?
  • javascript Confirm replacement with return true/false
  • Insert space after period using sed
  • runtime error when linking ffmpeg libraries in qt creator
  • Use sed with regex and (
  • Ruby on Rails App deployed to heroku showing “We're sorry, but something went wrong”
  • Detecting null parameter in preprocessor macro
  • C# program and C++ DLL compiled for 32-bit system crash on 64-bit system
  • WPF ICommand CanExecute(): RaiseCanExecuteChanged() or automatic handling via DispatchTimer?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • Retrieving value from sql ExecuteScalar()
  • Read text file and split every line in MSBuild
  • C# - Serializing and deserializing static member
  • Java applet as stand-alone Windows application?
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Javascript Callbacks with Object constructor
  • MySQL WHERE-condition in procedure ignored
  • How can I use Kendo UI with Razor?
  • When should I choose bucket sort over other sorting algorithms?
  • Font Awesome Showing Box instead of Icons
  • Adding custom controls to a full screen movie
  • How do you troubleshoot character encoding problems?
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • Comma separated Values
  • KeystoneJS: Relationships in Admin UI not updating
  • Error creating VM instance in Google Compute Engine
  • How to set the response of a form post action to a iframe source?
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • how does django model after text[] in postgresql [duplicate]
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Is it possible to post an object from jquery to bottle.py?
  • Checking variable from a different class in C#
  • reshape alternating columns in less time and using less memory
  • Python/Django TangoWithDjango Models and Databases