Миграция Google Analytics из BigQuery в Yandex Clickhouse

BigQuery — мощный облачный сервис от Google для работы с большими объемами данных. Один из часто используемых случаев использования BigQuery — это интеграция с Google Analytics. Эта связка позволяет компаниям эффективно обрабатывать и анализировать огромные массивы данных о поведении пользователей на сайтах и в приложениях. Благодаря способности обрабатывать большие данные, масштабироваться, делать мгновенные запросы и интегрироваться с другими инструментам BigQuery стал стандартом де-факто для тех, кто хочет перейти от обычных отчетов основанных на табличных данных к гибкой и осмысленной аналитике.

Google решил прекратить работу BigQuery на территории РФ начиная с 9 сентября, и в связи с этим, у многих пользователей возник вопрос относительно дальнейшего сохранения своих исторических данных и их миграции в другое место.

К счастью, существует достаточно понятный способ миграции данных из BigQuery в ClickHouse кластер, который может быть развернут как в Яндекс Облаке, так и в on-prem виде.

В данной статье мы рассмотрим миграцию данных Google Analytics из GCP BigQuery в Yandex Cloud ClickHouse, а также доступ к ним через DataLens, или же WebSQL.

Подготовка ресурсов на GCP

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

Создание бакета на GCP

GCP бакет будет использоваться как временное хранилище для выгрузки данных из BigQuery в виде файлов. Для его создания выполните следующие шаги:

1. В интерфейсе GCP зайдите в Object Storage

2. Кликните на кнопку “Create”

3. Введите имя бакета; Для этой статьи мы будем использовать имя habr-bg-to-ch

Более подробную инструкцию по созданию бакета можно найти в официальном руководстве.

Создание сервисного аккаунта

Сервисный аккаунт будет использоваться для миграции данных из BigQuery в Object Storage, для его создания воспользуйтесь официальным руководством от GCP. Обратите внимание, что у сервисного аккаунта должны быть назначены роли  BigQuery Data Editor и Storage Object Admin. 

После создания сервисного аккаунта создайте JSON ключ с помощью официальной инструкции и скачайте его в виде файла.

Подготовьте локальный стенд

На машине, где будет запускаться миграция необходимо:

• Установить google-cloud-sdk и python-bigquery

• Установить gcloud и авторизоваться внутри него с помощью JSON-ключа от созданного на предыдущем шаге сервис аккаунта.

Подготовка ресурсов в Yandex Cloud

Внутри Яндекс облака необходимо выполнить следующие действия:

1. Создать сервисный аккаунт с ролью storage.uploader для доступа к бакету Object Storage.

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

3. Создать бакет Object Storage с публичным доступом на чтение объектов и к списку объектов в бакете. Как и в случае с GCP, будем использовать имя habr-bg-to-ch.

Теперь нужно подготовить все остальные части инфраструктуры, а именно:

• IAM аккаунты, 

• облачные сети, группы безопасности, сетевые маршруты и NAT Gateway, 

• jumphost машину,

• Lockbox секреты,

• ClickHouse кластер с отказоустойчивостью с помощью хостов Zookeeper.

Для создания упомянутых компонентов будем использовать Terraform (или OpenTofu), а также коммьюнити модули для Yandex Cloud: terraform-yacloud-modules.

Полный код Terraform инфраструктуры доступен в репозитории kvendingoldo/habr-bq-to-ch. Сейчас же рассмотрим только основные его части.

01-common.tf

Данный файл содержит в себе создание VPC, групп безопасности, NAT Gateway, и IAM аккаунтов.

