drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacques Nadeau (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (DRILL-2093) Columns of time and timestamp data type are not stored correctly in json format on CTAS
Date Tue, 05 May 2015 14:30:00 GMT

     [ https://issues.apache.org/jira/browse/DRILL-2093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Jacques Nadeau resolved DRILL-2093.
-----------------------------------
    Resolution: Fixed

> Columns of time and timestamp data type are not stored correctly in json format on CTAS
> ---------------------------------------------------------------------------------------
>
>                 Key: DRILL-2093
>                 URL: https://issues.apache.org/jira/browse/DRILL-2093
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Storage - JSON
>    Affects Versions: 0.8.0
>            Reporter: Victoria Markman
>            Assignee: Jacques Nadeau
>            Priority: Critical
>             Fix For: 1.0.0
>
>         Attachments: t1.csv
>
>
> I have a csv file and am trying to create matching file in json format.
> {code}
> alter session set `store.format` = 'json';
> create table test_json(c_varchar, c_integer, c_bigint, c_smalldecimal, c_bigdecimal,
c_float, c_date, c_time, c_timestamp, c_boolean) as
> select
>         case when columns[0] = '' then cast(null as varchar(255)) else cast(columns[0]
as varchar(255)) end,
>         case when columns[1] = '' then cast(null as integer) else cast(columns[1] as
integer) end,
>         case when columns[2] = '' then cast(null as bigint) else cast(columns[2] as bigint)
end,
>         case when columns[3] = '' then cast(null as decimal(18,4)) else cast(columns[3]
as decimal(18, 4)) end,
>         case when columns[4] = '' then cast(null as decimal(38,4)) else cast(columns[4]
as decimal(38, 4)) end,
>         case when columns[5] = '' then cast(null as float) else cast(columns[5] as float)
end,
>         case when columns[6] = '' then cast(null as date) else cast(columns[6] as date)
end,
>         case when columns[7] = '' then cast(null as time) else cast(columns[7] as time)
end,
>         case when columns[8] = '' then cast(null as timestamp) else cast(columns[8] as
timestamp) end,
>         case when columns[9] = '' then cast(null as boolean) else cast(columns[9] as
boolean) end
> from `t1.csv`;
> {code}
> Create table succeeds, but I can't read back time or timestamp:
> {code}
> 0: jdbc:drill:schema=dfs> select cast(c_time as time) from test_json;
> Query failed: RemoteRpcException: Failure while running fragment., Invalid format: "1970-01-01T08:13:16.000Z"
is malformed at "70-01-01T08:13:16.000Z" [ b3f6c0c9-01e4-410f-919d-a899ede35ed9 on atsqa4-133.qa.lab:31010
]
> [ b3f6c0c9-01e4-410f-919d-a899ede35ed9 on atsqa4-133.qa.lab:31010 ]
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> 0: jdbc:drill:schema=dfs> select c_time from test_json;
> +------------+
> |   c_time   |
> +------------+
> | 1970-01-01T08:13:16.000Z |
> | 1970-01-01T04:15:45.000Z |
> | 1970-01-01T18:21:06.000Z |
> | 1970-01-01T13:35:54.000Z |
> | 1970-01-01T05:17:11.000Z |
> +------------+
> 5 rows selected (0.055 seconds)
> 0: jdbc:drill:schema=dfs> select cast(c_timestamp as timestamp) from test_json;
> Query failed: RemoteRpcException: Failure while running fragment., Invalid format: "2014-03-16T03:55:21.000Z"
is malformed at "T03:55:21.000Z" [ 527a42e5-2eb7-41d7-a55b-c6cee0779db6 on atsqa4-133.qa.lab:31010
]
> [ 527a42e5-2eb7-41d7-a55b-c6cee0779db6 on atsqa4-133.qa.lab:31010 ]
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> {code}



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

Mime
View raw message