Миграция Google Analytics из BigQuery в Yandex Clickhouse09.09.2024 13:31

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/*/*"]
  }
}

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

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

3. Подготовить 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=<...>

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

$ 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=""
export BOTO_CONFIG="./credentials.boto"
python3 00_migrate.py \
   --bq_project= \
   --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:.mdb.yandexcloud.net:8123 ubuntu@ -v

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

export GOOGLE_APPLICATION_CREDENTIALS=""
export BOTO_CONFIG="./credentials.boto"
python3 01_import.py \
   --s3_bucket_name=habr-bg-to-ch \
   --s3_access_key='' \
   --s3_secret_key='' \
   --ch_cluster_id='' \
   --ch_host='clickhouse_cluster_host' \
   --ch_password='' \
   --ch_database='clickhouse_database' \
   --bq_table_pattern='' \
   --bq_project_id="" \
   --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. Такой подход обеспечит гибкость и позволит адаптировать процесс миграции под конкретные требования вашей инфраструктуры, что сделает его еще более универсальным и масштабируемым.

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

© Habrahabr.ru