[jira] [Commented] (CARBONDATA-37) Support Date/Time format for Timestamp columns to be defined at column level

[Akash R Nilugal (Jira)]

    [ https://issues.apache.org/jira/browse/CARBONDATA-37?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15614001#comment-15614001 ]

ASF GitHub Bot commented on CARBONDATA-37:
------------------------------------------

Github user QiangCai commented on a diff in the pull request:

    https://github.com/apache/incubator-carbondata/pull/219#discussion_r85460491
 
    --- Diff: integration/spark/src/test/scala/org/apache/carbondata/spark/testsuite/dataload/TestLoadDataWithDiffTimestampFormat.scala ---
    @@ -0,0 +1,117 @@
    +/*
    + * Licensed to the Apache Software Foundation (ASF) under one
    + * or more contributor license agreements.  See the NOTICE file
    + * distributed with this work for additional information
    + * regarding copyright ownership.  The ASF licenses this file
    + * to you under the Apache License, Version 2.0 (the
    + * "License"); you may not use this file except in compliance
    + * with the License.  You may obtain a copy of the License at
    + *
    + *    http://www.apache.org/licenses/LICENSE-2.0
    + *
    + * Unless required by applicable law or agreed to in writing,
    + * software distributed under the License is distributed on an
    + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    + * KIND, either express or implied.  See the License for the
    + * specific language governing permissions and limitations
    + * under the License.
    + */
    +
    +package org.apache.carbondata.spark.testsuite.dataload
    +
    +import org.apache.spark.sql.common.util.CarbonHiveContext._
    +import org.apache.spark.sql.common.util.QueryTest
    +import org.scalatest.BeforeAndAfterAll
    +import java.sql.Timestamp
    +
    +import org.apache.carbondata.core.constants.CarbonCommonConstants
    +import org.apache.carbondata.core.util.CarbonProperties
    +import org.apache.carbondata.spark.exception.MalformedCarbonCommandException
    +import org.apache.spark.sql.Row
    +
    +class TestLoadDataWithDiffTimestampFormat extends QueryTest with BeforeAndAfterAll {
    +  override def beforeAll {
    +    sql("DROP TABLE IF EXISTS t3")
    +    sql("""
    +           CREATE TABLE IF NOT EXISTS t3
    +           (ID Int, date Timestamp, starttime Timestamp, country String,
    +           name String, phonetype String, serialname String, salary Int)
    +           STORED BY 'carbondata'
    +        """)
    +    CarbonProperties.getInstance()
    +      .addProperty(CarbonCommonConstants.CARBON_TIMESTAMP_FORMAT, "yyyy/MM/dd")
    +  }
    +
    +  test("test load data with different timestamp format") {
    +      sql(s"""
    +           LOAD DATA LOCAL INPATH './src/test/resources/timeStampFormatData1.csv' into table t3
    +           OPTIONS('dateformat' = 'starttime:yyyy-MM-dd HH:mm:ss')
    +           """)
    +      sql(s"""
    +           LOAD DATA LOCAL INPATH './src/test/resources/timeStampFormatData2.csv' into table t3
    +           OPTIONS('dateformat' = 'date:yyyy-MM-dd,starttime:yyyy/MM/dd HH:mm:ss')
    +           """)
    +      checkAnswer(
    +        sql("SELECT date FROM t3 WHERE ID = 1"),
    +        Seq(Row(Timestamp.valueOf("2015-07-23 00:00:00.0")))
    +      )
    +      checkAnswer(
    +        sql("SELECT starttime FROM t3 WHERE ID = 1"),
    +        Seq(Row(Timestamp.valueOf("2016-07-23 01:01:30.0")))
    +      )
    +      checkAnswer(
    +        sql("SELECT date FROM t3 WHERE ID = 18"),
    +        Seq(Row(Timestamp.valueOf("2015-07-25 00:00:00.0")))
    +      )
    +      checkAnswer(
    +        sql("SELECT starttime FROM t3 WHERE ID = 18"),
    +        Seq(Row(Timestamp.valueOf("2016-07-25 02:32:02.0")))
    +      )
    +  }
    +
    +  test("test load data with different timestamp format with being set an empty string") {
    +    try {
    +      sql(s"""
    +           LOAD DATA LOCAL INPATH './src/test/resources/timeStampFormatData1.csv' into table t3
    +           OPTIONS('dateformat' = '')
    +           """)
    +      assert(false)
    +    } catch {
    +      case ex: MalformedCarbonCommandException =>
    +        assertResult(ex.getMessage)("Error: Option DateFormat is set an empty string.")
    +      case _ => assert(false)
    +    }
    +  }
    +
    +  test("test load data with different timestamp format with a wrong column name") {
    +    try {
    +      sql(s"""
    +           LOAD DATA LOCAL INPATH './src/test/resources/timeStampFormatData1.csv' into table t3
    +           OPTIONS('dateformat' = 'fasfdas:yyyy/MM/dd')
    +           """)
    +      assert(false)
    +    } catch {
    +      case ex: MalformedCarbonCommandException =>
    +        assertResult(ex.getMessage)("Error: Wrong Column Name fasfdas is provided in Option DateFormat.")
    +      case _ => assert(false)
    +    }
    +  }
    +
    +  test("test load data with different timestamp format with a timestamp column is set an empty string") {
    +    try {
    +      sql(s"""
    +           LOAD DATA LOCAL INPATH './src/test/resources/timeStampFormatData1.csv' into table t3
    +           OPTIONS('dateformat' = 'fasfdas:')
    +           """)
    +      assert(false)
    +    } catch {
    +      case ex: MalformedCarbonCommandException =>
    +        assertResult(ex.getMessage)("Error: Wrong Column Name fasfdas is provided in Option DateFormat.")
    +      case _ => assert(false)
    +    }
    +  }
    +
    +  override def afterAll {
    --- End diff --
   
    dateformat='startTime'
    dateformat='startTime : '
    dateformat=' startTime : yyyy-MM-dd HH:mm:ss '
    dateformat='startTime:ydm1'
    dateformat=':yyyy/MM/dd'

> Support Date/Time format for Timestamp columns to be defined at column level
> ----------------------------------------------------------------------------
>
>                 Key: CARBONDATA-37
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-37
>             Project: CarbonData
>          Issue Type: Improvement
>            Reporter: Vimal Das Kammath
>            Assignee: Lionx
>
> Carbon support defining the Date/Time format. But the configuration for the same is present in carbon.properties and hence is global for all tables.
> This global configuration for timestamp format cannot support scenarios where different tables or different Timestamp columns in the same table.
> Suggest to provide option in the create table DDL itself to define the format for each Timestamp column. Also provide defaults so that users can create table with Timestamp columns without having to always define the Date/Time format.

--
This message was sent by Atlassian JIRA
(v6.3.4#6332)