Date
December 21, 2022
Private
BigQueryを使う頻度が増えてきた事もあり、一度コストをしっかりみるかと思い。見たことがきっかけ。
とりあえず９月から増えだした事は理解
そもそもLong term storageって？
• Long-term storage includes any table or table partition that has not been modified for 90 consecutive days. The price of storage for that table automatically drops by approximately 50%. There is no difference in performance, durability, or availability between active and long-term storage.
90日連続して変更されていないテーブル（またはパーティション）
もうツールを作成して少し調べてみた
が役に立ちました。
- この記事内に書かれているスクリプトを Colab で実行してBQにテーブルを作成して集約
- Looker（旧Data Studio）で可視化
調べてみるとテーブル数が多い。このテーブルはシャーディング（ _yyyymmmdd）
更に7月まではシャーディングで取得していたけど、このタイミングからdbtなどで取得方法が変わっていて、シャーディングテーブルはつくられなくなっていた。
原因は特定できたので、あとは消すか消さないか。消すにしてもすべて消すか一部残すか。利用用途など色々確認した結果不要と判断したので、削除することにしました
削除編
SELECT
CONCAT("bq rm -f -t ",table_schema,".", table_name, ";" )
FROM `<project_id>.<dataset>`.INFORMATION_SCHEMA.TABLES
WHERE
table_name LIKE "<ここにprefix>_%"
ORDER BY table_nameこれで削除コマンドが出力されるのであとは実行するだけでした。
結果1番先頭のテーブルは残りましたが、それ以外はスッキリしました。コストとしても数百ドル下がる結果ととなりました。もしBQのコストにお悩みの方がいたら参考にしていただければと思いました。
改めてになりますが、参考になりました感謝です。
他の記事
| Page | Date |
|---|---|
Last Thursday 1:56 PM (GMT+9) | |
Last Thursday 1:54 PM (GMT+9) | |
Last Thursday 1:53 PM (GMT+9) | |
Last Thursday 1:52 PM (GMT+9) | |
Last Thursday 1:51 PM (GMT+9) | |
Last Thursday 1:47 PM (GMT+9) | |
Last Thursday 1:46 PM (GMT+9) | |
Last Thursday 1:45 PM (GMT+9) | |
Last Thursday 12:36 PM (GMT+9) | |
Last Tuesday 10:36 PM (GMT+9) | |
Last Tuesday 10:34 PM (GMT+9) | |
Last Tuesday 10:29 PM (GMT+9) | |
Last Tuesday 10:28 PM (GMT+9) | |
Last Tuesday 10:25 PM (GMT+9) | |
Last Tuesday 10:23 PM (GMT+9) | |
Last Tuesday 10:16 PM (GMT+9) | |
Last Tuesday 10:01 PM (GMT+9) | |
Last Tuesday 9:54 PM (GMT+9) | |
Last Tuesday 8:52 PM (GMT+9) | |
Last Tuesday 8:46 PM (GMT+9) | |
Last Tuesday 4:46 PM (GMT+9) | |
Last Monday 10:38 AM (GMT+9) | |
Last Monday 1:57 AM (GMT+9) | |
Last Monday 12:42 AM (GMT+9) | |
November 26, 2023 3:57 PM (GMT+9) | |
November 25, 2023 11:41 PM (GMT+9) | |
November 24, 2023 10:10 PM (GMT+9) | |
November 24, 2023 3:19 PM (GMT+9) | |
November 24, 2023 11:55 AM (GMT+9) | |
November 24, 2023 11:55 AM (GMT+9) | |
November 24, 2023 1:45 AM (GMT+9) | |
November 24, 2023 1:43 AM (GMT+9) | |
November 23, 2023 10:22 PM (GMT+9) | |
November 23, 2023 6:37 PM (GMT+9) | |
November 21, 2023 8:22 PM (GMT+9) | |
November 21, 2023 12:35 PM (GMT+9) | |
November 21, 2023 11:23 AM (GMT+9) | |
November 21, 2023 10:35 AM (GMT+9) | |
November 17, 2023 12:52 PM (GMT+9) | |
November 16, 2023 12:11 AM (GMT+9) | |
November 15, 2023 8:44 PM (GMT+9) | |
November 14, 2023 8:16 PM (GMT+9) | |
November 14, 2023 6:57 PM (GMT+9) | |
November 14, 2023 5:18 PM (GMT+9) | |
November 14, 2023 3:02 PM (GMT+9) | |
November 14, 2023 11:53 AM (GMT+9) | |
November 14, 2023 12:14 AM (GMT+9) | |
November 14, 2023 12:06 AM (GMT+9) | |
November 13, 2023 11:03 PM (GMT+9) | |
November 13, 2023 11:02 PM (GMT+9) | |
November 13, 2023 10:43 PM (GMT+9) | |
November 13, 2023 12:16 AM (GMT+9) | |
November 12, 2023 9:53 PM (GMT+9) | |
November 12, 2023 9:40 PM (GMT+9) | |
November 12, 2023 8:08 PM (GMT+9) | |
November 12, 2023 5:42 PM (GMT+9) | |
November 12, 2023 4:31 PM (GMT+9) | |
November 12, 2023 4:26 PM (GMT+9) | |
November 10, 2023 3:53 PM (GMT+9) | |
November 10, 2023 3:50 PM (GMT+9) | |
November 9, 2023 10:06 PM (GMT+9) | |
November 9, 2023 6:48 PM (GMT+9) | |
November 8, 2023 4:18 PM (GMT+9) | |
November 8, 2023 1:43 PM (GMT+9) | |
November 8, 2023 1:37 PM (GMT+9) | |
November 8, 2023 12:41 PM (GMT+9) | |
November 8, 2023 12:36 PM (GMT+9) | |
November 8, 2023 12:26 PM (GMT+9) | |
November 7, 2023 9:37 PM (GMT+9) | |
November 7, 2023 9:37 PM (GMT+9) | |
November 7, 2023 5:28 PM (GMT+9) | |
November 7, 2023 5:25 PM (GMT+9) | |
November 7, 2023 2:38 PM (GMT+9) | |
November 7, 2023 12:12 PM (GMT+9) | |
November 7, 2023 12:37 AM (GMT+9) | |
November 6, 2023 11:04 PM (GMT+9) | |
November 6, 2023 8:43 PM (GMT+9) | |
November 6, 2023 8:10 PM (GMT+9) | |
November 6, 2023 5:25 PM (GMT+9) | |
November 5, 2023 8:42 PM (GMT+9) | |
November 5, 2023 1:43 PM (GMT+9) | |
November 5, 2023 12:14 AM (GMT+9) | |
November 4, 2023 11:59 PM (GMT+9) | |
November 4, 2023 11:47 PM (GMT+9) | |
November 3, 2023 11:22 PM (GMT+9) | |
November 3, 2023 10:31 PM (GMT+9) | |
November 3, 2023 10:27 PM (GMT+9) | |
November 3, 2023 10:18 PM (GMT+9) | |
November 3, 2023 9:46 PM (GMT+9) | |
November 3, 2023 12:07 PM (GMT+9) | |
November 2, 2023 5:14 PM (GMT+9) | |
November 1, 2023 8:42 PM (GMT+9) | |
October 31, 2023 9:37 PM (GMT+9) | |
October 31, 2023 7:10 PM (GMT+9) | |
October 31, 2023 3:47 PM (GMT+9) | |
October 30, 2023 4:37 PM (GMT+9) | |
October 30, 2023 10:13 AM (GMT+9) | |
October 29, 2023 11:56 PM (GMT+9) | |
October 29, 2023 11:09 PM (GMT+9) | |
October 28, 2023 9:47 PM (GMT+9) | |
October 28, 2023 6:23 PM (GMT+9) | |
October 28, 2023 6:21 PM (GMT+9) | |
October 28, 2023 3:17 PM (GMT+9) | |
October 27, 2023 11:15 AM (GMT+9) | |
October 27, 2023 10:44 AM (GMT+9) | |
October 26, 2023 9:48 PM (GMT+9) | |
October 26, 2023 7:11 PM (GMT+9) | |
October 26, 2023 12:50 AM (GMT+9) | |
October 25, 2023 9:19 PM (GMT+9) | |
October 25, 2023 2:05 PM (GMT+9) | |
October 24, 2023 6:46 PM (GMT+9) | |
October 24, 2023 1:26 PM (GMT+9) | |
October 24, 2023 | |
October 24, 2023 12:48 PM (GMT+9) | |
October 23, 2023 | |
October 23, 2023 | |
October 23, 2023 11:53 AM (GMT+9) | |
October 23, 2023 2:50 AM (GMT+9) | |
October 23, 2023 1:27 AM (GMT+9) | |
October 22, 2023 3:33 PM (GMT+9) | |
October 22, 2023 | |
October 22, 2023 | |
October 22, 2023 | |
October 22, 2023 | |
October 22, 2023 12:29 AM (GMT+9) | |
October 22, 2023 12:00 AM (GMT+9) | |
October 21, 2023 10:36 PM (GMT+9) | |
October 21, 2023 5:34 PM (GMT+9) | |
October 20, 2023 | |
October 20, 2023 | |
October 20, 2023 | |
October 19, 2023 | |
October 19, 2023 | |
October 19, 2023 1:29 AM (GMT+9) | |
October 19, 2023 1:29 AM (GMT+9) | |
October 19, 2023 1:28 AM (GMT+9) | |
October 18, 2023 | |
October 16, 2023 | |
October 14, 2023 11:14 PM (GMT+9) | |
October 12, 2023 | |
October 6, 2023 1:30 AM (GMT+9) | |
October 4, 2023 | |
October 2, 2023 7:52 PM (GMT+9) | |
October 1, 2023 | |
September 22, 2023 1:31 AM (GMT+9) | |
September 20, 2023 1:32 AM (GMT+9) | |
September 16, 2023 1:33 AM (GMT+9) | |
September 16, 2023 1:32 AM (GMT+9) | |
August 28, 2023 1:34 AM (GMT+9) | |
August 28, 2023 1:33 AM (GMT+9) | |
July 2, 2023 4:49 PM (GMT+9) | |
May 25, 2023 3:27 PM (GMT+9) | |
April 30, 2023 6:35 PM (GMT+9) | |
March 23, 2023 8:13 PM (GMT+9) | |
March 13, 2023 3:04 PM (GMT+9) | |
March 1, 2023 11:57 PM (GMT+9) | |
January 1, 2023 | |
December 21, 2022 | |
December 20, 2022 | |
December 5, 2022 | |
October 25, 2022 3:03 PM (GMT+9) | |
August 19, 2022 | |
May 18, 2022 | |
May 16, 2022 11:58 PM (GMT+9) | |
May 1, 2022 | |
March 27, 2022 | |
March 1, 2022 | |
January 31, 2022 | |
January 13, 2022 | |
January 12, 2022 10:22 PM (GMT+9) | |
January 9, 2022 6:31 PM (GMT+9) | |
January 4, 2022 | |
December 24, 2021 | |
December 18, 2021 | |
December 16, 2021 | |
December 6, 2021 | |
November 21, 2021 | |
November 11, 2021 | |
November 7, 2021 | |
November 4, 2021 | |
July 16, 2021 | |
July 13, 2021 | |
May 26, 2021 | |
May 23, 2021 | |
March 3, 2021 | |
January 15, 2021 10:00 PM (GMT+9) | |
January 4, 2021 | |
December 15, 2020 12:00 PM (GMT+9) | |
December 13, 2020 | |
December 1, 2020 | |
November 18, 2020 11:00 PM (GMT+9) | |
November 11, 2020 6:00 AM (GMT+9) | |
November 10, 2020 1:00 AM (GMT+9) | |
November 9, 2020 11:00 PM (GMT+9) | |
October 22, 2020 | |
October 1, 2020 | |
September 10, 2020 | |
September 1, 2020 | |
April 22, 2020 | |
January 2, 2020 | |
August 22, 2019 | |
June 13, 2019 | |
March 21, 2019 | |
September 30, 2018 9:26 PM (GMT+9) | |
May 22, 2018 3:12 PM (GMT+9) | |
May 1, 2018 | |
November 27, 2017 | |
July 31, 2017 | |
October 23, 2011 11:20 PM (GMT+9) | |
