Details
-
New Feature
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
None
Description
Support querying of metadata in various image formats. This plugin leverages metadata-extractor. This plugin is especially useful when querying on a large number of image files stored in a distributed file system without building metadata repository in advance.
This plugin supports the following file formats.
- JPEG, TIFF, PSD, PNG, BMP, GIF, ICO, PCX, WAV, AVI, WebP, MOV, MP4, EPS
- Camera Raw: ARW (Sony), CRW/CR2 (Canon), NEF (Nikon), ORF (Olympus), RAF (FujiFilm), RW2 (Panasonic), RWL (Leica), SRW (Samsung), X3F (Foveon)
This plugin enables to read the following metadata.
- Exif, IPTC, XMP, JFIF / JFXX, ICC Profiles, Photoshop fields, PNG properties, BMP properties, GIF properties, ICO properties, PCX properties, WAV properties, AVI properties, WebP properties, QuickTime properties, MP4 properties, EPS properties
Since each type of metadata has a different set of fields, the plugin returns a set of commonly-used fields such as the image width, height and bits per pixels for ease of use.
Examples:
Querying on a JPEG file with the property descriptive: true
0: jdbc:drill:zk=local> select FileName, * from dfs.`4349313028_f69ffa0257_o.jpg`;
+----------+----------+--------------+--------+------------+-------------+--------------+----------+-----------+------------+-----------+----------+----------+------------+-----------+------------+-----------------+-----------------+------+------+----------+------------+------------------+-----+---------------+-----------+------+---------+----------+
| FileName | FileSize | FileDateTime | Format | PixelWidth | PixelHeight | BitsPerPixel | DPIWidth | DPIHeight | Orientaion | ColorMode | HasAlpha | Duration | VideoCodec | FrameRate | AudioCodec | AudioSampleSize | AudioSampleRate | JPEG | JFIF | ExifIFD0 | ExifSubIFD | Interoperability | GPS | ExifThumbnail | Photoshop | IPTC | Huffman | FileType |
+----------+----------+--------------+--------+------------+-------------+--------------+----------+-----------+------------+-----------+----------+----------+------------+-----------+------------+-----------------+-----------------+------+------+----------+------------+------------------+-----+---------------+-----------+------+---------+----------+
| 4349313028_f69ffa0257_o.jpg | 257213 bytes | Fri Mar 09 12:09:34 +08:00 2018 | JPEG | 1199 | 800 | 24 | 96 | 96 | Unknown (0) | RGB | false | 00:00:00 | Unknown | 0 | Unknown | 0 | 0 | {"CompressionType":"Baseline","DataPrecision":"8 bits","ImageHeight":"800 pixels","ImageWidth":"1199 pixels","NumberOfComponents":"3","Component1":"Y component: Quantization table 0, Sampling factors 2 horiz/2 vert","Component2":"Cb component: Quantization table 1, Sampling factors 1 horiz/1 vert","Component3":"Cr component: Quantization table 1, Sampling factors 1 horiz/1 vert"} | {"Version":"1.1","ResolutionUnits":"inch","XResolution":"96 dots","YResolution":"96 dots","ThumbnailWidthPixels":"0","ThumbnailHeightPixels":"0"} | {"Software":"Picasa 3.0"} | {"ExifVersion":"2.10","UniqueImageID":"d65e93b836d15a0c5e041e6b7258c76e"} | {"InteroperabilityIndex":"Unknown ( )","InteroperabilityVersion":"1.00"} | {"GPSVersionID":".022","GPSLatitudeRef":"N","GPSLatitude":"47° 32' 15.98\"","GPSLongitudeRef":"W","GPSLongitude":"-122° 2' 6.37\"","GPSAltitudeRef":"Sea level","GPSAltitude":"0 metres"} | {"Compression":"JPEG (old-style)","XResolution":"72 dots per inch","YResolution":"72 dots per inch","ResolutionUnit":"Inch","ThumbnailOffset":"414 bytes","ThumbnailLength":"7213 bytes"} | {} | {"Keywords":"135;2002;issaquah;police car;wa;washington"} | {"NumberOfTables":"4 Huffman tables"} | {"DetectedFileTypeName":"JPEG","DetectedFileTypeLongName":"Joint Photographic Experts Group","DetectedMIMEType":"image/jpeg","ExpectedFileNameExtension":"jpg"} |
+----------+----------+--------------+--------+------------+-------------+--------------+----------+-----------+------------+-----------+----------+----------+------------+-----------+------------+-----------------+-----------------+------+------+----------+------------+------------------+-----+---------------+-----------+------+---------+----------+
1 row selected (0.229 seconds)
Querying on a JPEG file with the property descriptive: false
0: jdbc:drill:zk=local> select FileName, * from dfs.`4349313028_f69ffa0257_o.jpg`;
+----------+----------+--------------+--------+------------+-------------+--------------+----------+-----------+------------+-----------+----------+----------+------------+-----------+------------+-----------------+-----------------+------+------+----------+------------+------------------+-----+---------------+-----------+------+---------+----------+
| FileName | FileSize | FileDateTime | Format | PixelWidth | PixelHeight | BitsPerPixel | DPIWidth | DPIHeight | Orientaion | ColorMode | HasAlpha | Duration | VideoCodec | FrameRate | AudioCodec | AudioSampleSize | AudioSampleRate | JPEG | JFIF | ExifIFD0 | ExifSubIFD | Interoperability | GPS | ExifThumbnail | Photoshop | IPTC | Huffman | FileType |
+----------+----------+--------------+--------+------------+-------------+--------------+----------+-----------+------------+-----------+----------+----------+------------+-----------+------------+-----------------+-----------------+------+------+----------+------------+------------------+-----+---------------+-----------+------+---------+----------+
| 4349313028_f69ffa0257_o.jpg | 257213 | 2018-03-09 04:09:34.0 | JPEG | 1199 | 800 | 24 | 96.0 | 96.0 | 0 | RGB | false | 0 | Unknown | 0.0 | Unknown | 0 | 0.0 | {"CompressionType":0,"DataPrecision":8,"ImageHeight":800,"ImageWidth":1199,"NumberOfComponents":3,"Component1":{"ComponentId":1,"HorizontalSamplingFactor":2,"VerticalSamplingFactor":2,"QuantizationTableNumber":0},"Component2":{"ComponentId":2,"HorizontalSamplingFactor":1,"VerticalSamplingFactor":1,"QuantizationTableNumber":1},"Component3":{"ComponentId":3,"HorizontalSamplingFactor":1,"VerticalSamplingFactor":1,"QuantizationTableNumber":1}} | {"Version":257,"ResolutionUnits":1,"XResolution":96,"YResolution":96,"ThumbnailWidthPixels":0,"ThumbnailHeightPixels":0} | {"Software":"Picasa 3.0"} | {"ExifVersion":[48,50,49,48],"UniqueImageID":"d65e93b836d15a0c5e041e6b7258c76e"} | {"InteroperabilityIndex":" ","InteroperabilityVersion":[48,49,48,48]} | {"GPSVersionID":[0,0,2,2],"GPSLatitudeRef":"N","GPSLatitude":47.53777313232332,"GPSLongitudeRef":"W","GPSLongitude":-122.03510284423795,"GPSAltitudeRef":0,"GPSAltitude":0.0} | {"Compression":6,"XResolution":72.0,"YResolution":72.0,"ResolutionUnit":2,"ThumbnailOffset":414,"ThumbnailLength":7213} | {} | {"Keywords":["135","2002","issaquah","police car","wa","washington"]} | {"NumberOfTables":4} | {"DetectedFileTypeName":"JPEG","DetectedFileTypeLongName":"Joint Photographic Experts Group","DetectedMIMEType":"image/jpeg","ExpectedFileNameExtension":"jpg"} |
+----------+----------+--------------+--------+------------+-------------+--------------+----------+-----------+------------+-----------+----------+----------+------------+-----------+------------+-----------------+-----------------+------+------+----------+------------+------------------+-----+---------------+-----------+------+---------+----------+
1 row selected (0.15 seconds)
Retrieving GPS location data from the Exif metadata for the use of GIS functions
0: jdbc:drill:zk=local> select t.GPS.GPSLatitude as lat, t.GPS.GPSLongitude as lon from dfs.`4349313028_f69ffa0257_o.jpg` t; +--------------------+----------------------+ | lat | lon | +--------------------+----------------------+ | 47.53777313232332 | -122.03510284423795 | +--------------------+----------------------+ 1 row selected (0.317 seconds)
Retrieving the images larger than 640 x 480 pixels
0: jdbc:drill:zk=local> select FileName, PixelWidth, PixelHeight from dfs.`/images/*.png` where PixelWidth >= 640 and PixelHeight >= 480; +--------------------------+-------------+--------------+ | FileName | PixelWidth | PixelHeight | +--------------------------+-------------+--------------+ | 1.png | 2788 | 1758 | | 1500x500.png | 1500 | 500 | | 2.png | 2788 | 1758 | | 9784873116914_1.png | 874 | 1240 | | Driven-Example-Load.png | 1208 | 970 | | features-diagram.png | 1170 | 644 | | hal1.png | 1223 | 772 | | hal2.png | 1184 | 768 | | image-3.png | 1200 | 771 | | image-4.png | 1200 | 771 | | image002.png | 1689 | 695 | +--------------------------+-------------+--------------+ 11 rows selected (1.327 seconds)
