Apache Drill and the lack of support for nested arrays

Apache Drill and the lack of support for nested arrays

Apache Drill is very efficient and fast, till you try to use it with huge chunk of one file (such as a few GB) or if you attempt to query a complex data structure with nested data. Now, this is what I am trying to do right now - attempting to query large segments of data with a dynamic structure and nested schema.

 

I may construct a parquet data source from a nested array, as below,  

 

create table dfs.tmp.camic as ( select camic.geometry.coordinates[0][0] as geo_coordinates from dfs.`/home/pradeeban/programs/apache-drill-1.6.0/camic.json` camic);
 

Here I am giving the indices of the array. 

 

Then I can query the data efficiently. For example,  

select * from dfs.tmp.camic;

 

However, giving the indices wont work as I need, as I dont just need the first element. Rather I need the entire elements - in a large and dynamic array, representing the coordinates of geojson.

 

 

$ create table dfs.tmp.camic as ( select camic.geometry.coordinates[0] as geo_coordinates from dfs.`/home/pradeeban/programs/apache-drill-1.6.0/camic.json` camic);
Error: SYSTEM ERROR: UnsupportedOperationException: Unsupported type LIST

Fragment 0:0

[Error Id: a6d68a6c-50ea-437b-b1db-f1c8ace0e11d on llovizna:31010]

  (java.lang.UnsupportedOperationException) Unsupported type LIST
    org.apache.drill.exec.store.parquet.ParquetRecordWriter.getType():225
    org.apache.drill.exec.store.parquet.ParquetRecordWriter.newSchema():187
    org.apache.drill.exec.store.parquet.ParquetRecordWriter.updateSchema():172
    org.apache.drill.exec.physical.impl.WriterRecordBatch.setupNewSchema():155
    org.apache.drill.exec.physical.impl.WriterRecordBatch.innerNext():103
    org.apache.drill.exec.record.AbstractRecordBatch.next():162
    org.apache.drill.exec.record.AbstractRecordBatch.next():119
    org.apache.drill.exec.record.AbstractRecordBatch.next():109
    org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext():51
    org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext():129
    org.apache.drill.exec.record.AbstractRecordBatch.next():162
    org.apache.drill.exec.physical.impl.BaseRootExec.next():104
    org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext():81
    org.apache.drill.exec.physical.impl.BaseRootExec.next():94
    org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():257
    org.apache.drill.exec.work.fragment.FragmentExecutor$1.run():251
    java.security.AccessController.doPrivileged():-2
    javax.security.auth.Subject.doAs():422
    org.apache.hadoop.security.UserGroupInformation.doAs():1657
    org.apache.drill.exec.work.fragment.FragmentExecutor.run():251
    org.apache.drill.common.SelfCleaningRunnable.run():38
    java.util.concurrent.ThreadPoolExecutor.runWorker():1142
    java.util.concurrent.ThreadPoolExecutor$Worker.run():617
    java.lang.Thread.run():744 (state=,code=0)
 

 

Here, I am trying to query a multi-dimensional array, which is not straight-forward.

(I set the error messages to be verbose using  SET `exec.errors.verbose` = true;
 above).

 

The commonly suggested options to query multi-dimensional arrays are:

1. Using the array indexes in the select query: This is impractical. I do not know how many elements I would have in this geojson - the coordinates. It may be millions or as low as 3.

2. Flatten keyword: I am using Drill on top of Mongo - and finding an interesting case where Drill outperforms certain queries in a distributed execution than just using Mongo. Using Flatten basically kills all the performance benefits I have with Drill otherwise. Flatten is just plain expensive operation for the scale of my data (around 48 GB. But I can split them into a few GB each).

 

This is a known limitation of Drill. However, this significantly reduces its usability, as the proposed workarounds are either impractical or inefficient.

Read full post »

Apple File System revealed at WWDC 2016 focused on encryption and SSD support

Apple File System revealed at WWDC 2016 focused on encryption and SSD support

Apple recently announced the new Apple File System (APFS), replacing the decades-old HFS system.

Image: Josh Miller/CNET

On Monday, Tim Cook and his executive team took to the stage at the 2016 Apple WWDC event to discuss the latest updates to their core operating systems in the keynote address. One of the biggest releases for the enterprise, however, wasnt even announced.
 
As part of the updates with macOS Sierra, a new file system for storage called the Apple File System (APFS) is being released in developer preview. An explanation of the system was first spotted as part of a description for a system frameworks breakout session at WWDC:
 
"The Apple File System (APFS) is the next-generation file system designed to scale from an Apple Watch to a Mac Pro. APFS is optimized for Flash/SSD storage, and engineered with encryption as a primary feature. Learn about APFS benefits versus HFS+ and how to make sure your file system code is compatible."
 
As noted, APFS was created to replace the aging HFS and HFS+ systems, which Apple said were "developed in an era of floppy disks and spinning hard drives, where file sizes were calculated in kilobytes or megabytes." The APFS is built with Flash storage in mind and to handle millions of files that may be larger in size. 
 
Lets take a look at some of the potential improvements that APFS holds over HFS+. For starters, APFS supports 64-bit inode numbers vs. the 32-bit IDs supported by HFS+. APFS also has support sparse files, but HFS+ doesnt.
 
Another big improvement is the timestamp granularity. While HFS+ offers a 1 second timestamp granularity, APFS supports a 1 nanosecond timestamp granularity. This amount of detail would likely be used more for core OS files, and is a massive improvement in terms of file detail. 
 
To protect against crashes, APFS utilizes a copy-on-write metadata scheme to make sure that file systems updates are safe in the event of a crash.
 
With all the recent headlines about Apples war for encryption, its not surprising that APFS supports encryption natively. According to the developer documentation:
 
"On OS X, Full Disk Encryption has been available since OS X 10.7 Lion. On iOS, a version of data protection that encrypts each file individually with its own key has been available since iOS 4, as described in iOS Security Guide. APFS combines both of these features into a unified model that encrypts file system metadata."
 
Although hardware-dependent, APFS encryption supports AES-XTS or AES-CBC. The base storage unit for APFS is called a container, and users can choose one of three encryption models for each volume within a container: 

1. No encryption
2. Single-key encryption
3. Multi-key encryption with per-file keys for file data and a separate key for sensitive metadata

APFS also adds support for both clones and snapshots. A clone is a instant, writable, copy of a file or directory that doesnt take up any extra space for the data. Clones will be useful in versioning and revisioning, depending on how you can diff them.
 
Snapshots, on the other hand, are read-only instances of a file system on a volume. Developer documentation states: "The operating system can use snapshots to make backups work more efficiently, and offer a way to revert changes to a given point in time." This means well likely see snapshots changing or replacing Time Machine backups.
 
A new copy-on-write design using I/O coalescing could help with both performance and reliability, and a new Space Sharing feature where multiple file systems can share space on a physical volume, but with more flexibility than traditional partitioning. 
 
While there are a ton of updates with APFS, there are still some limitations.
 
If you were holding out for a non-case-sensitive system, youre out of luck. APFS filenames are currently case-sensitive only. This may seem like a step backwards, because HFS+ can be configured as non-case-sensitive. But, it is only a developer preview at this point, so keep that in mind. 
 
Additionally, you cannot use an APFS volume as a startup disk, as a Time Machine volume, or as part of a Fusion Drive. FileVault encryption will not work on it, either. APFS formatted volumes wont work on OS X 10.11 Yosemite and earlier.
 
Interested parties can find the developer documentation for APFS here.
 
~ Conner Forrest

Read full post »