[Vertica][VJDBC](5108) ERROR: Type "TEXT" does not exist

helenzusa

I'm working on Pyspark. I need to save a DataFrame to vertica table. But I got error:

[Vertica]VJDBC ERROR: Type "TEXT" does not exist

My DataFrame has some columns with string type, but vertica has VARCHAR.

root
|-- STARTHOUR: timestamp (nullable = true)
|-- CELL: string (nullable = true)
|-- Risk1: integer (nullable = true)
|-- Risk2: integer (nullable = true)
|-- Risk3: integer (nullable = true)
|-- MGRSID: string (nullable = true)
|-- LAT: double (nullable = true)
|-- LONG: double (nullable = true)
|-- ALERT_TYPE: string (nullable = true)

Do you have any solution? I saw some where mentioned solution with Scala. But I'm looking for solution on Pyspark. Any help is appreciated.

Thanks,
Helen

SruthiA

the issue is because of string type present in your DF. Spark converts string type to TEXT and vertica doesn't support it. What is your spark version?

It looks like the issue was fixed in spark 2.2.0

https://issues.apache.org/jira/browse/SPARK-10101

helenzusa

Hi, I read the post. I modified moy code as follows:

alert_schema=StructType([
StructField("STARTHOUR", TimestampType(), False),
StructField("CELL", dbStringDataType(), False),
StructField("Risk1", IntegerType(), False),
StructField("Risk2", IntegerType(), False),
StructField("Risk3", IntegerType(), False),
StructField("MGRS", dbStringDataType(), False),
StructField("LAT", DoubleType(), False),
StructField("LONG", DoubleType(), False),
StructField("ALERT_TYPE", dbStringDataType(), False)
])

But I got error:NameError: name 'dbStringType' is not defined

Is this the right way? Should I include some module in Pyspark?

helenzusa

But I got error:NameError: name 'dbStringDataType' is not defined

Bryan_H

Have a look here and the linked posts, you may need to create as string, then cast to the supported type when opening or writing to JDBC:
https://stackoverflow.com/questions/54191395/can-i-change-the-datatype-of-the-spark-dataframe-columns-that-is-being-loaded-to

SruthiA

Please check the below link. It might be helpful

https://akashrehan.wordpress.com/2018/01/22/spark-dialect-for-datatype-conversion/