際際滷

際際滷Share a Scribd company logo

BigQuery Data Lake & Airflow Contribution

Jason Kim
Jason Kim

contents about building data lake based on bigquery using airflow presented at google next extended seoul 2018

1 of 40
Download to read offline
EXTENDED SEOUL BigQuery Data Lake & Airflow Contribution Jay Kim kjh3477@gmail.com
EXTENDED SEOUL Agenda 觜貎朱Μ 螻蠍 觜貎朱Μ 螻殊 一危 危殊 蟲豢 螻覩朱 Airflow 螳 螳 Airflow 蠍磯 一危 危殊 螳讌 伎, ろ 谿語 襷覓企Μ
EXTENDED SEOUL 2016 12 襴讌2 覲朱 ろ 蠍一ヾ 豌 豌襴 譴 一危 RDB 蠍磯 襾語 蟆曙 讌螻螳 企れ 觜襯願 蟲蠍 觜貎朱Μ襯 覓語 願屋! 觜貎朱Μ 螻蠍
EXTENDED SEOUL 觜貎朱Μ 螻殊 蟲螳 螳 覲企 襷願係伎 覦企 碁 覦 磯 豌襴 覦 殊語狩 蠍一ヾ, IDC 企襷 一危 企 覲蟆 , 碁 覯襦 碁 覦 豺 覈詩 觜貎朱Μ 伎 觜貎朱Μ 豈 誤
EXTENDED SEOUL 結觚 IDC - 蟲蠍 GCS 覯 豢 , 蟲蠍 GCS - BigQuery 襦 結觚 IDC GCS 觜貎朱Μ 觜貎朱Μ 螻殊 : 蟲螳 螳 gzip
EXTENDED SEOUL 一危 瑚係襾狩 覲 覿/覲 豌襴, 豌襴 ろ伎 覲 (麹 ) 觜貎朱Μ 螻殊 : 襷願係伎
EXTENDED SEOUL 觜貎朱Μ 螳 ろ 貅伎 觜貎朱Μ 豈 ろ 貅伎 蠏 覦.. 蠍一ヾ 一危 觜貎朱Μ 誤 螻覩 (誤 蟯 蟾讌/) 觜貎朱Μ 一 蠍磯 覿 磯ジ 豌襴 覦 螻覩 觜貎朱Μ 螻殊 : 豺 覈詩 觜貎朱Μ 伎
EXTENDED SEOUL 蠍一ヾ 襦 蠍磯朱 ETL 蟲焔 朱 ル 蟲 蠍 企れ 一危 危殊 蟲豢 螻覩朱 : ASIS
EXTENDED SEOUL ろ / / 豌襴 蟆郁骸 磯ジ 覿蠍 豌襴襯 ろ襴渚碁ゼ 蟲覃? 覲旧″伎 訖 覈磯 螻, 蟯襴蠍磯 企れ れ譴襯 伎! ( Avoid reinventing the wheel ! ) 一危 危殊 蟲豢 螻覩朱 : TOBE
EXTENDED SEOUL 襯 豢 谿 豢螳 襦 谿螳 豢螳 壱蟆 蠍一ヾ 貊襯 磯 襷願係伎 危殊 螻 ル 覿 / 覲 豌襴 覈磯 覦 貉碁， 螳 ろ伎 覲襦 煙 危覃伎 螳 危殊語 谿朱 ろ 螳 豺 覈詩 觜貎朱Μ 伎れ 豌襴 襴 貊 磯 蠏碁 ろ 伎 伎 螳 伎 覿螳 蟆曙 ろ朱危一蟆 螳覲讌襷 レ Airflow
EXTENDED SEOUL Airflow 螳 螳 : 貉 Airflow : 襦 (殊 讌 螻殊/襴) 蟯襴 螳 伎 襦蠏碁覦 牛 一危 危殊語 蟲 / れ譴襷 / 覈磯
EXTENDED SEOUL Airflow 螳 螳 : 蟲 覦 襴
EXTENDED SEOUL Airflow 螳 螳 : れ譴
EXTENDED SEOUL Airflow 螳 螳 : れ譴
EXTENDED SEOUL Airflow 螳 螳 : 讌
EXTENDED SEOUL Airflow 螳 螳 : 讌 ろ
EXTENDED SEOUL Airflow 螳 螳 : 伎 覿
EXTENDED SEOUL 蠍一ヾ 襦朱 襷 ろ襴渚碁れ 螳覦 れ譴 磯螻 苦 襦朱 貉るЖ Airflow DAG 蟲 DAG 朱 貉るЖ 螳 譟伎 覦 轟 貉るЖ 蟯襴 Airflow 蠍磯 一危 危殊 : 襦 蟇
EXTENDED SEOUL 螻手碓 豌襴襯 蠍一ヾ 覈襦 朱誤磯 覲蟆渚覃 螻 苦 貉るЖ datetime 朱誤 譯殊 蠍磯 豢螳 れ譴 execution_date 螳 蠍磯朱 螻手碓 れ譴 Airflow 蠍磯 一危 危殊 : ろ
EXTENDED SEOUL 襷 豌襴 譴 ろ 覦 所 誤螻, 襦 螻 苦 return code 磯 蟇磯 轟 ろ襷 ろ 覃讌 Airflow 蠍磯 一危 危殊 : 覿蠍 豌襴 { ... branching: { cmd: "blah", upstream: [run_this_first], branch: { _0: branch_a, _1: branch_false} }, branch_a: {cmd: "blah", upstream: [branching] }, branch_false: {cmd: "blah", upstream: [branching] }, ... }
EXTENDED SEOUL 蠍一ヾ 襦語れ れろ碁伎 碁Μ蟇/殊 蠍磯レ 伎 譬襯 れ譴 磯 Airflow 蠍磯 一危 危殊 : れろ碁伎 sensor trigger Other Schedules
EXTENDED SEOUL 貊襯 覦覲旧朱 燕覃 螳 る 蟇碁Μ螻 れ 螳レ焔 襷 豕 れ襷 燕螻, 襾語 朱 燕伎朱 覓伎瑚螳 ! Airflow 蠍磯 一危 危殊 : 伎
EXTENDED SEOUL Airflow 蠍磯 一危 危殊 : Task Builder 結觚 一危一誤狩伎 襯 企 襦 json (豌 襷) 朱 れ譴 曙伎 Airflow れ譴
EXTENDED SEOUL 螳 豺 覈詩 覓語れ 覦 朱襦 蠏 蟲譟磯ゼ 危危 伎 螳讌 伎, ろ 谿語
EXTENDED SEOUL 螳讌 伎, ろ 谿語 覯蠏 Airflow 企 覯蠏碁れ 覿 **Appendix. Airflow Issues 焔 伎 Airflow れ譴襷 觀 豢 螻 讌 伎 伎 轟 譟郁唄 task instance 襯 貎朱Μ slow query 覦 覿覿 螳 伎 Airflow れ 讌蟯伎 蟇磯 伎 觜 覿覿る clear -f ろ upstream_failed 豌襴讌 伎 伎
EXTENDED SEOUL 貊 伎 譴 朱 contribution 螳讌 伎, ろ 谿語
EXTENDED SEOUL 觜貎朱Μ 襷願係伎 覦 觜貎朱Μ ろ朱伎 磯 Airflow 蟆企慨 Airflow 覯蠏 螻 るジ 豈 襷 Airflow るゼ 覿企慨覃 伎 ! 襷覓企Μ
EXTENDED SEOUL Q & A
EXTENDED SEOUL 觜貎朱Μ ろ朱伎 讌磯 貅伎 job status 襯 启覃, pending running 朱 願 done 讌 ろ蟆 豌襴 蟆 觜貎朱Μ 豈 磯ジ れ ろ 貅伎る https://cloud.google.com/bigquery/quotas https://cloud.google.com/bigquery/troubleshooting-errors slot limitation concurrent update limitation Exceeded rate limits: too many table update operations for this table. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors Appendix. Bigquery Issues
EXTENDED SEOUL Update state deadlock issue : 覃讌襯 覦 れ譴 れ / ろ ろ Deadlock found when trying to get lock; try restarting transaction 1. change_state_for_tis_without_dagrun 螻殊 覦覃 れ譴 れ 2. _check_and_change_state_before_execution 螻殊 覦覃 ろ ろ Limbo task issue : 轟 ろ螳 ろ 危 downstream 碁れ ろ讌 螻 れ譴 譬襭 Leaf-less backfill not working : 襭 れ譴 譴螳 碁襷 clear 企 碁螳 ろ讌 螻 譬襭 Stuck in queued state issue : ろ螳 queued running 朱 讌 Appendix. Airflow Issues
EXTENDED SEOUL Update state deadlock issue 覦 : れ譴, 貉, 襷る伎 譟一 螳 race condition task instance 覲蟆 螳 覦 蟲譟一 覓語 (Appendix. Airflow Scheduler, Appendix. Airflow Worker 伎 覲企殊 蠏碁朱 谿語＾) 1.8 覯蠏碁 碁 1.10 伎 (貉るる 伎 蟲譟一 覓語襯 螳螻 螻 ) 壱 覦覯 覓語 貎朱Μ 貊 襯 catch 襦る葦 襦 Appendix. Airflow Issues
EXTENDED SEOUL Limbo task issue 覦 : cut vertex 碁螳 ろ螻, 企 碁螳 all_success 碁Μ蟇 襭一 螳讌 child 襷 螳讌 蟆曙 れ譴螳 企 れ譴 譬襭 (Appendix. Airflow Cut Vertex 伎 谿語＾) 1.8 覯蠏碁 碁 1.10 伎 壱 覦覯 cut vertex dependency螳 leaf sensor 碁襯 豢螳 (豕譬 碁螳 襭 蟾讌 れ譴 譬襭襯 襷) Appendix. Airflow Issues
EXTENDED SEOUL Limbo task issue 覦 : cut vertex 碁螳 ろ螻, 企 碁螳 all_success 碁Μ蟇 襭一 螳讌 child 襷 螳讌 蟆曙 れ譴螳 企 れ譴 譬襭 (Appendix. Cut Vertex 伎 谿語＾) 1.8 覯蠏碁 碁 1.10 伎 壱 覦覯 cut vertex dependency螳 leaf sensor 碁襯 豢螳 (豕譬 碁螳 襭 蟾讌 れ譴 譬襭襯 襷) Appendix. Airflow Issues
EXTENDED SEOUL Leaf-less backfill not working 覦 : れ譴螳 runnable leaf 碁螳 れ譴 譬襭 1.8 覯蠏碁 碁 1.10 伎 壱 覦覯 豌襴 螻 譴螳 碁 leaf child 碁襯 豢螳 clear Appendix. Airflow Issues
EXTENDED SEOUL Stuck in queued state issue 覦 : れ譴螳 dag import 螻殊 timeout 朱 ろ 蟆曙 企ゼ 伎讌 螻 覦豺 1.8 覯蠏碁 碁 1.9 危襦 伎 壱 覦覯 れ 譴 dagbag_import_timeout 螳 豢覿 螳朱 讌 譴 Appendix. Airflow Issues
EXTENDED SEOUL Appendix. Airflow Sequence Diagram (Scheduler)
EXTENDED SEOUL Appendix. Airflow Sequence Diagram (Worker)
EXTENDED SEOUL Appendix. Airflow Scheduler
EXTENDED SEOUL Appendix. Airflow Worker
EXTENDED SEOUL Appendix. Airflow Cut Vertex 蟇 蠏碁螳 覲旧 貉危碁 覿襴 讌 碁 L2_CUT_VERTEX, L4_CUT_VERTEX Cut Vertex 企 讌 upstream 螻 downstream 狩 讌 1.8 覯 覯蠏 覦 譟郁唄 L2_CUT_VERTEX failed L3_A, L3_B, L3_C 覈 all_success 碁Μ蟇 襭磯 誤 れ譴 L2_CUT_VERTEX ろ 危 碁襯 螳 り れ譴 譬襭 (襷 L4_CUT_VERTEX 螳 all_done 襭一企朱 讌 )

