Выгрузка статистики во внешнюю таблицу
Всё, что записывает Qubix, принадлежит вам, и иногда эти же числа должны лежать ещё где-то: в общей таблице, которую команда держит открытой весь день, в финансовом отчёте, на панели, которую кто-то уже собрал.
Это закрывается скриптом. Он читает срез, который вы опишете, отправляет его по адресу, принимающему строки, и запоминает часы, ушедшие целиком, — поэтому повторный прогон после сбоя дозаполняет пропуск, а не начинает всё заново. Час, оборванный посередине, отправляется заново с начала, поэтому приёмник обязан переживать повтор одного и того же часа.
Речь о том, чтобы отдать числа наружу. Смотреть их внутри Qubix и так есть где — начните с раздела Мои панели.
Шаг 1. Подготовьте приёмник
Скрипт отправляет строки по HTTP, поэтому на той стороне нужен адрес, который их примет. Два обычных случая:
- Таблица. Google Sheets не принимает строки напрямую — рядом с таблицей публикуется небольшое веб-приложение Apps Script, и оно записывает пришедшее в строки. Скрипт отправляет на адрес этого веб-приложения.
- Что угодно ещё, что говорит по HTTP — хранилище, внутренняя служба, приёмник на конструкторе.
Что бы вы ни выбрали, адрес должен быть разрешён для исходящих запросов: администратор добавляет его узел в Система → вкладка JavaScript. До этого скрипт остановится на первой же отправке и скажет об этом в консоли:
Send failed, stopping: host "script.google.com" not in allowlist
Шаг 2. Посмотрите на срез, прежде чем его выгружать
Откройте любой скрипт и сначала выполните один запрос — это ничего не стоит и показывает ровно то, что ляжет в таблицу. Виды событий — в Справочнике событий.
function main() {
const rows = sql`
SELECT toStartOfHour(event_time) AS hour,
geo AS country,
campaign_id,
event,
count() AS n
FROM qubix_events
WHERE event_time > now() - INTERVAL 6 HOUR
AND event IN ('campaign_visit', 'reg', 'dep')
GROUP BY hour, country, campaign_id, event
ORDER BY hour, country
LIMIT 20`
for (const r of rows) console.log(r.hour, r.country, r.campaign_id, r.event, r.n)
}
Меняйте группировку, пока вывод не станет той таблицей, которая вам нужна. Какие таблицы и колонки доступны запросу — в разделе Таблицы для sql-запросов.
Шаг 3. Сам скрипт
Поставьте расписание 5 * * * * — пять минут каждого часа. Скрипт выгружает только целые часы, поэтому запуск чуть позже смены часа означает, что взятый час уже закрыт.
// ════════════════════════════════════════════════════════════════════════════
// Отправка среза вашей статистики во внешнюю таблицу.
//
// Каждый час скрипт выясняет, какие часы ещё не выгружены, читает их одним
// запросом и отправляет приёмнику пачками. Запоминаются только часы, ушедшие
// целиком, поэтому ничего не теряется. Час, оборванный сбоем, на следующем
// прогоне уходит заново с начала — приёмник обязан переживать повтор одного и
// того же часа.
//
// Скрипт — это ровно одна `function main()`, внутри которой лежит всё
// остальное: такую форму принимает редактор.
// ════════════════════════════════════════════════════════════════════════════
function main() {
// ─── Всё, что вы меняете под себя, лежит здесь ────────────────────────────
const options = {
// Что выгружаем
slice: {
// Какое окно охватываем. Скрипт выгружает только целые часы, поэтому
// прогон посреди часа никогда не отправит наполовину заполненный.
hoursBack: 24,
// Какие события считаем. Виды событий: /reference/events
countEvents: ['campaign_visit', 'reg', 'dep'],
},
// Куда уходят строки
send_to: {
// Адрес, принимающий строки. Администратор обязан разрешить этот узел
// в разделе «Система» → «JavaScript», иначе запрос будет отклонён, а
// причина напечатана в консоли.
url: 'https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec',
// Уходят заголовками; сюда же кладут ключ доступа, если приёмник просит.
headers: { 'Content-Type': 'application/json' },
// Сколько строк уходит в одном запросе. Приёмник таблицы давится
// тысячами строк в одном теле, а несколькими запросами поменьше — нет.
// Час, в котором строк больше этого числа, режется на несколько запросов,
// поэтому сбой внутри него повторяет весь час на следующем прогоне.
rowsPerRequest: 200,
},
run: {
// Запоминаются часы, ушедшие целиком, поэтому повторный прогон после сбоя
// досылает только недостающее. Час, прерванный посередине, не запоминается
// и потому повторяется целиком.
stateKey: 'exported_hours',
// Сколько часов держать в этой памяти, прежде чем забыть.
rememberHours: 168,
// Сколько ждать ответа приёмника, прежде чем считать запрос неудавшимся.
requestTimeoutMs: 15000,
// НЕ ДОЛЖНО превышать user_scripts.sql_max_rows в разделе «Система» →
// «JavaScript». Запрос режется на том числе строк, и об усечении движок
// сообщает только в консоль прогона — скрипту приходит короткий перечень
// без признака, поэтому заметить срез может лишь это число. Поставите ВЫШЕ
// настройки — проверка умрёт молча: движок режет, признак не срабатывает
// никогда, а недостающие строки помечаются выгруженными. Ниже настройки он
// лишь срабатывает раньше времени, и это ничего не стоит.
maxRowsPerRun: 10000,
},
}
// ─── 1. Какие часы ещё не отправлены ──────────────────────────────────────
// Работаем целыми часами: текущий, незакрытый час намеренно оставлен
// следующему прогону. Телеметрия пишется с задержкой, поэтому только что
// начавшийся час всегда неполон — выгрузив его, вы положили бы в таблицу
// числа, которые продолжат расти уже после того, как туда попали.
const nowHour = Math.floor(Date.now() / 3600000)
const wanted = []
for (let h = nowHour - options.slice.hoursBack; h < nowHour; h++) wanted.push(h)
const alreadySent = ctx.state.get(options.run.stateKey) || []
const sentSet = {}
for (const h of alreadySent) sentSet[h] = true
const todo = wanted.filter(function (h) { return !sentSet[h] })
if (!todo.length) {
console.log('Every hour in the window has already been exported.')
return
}
console.log('Hours to export:', todo.length, '(of', wanted.length, 'in the window)')
// ─── 2. Читаем срез ───────────────────────────────────────────────────────
// Один запрос на весь диапазон, а не по запросу на час: у прогона потолок в
// двадцать запросов, и почасовой обход потратил бы его на одно только окно.
// Час входит в группировку, поэтому строки приходят уже разбитыми по часам.
const fromSec = todo[0] * 3600
const toSec = (todo[todo.length - 1] + 1) * 3600
// Колонки выписаны здесь, а не собраны из настройки, намеренно: значения,
// переданные через `${…}`, едут безопасными параметрами, поэтому могут нести
// дату или перечень — но никогда имя колонки. Меняйте срез правкой этого
// запроса; он же единственное место, где видно, что именно отправляется.
const rows = sql`
SELECT toStartOfHour(event_time) AS hour,
geo AS country,
campaign_id,
event,
count() AS n
FROM qubix_events
WHERE event_time >= toDateTime(${fromSec})
AND event_time < toDateTime(${toSec})
AND event IN (${options.slice.countEvents})
GROUP BY hour, country, campaign_id, event
ORDER BY hour, country`
const truncated = rows.length >= options.run.maxRowsPerRun
console.log('Rows read:', rows.length, truncated ? '(ceiling hit)' : '')
if (!rows.length) {
console.log('Nothing recorded in those hours — nothing to send.')
return
}
// ─── 3. Отправляем пачками ────────────────────────────────────────────────
let batch = []
let everythingWentOut = true
// Час последней ушедшей строки, И ТОЛЬКО когда пачка кончилась на границе
// часа. Пачка закрывается на смене часа либо по размеру, поэтому полная пачка
// может оборваться посреди часа — такой час здесь не отмечается, иначе его
// остаток не ушёл бы никогда. При попадании в потолок строк граничный час
// тоже неполон, и он исключается ниже.
let lastRowSent = null
for (let i = 0; i < rows.length; i++) {
batch.push(rows[i])
const isLast = i === rows.length - 1
const hourEnds = isLast || rows[i + 1].hour !== rows[i].hour
if (batch.length < options.send_to.rowsPerRequest && !hourEnds) continue
let res
try {
res = ctx.fetch(options.send_to.url, {
method: 'POST',
headers: options.send_to.headers,
body: JSON.stringify({ rows: batch }),
timeout_ms: options.run.requestTimeoutMs,
})
} catch (e) {
// Адреса нет в списке разрешённых либо приёмник недостижим. Здесь
// останавливаемся: часы этой пачки не запоминаются, поэтому следующий
// прогон возьмёт их снова, а не потеряет.
console.log('Send failed, stopping:', e.message)
everythingWentOut = false
break
}
if (res.status >= 400) {
console.log('Receiver refused with', res.status, '- stopping so nothing is lost')
everythingWentOut = false
break
}
console.log('Sent', batch.length, 'rows, receiver answered', res.status)
if (hourEnds) lastRowSent = Math.floor(new Date(batch[batch.length - 1].hour + 'Z').getTime() / 3600000)
batch = []
}
// ─── 4. Запоминаем ушедшее ────────────────────────────────────────────────
// Записываются только те часы, строки которых действительно ушли, поэтому
// сбой на середине оставляет остаток следующему прогону, а не пропускает
// его молча.
const sentHours = (everythingWentOut && !truncated)
? todo
: todo.filter(function (h) {
if (lastRowSent === null) return false
return truncated ? h < lastRowSent : h <= lastRowSent
})
if (!everythingWentOut || truncated) {
console.log('Hours confirmed sent:', sentHours.length, 'of', todo.length,
'- the rest stay for the next run')
}
const keep = alreadySent.concat(sentHours)
.filter(function (h) { return h > nowHour - options.run.rememberHours })
ctx.state.set(options.run.stateKey, keep)
console.log('Remembered hours:', keep.length)
}
На чём построен скрипт
Только целые часы, никогда наполовину. Телеметрия приходит с задержкой, поэтому только что начавшийся час всегда неполон. Выгрузив его, вы положили бы в свою таблицу числа, которые продолжат расти уже после того, как туда попали. Текущий час оставлен следующему прогону — поэтому расписание и срабатывает через несколько минут после смены часа.
Окно, а не шагающая отметка. Скрипт спрашивает «какие из последних 24 часов ещё не ушли», вместо того чтобы держать позицию, которая двигается вперёд. Шагающая вперёд отметка перешагнёт событие, пришедшее с опозданием, и назад за ним уже не вернётся.
Один запрос на весь диапазон. У прогона потолок в двадцать запросов. Почасовое чтение потратило бы его на одно только окно и не оставило бы ничего на остальное.
Сбой ничего не теряет. Час записывается только тогда, когда запрос кончился ровно на его границе, то есть ушли все его строки. Если приёмник недостижим или ответил отказом, скрипт останавливается, запоминает только закончившиеся часы, а остаток берёт следующий прогон. Час, оборванный посередине, повторяется целиком, поэтому приёмник должен переживать повтор: скрипт таблицы, заменяющий строки по часу, или точка, которая просто не замечает повтора.
Потолок строк замечается, а не проглатывается. Запрос режется на user_scripts.sql_max_rows строках, и об усечении движок сообщает только в консоль прогона — самому скрипту приходит короткий перечень без признака. Поэтому скрипт сверяет полученное с maxRowsPerRun сам, и потому это число обязано совпадать с настройкой: при попадании печатает (ceiling hit), считает граничный час незаконченным и оставляет остаток окна следующему прогону. Видите эту строку часто — уменьшите hoursBack или сузьте группировку.
Что вы увидите в консоли
Обычный прогон:
Hours to export: 24 (of 24 in the window)
Rows read: 136
Sent 136 rows, receiver answered 200
Remembered hours: 24
Следующий прогон, часом позже, разберёт уже один час, а не двадцать четыре. А вот прогон, который не достучался до приёмника:
Hours to export: 24 (of 24 in the window)
Rows read: 136
Send failed, stopping: host "script.google.com" not in allowlist
Hours confirmed sent: 0 of 24 - the rest stay for the next run
Remembered hours: 0
Не запомнено ничего, поэтому следующий прогон начнёт с того же места.
Кнопка ▶ Запустить под редактором выполняет код на живых данных сразу — см. Проверка скрипта. Поставьте в url адрес своего приёмника и смотрите в консоль: строки либо дойдут, либо консоль назовёт причину.