Take Your Time

仕事や研究、コンピューターとの付き合い方

amazon redshiftでdrop tableが永遠に終わらないとき

ハマったのでメモ。

mediumの記事に書いてあるとおりだが、日本語で出てこなかったし、mediumが消えるかもなので書いておく。

症状

  • drop table hogehoge;というクエリを発行したところ、永久にdropできない。
  • 定石であるselect pid, user_name, starttime, query from stv_recents where status='Running';でクエリのpid (プロセスID)を確認してからの cancel <pid> も効かない。
  • コンソール画面からのクエリ停止は、そもそも drop table hogehoge が見つからなかったので断念。
  • ググりまくっていたらmediumの記事を発見。

medium.com

  • 要は対象のクエリが別のプロセスにブロックされているということらしい。
  • まず、どのクエリがブロックしているかを確認
SELECT waiting.relation::regclass AS waiting_table,
       blocking.relation::regclass AS blocking_table,
       waiting.pid AS waiting_pid,
       blocking.pid AS blocking_pid,
       waiting.mode AS waiting_mode,
       blocking.mode AS blocking_mode,
       waiting.GRANTED AS waiting_granted,
       blocking.GRANTED AS blocking_granted,
       waiting.TRANSACTION AS waiting_txn,
       blocking.TRANSACTION AS blocking_txn
FROM pg_locks AS waiting
  LEFT JOIN pg_locks AS blocking
         ON ( (waiting. "database" = blocking. "database"
        AND (waiting.relation = blocking.relation
         OR blocking.relation IS NULL
         OR waiting.relation IS NULL))
         OR waiting.TRANSACTION = blocking.TRANSACTION)
WHERE 1 = 1
AND   NOT waiting.GRANTED
AND   waiting.pid <> blocking.pid
AND   blocking_granted = 't'
ORDER BY blocking_granted,
         waiting_granted,
         blocking_pid,
         waiting_pid;
  • blocking_pidが諸悪の根源で、これを止めればいい。しかし cancel <pid> ではだめらしい。
  • SELECT pg_terminate_backend(<blocking pid>); を使う。select文なようだが、psqlコマンドで問い合わせを止めるらしい。

www.postgresql.jp

  • 一件落着。

行政区域データをbigqueryに入れる

分析のために行政区域データと緯度経度があるcsvファイルを空間結合したかった。 頼みのQGISはローカルだとおそすぎて無理。

こうなると空間データ対応のbigquery一択になる。

geojson対応だということでそのまま突っ込んでみるが、フォーマットエラー。

中身はこんな感じ。