module "network" {   source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-vpc.git?ref=v1.7.0"    blank_name = var.common_name    azs = var.azs    create_nat_gateway = true    public_subnets  = var.subnets["public"]   private_subnets = var.subnets["private"] }  module "security_groups" {   for_each = local.security_groups    source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-security-group.git?ref=v1.0.0"    blank_name = format("%s-%s", var.common_name, each.key)    vpc_id = module.network.vpc_id    ingress_rules = each.value["ingress_rules"]   egress_rules  = each.value["egress_rules"]    depends_on = [     module.network   ] }  module "iam_accounts" {   for_each = {     for k, v in var.iam : k => v if v["enabled"]   }    source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-iam.git//modules/iam-account?ref=v1.0.0"    name = format("%s-%s", var.common_name, each.key)    folder_roles = each.value["folder_roles"]   cloud_roles  = each.value["cloud_roles"]    enable_static_access_key = each.value["enable_static_access_key"]   enable_api_key           = each.value["enable_api_key"]   enable_account_key       = each.value["enable_account_key"] }

02-vms.tf

Данный файл содержит в себе описание виртуальных машин для Яндекс Облака и LockBox секретов, которые хранят SSH ключи для этих машин.

# # VM instances # module "vms" {   for_each = var.vms    source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-instance.git?ref=v1.0.0"    name = format("%s-%s", var.common_name, each.key)    zone       = each.value["zone"]   subnet_id  = local.zone2prvsubnet[each.value["zone"]]   enable_nat = each.value["enable_nat"]   create_pip = each.value["create_pip"]   security_group_ids = [     module.security_groups[each.key].id   ]    hostname = each.key    platform_id   = each.value["platform_id"]   cores         = each.value["cores"]   memory        = each.value["memory"]   core_fraction = each.value["core_fraction"]   preemptible   = each.value["preemptible"]    image_family = each.value["image_family"]    service_account_id = module.iam_accounts[each.key].id    generate_ssh_key = each.value["generate_ssh_key"]   ssh_user         = each.value["ssh_user"]   user_data        = null    boot_disk_initialize_params = each.value["boot_disk_initialize_params"]   secondary_disks             = each.value["secondary_disks"] }  # # Secrets # module "vms_secrets" {   for_each = var.vms    source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-lockbox.git?ref=v1.0.0"    name   = format("%s-%s", var.common_name, each.key)   labels = {}    entries = {     "ssh-prv" : module.vms[each.key].ssh_key_prv     "ssh-pub" : module.vms[each.key].ssh_key_pub   }    deletion_protection = false }

03-clickhouse.tf

Файл описывает вызов ClickHouse модуля, а также LockBox секреты в которых хранится пароль для администратора ClickHouse кластера.

module "clickhouse" {   for_each = local.clickhouse_clusters    source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-mdb-clickhouse.git?ref=main"    name = format("%s-%s", var.common_name, each.key)   labels = {}    network_id = module.network.vpc_id   security_group_ids = [module.security_groups[each.key].id]    access              = each.value["access"]   users               = each.value["users"]   databases           = each.value["databases"]   deletion_protection = each.value["deletion_protection"]    clickhouse_disk_size          = each.value["clickhouse_disk_size"]   clickhouse_disk_type_id       = each.value["clickhouse_disk_type_id"]   clickhouse_resource_preset_id = each.value["clickhouse_resource_preset_id"]   environment                   = each.value["environment"]   clickhouse_version            = each.value["clickhouse_version"]   description                   = each.value["description"]    sql_user_management     = each.value["sql_user_management"]   sql_database_management = each.value["sql_database_management"]   admin_password          = each.value["sql_user_management"] ? random_password.clickhouse_admin_password[each.key].result: null    shards                   = each.value["shards"]   hosts                    = each.value["hosts"]   cloud_storage            = each.value["cloud_storage"]   copy_schema_on_new_hosts = each.value["copy_schema_on_new_hosts"]    backup_window_start = each.value["backup_window_start"]   maintenance_window = {     type = each.value["maintenance_window_type"]     day  = each.value["maintenance_window_day"]     hour = each.value["maintenance_window_hour"]   }   depends_on = [module.iam_accounts, module.network] }   resource "random_password" "clickhouse_admin_password" {   for_each = {     for k, v in local.clickhouse_clusters : k => v if v["sql_user_management"]   }    length           = 8   special          = true   override_special = "!#$%&*()-_=+[]{}<>:?" }  module "clickhouse_secrets" {   for_each = {     for k, v in local.clickhouse_clusters : k => v if v["sql_user_management"]   }    source = "git::https://github.com/terraform-yacloud-modules/terraform-yandex-lockbox.git?ref=v1.0.0"    name = format("%s-clickhouse-%s", var.common_name, each.key)   labels = {}    entries =  {       "admin-password" : random_password.clickhouse_admin_password[each.key].result     }    deletion_protection = false }

10-variables.tf

В файле находится набор стандартных параметров для нашей инфраструктуры.

# # yandex # variable "azs" {   default = ["ru-central1-a", "ru-central1-b", "ru-central1-d"] }  # # naming # variable "common_name" {   default = "habr-bg-to-ch" }  # # network # variable "subnets" {   default = {     public  = [["10.100.0.0/24"], ["10.101.0.0/24"], ["10.102.0.0/24"]]     private = [["10.103.0.0/24"], ["10.104.0.0/24"], ["10.105.0.0/24"]]   } }  variable "security_groups" {   default = {     jumphost = {       ingress_rules = {         "ssh" = {           protocol       = "tcp"           port           = 22           v4_cidr_blocks = ["0.0.0.0/0"]           description    = "ssh"         }       }       egress_rules = {         "all" = {           protocol       = "any"           from_port      = 0           to_port        = 65535           v4_cidr_blocks = ["0.0.0.0/0"]         }       }     }      clickhouse-demo = {       ingress_rules = {         "self" = {           protocol          = "any"           from_port         = 0           to_port           = 65535           predefined_target = "self_security_group"         }         "8443_to_internet" = {           protocol       = "tcp"           port           = 8443           v4_cidr_blocks = ["0.0.0.0/0"]         }         "8123_to_internet" = {           protocol       = "tcp"           port           = 8123           v4_cidr_blocks = ["0.0.0.0/0"]         }         "9440_to_internet" = {           protocol       = "tcp"           port           = 9440           v4_cidr_blocks = ["0.0.0.0/0"]         }         "9000_to_internet" = {           protocol       = "tcp"           port           = 9000           v4_cidr_blocks = ["0.0.0.0/0"]         }       }       egress_rules = {         "all" = {           protocol       = "any"           from_port      = 0           to_port        = 65535           v4_cidr_blocks = ["0.0.0.0/0"]         }       }     }   } }  # # IAM # variable "iam" {   default = {     jumphost = {       enabled                  = true       folder_roles             = []       cloud_roles              = []       enable_static_access_key = false       enable_api_key           = false       enable_account_key       = false     }      clickhouse-demo = {       enabled                  = true       folder_roles             = []       cloud_roles              = []       enable_static_access_key = false       enable_api_key           = false       enable_account_key       = false     }   } }  # # Virtual Machines (VMs) # variable "vms" {   default = {     jumphost = {       zone = "ru-central1-a"        cloud_init = {         initial_setup    = false         cloud_init_debug = true       }        image_family = "ubuntu-2204-lts"        platform_id   = "standard-v2"       cores         = 2       memory        = 2       core_fraction = 100       preemptible   = true        create_pip       = true       enable_nat       = true       generate_ssh_key = true       ssh_user         = "ubuntu"        boot_disk_initialize_params = {         size = 30         type = "network-hdd"       }        secondary_disks = {}     }   } }  # # clickhouse # variable "clickhouse_clusters" {   default = {     "clickhouse-demo" = {       access = {         data_lens     = true         metrika       = false         web_sql       = true         serverless    = false         yandex_query  = false         data_transfer = false       }       users               = []       databases           = []       deletion_protection = false        disk_size          = 45       disk_type_id       = "network-ssd"       resource_preset_id = "s2.medium"       environment        = "PRODUCTION"       version            = "23.8"       description        = "Demo for Habr: Data export from Google BigQuery"        sql_user_management     = true       sql_database_management = true        zookeeper_disk_size          = null       zookeeper_disk_type_id       = null       zookeeper_resource_preset_id = null        shards = [         {           name   = "master01"           weight = 100           resources = {             resource_preset_id = "s2.micro"             disk_size          = 5             disk_type_id       = "network-ssd"           }         }       ]       hosts = [         {           shard_name       = "master01"           type             = "CLICKHOUSE"           zone             = "ru-central1-a"           assign_public_ip = false         },         {           shard_name       = "zk01"           type             = "ZOOKEEPER"           zone             = "ru-central1-a"           assign_public_ip = false         },         {           shard_name       = "zk02"           type             = "ZOOKEEPER"           zone             = "ru-central1-a"           assign_public_ip = false         },         {           shard_name       = "zk03"           type             = "ZOOKEEPER"           zone             = "ru-central1-a"           assign_public_ip = false         }       ]       cloud_storage = {         enabled             = false         move_factor         = 0         data_cache_enabled  = true         data_cache_max_size = 0       }       copy_schema_on_new_hosts = true        backup_window_start = {         hours   = "12"         minutes = 00       }        maintenance_window_type = "WEEKLY"       maintenance_window_hour = 1       maintenance_window_day  = "SUN"     }   } }

Деплой Terraform инфраструктуры

Для применения вышеописанной Terraform инфраструктуры нужно выполнить несколько простых шагов:

1. Создать файл конфигурацией зеркала от Яндекса для скачивания Terraform плагинов. К сожалению, данное действие необходимо из-за блокировки официальных провайдер-реестров со стороны Hashicorp и OpenTofu.

Вариант для Terraform (файл ~/.terraformrc):

provider_installation {       network_mirror {     url = "https://terraform-mirror.yandexcloud.net/"     include = ["registry.terraform.io/*/*"]   }   direct {     exclude = ["registry.terraform.io/*/*"]   } }

Вариант для OpenTofu (файл ~/.tofurc):

provider_installation {   network_mirror {     url = "https://terraform-mirror.yandexcloud.net/"     include = ["registry.opentofu.org/*/*"]   }   direct {     exclude = ["registry.opentofu.org/*/*"]   } }

2. Скачать и установить Terraform (или OpenTofu). Сделать это можно например с помощью утилиты tenv — менеджера версий для OpenTofu / Terraform / Terragrunt и Atmos.

3. Установить и настроить yc-cli с помощью официальной инструкции от Яндекса.

4. Подготовить S3 backend для сохранения Terraform state внутри Яндекс облака.

Экспортировать необходимые для Yandex Provider окружения среды.

export YC_TOKEN=<...> export YC_CLOUD_ID=<...> export YC_FOLDER_ID=<...> export AWS_ACCESS_KEY_ID=<...> export AWS_SECRET_ACCESS_KEY=<...> 

5. Выполнить последовательно команды:

$ tenv tf install latest $ terraform init $ terraform plan $ terraform apply

После выполнения всех этих действий у вас будет готовая инфраструктура в Яндекс Облаке, которая состоит из настроенного кластера ClickHouse находящегося в закрытой сети, без публичных IP-адресов. Чтобы получить к нему доступ, необходимо использовать также созданный jumphost, и через него прокинуть себе локально порты. SSH ключи для jumphost можно найти в соответствующем ему lockbox секрете.

Перенос данных из GCP BigQuery в Yandex Object Storage

На данном этапе нам необходимо перенести «сырые» данные в формате Apache Parquet из BigQuery в бакет внутри Яндекс облака. 

Для этого сначала необходимо создать файл credentials.boto с параметрами доступа к ресурсам Google Cloud и Yandex Cloud:

[Credentials] gs_service_client_id  = <сервисный_аккаунт_google_cloud> gs_service_key_file   = <абсолютный_путь_к_json_файлу> aws_access_key_id     = <идентификатор_ключа_сервисного_аккаунта> aws_secret_access_key = <секретный_ключ_сервисного_аккаунта>  [GSUtil]   default_project_id  = <идентификатор_проекта_Google_Cloud>  [s3]   calling_format     = boto.s3.connection.OrdinaryCallingFormat   host               = storage.yandexcloud.net

Где:

• gs_service_client_id — имя сервисного аккаунта Google Cloud в формате service-account-name@project-id.iam.gserviceaccount.com.

• gs_service_key_file — абсолютный путь к JSON-файлу ключа доступа сервисного аккаунта Google Cloud.

• aws_access_key_id — идентификатор ключа сервисного аккаунта Yandex Cloud.

• aws_secret_access_key — секретный ключ сервисного аккаунта Yandex Cloud.

• default_project_id — идентификатор проекта Google Cloud.

После подготовки файла credentials.boto необходимо выполнить скрипт 00_migrate.py:

export GOOGLE_APPLICATION_CREDENTIALS="<path_to_json>" export BOTO_CONFIG="./credentials.boto" python3 00_migrate.py \    --bq_project=<bq_project_id> \    --bq_location=<bq_location> \    --gs_bucket=habr-bg-to-ch \    --yc_bucket=habr-bg-to-ch

По окончанию работы скрипта, в S3 бакете внутри вашего Яндекс Облака вы увидите данные из BigQuery в формате Parquet. Обратите внимание, что синхронизация GCP и Yandex Cloud бакетов происходит через rsync, а следовательно это может занять какое-то время.

Использование смигрированных данных 

Начиная с этого момента данные уже можно использовать с помощью табличной функции s3Cluster и запросов следующего вида:

SELECT * FROM s3Cluster(  '<идентификатор_кластера>',  'https://storage.yandexcloud.net/<имя_бакета_Object_Storage>/events-*',  'Parquet',  <поля> )

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

Чтобы ускорить запросы, а также избежать падения ClickHouse узлов все смигрированные на S3 данные нужно экспортировать внутрь ClickHouse в естественном виде.  Для небольшого объема таблиц с предсказуемыми полями это не сложно, но в случае Google Analytics это может вызывать некоторые трудности из-за большой вложенности полей и отличающихся между собой схем данных.

Чтобы упросить процесс импорта, в уже упомянутом репозитории kvendingoldo/habr-bq-to-ch есть скрипт 01_import.py работающий по следующему алгоритму.

1. Забрать список всех таблиц из BigQuery базы.

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

3. На основании полученной схемы данных для ClickHouse® создать таблицу с помощью SQL.

4. Импортировать данные из файла S3 соответствующего названию таблицы.

Для запуска этого скрипта в случае нашей инфраструктуры созданной через Terraform необходимо предварительно прокинуть порт для ClickHouse локально, так как кластер находится в приватной сети и не доступен из Интернета. Сделать это можно следующей команды: $ ssh -L 127.0.0.1:8123:<clickhouse_cluster_id>.mdb.yandexcloud.net:8123 ubuntu@<jumphost_ip> -v

После прокидывания портов можно запустить миграцию данных следующей командой:

export GOOGLE_APPLICATION_CREDENTIALS="<path_to_json>" export BOTO_CONFIG="./credentials.boto" python3 01_import.py \    --s3_bucket_name=habr-bg-to-ch \    --s3_access_key='<yc_bucket_access_key>' \    --s3_secret_key='<yc_bucket_secret_key>' \    --ch_cluster_id='<clickhouse_cluster_id>' \    --ch_host='clickhouse_cluster_host' \    --ch_password='<clickhouse_cluster_password>' \    --ch_database='clickhouse_database' \    --bq_table_pattern='<tables_prefix_to_import>' \    --bq_project_id="<bq_project_id>" \    --bq_database="<bq_database>"

После окончания работы скрипта, данные будут находится внутри ClickHouse, а доступ к ним можно будет получить с помощью обычных SQL запросов через WebSQL, DataLens, CLI и тд. Подробнее про доступ к данным через WebSQL можно прочитать в официальной документации Яндекс Облака.

Удаление временных ресурсов

После завершения миграции данных можно удалить все ресурсы, которые уже больше не актуальны:

1. GCP сервис аккаунт

2. GCP S3 bucket habr-bg-to-ch

3. Yandex S3 bucket habr-bg-to-ch

Выводы

Миграция данных из BigQuery в ClickHouse в целом не сложный процесс. Самая большая проблема в ней — конвертация схемы данных. Следуя описанным в статье шагам можно смигрировать данные не только в Яндекс Облако, но и в ваш локальный ClickHouse кластер. Так же, тот же S3 bucket внутри Яндекс Облака можно легко заменить на minio или любое другой S3-совместимое хранилище, доступное из ClickHouse кластера.

Миграция данных из BigQuery в ClickHouse в целом не представляет значительной сложности. Основная сложность в этом процессе — это корректная конвертация схемы данных. Следуя шагам, описанным в статье, вы сможете успешно перенести данные как в Яндекс Облако, так и в ваш локальный ClickHouse кластер. Более того, используемый в примере S3 bucket в Яндекс Облаке можно легко заменить на MinIO или любое другое S3-совместимое хранилище, доступное вашему ClickHouse кластеру, например Selectel S3. Такой подход обеспечит гибкость и позволит адаптировать процесс миграции под конкретные требования вашей инфраструктуры, что сделает его еще более универсальным и масштабируемым.

Желаю вам успешных миграций!