Impala で画像処理

# やろうと思ったきっかけ

過去に SQLServer でビットマップ画像の2値化をやったから。
最近業務で Impala を触り始めたから書き直した。
（そういえば、去年 Hive 版も書いてた）

# 実装

```sql
with
    -- シーケンス 0 - 1
    seq2 as (
        select 0 as x
        union all select 1 as x
    ),
    -- シーケンス 0 - 3
    seq4 as (
        select row_number() over(order by a.x) - 1 as x from seq2 as a cross join seq2 as b
    ),
    -- シーケンス 0 - 15
    seq16 as (
        select row_number() over(order by a.x) - 1 as x from seq4 as a cross join seq4 as b
    ),
    -- シーケンス 0 - 255
    seq256 as (
        select row_number() over(order by a.x) - 1 as x from seq16 as a cross join seq16 as b
    ),
    -- シーケンス 0 - 65535
    seq65536 as (
        select row_number() over(order by a.x) - 1 as x from seq256 as a cross join seq256 as b
    ),
    -- 入力パラメータ
    input_param as (
        select
            50 as threshold,
            base64decode(
"ここは base64 エンコードされたビットマップ画像(24bit)を指定する"
            ) as img
    ),
    --BITMAPFILEHEADER
    bitmap_file_header as (
        select 'bfType' as name,                 1 as pos, 2 as size
        union all select 'bfSize' as name,       3 as pos, 4 as size
        union all select 'bfReserved1' as name,  7 as pos, 2 as size
        union all select 'bfReserved2' as name,  9 as pos, 2 as size
        union all select 'bfOffBits' as name,   11 as pos, 4 as size
    ),
    --BITMAPINFOHEADER
    bitmap_info_header as (
        select 'biSize' as name,                    15 as pos, 4 as size
        union all select 'biWidth' as name,         19 as pos, 4 as size
        union all select 'biHeight' as name,        23 as pos, 4 as size
        union all select 'biPlanes' as name,        27 as pos, 2 as size
        union all select 'biBitCount' as name,      29 as pos, 2 as size
        union all select 'biCopmression' as name,   31 as pos, 2 as size
        union all select 'biSizeImage' as name,     33 as pos, 4 as size
        union all select 'biXPixPerMeter' as name,  37 as pos, 4 as size
        union all select 'biYPixPerMeter' as name,  41 as pos, 4 as size
        union all select 'biClrUsed' as name,       45 as pos, 4 as size
        union all select 'biCirImportant' as name,  49 as pos, 4 as size
    ),
    --ファイルヘッダのパース
    parsed_file_header as (
        select
            'file_header' as id,
            h.index,
            h.name,
            h.size,
            h.pos,
            (case
                when h.size = 1 then
                    (cast(conv(hex(substr(p.img, h.pos + 0, 1)), 16, 10) as int))
                when h.size = 2 then
                    (cast(conv(hex(substr(p.img, h.pos + 1, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 0, 1)), 16, 10) as int))
                when h.size = 4 then
                    (cast(conv(hex(substr(p.img, h.pos + 3, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 2, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 1, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 0, 1)), 16, 10) as int))
                else
                    0
             end
            ) as _data
        from
            input_param as p,
            (select
                row_number() over(order by bfh.pos) - 1 as index,
                bfh.name,
                bfh.pos,
                bfh.size
             from
                bitmap_file_header as bfh
            ) as h
    ),
    --情報ヘッダのパース
    parsed_info_header as (
        select
            'info_header' as id,
            h.index,
            h.name,
            h.size,
            h.pos,
            (case
                when h.size = 1 then
                    (cast(conv(hex(substr(p.img, h.pos + 0, 1)), 16, 10) as int))
                when h.size = 2 then
                    (cast(conv(hex(substr(p.img, h.pos + 1, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 0, 1)), 16, 10) as int))
                when h.size = 4 then
                    (cast(conv(hex(substr(p.img, h.pos + 3, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 2, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 1, 1)), 16, 10) as int) +
                     cast(conv(hex(substr(p.img, h.pos + 0, 1)), 16, 10) as int))
                else
                    0
             end
            ) as _data
        from
            input_param as p,
            (select
                row_number() over(order by bih.pos) - 1 as index,
                bih.name,
                bih.pos,
                bih.size
             from
                bitmap_info_header as bih
            ) as h
    ),
    --データ取り出し
    bitmap_info as (
        select
            substr(p.img, b.offbits) as _data,
            a.pixel_count,
            a.width,
            a.height
        from
            input_param as p,
            --総ピクセル数, 幅, 高さ
            (select
                exp(sum(ln(_data))) as pixel_count,
                max(case name when 'biWidth' then _data else 0 end) as width,
                max(case name when 'biHeight' then _data else 0 end) as height
            from
                parsed_info_header
            where
                name in ('biWidth', 'biHeight')
            ) as a,
            --ファイル先頭から画像データまでのオフセット
            (select
                _data as offbits
            from
                parsed_file_header
            where name = 'bfOffBits'
            ) as b
    ),
    make_pix_sequence as (
        select
            cast(seq.x as int) as row_index
        from
            seq65536 as seq, bitmap_info as i
        where
            seq.x < (i.pixel_count)
    ),
    load_pixels as (
        select
            s.row_index as pos,
            cast(trunc(s.row_index / i.height) as int) as row_index,
            cast(s.row_index % i.height as int) as col_index,
            cast(conv(hex((substr(i._data, cast((s.row_index * 3) + 0 as int), 1))), 16, 10) as int) as red,
            cast(conv(hex((substr(i._data, cast((s.row_index * 3) + 1 as int), 1))), 16, 10) as int) as green,
            cast(conv(hex((substr(i._data, cast((s.row_index * 3) + 2 as int), 1))), 16, 10) as int) as blue,
            i.pixel_count
        from
            make_pix_sequence as s,
            bitmap_info as i
        order by
            pos,
            row_index,
            col_index
    ),
    binarize as (
        select
            p.pos,
            p.row_index,
            p.col_index,
            (case
                when p.red < param.threshold then 0
                when p.green < param.threshold then 0
                when p.blue < param.threshold then 0
                else 1
            end) as pix
        from
            input_param as param,
            load_pixels as p
        order by
            pos,
            row_index,
            col_index
    ),
    --可視化
    visualize as (
        select
            b.row_index,
            group_concat(case b.pix when 1 then '■' else '□' end, '') as a
        from
            binarize as b
        group by
            b.row_index
        order by
            row_index
    )
select
    *
from
    visualize
order by
    row_index desc
```

Hive 版よりかは標準の SQL に近くて、こっちの方がすっきり見やすい。
Hive 版は lateral view とか map とか使ってしまった

# 実行結果

Hue の画面
![a.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25396/b4af9122-75d8-4ab0-7cda-d9d7a4a627ad.png)

テキストエディタに貼ったもの
![b.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25396/9d31fba8-4db0-ea2a-bae5-530556b69d85.png)