BQで最長前方共通文字列を抽出する

背景

住所などで、前方一致する共通文字列を取り出したい。

方法

考え

前方部分文字列を1文字つずつ増やしながら生成して、同じ文字数で生成した文字列が何タイプあるか調べて、1以外なら共通していないので破棄する。（ここまでが前方共通文字列）

そのうち最長のものが欲しいわけだから、その文字数の最大値で、前方部分文字列を生成するとそれが最小共通文字列となる。

例

WITH
  data AS (
  SELECT
    x.latlon,
    x.address
  FROM
    UNNEST([
    STRUCT('東京都江東区有明1' AS address, 1 as latlon),
    STRUCT('東京都江東区有明2' AS address, 1 as latlon),
    STRUCT('東京都江東区東雲' AS address, 2 as latlon),
    STRUCT('東京都江東区東雲1' AS address, 2 as latlon),
    STRUCT('東京都江東区東雲2' AS address, 2 as latlon),
    STRUCT('東京都江東区豊洲4' AS address, 3 as latlon)
    ]) AS x),
  n_list AS (
  SELECT
    n
  FROM
    UNNEST(GENERATE_ARRAY(1, 30)) AS n ),
  substr_list AS (
  SELECT
    latlon,
    n,
    SUBSTR(address, 1, n) address
  FROM
    data
  CROSS JOIN
    n_list ),
  common_n_list AS (
  SELECT
    latlon,
    n,
    COUNT(DISTINCT address) type
  FROM
    substr_list
  GROUP BY
    latlon, n
  HAVING
    type = 1),
  max_common_n AS (
  SELECT
    latlon,
    MAX(n) n
  FROM
    common_n_list
  GROUP BY
    latlon ) --
  --
SELECT
  latlon,
  address,
  SUBSTR(address, 1, n) common_address
FROM
  data
JOIN
  max_common_n 
USING (latlon) 

Row	latlon	address	common_address
1	1	東京都江東区有明1	東京都江東区有明
2	1	東京都江東区有明2	東京都江東区有明
3	2	東京都江東区東雲	東京都江東区東雲
4	2	東京都江東区東雲1	東京都江東区東雲
5	2	東京都江東区東雲2	東京都江東区東雲
6	3	東京都江東区豊洲4	東京都江東区豊洲4

colab を使って Jira cloud をOAuth で接続

背景

colab から、Jira に接続したい。

方法

RSAのカギを作る。

次のサイトの情報に従って、鍵を作成する。

qiita.com

$ openssl genrsa -out jira.pem 1024
$ openssl rsa -in jira.pem -pubout -out jira.pub

アプリケーションリンクを作る。

次の方法（Step1）に従ってダミーのアプリケーションリンクを作る。 ただしアプリケーションのリンクは、「歯車」→「製品」→「アプリケーションのリンク」と辿る。

community.atlassian.com

ここで先 jira.pub の内容をコピペする。

colab 用にコードをコピペする。

次のサイトの回答をコピペする。

developer.atlassian.com

コピペすべき内容をこちらにも貼っておく。

!pip install jira

from oauthlib.oauth1 import SIGNATURE_RSA
from requests_oauthlib import OAuth1Session
from jira.client import JIRA

# The Consumer Key created while setting up the "Incoming Authentication" in
# JIRA for the Application Link.
CONSUMER_KEY = 'oauth-sample-consumer'
CONSUMER_SECRET = 'dont_care'
VERIFIER = 'jira_verifier'

# The contents of the rsa.pem file generated (the private RSA key)
RSA_KEY = """
-----BEGIN RSA PRIVATE KEY-----
-----END RSA PRIVATE KEY-----
"""

# The URLs for the JIRA instance
JIRA_SERVER = 'https://YOUR_JIRA_SERVER'
REQUEST_TOKEN_URL = JIRA_SERVER + '/plugins/servlet/oauth/request-token'
AUTHORIZE_URL = JIRA_SERVER + '/plugins/servlet/oauth/authorize'
ACCESS_TOKEN_URL = JIRA_SERVER + '/plugins/servlet/oauth/access-token'

# Step 1: Get a request token
oauth = OAuth1Session(CONSUMER_KEY, client_secret= CONSUMER_SECRET, signature_method=SIGNATURE_RSA, rsa_key=RSA_KEY)
request_token = oauth.fetch_request_token(REQUEST_TOKEN_URL)

resource_owner_key = request_token['oauth_token'];
resource_owner_secret = request_token['oauth_token_secret'];

print("STEP 1: GET REQUEST TOKEN")
print("  oauth_token={}".format(resource_owner_key))
print("  oauth_token_secret={}".format(resource_owner_secret))
print("\n")

# Step 2: Get the end-user's authorization
print("STEP2: AUTHORIZATION")
print("  Visit to the following URL to provide authorization:")
print("  {}?oauth_token={}".format(AUTHORIZE_URL, request_token['oauth_token']))
print("\n")

ここで、colab では、別のコードを一旦切って、

STEP2: AUTHORIZATION
  Visit to the following URL to provide authorization:

