Date
December 21, 2022
Private
用語
BigQueryを使う頻度が増えてきた事もあり、一度コストをしっかりみるかと思い。見たことがきっかけ。
とりあえず9月から増えだした事は理解
そもそも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のコストにお悩みの方がいたら参考にしていただければと思いました。
改めてになりますが、参考になりました感謝です。
他の記事
Name | Date |
---|---|
February 18, 2024 7:24 PM (GMT+9) | |
February 14, 2024 3:23 PM (GMT+9) | |
February 13, 2024 11:00 AM (GMT+9) | |
February 13, 2024 12:05 AM (GMT+9) | |
February 12, 2024 10:00 PM (GMT+9) | |
February 10, 2024 11:19 AM (GMT+9) | |
February 8, 2024 4:42 PM (GMT+9) | |
February 5, 2024 1:39 PM (GMT+9) | |
February 2, 2024 2:25 PM (GMT+9) | |
February 2, 2024 2:25 PM (GMT+9) | |
February 1, 2024 8:01 PM (GMT+9) | |
February 1, 2024 6:40 PM (GMT+9) | |
February 1, 2024 3:44 PM (GMT+9) | |
February 1, 2024 2:47 PM (GMT+9) | |
January 31, 2024 9:00 AM (GMT+9) | |
January 29, 2024 12:19 AM (GMT+9) | |
January 26, 2024 2:20 PM (GMT+9) | |
January 25, 2024 2:25 PM (GMT+9) | |
January 25, 2024 1:08 PM (GMT+9) | |
January 21, 2024 1:34 AM (GMT+9) | |
January 21, 2024 12:43 AM (GMT+9) | |
January 21, 2024 12:35 AM (GMT+9) | |
January 21, 2024 12:16 AM (GMT+9) | |
January 21, 2024 12:06 AM (GMT+9) | |
January 21, 2024 12:00 AM (GMT+9) | |
January 20, 2024 11:55 PM (GMT+9) | |
January 20, 2024 11:51 PM (GMT+9) | |
January 20, 2024 1:07 PM (GMT+9) | |
January 20, 2024 11:46 AM (GMT+9) | |
January 20, 2024 12:12 AM (GMT+9) | |
January 19, 2024 9:34 PM (GMT+9) | |
January 16, 2024 12:30 PM (GMT+9) | |
January 15, 2024 2:26 PM (GMT+9) | |
January 14, 2024 1:16 PM (GMT+9) | |
January 12, 2024 11:43 PM (GMT+9) | |
January 12, 2024 8:31 PM (GMT+9) | |
January 12, 2024 5:27 PM (GMT+9) | |
January 12, 2024 11:42 AM (GMT+9) | |
January 11, 2024 9:22 PM (GMT+9) | |
January 11, 2024 9:14 PM (GMT+9) | |
January 11, 2024 2:00 PM (GMT+9) | |
January 11, 2024 1:59 PM (GMT+9) | |
January 11, 2024 1:43 PM (GMT+9) | |
January 11, 2024 1:31 PM (GMT+9) | |
January 11, 2024 1:12 PM (GMT+9) | |
January 11, 2024 1:09 PM (GMT+9) | |
January 11, 2024 11:35 AM (GMT+9) | |
January 11, 2024 11:04 AM (GMT+9) | |
January 10, 2024 9:11 PM (GMT+9) | |
January 10, 2024 9:07 PM (GMT+9) | |
January 10, 2024 8:48 PM (GMT+9) | |
January 10, 2024 8:38 PM (GMT+9) | |
January 10, 2024 4:37 PM (GMT+9) | |
January 10, 2024 3:29 PM (GMT+9) | |
January 10, 2024 11:41 AM (GMT+9) | |
January 10, 2024 11:29 AM (GMT+9) | |
January 9, 2024 10:00 PM (GMT+9) | |
January 9, 2024 7:10 PM (GMT+9) | |
January 9, 2024 11:16 AM (GMT+9) | |
January 9, 2024 9:38 AM (GMT+9) | |
January 8, 2024 11:23 PM (GMT+9) | |
January 8, 2024 11:21 PM (GMT+9) | |
January 8, 2024 10:37 PM (GMT+9) | |
January 8, 2024 9:46 PM (GMT+9) | |
January 7, 2024 9:14 PM (GMT+9) | |
January 7, 2024 9:01 PM (GMT+9) | |
January 7, 2024 8:53 PM (GMT+9) | |
January 7, 2024 7:50 PM (GMT+9) | |
January 7, 2024 6:34 PM (GMT+9) | |
January 7, 2024 5:35 PM (GMT+9) | |
January 7, 2024 5:35 PM (GMT+9) | |
January 7, 2024 5:35 PM (GMT+9) | |
January 7, 2024 5:35 PM (GMT+9) | |
January 7, 2024 5:34 PM (GMT+9) | |
January 7, 2024 5:34 PM (GMT+9) | |
January 7, 2024 5:34 PM (GMT+9) | |
January 7, 2024 5:34 PM (GMT+9) | |
January 7, 2024 5:33 PM (GMT+9) | |
January 7, 2024 5:33 PM (GMT+9) | |
January 7, 2024 5:33 PM (GMT+9) | |
January 7, 2024 5:32 PM (GMT+9) | |
January 7, 2024 5:32 PM (GMT+9) | |
January 7, 2024 5:32 PM (GMT+9) | |
January 7, 2024 5:31 PM (GMT+9) | |
January 7, 2024 5:31 PM (GMT+9) | |
January 7, 2024 5:30 PM (GMT+9) | |
January 7, 2024 5:30 PM (GMT+9) | |
January 1, 2024 1:45 PM (GMT+9) | |
December 31, 2023 10:11 PM (GMT+9) | |
December 31, 2023 10:00 PM (GMT+9) | |
December 31, 2023 8:12 PM (GMT+9) | |
December 31, 2023 1:04 AM (GMT+9) | |
December 31, 2023 1:03 AM (GMT+9) | |
December 31, 2023 1:02 AM (GMT+9) | |
December 31, 2023 1:01 AM (GMT+9) | |
December 31, 2023 1:00 AM (GMT+9) | |
December 31, 2023 12:59 AM (GMT+9) | |
December 31, 2023 12:59 AM (GMT+9) | |
December 31, 2023 12:58 AM (GMT+9) | |
December 31, 2023 12:56 AM (GMT+9) | |
December 31, 2023 12:56 AM (GMT+9) | |
December 30, 2023 11:25 PM (GMT+9) | |
December 30, 2023 8:37 PM (GMT+9) | |
December 30, 2023 8:09 PM (GMT+9) | |
December 30, 2023 7:28 PM (GMT+9) | |
December 30, 2023 4:32 PM (GMT+9) | |
December 30, 2023 11:02 AM (GMT+9) | |
December 29, 2023 11:35 PM (GMT+9) | |
December 29, 2023 5:58 PM (GMT+9) | |
December 28, 2023 3:45 PM (GMT+9) | |
December 28, 2023 1:46 PM (GMT+9) | |
December 28, 2023 12:32 PM (GMT+9) | |
December 28, 2023 11:47 AM (GMT+9) | |
December 27, 2023 12:25 PM (GMT+9) | |
December 27, 2023 9:28 AM (GMT+9) | |
December 26, 2023 2:22 PM (GMT+9) | |
December 26, 2023 11:43 AM (GMT+9) | |
December 26, 2023 11:40 AM (GMT+9) | |
December 26, 2023 11:37 AM (GMT+9) | |
December 26, 2023 11:02 AM (GMT+9) | |
December 26, 2023 10:35 AM (GMT+9) | |
December 26, 2023 1:15 AM (GMT+9) | |
December 25, 2023 11:51 PM (GMT+9) | |
December 25, 2023 11:50 PM (GMT+9) | |
December 25, 2023 11:47 PM (GMT+9) | |
December 25, 2023 11:47 PM (GMT+9) | |
December 25, 2023 11:44 PM (GMT+9) | |
December 25, 2023 2:42 PM (GMT+9) | |
December 25, 2023 12:17 PM (GMT+9) | |
December 25, 2023 12:16 PM (GMT+9) | |
December 25, 2023 9:50 AM (GMT+9) | |
December 25, 2023 9:49 AM (GMT+9) | |
December 25, 2023 9:48 AM (GMT+9) | |
December 25, 2023 9:48 AM (GMT+9) | |
December 25, 2023 9:48 AM (GMT+9) | |
December 25, 2023 9:47 AM (GMT+9) | |
December 24, 2023 11:11 AM (GMT+9) | |
December 24, 2023 2:57 AM (GMT+9) | |
December 24, 2023 2:42 AM (GMT+9) | |
December 24, 2023 2:38 AM (GMT+9) | |
December 24, 2023 2:05 AM (GMT+9) | |
December 24, 2023 1:59 AM (GMT+9) | |
December 24, 2023 1:38 AM (GMT+9) | |
December 24, 2023 1:33 AM (GMT+9) | |
December 24, 2023 1:21 AM (GMT+9) | |
December 23, 2023 12:23 AM (GMT+9) | |
December 23, 2023 12:21 AM (GMT+9) | |
December 22, 2023 8:00 PM (GMT+9) | |
December 22, 2023 7:38 PM (GMT+9) | |
December 21, 2023 5:04 PM (GMT+9) | |
December 21, 2023 10:22 AM (GMT+9) | |
December 21, 2023 10:21 AM (GMT+9) | |
December 20, 2023 4:42 PM (GMT+9) | |
December 20, 2023 4:41 PM (GMT+9) | |
December 20, 2023 4:39 PM (GMT+9) | |
December 19, 2023 6:26 PM (GMT+9) | |
December 19, 2023 6:17 PM (GMT+9) | |
December 19, 2023 6:11 PM (GMT+9) | |
December 18, 2023 4:49 PM (GMT+9) | |
December 18, 2023 12:50 PM (GMT+9) | |
December 17, 2023 12:09 AM (GMT+9) | |
December 16, 2023 10:08 PM (GMT+9) | |
December 16, 2023 9:22 PM (GMT+9) | |
December 16, 2023 8:32 PM (GMT+9) | |
December 16, 2023 8:28 PM (GMT+9) | |
December 15, 2023 6:32 PM (GMT+9) | |
December 15, 2023 4:16 PM (GMT+9) | |
December 15, 2023 3:39 PM (GMT+9) | |
December 15, 2023 10:18 AM (GMT+9) | |
December 15, 2023 12:08 AM (GMT+9) | |
December 14, 2023 9:23 PM (GMT+9) | |
December 14, 2023 10:39 AM (GMT+9) | |
December 14, 2023 10:38 AM (GMT+9) | |
December 14, 2023 10:13 AM (GMT+9) | |
December 14, 2023 9:57 AM (GMT+9) | |
December 13, 2023 5:59 PM (GMT+9) | |
December 13, 2023 5:58 PM (GMT+9) | |
December 13, 2023 5:56 PM (GMT+9) | |
December 13, 2023 5:55 PM (GMT+9) | |
December 13, 2023 5:47 PM (GMT+9) | |
December 13, 2023 9:53 AM (GMT+9) | |
December 12, 2023 5:59 PM (GMT+9) | |
December 12, 2023 5:42 PM (GMT+9) | |
December 12, 2023 5:01 PM (GMT+9) | |
December 12, 2023 4:51 PM (GMT+9) | |
December 12, 2023 9:58 AM (GMT+9) | |
December 11, 2023 7:53 PM (GMT+9) | |
December 11, 2023 7:41 PM (GMT+9) | |
December 11, 2023 5:47 PM (GMT+9) | |
December 11, 2023 2:13 PM (GMT+9) | |
December 11, 2023 1:12 PM (GMT+9) | |
December 11, 2023 10:45 AM (GMT+9) | |
December 9, 2023 10:44 PM (GMT+9) | |
December 9, 2023 10:29 PM (GMT+9) | |
December 9, 2023 2:48 PM (GMT+9) | |
December 9, 2023 2:44 PM (GMT+9) | |
December 9, 2023 2:42 PM (GMT+9) | |
December 9, 2023 2:40 PM (GMT+9) | |
December 9, 2023 2:39 PM (GMT+9) | |
December 8, 2023 10:07 PM (GMT+9) | |
December 8, 2023 7:57 PM (GMT+9) | |
December 7, 2023 12:14 AM (GMT+9) | |
December 6, 2023 4:00 PM (GMT+9) | |
December 6, 2023 12:20 PM (GMT+9) | |
December 6, 2023 11:31 AM (GMT+9) | |
December 6, 2023 10:25 AM (GMT+9) | |
December 6, 2023 10:25 AM (GMT+9) | |
December 6, 2023 10:24 AM (GMT+9) | |
December 5, 2023 1:22 PM (GMT+9) | |
December 5, 2023 12:03 PM (GMT+9) | |
December 5, 2023 11:50 AM (GMT+9) | |
December 4, 2023 3:39 PM (GMT+9) | |
December 4, 2023 11:13 AM (GMT+9) | |
December 4, 2023 11:10 AM (GMT+9) | |
December 2, 2023 10:37 PM (GMT+9) | |
December 2, 2023 10:35 PM (GMT+9) | |
December 2, 2023 10:20 PM (GMT+9) | |
December 2, 2023 10:19 PM (GMT+9) | |
December 2, 2023 3:38 PM (GMT+9) | |
December 1, 2023 5:46 PM (GMT+9) | |
December 1, 2023 5:07 PM (GMT+9) | |
December 1, 2023 1:37 PM (GMT+9) | |
December 1, 2023 12:15 AM (GMT+9) | |
November 30, 2023 1:56 PM (GMT+9) | |
November 30, 2023 1:54 PM (GMT+9) | |
November 30, 2023 1:53 PM (GMT+9) | |
November 30, 2023 1:52 PM (GMT+9) | |
November 30, 2023 1:51 PM (GMT+9) | |
November 30, 2023 1:47 PM (GMT+9) | |
November 30, 2023 1:46 PM (GMT+9) | |
November 30, 2023 1:45 PM (GMT+9) | |
November 30, 2023 12:36 PM (GMT+9) | |
November 28, 2023 10:36 PM (GMT+9) | |
November 28, 2023 10:34 PM (GMT+9) | |
November 28, 2023 10:29 PM (GMT+9) | |
November 28, 2023 10:28 PM (GMT+9) | |
November 28, 2023 10:25 PM (GMT+9) | |
November 28, 2023 10:23 PM (GMT+9) | |
November 28, 2023 10:16 PM (GMT+9) | |
November 28, 2023 10:01 PM (GMT+9) | |
November 28, 2023 9:54 PM (GMT+9) | |
November 28, 2023 8:52 PM (GMT+9) | |
November 28, 2023 8:46 PM (GMT+9) | |
November 28, 2023 4:46 PM (GMT+9) | |
November 27, 2023 10:38 AM (GMT+9) | |
November 27, 2023 1:57 AM (GMT+9) | |
November 27, 2023 12:42 AM (GMT+9) | |
November 26, 2023 8:34 PM (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) |