SaveMode.Overwrite for string datatype
Hello,
In the SaveMode.Overwrite option for writing to a Vertica databse using scala , I am able to successfully write integer values, However , when I attempt to string values to the table in Vertica I get
java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ERROR: Type "TEXT" does not exist.
Pasted below is the code for writing an integer value to the Vertica table.
After that I have pasted the code for writing a character value to the Vertica table with the error message .
%spark
//Writing the Herirachy to Vertica table
import com.vertica.spark._
import org.apache.spark.sql.{DataFrame, Row, SQLContext, SaveMode}
import org.apache.spark.sql.types._
val resDF=spark.read.parquet("hdfs:/prod/11323/app/H9A0/data/DW00/DDWV21R/CC_HIER_CURR")
resDF.printSchema()
val newres = resDF.selectExpr("CM_EMP_ID")
val url = "jdbc:vertica://guerlpa16em02.fg.rbc.com:5433/eim_pe_vertica"
val prop = new java.util.Properties
prop.setProperty("user","321467367")
prop.setProperty("password","xxxxxx")
newres.write.mode(SaveMode.Overwrite).jdbc(url,"S_H9A0_AC.deleteMe",prop)
Log:
import com.vertica.spark._
import org.apache.spark.sql.{DataFrame, Row, SQLContext, SaveMode}
import org.apache.spark.sql.types._
resDF: org.apache.spark.sql.DataFrame = [CUSTM_MGRA_EMP_FULL_NM: string, CUSTM_MGRA_EMP_ID: decimal(13,0) ... 50 more fields]
root
|-- CUSTM_MGRA_EMP_FULL_NM: string (nullable = true)
|-- CUSTM_MGRA_EMP_ID: decimal(13,0) (nullable = true)
|-- CUSTM_MGRA_ORG_UNT_NO: integer (nullable = true)
|-- CUSTM_MGRB_EMP_FULL_NM: string (nullable = true)
|-- CUSTM_MGRB_EMP_ID: decimal(13,0) (nullable = true)
|-- CUSTM_MGRB_ORG_UNT_NO: integer (nullable = true)
|-- VP_EMP_FULL_NM: string (nullable = true)
|-- VP_EMP_ID: decimal(13,0) (nullable = true)
|-- VP_ORG_UNT_NO: integer (nullable = true)
|-- CM_ORG_UNT_NO: integer (nullable = true)
|-- CM_EMP_FULL_NM: string (nullable = true)
|-- CM_EMP_ID: decimal(13,0) (nullable = true)
|-- BM_ORG_UNT_NO: integer (nullable = true)
|-- BM_EMP_FULL_NM: string (nullable = true)
|-- BM_EMP_ID: decimal(13,0) (nullable = true)
newres: org.apache.spark.sql.DataFrame = [CM_EMP_ID: decimal(13,0)]
url: String = jdbc:vertica://guerlpa16em02.fg.rbc.com:5433/eim_pe_vertica
prop: java.util.Properties = {}
res570: Object = null
res571: Object = null
Now here is the same code trying to write a string value to the table
create table S_H9A0_AC.deleteMe (
-- CM_EMP_ID integer);
CM_EMP_FULL_NM varchar);
%spark
//Writing the Herirachy to Vertica table . Able to write an integer value but not a string value: CM_EMP_FULL_NM
import com.vertica.spark._
import org.apache.spark.sql.{DataFrame, Row, SQLContext, SaveMode}
import org.apache.spark.sql.types._
val resDF=spark.read.parquet("hdfs:/prod/11323/app/H9A0/data/DW00/DDWV21R/CC_HIER_CURR")
resDF.printSchema()
val newres = resDF.selectExpr("CM_EMP_FULL_NM")
val url = "jdbc:vertica://guerlpa16em02.fg.rbc.com:5433/eim_pe_vertica"
val prop = new java.util.Properties
prop.setProperty("user","321467367")
prop.setProperty("password","xxxxxx")
newres.write.mode(SaveMode.Overwrite).jdbc(url,"S_H9A0_AC.deleteMe",prop)
Here is the partial log
import com.vertica.spark._
import org.apache.spark.sql.{DataFrame, Row, SQLContext, SaveMode}
import org.apache.spark.sql.types._
resDF: org.apache.spark.sql.DataFrame = [CUSTM_MGRA_EMP_FULL_NM: string, CUSTM_MGRA_EMP_ID: decimal(13,0) ... 50 more fields]
root
|-- CUSTM_MGRA_EMP_FULL_NM: string (nullable = true)
|-- CUSTM_MGRA_EMP_ID: decimal(13,0) (nullable = true)
|-- CUSTM_MGRA_ORG_UNT_NO: integer (nullable = true)
|-- CUSTM_MGRB_EMP_FULL_NM: string (nullable = true)
|-- CUSTM_MGRB_EMP_ID: decimal(13,0) (nullable = true)
|-- CUSTM_MGRB_ORG_UNT_NO: integer (nullable = true)
|-- VP_EMP_FULL_NM: string (nullable = true)
|-- VP_EMP_ID: decimal(13,0) (nullable = true)
|-- VP_ORG_UNT_NO: integer (nullable = true)
|-- CM_ORG_UNT_NO: integer (nullable = true)
|-- CM_EMP_FULL_NM: string (nullable = true)
|-- CM_EMP_ID: decimal(13,0) (nullable = true)
|-- BM_ORG_UNT_NO: integer (nullable = true)
|-- BM_EMP_FULL_NM: string (nullable = true)
|-- BM_EMP_ID: decimal(13,0) (nullable = true)
|-- TM_ORG_UNT_NO: integer (nullable = true)
|-- TM_EMP_FULL_NM: string (nullable = true)
|-- TM_EMP_ID: decimal(13,0) (nullable = true)
|-- AG_EMP_FULL_NM: string (nullable = true)
|-- AG_EMP_ID: decimal(13,0) (nullable = true)
|-- AG_ORG_UNT_NO: integer (nullable = true)
|-- ENTITY: string (nullable = true)
|-- LOB: string (nullable = true)
|-- PHYSCL_SITE: string (nullable = true)
|-- CHANNEL: string (nullable = true)
|-- ROLE_NM: string (nullable = true)
|-- ROLE_ID: string (nullable = true)
|-- ROE_FLG: string (nullable = true)
|-- ROE_LOC: string (nullable = true)
|-- DEP_S_CD: string (nullable = true)
|-- AG_PRFNCY_TRNEE_FLG: string (nullable = true)
|-- AG_INCNTV_FLG: string (nullable = true)
|-- AG_ACS_FLG: string (nullable = true)
|-- AG_FLG_A: string (nullable = true)
|-- AG_FLG_B: string (nullable = true)
|-- PILOT_SWAT_FLG: string (nullable = true)
|-- CSTM_DIM_A: string (nullable = true)
|-- CSTM_DIM_B: string (nullable = true)
|-- CSTM_DIM_C: string (nullable = true)
|-- CSTM_DIM_D: string (nullable = true)
|-- CSTM_DIM_E: string (nullable = true)
|-- SYS_SRC_ID: integer (nullable = true)
|-- POSN_AUTH_WRK_SCHDL_WK_HR: decimal(8,2) (nullable = true)
|-- POSN_AUTH_WRK_ARGMT_WK_HR: decimal(8,2) (nullable = true)
|-- LEAVE_OF_ABS_IND: integer (nullable = true)
|-- LANG: string (nullable = true)
|-- STATUS: string (nullable = true)
|-- CAL_DT: date (nullable = true)
|-- UPD_FLG: string (nullable = true)
|-- CAPTR_DT: date (nullable = true)
|-- CHG_DT: date (nullable = true)
|-- LST_UPDT_DT_TM: timestamp (nullable = true)
newres: org.apache.spark.sql.DataFrame = [CM_EMP_FULL_NM: string]
url: String = jdbc:vertica://guerlpa16em02.fg.rbc.com:5433/eim_pe_vertica
prop: java.util.Properties = {}
res586: Object = null
res587: Object = null
java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ERROR: Type "TEXT" does not exist
at com.vertica.util.ServerErrorData.buildException(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.executeSimpleProtocol(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source)
at com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source)
at com.vertica.jdbc.common.SStatement.executeUpdate(Unknown Source)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.createTable(JdbcUtils.scala:692)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:72)
at org.apache.spark.sql.execution.datasources.DataSource.write(DataSource.scala:518)
at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:215)
Appreciate your help in this matter