と表示されるリンクをクリックし、許可をする。 その後、次のコードとして実行する。

oauth = OAuth1Session(CONSUMER_KEY, client_secret= CONSUMER_SECRET, resource_owner_key=resource_owner_key, resource_owner_secret=resource_owner_secret, verifier=VERIFIER, signature_method=SIGNATURE_RSA, rsa_key=RSA_KEY)

# Step 3: Get the access token
access_token = oauth.fetch_access_token(ACCESS_TOKEN_URL)

print("STEP2: GET ACCESS TOKEN")
print("  oauth_token={}".format(access_token['oauth_token']))
print("  oauth_token_secret={}".format(access_token['oauth_token_secret']))
print("\n")

# Now you can use the access tokens with the JIRA client. Hooray!
jira = JIRA(options={'server': JIRA_SERVER}, oauth={
    'access_token': access_token['oauth_token'],
    'access_token_secret': access_token['oauth_token_secret'],
    'consumer_key': CONSUMER_KEY,
    'key_cert': RSA_KEY
})

# print all of the project keys just as an exmaple
for project in jira.projects():
    print(project.key)

まとめ

ドキュメントが古いのか嘘が書いてあったりして難しいがなんとか上記の方法でできる（2019/07/17現在）

Jira core をワークフローツールとして使う

背景

Jira core を workflow として使うときのメモ。日々更新。

なぜ Jira を使うのか

決められたフローに従って処理をしてほしい。終わったら、ボタン一つで次の人にアサインして欲しい。

用語

プロジェクト

一つのワークフローを持つことができる。またボードを持つことができる。

ボード

ステータス上の課題（チケット）をみることができる。カンバンボード。

ワークフロー

ステータスとトランジッションからなる。 ワークフローの各ステータス上を課題（チケット）がトランジッションに従い移動する。 後述するワークフロースキームに紐づくとアクティブとなる。コピーされたワークフローは非アクティブの状態である。非アクティブでないと編集できない。

課題

課題（チケット）はタスクのような単位。課題はステータスや、課題フィールドといった複数の属性を持つ。

ステータス

課題（チケット）が持つことができる状態。課題はそれ以外に、課題フィールドとよばれる、複数の属性をもつ。

課題フィールド

課題がもつ属性。解決状況や、担当者（アサイニ）や報告者などがある。

担当者

課題フィールドの一つで、現在その課題に割り振られている人。アサイニ。課題にはそのほかに報告者という人もいる。

報告者

課題フィールドの一つで、その課題を作成した人。最後担当者を報告者に割り当てて確認してもらい完了するなどするときに使う。

トランジッション

ステータス間の方向性を持ったリンク。一つの状態から複数の選択肢がある場合には、複数のトランジッションを持たせることできる。トランジッションの名称は選択肢が一つしかないなら特に気にする必要はないが、複数ある時には、ステータス先を見て決めるよりもトランジッション名を見て決めるので、重要となる。

事後処理

トランジッションを行ったときに何か処理を行うかどうか。少なくともステータスは変更されるので一つ以上のの事後処理はあることになる。ステータスによって担当者が一人に決まるときは、ここに課題フィールドの変更として追加すればよい。

ワークフロースキーム

プロジェクトのステータスの種類を示す。ワークフロースキームというよりステータススキームといったほうが分りやすいだろう。アクティブであるということは現在プロジェクトで使われているということになる。コピーしたものは非アクティブとなる。また「ワークフローの追加」をすることで、「追加された」ワークフローはアクティブになり、これまで使っていたワークフローは非アクティブになる。このとき二つのワークフローステータスから新しいワークフローのステータスへ既存の課題を移し替える「調整」という作業が発生する。

Jira の slack 連携で使うslackアプリ

背景

気が付けば、Jiraの slack　アプリが二つも入っていた。何かおかしい。

二つのアプリの違い

Jira Cloud

my.slack.com

新しい。チャンネルごとの設定ができる。

チャンネル設定のやり方

• はじめに設定したいチャンネルに入る。
• プライベートチャネルなら、 /invite jira をする
• コマンド /jira connect で接続する（チャンネルごとに初回のみ）
• コマンド /jira manage で現れたリンクをクリックする。

ダイレクトメッセージ設定のやり方

• はじめにjiraのダイレクトメッセージに入る。
• コマンド /jira connect で接続する（初回のみ）
• コマンド /jira manage で現れたリンクをクリックする。
• JQLにてassignee = "your@mail.address" として save すると幸せになれるかも。（ダブルクオートを忘れない）

参考： ja.confluence.atlassian.com

JQLのフィールド

Jira Server Alerts (Legacy)

my.slack.com

古い。チャンネルごとの設定ができない。

まとめ

Jira Cloud を使おう

DataPortal のコピー

背景

DataPortal は個人（社内）で使うのは素晴らしいが、これを使ってビジネスでスケールアウトさせようとすると、いろいろと問題が出る。 とりあえず、コピーをして複製を作り、その複製に対して設定を変えるという手作業でなんとかしのげそうなのだが、コピーそのものがうまくいかない。

