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)
>>>
>>
