Love Drill - Hate Key Has String Token

[John Folkers]

Hello, I downloaded Drill over the weekend, and I love it.


Problem: $ string token in a key.


Question: How can I get Drill to not trip on the $ string token when it sees it inside the keyname?


Error Message
Error: DATA_READ ERROR: Failure while reading ExtendedJSON typed value. Expected a VALUE_NUMBER_INT but received a token of type VALUE_STRING


Solution is to remove $ string token from key
sed -i 's/\$key/key/g' <file>


Once I do the above, Drill can now query it. I have no control over source. I have TBs of this streaming NDJSON I'd love to sort through with Drill. I can do this if I can solve this. Please help.

Thanks,
John

[Charles Givre]

Hi John,
Have you tried enclosing your field names in back ticks? IE

SELECT `$field1`, `$field2`
FROM…

— C

Post by John Folkers
Hello, I downloaded Drill over the weekend, and I love it.
Problem: $ string token in a key.
Question: How can I get Drill to not trip on the $ string token when it sees it inside the keyname?
Error Message
Error: DATA_READ ERROR: Failure while reading ExtendedJSON typed value. Expected a VALUE_NUMBER_INT but received a token of type VALUE_STRING
Solution is to remove $ string token from key
sed -i 's/\$key/key/g' <file>
Once I do the above, Drill can now query it. I have no control over source. I have TBs of this streaming NDJSON I'd love to sort through with Drill. I can do this if I can solve this. Please help.
Thanks,
John

[Ted Dunning]

Can you post a sample file with, say, 5-10 lines?

Is it the file names?

Or the data values that are giving you fits?

Post by John Folkers
Hello, I downloaded Drill over the weekend, and I love it.
Problem: $ string token in a key.
Question: How can I get Drill to not trip on the $ string token when it
sees it inside the keyname?
Error Message
Error: DATA_READ ERROR: Failure while reading ExtendedJSON typed value.
Expected a VALUE_NUMBER_INT but received a token of type VALUE_STRING
Solution is to remove $ string token from key
sed -i 's/\$key/key/g' <file>
Once I do the above, Drill can now query it. I have no control over
source. I have TBs of this streaming NDJSON I'd love to sort through with
Drill. I can do this if I can solve this. Please help.
Thanks,
John

[Paul Rogers]

Hi John,

Tried out Ted's suggestion. Works fine in Drill 1.13. Here is my "test2.json" file:

{ "$f1": "fred", "f$2": "wilma", "f3$": "barney" }


Then ran two queries:

SELECT * FROM `test2.json`;+-------+--------+---------+|  $f1  |  f$2   |   f3$   |+-------+--------+---------+| fred  | wilma  | barney  |+-------+--------+---------+

SELECT `$f1`, `f$2`, `f3$` FROM `test2.json`;
+-------+--------+---------+|  $f1  |  f$2   |   f3$   |+-------+--------+---------+| fred  | wilma  | barney  |+-------+--------+---------+

So, the trick you are looking for is to use the back-tick quote for names. Back-ticks are need to quote names that are SQL reserved words or that contain non-symbol characters. Notice how the queries above also quote the file name to avoid issues.

The error you received, however, suggests that something is amiss with your JSON syntax. As Ted suggested, can you show us a few example lines?

Thanks,
- Paul



On Monday, August 27, 2018, 4:38:07 PM PDT, Ted Dunning <***@gmail.com> wrote:

Can you post a sample file with, say, 5-10 lines?

Is it the file names?

Or the data values that are giving you fits?

Post by John Folkers
Hello, I downloaded Drill over the weekend, and I love it.
Problem: $ string token in a key.
Question: How can I get Drill to not trip on the $ string token when it
sees it inside the keyname?
Error Message
Error: DATA_READ ERROR: Failure while reading ExtendedJSON typed value.
Expected a VALUE_NUMBER_INT but received a token of type VALUE_STRING
Solution is to remove $ string token from key
sed -i 's/\$key/key/g' <file>
Once I do the above, Drill can now query it.  I have no control over
source.  I have TBs of this streaming NDJSON I'd love to sort through with
Drill.  I can do this if I can solve this.  Please help.
Thanks,
John