More Related Content

BigQuery Data Lake & Airflow Contribution

  • 1. EXTENDED SEOUL BigQuery Data Lake & Airflow Contribution Jay Kim kjh3477@gmail.com
  • 2. EXTENDED SEOUL Agenda 觜貎朱Μ 螻蠍 觜貎朱Μ 螻殊 一危 危殊 蟲豢 螻覩朱 Airflow 螳 螳 Airflow 蠍磯 一危 危殊 螳讌 伎, ろ 谿語 襷覓企Μ
  • 3. EXTENDED SEOUL 2016 12 襴讌2 覲朱 ろ 蠍一ヾ 豌 豌襴 譴 一危 RDB 蠍磯 襾語 蟆曙 讌螻螳 企れ 觜襯願 蟲蠍 觜貎朱Μ襯 覓語 願屋! 觜貎朱Μ 螻蠍
  • 4. EXTENDED SEOUL 觜貎朱Μ 螻殊 蟲螳 螳 覲企 襷願係伎 覦企 碁 覦 磯 豌襴 覦 殊語狩 蠍一ヾ, IDC 企襷 一危 企 覲蟆 , 碁 覯襦 碁 覦 豺 覈詩 觜貎朱Μ 伎 觜貎朱Μ 豈 誤
  • 5. EXTENDED SEOUL 結觚 IDC - 蟲蠍 GCS 覯 豢 , 蟲蠍 GCS - BigQuery 襦 結觚 IDC GCS 觜貎朱Μ 觜貎朱Μ 螻殊 : 蟲螳 螳 gzip
  • 6. EXTENDED SEOUL 一危 瑚係襾狩 覲 覿/覲 豌襴, 豌襴 ろ伎 覲 (麹 ) 觜貎朱Μ 螻殊 : 襷願係伎
  • 7. EXTENDED SEOUL 觜貎朱Μ 螳 ろ 貅伎 觜貎朱Μ 豈 ろ 貅伎 蠏 覦.. 蠍一ヾ 一危 觜貎朱Μ 誤 螻覩 (誤 蟯 蟾讌/) 觜貎朱Μ 一 蠍磯 覿 磯ジ 豌襴 覦 螻覩 觜貎朱Μ 螻殊 : 豺 覈詩 觜貎朱Μ 伎
  • 8. EXTENDED SEOUL 蠍一ヾ 襦 蠍磯朱 ETL 蟲焔 朱 ル 蟲 蠍 企れ 一危 危殊 蟲豢 螻覩朱 : ASIS
  • 9. EXTENDED SEOUL ろ / / 豌襴 蟆郁骸 磯ジ 覿蠍 豌襴襯 ろ襴渚碁ゼ 蟲覃? 覲旧″伎 訖 覈磯 螻, 蟯襴蠍磯 企れ れ譴襯 伎! ( Avoid reinventing the wheel ! ) 一危 危殊 蟲豢 螻覩朱 : TOBE
  • 10. EXTENDED SEOUL 襯 豢 谿 豢螳 襦 谿螳 豢螳 壱蟆 蠍一ヾ 貊襯 磯 襷願係伎 危殊 螻 ル 覿 / 覲 豌襴 覈磯 覦 貉碁， 螳 ろ伎 覲襦 煙 危覃伎 螳 危殊語 谿朱 ろ 螳 豺 覈詩 觜貎朱Μ 伎れ 豌襴 襴 貊 磯 蠏碁 ろ 伎 伎 螳 伎 覿螳 蟆曙 ろ朱危一蟆 螳覲讌襷 レ Airflow
  • 11. EXTENDED SEOUL Airflow 螳 螳 : 貉 Airflow : 襦 (殊 讌 螻殊/襴) 蟯襴 螳 伎 襦蠏碁覦 牛 一危 危殊語 蟲 / れ譴襷 / 覈磯
  • 12. EXTENDED SEOUL Airflow 螳 螳 : 蟲 覦 襴
  • 16. EXTENDED SEOUL Airflow 螳 螳 : 讌 ろ
  • 17. EXTENDED SEOUL Airflow 螳 螳 : 伎 覿
  • 18. EXTENDED SEOUL 蠍一ヾ 襦朱 襷 ろ襴渚碁れ 螳覦 れ譴 磯螻 苦 襦朱 貉るЖ Airflow DAG 蟲 DAG 朱 貉るЖ 螳 譟伎 覦 轟 貉るЖ 蟯襴 Airflow 蠍磯 一危 危殊 : 襦 蟇
  • 19. EXTENDED SEOUL 螻手碓 豌襴襯 蠍一ヾ 覈襦 朱誤磯 覲蟆渚覃 螻 苦 貉るЖ datetime 朱誤 譯殊 蠍磯 豢螳 れ譴 execution_date 螳 蠍磯朱 螻手碓 れ譴 Airflow 蠍磯 一危 危殊 : ろ
  • 20. EXTENDED SEOUL 襷 豌襴 譴 ろ 覦 所 誤螻, 襦 螻 苦 return code 磯 蟇磯 轟 ろ襷 ろ 覃讌 Airflow 蠍磯 一危 危殊 : 覿蠍 豌襴 { ... branching: { cmd: "blah", upstream: [run_this_first], branch: { _0: branch_a, _1: branch_false} }, branch_a: {cmd: "blah", upstream: [branching] }, branch_false: {cmd: "blah", upstream: [branching] }, ... }
  • 21. EXTENDED SEOUL 蠍一ヾ 襦語れ れろ碁伎 碁Μ蟇/殊 蠍磯レ 伎 譬襯 れ譴 磯 Airflow 蠍磯 一危 危殊 : れろ碁伎 sensor trigger Other Schedules
  • 22. EXTENDED SEOUL 貊襯 覦覲旧朱 燕覃 螳 る 蟇碁Μ螻 れ 螳レ焔 襷 豕 れ襷 燕螻, 襾語 朱 燕伎朱 覓伎瑚螳 ! Airflow 蠍磯 一危 危殊 : 伎
  • 23. EXTENDED SEOUL Airflow 蠍磯 一危 危殊 : Task Builder 結觚 一危一誤狩伎 襯 企 襦 json (豌 襷) 朱 れ譴 曙伎 Airflow れ譴
  • 24. EXTENDED SEOUL 螳 豺 覈詩 覓語れ 覦 朱襦 蠏 蟲譟磯ゼ 危危 伎 螳讌 伎, ろ 谿語
  • 25. EXTENDED SEOUL 螳讌 伎, ろ 谿語 覯蠏 Airflow 企 覯蠏碁れ 覿 **Appendix. Airflow Issues 焔 伎 Airflow れ譴襷 觀 豢 螻 讌 伎 伎 轟 譟郁唄 task instance 襯 貎朱Μ slow query 覦 覿覿 螳 伎 Airflow れ 讌蟯伎 蟇磯 伎 觜 覿覿る clear -f ろ upstream_failed 豌襴讌 伎 伎
  • 26. EXTENDED SEOUL 貊 伎 譴 朱 contribution 螳讌 伎, ろ 谿語
  • 27. EXTENDED SEOUL 觜貎朱Μ 襷願係伎 覦 觜貎朱Μ ろ朱伎 磯 Airflow 蟆企慨 Airflow 覯蠏 螻 るジ 豈 襷 Airflow るゼ 覿企慨覃 伎 ! 襷覓企Μ
  • 29. EXTENDED SEOUL 觜貎朱Μ ろ朱伎 讌磯 貅伎 job status 襯 启覃, pending running 朱 願 done 讌 ろ蟆 豌襴 蟆 觜貎朱Μ 豈 磯ジ れ ろ 貅伎る https://cloud.google.com/bigquery/quotas https://cloud.google.com/bigquery/troubleshooting-errors slot limitation concurrent update limitation Exceeded rate limits: too many table update operations for this table. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors Appendix. Bigquery Issues
  • 30. EXTENDED SEOUL Update state deadlock issue : 覃讌襯 覦 れ譴 れ / ろ ろ Deadlock found when trying to get lock; try restarting transaction 1. change_state_for_tis_without_dagrun 螻殊 覦覃 れ譴 れ 2. _check_and_change_state_before_execution 螻殊 覦覃 ろ ろ Limbo task issue : 轟 ろ螳 ろ 危 downstream 碁れ ろ讌 螻 れ譴 譬襭 Leaf-less backfill not working : 襭 れ譴 譴螳 碁襷 clear 企 碁螳 ろ讌 螻 譬襭 Stuck in queued state issue : ろ螳 queued running 朱 讌 Appendix. Airflow Issues
  • 31. EXTENDED SEOUL Update state deadlock issue 覦 : れ譴, 貉, 襷る伎 譟一 螳 race condition task instance 覲蟆 螳 覦 蟲譟一 覓語 (Appendix. Airflow Scheduler, Appendix. Airflow Worker 伎 覲企殊 蠏碁朱 谿語＾) 1.8 覯蠏碁 碁 1.10 伎 (貉るる 伎 蟲譟一 覓語襯 螳螻 螻 ) 壱 覦覯 覓語 貎朱Μ 貊 襯 catch 襦る葦 襦 Appendix. Airflow Issues
  • 32. EXTENDED SEOUL Limbo task issue 覦 : cut vertex 碁螳 ろ螻, 企 碁螳 all_success 碁Μ蟇 襭一 螳讌 child 襷 螳讌 蟆曙 れ譴螳 企 れ譴 譬襭 (Appendix. Airflow Cut Vertex 伎 谿語＾) 1.8 覯蠏碁 碁 1.10 伎 壱 覦覯 cut vertex dependency螳 leaf sensor 碁襯 豢螳 (豕譬 碁螳 襭 蟾讌 れ譴 譬襭襯 襷) Appendix. Airflow Issues
  • 33. EXTENDED SEOUL Limbo task issue 覦 : cut vertex 碁螳 ろ螻, 企 碁螳 all_success 碁Μ蟇 襭一 螳讌 child 襷 螳讌 蟆曙 れ譴螳 企 れ譴 譬襭 (Appendix. Cut Vertex 伎 谿語＾) 1.8 覯蠏碁 碁 1.10 伎 壱 覦覯 cut vertex dependency螳 leaf sensor 碁襯 豢螳 (豕譬 碁螳 襭 蟾讌 れ譴 譬襭襯 襷) Appendix. Airflow Issues
  • 34. EXTENDED SEOUL Leaf-less backfill not working 覦 : れ譴螳 runnable leaf 碁螳 れ譴 譬襭 1.8 覯蠏碁 碁 1.10 伎 壱 覦覯 豌襴 螻 譴螳 碁 leaf child 碁襯 豢螳 clear Appendix. Airflow Issues
  • 35. EXTENDED SEOUL Stuck in queued state issue 覦 : れ譴螳 dag import 螻殊 timeout 朱 ろ 蟆曙 企ゼ 伎讌 螻 覦豺 1.8 覯蠏碁 碁 1.9 危襦 伎 壱 覦覯 れ 譴 dagbag_import_timeout 螳 豢覿 螳朱 讌 譴 Appendix. Airflow Issues
  • 36. EXTENDED SEOUL Appendix. Airflow Sequence Diagram (Scheduler)
  • 37. EXTENDED SEOUL Appendix. Airflow Sequence Diagram (Worker)
  • 40. EXTENDED SEOUL Appendix. Airflow Cut Vertex 蟇 蠏碁螳 覲旧 貉危碁 覿襴 讌 碁 L2_CUT_VERTEX, L4_CUT_VERTEX Cut Vertex 企 讌 upstream 螻 downstream 狩 讌 1.8 覯 覯蠏 覦 譟郁唄 L2_CUT_VERTEX failed L3_A, L3_B, L3_C 覈 all_success 碁Μ蟇 襭磯 誤 れ譴 L2_CUT_VERTEX ろ 危 碁襯 螳 り れ譴 譬襭 (襷 L4_CUT_VERTEX 螳 all_done 襭一企朱 讌 )