XMLAGG in Teradata

SQLServerやPostgreSQLにあるstring_aggやMySQLのgroup_concatですが残念なことにTeradataでは存在しません。代わりにという訳ではないですが、xmlaggという集約関数があり、これを使うと同じようなことが実現できます。

まずはデータを準備します。
drop table test_xml_agg
;
create table test_xml_agg (
 c1 int
,c2 int
,c3 varchar(10)
)
;
insert into test_xml_agg values (1,1,'hello');
insert into test_xml_agg values (1,2,'world');
insert into test_xml_agg values (2,1,'this');
insert into test_xml_agg values (2,2,'is');
insert into test_xml_agg values (2,3,'xmlagg');

上記のデータを1列目で集約し、2列目の順番で一度3列目を横に展開しカンマで結合するというクエリを書いてみます。
select
 c1  
 ,trim(trailing ',' from xmlagg(c3 || ',' order by c2) (varchar(100))) as string_agg
from test_xml_agg
group by 1
;

Result Set
c1 string_agg
1  hello, world
2  this, is, xmlagg


string_aggに比べるとごちゃちゃしていますが、まずxmlagg(c3 || ',' order by c2)で2列目の昇順で3列目を結合していくことを表しています。続いて(varchar(100))で型をsysudtlib.xmlからvarcharにキャストし、最後に末尾のカンマを除いています。

2018/10/19追記
tdstats.udfconcatという関数がひそかに存在すると同僚が教えてくれました。AutoStatsで統計対象カラム名を連結するための関数とのことです。
UDFCONCAT

GCPUG BD & ML 2018

Google Cloud Next 2018 Extended BigData & ML Dayに行ってきました。前半はメモレベルですが今回のミートアップについて、後半はBQMLとAsterの簡単な比較について書いてみます。BigQueryでHardware Accelerationを使ってるのは初めて知ったんですが、結構驚きでした。

# BigQuery New Feature by satoluxx
BQML
- 今のところ線形回帰とロジスティック回帰のみが使用できる
本筋とは関係ないですが、モデルの説明を画面に保存できるの地味に凄い助かるやつですね...! Create Model文も自動で保存してくれていたりしたら言うことなしな気がします。

GIS
- PostGISで実装されている
- WKTとGeoJSON (ShapeFileは変換する必要あり)
- 現在はベーダだが可視化もできる

Clusterd Tables
- 今まではテーブルの分割は時間軸だったのに対し、任意のカラムでサブテーブルに分ける(パーティションのイメージ)

# Cloud TPU by kazunori_279
TPU
- John Hennessy:ソフトウェアだけで解決できる時代は終わりつつある → Domain Specific Hardwareが必要
- 実はBigQueryはHardware Accelerationを使っているので速い
- GPUは電力を食う、DCの費用の1/3~1/2は電力代とも言われている。これが理由で東京のDCはGPUを置かせてくれなかったりする
- GPUも並列とはいえCPUと同様の処理が動いている
- TPUはメモリへのアクセス回数を減らすことで性能および電力消費量を大幅に改善している
- Cloud TPU v2 の料金体系はNormal とPreemptible(優先度低)で値段差がある
- DAWNBenchでベンチマークおよびコストパフォーマンスが公開されている

TPU Pod
- MPIライクなものを使ってTPU同士で通信している(all reduce)
今まではParameter ServerとNodeはgRPCで通信している
- eBayの事例ではaccuracy +10%, 100x faster。試行錯誤の回数も増えるのでaccuracyも向上

# Auto ML by shuhei_fujiwara
Auto ML Vision
- Cloud Storageに画像をアップロードし、csv(path, label)を用意し、実行するとWebAPIが生えてくる

AutoML Natural Language
- CSVをアップロードする(文書, カテゴリ)

AutoML Translation
TMXファイルをアップロードする
English→Japneseは既にあるが、逆はまだ

学習後に学習過程の遷移やConfusionMatrix、間違った部分なども表示してくれる
- AutoMLのモデルダウンロードは時期が未定だがいずれ可能になる予定

