Skip to main content
Question

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

  • July 31, 2019
  • 5 replies
  • 20 views
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

    5 replies

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • July 31, 2019

    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
    • helenzusa
    • Author
    • New Participant
    • July 31, 2019

    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
    • helenzusa
    • Author
    • New Participant
    • July 31, 2019

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

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • August 2, 2019

    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
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • August 2, 2019

    Please check the below link. It might be helpful

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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement