Выполнение федеративных запросов в Google BigQuery

На этой странице описывается, как настроить федерацию Lakehouse для выполнения объединённых запросов к данным BigQuery, которые не управляются Azure Databricks. Дополнительные сведения о Федерации Lakehouse см. в статье "Подключение к внешним базам данных и каталогам"

Чтобы подключиться к вашей базе данных BigQuery с помощью федерации Lakehouse, необходимо создать следующее в хранилище метаданных Unity Catalog в Azure Databricks (рабочие области, созданные после 9 ноября 2023 года, уже автоматически обеспечены хранилищем метаданных Unity Catalog):

  • Подключение к базе данных BigQuery.
  • внешний каталог, который дублирует базу данных BigQuery в каталоге Unity, чтобы использовать синтаксис запросов и инструменты управления данными Unity Catalog для управления доступом пользователей Azure Databricks к базе данных.

Перед началом работы

Чтобы запустить федеративные запросы в BigQuery, создайте подключение к BigQuery и внешнему каталогу, который зеркально отражает базу данных BigQuery. Затем вы можете запрашивать данные BigQuery и управлять ими с помощью Azure Databricks и каталога Unity. Дополнительные требования к разрешениям указываются в каждом следующем разделе, основанном на задачах.

Требования к рабочей области:

  • Рабочая область подключена к каталогу Unity.

Требования к вычислениям:

  • Сетевое подключение вашего вычислительного ресурса к целевым системам баз данных. См. рекомендации по сетям для федерации Lakehouse.
  • Azure Databricks Compute должен использовать Databricks Runtime 16.1 или выше, а также стандартный или выделенный режим доступа (ранее общий и однопользовательский).
  • SQL-хранилища должны быть про- или безсерверными.

Требования к разрешениям:

  • Чтобы создать подключение, необходимо иметь CREATE CONNECTION привилегию в хранилище метаданных Unity Catalog, которое подключено к рабочей области.
  • Чтобы создать внешний каталог, необходимо иметь разрешение CREATE CATALOG на хранилище метаданных и либо быть владельцем подключения, либо иметь привилегию CREATE FOREIGN CATALOG подключения.

Создание подключения

Подключение задает путь и учетные данные для доступа к внешней системе базы данных. Чтобы создать подключение, можно использовать обозреватель каталогов или команду CREATE CONNECTION SQL в записной книжке Azure Databricks или редакторе sql-запросов Databricks.

Примечание.

Для создания подключения можно также использовать REST API Databricks или интерфейс командной строки Databricks. См. POST /api/2.1/unity-catalog/connections и команды каталога Unity .

Необходимые разрешения: администратор хранилища метаданных или пользователь с привилегиями CREATE CONNECTION .

