Redshift: The JDBC storage plugin failed while trying setup the SQL query

1

The queries worked on v3.1.8, now throw this error, since I upgraded to 3.1.9
The JDBC storage plugin failed while trying setup the SQL query.

2

@Rohithzr,

3

here is the profile of the failed query.
a86421b3-0ce7-4ea0-84c5-78ce998f4719.zip (44.3 KB)

settings
Record fetch size: 0
Enable legacy dialect: checked

4

@ben
i am also getting these logs in the master

 2019-04-16T18:00:11.383+0000: [GC (Allocation Failure) [PSYoungGen: 58560K->2432K(72192K)] 213357K->159421K(247296K), 0.0055391 secs] [Times: user=0.03 sys=0.00, real=0.00 secs] 
2019-04-16T18:00:11.479+0000: [GC (Allocation Failure) [PSYoungGen: 60800K->1664K(71680K)] 217789K->160969K(246784K), 0.0052010 secs] [Times: user=0.03 sys=0.00, real=0.01 secs] 
2019-04-16T18:00:11.575+0000: [GC (Allocation Failure) [PSYoungGen: 60032K->1056K(72704K)] 219337K->161945K(247808K), 0.0048196 secs] [Times: user=0.02 sys=0.00, real=0.01 secs] 
2019-04-16T18:00:11.699+0000: [GC (Allocation Failure) [PSYoungGen: 60960K->3108K(72704K)] 221849K->164829K(247808K), 0.0183040 secs] [Times: user=0.11 sys=0.00, real=0.02 secs]


 192.168.8.2 - - [16/Apr/2019:18:00:11 +0000] "GET /apiv2/job/2349f07a-1202-5b43-f392-e496318b6000/details HTTP/1.1" 200 1446 "https://dremio.kube.labroots.inkeystone.com/jobs?filters=%7B%22qt%22%3A%5B%22UI%22%2C%22EXTERNAL%22%5D%7D&order=DESCENDING&sort=st" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36"
2019-04-16 18:00:11,967 [FABRIC-rpc-event-queue] INFO  c.d.exec.work.foreman.QueryManager - Fragment 2349e7d8-646b-535f-31a4-fc69fbd85a00:0:0 failed, cancelling remaining fragments.
2019-04-16 18:00:11,971 [out-of-band-observer] INFO  query.logger - {"queryId":"2349e7d8-646b-535f-31a4-fc69fbd85a00","schema":"[us-patents]","queryText":"SELECT \n    patent_id, \n    test_value_data.\"date\" AS \"patent_date\", \n    kind, \n    num_claims, \n    number, \n    assignee_id, \n    organization\nFROM us_patents.test_value_data AS test_value_data","start":1555437607277,"finish":1555437611967,"outcome":"FAILED","username":"rohithzr"}
2019-04-16 18:00:11,979 [FABRIC-rpc-event-queue] WARN  c.d.exec.work.foreman.AttemptManager - Dropping request to move to COMPLETED state as query is already at FAILED state (which is terminal).
5

@ben

more logs

starting dremio
rename to txt from zip.

logs-from-dremio-executor-in-dremio-executor-0.zip (398.0 KB)

6

Hello @Rohithzr,

If you look at the profile, under the Planning tab there’s a section called the Final Physical Transformation. This is the query plan that is executed and you can see that a large part of the query is pushed down into the Redshift source. It is during that pushdown that we see the root error (as can be seen in the verbose error message in the profile)

(java.sql.SQLException) Amazon Invalid operation: function datediff(date, timestamp without time zone) does not exist;

This query references several virtual datasets that call the function datediff which is not supported in Dremio. Specifically us_patents.all_forward_citation_counts and us_patents.all_backward_citation_counts call this function.

Can you successfully run queries against those virtual datasets?

If not, you can try a couple of things:

  1. Disable “Legacy dialect”
    … or …
  2. Replace the call to datediff with an equivalent call to Dremio’s timestampdiff