1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ
1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ
ɹαϯϓϧSQL WITH tables AS ( SELECT table_id FROM `{project_id}.{dataset_name}`.__TABLES__ WHERE table_id NOT LIKE ‘LOAD_TEMP_%' AND table_id NOT LIKE ‘TMP_%' ), log AS ( SELECT REGEXP_REPLACE(data.resource, ‘projects/{project_id}/datasets/{dataset_name}/tables/‘, '') AS table, protopayload_auditlog.authenticationInfo.principalEmail AS user, DATE(timestamp) AS day FROM `{project_id}.{source__cloudaudit__bigquery}.cloudaudit_googleapis_com_data_access_*`, UNNEST(protopayload_auditlog.authorizationInfo) AS data WHERE data.permission = 'bigquery.tables.getData' ), calc AS ( SELECT table, day, COUNT(*) AS PV, COUNT(DISTINCT user) AS UU FROM log WHERE table NOT LIKE ‘LOAD_TEMP_%' AND table != ‘__TABLES__' AND table NOT LIKE 'TMP_%' GROUP BY 1, 2 ) SELECT tables.table_id, calc.PV, calc.UU, REGEXP_REPLACE(CAST(calc.day AS STRING), '-', '') AS day FROM tables LEFT JOIN calc ON tables.table_id = calc.table
1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ
MySQL Read ReplicaͱBigQueryͷ݅Λൺֱ ※୲Ҋ݅ͩͱෛՙܰݮ؍ͰʮϝλςʔϒϧΛࢀরͯ͠99.5%ҰகΛࢦ͢ʯͳͲͷ Τϯϋϯε։ൃΛ͍ͯ͠Δ͕ϕʔεͱͳΔίϯηϓτ্هαϯϓϧίʔυ
ɹ݅Ұகࢹ
# BQ query = f""" SELECT COUNT(*) AS row_count FROM `xxxxx.xxxxx.xxxxx` WHERE CAST(created AS DATE) = DATE(‘2019-01-01') """ result_bq = pd.read_gbq(query, project_id=‘xxxxx', dialect='standard') # MySQL con = mysql.connector.connect(host = ‘x.x.x.x’, port = xxxxx, user = ‘xxxxx’, password = 'xxxxx', database = 'xxxxx') read_sql = f""" SELECT COUNT(*) AS row_count FROM `xxxxx` WHERE created BETWEEN '2019-01-01 00:00:00' AND '2019-01-01 23:59:59' """ result_mysql = psql.read_sql(read_sql, con) result_bq.equals(result_mysql)
1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ
1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ
1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ
1. ͡Ίʹ 2. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 3. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 4. ߏஙޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 5. Guide to Data Warehouse & Mart Designʢࣗฤʣ 6. Guide to Data Warehouse & Mart DesignʢҰൠฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