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

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

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

Требования

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

Ограничения

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

Реализация

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

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

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

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

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

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

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

Установите для dataStudio.forceViewersCredentials значение true.
Установите для dataStudio.useQueryConfig значение true.
Для 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"
    ],
  ...
}

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

Создайте сервисный аккаунт в проекте Google Cloud. Это будет платежный проект.
Убедитесь, что у этого сервисного аккаунта есть доступ к BigQuery в облачном проекте.
- Требуемые роли для управления учетными данными и доступом: BigQuery Data Viewer, BigQuery Job User.
Скачайте файл JSON, чтобы получить ключи сервисного аккаунта. Сохраните их в свойствах скрипта проекта коннектора.
Добавьте в проект Apps Script библиотеку OAuth2 для Apps Script.

Внедрите необходимый код 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()`

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

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

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

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

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

Пример кода

Разверните раздел, чтобы просмотреть пример полного кода коннектора.

main.js

var cc = DataStudioApp.createCommunityConnector();
var scriptProperties = PropertiesService.getScriptProperties();

function isAdminUser() {
  return true;
}

function getAuthType() {
  var AuthTypes = cc.AuthType;
  return cc
    .newAuthTypeResponse()
    .setAuthType(AuthTypes.NONE)
    .build();
}

function getConfig(request) {
  var config = cc.getConfig();

  config
    .newInfo()
    .setId('generalInfo')
    .setText('This is an example connector to showcase row level security.');

  return config.build();
}

function getFields() {
  var fields = cc.getFields();
  var types = cc.FieldType;
  var aggregations = cc.AggregationType;

  fields
    .newDimension()
    .setId('region')
    .setName('Region')
    .setType(types.TEXT);

  fields
    .newMetric()
    .setId('sales')
    .setName('Sales')
    .setType(types.NUMBER)
    .setAggregation(aggregations.SUM);

  fields
    .newDimension()
    .setId('date')
    .setName('Date')
    .setType(types.YEAR_MONTH_DAY);

  return fields;
}

function getSchema(request) {
  return {schema: getFields().build()};
}

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']);
}

var BASE_SQL =
  'SELECT d.region, d.sales, d.date ' +
  'FROM `datastudio-solutions.row_level_security.data` d ' +
  'INNER JOIN `datastudio-solutions.row_level_security.access` a ' +
  'ON d.region = a.region ' +
  'where a.email=@email';

function getData(request) {
  var accessToken = getOauthService().getAccessToken();
  var serviceAccountCreds = getServiceAccountCreds();
  var billingProjectId = serviceAccountCreds[BILLING_PROJECT_ID];
  var email = Session.getEffectiveUser().getEmail();

  var bqTypes = DataStudioApp.createCommunityConnector().BigQueryParameterType;

  return cc
    .newBigQueryConfig()
    .setAccessToken(accessToken)
    .setBillingProjectId(billingProjectId)
    .setUseStandardSql(true)
    .setQuery(BASE_SQL)
    .addQueryParameter('email', bqTypes.STRING, email)
    .build();
}