BigQueryで指数表記されてしまう数値を正常に表示する

概要

BigQueryで検索範囲を大きくした状態でSUMすると、1.0E7のような指数で表示されてしまうことがあります。ここではそれを回避する方法を書いていきます。

利用するクエリ

10000000.0くらいの数値になると1.0E7になるのですが、これを回避するためにCASTすると良いようでした。

WITH test_table AS (
  SELECT a FROM UNNEST(ARRAY[1000000.0, 10000000.0, 10000000.1]) a
)

SELECT
  a,
  CAST(a AS STRING) a_str
FROM test_table

実際に実行すると以下のような結果になります。a_strの方がCASTした値になります。

Row	a	a_str
1	1000000.0	1000000
2	1.0E7	10000000
3	1.00000001E7	10000000.1

まとめ

文字列としてしまうと具合良く表示されるようなので、今後同じような指数表記になった場合は慌てずにCASTしてみようかと思います。

BigQueryで月初と月末の日付を出す

概要

BigQueryで月初や月末日付を取りたい場合があると思うのですが、検索すると標準SQLの情報があまり見つからなかったので何とか自力で作成してみました。いつものように個人的なメモを残します。

月初

月初についてです。今は2018年5月なので2018/05/01が表示されるクエリとなります。

TIMESTAMPから表示するやり方は以下のようになりました。