Обозреватель каталогов

  1. В рабочей области Azure Databricks щелкните значок данных.Каталог.

  2. В верхней части области каталога щелкните значок " и выберите "Создать подключение" в меню.

  3. На странице основы подключения мастера настройки подключения введите понятное имя подключения.

  4. Выберите тип подключения Google BigQuery, а затем нажмите кнопку Далее.

  5. На странице Проверка подлинности введите JSON ключ учетной записи службы Google для экземпляра BigQuery.

    Это необработанный объект JSON, используемый для указания проекта BigQuery и предоставления проверки подлинности. Этот объект JSON можно создать и скачать на странице сведений о учетной записи службы в Google Cloud в разделе "КЛЮЧИ". Учетная запись службы должна иметь надлежащие разрешения, предоставленные в BigQuery, включая BigQuery User и BigQuery Data Viewer. Пример приведен ниже.

    {
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "PRIVATE_KEY",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL",
  "universe_domain": "googleapis.com"
}

    Примечание.

    Google задаёт значения URL в JSON-файле сервисного аккаунта, и они могут различаться в зависимости от аккаунта. Используйте их точно так же, как они отображаются в скачанном JSON-файле. Если вы настраиваете правила сетевого прокси для Azure Databricks для обращения к API Google, разрешите как https://accounts.google.com, так и https://oauth2.googleapis.com.

  6. (Необязательно) Введите идентификатор проекта для вашей инстанции BigQuery.

    Это имя проекта BigQuery, используемого для выставления счетов для всех запросов, выполняемых в этом соединении. По умолчанию используется ID проекта служебного аккаунта. Учетная запись службы должна иметь соответствующие разрешения, предоставленные для этого проекта в BigQuery, включая пользователя BigQuery. В этом проекте может быть создан дополнительный набор данных, используемый для хранения временных таблиц BigQuery.

  7. (Необязательно) Добавьте комментарий.

  8. Щелкните Создать подключение.

  9. На странице основы каталога введите имя внешнего каталога. Внешний каталог зеркально отражает базу данных во внешней системе данных, чтобы можно было запрашивать и управлять доступом к данным в этой базе данных с помощью Azure Databricks и каталога Unity.

  10. (Необязательно) Щелкните Проверить подключение, чтобы убедиться, что оно работает.

  11. Щелкните Создать каталог.

  12. На странице Access выберите рабочие области, в которых пользователи могут получить доступ к созданному каталогу. Вы можете выбрать параметр Все рабочие области имеют доступили нажать Назначить рабочим областям, выбрать рабочие области, а затем нажать Назначить.

  13. Измените владельца, который сможет управлять доступом ко всем объектам в каталоге. Начните вводить субъект в текстовом поле, а затем щелкните субъект в возвращенных результатах.

  14. Предоставьте привилегии на каталог. Щелкните Предоставить:

    1. Укажите участников , у которых будет доступ к объектам в каталоге. Начните вводить субъект в текстовом поле, а затем щелкните субъект в возвращенных результатах.
    2. Выберите предустановки привилегий , чтобы предоставить их каждому принципалу. Всем пользователям учетной записи предоставлено BROWSE по умолчанию.
      • Выберите считыватель данных в раскрывающемся меню, чтобы предоставить read привилегии на объекты в каталоге.
      • Выберите редактор данных в раскрывающемся меню, чтобы предоставить read и modify привилегии для объектов в каталоге.
      • Вручную выберите привилегии для предоставления.
    3. Щелкните Предоставить.

  15. Нажмите кнопку Далее.

  16. На странице метаданных введите пары тегов "ключ-значение". Дополнительные сведения см. в статье Применение тегов к защищаемым объектам каталога Unity.

  17. (Необязательно) Добавьте комментарий.

  18. Нажмите кнопку Сохранить.

SQL

Выполните следующую команду в записной книжке или редакторе sql-запросов Databricks. Замените <GoogleServiceAccountKeyJson> необработанный объект JSON, указывающий проект BigQuery и предоставляющий проверку подлинности. Этот объект JSON можно создать и скачать на странице сведений о учетной записи службы в Google Cloud в разделе "КЛЮЧИ". Учетная запись службы должна иметь соответствующие разрешения, предоставленные в BigQuery, включая пользователя BigQuery и средство просмотра данных BigQuery. Пример объекта JSON см. на вкладке обозревателя каталогов на этой странице.

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson '<GoogleServiceAccountKeyJson>'
);

Databricks рекомендует использовать секреты вместо строк открытого текста для конфиденциальных значений, таких как учетные данные. Например:

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson secret ('<secret-scope>','<secret-key-user>')
)

Сведения о настройке секретов см. в разделе "Управление секретами".

Создание внешнего каталога

Примечание.

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

Внешний каталог зеркально отражает базу данных во внешней системе данных, чтобы можно было запрашивать и управлять доступом к данным в этой базе данных с помощью Azure Databricks и каталога Unity. Чтобы создать внешний каталог, используйте подключение к источнику данных, который уже определен.

Чтобы создать внешний каталог, можно использовать обозреватель каталогов или CREATE FOREIGN CATALOG в записной книжке Azure Databricks или редакторе sql-запросов Databricks. Для создания каталога можно также использовать REST API Databricks или интерфейс командной строки Databricks. См. POST /api/2.1/unity-catalog/catalogs или команды каталога Unity .

Необходимые разрешения:CREATE CATALOG разрешение на хранилище метаданных и право владения подключением или CREATE FOREIGN CATALOG привилегией подключения.

Обозреватель каталогов

  1. В рабочей области Azure Databricks щелкните значок данных.Каталог , чтобы открыть обозреватель каталогов.

  2. В верхней части панели Каталог щелкните значок Добавить или плюси выберитеДобавить каталог в меню.

    Кроме того, на странице быстрого доступа нажмите кнопку "Каталогов", а затем нажмите кнопку "Создать каталог".

  3. (Необязательно) Введите следующее свойство каталога:

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

  4. Следуйте инструкциям, по созданию иностранных каталогов в Создание каталогов.

  5. (Необязательно) Укажите следующие параметры каталога:

    • Materialization Dataset: необязательное имя набора данных BigQuery, используемое для материализации результатов запроса. Если это не указано, при необходимости набор данных материализации подготавливается автоматически. Дополнительные сведения см. в разделе "Материализация ".
    • Force materialization: Нужно ли материализовать результаты для каждого запроса к каталогу. Значение по умолчанию — false. Дополнительные сведения см. в разделе "Материализация ".
    • BIGNUMERIC Default Scale: необязательное значение масштабирования для сопоставления BigQuery BIGNUMERIC с Spark DecimalType. Дополнительные сведения см. в сопоставлениях типов данных .

