Amazon Redshift これでデータレイクの連携がスムーズになる! UNLOADコマンドがヘッダー行に対応したので試してみました

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

UNLOADコマンドでHEADERオプションを指定すると、作成された各出力ファイルに特定のヘッダー行を追加できるようになりました。ヘッダー行には、UNLOADクエリによって作成されたカラム名が含まれています。では早速試してみましょう。

検証テーブルとデフォルトのUNLOAD

例えば、以下のようなテーブルがあったとします。

CREATE TABLE IF NOT EXISTS "cm_schema"."orders"
(
        "row_id" BIGINT   ENCODE lzo
        ,"order_id" VARCHAR(255)   ENCODE raw
        ,"order_date" DATE   ENCODE lzo
        ,"ship_date" DATE   ENCODE lzo
        ,"ship_mode" VARCHAR(255)   ENCODE lzo
        ,"customer_id" VARCHAR(255)   ENCODE lzo
        ,"customer_name" VARCHAR(255)   ENCODE lzo
        ,"segment" VARCHAR(255)   ENCODE lzo
        ,"country" VARCHAR(255)   ENCODE lzo
        ,"city" VARCHAR(255)   ENCODE lzo
        ,"state" VARCHAR(255)   ENCODE lzo
        ,"region" VARCHAR(255)   ENCODE lzo
        ,"product_id" VARCHAR(255)   ENCODE lzo
        ,"category" VARCHAR(255)   ENCODE lzo
        ,"sub_category" VARCHAR(255)   ENCODE lzo
        ,"product_name" VARCHAR(255)   ENCODE lzo
        ,"sales" DOUBLE PRECISION
        ,"quantity" BIGINT   ENCODE lzo
        ,"discount" DOUBLE PRECISION
        ,"profit" DOUBLE PRECISION
)
DISTKEY("order_id")
SORTKEY (
        "order_date"
        )
;

cmdb=> select * from orders limit 1;
-[ RECORD 1 ]-+---------------------------
row_id        | 1
order_id      | JP-2016-1000099
order_date    | 2016-11-08
ship_date     | 2016-11-08
ship_mode     | 即日配送
customer_id   | 谷大-14605
customer_name | 谷奥 大地
segment       | 消費者
country       | 日本
city          | 千歳
state         | 北海道
region        | 北海道
product_id    | 家具-本棚-10004817
category      | 家具
sub_category  | 本棚
product_name  | Dania キャビネット, 従来型
sales         | 16974
quantity      | 3
discount      | 0.4
profit        | -1986

まずは従来通りUNLOADコマンドを実行します。

UNLOAD ('SELECT * FROM cm_schema.orders')
TO 's3://cm-bucket/orders_unload/' 
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/cm-redshift-role'
DELIMITER '\t'
;

指定したS3パスの下にスライス数の整数倍のファイルが出力されます。例えば、ファイルの内容は以下のとおりです。先頭からレコードが出力されています。

3560    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    家具-本棚-10003403  家具  本棚  Dania コーナー シェルフ, 白  24918   30  2490
3561    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    家電-コピ-10003199  家電  コピー機    ヒューレット・パッカード ファックス, デジタル    85216   4   0   17888
3562    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    事務用-バイ-10004115 事務用品    バインダー   Avery とじ機, 高耐久性 20136   6   0   3012
:
:

[新機能] HEADERオプションの検証

HEADERオプション指定してUNLOAD

UNLOADコマンドにおいて、HEADERを指定すると先頭行にカラム名が設定された「ヘッダ行」が追加されます。このテーブルに対して、HEADERオプション指定のUNLOADコマンドを実行します。

UNLOAD ('SELECT * FROM cm_schema.orders')
TO 's3://cm-bucket/orders_unload/' 
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/cm-redshift-role'
DELIMITER '\t'
HEADER
;

テーブルのカラム名がヘッダ行に追加されます。指定したS3パスの下にスライス数の整数倍のファイルが出力されますが、それぞれのファイルにヘッダ行が追加されます。

