{"id":60388,"date":"2016-09-27T19:00:25","date_gmt":"2016-09-27T16:00:25","guid":{"rendered":"https:\/\/www.javacodegeeks.com\/?p=60388"},"modified":"2016-09-25T23:22:00","modified_gmt":"2016-09-25T20:22:00","slug":"guide-getting-started-apache-drill","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2016\/09\/guide-getting-started-apache-drill.html","title":{"rendered":"How to Guide: Getting Started with Apache Drill"},"content":{"rendered":"

Apache Drill<\/a> is an engine that can connect to many different data sources, and provide a SQL interface to them. It’s not just a wanna-be SQL interface that trips over at anything complex – it’s a hugely functional one<\/a> including support for many built in functions as well as windowing functions. Whilst it can connect to standard data sources that you’d be able to query with SQL anyway, like Oracle or MySQL, it can also work with flat files such as CSV or JSON, as well as Avro and Parquet formats. It’s this capability to run SQL against files that first piqued my interest in Apache Drill. I’ve been spending a lot of time looking at Big Data architectures and tools, including Big Data Discovery<\/a>. As part of this, and experimenting with data pipeline options<\/a> one of the gaps that I’ve found is the functionality to dig through files in their raw state, before they’ve been brought into something like Hive which would enable their exploration through BDD and other tools.<\/p>\n

In this article I’ll walk through getting started with Apache Drill, and show some of the types of queries that I think are a great example of how useful it can be.<\/p>\n

Getting Started<\/h2>\n

It’s very simple to get going with Apache Drill – just download<\/a> and unpack it, and run. Whilst it can run distributed across machines for performance, it can also run standalone on a laptop.<\/p>\n

To launch it<\/p>\n

cd \/opt\/apache-drill-1.7.0\/\r\nbin\/sqlline -u jdbc:drill:zk=local<\/pre>\n
If you get No current connection<\/code> or com.fasterxml.jackson.databind.JavaType.isReferenceType()Z<\/code> then you have a conflicting JAR problem<\/a> (e.g. I encountered this on Oracle’s BigDataLite VM), and should launch it with a clean environment<\/p>\n
env -i HOME=\"$HOME\" LC_CTYPE=\"${LC_ALL:-${LC_CTYPE:-$LANG}}\" PATH=\"$PATH\" USER=\"$USER\" \/opt\/apache-drill-1.7.0\/bin\/drill-embedded<\/pre>\n
There’s a built in dataset that you can use for testing:<\/p>\n
USE cp;\r\nSELECT employee_id, first_name FROM `employee.json` limit 5;<\/pre>\n
This should return five rows, in a very familiar environment if you’re used to using SQL*Plus and similar tools:<\/p>\n
0: jdbc:drill:zk=local> USE cp;\r\n+-------+---------------------------------+\r\n|  ok   |             summary             |\r\n+-------+---------------------------------+\r\n| true  | Default schema changed to [cp]  |\r\n+-------+---------------------------------+\r\n1 row selected (1.776 seconds)\r\n0: jdbc:drill:zk=local>     SELECT employee_id, first_name FROM `employee.json` limit 5;\r\n+--------------+-------------+\r\n| employee_id  | first_name  |\r\n+--------------+-------------+\r\n| 1            | Sheri       |\r\n| 2            | Derrick     |\r\n| 4            | Michael     |\r\n| 5            | Maya        |\r\n| 6            | Roberta     |\r\n+--------------+-------------+\r\n5 rows selected (3.624 seconds)<\/pre>\n
So far, so SQL, so relational – so familiar, really. Where Apache Drill starts to deviate from the obvious is its use of storage handlers. In the above query cp is the ‘database’ that we’re running our query against, but this is in fact a “classpath” (hence “cp”) storage handler that’s defined by default. Within a ‘database’ there are ‘schemas’ which are sub-configurations of the storage handler. We’ll have a look at viewing and defining these later on. For now, it’s useful to know that you can also list out the available databases:<\/p>\n
0: jdbc:drill:zk=local> show databases;\r\n+---------------------+\r\n|     SCHEMA_NAME     |\r\n+---------------------+\r\n| INFORMATION_SCHEMA  |\r\n| cp.default          |\r\n| dfs.default         |\r\n| dfs.root            |\r\n| dfs.tmp             |\r\n| sys                 |\r\n+---------------------+<\/pre>\n
Note databases<\/code> command is a synonym for schemas<\/code>; it’s the <database>.<schema><\/code> that’s returned for both. In Apache Drill the backtick is used to enclose identifiers (such as schema names, column names, and so on), and it’s quite particular about it. For example, this is valid:<\/p>\n
0: jdbc:drill:zk=local> USE `cp.default`;\r\n+-------+-----------------------------------------+\r\n|  ok   |                 summary                 |\r\n+-------+-----------------------------------------+\r\n| true  | Default schema changed to [cp.default]  |\r\n+-------+-----------------------------------------+\r\n1 row selected (0.171 seconds)<\/pre>\n
whilst this isn’t:<\/p>\n
0: jdbc:drill:zk=local> USE cp.default;\r\nError: PARSE ERROR: Encountered \". default\" at line 1, column 7.\r\nWas expecting one of:\r\n<EOF>\r\n\".\" <IDENTIFIER> ...\r\n\".\" <QUOTED_IDENTIFIER> ...\r\n\".\" <BACK_QUOTED_IDENTIFIER> ...\r\n\".\" <BRACKET_QUOTED_IDENTIFIER> ...\r\n\".\" <UNICODE_QUOTED_IDENTIFIER> ...\r\n\".\" \"*\" ...\r\n\r\nSQL Query USE cp.default<\/pre>\n
This is because default<\/code> is a reserved word, and hence must be quoted. Hence, you can also use<\/p>\n
0: jdbc:drill:zk=local> use cp.`default`;<\/code><\/p>\n
but not<\/p>\n
0: jdbc:drill:zk=local> use `cp`.default;<\/code><\/p>\n
Querying JSON data<\/h2>\n
On the Apache Drill website there’s some useful tutorials<\/a>, including one using data provided by Yelp<\/a>. This was the dataset that originally got me looking at Drill, since I was using it as an input to Big Data Discovery<\/a> (BDD) but struggling on two counts. First up was how best to define a suitable Hive table over it in order to ingest it to BDD. Following from this was trying to understand what value there might be in the data which would drive how long to spend perfecting the way in which I exposed the data in Hive. The examples below show the kind of complications that complex JSON can introduce when queried in a tabular fashion.<\/p>\n
First up, querying a JSON file, with the schema inferred automagically. Pretty cool.<\/p>\n
0: jdbc:drill:zk=local> select * from `\/user\/oracle\/incoming\/yelp\/tip_json\/yelp_academic_dataset_tip.json` limit 5;\r\n+---------+------+-------------+-------+------+------+\r\n| user_id | text | business_id | likes | date | type |\r\n+---------+------+-------------+-------+------+------+\r\n| -6rEfobYjMxpUWLNxszaxQ | Don't waste your time. | cE27W9VPgO88Qxe4ol6y_g | 0 | 2013-04-18 | tip |\r\n| EZ0r9dKKtEGVx2CdnowPCw | Your GPS will not allow you to find this place. Put Rankin police department in instead. They are directly across the street. | mVHrayjG3uZ_RLHkLj-AMg | 1 | 2013-01-06 | tip |\r\n| xb6zEQCw9I-Gl0g06e1KsQ | Great drink specials! | KayYbHCt-RkbGcPdGOThNg | 0 | 2013-12-03 | tip |\r\n| QawZN4PSW7ng_9SP7pjsVQ | Friendly staff, good food, great beer selection, and relaxing atmosphere | KayYbHCt-RkbGcPdGOThNg | 0 | 2015-07-08 | tip |\r\n| MLQre1nvUtW-RqMTc4iC9A | Beautiful restoration. | 1_lU0-eSWJCRvNGk78Zh9Q | 0 | 2015-10-25 | tip |\r\n+---------+------+-------------+-------+------+------+\r\n5 rows selected (2.341 seconds)<\/pre>\n
We can use standard SQL aggregations such as COUNT<\/code>:<\/p>\n
0: jdbc:drill:zk=local> select count(*) from `\/user\/oracle\/incoming\/yelp\/tip_json\/yelp_academic_dataset_tip.json`;\r\n+---------+\r\n| EXPR$0  |\r\n+---------+\r\n| 591864  |\r\n+---------+\r\n1 row selected (4.495 seconds)<\/pre>\n
as well as GROUP BY<\/code> operation:<\/p>\n
0: jdbc:drill:zk=local> select `date`,count(*) as tip_count from `\/user\/oracle\/incoming\/yelp\/tip_json\/yelp_academic_dataset_tip.json` group by `date` order by 2 desc limit 5;\r\n+-------------+------------+\r\n|    date     | tip_count  |\r\n+-------------+------------+\r\n| 2012-07-21  | 719        |\r\n| 2012-05-19  | 718        |\r\n| 2012-08-04  | 699        |\r\n| 2012-06-23  | 690        |\r\n| 2012-07-28  | 682        |\r\n+-------------+------------+\r\n5 rows selected (7.111 seconds)<\/pre>\n
Digging into the data a bit, we can see that it’s not entirely flat – note, for example, the hours<\/code> column, which is a nested JSON object:<\/p>\n
0: jdbc:drill:zk=local> select full_address,city,hours from `\/user\/oracle\/incoming\/yelp\/business_json` b limit 5;\r\n+--------------+------+-------+\r\n| full_address | city | hours |\r\n+--------------+------+-------+\r\n| 4734 Lebanon Church Rd\r\nDravosburg, PA 15034 | Dravosburg | {\"Friday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Tuesday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Thursday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Wednesday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Monday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Sunday\":{},\"Saturday\":{}} |\r\n| 202 McClure St\r\nDravosburg, PA 15034 | Dravosburg | {\"Friday\":{},\"Tuesday\":{},\"Thursday\":{},\"Wednesday\":{},\"Monday\":{},\"Sunday\":{},\"Saturday\":{}} |\r\n| 1 Ravine St\r\nDravosburg, PA 15034 | Dravosburg | {\"Friday\":{},\"Tuesday\":{},\"Thursday\":{},\"Wednesday\":{},\"Monday\":{},\"Sunday\":{},\"Saturday\":{}} |\r\n| 1530 Hamilton Rd\r\nBethel Park, PA 15234 | Bethel Park | {\"Friday\":{},\"Tuesday\":{},\"Thursday\":{},\"Wednesday\":{},\"Monday\":{},\"Sunday\":{},\"Saturday\":{}} |\r\n| 301 South Hills Village\r\nPittsburgh, PA 15241 | Pittsburgh | {\"Friday\":{\"close\":\"17:00\",\"open\":\"10:00\"},\"Tuesday\":{\"close\":\"21:00\",\"open\":\"10:00\"},\"Thursday\":{\"close\":\"17:00\",\"open\":\"10:00\"},\"Wednesday\":{\"close\":\"21:00\",\"open\":\"10:00\"},\"Monday\":{\"close\":\"21:00\",\"open\":\"10:00\"},\"Sunday\":{\"close\":\"18:00\",\"open\":\"11:00\"},\"Saturday\":{\"close\":\"21:00\",\"open\":\"10:00\"}} |\r\n+--------------+------+-------+\r\n5 rows selected (0.721 seconds)\r\n0: jdbc:drill:zk=local><\/pre>\n
With Apache Drill we can simply use dot notation to access nested values. It’s necessary to alias the table (b<\/code> in this example) when you’re doing this:<\/p>\n
0: jdbc:drill:zk=local> select b.hours from `\/user\/oracle\/incoming\/yelp\/business_json` b limit 1;\r\n+-------+\r\n| hours |\r\n+-------+\r\n| {\"Friday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Tuesday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Thursday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Wednesday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Monday\":{\"close\":\"21:00\",\"open\":\"11:00\"},\"Sunday\":{},\"Saturday\":{}} |\r\n+-------+<\/pre>\n
Nested objects can themselves be nested – not a problem with Apache Drill, we just chain the dot notation further:<\/p>\n
0: jdbc:drill:zk=local> select b.hours.Friday from `\/user\/oracle\/incoming\/yelp\/business_json` b limit 1;\r\n+-----------------------------------+\r\n|              EXPR$0               |\r\n+-----------------------------------+\r\n| {\"close\":\"21:00\",\"open\":\"11:00\"}  |\r\n+-----------------------------------+\r\n1 row selected (0.238 seconds)<\/pre>\n
Note the use of backtick (`<\/code>) to quote the reserved open<\/code> and close<\/code> keywords:<\/p>\n
0: jdbc:drill:zk=local> select b.hours.Friday.`open`,b.hours.Friday.`close` from `\/user\/oracle\/incoming\/yelp\/business_json` b limit 1;\r\n+---------+---------+\r\n| EXPR$0  | EXPR$1  |\r\n+---------+---------+\r\n| 11:00   | 21:00   |\r\n+---------+---------+\r\n1 row selected (0.58 seconds)<\/pre>\n
Nested columns are proper objects in their own right in the query, and can be used as predicates too:<\/p>\n
0: jdbc:drill:zk=local> select b.name,b.full_address,b.hours.Friday.`open` from `\/user\/oracle\/incoming\/yelp\/business_json` b where b.hours.Friday.`open` = '11:00' limit 5;\r\n+------------------------+------------------------------------------------+---------+\r\n|          name          |                  full_address                  | EXPR$2  |\r\n+------------------------+------------------------------------------------+---------+\r\n| Mr Hoagie              | 4734 Lebanon Church Rd\r\nDravosburg, PA 15034    | 11:00   |\r\n| Alexion's Bar & Grill  | 141 Hawthorne St\r\nGreentree\r\nCarnegie, PA 15106  | 11:00   |\r\n| Rocky's Lounge         | 1201 Washington Ave\r\nCarnegie, PA 15106         | 11:00   |\r\n| Papa J's               | 200 E Main St\r\nCarnegie\r\nCarnegie, PA 15106      | 11:00   |\r\n| Italian Village Pizza  | 2615 Main St\r\nHomestead, PA 15120               | 11:00   |\r\n+------------------------+------------------------------------------------+---------+\r\n5 rows selected (0.404 seconds)<\/pre>\n
You’ll notice in the above output that the full_address<\/code> field has line breaks in — we can just use a SQL Function<\/a> to replace line breaks with commas:<\/p>\n
0: jdbc:drill:zk=local> select b.name,regexp_replace(b.full_address,'\\n',','),b.hours.Friday.`open` from `\/user\/oracle\/incoming\/yelp\/business_json` b where b.hours.Friday.`open` = '11:00' limit 5;\r\n+------------------------+------------------------------------------------+---------+\r\n|          name          |                     EXPR$1                     | EXPR$2  |\r\n+------------------------+------------------------------------------------+---------+\r\n| Mr Hoagie              | 4734 Lebanon Church Rd,Dravosburg, PA 15034    | 11:00   |\r\n| Alexion's Bar & Grill  | 141 Hawthorne St,Greentree,Carnegie, PA 15106  | 11:00   |\r\n| Rocky's Lounge         | 1201 Washington Ave,Carnegie, PA 15106         | 11:00   |\r\n| Papa J's               | 200 E Main St,Carnegie,Carnegie, PA 15106      | 11:00   |\r\n| Italian Village Pizza  | 2615 Main St,Homestead, PA 15120               | 11:00   |\r\n+------------------------+------------------------------------------------+---------+\r\n5 rows selected (1.346 seconds)<\/pre>\n
Query Federation<\/h2>\n
So Apache Drill enables you to run SQL queries against data in a multitude of formats and locations, which is rather useful in itself. But even better than that, it lets you federate these sources in a single query. Here’s an example of joining between data in HDFS and Oracle:<\/p>\n
0: jdbc:drill:zk=local> select X.text,\r\n. . . . . . . . . . . > Y.NAME\r\n. . . . . . . . . . . > from hdfs.`\/user\/oracle\/incoming\/yelp\/tip_json\/yelp_academic_dataset_tip.json` X\r\n. . . . . . . . . . . > inner join ora.MOVIEDEMO.YELP_BUSINESS Y\r\n. . . . . . . . . . . > on X.business_id = Y.BUSINESS_ID\r\n. . . . . . . . . . . > where Y.NAME = 'Chick-fil-A'\r\n. . . . . . . . . . . > limit 5;\r\n+--------------------------------------------------------------------+--------------+\r\n|                                text                                |     NAME     |\r\n+--------------------------------------------------------------------+--------------+\r\n| It's daddy daughter date night here and they go ALL OUT!           | Chick-fil-A  |\r\n| Chicken minis!  The best part of waking up Saturday mornings.  :)  | Chick-fil-A  |\r\n| Nice folks as always unlike those ghetto joints                    | Chick-fil-A  |\r\n| Great clean and delicious chicken sandwiches!                      | Chick-fil-A  |\r\n| Spicy Chicken with lettuce, tomato, and pepperjack cheese FTW!     | Chick-fil-A  |\r\n+--------------------------------------------------------------------+--------------+\r\n5 rows selected (3.234 seconds)<\/pre>\n
You can define a view over this:<\/p>\n
0: jdbc:drill:zk=local> create or replace view dfs.tmp.yelp_tips as select X.text as tip_text, Y.NAME as business_name from hdfs.`\/user\/oracle\/incoming\/yelp\/tip_json\/yelp_academic_dataset_tip.json` X inner join ora.MOVIEDEMO.YELP_BUSINESS Y on X.business_id = Y.BUSINESS_ID ;\r\n+-------+-------------------------------------------------------------+\r\n|  ok   |                           summary                           |\r\n+-------+-------------------------------------------------------------+\r\n| true  | View 'yelp_tips' replaced successfully in 'dfs.tmp' schema  |\r\n+-------+-------------------------------------------------------------+\r\n1 row selected (0.574 seconds)\r\n0: jdbc:drill:zk=local> describe dfs.tmp.yelp_tips;\r\n+----------------+--------------------+--------------+\r\n|  COLUMN_NAME   |     DATA_TYPE      | IS_NULLABLE  |\r\n+----------------+--------------------+--------------+\r\n| tip_text       | ANY                | YES          |\r\n| business_name  | CHARACTER VARYING  | YES          |\r\n+----------------+--------------------+--------------+\r\n2 rows selected (0.756 seconds)<\/pre>\n
and then query it as any regular object:<\/p>\n
0: jdbc:drill:zk=local> select tip_text,business_name from dfs.tmp.yelp_tips where business_name like '%Grill' limit 5;\r\n+------+------+\r\n| text | NAME |\r\n+------+------+\r\n| Great drink specials! | Alexion's Bar & Grill |\r\n| Friendly staff, good food, great beer selection, and relaxing atmosphere | Alexion's Bar & Grill |\r\n| Pretty quiet here... | Uno Pizzeria & Grill |\r\n| I recommend this location for quick lunches. 10 min or less lunch menu. Soup bar ( all you can eat)  the broccoli cheddar soup is delicious. | Uno Pizzeria & Grill |\r\n| Instead of pizza, come here for dessert. The deep dish sundae is really good. | Uno Pizzeria & Grill |\r\n+------+------+\r\n5 rows selected (3.272 seconds)<\/pre>\n
Querying Twitter JSON data<\/h2>\n
Here’s an example of using Drill to query a local file holding some Twitter data. You can download the file here<\/a> if you want to try querying it yourself.<\/p>\n
To start with I switched to using the dfs<\/code> storage plugin:<\/p>\n
0: jdbc:drill:zk=local> use dfs;\r\n+-------+----------------------------------+\r\n|  ok   |             summary              |\r\n+-------+----------------------------------+\r\n| true  | Default schema changed to [dfs]  |\r\n+-------+----------------------------------+<\/pre>\n
And then tried a select against the file. Note the limit 5<\/code> clause – very useful when you’re just examining the structure of a file.<\/p>\n
0: jdbc:drill:zk=local> select * from `\/user\/oracle\/incoming\/twitter\/geo_tweets.json` limit 5;\r\nError: DATA_READ ERROR: Error parsing JSON - Unexpected end-of-input within\/between OBJECT entries\r\n\r\nFile  \/user\/oracle\/incoming\/twitter\/geo_tweets.json\r\nRecord  2819\r\nColumn  3503\r\nFragment 0:0<\/pre>\n
An error? That’s not supposed to happen. I’ve got a JSON file, right? It turns out the JSON file is one complete JSON object per line. Except that it’s not on the last record. Note the record count given in the error above – 2819:<\/p>\n
[oracle@bigdatalite ~]$ wc -l geo_tweets.json\r\n2818 geo_tweets.json<\/pre>\n
So the file only has 2818 complete lines. Hmmm. Let’s take a look at that record, using a head\/tail bash combo :<\/p>\n
[oracle@bigdatalite ~]$ head -n 2819 geo_tweets.json |tail -n1\r\n{\"created_at\":\"Sun Jul 24 21:00:44 +0000 2016\",\"id\":757319630432067584,\"id_str\":\"757319630432067584\",\"text\":\"And now @HillaryClinton hires @DWStweets: Honorary Campaign Manager across the USA #corruption #hillarysamerica  https:\/\/t.co\/8jAGUu6w2f\",\"source\":\"TweetCaster for iOS\",\"truncated\":false,\"in_reply_to_status_id\":null,\"in_reply_to_status_id_str\":null,\"in_reply_to_user_id\":null,\"in_reply_to_user_id_str\":null,\"in_reply_to_screen_name\":null,\"user\":{\"id\":2170786369,\"id_str\":\"2170786369\",\"name\":\"Patricia Weber\",\"screen_name\":\"InnieBabyBoomer\",\"location\":\"Williamsburg, VA\",\"url\":\"http:\/\/lovesrantsandraves.blogspot.com\/\",\"description\":\"Baby Boomer, Swing Voter, Conservative, Spiritual, #Introvert, Wife, Grandma, Italian, \u2665 Books, Cars, Ferrari, F1 Race\u2665  #tcot\",\"protected\":false,\"verified\":false,\"followers_count\":861,\"friends_count\":918,\"listed_count\":22,\"favourites_count\":17,\"statuses_count\":2363,\"created_at\":\"Sat Nov 02 19:13:06 +0000 2013\",\"utc_offset\":null,\"time_zone\":null,\"geo_enabled\":true,\"lang\":\"en\",\"contributors_enabled\":false,\"is_translator\":false,\"profile_background_color\":\"C0DEED\",\"profile_background_image_url\":\"http:\/\/pbs.twimg.com\/profile_background_images\/378800000107659131\/3589f<\/pre>\n
That’s the complete data in the file – so Drill is right – the JSON is corrupted. If we drop that last record and create a new file (geo_tweets.fixed.json<\/code>)<\/p>\n
and query it again, we get something!<\/p>\n
0: jdbc:drill:zk=local>  select text from `\/users\/rmoff\/data\/geo_tweets.fixed.json` limit 5;\r\n+------+\r\n| text |\r\n+------+\r\n| Vancouver trends now: Trump, Evander Kane, Munich, 2016HCC and dcc16. https:\/\/t.co\/joI9GMfRim |\r\n| We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs |\r\n| Donald Trump accepted the Republican nomination last night. Isis claimed responsibility. |\r\n| Obama: \"We must stand together and stop terrorism\"\r\nTrump: \"We don't want these people in our country\"\r\n\ufffd |\r\n| Someone built a wall around Trump's star on the Hollywood Walk of Fame. #lol #nowthatsfunny @\u2026 https:\/\/t.co\/qHWuJXnzbw |\r\n+------+\r\n5 rows selected (0.246 seconds)<\/pre>\n
text<\/code> here being one of the json fields. I could do a select *<\/code> but it’s not so intelligable:
 
<\/div> <\/div><\/p>\n
0: jdbc:drill:zk=local>  select * from `\/users\/rmoff\/data\/geo_tweets.fixed.json` limit 5;\r\n+------------+----+--------+------+--------+-----------+------+-----+-------------+-------+-----------------+---------------+----------------+----------+-----------+-----------+--------------------+--------------+------+--------------+----------+------------+-----------+------------------+----------------------+--------------------+-------------------+-----------------------+---------------------+-----------------+------------+---------------+---------------+------------+-----------+--------------------------------+-----------+----------+----------------+-------------------+---------------------------------+-----------------------+---------------------------+---------------------+-------------------------+-------------------------+------------------+-----------------------+------------------+----------------------+---------------+\r\n| created_at | id | id_str | text | source | truncated | user | geo | coordinates | place | is_quote_status | retweet_count | favorite_count | entities | favorited | retweeted | possibly_sensitive | filter_level | lang | timestamp_ms | @version | @timestamp | user_name | user_screen_name | user_followers_count | user_friends_count | user_listed_count | user_favourites_count | user_statuses_count | user_created_at | place_name | place_country | hashtags_list | urls_array | urls_list | user_mentions_screen_name_list | longitude | latitude | hashtags_array | extended_entities | user_mentions_screen_name_array | in_reply_to_status_id | in_reply_to_status_id_str | in_reply_to_user_id | in_reply_to_user_id_str | in_reply_to_screen_name | retweeted_status | retweeted_screen_name | quoted_status_id | quoted_status_id_str | quoted_status |\r\n+------------+----+--------+------+--------+-----------+------+-----+-------------+-------+-----------------+---------------+----------------+----------+-----------+-----------+--------------------+--------------+------+--------------+----------+------------+-----------+------------------+----------------------+--------------------+-------------------+-----------------------+---------------------+-----------------+------------+---------------+---------------+------------+-----------+--------------------------------+-----------+----------+----------------+-------------------+---------------------------------+-----------------------+---------------------------+---------------------+-------------------------+-------------------------+------------------+-----------------------+------------------+----------------------+---------------+\r\n| Fri Jul 22 19:37:11 +0000 2016 | 756573827589545984 | 756573827589545984 | Vancouver trends now: Trump, Evander Kane, Munich, 2016HCC and dcc16. https:\/\/t.co\/joI9GMfRim | dlvr.it | false | {\"id\":67898674,\"id_str\":\"67898674\",\"name\":\"Vancouver Press\",\"screen_name\":\"Vancouver_CP\",\"location\":\"Vancouver, BC\",\"url\":\"http:\/\/vancouver.cityandpress.com\/\",\"description\":\"Latest news from Vancouver. Updates are frequent.\",\"protected\":false,\"verified\":false,\"followers_count\":807,\"friends_count\":13,\"listed_count\":94,\"favourites_count\":1,\"statuses_count\":131010,\"created_at\":\"Sat Aug 22 14:25:37 +0000 2009\",\"utc_offset\":-25200,\"time_zone\":\"Pacific Time (US & Canada)\",\"geo_enabled\":true,\"lang\":\"en\",\"contributors_enabled\":false,\"is_translator\":false,\"profile_background_color\":\"FFFFFF\",\"profile_background_image_url\":\"http:\/\/abs.twimg.com\/images\/themes\/theme1\/bg.png\",\"profile_background_image_url_https\":\"https:\/\/abs.twimg.com\/images\/themes\/theme1\/bg.png\",\"profile_background_tile\":false,\"profile_link_color\":\"8A1C3B\",\"profile_sidebar_border_color\":\"FFFFFF\",\"profile_sidebar_fill_color\":\"FFFFFF\",\"profile_text_color\":\"2A2C31\",\"profile_use_background_image\":false,\"profile_image_url\":\"http:\/\/pbs.twimg.com\/profile_images\/515841109553983490\/_t0QWPco_normal.png\",\"profile_image_url_https\":\"https:\/\/pbs.twimg.com\/profile_images\/515841109553983490\/_t0QWPco_normal.png\",\"profile_banner_url\":\"https:\/\/pbs.twimg.com\/profile_banners\/67898674\/1411821103\",\"default_profile\":false,\"default_profile_image\":false} | {\"type\":\"Point\",\"coordinates\":[49.2814375,-123.12109067]} | {\"type\":\"Point\",\"coordinates\":[-123.12109067,49.2814375]} | {\"id\":\"1e5cb4d0509db554\",\"url\":\"https:\/\/api.twitter.com\/1.1\/geo\/id\/1e5cb4d0509db554.json\",\"place_type\":\"city\",\"name\":\"Vancouver\",\"full_name\":\"Vancouver, British Columbia\",\"country_code\":\"CA\",\"country\":\"Canada\",\"bounding_box\":{\"type\":\"Polygon\",\"coordinates\":[[[-123.224215,49.19854],[-123.224215,49.316738],[-123.022947,49.316738],[-123.022947,49.19854]]]},\"attributes\":{}} | false | 0 | 0 | {\"urls\":[{\"url\":\"https:\/\/t.co\/joI9GMfRim\",\"expanded_url\":\"http:\/\/toplocalnow.com\/ca\/vancouver?section=trends\",\"display_url\":\"toplocalnow.com\/ca\/vancouver?s\u2026\",\"indices\":[70,93]}],\"hashtags\":[],\"user_mentions\":[],\"media\":[],\"symbols\":[]} | false | false | false | low | en | 1469216231616 | 1 | 2016-07-22T19:37:11.000Z | Vancouver Press | Vancouver_CP | 807 | 13 | 94 | 1 | 131010 | Sat Aug 22 14:25:37 +0000 2009 | Vancouver | Canada |  | [\"toplocalnow.com\/ca\/vancouver?s\u2026\"] | toplocalnow.com\/ca\/vancouver?s\u2026 |  | -123.12109067 | 49.2814375 | [] | {\"media\":[]} | [] | null | null | null | null | null | {\"user\":{},\"entities\":{\"user_mentions\":[],\"media\":[],\"hashtags\":[],\"urls\":[]},\"extended_entities\":{\"media\":[]},\"quoted_status\":{\"user\":{},\"entities\":{\"hashtags\":[],\"user_mentions\":[],\"media\":[],\"urls\":[]},\"extended_entities\":{\"media\":[]}}} | null | null | null | {\"user\":{},\"entities\":{\"user_mentions\":[],\"media\":[],\"urls\":[],\"hashtags\":[]},\"extended_entities\":{\"media\":[]},\"place\":{\"bounding_box\":{\"coordinates\":[]},\"attributes\":{}},\"geo\":{\"coordinates\":[]},\"coordinates\":{\"coordinates\":[]}} |<\/pre>\n
Within the twitter data there’s root-level fields, such as text<\/code>, as well as nested ones such as information about the tweeter in the user<\/code> field. As we saw above you reference nested fields using dot notation. Now’s a good time to point out a couple of common mistakes that you may encounter. The first is not quoting reserved words, and is the first thing to check for if you get an error such as Encountered \".\"<\/code>:<\/p>\n
0: jdbc:drill:zk=local>  select user.screen_name,text from `\/users\/rmoff\/data\/geo_tweets.fixed.json` limit 5;\r\nError: PARSE ERROR: Encountered \".\" at line 1, column 12.\r\n[...]<\/pre>\n
Second is declaring the table alias when using dot notation – if you don’t then Apache Drill thinks that the parent column is actually the table name (VALIDATION ERROR: [...] Table 'user' not found<\/code>):<\/p>\n
0: jdbc:drill:zk=local>  select `user`.screen_name,text from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` limit 5;\r\nAug 10, 2016 11:16:45 PM org.apache.calcite.sql.validate.SqlValidatorException \r\nSEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table 'user' not found\r\nAug 10, 2016 11:16:45 PM org.apache.calcite.runtime.CalciteException \r\nSEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 13: Table 'user' not found\r\nError: VALIDATION ERROR: From line 1, column 8 to line 1, column 13: Table 'user' not found\r\n\r\nSQL Query null\r\n\r\n[Error Id: 1427fd23-e180-40be-a751-b6f1f838233a on 192.168.56.1:31010] (state=,code=0)<\/pre>\n
With those mistakes fixed, we can see the user’s screenname:<\/p>\n
0: jdbc:drill:zk=local>  select tweets.`user`.`screen_name` as user_screen_name,text from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets limit 2;\r\n+------------------+------+\r\n| user_screen_name | text |\r\n+------------------+------+\r\n| Vancouver_CP     | Vancouver trends now: Trump, Evander Kane, Munich, 2016HCC and dcc16. https:\/\/t.co\/joI9GMfRim |\r\n| tmj_TUC_skltrd   | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs |\r\n+------------------+------+\r\n2 rows selected (0.256 seconds)\r\n0: jdbc:drill:zk=local><\/pre>\n
As well as nested objects, JSON supports arrays. An example of this in twitter data is hashtags, or URLs, both of which there can be zero, one, or many of in a given tweet.<\/p>\n
0: jdbc:drill:zk=local>  select tweets.entities.hashtags from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets limit 5;\r\n+--------+\r\n| EXPR$0 |\r\n+--------+\r\n| [] |\r\n| [{\"text\":\"hiring\",\"indices\":[6,13]},{\"text\":\"Job\",\"indices\":[98,102]},{\"text\":\"SkilledTrade\",\"indices\":[103,116]},{\"text\":\"Tucson\",\"indices\":[117,124]},{\"text\":\"Jobs\",\"indices\":[129,134]}] |\r\n| [] |\r\n| [] |\r\n| [{\"text\":\"lol\",\"indices\":[72,76]},{\"text\":\"nowthatsfunny\",\"indices\":[77,91]}] |\r\n+--------+\r\n5 rows selected (0.286 seconds)<\/pre>\n
Using the FLATTEN<\/code> function each array entry becomes a new row, thus:<\/p>\n
0: jdbc:drill:zk=local>  select flatten(tweets.entities.hashtags) from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets limit 5;\r\n+----------------------------------------------+\r\n|                    EXPR$0                    |\r\n+----------------------------------------------+\r\n| {\"text\":\"hiring\",\"indices\":[6,13]}           |\r\n| {\"text\":\"Job\",\"indices\":[98,102]}            |\r\n| {\"text\":\"SkilledTrade\",\"indices\":[103,116]}  |\r\n| {\"text\":\"Tucson\",\"indices\":[117,124]}        |\r\n| {\"text\":\"Jobs\",\"indices\":[129,134]}          |\r\n+----------------------------------------------+\r\n5 rows selected (0.139 seconds)<\/pre>\n
Note that the limit 5<\/code> clause is showing only the first five array instances, which is actually just hashtags from the first tweet in the above list.<\/p>\n
To access the text of the hashtag we use a subquery and the dot notation to access the text<\/code> field:<\/p>\n
0: jdbc:drill:zk=local> select ent_hashtags.hashtags.text from (select flatten(tweets.entities.hashtags) as hashtags from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets) as ent_hashtags limit 5;\r\n+---------------+\r\n|    EXPR$0     |\r\n+---------------+\r\n| hiring        |\r\n| Job           |\r\n| SkilledTrade  |\r\n| Tucson        |\r\n| Jobs          |\r\n+---------------+\r\n5 rows selected (0.168 seconds)<\/pre>\n
This can be made more readable by using Common Table Expressions<\/a> (CTE, also known as subquery factoring) for the same result:<\/p>\n
0: jdbc:drill:zk=local> with ent_hashtags as (select flatten(tweets.entities.hashtags) as hashtags from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets)\r\n. . . . . . . . . . . > select ent_hashtags.hashtags.text from ent_hashtags\r\n. . . . . . . . . . . > limit 5;\r\n+---------------+\r\n|    EXPR$0     |\r\n+---------------+\r\n| hiring        |\r\n| Job           |\r\n| SkilledTrade  |\r\n| Tucson        |\r\n| Jobs          |\r\n+---------------+\r\n5 rows selected (0.253 seconds)<\/pre>\n
Combining the flattened array with existing fields enables us to see things like a list of tweets with their associated hashtags:<\/p>\n
0: jdbc:drill:zk=local> with tmp as ( select flatten(tweets.entities.hashtags) as hashtags,tweets.text,tweets.`user`.screen_name as user_screen_name from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets) select tmp.user_screen_name,tmp.text,tmp.hashtags.text as hashtag from tmp limit 10;\r\n+------------------+------+---------+\r\n| user_screen_name | text | hashtag |\r\n+------------------+------+---------+\r\n| tmj_TUC_skltrd | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs | hiring |\r\n| tmj_TUC_skltrd | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs | Job |\r\n| tmj_TUC_skltrd | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs | SkilledTrade |\r\n| tmj_TUC_skltrd | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs | Tucson |\r\n| tmj_TUC_skltrd | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs | Jobs |\r\n| johnmayberry | Someone built a wall around Trump's star on the Hollywood Walk of Fame. #lol #nowthatsfunny @\u2026 https:\/\/t.co\/qHWuJXnzbw | lol |\r\n| johnmayberry | Someone built a wall around Trump's star on the Hollywood Walk of Fame. #lol #nowthatsfunny @\u2026 https:\/\/t.co\/qHWuJXnzbw | nowthatsfunny |\r\n| greensboro_nc | #WinstonSalem Time and place announced for Donald Trump's visit to\u2026 https:\/\/t.co\/6OVl7crshw #ws @winston_salem_ https:\/\/t.co\/l5h220otj4 | WinstonSalem |\r\n| greensboro_nc | #WinstonSalem Time and place announced for Donald Trump's visit to\u2026 https:\/\/t.co\/6OVl7crshw #ws @winston_salem_ https:\/\/t.co\/l5h220otj4 | ws |\r\n| trendinaliaSG | 6. Hit The Stage\r\n7. TTTT\r\n8. Demi Lovato\r\n9. Beijing\r\n10. Donald Trump\r\n\r\n2016\/7\/23 03:36 SGT #trndnl https:\/\/t.co\/psP0GzBgZB | trndnl |\r\n+------------------+------+---------+\r\n10 rows selected (0.166 seconds)<\/pre>\n
We can also filter based on hashtag:<\/p>\n
0: jdbc:drill:zk=local> with tmp as ( select flatten(tweets.entities.hashtags) as hashtags,tweets.text,tweets.`user`.screen_name as user_screen_name from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets) select tmp.user_screen_name,tmp.text,tmp.hashtags.text as hashtag from tmp where tmp.hashtags.text = 'Job' limit 5;\r\n+------------------+------+---------+\r\n| user_screen_name | text | hashtag |\r\n+------------------+------+---------+\r\n| tmj_TUC_skltrd | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs | Job |\r\n| tmj_VAL_health | Want to work at Genesis Rehab Services? We're #hiring in #Clinton, MD! Click for details: https:\/\/t.co\/4lt7I4gMZk #Job #Healthcare #Jobs | Job |\r\n| tmj_in_retail | Want to work in #Clinton, IN? View our latest opening: https:\/\/t.co\/UiimnlubYs #Job #Retail #Jobs #Hiring #CareerArc | Job |\r\n| tmj_la_hrta | Want to work at SONIC Drive-In? We're #hiring in #Clinton, LA! Click for details: https:\/\/t.co\/aQ1FrWc7iR #Job #SONIC #Hospitality #Jobs | Job |\r\n| tmj_ia_hrta | We're #hiring! Click to apply: Department Manager - https:\/\/t.co\/SnoKcwwHFk #Job #Hospitality #Clinton, IA #Jobs #CareerArc | Job |\r\n+------------------+------+---------+\r\n5 rows selected (0.207 seconds)<\/pre>\n
as well as summarise hashtag counts:<\/p>\n
0: jdbc:drill:zk=local> with ent_hashtags as (select flatten(tweets.entities.hashtags) as hashtags from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets)\r\n. . . . . . . . . . . > select ent_hashtags.hashtags.text,count(ent_hashtags.hashtags.text) from ent_hashtags\r\n. . . . . . . . . . . > group by ent_hashtags.hashtags.text\r\n. . . . . . . . . . . > order by 2 desc;\r\n+-----------------------------+---------+\r\n|           EXPR$0            | EXPR$1  |\r\n+-----------------------------+---------+\r\n| Trump                       | 365     |\r\n| trndnl                      | 176     |\r\n| job                         | 170     |\r\n| Hiring                      | 127     |\r\n| Clinton                     | 108     |\r\n| Yorkshire                   | 100     |\r\n| CareerArc                   | 100     |\r\n[...]<\/pre>\n
To filter out records that may not have array values (such as hashtags, which not every tweet has) and without with the query may fail, use IS NOT NULL<\/code> against an attribute of first index of the array:<\/p>\n
0: jdbc:drill:zk=local> select tweets.entities.hashtags from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets where tweets.entities.hashtags[0].text is not null limit 5;\r\n+--------+\r\n| EXPR$0 |\r\n+--------+\r\n| [{\"text\":\"hiring\",\"indices\":[6,13]},{\"text\":\"Job\",\"indices\":[98,102]},{\"text\":\"SkilledTrade\",\"indices\":[103,116]},{\"text\":\"Tucson\",\"indices\":[117,124]},{\"text\":\"Jobs\",\"indices\":[129,134]}] |\r\n| [{\"text\":\"lol\",\"indices\":[72,76]},{\"text\":\"nowthatsfunny\",\"indices\":[77,91]}] |\r\n| [{\"text\":\"WinstonSalem\",\"indices\":[0,13]},{\"text\":\"ws\",\"indices\":[92,95]}] |\r\n| [{\"text\":\"trndnl\",\"indices\":[89,96]}] |\r\n| [{\"text\":\"trndnl\",\"indices\":[92,99]}] |\r\n+--------+\r\n5 rows selected (0.187 seconds)<\/pre>\n
If you try and compare the array itself, it doesn’t work:<\/p>\n
 0: jdbc:drill:zk=local> select tweets.entities.hashtags from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets where tweets.entities.hashtags is not null limit 5; Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema. Errors: Error in expression at index -1. Error: Missing function implementation: [isnotnull(MAP-REPEATED)]. Full expression: --UNKNOWN EXPRESSION--.. Fragment 0:0 [Error Id: 99ac12aa-f6b4-4692-b815-8f483da682c4 on 192.168.56.1:31010] (state=,code=0) <\/code><\/p>\n
The above example demonstrates using array indexing, which is an alternative to FLATTEN<\/code> for accessing individual objects in the array if you know they’re going to exist:<\/p>\n
0: jdbc:drill:zk=local> select tweets.entities.hashtags[0].text as first_hashtag,text from dfs.`\/users\/rmoff\/data\/geo_tweets.fixed.json` tweets where tweets.entities.hashtags[0].text is not null limit 5;\r\n+---------------+------+\r\n| first_hashtag | text |\r\n+---------------+------+\r\n| hiring | We're #hiring! Click to apply: Bench Jeweler - SEC Oracle & Wetmore - https:\/\/t.co\/Oe2SHaL0Hh #Job #SkilledTrade #Tucson, AZ #Jobs |\r\n| lol | Someone built a wall around Trump's star on the Hollywood Walk of Fame. #lol #nowthatsfunny @\u2026 https:\/\/t.co\/qHWuJXnzbw |\r\n| WinstonSalem | #WinstonSalem Time and place announced for Donald Trump's visit to\u2026 https:\/\/t.co\/6OVl7crshw #ws @winston_salem_ https:\/\/t.co\/l5h220otj4 |<\/pre>\n
Querying CSV files<\/h2>\n
JSON files are relatively easy to interpret because they have a semi-defined schema within them, including column names. CSV (and character delimited files in general), on the other hand, are a bit more of a ‘wild west’ when it comes to reliably inferring column names. You can configure Apache Drill<\/a> to ignore the first line of a CSV file (on the assumption that it’s a header) if you want to, or to take them as column names. If you don’t do this and query a CSV file that looks like this:<\/p>\n
[oracle@bigdatalite ~]$ head nyc_parking_violations.csv\r\nSummons Number,Plate ID,Registration State,Plate Type,Issue Date,Violation Code,Vehicle Body Type,Vehicle Make,Issuing Agency,Street Code1,Street Code2,Street Code3,Vehicle Expiration Date,Violation Location,Violation Precinct,Issuer Precinct,Issuer Code,Issuer Command,Issuer Squad,Violation Time,Time First Observed,Violation County,Violation In Front Of Or Opposite,House Number,Street Name,Intersecting Street,Date First Observed,Law Section,Sub Division,Violation Legal Code,Days Parking In Effect    ,From Hours In Effect,To Hours In Effect,Vehicle Color,Unregistered Vehicle?,Vehicle Year,Meter Number,Feet From Curb,Violation Post Code,Violation Description,No Standing or Stopping Violation,Hydrant Violation,Double Parking Violation\r\n1360858775,PHW9801,OH,PAS,07\/01\/2015,20,SUBN,HONDA,P,61490,26160,26190,0,0044,44,44,929822,0044,0000,0653P,,BX,O,651,RIVER AVE,,0,408,D,,BBBBBBB,ALL,ALL,,0,0,-,0,,,,,<\/pre>\n
You’ll get two records, each one column wide, as an array:<\/p>\n
0: jdbc:drill:zk=local> select *  from `\/user\/oracle\/incoming\/nyc_parking\/nyc_parking_violations.csv` LIMIT 5;\r\n+---------+\r\n| columns |\r\n+---------+\r\n| [\"Summons Number\",\"Plate ID\",\"Registration State\",\"Plate Type\",\"Issue Date\",\"Violation Code\",\"Vehicle Body Type\",\"Vehicle Make\",\"Issuing Agency\",\"Street Code1\",\"Street Code2\",\"Street Code3\",\"Vehicle Expiration Date\",\"Violation Location\",\"Violation Precinct\",\"Issuer Precinct\",\"Issuer Code\",\"Issuer Command\",\"Issuer Squad\",\"Violation Time\",\"Time First Observed\",\"Violation County\",\"Violation In Front Of Or Opposite\",\"House Number\",\"Street Name\",\"Intersecting Street\",\"Date First Observed\",\"Law Section\",\"Sub Division\",\"Violation Legal Code\",\"Days Parking In Effect    \",\"From Hours In Effect\",\"To Hours In Effect\",\"Vehicle Color\",\"Unregistered Vehicle?\",\"Vehicle Year\",\"Meter Number\",\"Feet From Curb\",\"Violation Post Code\",\"Violation Description\",\"No Standing or Stopping Violation\",\"Hydrant Violation\",\"Double Parking Violation\"] |\r\n| [\"1360858775\",\"PHW9801\",\"OH\",\"PAS\",\"07\/01\/2015\",\"20\",\"SUBN\",\"HONDA\",\"P\",\"61490\",\"26160\",\"26190\",\"0\",\"0044\",\"44\",\"44\",\"929822\",\"0044\",\"0000\",\"0653P\",\"\",\"BX\",\"O\",\"651\",\"RIVER AVE\",\"\",\"0\",\"408\",\"D\",\"\",\"BBBBBBB\",\"ALL\",\"ALL\",\"\",\"0\",\"0\",\"-\",\"0\",\"\",\"\",\"\",\"\",\"\"] |<\/pre>\n
To access the actual columns in the CSV file you need to use columns[x]<\/code> syntax to reference them. Watch out that columns<\/code> is case-sensitive, and the numbering is zero-based:<\/p>\n
0: jdbc:drill:zk=local> select columns[1] as `PlateID`, columns[2] as `RegistrationState` from `\/user\/oracle\/incoming\/nyc_parking\/nyc_parking_violations.csv` limit 5;\r\n+----------+--------------------+\r\n| PlateID  | RegistrationState  |\r\n+----------+--------------------+\r\n| AR877A   | NJ                 |\r\n| 73268ME  | NY                 |\r\n| 2050240  | IN                 |\r\n| 2250017  | IN                 |\r\n| AH524C   | NJ                 |\r\n+----------+--------------------+\r\n5 rows selected (0.247 seconds)<\/pre>\n
To make it easier to work with the data on a repeated basis you can define a view over the data:<\/p>\n
0: jdbc:drill:zk=local> create view dfs.tmp.NYC_Parking_01 as select columns[1] as `PlateID`, columns[2] as `RegistrationState` from `\/user\/oracle\/incoming\/nyc_parking\/nyc_parking_violations.csv`;\r\n+-------+-----------------------------------------------------------------+\r\n|  ok   |                             summary                             |\r\n+-------+-----------------------------------------------------------------+\r\n| true  | View 'NYC_Parking_01' created successfully in 'dfs.tmp' schema  |\r\n+-------+-----------------------------------------------------------------+\r\n1 row selected (0.304 seconds)<\/pre>\n
This is using the dfs<\/code> storage plugin and the tmp<\/code> schema within it, which has the following storage configuration – note that writeable<\/code> is true<\/p>\n
\"tmp\": {\r\n  \"location\": \"\/tmp\",\r\n  \"writable\": true,\r\n  \"defaultInputFormat\": null\r\n}<\/pre>\n
(if you use the wrong database [storage plugin] or schema you’ll get Schema [hdfs] is immutable.<\/code>)<\/p>\n
Query the new view<\/p>\n
0: jdbc:drill:zk=local> select * from dfs.tmp.NYC_Parking_01 limit 5;\r\n+-----------+---------------------+\r\n|  PlateID  |  RegistrationState  |\r\n+-----------+---------------------+\r\n| Plate ID  | Registration State  |\r\n| PHW9801   | OH                  |\r\n| K8010F    | TN                  |\r\n| GFG6211   | NY                  |\r\n| GHL1805   | NY                  |\r\n+-----------+---------------------+\r\n5 rows selected (0.191 seconds)<\/pre>\n
Through the view, or direct against the CSV path, you can also run aggregates:<\/p>\n
0: jdbc:drill:zk=local> select PlateID,count(*) from dfs.tmp.NYC_Parking_01 group by PlateID having count(*) > 1 limit 1;\r\n+----------+---------+\r\n| PlateID  | EXPR$1  |\r\n+----------+---------+\r\n| 2050240  | 4       |\r\n+----------+---------+\r\n1 row selected (15.983 seconds)<\/pre>\n
Although this isn’t rerunnable for the same result – probably because of the limit<\/code> clause<\/p>\n
0: jdbc:drill:zk=local> select PlateID,count(*) from dfs.tmp.NYC_Parking_01 group by PlateID having count(*) > 1 limit 1;\r\n+----------+---------+\r\n| PlateID  | EXPR$1  |\r\n+----------+---------+\r\n| AR877A   | 3       |\r\n+----------+---------+\r\n1 row selected (12.881 seconds)<\/pre>\n
Under the covers the view definition is written to \/tmp<\/code> – you’ll want to move this path if you’re wanting to preserve this data past reboot:<\/p>\n
[oracle@bigdatalite parking]$ cat \/tmp\/NYC_Parking_01.view.drill\r\n{\r\n  \"name\" : \"NYC_Parking_01\",\r\n  \"sql\" : \"SELECT `columns`[1] AS `PlateID`, `columns`[2] AS `RegistrationState`\\nFROM `\/user\/oracle\/incoming\/nyc_parking\/nyc_parking_violations.csv`\",\r\n  \"fields\" : [ {\r\n    \"name\" : \"PlateID\",\r\n    \"type\" : \"ANY\",\r\n    \"isNullable\" : true\r\n  }, {\r\n    \"name\" : \"RegistrationState\",\r\n    \"type\" : \"ANY\",\r\n    \"isNullable\" : true\r\n  } ],\r\n  \"workspaceSchemaPath\" : [ \"hdfs\" ]<\/pre>\n
You can also create an actual table using CTAS (Create Table As Select):<\/p>\n
0: jdbc:drill:zk=local> create table dfs.tmp.parking as select columns[1] as `PlateID`, columns[2] as `RegistrationState` from `\/user\/oracle\/incoming\/nyc_parking\/nyc_parking_violations.csv`;\r\n+-----------+----------------------------+\r\n| Fragment  | Number of records written  |\r\n+-----------+----------------------------+\r\n| 1_1       | 4471875                    |\r\n| 1_0       | 4788421                    |\r\n+-----------+----------------------------+\r\n2 rows selected (42.913 seconds)<\/pre>\n
This is stored on disk (per the dfs<\/code> config) and by default in Parquet format:<\/p>\n
[oracle@bigdatalite parking]$ ls -l \/tmp\/parking\/\r\ntotal 76508\r\n-rw-r--r--. 1 oracle oinstall 40623288 Aug 10 22:53 1_0_0.parquet\r\n-rw-r--r--. 1 oracle oinstall 37717804 Aug 10 22:53 1_1_0.parquet<\/pre>\n
Drill’s Web Interface<\/h2>\n
Drill comes with a web interface which you can access at http:\/\/<\/p>\n
:8047\/ and is useful for<\/p>\n