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] > <mailto:[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] > > <mailto:[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] > > <mailto:[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>