row_id,order_id,order_date,ship_date,ship_mode,customer_id,customer_name,segment,country,city,state,region,product_id,category,sub_category,product_name,sales,quantity,discount,profit
row_id  order_id    order_date  ship_date   ship_mode   customer_id customer_name   segment country city    state   region  product_id  category    sub_category    product_name    sales   quantity    discount    profit
3560    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    家具-本棚-10003403  家具  本棚  Dania コーナー シェルフ, 白  24918   30  2490
3561    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    家電-コピ-10003199  家電  コピー機    ヒューレット・パッカード ファックス, デジタル    85216   4   0   17888
:
:

HEADERオプション指定してUNLOAD(日本語カラム)

次に任意のカラム名が指定できるか確認します。カラムにエイリアスをつけることで変更できます。

UNLOAD ('SELECT "row_id" as 行id ,"order_id" as オーダーid ,"order_date" as オーダー日 ,"ship_date" as 出荷日 ,"ship_mode" as 出荷モード ,"customer_id" as 顧客id ,"customer_name" as 顧客名 ,"segment" as 区分 ,"country" as 国 ,"city" as 市 ,"state" as 県 ,"region" as 地域 ,"product_id" as 商品id ,"category" as 分類 ,"sub_category" as サブ分類 ,"product_name" as 商品名 ,"sales" as 売上 ,"quantity" as 数量 ,"discount" as 割引率 ,"profit" as 利益 FROM cm_schema.orders;')
TO 's3://cm-bucket/orders_unload/' 
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/cm-redshift-role'
DELIMITER '\t'
HEADER
;

どうせ変更するならということで、日本語カラムにしましたが全く問題ありませんでした。

行id オーダーid  オーダー日   出荷日 出荷モード   顧客id    顧客名 区分  国   市   県   地域  商品id     分類 サブ分類    商品名 売上  数量  割引率 利益
3560    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    家具-本棚-10003403  家具  本棚  Dania コーナー シェルフ, 白  24918   30  2490
3561    JP-2014-2034412 2014-01-06  2014-01-08  セカンド クラス    桃優-13840    桃木 優太   大企業 日本  福山  広島  中国地方    家電-コピ-10003199  家電  コピー機    ヒューレット・パッカード ファックス, デジタル    85216   4   0   17888
:
:

これでデータレイクの連携がスムーズになる!?

デフォルトのUNLOADで作成したヘッダ行なしのファイルをAWS Glueでクロールするとカラム名がcol0,col1,col2..のように設定されます。Glueのコンソールからカラム名を変更することが可能ですが、カラム名が数百ある場合は気が遠くなるような作業です。

AWS Glueはヘッダ行を自動認識してカラム名に設定してくれます。これで、RedshiftからUNLOADしたファイルを何も変更も加えずにGlueでクロール、AthenaやRedshift Spectrumでクエリが実行できるようになりました。

最後に

Hadoopの世界では、.csvはヘッダを含まないものであり、ヘッダを含むものは.csvhであるという暗黙のルールがありますが、データレイクが民主化した今日においては、むしろヘッダ行付きが一般的になりつつあります。これまでRedshiftも、ヘッダ行付きファイルの読み込み(COPY)は対応していましたが、ヘッダ行付きファイルを出力(UNLOAD)するオプションがなく、COPY/UNLOADコマンドに対称性がないことが気になっていました。

Amazon Redshiftを中心としたDMPでは、SQLをインタフェースとして、射影、集計、フィルタした結果を、ファイル出力・データ連携しますが、今後は出力したい形式でUNLOADコマンドのSELECT文を書き換えることで、英数字・日本語問わずヘッダ行を追加できることが確認できました。UNLOADコマンドは、リーダーノードを介さず、コンピュートノードが直接S3に水平分散出力することで高速にファイル出力できますので積極的にご活用ください。