Фильтрация данных BigQuery для Студии данных на уровне строк

Предположим, вам необходимо настроить просмотр статистики крупной компании региональными менеджерами. Набор данных BigQuery содержит информацию о продажах по регионам и датам.

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

Требования

  • Читатель сводки вошел в аккаунт Google.
  • Адрес электронной почты пользователя можно сопоставить с доступными ему данными/строками.
  • Для доступа к данным BigQuery используется сервисный аккаунт (оплатой будет централизованно управлять поставщик сводки).

Ограничения

  • Каждый читатель сводки должен один раз пройти авторизацию (при первом просмотре).
  • Читатели не могут ни изменять сводку, ни делиться ею с другими.
  • Если вы используете G Suite и ваш администратор отключил доступ к файлам на Диске по ссылке, то вам нужно или снять это ограничение, или использовать для разработки аккаунт Gmail.

Реализация

Выполните указанные ниже действия.

Создайте коннектор с открытым кодом

Сначала мы рекомендуем посмотреть видео о принципах работы коннектора с открытым кодом и выполнить практическое задание. Воспользуйтесь также инструментом для создания коннекторов, который упростит вашу работу.

Напишите код коннектора

  1. Функция getAuthType() должна возвращать значение NONE.
  2. Функция getConfig() должна возвращать пустую конфигурацию.
    • Необязательно. Если вам необходимо задать для сводки особые настройки, вы можете запросить ввод данных пользователем.
  3. Функция getSchema() должна возвращать схему для запроса.
    • Необязательно. Если необходимо, добавьте в схему специальные поля или вычисления с помощью запроса SQL или вычисляемых полей.
  4. Настройку функции getData() мы выполним позже.

Внесите изменения в манифест

Прочитайте Справку по манифесту и укажите в нем всю необходимую информацию, в том числе следующую:

  1. Установите для dataStudio.forceViewersCredentials значение true.
  2. Установите для dataStudio.useQueryConfig значение true.
  3. Для oauthScopes добавьте https://www.googleapis.com/auth/userinfo.email и https://www.googleapis.com/auth/script.external_request. Подробнее…
    • По необходимости. Добавьте соответствующие области действия для всех сервисов, используемых в коннекторе.

Манифест должен выглядеть так:

{
  ...
  "dataStudio": {
    "forceViewersCredentials": true,
    "useQueryConfig": true
    ...
  }
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/userinfo.email"
    ],
  ...
}

Настройте сервисный аккаунт

  1. Создайте сервисный аккаунт в проекте Google Cloud. Это будет платежный проект.
  2. Убедитесь, что у этого сервисного аккаунта есть доступ к BigQuery в облачном проекте.
    • Требуемые роли для управления учетными данными и доступом: BigQuery Data Viewer, BigQuery Job User.
  3. Скачайте файл JSON, чтобы получить ключи сервисного аккаунта. Сохраните их в свойствах скрипта проекта коннектора.
  4. Добавьте в проект Apps Script библиотеку OAuth2 для Apps Script.
  5. Внедрите необходимый код OAuth2 для сервисного аккаунта:
    var SERVICE_ACCOUNT_CREDS = 'SERVICE_ACCOUNT_CREDS';
    var SERVICE_ACCOUNT_KEY = 'private_key';
    var SERVICE_ACCOUNT_EMAIL = 'client_email';
    var BILLING_PROJECT_ID = 'project_id';
    
    /**
     * Copy the entire credentials JSON file from creating a service account in GCP.
     */
    function getServiceAccountCreds() {
      return JSON.parse(scriptProperties.getProperty(SERVICE_ACCOUNT_CREDS));
    }
    
    function getOauthService() {
      var serviceAccountCreds = getServiceAccountCreds();
      var serviceAccountKey = serviceAccountCreds[SERVICE_ACCOUNT_KEY];
      var serviceAccountEmail = serviceAccountCreds[SERVICE_ACCOUNT_EMAIL];
    
      return OAuth2.createService('RowLevelSecurity')
        .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
        .setTokenUrl('https://accounts.google.com/o/oauth2/token')
        .setPrivateKey(serviceAccountKey)
        .setIssuer(serviceAccountEmail)
        .setPropertyStore(scriptProperties)
        .setCache(CacheService.getScriptCache())
        .setScope(['https://www.googleapis.com/auth/bigquery.readonly']);
    }
    

Настройте функцию getData()

  1. Создайте запрос BigQuery.
    • Определите соответствие между адресом электронной почты и данными.
    • Используйте условие JOIN и/или WHERE, чтобы отфильтровать данные.
  2. Получите адрес электронной почты действующего пользователя (см. Справку по идентификации пользователей).
  3. Включите дополнительные службы Студии данных, чтобы функция getData возвращала конфигурацию запроса.
    • Передайте составленный запрос, платежный проект и токен OAuth сервисного аккаунта.
    • По необходимости. Если функция getConfig передает данные, которые указывает пользователь, добавьте их в виде параметров BigQuery.

Создайте сводку

  1. Прочитайте статью о развертываниях и версиях коннектора.
  2. Создайте рабочее развертывание коннектора.
  3. С помощью рабочего развертывания создайте источник данных и отчет в Студии данных.
  4. Добавьте в отчет все необходимые таблицы и диаграммы.
  5. Сводка готова, вы можете открыть к ней доступ.

Предоставьте пользователям доступ к сводке

  1. Откройте доступ к скрипту коннектора выбранным пользователям или всем, у кого есть ссылка.
  2. Откройте доступ к сводке выбранным пользователям или всем, у кого есть ссылка.
  3. Необязательно. Создайте короткий URL сводки в сервисе URL Shortener. Поделитесь этим URL с пользователями. Это упростит замену URL в будущем (при необходимости).
  4. Необязательно. Настройте сбор статистики об использовании сводки.

Пример кода