GAEを用いたBQ Load戦略/gae_bq_load_strategy

Transcript

1. copyright Fringe81 Co.,Ltd. GAE
   BQ Load Fringe81    

     1

2. copyright Fringe81 Co.,Ltd. 
   l  (Hiroki Fujino) l @Fringe81

   l   l
    2

3. copyright Fringe81 Co.,Ltd.  / 
    Ø  Ø 

   l GAE"   l BQ Load l 
    #! l GAETaskQueue 3

4. copyright Fringe81 Co.,Ltd. 4  
   

5. copyright Fringe81 Co.,Ltd. Columva l 
    (2017/11~) l SaaS l

   ETL 5

6. copyright Fringe81 Co.,Ltd. Columva 6 l  (2017/11~) l SaaS

   l ETL Columva GAEBQ
    GCP 

7. copyright Fringe81 Co.,Ltd.
      App Engine Big Query

   App Engine
    7 /

8. copyright Fringe81 Co.,Ltd.
    lStandard Environment lPaaS l 
   

   lJava8/Scala l 8   App Engine Big Query App Engine /

9. copyright Fringe81 Co.,Ltd.
    l l&"# l&!% lAPI  l
   

   $  9  App Engine Big Query App Engine /

10. copyright Fringe81 Co.,Ltd. BigQuery
     Big Query In Out 10

11. copyright Fringe81 Co.,Ltd. BigQuery
     lSQL l
     Big Query In Out

    11

12. copyright Fringe81 Co.,Ltd. BigQuery
     l 
      Big Query In

    Out 12

13. copyright Fringe81 Co.,Ltd. 
     13

14. copyright Fringe81 Co.,Ltd. BQ
          14

15. copyright Fringe81 Co.,Ltd.
        15

16. copyright Fringe81 Co.,Ltd. BigQuery  
     16 
    

17. copyright Fringe81 Co.,Ltd. 
     17    BigQuery

      
    

18. copyright Fringe81 Co.,Ltd. 18 %#"  TaskQueue   GAE

    SE
    & !$60   GAE
    

19. copyright Fringe81 Co.,Ltd. App Engine Push Queue Push Queue App

    Engine App Engine 1 3 2 2 Enqueue    Push Queue
      GAE
     2 Push 3 Enqueue 3 Push 19

20. copyright Fringe81 Co.,Ltd. App Engine Push Queue "! Enqueue GAE
    

    "! #$ Push Queue     
     20

21. copyright Fringe81 Co.,Ltd. App Engine Push Queue  *)'#Enqueue GAE
    

    
      *)'#&,.! Push Queue  #  "!    )(.-$%+  21

22. copyright Fringe81 Co.,Ltd.    BQ   

    
    22 BigQuery  
    

23. copyright Fringe81 Co.,Ltd.   !"  #  
    

     23

24. copyright Fringe81 Co.,Ltd.  
    l Ver.1 (Streaming Insert)

    l Ver.2 (BQ Load) l Ver.3 (Pull Queue
    BQ Load) 24

25. copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ

    Load) l Ver.3 (Pull Queue
    BQ Load)  
    25

26. copyright Fringe81 Co.,Ltd. Streaming Insert BQ    
    

    Streaming Insert 
     Ver.1(Streaming Insert) 26 BigQuery 

27. copyright Fringe81 Co.,Ltd. Streaming Insert l 
     BigQuery 

      l $0.01/200MB BigQuery Streaming Insert Table User id INTEGER name STRING 27 
     User(id: Long, name: String)

28. copyright Fringe81 Co.,Ltd.
     28

29. copyright Fringe81 Co.,Ltd. 
     BQ     

    
    BQ   29

30. copyright Fringe81 Co.,Ltd. 
     BQ!"  !"   

    !"  !"  
    BQ!"    !"  ## 30

31. copyright Fringe81 Co.,Ltd. Streaming Insert 
     SELECT DELETE 

     BigQuery SELECT DELETE Streaming Insert 31

