[ https://issues.apache.org/jira/browse/DRILL-4364?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16511531#comment-16511531 ]
Bridget Bevens commented on DRILL-4364: --------------------------------------- Sorry about that [~nagix]. I moved the text out of the code block: https://drill.apache.org/docs/image-metadata-format-plugin/#examples Let me know if you see any other issues. Thanks! Bridget > Image Metadata Format Plugin > ---------------------------- > > Key: DRILL-4364 > URL: https://issues.apache.org/jira/browse/DRILL-4364 > Project: Apache Drill > Issue Type: New Feature > Components: Storage - Other > Reporter: Akihiko Kusanagi > Assignee: Akihiko Kusanagi > Priority: Major > Labels: doc-complete, ready-to-commit > Fix For: 1.14.0 > > > Support querying of metadata in various image formats. This plugin leverages > [metadata-extractor|https://github.com/drewnoakes/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 > {noformat} > 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) > {noformat} > Querying on a JPEG file with the property descriptive: false > {noformat} > 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) > {noformat} > Retrieving GPS location data from the Exif metadata for the use of GIS > functions > {noformat} > 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) > {noformat} > Retrieving the images larger than 640 x 480 pixels > {noformat} > 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) > {noformat} -- This message was sent by Atlassian JIRA (v7.6.3#76005)