Thanks - I knew that - I was trying to understand better what was going on under the hood. Kristine's link got me what I needed.
On Mon, Mar 30, 2015 at 1:48 PM, Sudhakar Thota <[email protected]> wrote: > Vince, > > Can you try doing this again and query one more time. > > alter system set `store.json.all_text_mode` = true > > I used your file on sandbox and queried. It is working. > > 0: jdbc:drill:> select * from sys.options where name like '%all_text%'; > > +------------+------------+------------+------------+------------+------------+------------+ > | name | kind | type | num_val | string_val | > bool_val | float_val | > > +------------+------------+------------+------------+------------+------------+------------+ > | store.mongo.all_text_mode | BOOLEAN | SYSTEM | null | null > | false | null | > | store.json.all_text_mode | BOOLEAN | SYSTEM | null | null > | true | null | > > +------------+------------+------------+------------+------------+------------+------------+ > 2 rows selected (1.193 seconds) > 0: jdbc:drill:> select * from dfs.sthota.`/vince/stocks.json` limit 1; > > +------------+------------+---------------+-------------------------+-------------------------+-------------------+---------------+------------------+------------+------------+------------------+-------------------+--------------------+-------------+----------------------+------------+---------------------------------+--------------+-----------------------+-------------+------------+-------------------------------+--------------------+---------------+--------------+------------+------------+---------------+-------------------+------------+------------------+------------+------------+-------------+----------------------+--------------+----------------+-------------------------+------------+------------+-----------------------------------+------------------+------------+------------+-------------+-------------+--------------------+----------------------+---------------------------+------------+------------+-----------------+--------------------+------------+------------+--------------+-------------+-------------------------+------------------------------+-------------------+------------------------------+---------------------+------------------+----------------------------+--------------------+----------------+----------------+----------------------+------------------------------+ > | _id | Ticker | Profit Margin | Institutional Ownership | EPS > growth past 5 years | Total Debt/Equity | Current Ratio | Return on Assets > | Sector | P/S | Change from Open | Performance (YTD) | > Performance (Week) | Quick Ratio | Insider Transactions | P/B | EPS > growth quarter over quarter | Payout Ratio | Performance (Quarter) | > Forward P/E | P/E | 200-Day Simple Moving Average | Shares > Outstanding | Earnings Date | 52-Week High | P/Cash | Change | > Analyst Recom | Volatility (Week) | Country | Return on Equity | 50-Day > Low | Price | 50-Day High | Return on Investment | Shares Float | > Dividend Yield | EPS growth next 5 years | Industry | Beta | Sales > growth quarter over quarter | Operating Margin | EPS (ttm) | PEG | > Float Short | 52-Week Low | Average True Range | EPS growth next year | > Sales growth past 5 years | Company | Gap | Relative Volume | > Volatility (Month) | Market Cap | Volume | Gross Margin | Short Ratio | > Performance (Half Year) | Relative Strength Index (14) | Insider Ownership > | 20-Day Simple Moving Average | Performance (Month) | P/Free Cash Flow | > Institutional Transactions | Performance (Year) | LT Debt/Equity | Average > Volume | EPS growth this year | 50-Day Simple Moving Average | > > +------------+------------+---------------+-------------------------+-------------------------+-------------------+---------------+------------------+------------+------------+------------------+-------------------+--------------------+-------------+----------------------+------------+---------------------------------+--------------+-----------------------+-------------+------------+-------------------------------+--------------------+---------------+--------------+------------+------------+---------------+-------------------+------------+------------------+------------+------------+-------------+----------------------+--------------+----------------+-------------------------+------------+------------+-----------------------------------+------------------+------------+------------+-------------+-------------+--------------------+----------------------+---------------------------+------------+------------+-----------------+--------------------+------------+------------+--------------+-------------+-------------------------+------------------------------+-------------------+------------------------------+---------------------+------------------+----------------------------+--------------------+----------------+----------------+----------------------+------------------------------+ > | {"$oid":"52853800bb1177ca391c17ff"} | A | 0.137 | > 0.847 | 0.158 | 0.5600000000000001 | 3 > | 0.089 | Healthcare | 2.54 | -0.0148 > | 0.2605 | 0.0031 | 2.3 | -0.1352 > | 3.63 | -0.29 | 0.162 | > 0.09279999999999999 | 16.11 | 19.1 | 0.1062 > | 339 | {"$date":"1384464600000"} | -0.0544 | > 7.45 | -0.0148 | 1.6 | 0.0177 | USA | > 0.182 | 0.0728 | 50.44 | -0.0544 | 0.163 > | 330.21 | 0.0094 | 0.0843 | Medical > Laboratories & Research | 1.5 | -0.041 | > 0.187 | 2.68 | 2.27 | 0.008 | 0.4378 | > 0.86 | 0.1194 | 0.048 | > Agilent Technologies Inc. | 0 | 0.79 | 0.0168 > | 17356.8 | 1847978 | 0.512 | 1.03 | 0.1439 > | 46.51 | 0.001 | -0.0172 > | 0.0063 | 19.63 | -0.0074 > | 0.4242 | 0.5600000000000001 | 2569.36 | 0.147 > | -0.0055 | > > +------------+------------+---------------+-------------------------+-------------------------+-------------------+---------------+------------------+------------+------------+------------------+-------------------+--------------------+-------------+----------------------+------------+---------------------------------+--------------+-----------------------+-------------+------------+-------------------------------+--------------------+---------------+--------------+------------+------------+---------------+-------------------+------------+------------------+------------+------------+-------------+----------------------+--------------+----------------+-------------------------+------------+------------+-----------------------------------+------------------+------------+------------+-------------+-------------+--------------------+----------------------+---------------------------+------------+------------+-----------------+--------------------+------------+------------+--------------+-------------+-------------------------+------------------------------+-------------------+------------------------------+---------------------+------------------+----------------------------+--------------------+----------------+----------------+----------------------+------------------------------+ > 1 row selected (0.594 seconds) > 0: jdbc:drill:> > > Thanks > Sudhkar Thota > > > On Mar 30, 2015, at 8:21 AM, Vince Gonzalez <[email protected]> > wrote: > > > Great doc Kristine, thanks! > > > > On Mon, Mar 30, 2015 at 11:06 AM, Kristine Hahn <[email protected]> > wrote: > > > >> When you set all_text_mode to true, Drill reads all data from the JSON > >> files as VARCHAR. After reading the data, use a SELECT statement in > Drill > >> to cast data as follows: > >> > >> * Cast JSON numeric values to [SQL types](/docs/data-types), such as > >> BIGINT, DECIMAL, FLOAT, and INTEGER. > >> * Cast JSON strings to [Drill Date/Time Data Type > >> Formats](/docs/supported-date-time-data-type-formats). > >> > >> For more info, please see preliminary docs at > >> http://drill.apache.org/docs/json-data-model. > >> > >> > >> Kristine Hahn > >> Sr. Technical Writer > >> 415-497-8107 @krishahn > >> > >> > >> On Mon, Mar 30, 2015 at 7:50 AM, Vince Gonzalez < > [email protected]> > >> wrote: > >> > >>> My guess is that setting store.json.all_text_mode = True effectively > >> tells > >>> Drill not to skip inferrering the type of the columns, which perhaps > >>> avoids any complications due to a change in schema that confuses things > >> (in > >>> this case from Float8 to null)? But I am not confident I understand > >> what's > >>> really happening here. Could someone enlighten me? > >>> > >>> I'm using a nightly 0.8.0-SNAPSHOT build, circa March 18. > >>> > >>> The data comes from here: > >>> http://jsonstudio.com/wp-content/uploads/2014/02/stocks.zip > >>> > >>> If I set store.json.all_text_mode = True, the below query runs fine: > >>> > >>> 0: jdbc:drill:zk=localhost:2181> alter system set > >>> `store.json.all_text_mode` = False; > >>> +------------+------------+ > >>> | ok | summary | > >>> +------------+------------+ > >>> | true | store.json.all_text_mode updated. | > >>> +------------+------------+ > >>> 1 row selected (0.018 seconds) > >>> 0: jdbc:drill:zk=localhost:2181> select * from > >>> dfs.`/Users/vince/Downloads/stocks.json` limit 5; > >>> *Query failed: Query stopped., You tried to write a Float8 type when > you > >>> are using a ValueWriter of type NullableBigIntWriterImpl. [ > >>> 4df22bc2-a37e-4057-bd5d-c7ec7d70322b on 10.9.104.180:31010 > >>> <http://10.9.104.180:31010> ]* > >>> * (java.lang.IllegalArgumentException) You tried to write a Float8 > type > >>> when you are using a ValueWriter of type NullableBigIntWriterImpl.* > >>> > >>> > org.apache.drill.exec.vector.complex.impl.AbstractFieldWriter.fail():625 > >>> > >>> > >>> > >> > org.apache.drill.exec.vector.complex.impl.AbstractFieldWriter.writeFloat8():205 > >>> > >>> > >>> > >> > org.apache.drill.exec.vector.complex.impl.NullableBigIntWriterImpl.writeFloat8():88 > >>> org.apache.drill.exec.vector.complex.fn.JsonReader.writeData():283 > >>> > >>> > org.apache.drill.exec.vector.complex.fn.JsonReader.writeDataSwitch():208 > >>> > >> org.apache.drill.exec.vector.complex.fn.JsonReader.writeToVector():182 > >>> org.apache.drill.exec.vector.complex.fn.JsonReader.write():156 > >>> org.apache.drill.exec.store.easy.json.JSONRecordReader.next():125 > >>> org.apache.drill.exec.physical.impl.ScanBatch.next():165 > >>> > >>> > >>> > >> > org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next():118 > >>> org.apache.drill.exec.record.AbstractRecordBatch.next():99 > >>> org.apache.drill.exec.record.AbstractRecordBatch.next():89 > >>> > org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():51 > >>> > >>> > >> > org.apache.drill.exec.physical.impl.limit.LimitRecordBatch.innerNext():113 > >>> org.apache.drill.exec.record.AbstractRecordBatch.next():142 > >>> > >>> > >>> > >> > org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next():118 > >>> org.apache.drill.exec.record.AbstractRecordBatch.next():99 > >>> org.apache.drill.exec.record.AbstractRecordBatch.next():89 > >>> > org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():51 > >>> > >>> > >>> > >> > org.apache.drill.exec.physical.impl.svremover.RemovingRecordBatch.innerNext():96 > >>> org.apache.drill.exec.record.AbstractRecordBatch.next():142 > >>> > >>> > >>> > >> > org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next():118 > >>> org.apache.drill.exec.physical.impl.BaseRootExec.next():67 > >>> > >>> > >> > org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext():97 > >>> org.apache.drill.exec.physical.impl.BaseRootExec.next():57 > >>> org.apache.drill.exec.work.fragment.FragmentExecutor.run():121 > >>> org.apache.drill.exec.work.WorkManager$RunnableWrapper.run():303 > >>> .......():0 > >>> > >>> > >>> Error: exception while executing query: Failure while executing query. > >>> (state=,code=0) > >>> > >> > >
