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>