2

I'm currently connecting to Presto using Spark. Our queries are getting timed out after 60m, to increase query execution time I've set query.max-execution-time parameters in getDBProperties() like below

private def constructPrestoDataFrame(sparkSession : SparkSession, jobConfig : Config, query : String) : DataFrame = {
    sparkSession
      .read
      .jdbc(getPrestoConnectionUrl(jobConfig), query, getDBProperties(jobConfig))
  }

  private def getDBProperties(jobConfig : Config) : Properties = {
    val dbProperties = new Properties
    dbProperties.put("user", jobConfig.getString("presto.user"))
    dbProperties.put("password", jobConfig.getString("presto.password"))
    dbProperties.put("Driver", jobConfig.getString("presto.driver.name"))
    dbProperties.put("query.max-execution-time", "2d")

    dbProperties
  }

  private def getPrestoConnectionUrl(jobConfig : Config) : String = {
    s"jdbc:presto://${jobConfig.getString("presto.host")}:8443/${jobConfig.getString("presto.catalogue.name")}?SSL=true&SSLTrustStorePath=${jobConfig.getString("sslTrustStorePath")}"+
      "&SSLTrustStorePassword="+URLEncoder.encode(jobConfig.getString("sslTrustStorePassword"))
  }

When I run the job I'm getting exception saying exception caught: Cause = null Message = Unrecognized connection property 'query.max-execution-time'

We use apache-spark-2.3.x, presto-jdbc-driver-300.

halfer
  • 19,824
  • 17
  • 99
  • 186
Anoop Deshpande
  • 514
  • 1
  • 6
  • 23
  • isn't this part of the session properties of Presto? Or at least did you try it as session property? The key would be `query_max_execution_time` in that case – UninformedUser Jul 26 '20 at 08:21
  • Yes, I tried with `query_max_execution_time`. I'm getting same exception. – Anoop Deshpande Jul 26 '20 at 08:27
  • @UninformedUser If my understanding is correct, the way we add `session properties` is same as how we add other data base properties (adding to `dbProperties` object) – Anoop Deshpande Jul 26 '20 at 08:33
  • 1
    from my understanding, and according to the docs, it only takes JDBS params because it's just for the JDBC driver: https://prestosql.io/docs/current/installation/jdbc.html - so I think what you want to set is part of your Presto setup itself, there should be a properties file somewhere around used by your Presto instance. – UninformedUser Jul 26 '20 at 08:36
  • I agree with your point @UninformedUser, but in my case I don't have access to change any configuration settings at presto instance level. If I can set the configuration within my connection/session scope, we are good – Anoop Deshpande Jul 26 '20 at 10:16

1 Answers1

1

Adding MAX_EXECUTION_TIME to sessionVariables in the URL does the job for me:

jdbc:mysql://{host}:{port}/{database}?sessionVariables=MAX_EXECUTION_TIME=123456666

Query to verify:

SELECT @@max_execution_time

Expected output:

+--------------------+ 
|@@max_execution_time| 
+--------------------+ 
| 123456666          | 
+--------------------+
Beniamin H
  • 2,048
  • 1
  • 11
  • 17