Skip to content

Instantly share code, notes, and snippets.

@frankyaorenjie

frankyaorenjie/get_table_size.sql

Last active January 22, 2019 14:00
Show Gist options
  • Save frankyaorenjie/6a27decdf45a0988a54bf274046c013b to your computer and use it in GitHub Desktop.
Save frankyaorenjie/6a27decdf45a0988a54bf274046c013b to your computer and use it in GitHub Desktop.
Download ZIP
bigquery #bigquery
Raw
get_table_size.sql
SELECT
table_id,
SUM(size_bytes)/POW(10,9) AS size
FROM
`data-platform-163807.raw_logs.__TABLES__`
WHERE
table_id LIKE '%20190101'
GROUP BY
table_id
ORDER BY
2 DESC
Raw
index_query_log.sql
SELECT
im_url,
CONCAT(account_app.account_name, '@', account_app.app_name) AS account_app_name,
STRING_AGG(log_id, "|"),
STRING_AGG(FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", timestamp), "|")
FROM
`data-platform-163807.telescope.uploadsearch_history` AS uploadsearch_history
JOIN (
SELECT
account.account_id AS account_id,
app.app_id AS app_id,
account.name AS account_name,
app.name AS app_name
FROM
`data-platform-163807.etl_dashboard_production.vs_account_apps` AS account_apps
JOIN (
SELECT
account_id,
name,
id
FROM
`data-platform-163807.etl_dashboard_production.vs_account`
WHERE
account_id IS NOT NULL ) AS account
ON
account_apps.account_id = account.id
JOIN (
SELECT
app_id,
name,
id
FROM
`data-platform-163807.etl_dashboard_production.vs_app`
WHERE
deleted IS NULL
OR deleted = 0) AS app
ON
account_apps.app_id = app.id) AS account_app
ON
uploadsearch_history.app_id = CAST(account_app.app_id AS STRING)
WHERE
_PARTITIONTIME >= "2018-01-01 00:00:00"
AND _PARTITIONTIME < "2018-06-06 00:00:00"
AND uploadsearch_history.account_id != '1218'
AND uploadsearch_history.im_url IS NOT NULL
AND uploadsearch_history.im_url != ''
GROUP BY
im_url,
account_app_name
Raw
timestamp to string
FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", timestamp)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment