sync_amz_data/sync_amz_data/DataTransform/Data_ETL.py

import logging
from sync_amz_data.public.amz_ad_client import SPClient, Account, SBClient, SDClient, BaseClient
import pandas as pd
import numpy as np
from dateutil.parser import parse
import time
pd.set_option('display.max_columns', None)
import warnings

warnings.filterwarnings('ignore')
pd.set_option('expand_frame_repr', False)
from datetime import datetime, timezone, timedelta
import clickhouse_connect
import pytz
from typing import Literal

class Common_ETLMethod(BaseClient):
    def timeZone(self):
        rel = self.get_profilesInfo()
        profileId = int(self.profile_id)
        df_info = pd.json_normalize(rel)
        return df_info.query("profileId==@profileId")['timezone'].values[0]

    def clickhouse_connect(self):
        conn = clickhouse_connect.get_client(host='3.93.43.158', port=8123, username='root',
                                             password='6f0eyLuiVn3slzbGWpzI')
        return conn

    def columnsName_modify(self, df):
        """
        列名.换_，设置全部小写
        """
        df.columns = [i.replace(".", "_").lower() for i in df.columns]
        return df

    def time_stamp_convert(self, df, time_columns: list):
        """
        时间戳转换为utc
        """
        for time_column in time_columns:
            df[time_column] = pd.to_datetime(df[time_column] * 1000000).map(lambda x: x.strftime("%Y-%m-%d %H:%M:%S"))
        df[time_columns] = df[time_columns].astype("datetime64[ns]")
        return df

    def TZ_Deal(self, df, time_columns):
        """
        TZ时间格式转换为utc
        """
        for time_column in time_columns:
            df[time_column] = df[time_column].map(lambda x: parse(x).strftime("%Y-%m-%d %H:%M:%S"))
        df[time_columns] = df[time_columns].astype("datetime64[ns]")
        return df

    def placement_segmentsplit(self, df, segment):
        """
        拆分placement与percentage列
        """
        df[segment] = df[segment].astype("string")
        df[segment + str("_percentage")] = df[segment].str.extract("'percentage':.+([\d\.]{1,}),").astype('float32')
        df[segment + str("_placement")] = df[segment].str.extract("'placement':.+'(.+)'")
        df.replace(['nan', 'Nan', 'NaN'], np.nan, inplace=True)
        df.drop(columns=[segment], inplace=True)
        return df

    def expression_split(self, df, segment):
        """
        拆分type，value列
        """
        df[segment] = df[segment].astype("string")
        df[segment + str("_type")] = df[segment].str.extract(r"'type':\s{0,1}'(.+?)',")
        df[segment + str("_value")] = df[segment].str.extract(r"'value':\s{0,1}[\'\[\{](.+)'")
        df[segment + str("_value")] = df[segment + str("_value")].map(
            lambda x: x if pd.isna(x) or "," not in x else "[{" + x + "'}]").str.replace("{{","{")
        df.replace(['nan', 'Nan', 'NaN'], np.nan, inplace=True)
        df.drop(columns=[segment], inplace=True)
        return df

    @classmethod
    def get_keyOvalue(cls,expressions, result: Literal['value', 'type']):
        if expressions not in [None,pd.NA,pd.NaT]:
            if len(expressions) > 1:
                return [i.get(result) for i in expressions]
            if len(expressions) == 1:
                sub_ = expressions[0].get(result)
                if sub_ is None:
                    return None
                elif type(sub_) == str:
                    return sub_
                elif type(sub_) == list:
                    return [i.get(result) for i in sub_]
                else:
                    return expressions
        else:
            return '-'

    def id_type_trans(self,df):
        id_columns = [i for i in df.columns if 'id' in i.lower() and 'bid' not in i.lower()]
        df[id_columns] = df[id_columns].fillna(-1).applymap(lambda x: round(int(x), 0)).astype("string")
        df[id_columns] = df[id_columns].applymap(lambda x: '' if x == '-1' else x)
        return df

    def type_trans(self, df_report, columns: list, timeZone_: str, extra_columns: list = []):
        # 添加字段
        df_report['profileId'] = self.profile_id
        df_report['timeZone'] = timeZone_

        df_needManualAdd = [i for i in columns if i not in df_report.columns]
        if len(df_needManualAdd) > 0:
            df_report[df_needManualAdd] = None
        columns.extend(extra_columns)
        columns.append("date") if "date" not in columns else columns
        columns.extend(["timeZone", "profileId"])

        # 修改字段类型
        df_report = self.id_type_trans(df_report)
        df_report['date'] = df_report['date'].astype("datetime64[ns]")

        # df_report[df_report.select_dtypes('O').columns] = df_report[df_report.select_dtypes('O').columns].astype('string')
        toFloat = [i for i in columns if 'sales' in i.lower() or 'percent' in i.lower() or 'video' in i.lower()]
        if len(toFloat) > 0:
            df_report[toFloat] = df_report[toFloat].applymap(lambda x: 0.0 if pd.isna(x) or x == '' else float(x))

        df_report[df_report.select_dtypes(["int"]).columns] = df_report[df_report.select_dtypes(["int"]).columns].fillna(0)
        df_report[df_report.select_dtypes(["float"]).columns] = df_report[df_report.select_dtypes(["float"]).columns].fillna(0.0)
        if "campaignRuleBasedBudget" in df_report.columns:
            df_report["campaignRuleBasedBudget"] = df_report["campaignRuleBasedBudget"].fillna(0.0)
        if "campaignRuleBasedBudgetAmount" in df_report.columns:
            df_report["campaignRuleBasedBudgetAmount"] = df_report["campaignRuleBasedBudgetAmount"].fillna(0.0)
        if "eCPAddToCart" in df_report.columns:
            df_report["eCPAddToCart"] = df_report["eCPAddToCart"].fillna(0.0)
        if "eCPBrandSearch" in df_report.columns:
            df_report["eCPBrandSearch"] =df_report["eCPBrandSearch"].fillna(0.0)
        if "viewClickThroughRate" in df_report.columns:
            df_report["viewClickThroughRate"] = df_report["viewClickThroughRate"].fillna(0.0)
        if "searchTermImpressionRank" in df_report.columns:
            df_report["searchTermImpressionRank"] = df_report["searchTermImpressionRank"].fillna(0)
        if "searchTermImpressionShare" in df_report.columns:
            df_report["searchTermImpressionShare"] = df_report["searchTermImpressionShare"].fillna(0.0)
        if "impressionsFrequencyAverage" in df_report.columns:
            df_report["impressionsFrequencyAverage"] = df_report["impressionsFrequencyAverage"].fillna(0.0)
        df_report.fillna(np.nan, inplace=True)
        # print(df_report[columns].info())
        # df_report.to_excel("df.xlsx")

        return df_report

    def today_(self):
        timeZone_ = self.timeZone()
        today_ = datetime.now(tz=pytz.timezone(timeZone_))
        logging.info(f"timezone:{timeZone_},today:{today_}")

        return timeZone_,today_

    def config_params(self,params:dict=None):
        timzone, today = self.today_()
        if params==None:
            params={}
            params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d")
            params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d")
            params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d")
        else:
            if params.get("endDate") == None:
                params["endDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d")
            if params.get("startDate") == None:
                params["startDate"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y-%m-%d")
            if params.get("date") == None:
                params["date"] = (datetime(today.year, today.month, today.day) - timedelta(days=1)).strftime("%Y%m%d")
        print(params)
        return params

class Acount_ETL(Account, Common_ETLMethod):
    def portfolio_ETL(self):
        list_portfolio = self.get_portfolios()
        df_portfolio = pd.json_normalize(list_portfolio)
        # print(self.columnsName_modify(df_portfolio))
        return self.columnsName_modify(df_portfolio)


class SP_ETL(SPClient, Common_ETLMethod):
    def campaigns_ETL(self):
        list_campaign_SP = list(self.iter_campaigns(**{"includeExtendedDataFields": True}))
        df_campaign = pd.json_normalize(list_campaign_SP)
        df_campaign = self.placement_segmentsplit(df_campaign, "dynamicBidding.placementBidding")
        df_campaign = self.TZ_Deal(df_campaign, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        # print(df_campaign)
        return self.columnsName_modify(df_campaign)

    def adGroups_ETL(self):
        list_adGroup_SP = list(self.iter_adGroups(**{"includeExtendedDataFields": True}))
        df_adGroup_SP = pd.json_normalize(list_adGroup_SP)
        df_adGroup_SP = self.TZ_Deal(df_adGroup_SP,
                                     ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        return self.columnsName_modify(df_adGroup_SP)

    def ads_ETL(self):
        list_adId_SP = list(self.iter_ads(**{"includeExtendedDataFields": True}))
        df_adId_SP = pd.json_normalize(list_adId_SP)
        df_adId_SP = self.TZ_Deal(df_adId_SP, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        return self.columnsName_modify(df_adId_SP)

    def keywords_ETL(self):
        list_keywords_SP = list(self.iter_keywords(**{"includeExtendedDataFields": True}))
        df_keywords_SP = pd.json_normalize(list_keywords_SP)
        df_keywords_SP = self.TZ_Deal(df_keywords_SP,
                                      ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        return self.columnsName_modify(df_keywords_SP)

    def targets_ETL(self):
        list_targets = list(self.iter_targets(**{"includeExtendedDataFields": True}))
        df_targets = pd.json_normalize(list_targets)
        df_targets = self.TZ_Deal(df_targets, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        df_targets['resolvedExpressions_type'] = df_targets['resolvedExpression'].map(
            lambda x: self.get_keyOvalue(x, 'type'))
        df_targets['resolvedExpressions_value'] = df_targets['resolvedExpression'].map(
            lambda x: self.get_keyOvalue(x, 'value'))
        df_targets['expression_type'] = df_targets['expression'].map(
            lambda x: self.get_keyOvalue(x, 'type'))
        df_targets['expression_value'] = df_targets['expression'].map(
            lambda x: self.get_keyOvalue(x, 'value'))
        return self.columnsName_modify(df_targets)

    def negative_targets_ETL(self):
        list_targets = list(self.iter_negativetargeting(**{"includeExtendedDataFields": True}))
        df_targets = pd.json_normalize(list_targets)
        df_targets = self.TZ_Deal(df_targets, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        df_targets['resolvedExpressions_type'] = df_targets['resolvedExpression'].map(
            lambda x: self.get_keyOvalue(x, 'type'))
        df_targets['resolvedExpressions_value'] = df_targets['resolvedExpression'].map(
            lambda x: self.get_keyOvalue(x, 'value'))
        df_targets['expression_type'] = df_targets['expression'].map(
            lambda x: self.get_keyOvalue(x, 'type'))
        df_targets['expression_value'] = df_targets['expression'].map(
            lambda x: self.get_keyOvalue(x, 'value'))
        return self.columnsName_modify(df_targets)

    def budget_ETL(self, campaign_ids: list):
        list_budget = self.get_budget(campaign_ids=campaign_ids)['success']
        df_budget = pd.json_normalize(list_budget)
        df_budget = self.TZ_Deal(df_budget, ["usageUpdatedTimestamp"])
        return self.columnsName_modify(df_budget)

    def reportV3_campaign_spCampaignsETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:","reportV3_campaign_spCampaignsETL",'\n',"table_name:","SP_spCampaigns_campaignV3")
        if len(conn.query_df(f"select * from AmazonReport.SP_spCampaigns_campaignV3 where date='{params['startDate']}'"))>0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'

        params['reportType'] = "spCampaigns"
        params['columns'] = [
            'campaignName', 'campaignId', 'campaignStatus', 'campaignBudgetAmount', 'campaignBudgetType',
            'campaignRuleBasedBudgetAmount', 'campaignApplicableBudgetRuleId', 'campaignApplicableBudgetRuleName',
            'campaignBudgetCurrencyCode', 'topOfSearchImpressionShare',
            'impressions', 'clicks', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d',
            'purchasesSameSku1d',
            'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d',
            'unitsSoldClicks7d',
            'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d',
            'attributedSalesSameSku1d',
            'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d',
            'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d',
            'kindleEditionNormalizedPagesRoyalties14d', 'date', 'campaignBiddingStrategy', 'costPerClick',
            'clickThroughRate', 'spend'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['campaign']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SP_spCampaigns_campaignV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SP_spCampaigns_campaignV3")

        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_adGroup_spCampaignsETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_adGroup_spCampaignsETL", '\n', "table_name:", "SP_spCampaigns_adGroupV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SP_spCampaigns_adGroupV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "spCampaigns"
        params['columns'] = [
            'adGroupName', 'adGroupId', 'adStatus','campaignName', 'campaignId', 'campaignStatus', 'campaignBudgetAmount', 'campaignBudgetType',
            'campaignRuleBasedBudgetAmount', 'campaignApplicableBudgetRuleId', 'campaignApplicableBudgetRuleName',
            'campaignBudgetCurrencyCode',
            'impressions', 'clicks', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d',
            'purchasesSameSku1d',
            'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d',
            'unitsSoldClicks7d',
            'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d',
            'attributedSalesSameSku1d',
            'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d',
            'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d',
            'kindleEditionNormalizedPagesRoyalties14d', 'date', 'campaignBiddingStrategy', 'costPerClick',
            'clickThroughRate', 'spend'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['adGroup',"campaign"]
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SP_spCampaigns_adGroupV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SP_spCampaigns_adGroupV3")

        df_rel =  df_report[params['columns']]
        return df_rel

    #
    def reportV3_campaignPlacement_spCampaignsETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        params['reportType'] = "spCampaigns"
        print("func_name:", "reportV3_campaignPlacement_spCampaignsETL", '\n', "table_name:", "SP_spCampaigns_placementV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SP_spCampaigns_placementV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['columns'] = [
            'placementClassification','campaignName', 'campaignId','campaignStatus',  'campaignBudgetAmount', 'campaignBudgetType',
            'campaignRuleBasedBudgetAmount', 'campaignApplicableBudgetRuleId', 'campaignApplicableBudgetRuleName',
            'campaignBudgetCurrencyCode',
            'impressions', 'clicks', 'cost', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d',
            'purchasesSameSku1d',
            'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d', 'unitsSoldClicks1d',
            'unitsSoldClicks7d',
            'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d', 'sales14d', 'sales30d',
            'attributedSalesSameSku1d',
            'attributedSalesSameSku7d', 'attributedSalesSameSku14d', 'attributedSalesSameSku30d', 'unitsSoldSameSku1d',
            'unitsSoldSameSku7d', 'unitsSoldSameSku14d', 'unitsSoldSameSku30d', 'kindleEditionNormalizedPagesRead14d',
            'kindleEditionNormalizedPagesRoyalties14d', 'date', 'campaignBiddingStrategy', 'costPerClick',
            'clickThroughRate', 'spend'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['campaign','campaignPlacement']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        # print(df_report)
        # print(df_report.columns)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SP_spCampaigns_placementV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SP_spCampaigns_placementV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_targeting_spTargetingETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        params['reportType'] = "spTargeting"
        print("func_name:", "reportV3_targeting_spTargetingETL", '\n', "table_name:",
              "SP_spTargeting_targetingV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SP_spTargeting_targetingV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['columns'] = [
            'adKeywordStatus',
            'impressions', 'clicks', 'costPerClick', 'clickThroughRate', 'cost', 'purchases1d', 'purchases7d',
            'purchases14d',
            'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d',
            'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d',
            'sales14d',
            'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d',
            'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d',
            'unitsSoldSameSku30d',
            'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'salesOtherSku7d',
            'unitsSoldOtherSku7d', 'acosClicks7d', 'acosClicks14d', 'roasClicks7d', 'roasClicks14d', 'keywordId',
            'keyword',
            'campaignBudgetCurrencyCode', 'date', 'portfolioId', 'campaignName', 'campaignId',
            'campaignBudgetType', 'campaignBudgetAmount', 'campaignStatus', 'keywordBid', 'adGroupName', 'adGroupId',
            'keywordType',
            'matchType', 'targeting', 'topOfSearchImpressionShare'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['targeting']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SP_spTargeting_targetingV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SP_spTargeting_targetingV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_searchTerm_spSearchTermETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_searchTerm_spSearchTermETL", '\n', "table_name:",
              "SP_spSearchTerm_searchTermV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SP_spSearchTerm_searchTermV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "spSearchTerm"
        params['columns'] = [
            'adKeywordStatus',
            'impressions', 'clicks', 'costPerClick', 'clickThroughRate', 'cost', 'purchases1d', 'purchases7d',
            'purchases14d',
            'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d',
            'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d',
            'sales14d',
            'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d',
            'attributedSalesSameSku30d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d', 'unitsSoldSameSku14d',
            'unitsSoldSameSku30d',
            'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d', 'salesOtherSku7d',
            'unitsSoldOtherSku7d', 'acosClicks7d', 'acosClicks14d', 'roasClicks7d', 'roasClicks14d', 'keywordId',
            'keyword',
            'campaignBudgetCurrencyCode', 'date', 'portfolioId', 'searchTerm', 'campaignName', 'campaignId',
            'campaignBudgetType', 'campaignBudgetAmount', 'campaignStatus', 'keywordBid', 'adGroupName', 'adGroupId',
            'keywordType',
            'matchType', 'targeting'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['searchTerm']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SP_spSearchTerm_searchTermV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SP_spSearchTerm_searchTermV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_advertiser_spAdvertisedProductETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_advertiser_spAdvertisedProductETL", '\n', "table_name:",
              "SP_spAdvertisedProduct_advertiserV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SP_spAdvertisedProduct_advertiserV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "spAdvertisedProduct"
        params['columns'] = [
            'date', 'campaignName', 'campaignId', 'adGroupName', 'adGroupId', 'adId', 'portfolioId', 'impressions',
            'clicks', 'costPerClick', 'clickThroughRate', 'cost', 'spend', 'campaignBudgetCurrencyCode',
            'campaignBudgetAmount',
            'campaignBudgetType', 'campaignStatus', 'advertisedAsin', 'advertisedSku', 'purchases1d', 'purchases7d',
            'purchases14d',
            'purchases30d', 'purchasesSameSku1d', 'purchasesSameSku7d', 'purchasesSameSku14d', 'purchasesSameSku30d',
            'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d',
            'sales14d',
            'sales30d', 'attributedSalesSameSku1d', 'attributedSalesSameSku7d', 'attributedSalesSameSku14d',
            'attributedSalesSameSku30d', 'salesOtherSku7d', 'unitsSoldSameSku1d', 'unitsSoldSameSku7d',
            'unitsSoldSameSku14d',
            'unitsSoldSameSku30d', 'unitsSoldOtherSku7d', 'kindleEditionNormalizedPagesRead14d',
            'kindleEditionNormalizedPagesRoyalties14d', 'acosClicks7d', 'acosClicks14d', 'roasClicks7d', 'roasClicks14d'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['advertiser']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])
        # TODO
        df_report[df_report.select_dtypes(["object"]).columns] = df_report[df_report.select_dtypes(["object"]).columns].fillna('')
        df_report[df_report.select_dtypes(["string"]).columns] = df_report[df_report.select_dtypes(["string"]).columns].fillna('')

        # print(df_report.info())
        conn.insert_df("AmazonReport.SP_spAdvertisedProduct_advertiserV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SP_spAdvertisedProduct_advertiserV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_asin_spPurchasedProductETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_asin_spPurchasedProductETL", '\n', "table_name:",
              "SP_spPurchasedProduct_asinV3")
        if params.get('Download') in [None,False]:
            if len(conn.query_df(
                    f"select * from AmazonReport.SP_spPurchasedProduct_asinV3 where date='{params['startDate']}'")) > 0:
                logging.info("数据已存在...")
                time.sleep(8.5)
                return 'Pass'
        params['reportType'] = "spPurchasedProduct"
        params['columns'] = [
            'date', 'portfolioId', 'campaignName', 'campaignId', 'adGroupName', 'adGroupId', 'keywordId', 'keyword',
            'keywordType', 'advertisedAsin', 'purchasedAsin', 'advertisedSku', 'campaignBudgetCurrencyCode',
            'matchType',
            'unitsSoldClicks1d', 'unitsSoldClicks7d', 'unitsSoldClicks14d', 'unitsSoldClicks30d', 'sales1d', 'sales7d',
            'sales14d',
            'sales30d', 'purchases1d', 'purchases7d', 'purchases14d', 'purchases30d', 'unitsSoldOtherSku1d',
            'unitsSoldOtherSku7d',
            'unitsSoldOtherSku14d', 'unitsSoldOtherSku30d', 'salesOtherSku1d', 'salesOtherSku7d', 'salesOtherSku14d',
            'salesOtherSku30d', 'purchasesOtherSku1d', 'purchasesOtherSku7d', 'purchasesOtherSku14d',
            'purchasesOtherSku30d',
            'kindleEditionNormalizedPagesRead14d', 'kindleEditionNormalizedPagesRoyalties14d'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['asin']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])
        df_report['advertisedAsin'] = df_report['advertisedAsin'].map(lambda x: str(x) if x is not None else '-')
        df_report['advertisedSku'] = df_report['advertisedSku'].map(lambda x: str(x) if x is not None else '-')
        print(df_report.dtypes)
        # df_report.to_excel('ATEST.xlsx')
        if params.get('Download') in [None, False]:
            conn.insert_df("AmazonReport.SP_spPurchasedProduct_asinV3", df_report[params['columns']])
            time.sleep(1.05)
            print("插入完成SP_spPurchasedProduct_asinV3")


        df_rel =  df_report[params['columns']]
        return df_rel


class SB_ETL(SBClient, Common_ETLMethod):
    def campaigns_ETL(self):
        list_campaign_SB = list(self.iter_campaigns(**{"includeExtendedDataFields": True}))
        df_campaign = pd.json_normalize(list_campaign_SB)
        df_campaign = self.placement_segmentsplit(df_campaign, "bidding.bidAdjustmentsByPlacement")
        df_campaign = self.time_stamp_convert(df_campaign, ["extendedData.creationDate", "extendedData.lastUpdateDate"])
        # print(df_campaign)
        return self.columnsName_modify(df_campaign)

    def adGroups_ETL(self):
        list_adGroup_SB = list(self.iter_adGroups(**{"includeExtendedDataFields": True}))
        df_adGroup_SP = pd.json_normalize(list_adGroup_SB)
        df_adGroup_SP = self.time_stamp_convert(df_adGroup_SP,
                                                ["extendedData.creationDate", "extendedData.lastUpdateDate"])
        return self.columnsName_modify(df_adGroup_SP)

    def ads_ETL(self):
        list_adId_SB = list(self.iter_ads(**{"includeExtendedDataFields": True}))
        df_adId_SP = pd.json_normalize(list_adId_SB)
        df_adId_SP = self.time_stamp_convert(df_adId_SP, ["extendedData.creationDate", "extendedData.lastUpdateDate"])
        return self.columnsName_modify(df_adId_SP)

    def keywords_ETL(self):
        list_keywords_SB = [row for _ in list(self.iter_keywords()) for row in _]
        df_keywords_SP = pd.json_normalize(list_keywords_SB)
        return self.columnsName_modify(df_keywords_SP)

    def targets_ETL(self):
        list_targets = list(self.iter_targets())
        df_targets = pd.json_normalize(list_targets)
        # df_targets = self.TZ_Deal(df_targets, ["extendedData.creationDateTime", "extendedData.lastUpdateDateTime"])
        # df_targets = self.expression_split(df_targets, "resolvedExpressions")
        df_targets = self.id_type_trans(df_targets)
        df_targets['expressions_type'] = df_targets['expressions'].map(
            lambda x: self.get_keyOvalue(x, 'type'))
        df_targets['expressions_value'] = df_targets['expressions'].map(
            lambda x: self.get_keyOvalue(x, 'value'))
        df_targets['resolvedExpressions_type'] = df_targets['resolvedExpressions'].map(lambda x:self.get_keyOvalue(x,'type'))
        df_targets['resolvedExpressions_value'] = df_targets['resolvedExpressions'].map(lambda x:self.get_keyOvalue(x,'value'))
        return self.columnsName_modify(df_targets)

    def budget_ETL(self, campaign_ids: list):
        list_budget = self.get_budget(campaignIds=campaign_ids)['success']
        df_budget = pd.json_normalize(list_budget)
        df_budget = self.TZ_Deal(df_budget, ["usageUpdatedTimestamp"])
        return self.columnsName_modify(df_budget)

    def reportV3_campaign_sbCampaigns_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_campaign_sbCampaigns_ETL", '\n', "table_name:",
              "SB_sbCampaigns_campaignV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbCampaigns_campaignV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbCampaigns" #sbCampaigns
        params['columns'] = ['campaignName','campaignId','campaignStatus',
                             'campaignBudgetAmount', 'campaignBudgetCurrencyCode', 'campaignBudgetType','impressions',
                             'clicks', 'cost','addToCart', 'addToCartClicks', 'addToCartRate','brandedSearches','brandedSearchesClicks'
                             ,'costType','detailPageViews','detailPageViewsClicks','eCPAddToCart','newToBrandDetailPageViewRate',
                             'newToBrandDetailPageViews', 'newToBrandDetailPageViewsClicks',
                            'newToBrandECPDetailPageView','newToBrandSales', 'newToBrandSalesClicks','newToBrandPurchases',
                             'newToBrandPurchasesClicks', 'newToBrandPurchasesPercentage','newToBrandPurchasesRate','newToBrandUnitsSold',
                             'newToBrandUnitsSoldClicks', 'newToBrandUnitsSoldPercentage','newToBrandSalesPercentage','date', 'purchases',
                             'purchasesClicks', 'purchasesPromoted', 'sales', 'salesClicks', 'salesPromoted','topOfSearchImpressionShare',
                             'unitsSold', 'unitsSoldClicks', 'video5SecondViewRate',
                             'video5SecondViews', 'videoCompleteViews', 'videoFirstQuartileViews', 'videoMidpointViews',
                             'videoThirdQuartileViews', 'videoUnmutes', 'viewabilityRate', 'viewableImpressions',
                             'viewClickThroughRate'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['campaign']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbCampaigns_campaignV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbCampaigns_campaignV3")
        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_adGroup_sbAdGroup_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_adGroup_sbAdGroup_ETL", '\n', "table_name:",
              "SB_sbAdGroup_adGroupV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbAdGroup_adGroupV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbAdGroup" #sbCampaigns
        params['columns'] = ['campaignName','campaignId','campaignBudgetCurrencyCode','adGroupName','adGroupId', 'impressions',  'clicks', 'cost',
                                'addToCartRate',  'brandedSearches', 'brandedSearchesClicks','detailPageViews', 'detailPageViewsClicks',
                                    'eCPAddToCart','newToBrandSales', 'newToBrandSalesClicks','newToBrandPurchases', 'newToBrandPurchasesClicks',
                                  'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks','date',
                                 'purchases', 'purchasesClicks',  'sales', 'salesClicks',
                                'unitsSold', 'unitsSoldClicks', 'videoCompleteViews',
                                 'videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews', 'videoUnmutes',
                                 'viewabilityRate', 'viewClickThroughRate'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['adGroup']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbAdGroup_adGroupV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbAdGroup_adGroupV3")
        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_sbCampaignPlacement_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_sbCampaignPlacement_ETL", '\n', "table_name:",
              "SB_sbCampaigns_placementV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbCampaigns_placementV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbCampaignPlacement" #sbCampaigns
        params['columns'] = ['placementClassification','campaignName','campaignId','campaignStatus','campaignBudgetAmount','campaignBudgetType','campaignBudgetCurrencyCode',
                            'impressions','clicks', 'cost','addToCart', 'addToCartClicks', 'addToCartRate', 'brandedSearches',
                            'brandedSearchesClicks', 'costType','detailPageViews','detailPageViewsClicks', 'eCPAddToCart', 'newToBrandDetailPageViewRate',
                            'newToBrandDetailPageViews', 'newToBrandDetailPageViewsClicks','newToBrandECPDetailPageView','newToBrandSales', 'newToBrandSalesClicks',
                            'newToBrandPurchases', 'newToBrandPurchasesClicks','newToBrandPurchasesPercentage', 'newToBrandPurchasesRate',
                            'newToBrandUnitsSold','newToBrandUnitsSoldClicks', 'newToBrandUnitsSoldPercentage','newToBrandSalesPercentage',
                            'date',  'purchases', 'purchasesClicks', 'purchasesPromoted',  'sales', 'salesClicks', 'salesPromoted',
                            'unitsSold', 'unitsSoldClicks', 'video5SecondViewRate', 'video5SecondViews','videoCompleteViews', 'videoFirstQuartileViews',
                            'videoMidpointViews', 'videoThirdQuartileViews', 'videoUnmutes','viewabilityRate', 'viewableImpressions', 'viewClickThroughRate'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['campaignPlacement']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbCampaigns_placementV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbCampaigns_placementV3")
        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_sbTargeting_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_sbTargeting_ETL", '\n', "table_name:",
              "SB_sbTargeting_targetingV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbTargeting_targetingV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbTargeting" #sbCampaigns
        params['columns'] = [
            'campaignName','campaignId','campaignStatus','campaignBudgetAmount','campaignBudgetType','campaignBudgetCurrencyCode',
            'adGroupName','adGroupId','keywordText','keywordId', 'adKeywordStatus',  'keywordType', 'matchType','keywordBid',
            'targetingText', 'targetingId', 'targetingType', 'impressions', 'clicks', 'cost',
            'addToCart', 'addToCartClicks', 'addToCartRate',   'brandedSearches', 'brandedSearchesClicks',
            'costType', 'detailPageViews', 'detailPageViewsClicks','eCPAddToCart','newToBrandDetailPageViewRate',
            'newToBrandDetailPageViews','newToBrandDetailPageViewsClicks','newToBrandECPDetailPageView','newToBrandSales',
            'newToBrandSalesClicks','newToBrandPurchases', 'newToBrandPurchasesClicks',
            'newToBrandPurchasesPercentage', 'newToBrandPurchasesRate',  'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks', 'newToBrandUnitsSoldPercentage',
            'newToBrandSalesPercentage','date',    'purchases', 'purchasesClicks', 'purchasesPromoted',
            'sales', 'salesClicks', 'salesPromoted', 'targetingExpression',  'topOfSearchImpressionShare'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['targeting']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbTargeting_targetingV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbTargeting_targetingV3")
        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_sbSearchTerm_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_sbSearchTerm_ETL", '\n', "table_name:",
              "SB_sbSearchTerm_searchTermV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbSearchTerm_searchTermV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbSearchTerm" #sbCampaigns
        params['columns'] = [
            'campaignName','campaignId','campaignStatus','campaignBudgetAmount','campaignBudgetType', 'campaignBudgetCurrencyCode', 'adGroupName',
            'adGroupId',  'keywordText','keywordId','adKeywordStatus', 'matchType','keywordBid','impressions',  'clicks', 'cost', 'costType', 'date',
             'purchases', 'purchasesClicks', 'sales', 'salesClicks', 'searchTerm', 'unitsSold',
            'video5SecondViewRate', 'video5SecondViews', 'videoCompleteViews', 'videoFirstQuartileViews', 'videoMidpointViews',
            'videoThirdQuartileViews', 'videoUnmutes', 'viewabilityRate', 'viewableImpressions', 'viewClickThroughRate'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['searchTerm']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbSearchTerm_searchTermV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbSearchTerm_searchTermV3")
        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_sbAds_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_sbAds_ETL", '\n', "table_name:",
              "SB_sbAds_adsV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbAds_adsV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbAds" #sbCampaigns
        params['columns'] = [
            'campaignName', 'campaignId','campaignStatus','campaignBudgetAmount','campaignBudgetCurrencyCode', 'campaignBudgetType',
            'adGroupName','adGroupId', 'adId','impressions','clicks', 'cost',
            'addToCart', 'addToCartClicks', 'addToCartRate',   'brandedSearches',
            'brandedSearchesClicks',  'costType','detailPageViews', 'detailPageViewsClicks','eCPAddToCart',
            'newToBrandDetailPageViewRate', 'newToBrandDetailPageViews','newToBrandDetailPageViewsClicks','newToBrandECPDetailPageView',
            'newToBrandSales','newToBrandSalesClicks', 'newToBrandPurchases',
            'newToBrandPurchasesClicks', 'newToBrandPurchasesPercentage', 'newToBrandPurchasesRate',  'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
            'newToBrandUnitsSoldPercentage','newToBrandSalesPercentage','date','purchases', 'purchasesClicks', 'purchasesPromoted', 'sales', 'salesClicks',
            'salesPromoted',  'unitsSold', 'unitsSoldClicks', 'video5SecondViewRate', 'video5SecondViews',
            'videoCompleteViews', 'videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews', 'videoUnmutes',
            'viewabilityRate', 'viewableImpressions'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['ads']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report['date'] = df_report['date'].map(lambda x: params['startDate'] if type(x) != int else datetime.fromtimestamp(int(x)/1000).date(),na_action='ignore')#,na_action='ignore'
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbAds_adsV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbAds_adsV3")

        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_purchasedAsinRecord_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_purchasedAsinRecord_ETL", '\n', "table_name:",
              "SB_sbPurchasedProduct_asinV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_sbPurchasedProduct_asinV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sbPurchasedProduct"
        params['columns'] = [
            'campaignId', 'adGroupId', 'date', 'campaignBudgetCurrencyCode', 'campaignName', 'adGroupName',
            'attributionType', 'purchasedAsin', 'productName', 'productCategory', 'sales14d', 'orders14d',
            'unitsSold14d',
            'newToBrandSales14d', 'newToBrandPurchases14d', 'newToBrandUnitsSold14d', 'newToBrandSalesPercentage14d',
            'newToBrandPurchasesPercentage14d', 'newToBrandUnitsSoldPercentage14d'
        ]  # 'startDate', 'endDate',
        params['groupby'] = ['purchasedAsin']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])
        df_report[df_report.select_dtypes(["object"]).columns] = df_report[df_report.select_dtypes(["object"]).columns].fillna('')
        df_report[df_report.select_dtypes(["string"]).columns] = df_report[df_report.select_dtypes(["string"]).columns].fillna('')
        # print(df_report)
        conn.insert_df("AmazonReport.SB_sbPurchasedProduct_asinV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SB_sbPurchasedProduct_asinV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV2_campaignsRecord_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_campaignsRecord_ETL", '\n', "table_name:",
              "SB_campaignsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_campaignsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = ['campaignId','campaignName', 'impressions', 'clicks', 'cost',
                                 'attributedBrandedSearches14d','applicableBudgetRuleId','applicableBudgetRuleName',
                                 'attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d',
                                 'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d',
                                 'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d',
                                 'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget',
                                 'campaignBudgetType','campaignRuleBasedBudget','campaignStatus',
                                 'dpv14d','topOfSearchImpressionShare','unitsSold14d'
                                 ]
            # print(metric)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric)
        # print(list_report)
        df_report = pd.json_normalize(list_report)

        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SB_campaignsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_campaignsV2")


        df_rel =  df_report[metric]
        return df_rel

    def reportV2_campaignsVideo_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_campaignsVideo_ETL", '\n', "table_name:",
              "SB_campaignsVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_campaignsVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = [
                                 'campaignId','campaignName','impressions','clicks','cost',
                                 'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU',
                                 'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d',
                                 'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU',
                                 'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d',
                                 'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus',
                                 'currency','dpv14d','topOfSearchImpressionShare','vctr','vtr','video5SecondViewRate','video5SecondViews',
                                 'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes',
                                 'viewableImpressions'
                                 ]
            # print(metric)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, creative_type='video')
        # print(list_report)
        df_report = pd.json_normalize(list_report)

        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SB_campaignsVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_campaignsVideoV2")
        # print(df_report[metric].info())

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_placementRecord_ETL(self, conn, params:dict=None):
        print(params)
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_placementRecord_ETL", '\n', "table_name:",
              "SB_campaignsPlacementV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_campaignsPlacementV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = ['campaignId','campaignName','impressions','clicks','cost',
                                 'attributedBrandedSearches14d','applicableBudgetRuleId','applicableBudgetRuleName',
                                 'attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d',
                                 'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d',
                                 'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d',
                                 'attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d',
                                 'campaignBudget','campaignBudgetType','campaignRuleBasedBudget','campaignStatus','dpv14d','unitsSold14d'
                                 ]  # 'placement'
            # print(metric)
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, segment='placement')
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['placement'])

        conn.insert_df("AmazonReport.SB_campaignsPlacementV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_campaignsPlacementV2")
        # print(df_report[metric].info())

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_placementVideo_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_placementVideo_ETL", '\n', "table_name:",
              "SB_campaignsPlacementVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_campaignsPlacementVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = [
                'campaignId','campaignName','impressions','clicks','cost',
                'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU',
                'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d',
                'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU',
                'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d',
                'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus',
                'currency','dpv14d','vctr','vtr','video5SecondViewRate','video5SecondViews','videoCompleteViews',
                'videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes','viewableImpressions',
            ]  # 'placement'
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, segment='placement', creative_type='video')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['placement'])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_campaignsPlacementVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_campaignsPlacementVideoV2")


        df_rel =  df_report[metric]
        return df_rel

    def reportV2_adGroupsRecord_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adGroupsRecord_ETL", '\n', "table_name:",
              "SB_adGroupsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_adGroupsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'adGroups'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','impressions', 'clicks', 'cost',
                'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU',
                'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d',
                'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU',
                'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d',
                'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus',
                'dpv14d','unitsSold14d',
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric)
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_adGroupsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_adGroupsV2")


        df_rel =  df_report[metric]
        return df_rel

    def reportV2_adGroupsVideo_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        params['record_type'] = 'adGroups'
        print("func_name:", "reportV2_adGroupsVideo_ETL", '\n', "table_name:",
              "SB_adGroupsVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_adGroupsVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','impressions','clicks','cost',
                'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU',
                'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d',
                'attributedOrdersNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d',
                'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d',
                'campaignBudget','campaignBudgetType','campaignStatus','currency','vctr','vtr','video5SecondViewRate',
                'video5SecondViews','videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews',
                'videoUnmutes','viewableImpressions','dpv14d'
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, creative_type='video')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_adGroupsVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_adGroupsVideoV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_targetsRecord_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_targetsRecord_ETL", '\n', "table_name:",
              "SB_targetsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_targetsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'targets'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions', 'clicks', 'cost',
                'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d',
                'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d','attributedSales14d',
                'attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d',
                'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget',
                'campaignBudgetType','campaignStatus','dpv14d','targetingExpression','targetingType',
                'topOfSearchImpressionShare','unitsSold14d'
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric)
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_targetsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_targetsV2")


        df_rel =  df_report[metric]
        return df_rel

    def reportV2_targetsVideo_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_targetsVideo_ETL", '\n', "table_name:",
              "SB_targetsVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_targetsVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'targets'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions','clicks','cost',
                'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU',
                'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d',
                'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU',
                'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d',
                'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType','campaignStatus',
                'currency','dpv14d','targetingExpression','targetingType','topOfSearchImpressionShare','vctr','vtr',
                'video5SecondViewRate','video5SecondViews','videoCompleteViews','videoFirstQuartileViews','videoMidpointViews',
                'videoThirdQuartileViews','videoUnmutes','viewableImpressions',
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, creative_type='video')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_targetsVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_targetsVideoV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_keywordsRecord_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_keywordsRecord_ETL", '\n', "table_name:",
              "SB_keywordsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_keywordsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'keywords'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions', 'clicks', 'cost',
                'attributedBrandedSearches14d','applicableBudgetRuleId','applicableBudgetRuleName','attributedConversions14d',
                'attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d',
                'attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d','attributedSales14d',
                'attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d',
                'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget',
                'campaignBudgetType','campaignRuleBasedBudget','campaignStatus','dpv14d','keywordBid','keywordStatus',
                'matchType','searchTermImpressionRank','searchTermImpressionShare','topOfSearchImpressionShare','unitsSold14d',
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric)
        # print(list_campaigns_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_keywordsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_keywordsV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_keywordsVideo_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_keywordsVideo_ETL", '\n', "table_name:",
              "SB_keywordsVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_keywordsVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'keywords'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions', 'clicks', 'cost',
                'attributedBrandedSearches14d','attributedConversions14d','attributedConversions14dSameSKU',
                'attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d',
                'attributedOrdersNewToBrandPercentage14d','attributedSales14d','attributedSales14dSameSKU',
                'attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d',
                'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType',
                'campaignStatus','currency','dpv14d','keywordBid','keywordStatus','matchType','topOfSearchImpressionShare',
                'vctr','vtr','video5SecondViewRate','video5SecondViews','videoCompleteViews','videoFirstQuartileViews',
                'videoMidpointViews','videoThirdQuartileViews','videoUnmutes','viewableImpressions',
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, creative_type='video')
        # print(list_campaigns_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_keywordsVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_keywordsVideoV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_searchtermsRecord_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_searchtermsRecord_ETL", '\n', "table_name:",
              "SB_keywordsQueryV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_keywordsQueryV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'keywords'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions', 'clicks', 'cost',
                'attributedConversions14d','attributedSales14d','campaignBudget','campaignBudgetType','campaignStatus','keywordBid',
                'keywordStatus','matchType','searchTermImpressionRank','searchTermImpressionShare'
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, segment='query')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['query'])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_keywordsQueryV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_keywordsQueryV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_searchtermsVideo_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_searchtermsVideo_ETL", '\n', "table_name:",
              "SB_keywordsQueryVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_keywordsQueryVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'keywords'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','keywordId','keywordText','impressions','clicks','cost',
                'attributedConversions14d','attributedSales14d','campaignBudget','campaignBudgetType','campaignStatus',
                'keywordBid','keywordStatus','matchType','vctr','vtr','video5SecondViewRate','video5SecondViews',
                'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews',
                'videoUnmutes','viewableImpressions',
            ]  # 'query','currency'

        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, segment='query', creative_type='video')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['query'])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_keywordsQueryVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_keywordsQueryVideoV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_adsRecord_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adsRecord_ETL", '\n', "table_name:",
              "SB_adsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_adsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'ads'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','adId','impressions','clicks','cost',
                'applicableBudgetRuleId','applicableBudgetRuleName','attributedBrandedSearches14d','attributedConversions14d',
                'attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d','attributedOrderRateNewToBrand14d',
                'attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d','attributedSales14d',
                'attributedSales14dSameSKU','attributedSalesNewToBrand14d','attributedSalesNewToBrandPercentage14d',
                'attributedUnitsOrderedNewToBrand14d','attributedUnitsOrderedNewToBrandPercentage14d',
                'campaignBudget','campaignBudgetType','campaignRuleBasedBudget','campaignStatus',
                'dpv14d','unitsSold14d','vctr',
            ]  #

        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])

        conn.insert_df("AmazonReport.SB_adsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_adsV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_adsVideo_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adsVideo_ETL", '\n', "table_name:",
              "SB_adsVideoV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SB_adsVideoV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'ads'

        metric = [
                'campaignId','campaignName','adGroupId','adGroupName','adId', 'impressions', 'clicks', 'cost',
                'applicableBudgetRuleId','applicableBudgetRuleName','attributedBrandedSearches14d',
                'attributedConversions14d','attributedConversions14dSameSKU','attributedDetailPageViewsClicks14d',
                'attributedOrderRateNewToBrand14d','attributedOrdersNewToBrand14d','attributedOrdersNewToBrandPercentage14d',
                'attributedSales14d','attributedSales14dSameSKU','attributedSalesNewToBrand14d',
                'attributedSalesNewToBrandPercentage14d','attributedUnitsOrderedNewToBrand14d',
                'attributedUnitsOrderedNewToBrandPercentage14d','campaignBudget','campaignBudgetType',
                'campaignRuleBasedBudget','campaignStatus','currency','dpv14d','vctr','vtr',
                'video5SecondViewRate','video5SecondViews','videoCompleteViews','videoFirstQuartileViews',
                'videoMidpointViews','videoThirdQuartileViews','videoUnmutes','viewableImpressions',
            ]  #
        # print(date)
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, creative_type='video')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=[])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SB_adsVideoV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SB_adsVideoV2")

        df_rel =  df_report[metric]
        return df_rel


