Working with Joda-Time Format
Describes queries on Joda-Time formatted columns and how to include annotations for successful queries.
Drill cannot read timestamp values in a maprdb column if the column is formatted in
Joda-Time. You cannot CAST Joda-Time columns or values to another type unless you annotate the
Joda-Time column with
"$date"
for timestamp values or
"$dateDay"
for date values, as
shown:"order_received_ts" : { "$date": "1992-04-05T02:15:16Z"}
NOTE
The
following set of annotations were used for internal purposes, but they do not provide any
additional SQL benefits in Drill. Instead of using these annotations, you can CAST the data
types.$binary
$numberByte
$decimal
$numberFloat
$numberInt
$interval
$numberLong
$numberShort
$time
Querying Joda-Time Formatted Columns without Annotation
The following examples show you the results of two queries on a JSON database table (t2)
with a Joda-Time formatted column,
order_received_ts
, that does not have
the "$date"
or "$dateDay"
annotation. Table t2 contains
the following data:maprdb mapr:> find /test01/t2
{"_id":"472271675972670","amount":10433.28,"delivery_method":"TRUCK","discount_rate":10.03,"instructions":"DELIVER IN PERSON","notes":"ccording to the foo","order_date":"1993-09-11","order_received_ts":"1992-04-05T02:15:16Z","product_category_id":6,"quantity":6,"ship_date":"1993-09-21","store_id":"8134660","tax_rate":10.06,"type_code":"F"}
1 document(s) found.
The following query filters on
order_received_ts
and casts the value to
the timestamp data
type:apache drill> select _id, order_received_ts from dfs.`/test01/t2` where order_received_ts > cast('1992-04-05 01:15:16' as timestamp);
+--+
| |
+--+
+--+
No rows selected (0.402 seconds)
The following query casts the
order_received_ts
column to timestamp:
apache drill> select _id, cast(order_received_ts as timestamp) ,customer_id, order_date from dfs.`/test01/t2`;
Error: SYSTEM ERROR: DateTimeParseException: Text '1992-04-05T02:15:16Z' could not be parsed, unparsed text found at index 10
Fragment 0:0
Please, refer to logs for more information.
[Error Id: 039a92d4-43d6-4561-80b5-d87a588f5b10 on mycluster:31010] (state=,code=0)
apache drill>
Both queries return unexpected results; however, adding an annotation can resolve the issue.
Adding an Annotation to Joda-Time Formatted Columns
You will need to update the table or recreate the table to add the annotation. You can do this through the maprdb shell or you can update the data in a JSON file and then import the JSON file into a maprdb JSON table. In this example, the annotation is added to a JSON file and then imported into a JSON table.
To add the annotation to the
order_received_ts
column:- Update the JSON file with the annotation:
{ "_id" : "472271675972670", "store_id" : "8134660", "quantity" : 6, "product_category_id" : 6, "amount" : 10433.28, "discount_rate" : 10.03, "tax_rate" : 10.06, "type_code" : "F", "order_date" : {"$dateDay": "1993-09-11"}, "ship_date" : "1993-09-21", "order_received_ts" : {"$date": "1992-04-05T02:15:16Z"}, "instructions" : "DELIVER IN PERSON", "delivery_method" : "TRUCK", "notes" : "ccording to the foo" }
- Put the JSON file in a
mapr
directory:hadoop fs -put t2.json /user/mapr/.
- Create the JSON database
table:
maprcli table create -path /test01/t2 -tabletype json
- Import the JSON file into the JSON
table:
mapr importJSON -src /user/mapr/t2.json -dst /test01/t2 -mapreduce false
Now that the annotation is added, Drill queries against the table (t2) run successfully and
return the expected
results:
apache drill> select _id, order_received_ts from dfs.`/test01/t2` where order_received_ts > cast('1992-04-05 01:15:16' as timestamp);
+-----------------+--------------------------+
| _id | order_received_ts |
+-----------------+--------------------------+
| 472271675972670 | 1992-04-05T02:15:16.000Z |
+-----------------+--------------------------+
1 row selected (0.334 seconds)
apache drill> select _id, order_date from dfs.`/test01/t2` where order_date > cast('1993-08-10' as date);
+-----------------+------------+
| _id | order_date |
+-----------------+------------+
| 472271675972670 | 1993-09-11 |
+-----------------+------------+
1 row selected (0.156 seconds)
apache drill>