drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paolo Spanevello <paolosp...@gmail.com>
Subject Re: Drill ODBC: format number on excel looks like Text
Date Sat, 06 Feb 2016 16:55:57 GMT
Thanks Andries!

With FLOAT it works properly.

2016-01-25 17:01 GMT+01:00 Andries Engelbrecht <aengelbrecht@maprtech.com>:

> The JSON data has the data type as a string, so it will require a
> conversion to a numeric type. Do you require decimal, or would FLOAT or
> DOUBLE suffice? Decimal data type is typically slower than the others.
>
> Or is it feasible to do the conversion in Excel? Setting the separator to
> "." on the import and then going back.
>
> Alternatively you can convert with Drill on the string data type by using
> regexp_replace to convert for these columns the "." to a "," , and see if
> that has the required performance compared to DECIMAL data conversion.
>
>
> --Andries
>
>
>
> On Jan 23, 2016, at 10:23 AM, Paolo Spanevello <paolospane@gmail.com>
> wrote:
>
> Dear all,
>
> thanks for your reply. In the DataSet it looks with dot.
>
> "METRICS":{
> "1s_critical_power":"2.42418",
>
> and when they are imported to excel it looked string as showed into the
> previous mail.
>
> I had to use this workaround and it works for one field:
>
> select CAST(tt.flat_intervals.metrics.`1s_critical_power` as DECIMAL(28,
> 3)) as `1s_critical_power`,...
>
> but it does not work if i have 15 fields to "cast" the performance is
> incredible low until i have to kill the drill. On my microsoft excel, the
> numbers has the comma "," and the string has the dot ".".
>
> In attach you can find the dataset. Do you have any suggest?
>
> Thanks for your time.
>
> Paolo
>
>
>
>
>
> 2016-01-22 18:26 GMT+01:00 Andries Engelbrecht <aengelbrecht@maprtech.com>
> :
>
>> What does the JSON data look like?
>>
>> I did a quick test with Excel and MS Query through ODBC to connect to
>> Drill.
>>
>> Selecting data as either a string or numeric value.
>>
>> select * from (values('925.000',925.0000))
>>
>> The results returned is
>> 925.000  for the string value and 925 for the numeric value to Excel.
>>
>>
>> With Drill 1.4 you can use typeof() to see what data type it being
>> interpreted as.
>>
>> --Andries
>>
>>
>>
>> > On Jan 22, 2016, at 8:50 AM, Ted Dunning <ted.dunning@gmail.com> wrote:
>> >
>> > This sounds like this might be a problem of decimal point separator.
>> Can
>> > you say what decimal point character you normally use? It might also be
>> > that this is set differently on the exel machine from the machine where
>> > drill is running.
>> >
>> > I am presuming that the value that you want to see is 925.0
>> >
>> > Is that correct?
>> >
>> >
>> > On Fri, Jan 22, 2016 at 6:33 AM, Paolo Spanevello <paolospane@gmail.com
>> >
>> > wrote:
>> >
>> >> Dear All,
>> >> i'm drilling a JSON File with some fields with numbers with this
>> format :
>> >>
>> >> 1s_critical_power
>> >> 925.00000
>> >>
>> >>
>> >> I'm using ODBC Driver to connect it on excel and the result aspect is
>> >>
>> >> 1s_critical_power
>> >> 925,00000
>> >> Do you know the right way to have it?
>> >>
>> >> Best regards,
>> >> Paolo
>> >>
>>
>>
> <rideDB.json>
>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message