SELECT FORMAT_DATE('%Y/%m/%d', DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo"));

また、日付から表示するやり方は以下のようになりました。こちらの方がわかりやすいですかね。

SELECT FORMAT_DATE('%Y/%m/%d', DATE_TRUNC(CURRENT_DATE("Asia/Tokyo"), MONTH));

月末

次は月末についてです。先程の月初で取得した値に対してDATE_ADDで1ヶ月追加して、さらにDATE_ADDで1日引いた値となります。

こちらも先程の内容と同様にTIMESTAMPを利用したやり方です。

SELECT FORMAT_DATE('%Y/%m/%d',  DATE_ADD(DATE_ADD(DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo") , INTERVAL 1 MONTH), INTERVAL -1 DAY))

こちらは日付を利用したやり方です。

SELECT FORMAT_DATE('%Y/%m/%d',  DATE_ADD(DATE_ADD(DATE_TRUNC(CURRENT_DATE("Asia/Tokyo"), MONTH), INTERVAL 1 MONTH), INTERVAL -1 DAY));

まとめ

何となく遠回りをしているような気がするのですが、こんな感じで表示することができたので、あとはFORMAT_DATEで値を整形して具合良く利用できるのではないかと思いました。

pythonでAWS シークレットマネージャーの値の取得と更新を試す

概要

最近AWS シークレットマネージャーを使い始めたのですが、値を取得するサンプルコードはシークレットマネージャー上で見れるものの、更新はどうやるのかなと思ったのでサンプルを書いてみました。

サンプルコード

とりあえずこんな感じで動きました。<SECRET_NAME>となっている部分は任意で書き換えてもらえればと思います。エンドポイントとリージョンもですね。

import boto3
import json
from botocore.exceptions import ClientError


class SecretsManager(object):
    def __init__(self):
        endpoint_url = "https://secretsmanager.us-west-2.amazonaws.com"
        region_name = "us-west-2"
        session = boto3.session.Session()
        self.client = session.client(
            service_name='secretsmanager',
            region_name=region_name,
            endpoint_url=endpoint_url
        )

    def get_secret(self, secret_name):
        get_secret_value_response = self.client.get_secret_value(
            SecretId=secret_name
        )

        if 'SecretString' in get_secret_value_response:
            return get_secret_value_response['SecretString']

        return get_secret_value_response['SecretBinary']

    def update_secret(self, secret_name, update_json):
        try:
            return self.client.update_secret(
                SecretId=secret_name,
                SecretString=update_json
            )
        except ClientError as e:
            print('Error!!!')
            print(e)


def main():
    secret_name = "<SECRET_NAME>"

    # シークレット値を取得
    sm = SecretsManager()
    secret = sm.get_secret(secret_name)
    print(secret)

    # シークレット値を更新
    secret_dict = json.loads(secret)
    secret_dict['xxxxx'] = 'piyo'
    sm.update_secret(secret_name, json.dumps(secret_dict))


if __name__ == '__main__':
    main()

まとめ

更新する方法も分かってしまえば難しいことではなかったようでした。辞書型で任意の値を設定した後にjson.loadsで変更すると意図した値で更新することが出来ました。 あとは用途を考えて、値を更新することがあればしていこうと思います。

週末に撮った写真

OLYMPUS OM-D E-M10 Mark Ⅲを買ってから雨の日以外は毎日カメラを持って散歩に出ています。

yuji0602.hatenablog.jp

メインで撮っているのはうちの小麦さんなので、大体の写真はこちらに載せています。

shibainu-komugi.hatenablog.com

小麦さん以外に極稀に他の写真を撮っているので、それをただ貼っていこうと思います。

まずは道に咲いてた花から。名前は分からないのですが良い色だったので撮ってみました。

次はあじさい2枚。ようやく色がついたあじさいが咲いてきましたね。

その次は林道で撮った2枚。最近朝から暑いので木陰で散歩すると涼しくて良いんですよね。

犬の散歩をしながらなので、左手にリードを持って右手でカメラで写真を撮ろうとしているので何気に忙しい感じです。とはいえカメラが良いので具合よくピントが合ってくれるので良いですね。

いつかレンズをM.ZUIKO DIGITAL ED 12-100mm F4.0 IS PROにしてみたいのですが、宝くじが当たったらかな。

OLYMPUS マイクロフォーサーズレンズ M.ZUIKO DIGITAL ED 12-100mm F4.0 IS PRO

まだまだカメラを使いこなせてないので明日もまた写真を撮っていきます。

BigQueryで複数記載したSQLの中から選択したSQLを実行する

今日、BigQueryのアルファ版に登録している人向けのメールが届いた。メール内容については以下のようになっています。

Dear BigQuery UI Alpha users,

Since the last update a month ago, we've rolled out more features and improvements to the BigQuery Cloud Console UI. If you haven't tried it in a while, or are new to it, please try it out, and let us know what you think!

* Redesigned query results. You will see a more tabular format, with a fixed pagination footer, a default of 50 rows per page, and clear indication of null values (as opposed to strings with "null" as the value, or missing record fields). We're still iterating on this, so it'll look even better soon!
* A few missing features are now implemented:
    * Save to Sheets for query results.
    * Column-based partitioning options in Create Table.
    * Run selected in the Query Editor.
* We've fixed several bugs, including:
    * Various issues with Saved Queries.
    * Better support for hiding and showing the query editor without losing your query job state.
    * Better table lookup behavior in the query editor's autocomplete functionality.
    * Better polling of queries, which should result in slightly more responsive "run query query" behavior, as well as fixed bugs around queries appearing to run forever (please report them if you see them). 
    * Wildcard GCS URLs are now allowed in Create Table.
    * Layout fixes for small screens.

We're still working hard towards our Beta release. You will see a <BETA> badge in the UI, but we haven't officially launched yet! That's there for the public launch; we apologize for any confusion.

If you see things that you've reported and are still missing or not fixed, please know that we will get to them! In the meantime, please continue reporting issues or sending us feature requests to bqui-feedback@google.com.

Thank you for being an BigQuery UI Alpha customer! 

Your BigQuery UI Team

この中で、 Run selected in the Query Editor. が個人的には嬉しかったので実際に試してみました。

BigQueryを開いて、New Queryの中に次のようなクエリを書いてみます。

-- 昨日日付
SELECT
  DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) as yesterday,
  FORMAT_DATE('%Y/%m/%d', DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) as formated_yesterday;

-- 数値にカンマを埋める
SELECT 
  input,
  FORMAT("%'d", input) as formatted
FROM (
  SELECT 123 AS input UNION ALL 
  SELECT 1234 AS input UNION ALL
  SELECT 12345 AS input UNION ALL
  SELECT 123456 AS input UNION ALL
  SELECT 1234567 AS input UNION ALL
  SELECT 12345678 AS input UNION ALL
  SELECT 123456789 AS input
);

このクエリを書いたまま、RUN QUERYボタンを押して実行するとエラーになりました。

なので、どちらかのクエリをコメントアウトして実行していました。

今回のメールで来ていた内容はクエリを選択した状態で実行すると良いようです。

選択した部分だけ実行できるのでだいぶ便利な気がしますね。

作業が捗りそうです。