Take Your Time

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

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

SQL AWS

ハマったのでメモ。

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に入れる

QGIS Python R GC Google Cloud Platform

分析のために行政区域データと緯度経度がある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

GCP GIS

大変だったのでメモ。 行政区域データからmultipolygonで境界データをとってきて、別のattributeテーブルにある２つの属性をポイントでジョインした上でサマライズ。 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に切り替えた

jupyter

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

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