class SD_ETL(SDClient, Common_ETLMethod):
    def campaigns_ETL(self):
        list_campaign_SD = self.get_campaigns()
        df_campaign = pd.json_normalize(list_campaign_SD)
        df_campaign['startDate'] = df_campaign['startDate'].map(
            lambda x: datetime.strptime(x, "%Y%m%d").date())  # 转换为标准时间格式
        df_campaign['portfolioId'] = df_campaign['portfolioId'].fillna(-1).astype("int64")  # 将portfolio列为空的填充为-1
        return self.columnsName_modify(df_campaign)

    def adGroups_ETL(self, **param):
        list_adGroups_SD = [row for _ in list(self.iter_adGroups(**param)) for row in _]
        df_adGroups_SD = pd.json_normalize(list_adGroups_SD)
        tactic = {"T00020": "Contextual targeting", "T00030": "Audiences targeting"}
        df_adGroups_SD["tactic_type"] = df_adGroups_SD['tactic'].map(tactic)  # T00020、T00030解释字段
        return self.columnsName_modify(df_adGroups_SD)

    def ads_ETL(self):
        list_ads_SD = [row for _ in list(self.iter_ads()) for row in _]
        df_ads_SD = pd.json_normalize(list_ads_SD)
        return self.columnsName_modify(df_ads_SD)

    def targets_ETL(self, **param):
        list_targets = [row for _ in list(self.iter_targets(**param)) for row in _]
        df_targets = pd.json_normalize(list_targets)
        # df_targets = self.expression_split(df_targets, "resolvedExpression")
        df_targets = self.id_type_trans(df_targets)
        df_targets['resolvedExpressions_type'] = df_targets['resolvedExpression'].map(lambda x:self.get_keyOvalue(x,'type'))
        df_targets['resolvedExpressions_value'] = df_targets['resolvedExpression'].map(lambda x:self.get_keyOvalue(x,'value'))
        return self.columnsName_modify(df_targets)

    def budget_ETL(self, campaignsIds: list):
        list_budget = self.get_budget(campaignIds=campaignsIds)['success']
        df_budget = pd.json_normalize(list_budget)
        df_budget = self.TZ_Deal(df_budget, ["usageUpdatedTimestamp"])
        return self.columnsName_modify(df_budget)

    def reportV3_campaign_sdCampaigns_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        params = self.config_params(params)
        print("func_name:", "reportV3_campaign_sdCampaigns_ETL", '\n', "table_name:",
              "SD_sdCampaigns_campaignV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_sdCampaigns_campaignV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdCampaigns"
        params['columns'] = [ 'campaignName', 'campaignId','campaignStatus','campaignBudgetAmount', 'impressions','clicks', 'cost',
                             'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',
                             'brandedSearches', 'brandedSearchesClicks','brandedSearchesViews', 'brandedSearchRate',
                             'costType', 'cumulativeReach','detailPageViews', 'detailPageViewsClicks','eCPAddToCart', 'eCPBrandSearch',
                             'newToBrandDetailPageViewClicks','newToBrandDetailPageViewRate', 'newToBrandDetailPageViews', 'newToBrandDetailPageViewViews', 'newToBrandECPDetailPageView',
                             'newToBrandSales','newToBrandPurchases', 'newToBrandPurchasesClicks', 'newToBrandSalesClicks','newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
                             'campaignBudgetCurrencyCode','date',
                             'impressionsViews','impressionsFrequencyAverage',  'purchases', 'purchasesClicks', 'purchasesPromotedClicks',
                             'sales','salesClicks', 'salesPromotedClicks', 'unitsSold', 'unitsSoldClicks', 'videoCompleteViews',
                             'videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews', 'videoUnmutes', 'viewabilityRate','viewClickThroughRate'
        ]  # 'startDate', 'endDate',


        params['groupby'] = ['campaign']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report.columns)
        conn.insert_df("AmazonReport.SD_sdCampaigns_campaignV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_sdCampaigns_campaignV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_campaignMT_sdCampaigns_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_campaignMT_sdCampaigns_ETL", '\n', "table_name:",
              "SD_sdCampaigns_campaignMatchedTargetV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_sdCampaigns_campaignMatchedTargetV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdCampaigns"
        params['columns'] = [
            'matchedTargetAsin','campaignName', 'campaignId','campaignStatus','campaignBudgetAmount', 'impressions','clicks', 'cost',
                             'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',
                             'brandedSearches', 'brandedSearchesClicks','brandedSearchesViews', 'brandedSearchRate',
                             'costType', 'detailPageViews', 'detailPageViewsClicks','eCPAddToCart', 'eCPBrandSearch',
                             'newToBrandSales','newToBrandPurchases', 'newToBrandPurchasesClicks', 'newToBrandSalesClicks','newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
                             'campaignBudgetCurrencyCode','date',
                             'impressionsViews', 'purchases', 'purchasesClicks', 'purchasesPromotedClicks',
                             'sales','salesClicks', 'salesPromotedClicks', 'unitsSold', 'unitsSoldClicks', 'videoCompleteViews',
                             'videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews', 'videoUnmutes', 'viewabilityRate','viewClickThroughRate'
        ]  # 'startDate', 'endDate',


        params['groupby'] = ['campaign',"matchedTarget"]
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report.columns)
        conn.insert_df("AmazonReport.SD_sdCampaigns_campaignMatchedTargetV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_sdCampaigns_campaignMatchedTargetV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_adgroup_sdAdGroup_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_adgroup_sdAdGroup_ETL", '\n', "table_name:",
              "SD_sdAdGroup_adGroupV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_sdAdGroup_adGroupV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdAdGroup"
        params['columns'] = ['campaignName','campaignId', 'adGroupName', 'adGroupId', 'impressions','clicks', 'cost',
                             'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',
                             'bidOptimization','brandedSearches', 'brandedSearchesClicks', 'brandedSearchesViews', 'brandedSearchRate',
                             'cumulativeReach','detailPageViews', 'detailPageViewsClicks', 'eCPAddToCart','eCPBrandSearch',
                             'newToBrandDetailPageViewClicks', 'newToBrandDetailPageViewRate','newToBrandDetailPageViews', 'newToBrandDetailPageViewViews', 'newToBrandECPDetailPageView',
                            'newToBrandSales', 'newToBrandPurchases', 'newToBrandPurchasesClicks','newToBrandSalesClicks', 'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
                            'campaignBudgetCurrencyCode','date', 'impressionsViews', 'impressionsFrequencyAverage',  'purchases',
                            'purchasesClicks', 'purchasesPromotedClicks', 'sales', 'salesClicks', 'salesPromotedClicks', 'unitsSold',
                            'unitsSoldClicks', 'videoCompleteViews', 'videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews',
                            'videoUnmutes', 'viewabilityRate', 'viewClickThroughRate'

        ]  # 'startDate', 'endDate',


        params['groupby'] = ['adGroup']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SD_sdAdGroup_adGroupV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_sdAdGroup_adGroupV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_adgroupMT_sdAdGroup_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_adgroupMT_sdAdGroup_ETL", '\n', "table_name:",
              "SD_sdAdGroup_adGroupMatchedTargetV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_sdAdGroup_adGroupMatchedTargetV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdAdGroup"
        params['columns'] = [
            'matchedTargetAsin','campaignName','campaignId', 'adGroupName', 'adGroupId', 'impressions','clicks', 'cost',
                             'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',
                             'bidOptimization','brandedSearches', 'brandedSearchesClicks', 'brandedSearchesViews', 'brandedSearchRate',
                             'detailPageViews', 'detailPageViewsClicks', 'eCPAddToCart','eCPBrandSearch',
                            'newToBrandSales', 'newToBrandPurchases', 'newToBrandPurchasesClicks','newToBrandSalesClicks', 'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
                            'campaignBudgetCurrencyCode','date', 'impressionsViews',   'purchases',
                            'purchasesClicks', 'purchasesPromotedClicks', 'sales', 'salesClicks', 'salesPromotedClicks', 'unitsSold',
                            'unitsSoldClicks', 'videoCompleteViews', 'videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews',
                            'videoUnmutes', 'viewabilityRate', 'viewClickThroughRate'

        ]  # 'startDate', 'endDate',


        params['groupby'] = ['adGroup',"matchedTarget"]
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SD_sdAdGroup_adGroupMatchedTargetV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_sdAdGroup_adGroupMatchedTargetV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_targeting_sdTargeting_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_targeting_sdTargeting_ETL", '\n', "table_name:",
              "SD_targeting_sdTargetingV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_targeting_sdTargetingV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdTargeting"
        params['columns'] = ['campaignName', 'campaignId','adGroupName','adGroupId', 'targetingText','targetingId','impressions','clicks', 'cost',
            'adKeywordStatus', 'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',
            'brandedSearches',
            'brandedSearchesClicks', 'brandedSearchesViews', 'brandedSearchRate',
            'detailPageViews', 'detailPageViewsClicks', 'eCPAddToCart', 'eCPBrandSearch',
            'newToBrandDetailPageViewClicks', 'newToBrandDetailPageViewRate', 'newToBrandDetailPageViews',
            'newToBrandDetailPageViewViews', 'newToBrandECPDetailPageView','newToBrandSales','newToBrandPurchases', 'newToBrandPurchasesClicks',
            'newToBrandSalesClicks', 'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
                'campaignBudgetCurrencyCode',
            'date',
              'impressionsViews',   'purchases', 'purchasesClicks',
            'purchasesPromotedClicks', 'sales', 'salesClicks', 'salesPromotedClicks', 'targetingExpression',
              'unitsSold', 'unitsSoldClicks', 'videoCompleteViews','videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews',
            'videoUnmutes', 'viewabilityRate', 'viewClickThroughRate'

        ]  # 'startDate', 'endDate',


        params['groupby'] = ['targeting']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SD_targeting_sdTargetingV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_targeting_sdTargetingV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_targetingMT_sdTargeting_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_targetingMT_sdTargeting_ETL", '\n', "table_name:",
              "SD_targeting_sdTargetingMatchedTargetV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_targeting_sdTargetingMatchedTargetV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdTargeting"
        params['columns'] = [
            'matchedTargetAsin',
            'campaignName', 'campaignId','adGroupName','adGroupId', 'targetingText','targetingId','impressions','clicks', 'cost',
            'adKeywordStatus', 'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',
            'brandedSearches',
            'brandedSearchesClicks', 'brandedSearchesViews', 'brandedSearchRate',
            'detailPageViews', 'detailPageViewsClicks', 'eCPAddToCart', 'eCPBrandSearch',
            'newToBrandSales','newToBrandPurchases', 'newToBrandPurchasesClicks',
            'newToBrandSalesClicks', 'newToBrandUnitsSold', 'newToBrandUnitsSoldClicks',
            'campaignBudgetCurrencyCode',
            'date',
              'impressionsViews',   'purchases', 'purchasesClicks',
            'purchasesPromotedClicks', 'sales', 'salesClicks', 'salesPromotedClicks', 'targetingExpression',
              'unitsSold', 'unitsSoldClicks', 'videoCompleteViews','videoFirstQuartileViews', 'videoMidpointViews', 'videoThirdQuartileViews',
            'videoUnmutes', 'viewabilityRate', 'viewClickThroughRate'

        ]  # 'startDate', 'endDate',


        params['groupby'] = ['targeting',"matchedTarget"]
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SD_targeting_sdTargetingMatchedTargetV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_targeting_sdTargetingMatchedTargetV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_advertiser_sdAdvertisedProduct_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_advertiser_sdAdvertisedProduct_ETL", '\n', "table_name:",
              "SD_advertiser_sdAdvertisedProductV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_advertiser_sdAdvertisedProductV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdAdvertisedProduct"
        params['columns'] = ['campaignName','campaignId','adGroupName','adGroupId','adId','impressions','clicks', 'cost',
            'addToCart', 'addToCartClicks', 'addToCartRate', 'addToCartViews',    'bidOptimization',
            'brandedSearches', 'brandedSearchesClicks', 'brandedSearchesViews', 'brandedSearchRate',
            'detailPageViews', 'detailPageViewsClicks','eCPAddToCart',
            'eCPBrandSearch','newToBrandDetailPageViewClicks',
            'newToBrandDetailPageViewRate', 'newToBrandDetailPageViews', 'newToBrandDetailPageViewViews', 'newToBrandECPDetailPageView','newToBrandSales',
            'newToBrandPurchases', 'newToBrandPurchasesClicks',  'newToBrandSalesClicks', 'newToBrandUnitsSold',
            'newToBrandUnitsSoldClicks',
            'campaignBudgetCurrencyCode',
               'cumulativeReach', 'date',    'impressionsFrequencyAverage', 'impressionsViews', 'promotedAsin', 'promotedSku', 'purchases', 'purchasesClicks', 'purchasesPromotedClicks',
               'sales',
            'salesClicks', 'salesPromotedClicks', 'unitsSold', 'unitsSoldClicks', 'videoCompleteViews', 'videoFirstQuartileViews',
            'videoMidpointViews', 'videoThirdQuartileViews', 'videoUnmutes', 'viewabilityRate', 'viewClickThroughRate'
        ]  # 'startDate', 'endDate',


        params['groupby'] = ['advertiser']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SD_advertiser_sdAdvertisedProductV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_advertiser_sdAdvertisedProductV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    def reportV3_asin_sdPurchasedProduct_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV3_asin_sdPurchasedProduct_ETL", '\n', "table_name:",
              "SD_asin_sdPurchasedProductV3")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_asin_sdPurchasedProductV3 where date='{params['startDate']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['reportType'] = "sdPurchasedProduct"
        params['columns'] = ['campaignName','campaignId', 'adGroupName','adGroupId', 'promotedAsin', 'promotedSku',
              'asinBrandHalo', 'campaignBudgetCurrencyCode',
            'conversionsBrandHalo', 'conversionsBrandHaloClicks', 'date',  'salesBrandHalo',
            'salesBrandHaloClicks',  'unitsSoldBrandHalo', 'unitsSoldBrandHaloClicks'

        ]  # 'startDate', 'endDate',


        params['groupby'] = ['asin']
        params['timeUnit'] = 'DAILY'
        list_report = self.get_v3_report(timeUnit=params['timeUnit'], groupby=params['groupby'],
                                         columns=params['columns'], startDate=params['startDate'],
                                         endDate=params['endDate'], reportType=params['reportType'])
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        df_report = self.type_trans(df_report, params['columns'], timeZone_, extra_columns=[])

        # print(df_report)
        conn.insert_df("AmazonReport.SD_asin_sdPurchasedProductV3", df_report[params['columns']])
        time.sleep(1.05)
        print("插入完成SD_asin_sdPurchasedProductV3")


        df_rel =  df_report[params['columns']]
        return df_rel

    campaigns_metrics = [
        'campaignId','campaignName','impressions','clicks','cost','attributedBrandedSearches14d',
        'attributedConversions1d','attributedConversions1dSameSKU','attributedConversions7d',
        'attributedConversions7dSameSKU','attributedConversions14d','attributedConversions14dSameSKU',
        'attributedConversions30d','attributedConversions30dSameSKU','attributedDetailPageView14d',
        'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU',
        'attributedSales7d','attributedSales7dSameSKU','attributedSales14d',
        'attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU',
        'attributedSalesNewToBrand14d','attributedUnitsOrdered1d','attributedUnitsOrdered7d',
        'attributedUnitsOrdered14d','attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d',
        'avgImpressionsFrequency','campaignBudget','campaignStatus','cumulativeReach','costType',
        'currency','vctr','vtr', 'viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d',
        'viewAttributedSales14d','viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d',
        'viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d',
        'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes',
    ]

    def reportV2_campaignsRecord_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_campaignsRecord_t2_ETL", '\n', "table_name:",
              "SD_campaignsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_campaignsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = self.campaigns_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020")
        # print(list_report)
        df_report = pd.json_normalize(list_report)

        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])

        conn.insert_df("AmazonReport.SD_campaignsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_campaignsV2—")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_campaignsRecord_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_campaignsRecord_t3_ETL", '\n', "table_name:",
              "SD_campaignsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_campaignsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = self.campaigns_metrics
        print("tactic:t3")
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030")#
        df_report = pd.json_normalize(list_report)
        print(df_report.columns)

        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])

        conn.insert_df("AmazonReport.SD_campaignsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_campaignsV2")

        df_rel =  df_report[metric]
        return df_rel

    adGroups_metrics = [
        'campaignId','campaignName','adGroupId','adGroupName','impressions','clicks','cost',
        'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU',
        'attributedConversions7d','attributedConversions7dSameSKU','attributedConversions14d',
        'attributedConversions14dSameSKU','attributedConversions30d','attributedConversions30dSameSKU',
        'attributedDetailPageView14d','attributedOrdersNewToBrand14d','attributedSales1d',
        'attributedSales1dSameSKU','attributedSales7d','attributedSales7dSameSKU',
        'attributedSales14d','attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU',
        'attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d','attributedUnitsOrdered30d',
        'attributedUnitsOrderedNewToBrand14d','avgImpressionsFrequency','bidOptimization','cumulativeReach',
        'currency','vctr','vtr', 'viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d',
        'viewAttributedSales14d','viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d',
        'viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d',
        'videoCompleteViews','videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes',
    ]

    def reportV2_adGroupsRecord_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adGroupsRecord_t2_ETL", '\n', "table_name:",
              "SD_adGroupsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_adGroupsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'adGroups'

        metric = self.adGroups_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020")
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])

        conn.insert_df("AmazonReport.SD_adGroupsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_adGroupsV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_adGroupsRecord_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adGroupsRecord_t3_ETL", '\n', "table_name:",
              "SD_adGroupsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_adGroupsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'

        params['record_type'] = 'adGroups'

        metric = self.adGroups_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020")
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SD_adGroupsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_adGroupsV2")

        df_rel =  df_report[metric]
        return df_rel

    productAds_metrics = [
        'campaignId','campaignName','adGroupId','adGroupName','adId','asin','sku', 'impressions', 'clicks','cost',
        'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU','attributedConversions7d',
        'attributedConversions7dSameSKU','attributedConversions14d','attributedConversions14dSameSKU',
        'attributedConversions30d','attributedConversions30dSameSKU','attributedDetailPageView14d',
        'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU','attributedSales7d',
        'attributedSales7dSameSKU','attributedSales14d','attributedSales14dSameSKU','attributedSales30d',
        'attributedSales30dSameSKU','attributedSalesNewToBrand14d','attributedUnitsOrdered1d','attributedUnitsOrdered7d',
        'attributedUnitsOrdered14d','attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','avgImpressionsFrequency',
        'cumulativeReach','currency','vctr','vtr','viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d',
        'viewAttributedSales14d','viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d',
        'viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d','videoCompleteViews','videoFirstQuartileViews',
        'videoMidpointViews','videoThirdQuartileViews','videoUnmutes',
    ]

    def reportV2_productAds_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_productAds_t2_ETL", '\n', "table_name:",
              "SD_adsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_adsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'productAds'

        metric = self.productAds_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020")
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])

        conn.insert_df("AmazonReport.SD_adsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_adsV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_productAds_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_productAds_t3_ETL", '\n', "table_name:",
              "SD_adsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_adsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'productAds'

        metric = self.productAds_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030")
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SD_adsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_adsV2")

        df_rel =  df_report[metric]
        return df_rel

    targets_metrics = [
        'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions','clicks','cost',
        'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU','attributedConversions7d',
        'attributedConversions7dSameSKU','attributedConversions14d','attributedConversions14dSameSKU','attributedConversions30d',
        'attributedConversions30dSameSKU','attributedDetailPageView14d','attributedOrdersNewToBrand14d',
        'attributedSales1d','attributedSales1dSameSKU','attributedSales7d','attributedSales7dSameSKU',
        'attributedSales14d','attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU',
        'attributedSalesNewToBrand14d','attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d',
        'attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','currency','targetingExpression','targetingType',
        'vctr','vtr','viewImpressions','viewAttributedConversions14d','viewAttributedDetailPageView14d','viewAttributedSales14d',
        'viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d',
        'viewAttributedUnitsOrderedNewToBrand14d','viewAttributedBrandedSearches14d','videoCompleteViews',
        'videoFirstQuartileViews','videoMidpointViews','videoThirdQuartileViews','videoUnmutes',
    ]

    def reportV2_targets_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_targets_t2_ETL", '\n', "table_name:",
              "SD_targetsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_targetsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'targets'

        metric = self.targets_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020")
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])
        # print(df_report[metric].info())
        conn.insert_df("AmazonReport.SD_targetsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_targetsV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_targets_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_targets_t3_ETL", '\n', "table_name:",
              "SD_targetsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_targetsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'targets'

        metric = self.targets_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030")
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])
        # print(df_report.info())

        conn.insert_df("AmazonReport.SD_targetsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_targetsV2")

        df_rel =  df_report[metric]
        return df_rel

    asins_metrics = [
        'campaignId','campaignName','adGroupId','adGroupName','asin','sku','attributedConversions1dOtherSKU',
        'attributedConversions7dOtherSKU','attributedConversions14dOtherSKU','attributedConversions30dOtherSKU',
        'attributedSales1dOtherSKU','attributedSales7dOtherSKU','attributedSales14dOtherSKU','attributedSales30dOtherSKU',
        'attributedUnitsOrdered1dOtherSKU','attributedUnitsOrdered7dOtherSKU','attributedUnitsOrdered14dOtherSKU',
        'attributedUnitsOrdered30dOtherSKU','currency','otherAsin','viewAttributedUnitsOrdered1dOtherSKU',
        'viewAttributedUnitsOrdered7dOtherSKU','viewAttributedUnitsOrdered14dOtherSKU','viewAttributedUnitsOrdered30dOtherSKU',
        'viewAttributedSales1dOtherSKU','viewAttributedSales7dOtherSKU','viewAttributedSales14dOtherSKU',
        'viewAttributedSales30dOtherSKU','viewAttributedConversions1dOtherSKU','viewAttributedConversions7dOtherSKU',
        'viewAttributedConversions14dOtherSKU','viewAttributedConversions30dOtherSKU',
    ]

    def reportV2_asins_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_asins_t2_ETL", '\n', "table_name:",
              "SD_asinsV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_asinsV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'asins'

        metric = self.asins_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020")
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])
        # print(df_report.info())
        conn.insert_df("AmazonReport.SD_asinsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_asinsV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_asins_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        params['record_type'] = 'asins'

        metric = self.asins_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030")
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['tactic'])

        conn.insert_df("AmazonReport.SD_asinsV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_asinsV2")

        df_rel =  df_report[metric]
        return df_rel

    campaigns_MT_metrics = [
        'campaignId','campaignName','impressions','clicks','cost','attributedBrandedSearches14d',
        'attributedConversions1d','attributedConversions1dSameSKU', 'attributedConversions7d',
        'attributedConversions7dSameSKU', 'attributedConversions14d','attributedConversions14dSameSKU',
        'attributedConversions30d','attributedConversions30dSameSKU','attributedDetailPageView14d',
        'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU', 'attributedSales7d',
        'attributedSales7dSameSKU', 'attributedSales14d','attributedSales14dSameSKU','attributedSales30d',
        'attributedSales30dSameSKU','attributedSalesNewToBrand14d',
        'attributedUnitsOrdered1d', 'attributedUnitsOrdered7d', 'attributedUnitsOrdered14d',
        'attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','campaignBudget',
        'campaignStatus','costType','currency', 'viewImpressions','viewAttributedBrandedSearches14d',
        'viewAttributedConversions14d','viewAttributedDetailPageView14d','viewAttributedSales14d',
        'viewAttributedUnitsOrdered14d','viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d',
        'viewAttributedUnitsOrderedNewToBrand14d',

    ]  # 'matchedTarget'

    def reportV2_campaign_matchedTarget_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_campaign_matchedTarget_t2_ETL", '\n', "table_name:",
              "SD_campaignsMatchedTargetV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_campaignsMatchedTargetV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'

        params['record_type'] = 'campaigns'

        metric = self.campaigns_MT_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020", segment='matchedTarget')
        # print(list_report)
        df_report = pd.json_normalize(list_report)

        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['matchedTarget', 'tactic'])

        conn.insert_df("AmazonReport.SD_campaignsMatchedTargetV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_campaignsMatchedTargetV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_campaign_matchedTarget_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()

        params = self.config_params(params)
        print("func_name:", "reportV2_campaign_matchedTarget_t3_ETL", '\n', "table_name:",
              "SD_campaignsMatchedTargetV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_campaignsMatchedTargetV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'campaigns'

        metric = self.campaigns_MT_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030", segment='matchedTarget')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['matchedTarget', 'tactic'])

        conn.insert_df("AmazonReport.SD_campaignsMatchedTargetV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_campaignsMatchedTargetV2")

        df_rel =  df_report[metric]
        return df_rel

    adGroups_MT_metrics = [
        'campaignId','campaignName','adGroupId','adGroupName','impressions','clicks','cost',
        'attributedBrandedSearches14d','attributedConversions1d', 'attributedConversions1dSameSKU',
        'attributedConversions7d', 'attributedConversions7dSameSKU','attributedConversions14d',
        'attributedConversions14dSameSKU','attributedConversions30d','attributedConversions30dSameSKU',
        'attributedDetailPageView14d', 'attributedOrdersNewToBrand14d','attributedSales1d','attributedSales1dSameSKU',
        'attributedSales7d','attributedSales7dSameSKU','attributedSales14d','attributedSales14dSameSKU','attributedSales30d',
        'attributedSales30dSameSKU','attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d',
        'attributedUnitsOrdered30d','attributedUnitsOrderedNewToBrand14d','bidOptimization',
        'currency','viewImpressions','viewAttributedBrandedSearches14d','viewAttributedConversions14d',
        'viewAttributedDetailPageView14d','viewAttributedSales14d','viewAttributedUnitsOrdered14d',
        'viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d',

    ]  # , 'matchedTarget'

    def reportV2_adGroups_matchedTarget_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adGroups_matchedTarget_t2_ETL", '\n', "table_name:",
              "SD_adGroupsMatchedTargetV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_adGroupsMatchedTargetV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'adGroups'

        metric = self.adGroups_MT_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020", segment='matchedTarget')
        # print(list_report)
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['matchedTarget', 'tactic'])

        conn.insert_df("AmazonReport.SD_adGroupsMatchedTargetV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_adGroupsMatchedTargetV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_adGroups_matchedTarget_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_adGroups_matchedTarget_t3_ETL", '\n', "table_name:",
              "SD_adGroupsMatchedTargetV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_adGroupsMatchedTargetV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'adGroups'

        metric = self.adGroups_MT_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030", segment='matchedTarget')

        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['matchedTarget', 'tactic'])
        conn.insert_df("AmazonReport.SD_adGroupsMatchedTargetV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_adGroupsMatchedTargetV2")

        df_rel =  df_report[metric]
        return df_rel

    targets_MT_metrics = [
        'campaignId','campaignName','adGroupId','adGroupName','targetId','targetingText','impressions','clicks','cost',
        'attributedBrandedSearches14d','attributedConversions1d','attributedConversions1dSameSKU',
        'attributedConversions7d','attributedConversions7dSameSKU','attributedConversions14d',
        'attributedConversions14dSameSKU','attributedConversions30d', 'attributedConversions30dSameSKU',
        'attributedDetailPageView14d','attributedOrdersNewToBrand14d','attributedSales1d',
        'attributedSales1dSameSKU','attributedSales7d','attributedSales7dSameSKU','attributedSales14d',
        'attributedSales14dSameSKU','attributedSales30d','attributedSales30dSameSKU','attributedSalesNewToBrand14d',
        'attributedUnitsOrdered1d','attributedUnitsOrdered7d','attributedUnitsOrdered14d','attributedUnitsOrdered30d',
        'attributedUnitsOrderedNewToBrand14d','currency','targetingExpression','targetingType','viewAttributedBrandedSearches14d',
        'viewAttributedConversions14d','viewAttributedDetailPageView14d','viewAttributedSales14d','viewAttributedUnitsOrdered14d',
        'viewAttributedOrdersNewToBrand14d','viewAttributedSalesNewToBrand14d','viewAttributedUnitsOrderedNewToBrand14d',
    ]

    def reportV2_targets_matchedTarget_t2_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_targets_matchedTarget_t2_ETL", '\n', "table_name:",
              "SD_targetsMatchedTargetV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_targetsMatchedTargetV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'targets'

        metric = self.targets_MT_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00020", segment='matchedTarget')
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Contextual targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['matchedTarget', 'tactic'])

        conn.insert_df("AmazonReport.SD_targetsMatchedTargetV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_targetsMatchedTargetV2")

        df_rel =  df_report[metric]
        return df_rel

    def reportV2_targets_matchedTarget_t3_ETL(self, conn, params:dict=None):
        timeZone_,today = self.today_()
        params = self.config_params(params)
        print("func_name:", "reportV2_targets_matchedTarget_t3_ETL", '\n', "table_name:",
              "SD_targetsMatchedTargetV2")
        if len(conn.query_df(
                f"select * from AmazonReport.SD_targetsMatchedTargetV2 where date='{params['date']}'")) > 0:
            logging.info("数据已存在...")
            time.sleep(8.5)
            return 'Pass'
        params['record_type'] = 'targets'

        metric = self.targets_MT_metrics
        list_report = self.get_v2_report(record_type=params['record_type'], report_date=params["date"],
                                         metrics=metric, tactic="T00030", segment='matchedTarget')
        df_report = pd.json_normalize(list_report)
        date = datetime.strptime(params['date'], '%Y%m%d')
        df_report['date'] = date
        df_report['tactic'] = 'Audiences targeting'  # {"T00020":"Contextual targeting","T00030":"Audiences targeting"}
        df_report = self.type_trans(df_report, metric, timeZone_, extra_columns=['matchedTarget', 'tactic'])

        conn.insert_df("AmazonReport.SD_targetsMatchedTargetV2", df_report[metric])
        time.sleep(1.05)
        print("插入完成SD_targetsMatchedTargetV2")

        df_rel =  df_report[metric]
        return df_rel


