SQL DDL metadata into Spark schema metadata

user_16394869 0
Viewed 47

I can create a Spark StructType via DDL schema like so:

val ddl = "a STRING COMMENT 'max_length=1000'"
val schema = StructType.fromDDL(ddl)

This creates a schema where the field for column a looks like so:

StructField(
  name = "a",
  dataType = StringType,
  nullable = true,
  metadata = Metadata(Map("comment" -> "max_length=1000"))
)

After that, I can do something like this to put the comment as actual metadata:

val maxLengthMetadata = metadata.getString("comment") // max_length=1000
/*
String regex to grab elements individually e.g. 
key = "max_length" 
val = "1000"
*/
metadata.putString(key, val)

Is there a way to format ddl so the Metadata object can be populated like above without going through String manipulation after grabbing data from SQL comment? Something like this:

val ddl = "a STRING max_length='1000'"

So instead of

Metadata(Map("comment" -> "max_length=1000"))

I want

Metadata(Map("max_length" -> "1000"))

without having to go through the above roundabout way.

I've also tried running some scala code to see if I can put some metadata then run StructField.toDDL like so:

val metadata: Metadata = new MetadataBuilder()
  .putString("timestamp_mask", "yyyy-MM-dd")
  .build()

val schema = StructType(
  Seq(
    StructField("c", TimestampType, nullable = true, metadata)
  )
)

schema.fields.foreach(field => println(field.toDDL))

but this doesn't work either since toDDL depends on metadata.getString("comment")....

I don't see an easy way for DDL to support this kind of behavior.

apache-spark scala
1 Answers

I am not sure if it's possible to do it using DDL string but you can use json schema instead. For example:

import org.apache.spark.sql.types.{DataType, StructType}

val jsonSchema = """{"type":"struct","fields":[{"name":"col1","type":"string","nullable":true,"metadata":{"max-length": 100}}]}"""
val schema = DataType.fromJson(jsonSchema).asInstanceOf[StructType]
println(s"${schema.fields(0).name} - ${schema.fields(0).metadata}") // col1 - {"max-length":100}
user_124002140
Related