gen_sql_from_projects_cratedbs.py

import yaml
import boto3
import re
import json


# kubectl get cratedbs -A -o yaml > cratedbs2.yaml                                                                                                                                           ✔  aks1-westeurope/aa6c3932-424e-4bdd-a4eb-b5d0dd2ae4fa ⎈
# kubectl get projects -o yaml -A > projects2.yaml                                                                                                                                           ✔  aks1-westeurope/aa6c3932-424e-4bdd-a4eb-b5d0dd2ae4fa ⎈
# croud clusters list -r aks1.westeurope.azure --sudo  -o json > croud_cluster_list.json

CRATEDBS_YAML = "cratedbs2.yaml"
PROJECTS_YAML = "projects2.yaml"
CROUD_CLUSTERS_LIST_JSON = "croud_cluster_list.json"

s3_client = boto3.client("s3")

with open(CRATEDBS_YAML, "r") as file:
    cratedbs = yaml.safe_load(file)

print("--- CrateDBs ---")
clusters = {}
for key, value in cratedbs.items():
    if key == "items":
        for x in value:
            #            org = x['metadata']['labels']['cloud.crate.io/organization-name']
            prj = x["metadata"]["labels"]["cloud.crate.io/project-name"]
            prjid = x["metadata"]["labels"]["cloud.crate.io/project-id"]
            name = x["metadata"]["name"]
            #            dns = x['spec']['cluster']['externalDNS']
            clustername = x["spec"]["cluster"]["name"]
            print(f"Project: {prj} Namespace: {prjid} -- Cluster-Name: {clustername}")
            clusters[prjid] = name


print("\n--- Projects / Restore Commands ---")

with open(PROJECTS_YAML, "r") as file:
    projects = yaml.safe_load(file)

for key, value in projects.items():
    if key == "items":
        for x in value:
            project = x["spec"]["project"]["id"]
            name = x["spec"]["project"]["name"]
            keyid = x["spec"]["credentials"]["aws"]["accessKeyId"]
            secret = x["spec"]["credentials"]["aws"]["secretAccessKey"]
            bucket = x["spec"]["credentials"]["aws"]["bucket"]
            region = x["spec"]["credentials"]["aws"]["region"]
            org_id = x["spec"]["organization"]["id"]

            # secret = "<redacted>"
            if clusters.get(project):
                print(f"\n---")
                print(f"kubens {project}")
                print(
                    f"kubectl -n {project} port-forward crate-data-hot-{clusters.get(project)}-0 4200"
                )

                s3_folder = f"{project}/{clusters.get(project)}/"
                response = s3_client.list_objects_v2(
                    Bucket="cratedb-backup-westeurope-azure",
                    Prefix=s3_folder,
                    Delimiter="/",
                )
                folders = []
                for prefix in response["CommonPrefixes"]:
                    folders.append(prefix["Prefix"][:-1])
                folders.sort(reverse=True)
                backup_repo = ""
                for folder in folders:
                    print(f"    {prefix['Prefix'][:-1]}")
                    if not backup_repo:
                        backup_repo_list = folder.rsplit("/", 1)
                        backup_repo = backup_repo_list[-1]
                #                    print(f"{backup_repo}")
                print(
                    f"\ncreate repository \"{name}\" type s3 with (access_key = '{keyid}',"
                    f" secret_key = '{secret}', bucket = '{bucket}',"
                    f" base_path = '{project}/{clusters.get(project)}/{backup_repo}',"
                    f"readonly=true); -- {name}"
                )
                print(f"-- select * from sys.snapshots  order by finished desc limit 4")
                print(f'-- restore snapshot "{name}"."xx" all with (wait_for_completion=true);')
                print(f'-- drop repository "{name}"; ')
                print(f'-- kubectl -n {project} get secrets user-system-{clusters.get(project)} -o yaml | grep  \" password:\" | cut -d' ' -f 4 | base64 -d" ')
                print(f"-- alter user system SET ( password = 'xx' ); ")

print("\n--- croud projects ---")

for key, value in projects.items():
    if key == "items":
        for x in value:
            project = x["spec"]["project"]["id"]
            name = x["spec"]["project"]["name"]
            region = x["spec"]["credentials"]["aws"]["region"]
            org_id = x["spec"]["organization"]["id"]
            if clusters.get(project):
                print(
                    f"croud projects create --name {name} --region CHANGE --org-id {org_id} --sudo"
                )


print("\n--- croud clusters deploy ---")

with open(CROUD_CLUSTERS_LIST_JSON, "r") as file:
    clusters = json.load(file)

for cluster in clusters:
    product = cluster["product_name"]
    tier = cluster["product_tier"]
    project_id = cluster["project_id"]
    clustername = cluster["name"]
    cratedb_version = cluster["crate_version"]
    password = "xxx"
    unit = cluster["product_unit"]
    channel = cluster["channel"]
    username = cluster["username"]
    disk_type = cluster["hardware_specs"]["disk_type"]
    try:
        region = re.search(
            rf"({clustername}\.)(.+?)(\.cratedb.)", cluster["fqdn"]
        ).group(2)
    except AttributeError:
        region = ""

    print(
        f"croud clusters deploy "
        f"--cluster-name {clustername} --project-id {project_id} "
        f"--version {cratedb_version} "
        f"--channel {channel} "
        f"--product-name {product} "
        f"--tier {tier} --unit {unit} "
        #         f"--disk-type {disk_type} "
        f"--username {username} --password {password} "
        f"--region CHANGE --sudo"
    )