Как бесплатно экспортировать запросы PostgreSQL в Google Sheets

Пару лет назад я и не подозревал, что подключение базы данных PostgreSQL к Google Sheets может стоить так дорого. Несмотря на тривиальную проблему, существующие рыночные решения, такие как Zapier, KPIBees и т. д., требуют от нас дополнительных затрат.

TL;DR В этой статье я пишу о том, как мне удалось экспортировать запросы PostgreSQL в Google Таблицы через GitHub (и немного сценариев Bash).

Зачем мне это нужно

Вот немного контекста.

Я поддерживаю крошечный побочный проект под названием Fareview — инструмент коммерческого мониторинга цен на пиво, который собирает данные о коммерческом пиве с сайтов электронной коммерции Сингапура и сохраняет их в базе данных PostgreSQL.

Затем сводка собранных данных ежедневно синхронизируется с Google Таблицами для просмотра пользователями.

Вместо того, чтобы платить за ежемесячную премию, я решил использовать GitHub Action, чтобы помочь мне с такой задачей бесплатно.

Вот как это работает

Этот метод также должен работать с любыми другими базами данных SQL (например, MySQL) с интерфейсом командной строки, например psql.

  1. Создайте простой сценарий Bash, который использует CLI клиента Postgres (psql) для выполнения запросов SQL и вывода их в формате файла CSV с нашего сервера базы данных PostgreSQL.
  2. Настройте рабочий процесс GitHub Actions, который запускает сценарий Bash на шаге 1 и фиксирует сгенерированный файл в нашем репозитории по расписанию Cron.
  3. В Google Таблицах используйте функцию =IMPORTDATA("<url-of-csv-file>") для импорта наших данных CSV из нашего репозитория в наши Google Таблицы.

Важно отметить, что функция IMPORTDATA автоматически обновляет данные с интервалом до 1 часа. Если вам нужен вариант использования с более коротким интервалом, возможно, вам придется обойти его.

Вот шаги, чтобы сделать это

Баш-скрипт

В зависимости от вашего варианта использования вам может даже не понадобиться сценарий Bash. Например, вы можете просто запустить команду psql в качестве одного из шагов рабочего процесса GitHub Actions.

Использование сценария Bash здесь обеспечивает большую гибкость, поскольку вы также можете запустить его вручную вне GitHub Actions, если вам это нужно.

Это скрипт Bash (generate_csv.sh), который я запускаю:

#!/bin/bash
BRANDS=("carlsberg" "tiger" "heineken" "guinness" "asahi")
PGDATABASE="${PGDATABASE-fareview}"
PGHOST="${PGHOST-localhost}"
PGPASSWORD="${PGPASSWORD-}"
PGPORT="${PGPORT-5432}"
PGUSER="${PGUSER-postgres}"
mkdir -p data/
for brand in "${BRANDS[@]}"; do
    PGPASSWORD=$PGPASSWORD psql -v brand="'${brand}'" -h "$PGHOST" -U "$PGUSER" -d "$PGDATABASE" -F ',' -A --pset footer -f alembic/examples/get_all_by_brand.sql >"data/${brand}.csv"
done

Копировать

  • Простой сценарий использует команду psql для запуска SQL-запроса из файла .sql с режимом вывода таблицы CSV (флаг -A)
  • Вывод этой команды сохраняется в файле CSV в каталоге data репозитория Git.
  • Скрипт получает все необходимые настройки базы данных из переменных окружения
  • В наших действиях GitHub мы собираемся установить эти переменные среды из секретов нашего репозитория (примечание: нам нужно будет добавить эти переменные среды в наш репозиторий самостоятельно)

Вот постоянная ссылка на скрипт Bash.

Рабочий процесс действий GitHub

Почему действия GitHub?

Рабочий процесс GitHub Actions поддерживает запуск по расписанию Cron. По сути, это означает, что мы можем запланировать выполнение нашего задания (то есть нашего сценария в данном случае) так, чтобы оно выполнялось с интервалом в 5 минут.

В нашем случае мы можем использовать это для ежедневного экспорта нашего запроса PostgreSQL в наши таблицы Google.

Начнем с создания файла рабочего процесса generate_csv.yml в папке .github/workflows каталога нашего проекта:

name: Generate CSV
on:
    workflow_dispatch:
    schedule:
        - cron: "30 23 * * *" # At 23:30 UTC daily

Копировать

  • workflow_dispatch добавлено, чтобы мы могли вручную запускать наш рабочий процесс из GitHub API, CLI или пользовательского интерфейса браузера.
  • Проверьте Crontab Guru для синтаксиса расписания Cron

Далее, чтобы подключиться к любой базе данных, нам нужно передать параметры подключения:

name: Generate CSV
on:
    workflow_dispatch:
    schedule:
        - cron: "30 23 * * *" # <https://crontab.guru/#30_23_*_*_*>
env:
    PGDATABASE: ${{ secrets.PGDATABASE }}
    PGHOST: ${{ secrets.PGHOST }}
    PGPASSWORD: ${{ secrets.PGPASSWORD }}
    PGPORT: ${{ secrets.PGPORT }}
    PGUSER: ${{ secrets.PGUSER }}

Копировать

В той же настройке секретов репозитория проекта GitHub введите соответствующие переменные среды в разделе «Действия»:

Наконец, давайте создадим задание для экспорта наших запросов PostgreSQL и фиксации их в нашем репозитории.

Задание в рабочем процессе GitHub Actions может содержать много шагов. Различные шаги в GitHub Actions также выполняются в разных контейнерах.

  1. Первый шаг — настроить и установить PostgreSQL в контейнер шага.
  2. Далее мы добавим шаг для запуска нашего Bash-скрипта, который запускает SQL-запрос из файла.
  3. Необязательно: получите локальную дату и время, чтобы мы могли использовать их как часть нашего сообщения фиксации на шаге 4.
  4. Зафиксируйте сгенерированный файл CSV из шага 2 в наш репозиторий. Здесь я использую Добавить и зафиксировать действия GitHub, чтобы зафиксировать изменения в файле CSV.

Зачем использовать флаг --autostash с git pull? Это позволяет нам автоматически сохранять и извлекать ожидающие изменения файла CSV перед фиксацией и отправкой их в репозиторий. Это помогает нам обойти проблемы фиксации Git, из-за которых другие разработчики могут вносить новые изменения в код во время выполнения этого задания.

Вот и все! теперь у нас есть задание Cron, которое запускается каждый день для обновления нашего CSV-файла, чтобы наши таблицы Google могли их импортировать.

Закрытие через

Иметь GitHub — высокодоступный сервис для размещения нашего CSV-файла для нас — это здорово. Более того, бесплатный хостинг на GitHub выглядит как обман.

Я также использовал аналогичный подход, чтобы выполнить задание по поиску ресторанов гида Мишлен с веб-сайта гида Мишлен.

В качестве альтернативы я также рассматривал возможность использования API Google Sheets для синхронизации моих данных непосредственно с Google Sheets. Учитывая необходимые усилия по интеграции, я рад, что придерживаюсь этого очень простого метода.

Want to Connect?
This article was originally published at jerrynsh.com