current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to unload data from RedShift to S3 using Java + Hibernate, but cannot figure out how to get it to accept the string-within-a-string in the SQL.

No matter the escape sequences I try to use to get the quote characters correctly passed to RedShift, I just get exception after exception.

Any ideas would be welcome. Thanks.

Here's the query I'm trying to run on RedShift (and if I type this directly into RedShift, it works):

unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ')
  to 's3://myBucket/myTable/'
  credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip;

And here are the various commands I've tried in Java, with the errors I've encountered:

Single backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "2013" Position: 66
ERROR org.hibernate.exception.SQLGrammarException: could not execute statement

Double backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\'2013-01-01\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
ERROR org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ') to 's3://myBucket/myTable/' credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip]
at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:95)

Triple backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\\'2013-01-01\\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
2013-06-19 01:39:59,233 ERROR org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ') to 's3://myBucket/myTable/' credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip]
at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:95)

Quadruple backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\\\'2013-01-01\\\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "2013" Position: 68
ERROR org.hibernate.exception.SQLGrammarException: could not execute statement

Named parameter

session.createSQLQuery("unload (:query)"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .setString("query", "select raw_line from stl_load_errors where starttime > '2013-01-01'")
  .executeUpdate();

RESULTS:
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "$1" Position: 9
ERROR org.hibernate.exception.SQLGrammarException: could not execute statement

share|improve this question
    
you are putting key and secret not parametrized, sqlinjection, why not putting date parametrized too? :date :key :secret –  nachokk Jun 19 '13 at 2:32
    
If I can get any of this to work, I'll certainly clean it up (named parameters all around and such) –  Tinclon Jun 19 '13 at 2:41

2 Answers 2

up vote 1 down vote accepted

You should take a look at dollar quoted strings:

http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

share|improve this answer
    
FANTASTIC! Worked like a charm. Thanks so much. –  Tinclon Jun 21 '13 at 4:29
up vote 0 down vote

I was given the suggestion to try to put the s3 path as a named parameter, as Hibernate (in the double and triple backslash cases) seems to not like the colon character.

Doing so, gave me this error:

ERROR java.lang.IllegalArgumentException: Parameter s3 does not exist as a named parameter in [unload ('select raw_line from stl_load_errors where starttime > \'2014-01-01\'') to :s3 credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip]

I get the impression that Hibernate is getting confused with the escaped single quotes and loses track of what is supposed to be quoted and what isn't (the colon after s3 shouldn't have posed a problem in the first place, as it was inside quoted text, but apparently Hibernate can't figure that out).

Sometimes you're just too close to the problem. After not thinking about it for a while, the obvious solution (workaround, really), is that I'll be doing the following:

  1. Create a temporary table
  2. Insert the needed data into the temporary table (no need for nested strings)
  3. Unload the entire contents of the temporary table to S3 (no need for nested strings)
  4. Drop the temporary table
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.