CODEWITHSUNDEEP
apache-sparkjdbcexternalsnappydata
Jiang Yan
Jiang Yan

Reputation: 41

How can I get external table jdbc url in SnappyData

Previously I created an external table in SnappyData like this:

create external table EXT_DIM_CITY
using jdbc options(url 'jdbc:mysql://***:5002/***?user=***&password=***', 
driver 'com.mysql.jdbc.Driver',
dbtable 'dim_city');

but now I forget the mysql jdbc url that EXT_DIM_CITY referred to. How can I get the jdbc url from SnappyData?

Upvotes: 1

Views: 184

Answers (1)

Sumedh
Sumedh

Reputation: 383

With the latest SnappyData release 1.0.2.1, all table properties can be seen with extended describe:

describe extended EXT_DIM_CITY

The properties will be visible under the "# Detailed Table Information" line that has the "Properties: " tag. Note that when running from snappy shell, you will need to increase the maximum display width to see the full value of the string column (maximumdisplaywidth 2000).

However, in this case the url property value is deliberately masked out as "###" because it contains the embedded password. If you had the "user" and "password" options specified separately, then only the "password" property would have been masked and url would be visible.

So in this case you can instead write a job to force display the value directly using catalog API like below (Scala code):

package test

import java.io.PrintWriter

import com.typesafe.config.Config

import org.apache.spark.sql.catalyst.TableIdentifier
import org.apache.spark.sql._

object CatalogReadJob extends SnappySQLJob {
  override def runSnappyJob(session: SnappySession, jobConfig: Config): Any = {
    val catalog = session.sessionCatalog
    val metadata = catalog.getTableMetadata(new TableIdentifier("EXT_DIM_CITY"))
    // dump metadata properties to a file
    new PrintWriter("/tmp/EXT_DIM_CITY-metadata.txt") {
      write(metadata.toString() + "\nFull URL = " + metadata.storage.properties("url"))
      close()
    }
  }

  override def isValidJob(ss: SnappySession, conf: Config): SnappyJobValidation = SnappyJobValid()
}

Use Some("schema") in TableIdentifier constructor above if the table is in a schema other than the default "APP". Compile the code using build tools like gradle/maven etc or directly using scalac: scalac -classpath '/path/to/product/jars/*' CatalogReadJob.scala

Create a jar, say test.jar, then submit: snappy-job.sh submit --lead :8090 --app-name CatalogRead --class test.CatalogReadJob --app-jar test.jar

The URL and all other table properties should be dumped in /tmp/EXT_DIM_CITY-metadata.txt

Upvotes: 1

Related Questions