SQL

Выполните следующую SQL-команду в записной книжке или в SQL-редакторе Databricks. Элементы в квадратных скобках являются необязательными. Замените значения плейсхолдеров.

  • <catalog-name>: имя каталога в Azure Databricks.
  • <connection-name>: объект подключения , указывающий источник данных, путь и учетные данные доступа.
  • <data-project-id>: необязательный идентификатор проекта BigQuery, содержащий данные, которые необходимо сопоставить с этим каталогом. Если он не указан, используется идентификатор проекта для подключения, а затем идентификатор проекта учетной записи службы.
  • <dataset-name>: необязательное имя набора данных BigQuery, используемое для материализации результатов запроса. Если это не указано, при необходимости набор данных материализации подготавливается автоматически. Дополнительные сведения см. в разделе "Материализация ".
  • <force-materialization>: Необязательное булево значение. Если true, каждый запрос к каталогу материализует свои результаты. Значение по умолчанию — false. Дополнительные сведения см. в разделе "Материализация ".
  • <scale>: необязательное значение масштабирования [0, 38] для сопоставления BigQuery BIGNUMERIC с Spark DecimalType(38, scale). По умолчанию — 38. Дополнительные сведения см. в сопоставлениях типов данных .
CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
[OPTIONS (
  dataProjectId '<data-project-id>',
  materializationDataset '<dataset-name>',
  forceMaterialization '<force-materialization>',
  bigNumericDefaultScale '<scale>'
)];

Материализация

В отличие от других коннекторов федерации, коннектор BigQuery использует BigQuery Storage API вместо JDBC для повышения производительности. Azure Databricks может считывать данные из BigQuery непосредственно из хранилища или с помощью материализованного набора данных. Прямое чтение обеспечивает лучшую производительность для больших сканирований и поддерживает фильтрацию и проекцию на уровне источника данных. Материализация отправляет дополнительные операции (ограничение, агрегаты, соединения, сортировку) в вычисления BigQuery перед потоковой передачей результатов в Azure Databricks.

Представления и внешние таблицы всегда материализуются. Все остальные операции чтения используют прямое хранилище без материализации по умолчанию.

Рассмотрите возможность включения материализации, если требуются расширенные операции pushdown, считываются небольшие результирующие наборы из больших наборов данных или считываются данные между регионами. Материализация влечёт за собой дополнительные затраты на вычислительные ресурсы BigQuery.

Чтобы принудительно материализовать каждый запрос к внешнему каталогу, выберите Force materialization в Catalog Explorer или задайте для параметра каталога forceMaterialization значение true. Вам не нужно обновлять отдельные запросы.

Опция каталога forceMaterialization поддерживается на требуемых вычислительных ресурсах, при этом кластеры должны работать под управлением Databricks Runtime 16.4 LTS или более поздней версии.

Чтобы включить материализацию для одного запроса, задайте для параметра materializationEnabled значение true после имени таблицы BigQuery:

SELECT * FROM <catalog-name>.<schema-name>.<table-name>
WITH ('materializationEnabled' 'true');

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

CREATE FOREIGN CATALOG my_catalog USING CONNECTION my_bq_connection
OPTIONS (materializationDataset 'my_materialization_dataset');

Чтобы обновить существующий каталог, выполните следующую команду:

ALTER CATALOG my_catalog OPTIONS (materializationDataset 'my_materialization_dataset');

Чтение внешних таблиц BigQuery

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

Поддерживаемые внешние таблицы

Поддерживаются внешние таблицы BigLake и облачного хранилища.

  • Таблицы BigLake ссылаются на данные, хранящиеся в облачном хранилище, и включают точное управление доступом, управляемое с помощью BigQuery.
  • Внешние таблицы облачного хранилища ссылаться на файлы напрямую с помощью URI.

При запросе этих таблиц система материализует данные, поэтому запрос выполняется в встроенном хранилище BigQuery для полной поддержки функций SQL и оптимальной производительности.

Дополнительные сведения см. в документации по BigQuery для таблиц BigLake и внешних таблиц Cloud Storage.

Поддерживаемые pushdowns

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

