Thanks Andries! With FLOAT it works properly.
2016-01-25 17:01 GMT+01:00 Andries Engelbrecht <[email protected]>: > 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 <[email protected]> > 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 <[email protected]> > : > >> 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 <[email protected]> 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 <[email protected] >> > >> > 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> > > >