{
"type": "FeatureCollection",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::6668" } },
"features": [
{ "type": "Feature", "properties": { "N03_001": "北海道", "N03_002": "オホーツク総合振興局", "N03_003": null, "N03_004": "北見市", "N03_007": "01208" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 144.08143547296879, 44.125060386827442 ], [ 144.08142697797268, 44.12505705393994 ], [ 144.08140771719243, 44.125058359755542 ], [ 144.08139907920417, 44.125065108268188 ], [ 144.08139883279, 44.125071864874712 ], [ 144.08140364506221, 44.125092586153983 ], [ 144.08141955856581, 44.125165666862074 ], [ 144.08142379976857, 44.125202054331403 ], [ 144.08143380022977, 44.125253802221266 ], [ 144.08145983110637, 44.125327468388036 ], [ 144.08153007805072, 44.125313802290179 ], [ 144.08146282674807, 44.125145387149928 ], [ 144.08143547296879, 44.125060386827442 ] ] ] } },
{ "type": "Feature", "properties": { "N03_001": "北海道", "N03_002": "オホーツク総合振興局", "N03_003": null, "N03_004": "北見市", "N03_007": "01208" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 143.78333333266721, 44.184525108247215 ], [ 143.78280805394854, 44.183368613370874 ], [ 143.78274722380536, 44.183381107652053 ], [ 143.78272647194922, 44.183333332966981 ], [ 143.78234999955293, 44.182466945892884 ], [ 143.78193250278287, 44.181506666899224 ], [ 143.78213194633236, 44.181299170820125 ], [ 143.78239277580599, 44.181013612688105 ], [ 143.78261389121815, 44.180794721299378 ], [ 143.78280805394854, 44.180620838281129 ], [ 143.78307221810849, 44.180408892356525 ], [ 143.78317250330849, 44.180337774868462 ], [ 143.78324138777987, 44.18029583768

どうやらgeojsonへの対応は完全ではなくfeatureCollectionに対応していない。 ここで方法は、.geojsonファイルをいじって形式を合わせるか、諦めてWKTにするか。 前者を試したがだめ。

QGIS経由でWKTにして読ませるのが良さそう。

まず、QGISに読み込ませる。このとき、エンコーディングSJISにして文字化けを起こさないようにする。

https://gis.stackexchange.com/questions/43129/creating-csv-with-geometry-as-wkt-in-qgis-with-choosing-field-delimiter

utf-8変換した上でGCSに送る。

あとはcsvにしてカラムを自動認識にして読み込ませれば終わり。

spatial join

大変だったのでメモ。 行政区域データからmultipolygonで境界データをとってきて、別のattributeテーブルにある2つの属性をポイントでジョインした上でサマライズ。 st_union_aggを見つけるまで苦労した。まあ、bigqueryのマニュアルにあったのだが。 geometry型はavgなどの集計が使えないので適宜st_関数を探す必要がある。

with t1 as (select N03_001 as prefecture, N03_004 as city, ST_GeogFromText(WKT) as polygon from kokuseisuuchi.kyokai ),
t2 as (select att1, att2, st_geogpoint(cast(longitude as numeric), cast(latitude as numeric)) as loc from data.attribute where longitude is not null and latitude is not null and longitude != "NA" and latitude != "NA") 
select prefecture, city, avg(att1) as att1, avg(att2) as att2, st_union_agg(t1.polygon) as polygon from t1 join t2 on st_contains(t1.polygon, t2.loc) group by 1,2;

体力も気力も衰えた自分が若い時の自分に勝てる部分の一つとしてもうモテなくてもいい、伴侶を得るための努力をしなくていいということがある。 伴侶を得るというのは結構大変な仕事だ。 まず戦いのルールを知る必要がある。社会的に自分と違う扱いを受けてきた人種(ヘテロセクシャルの場合はだが)を理解して好意を得る方法を学ぶ必要がある。 ある程度のマニュアルはあるが、書いてあることがバラバラだったりする。第一、相手によって攻略法が違いすぎる。ある意味、モンスター図鑑にないモンスターと戦い続けるようなものだ。 それに主人公のパラメータがプレイヤーによって違いすぎる。 外見、内面の評価は自分ではほとんど不可能だ。学歴や年収、職業みたいな要素は観察は可能だが、相手に有効なのかどうかは戦ってみないわからない。 したがって経験がものをいう。 そのためには、できるだけ多くの時間を費やしたものが勝つ。 つまり、仕事や趣味を犠牲にする必要がある。 結婚前から仕事との両立を強いられている。両立できないものは次のステージに行けずひたすらもがくか、諦めるか。 別に諦めるのも悪くない。 諦めてレベル上げしてるうちにステータスがあがりまくり敵が勝手に弱くなって勝手に次のステージにいける場合もある。 しかし、基本的にステータスをあげるために頑張るというのが先にあって、次のステージに行く必要がないから頑張らなくなる方が多いだろう。 もし、モンスターが強すぎて全ての勇者がアリアハンで滞留するようになったら世界はどうなるんだろう。

jupyter labに切り替えた

今まではnotebookとlabを適当に使い分けていたが完全にlabに切り替えることにした。 理由は

  • jupyter labのダークテーマがかっこいい
  • .pyを修正するときに()の自動補完をしてくれる
  • github連携ができる
  • まだ使っていないがRStudio的なdata viewerが使える

シニア

ジュニアとシニアというのは割と広く共有されている概念だと思う。 ジュニアは作業者で、シニアは確認者。ジュニアは提案者でシニアが意思決定者。ジュニアはネタ作りで、シニアはスピーカー。 研究の世界だと、博士課程生/ポスドクー教授は完全なジュニアーシニア関係だが、研究室概念に乏しい文系だと助教ー教授は単に作業分担者だったりするので割と曖昧。 プロフェッショナルファーム系だと、シニアなんとかという職階や、マネジャー、MDみたいな肩書きがシニアであることを明確にしているのでその区別は明確。 ジュニアーシニアを現代的徒弟制と考えるたときにその功罪を考える。 まず、プログラマには現代的徒弟制はなじまない。基本的に若い方が新しいものに詳しいし強い。 CTOやシニアエンジニアみたいな肩書きを持つ人が平気で20代だったりする。年齢がほとんど意味をなさない世界。 研究者も若い方が新しいものに詳しかったりするが、文献研究では勝てないしグラントの取り方も下手。なのでシニアの価値は一定程度ある。 さて、公務員はどうか。 たしかに、政治家との交渉は人脈のあるシニアの方がうまい。しかし、しょぼいシニアが外に追いやられるプロファームと違って得てしてしょぼいシニアがのさばっているため必ずしもうまいわけではない。 年齢の分け方もひどい。 プログラマなら20代でもシニア、プロファームなら30代後半でシニア、研究者は40代くらいだろうか。 公務員は本省課長がはやくて40代後半(早い省庁もあるにはあるが)。そしてそれもまだ上に中二階の審議官やらが大量にいるので、シニアというよりミドル扱い。 しかし、40代までジュニア扱いをされ、50代になって初めてシニアになる人間には責任を取れないジュニア根性が染み付いている。使えない。