The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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

  • rj4rahuljharj4rahuljha Vertica Customer

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

  • SruthiASruthiA Vertica Employee Employee

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

  • rj4rahuljharj4rahuljha Vertica Customer
    edited August 2021

    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