32. copyright Fringe81 Co.,Ltd. Streaming Insert
     streaming insert   BigTable

    
      select BigTable  [https://cloud.google.com/blog/big-data/2017/06/life-of-a-bigquery-streaming-insert] 32

33. copyright Fringe81 Co.,Ltd. Streaming Insert
     streaming insert  # BigTable
    !"

    select BigTable!"  33 deleteupdate  90  [https://cloud.google.com/blog/big-data/2017/06/life-of-a-bigquery-streaming-insert]

34. copyright Fringe81 Co.,Ltd. Streaming Insert 34 l%*$
    (1("!#& 1MB) l()$ '

      %*$
     %*$
     

35. copyright Fringe81 Co.,Ltd. Streaming Insert
     
     90  

    35

36. copyright Fringe81 Co.,Ltd. Streaming Insert
     
    90   BQ
     

        36

37. copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ

    Load) l Ver.3 (Pull Queue
    BQ Load)  
    37

38. copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! !  !

    Cloud Storage 
     Ver.2 (BQ Load)  
     BQ Load BigQuery 38  

39. copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! s 
    

    Ver.2 (BQ Load) 39 !  ! Cloud Storage  
     BQ Load BigQuery  

40. copyright Fringe81 Co.,Ltd. BQ Load 40 lGoogle   

    lSDK
      (Java, Python, Go…)

41. copyright Fringe81 Co.,Ltd. BQ Load CSV JSON BigQuery Load 
    

    1 BQ     Load 41 Table User id INTEGER name STRING id, name 1, “hoge” 2, “fuga” {“id”: 1, “name”: “hoge”} {“id”: 2, “name”: “fuga”}

42. copyright Fringe81 Co.,Ltd. BQ Load GCP
       Cloud Storage

    Cloud Dataflow Load Load BigQuery 42

43. copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! 
     Ver.2

    (BQ Load) 43 !  ! Cloud Storage  
     BQ Load BigQuery  

44. copyright Fringe81 Co.,Ltd. Google Cloud Storage !, )$(./# GCS <=>

    BQ'/%  "$&+'/%*- 
    GCSJSON*- 44

45. copyright Fringe81 Co.,Ltd. GCS => BQ Cloud Storage BQ Load

    hoge1.json hoge_table hoge2.json hoge3.json hoge_table hoge_table BigQuery 45

46. copyright Fringe81 Co.,Ltd. BQ Load
     l Streaming Insert% BQ Load%

    l -.,+* & ) l $-.,+
    *)! ) l ACID +  u *-.,% #)'#")(Atomicity) u -.,%)("(Consistency) 46

47. copyright Fringe81 Co.,Ltd.  
      47