コピーの理解

DataPortalとデータソースは別物

DataPortalをコピーすると、当然紐づけられているデータソースもコピーされると思うだろう。 実際、自分で作った（カスタム）データソースは自分でコピーする際に、デフォルトで選択肢として表示される。 ところが、他の人がDataPortalをコピーしようとすると、そのデータソースが共有されていない場合、 デフォルトの選択肢として表示されない。

解決策

コピーする人にデータソースを共有する

データーソースの共有は閲覧者としてのみでOK.。ダウンロードや印刷、コピーを禁止しておく。 一方で、データソースの先のアクセス権は、データソースの「データの認証情報」が「オーナー」にセットされていれば、データソースへはオーナーの権限でアクセスしに行くので、問題ないだろう。 DataPortalそのものはその作成者（複製者）の権限でデータソースにアクセスしに行くので、これらのコンボ技でDataPortalが見える人なら閲覧することが可能となる。

コピーした人が複製されたDataPortalの共有権限を設定する

複製元のDataPortalの共有設定は複製先のDataPortalに継承されない。ゆえに他人に見てもらうのであれば、複製されたDataPortalの共有設定を忘れずにする必要がある。

Google Analytics の tracking ID もコピーする。

コピーされないので、こちらもコピーする。

まとめ

• DataPortal とデータソースは別物
• だからコピーにはデータソースの共有も必要（閲覧者でOK）
• 複製されたDataPortalの共有設定は忘れずに。

Jira Software Cloud と GitHub の連携

背景

Jira Software Cloud と GitHub の連携に関して、検索すると古い記事が出てくる。 Atlassian の公式のページの情報も古い記事なので注意。OAuth や、DVCS アカウントというキーワードが出ていれば全て古い。

解決策

すべては、

www.atlassian.com

の2018年10月4日以降の記事を参考にすべし。

すなわち、

marketplace.atlassian.com

をJira側でインストール（Jiraの管理者権限が必要）し、そこの設定リンクから、github側の設定に飛ぶ（githubの管理者権限が必要）

連携後の使い方

ja.confluence.atlassian.com

BigQueryで、1000円分の切手問題を解く

背景

igcn.hateblo.jp

方法

実行は慎むこと。データ参照は0バイトだが、7000秒たっても（現時点）結果は出ない。こんなおバカなクエリをBQ様に投げて心が痛い。

WITH
  num_list AS (
  SELECT
    n
  FROM (
    SELECT
      GENERATE_ARRAY(1,1000) a )
  CROSS JOIN
    UNNEST(a) AS n),
n1 as (select n from num_list where n * 1 <= 1000),
n2 as (select n from num_list where n * 2 <= 1000),
n3 as (select n from num_list where n * 3 <= 1000),
n5 as (select n from num_list where n * 5 <= 1000),
n10 as (select n from num_list where n * 10 <= 1000),
n20 as (select n from num_list where n * 20 <= 1000),
n30 as (select n from num_list where n * 30 <= 1000),
n50 as (select n from num_list where n * 50 <= 1000),
n62 as (select n from num_list where n * 62 <= 1000),
n82 as (select n from num_list where n * 82 <= 1000),
n92 as (select n from num_list where n * 92 <= 1000),
n100 as (select n from num_list where n * 100 <= 1000),
n120 as (select n from num_list where n * 120 <= 1000),
n140 as (select n from num_list where n * 140 <= 1000),
n205 as (select n from num_list where n * 205 <= 1000),
n280 as (select n from num_list where n * 280 <= 1000),
n310 as (select n from num_list where n * 310 <= 1000),
n500 as (select n from num_list where n * 500 <= 1000),
n1000 as (select n from num_list where n * 1000 <= 1000)

select 
n1.n as nn1,
n2.n as nn2,
n3.n as nn3,
n5.n as nn5,
n10.n as nn10,
n20.n as nn20,
n30.n as nn30,
n50.n as nn50,
n62.n as nn62,
n82.n as nn82,
n92.n as nn92,
n100.n as nn100,
n120.n as nn120,
n140.n as nn140,
n205.n as nn205,
n280.n as nn280,
n310.n as nn310,
n500.n as nn500,
n1000.n as nn1000
from n1
cross join n2
cross join n3
cross join n5
cross join n10
cross join n20
cross join n30
cross join n50
cross join n62
cross join n82
cross join n92
cross join n100
cross join n120
cross join n140
cross join n205
cross join n280
cross join n310
cross join n500
cross join n1000
where 
n1.n * 1 +
n2.n * 2 +
n3.n * 3 +
n5.n * 5 +
n10.n * 10 +
n20.n * 20 +
n30.n * 30 +
n50.n * 50 +
n62.n * 62 +
n82.n * 82 +
n92.n * 92 +
n100.n * 100 +
n120.n * 120 +
n140.n * 140 +
n205.n * 205 +
n280.n * 280 +
n310.n * 310 +
n500.n * 500 +
n1000.n * 1000  = 1000

結論

バカは俺一人で十分