How to write SQL query in Java to get data from Snowflake

Question

I am trying to write this Snowflake query into Java code:

copy into s3://snowflake171
  from USER_TABLE
  storage_integration = s3_int
  file_format = CSV_TEST;

I am writing it like this:

 String q ="COPY INTO s3://snowflake171\n" +
                    "  FROM \"TEST\".\"PUBLIC\".\"USER_TABLE\"WHERE\"ID\\\"=?\"\n" +
                    "  storage_integration = s3_int\n" +
                    "  file_format = CSV_TEST";

But I am getting this error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [COPY INTO s3://snowflake171
  FROM "TEST"."PUBLIC"."USER_TABLE"WHERE"ID\"=?"
  storage_integration = s3_int
  file_format = CSV_TEST]; nested exception is net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
syntax error line 1 at position 5 unexpected '<EOF>'.

What am I missing?

"unexpected 'INOT" - it seems you're not sending INTO but INOT. Please check your code. — Thomas
– Thomas, Commented Oct 20, 2021 at 13:01
WHERE"ID\"=?" - you're missing the parameter here. Also, the query seems to miss the semi-colon at the end: I'd say it should be " file_format = CSV_TEST;"; — Thomas
– Thomas, Commented Oct 20, 2021 at 13:04
I have fixed parameter now I HAVE THIS ERROR: SQL access control error: Insufficient privileges to operate on integration 'S3_INT' — user9347049
– user9347049, Commented Oct 20, 2021 at 13:07

Gokhan Atil · Accepted Answer · 2021-10-20 13:38:42Z

2

As the latest error message is "SQL access control error: Insufficient privileges to operate on integration 'S3_INT'", can you try to grant USAGE permission on s3_int to your role? Are you sure you set the correct role when connecting?

String url = "jdbc:snowflake://<account_identifier>.snowflakecomputing.com";
Properties prop = new Properties();
prop.put("user", "<user>");
prop.put("privateKey", PrivateKeyReader.get(PRIVATE_KEY_FILE));
prop.put("db", "<database_name>");
prop.put("schema", "<schema_name>");
prop.put("warehouse", "<warehouse_name>");
prop.put("role", "<role_name>");

answered Oct 20, 2021 at 13:38

Gokhan Atil

10.6k2 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9347049 Over a year ago

Thank you that was it! There is also possibility to put it in url like this: jdbc:snowflake://account.aws.snowflakecomputing.com/?db=TEST&warehouse=SF_TUTS_WH&schema=PUBLIC&role=ACCOUNTADMIN

Collectives™ on Stack Overflow

How to write SQL query in Java to get data from Snowflake

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related