from google.cloud import bigquery

project = 'dbaas-eng-bi'
location = 'US'
client = bigquery.Client(project=project, location=location)

for table_name in [
    # AWS
    'aws_ec2_instances', 'aws_ec2_ebs_volumes', 'aws_elbv1_load_balancers', 'aws_elbv2_load_balancers',
    'aws_ec2_nat_gateways', 'aws_eks_clusters', 'aws_ec2_vpcs', 'aws_ec2_security_groups',
    'aws_eks_cluster_node_groups', 'aws_autoscaling_groups',
    # GCP
    'gcp_compute_disks', 'gcp_compute_instances',
    'gcp_compute_routers', 'gcp_compute_router_nat_mapping_infos',
    'gcp_compute_forwarding_rules', 'gcp_container_clusters', 'gcp_compute_networks'
]:
    print(table_name)
    client.query(
        """
      SELECT *
      FROM dbaas-eng-bi.resource_asset_snapshot.{table_name}
      WHERE _cq_sync_time = (
        SELECT MAX(_cq_sync_time)
        FROM dbaas-eng-bi.resource_asset_snapshot.{table_name}
        WHERE DATE(_cq_sync_time) = CURRENT_DATE()
        )
      """.format(table_name=table_name)
    ).to_dataframe().to_pickle("{}.pkl".format(table_name), compression="gzip")