if __name__ == '__main__':
    AWS_CREDENTIALS = {
        'lwa_client_id': 'amzn1.application-oa2-client.ebd701cd07854fb38c37ee49ec4ba109',
        'refresh_token': "Atzr|IwEBIL4ur8kbcwRyxVu_srprAAoTYzujnBvA6jU-0SMxkRgOhGjYJSUNGKvw24EQwJa1jG5RM76mQD2P22AKSq8qSD94LddoXGdKDO74eQVYl0RhuqOMFqdrEZpp1p4bIR6_N8VeSJDHr7UCuo8FiabkSHrkq7tsNvRP-yI-bnpQv4EayPBh7YwHVX3hYdRbhxaBvgJENgCuiEPb35Q2-Z6w6ujjiKUAK2VSbCFpENlEfcHNsjDeY7RCvFlwlCoHj1IeiNIaFTE9yXFu3aEWlExe3LzHv6PZyunEi88QJSXKSh56Um0e0eEg05rMv-VBM83cAqc5POmZnTP1vUdZO8fQv3NFLZ-xU6e1WQVxVPi5Cyqk4jYhGf1Y9t98N654y0tVvw74qNIsTrB-8bGS0Uhfe24oBEWmzObvBY3zhtT1d42myGUJv4pMTU6yPoS83zhPKm3LbUDEpBA1hvvc_09jHk7vUEAuFB-UAZzlht2C1yklzQ",
        'lwa_client_secret': 'cbf0514186db4df91e04a8905f0a91b605eae4201254ced879d8bb90df4b474d',
        'profile_id': "3006125408623189"
    }
    conn = Common_ETLMethod(**AWS_CREDENTIALS).clickhouse_connect()

    sb_ = SB_ETL(**AWS_CREDENTIALS)
    conn = sb_.clickhouse_connect()
    # print(sb_.reportV2_adGroupsRecord_ETL(conn))
    # list_date = ['2023-11-20']
    list_date = [f'2023-11-{"0" + str(i) if len(str(i)) == 1 else i}' for i in range(28, 30)]
    for date_ in list_date:
        print(date_)
        print(date_.replace("-",""))
        sb_ = SB_ETL(**AWS_CREDENTIALS)
        rel = sb_.reportV3_sbAds_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        # print(rel)
        # print(rel.info())
        # print(rel.columns)