Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Spark-vertica connector adding 4hr to timestamp value.

I am fetching data from soucre (UTC+4) to spark (UTC), till here there is no change in timestamp value but when writing same data on Vertica (UTC+4), it is adding 4hr.

We can't change the timezone for whole vertica cluster.

Answers

  • For fetching data from source i am using plain jdbc and for load in to vertica i am using spark-vertica connector (V2).

  • SruthiASruthiA Employee

    @rj4rahuljha Could you please share sample spark program demonstrating the issue?

  • edited August 12

    val sparkSession = SparkSession.builder().appName("pg_to_df").getOrCreate()
    val jdbcDF = sparkSession.read.format("jdbc").option("url", "jdbc:postgresql://X.X.X.X:5432/XXXX?useUnicode=true&characterEncoding=UTF-8").option("query", "select * from A where file_id = '0xxxxxa955-xxxxxxx'").option("user", "user" ).option("password", "pass").option("driver", "org.postgresql.Driver").option("multiline", true).load()

    // TILL HERE DATA IS FINE

    val opts: Map[String, String] = Map(
    "host" -> "x.x.x.x",
    "user" -> "user",
    "db" -> "db_name",
    "password" -> "password",
    "staging_fs_url" -> "s3://xxxxxxx-dev/spark_vertica_conn/",
    "dbschema" -> "schema_name",
    "table" -> "table_name",
    "aws_credentials_provider" -> "org.apache.hadoop.fs.s3a.auth.IAMInstanceCredentialsProvider",
    "aws_region" -> "eu-west-1")

    val mode = SaveMode.Append
    jdbcDF.write.format("com.vertica.spark.datasource.VerticaSource").options(opts).mode(mode).save()

    // Now all the timestamp value is increased by 4 hr

    @SruthiA

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.