Поддержка следующих pushdown-ов осуществляется без материализации:

  • Фильтры, передаваемые как ограничения строк через BigQuery Storage API (только простые предикаты — сравнения столбцов с литералами, IN, IS NULL, LIKE, а также AND или OR комбинации этих предикатов). Фильтры, ссылающиеся на операторы или функции, перечисленные ниже, требуют материализации.
  • Проекции

Следующие дополнительные операции pushdown поддерживаются при включённой материализации. При материализации фильтры компилируются в SQL вместо ограничений строк API службы хранилища BigQuery, поэтому они могут дополнительно содержать следующие операторы и функции:

  • Предел
  • Смещение при использовании с ограничением
  • Агрегаты
  • Сортировка при использовании с ограничением
  • Соединения (Databricks Runtime 16.1 или более поздней версии)
  • Операторы сравнения, булевы, побитовые и арифметические операторы (арифметические операторы проталкиваются вниз только если включён режим ANSI)
  • Математические функции (ABS, FLOOR) — частичная поддержка, только выражения фильтров
  • Строковые функции (CONCAT, UPPER, LOWER, LENGTH, TRIM, LTRIM, RTRIM) — частичная поддержка, только для выражений фильтра
  • Contains, Startswith, Endswith
  • Функции даты, времени и временной метки (DATE_TRUNC и EXTRACT для года, квартала, месяца, дня, часа и минуты) — частичная поддержка, только в выражениях фильтра
  • Прочие функции (COALESCE, , Cast, CASE WHENIFи доступ к элементу массива) — частичная поддержка, только выражения фильтра

Следующие pushdown не поддерживаются:

  • Функции окна

Сопоставление типов данных

В следующей таблице показано сопоставление типов данных BigQuery с Spark.

Тип BigQuery Тип Spark
BIGNUMERIC, NUMERIC DecimalType*
INT64 LongType
FLOAT64 DoubleType
ARRAY, GEOGRAPHY, JSON, STRING, STRUCT VarcharType
BYTES BinaryType
BOOL BooleanType
DATE DateType
DATETIME TimestampNTZType, за исключением StringType в Databricks Runtime 16.4–17.x**
TIME, TIMESTAMP TimestampType/TimestampNTZType
Любой тип с режимом REPEATED ArrayType соответствующего типа Spark***

* BigQuery BIGNUMERIC имеет точность до 76 цифр, что превышает максимальную DecimalType точность Spark 38. По умолчанию BIGNUMERIC сопоставляется с DecimalType(38, 38). Чтобы настроить масштаб, используйте bigNumericDefaultScale параметр каталога. Допустимые значения : [0, 38]. Например, bigNumericDefaultScale = '10' сопоставляется с BIGNUMERICDecimalType(38, 10). BigQuery NUMERIC согласуется с объявленной точностью и масштабом.

** Соединитель начал использовать API хранилища BigQuery в Databricks Runtime 16.4. От Databricks Runtime 16.4 до 17.x API хранилища сопоставлял BigQuery DATETIME с Spark StringType вместо TimestampNTZType. Databricks Runtime 18.0 восстанавливает сопоставление TimestampNTZType.

В BigQuery столбец с режимом REPEATED сопоставляется с ArrayType Spark, содержащим соответствующий тип Spark. Например, столбец BigQuery REPEATED STRING сопоставляется с ArrayType(VarcharType), а столбец BigQuery REPEATED INT64 сопоставляется с ArrayType(LongType).

При чтении из BigQuery, BigQuery Timestamp сопоставляется с Spark TimestampType, если preferTimestampNTZ = false (по умолчанию). BigQuery Timestamp сопоставляется с TimestampNTZType, если preferTimestampNTZ = true.

Примечание.

Колонки BigQuery INTERVAL в настоящее время не поддерживаются. Иностранная таблица с INTERVAL столбцем выходит из строя при загрузке схемы, поэтому вы не можете описать, запросить или загрузить таблицу. Таблицы без INTERVAL столбца не затронуты.

Устранение неполадок

В следующем разделе описывается распространённая ошибка и способ её устранения при использовании коннектора BigQuery.

Error creating destination table using the following query [<query>]

Распространенные причины. Учетная запись службы, используемая подключением, не имеет роли пользователя BigQuery .

Резолюция:

  1. Предоставьте роль пользователя BigQuery учетной записи службы, используемой подключением. Эта роль необходима для создания набора данных материализации, который временно хранит результаты запроса.
  2. Повторите вполнение запроса.
  • Last updated on