48. copyright Fringe81 Co.,Ltd. BQ Load
     error=BigQueryError{reason=rateLimitExceeded, location=table.write, message=Exceeded rate limits:

    too many table update operations for this table… 48

49. copyright Fringe81 Co.,Ltd. rateLimitExceeded
     49

50. copyright Fringe81 Co.,Ltd. GCS BQ Load BQ % 
    

    Ver.2 (BQ Load) Load  50 % "$%! Cloud Storage 
     BQ Load BigQuery  #

51. copyright Fringe81 Co.,Ltd. 
      BQ Load   BQ

    Load  
     51

52. copyright Fringe81 Co.,Ltd. BQ Load
     l 15'"% 10 $" l

    * '"% 1,000  $"( ) l * #& (!'$"
    #& ) 10,000  $"( )  52

53. copyright Fringe81 Co.,Ltd. BQ Load
     l 15)0836!10"%%/53$ l ; )0836!

    1,000 "%%/53$9 +&) l ; )47/,-19.28/53+
    "47/,-1:! 10,000 "%%/53$9 +&:  53 
    "#" +(Load*'! 

54. copyright Fringe81 Co.,Ltd. BQ Load
     l 15$-614 10 ""+31! l

    9 $-614 1,000  ""+31!7 '#) l 9 $25+()/7*06+31' 
    25+()/8 10,000  ""+31!7 '#8  54 .6,& %

55. copyright Fringe81 Co.,Ltd. BQ Load
     l 15"*3.1 10 (0. l

    6 "*3.1 1,000  (0.4 $ ) l 6 "/2(%&,4'-3(0.$
    /2(%&,5 10,000  (0.4 $ 5  55 +3)$! BQLoad#7

56. copyright Fringe81 Co.,Ltd. BQ Load  
     Cloud Storage

    gs://bucket/dir/* [gs://bucket/dir/test1.txt, gs://bucket/dir/test2.txt] [gs://bucket/dir1/*, gs://bucket/dir2/*]    
        
     Load Load Load BigQuery 56

57. copyright Fringe81 Co.,Ltd. BQ Load  
     Cloud Storage

    gs://bucket/dir/* [gs://bucket/dir/test1.txt, gs://bucket/dir/test2.txt] [gs://bucket/dir1/*, gs://bucket/dir2/*]    
    1   Load Load Load BigQuery 57    

58. copyright Fringe81 Co.,Ltd. BQ Load  
    
    Load 

     l  10,000URI l  10,000,000 l  15TB  58

59. copyright Fringe81 Co.,Ltd.
     *1&%BQ Load1"0#!0 Load *62391!0 .&
    ' Load%0

    59 l <!&/5:79$() 1,000 *,,487+'; 1 -) 487* l 487' 10,000URI+' l 487' 10,000,0006239+' l 487' 15TB+' 1487&/*

60. copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ

    Load) l Ver.3 (Pull Queue
    BQ Load)  
    60

61. copyright Fringe81 Co.,Ltd. GCS%),-(
    $#!  Pull Queue BQLoad  BQ

    Load Pull Queue   +- %),-(
     '-$  
     Ver.3 (Pull Queue × BQ Load) Cloud Storage BigQuery 61 Pull Queue %),-(
     '-$  +- '-$ %),-( Cloud Storage  "#&*

62. copyright Fringe81 Co.,Ltd. GCS%),-(
    $#!  Pull Queue BQLoad  BQ

    Load 
     Ver.3 (Pull Queue × BQ Load) Cloud Storage BigQuery 62 Pull Queue %),-(
     '-$  +- '-$ %),-( Cloud Storage  "#&* Pull Queue   +- %),-(
     '-$ 

63. copyright Fringe81 Co.,Ltd. Pull Queue &%$#   Queue 

    ! "
      Pull Queue &%$3 &%$1 &%$2 Enqueue &%$#
     63

64. copyright Fringe81 Co.,Ltd. Pull Queue
      
      Enqueue Pull

    Queue id: 3 id: 2 id: 1  3(payload: 3)  1(payload: 1)  2(payload: 2) Enqueue 64

65. copyright Fringe81 Co.,Ltd. Pull Queue
     lease payload   

    
      Pull Queue Lease 3(payload: 3) 1(payload: 1) 2(payload: 2) id: 3 id: 2 id: 1 65

66. copyright Fringe81 Co.,Ltd. Pull Queue  
     Load *&'-%$ "
    Load$

    66 l 0#).+- 1,000   (,+/ %!) (,+  l (,+10,000URI l (,+10,000,000*&'- l (,+15TB 1(,+ #

67. copyright Fringe81 Co.,Ltd. Pull Queue  
     Load (4017/. ,#%Load".

    67 l :#-3857 &' 1,000 (**265)%9 /+) 265( l 265% 10,000URI)% l 265% 10,000,0004017)% l 265% 15TB)% 1265#-( Pull Queue'
    ' Load (4017 /!$.

68. copyright Fringe81 Co.,Ltd. GCS%),-(
    $#!  Pull Queue BQLoad Pull Queue

    %),-(
     '-$  +- 
     Ver.3 (Pull Queue × BQ Load) Cloud Storage  BQ Load BigQuery 68 '-$ %),-( Cloud Storage  "#&* Pull Queue   +- %),-(
     '-$ 

69. copyright Fringe81 Co.,Ltd. Pull Queue × BQ Load  BQ

    Load Cloud Storage Pull Queue Lease id: 3 id: 2 id: 1 Lease  
    BigQuery 69

70. copyright Fringe81 Co.,Ltd. 
    70 l  1 100
     5

     l    100

71. copyright Fringe81 Co.,Ltd. 
    71 l (+* 1 100
    !5 

    l -(+* #!)&', "  100)&', GAE% BQ Load  %$

72. copyright Fringe81 Co.,Ltd. 
     72  %  *0 0

    
     Streaming Insert  1MB/1, % ,-!) /0% 90  BQ Load  15TB/1+' 1000+'/1$0'- 10000+'/1(.% &#"

73. copyright Fringe81 Co.,Ltd. 
     l BQ Load *$%-# l GAE+',.-#
    "!

    BQ Load#! l GAE&()  73

74. copyright Fringe81 Co.,Ltd. 
     l
    GAE+TQ  , Dataflow  

      74

75. copyright Fringe81 Co.,Ltd. 
       75