モデルの作成の価格
- Auto ML Vision (20$/per)
- AutoML Natural Language (3$/per)
- AutoML Translation (76$/per)
VisionはNASNetがベースとなっている

~~~

次に、BQMLとAsterの簡単な違いについて見ていきたいと思います。Teradataの製品であるAsterもBQMLと同様にSQLで機械学習を行えます。現在BQMLは線形回帰とロジスティック回帰の2つのようですが、Asterはなななんと100種類以上もの関数が用意されていて、NeuralNetやXGBoostなんかもサポートしています。各関数はJavaで書かれているのですが、足りない関数があれば自分で書いてUDFとしてインストールできるのが個人的には気に入っています。両者をシンタックスレベルで並べると以下のようになります。Aster側は見慣れていないとやや奇妙な印象を受けますね。BQMLはシンタックスから想像する限り、説明変数Numeric/Categoryは自動で判別してくれているのでしょうか...？もしそうだとしたら良いですね〜。

-- BQML (Train)
CREATE  `bqml_model` 
OPTIONS(model_type='logistic_reg') AS
SELECT
  label,
  os,
  is_mobile,
  country,
  pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`;

-- BQML (Predict)

SELECT  *

FROM

  ml.EVALUATE(MODEL `bqml_codelab.sample_model`, (

SELECT

  label,

  os,

  is_mobile,

  country,

  pageviews

FROM

  `bigquery-public-data.google_analytics_sample.ga_sessions_*`;

-- Aster (Train)
SELECT * FROM GLM (
  ON (SELECT 1)
  PARTITION BY 1
  InputTable ('aster_train')
  OutputTable ('aster_model')
  InputColumns ('pageviews')
  CategoricalColumns ('os', 'is_mobile', 'county')
);

-- Aster (Predict)
 SELECT * FROM GLMPredict (
  ON aster_test
  ModelTable ('aster_model'')
  Family ('LOGISTIC')
  Link ('LOGIT')
);

ミートアップで紹介されていたリンクはこれから見ていくつもりです。
GCPUG http://gcpug.jp/
Google Cloud Platformのノウハウ https://github.com/gcpug/nouhau
Google Cloud Next 2018 まとめ https://github.com/gcpug/nouhau/issues/60

Vulture, Syndrome

真山仁さんの「シンドローム」を読み終わりました。タイトルはチャイナ・シンドロームにかけているのかなと想像しています。久しぶりのスピンオフではない本編とあって思わず一気読みしました。内容は東日本大震災を描いたもので、首都電力という東京電力を模した会社が出てきます。事実と虚飾が入り混じっているんだとは思いますが、政府の鈍さや首都電会長の無責任さは見ていて複雑な気持ちになります。そのぐらいリアルだということでしょうが....。人名については当時の方々と照らし合わせると、以下のようになるでしょうか。

濱尾重臣→勝俣恒久
萩本あかね→丸山桂里奈
串村勝之→吉田昌郎
古谷光太郎→菅直人
水野義信→枝野幸男
佐伯昇→鉢呂吉雄

同じく真山さんの原発事故に関する小説のベイジンも読んだことがありますが、福島原発事故のあとに出た本だと思っていたので、出版された年が2008年だったということに驚きました。おおまかなあらすじは北京五輪の開会式に向けて突貫工事で進めた原発が大地震とメルトダウンを起こすといったもので、東日本大地震以前であればそんなこと起きるはずない！と一蹴できていたかもしれない内容です。
どちらの本も事実のみでなく、少し引いた目線から原発事故の流れやそこで働いていたもしくは働いている方々についてもう1度考えてみたい人にお勧めしたい本です。

Functional Programming in Java

4年近く前に出版された本ですが、Javaによる関数型プログラミングを読んでみました。タイトルをそのまま受け取ると関数型に興味のある人しか読まなさそうですが、遅延評価、末尾呼び出し最適化、メモ化などの話も載っていて、全体的に性能向上に繋がる要素がちりばめられています。末尾の付録にDRY原則が載っているのですが、そこに"DRYの精神の則って、ここではその内容を説明しません。URLの記事を参照してください"と書いてあるのがちょっと面白かったです。