[Yandex Cloud documentation](../../index.md) > [Yandex DataLens](../index.md) > [Tutorials](index.md) > Service analytics > Migrating databases from Google BigQuery to Managed Service for ClickHouse® # Migrating databases from Google BigQuery to Yandex Managed Service for ClickHouse® You can migrate a database from Google BigQuery to Yandex Managed Service for ClickHouse® and then use Yandex DataLens to analyze it. A table is compressed and moved to a Google Storage bucket, from where it is transferred to an Yandex Object Storage bucket. After that, the data is imported to a Managed Service for ClickHouse® cluster where you can analyze it using Yandex DataLens. This migration method offers the following benefits: * You can specify the export format and the compression ratio. * Significantly reduced data volume leads to faster migration and lower costs. However, in this case, the data is migrated as is without transforming or copying the updated increments. To migrate a database from Google BigQuery to Managed Service for ClickHouse®: 1. [Transfer data from Google BigQuery to Yandex Object Storage](#migrate-data). 1. [Configure data mapping from Yandex Object Storage to the Managed Service for ClickHouse® cluster](#create-view). 1. [Analyze your data with Yandex DataLens](#datalens). If you no longer need the resources you created, [delete them](#clear-out). ## Required paid resources {#paid-resources} The support cost for this solution includes: * Managed Service for ClickHouse® cluster fee: use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see [Managed Service for ClickHouse® pricin](../../managed-clickhouse/pricing.md)g). * Fee for public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)). * Object Storage bucket fee: data storage and data operations (see [Object Storage pricing](../../storage/pricing.md)). * Yandex DataLens fee (see [DataLens pricing](../pricing.md)). ## Getting started {#before-you-begin} To migrate your database, first create the following Google Cloud and Yandex Cloud resources. ### Create Google Cloud resources {#create-google-res} 1. [Create a Google Storage bucket](https://cloud.google.com/storage/docs/creating-buckets). 1. [Create a Google Cloud service account](https://cloud.google.com/iam/docs/creating-managing-service-accounts#creating) with the `BigQuery Data Editor` and `Storage Object Admin` roles. 1. [Create an access key for the service account](https://cloud.google.com/iam/docs/creating-managing-service-account-keys#creating) and save it as a `.json` file. 1. [Download and install the `google-cloud-sdk` CLI tools](https://cloud.google.com/sdk/docs/install). 1. [Authenticate in gcloud CLI](https://cloud.google.com/sdk/docs/authorizing#authorizing_with_a_service_account). 1. [Install the Google BigQuery Python SDK](https://github.com/googleapis/python-bigquery). This package requires Python version 3.7 or higher. 1. Prepare a dataset for Google BigQuery. For this example, we use a Google BigQuery’s [public dataset](https://cloud.google.com/bigquery/public-data) `google_trends`, which includes the `international_top_terms` table with the following columns: * `rank` * `country_name` * `country_code` * `region_name` * `week` * `score` * `region_code` * `term` * `refresh_date` ### Create Yandex Cloud resources {#create-yandex-res} 1. [Create a service account](../../iam/operations/sa/create.md) with the `storage.uploader` role to access the Object Storage bucket. 1. [Create a static access key](../../iam/operations/authentication/manage-access-keys.md#create-access-key) for the service account. Save the key ID and secret key, as you will need them later. 1. [Create a Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-create.md) of any suitable configuration. When creating your cluster: * Specify the service account you created earlier. * Enable **DataLens access**. * [Add ZooKeeper hosts](../../managed-clickhouse/operations/zk-hosts.md) to ensure high cluster availability. 1. [Create an Object Storage bucket](../../storage/operations/buckets/create.md). When creating the bucket, activate [public read permissions](../../storage/operations/buckets/bucket-availability.md) for objects and the bucket listing. ## Migrate data from Google BigQuery to Yandex Object Storage {#migrate-data} 1. Create a file named `credentials.boto` with access credentials for Google Cloud and Yandex Cloud resources: ```boto [Credentials] gs_service_client_id = gs_service_key_file = aws_access_key_id = aws_secret_access_key = [GSUtil] default_project_id = [s3] calling_format=boto.s3.connection.OrdinaryCallingFormat host=storage.yandexcloud.net ``` Where: * `gs_service_client_id`: [Google Cloud service account name](https://cloud.google.com/iam/docs/service-account-overview) in `service-account-name@project-id.iam.gserviceaccount.com` format. * `gs_service_key_file`: Absolute path to the JSON file containing your Google Cloud service account’s access key. * `aws_access_key_id`: Yandex Cloud service account key ID. * `aws_secret_access_key`: Yandex Cloud service account secret key. * `default_project_id`: [Google Cloud project ID](https://cloud.google.com/resource-manager/docs/creating-managing-projects#identifying_projects). 1. Create a `main.py` script for data compression and migration: {% cut "main.py" %} ```python from google.cloud import bigquery import sys import argparse import time import subprocess import os os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="" os.environ["BOTO_CONFIG"]="" def parse_args(): parser = argparse.ArgumentParser(description='Export data from Google Big Query to Yandex Cloud object storage') parser.add_argument('--bq_project', type=str, help='GBQ project ID') parser.add_argument('--bq_location', type=str, help='GBQ table AND GS location') parser.add_argument('--gs_bucket', type=str, help='GS export destination bucket') parser.add_argument('--yc_bucket', type=str, help='YC copy destination bucket') parser.add_argument('--gsutil_path', type=str, help='GSutil exec path', default='gsutil') return parser.parse_args() def select_from_list(message, elements): print(message) print("\t{}. {}".format(0, "Export all")) for ind in range(len(elements)): if isinstance(elements[ind].reference, bigquery.DatasetReference): print("\t{}. {}".format(ind+1, elements[ind].reference.dataset_id)) elif isinstance(elements[ind].reference, bigquery.TableReference): print("\t{}. {}".format(ind+1, elements[ind].reference.table_id)) try: return int(input("(any letter for cancel) >> ")) except ValueError: print("Exiting") sys.exit() if __name__ == '__main__': args = parse_args() client = bigquery.Client() datasets = list(client.list_datasets(args.bq_project)) dataset_selector = select_from_list("Datasets in project {}".format(args.bq_project), datasets) export_list = [] for i in range(len(datasets)): dataset_ref = datasets[i].reference if dataset_selector == 0: export_list += list(client.list_tables(dataset_ref)) else: if i == dataset_selector - 1: tables = list(client.list_tables(dataset_ref)) table_selector = select_from_list("Tables in dataset {}".format(dataset_ref.dataset_id), tables) for j in range(len(tables)): if table_selector == 0 or j == table_selector - 1: export_list.append(tables[j]) print("Starting tables export") for n in range(len(export_list)): table_ref = export_list[n].reference # Creating Extract Job config. Selecting compression level and data format. job_config = bigquery.job.ExtractJobConfig() job_config.compression = bigquery.Compression.GZIP job_config.destination_format = bigquery.DestinationFormat.PARQUET print("Exporting {} table".format(table_ref.table_id)) extract_job = client.extract_table( source=table_ref, destination_uris="gs://{}/{}".format(args.gs_bucket, "{}-*".format(table_ref.table_id)), job_id="export-job-{}-{}".format(table_ref.table_id, round(time.time() * 1000)), location=args.bq_location, job_config=job_config) extract_job.result() print("Tables export done") # Calling gsutil rsync to synchronize source and destination buckets. source_uri = "gs://{}/".format(args.gs_bucket) destination_uri = "s3://{}/".format(args.yc_bucket) print("Synchronizing {} with {}...".format(source_uri, destination_uri)) proc = subprocess.Popen([args.gsutil_path, "-m", "rsync", source_uri, destination_uri], stdout=sys.stdout, stderr=sys.stderr) proc.communicate() print("Buckets synchronization done") ``` {% endcut %} 1. Run the `main.py` script to migrate data from Google BigQuery to Google Cloud Storage, with subsequent transfer to the Yandex Object Storage bucket: ```bash python main.py \ --bq_project= \ --bq_location=US \ --gs_bucket= \ --yc_bucket= ``` Wait for the data migration to complete. ## Configure data mapping from Yandex Object Storage to the Managed Service for ClickHouse® cluster {#create-view} 1. To create a view of the imported data, [connect to the Managed Service for ClickHouse® cluster database](../../managed-clickhouse/operations/connect/clients.md) and run the following SQL query: ```sql CREATE view db1.v$google_top_rising_terms on cluster on cluster '{cluster}' AS (SELECT term, score, rank, country_name, country_code, region_name, region_code, week, refresh_date FROM s3Cluster( '', 'https://storage.yandexcloud.net//top_terms-*', 'Parquet', 'rank Int32, country_name String, country_code String, region_name String, week Timestamp, score Nullable(Int32), region_code String, term String, refresh_date Timestamp') ) ``` Where: * `db1`: Name of the database in the Managed Service for ClickHouse® cluster where you want to create a view. * `v$google_top_rising_terms`: Name of the view for the imported data. * ``: Managed Service for ClickHouse® cluster ID. You can get it from your [folder’s cluster list](../../managed-clickhouse/operations/cluster-list.md). * `top_terms-*`: Naming prefix for Object Storage bucket objects. For example, if you migrate a Google Cloud table containing rows named `top_terms`, the corresponding objects in the Object Storage bucket will have the following names: `top_terms-000000000001`, `top_terms-000000000002`, etc. To include all table entries with this name in the view, use the `top_terms-*` pattern in your SQL query. 1. To retrieve the first 100 records from the view, run the following SQL query (in our example, we use database `db1` and its view `v$google_top_rising_terms`): ```sql SELECT * FROM db1.v$google_top_rising_terms limit 100 ``` ## Analize your data with Yandex DataLens {#datalens} 1. [Connect the Managed Service for ClickHouse® cluster to DataLens](../operations/connection/create-clickhouse.md). 1. [Create a dataset](../dataset/create-dataset.md#create) from the `db1.v$google_top_rising_terms` table. For the `score` field, select the average aggregation. 1. [Create a bar chart](../visualization-ref/column-chart.md#create-diagram): 1. Drag the `country_name` field to the **X** section. 1. Drag the `score` field to the **Y** section. 1. Drag the `term` field to the **Filters** section. In the form that opens, specify the following settings: * **Operation**: **Belongs to a set**. * **Available**: Select a term from a list of available terms, then click **Apply filter**. 1. Drag the `term` field to the **Sorting** section. The use of the specified search query will be analyzed, with the result displayed as a country-by-country bar chart. ## Delete the resources you created {#clear-out} Delete the resources you no longer need to avoid paying for them: 1. [Delete the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-delete.md). 1. [Delete all objects from the Object Storage bucket](../../storage/operations/objects/delete-all.md), then [delete the bucket](../../storage/operations/buckets/delete.md). 1. [Delete the Google Storage bucket](https://cloud.google.com/storage/docs/deleting-buckets). _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._