如何在SnappyData中获取外部表jdbc url [英] How can I get external table jdbc url in SnappyData

问题描述

以前，我在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');

但是现在我忘记了EXT_DIM_CITY引用的mysql jdbc url.如何从SnappyData获取jdbc网址?

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

推荐答案

在最新的SnappyData版本1.0.2.1中，可以使用扩展describe来查看所有表属性:

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

describe extended EXT_DIM_CITY

这些属性将在带有属性:"标记的#详细表信息"行下可见.请注意，从灵活的外壳程序运行时，您需要增加最大显示宽度以查看字符串列的完整值(最大显示宽度2000).

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).

但是，在这种情况下，故意将url属性值屏蔽为"###"，因为它包含嵌入式密码.如果您分别指定了"user"和"password"选项，则只有"password"属性会被屏蔽，而url才可见.

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.

因此，在这种情况下，您可以编写一个作业来强制使用目录API直接显示值，如下所示(Scala代码):

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()
}

如果表不在默认的"APP"之外的其他模式中，请在上面的TableIdentifier构造函数中使用Some("schema").使用gradle/maven等构建工具或直接使用scalac编译代码:scalac -classpath'/path/to/product/jars/*'CatalogReadJob.scala

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

创建一个jar，例如说test.jar，然后提交:snappy-job.sh Submit --lead:8090 --app-name CatalogRead --class test.CatalogReadJob --app-jar test.jar

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

URL和所有其他表属性应转储到/tmp/EXT_DIM_CITY-metadata.txt

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

这篇关于如何在SnappyData中获取外部表jdbc url的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！