DESAMIS Go Training Season 1 Day 11

デザミス社内 Go 研修 (第1期) の11日目の資料です。
今回は PostgreSQL, BigQuery そして DynamoDB にアクセスに必要なライブラリを紹介し、実際に PostgreSQL と DynamoDB のクエリを実行するデモを行います。

Transcript

1. (P
   ݚम
   ୈظ
   %":
    :VUBLB
   ,BUP 
 3%
   %&4".*4 %&4".*4
   ࣾ಺ݚम
2. ݚमܭը ճ ೔࣍
   ༧ఆ λΠτϧ ୈճ  Ր
   
   
    (P
   ͷجຊ ୈճ  Ր
   
   
    ؀ڥߏஙͱ
   (JU
   (JU)VC
   ͷجຊ ୈճ  Ր
   
   
    جຊߏจ ୈճ  Ր
   
   
    ߏ଄ମɺεϥΠεɺϚοϓ ୈճ  Ր
   
   
    ඪ४ϥΠϒϥϦ͸΍Ί͙Γ ୈճ  Ր
   
   
    ϞδϡʔϧγεςϜͱ֎෦ϥΠϒϥϦ ୈճ  Ր
   
   
    )FSPLV(P
   ೖ໳ ୈճ  Ր
   
   
    "84
   -BNCEB(P
   ೖ໳ ୈճ  Ր
   
   
    ฒߦॲཧϓϩάϥϛϯά ୈճ  Ր
   
   
    ୯ମςετٕ๏ ୈճ  Ր
   
   
    σʔλϕʔεΞΫηε ୈճ  Ր
   
   
    'ZOF
   ʹΑΔ
   (6*
   ΞϓϦέʔγϣϯ։ൃ ୈճ  Ր
   
   
    ࣾ಺
   (P
   ϓϩδΣΫτͷίʔυղઆ 
3. ˞
   લճͷ
   13
   ͷ 
 ղઆλΠϜ 
4. ຊ೔ͷΰʔϧ w 🚩ΰʔϧ
   w 42-
   ͷ઀ଓɾσʔλऔಘํ๏Λशಘ͢Δ
   w /P42-
   ͷ઀ଓɾσʔλऔಘํ๏Λशಘ͢Δ
   w ❌
   ΰʔϧͰ͸ͳ͍
   w ςʔϒϧઃܭɺੑೳվળɺ03.
   ͷར༻ɺFUD 
5. EBUBCBTFTRM 
6. EBUBCBTFTRM  w 42-
   Λ࣮ߦ͢ΔͨΊͷڞ௨ΠϯλϑΣʔεΛఏڙ͢Δඪ ४ϥΠϒϥϦ
   w جຊతͳσʔλϕʔε઀ଓͷ؅ཧ΍
   42-
   ΠϯδΣΫγϣ ϯͷอޢͳͲΛఏڙ͢Δ
   w ࣮ࡍͷσʔλϕʔεʹΞΫηε͢Δʹ͸ɺσʔλϕʔε ͝ͱʹ༻ҙ͞ΕͨυϥΠόʔͷ
   ϒϥϯΫ
   Πϯϙʔτ͕ ඞཁ import ( "database/sql " _ "github.com/lib/pq " )
7. ୅දతͳυϥΠόʔ  %#.4 %SJWFS .Z42- HJUIVCDPNHPTRMESJWFSNZTRM 1PTUHSF42- HJUIVCDPNMJCQR 0SBDMF HJUIVCDPNNBUUOHPPDJ 42-
   4FSWFS HJUIVCDPNEFOJTFOLPNHPNTTRMEC 42-JUF HJUIVCDPNNBUUOHPTRMJUF 8JLJ
   👉
   IUUQTHJUIVCDPNHPMBOHHPXJLJ42-%SJWFST
8. ΍ͬͯΈΑ͏
     package mai n import ( // import লུ… _ "github.com/lib/pq " ) func main() { url := flag.String("url", "", "σʔλϕʔε઀ଓจࣈྻ") flag.Parse( ) db, err := sql.Open("postgres", *url ) if err != nil { log.Fatalf("઀ଓʹࣦഊ͠·ͨ͠: %v", err ) } defer db.Close()
 }  74
   $PEF
   Λىಈ͠ݚमϦϙδτϦΛ։͖·͢
    Ϣʔβʔ໊ϑΥϧμҎԼʹʮEBZQTRMʯϑΥϧμΛ࡞੒͠ɺͦͷதʹҎԼͷ಺ ༰ͰʮNBJOHPʯΛ࡞੒͠·͢ ౷߹λʔϛφϧͰʮgo mod tidyʯͱʮgo run . -url <ผ్ࢦࣔͨ͠ URL>ʯΛ࣮ߦ͠·͢
9. ΍ͬͯΈΑ͏
     rows, err := db.Query ( "SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = $1" , "public") if err != nil { log.Fatalf("ΫΤϦͷ࣮ߦʹࣦഊ͠·ͨ͠: %v", err ) } defer rows.Close( ) var count in t for rows.Next() { count+ + var tableSchema, tableName strin g if err := rows.Scan(&tableSchema, &tableName); err != nil { log.Fatalf("ߦͷऔಘʹࣦഊ͠·ͨ͠: %v", err ) } fmt.Printf("%s.%s\n", tableSchema, tableName ) } fmt.Printf("%d ݸͷςʔϒϧΛൃݟ͠·ͨ͠\n", count)  ʮNBJOHPʯͷ
   defer
   ͷԼʹɺҎԼͷίʔυΛ௥Ճ͠·͢
    ౷߹λʔϛφϧͰઌ΄Ͳͱಉ͡ίϚϯυΛ࣮ߦ͠·͢
10. ΍ͬͯΈΑ͏
      rows, err := db.Query ( "SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = $1" , "public") if err != nil { log.Fatalf("ΫΤϦͷ࣮ߦʹࣦഊ͠·ͨ͠: %v", err ) } defer rows.Close( ) var count in t for rows.Next() { count+ + var tableSchema, tableName strin g if err := rows.Scan(&tableSchema, &tableName); err != nil { log.Fatalf("ߦͷऔಘʹࣦഊ͠·ͨ͠: %v", err ) } fmt.Printf("%s.%s\n", tableSchema, tableName ) } fmt.Printf("%d ݸͷςʔϒϧΛൃݟ͠·ͨ͠\n", count)  ʮNBJOHPʯͷ
    defer
    ͷԼʹɺҎԼͷίʔυΛ௥Ճ͠·͢
     ౷߹λʔϛφϧͰઌ΄Ͳͱಉ͡ίϚϯυΛ࣮ߦ͠·͢ ύϥϝʔλʔϚʔΧʔ 
 %#
    υϥΠόʹΑͬͯ͸ʮ ʯ ඞͣ
    Close()
    ΛݺͿ͜ͱ
    ઈରʹʂ બ୒ྻͱ׬શʹҰகͤ͞Δ͜ͱ 
 ʮSELECT *ʯ͸ةݥ
11. ςʔϒϧͷ࡞੒  w ςʔϒϧͷ࡞੒ͳͲɺߦΛऔಘ͢ΔҎ֎ͷॲཧʹ͸
    DB
    ΦϒδΣΫτͷ
    Exec()
    ౳ͷϝιουΛ༻͍Δ
    w ฦͬͯ͘Δͷ͸
    Result
    ΠϯλϑΣʔεͰɺLastInsertId
    ͱ
    RowsAffected
    ͕ಘΒΕΔ
    w ͍Βͳ͚Ε͹ʮ@ʯͰແࢹͯ͠΋ྑ͍
    w υϥΠόʔʹΑͬͯ͸αϙʔτ͍ͯ͠ͳ͍ࣄ΋͋Δ͔΋ _, err = db.Exec("CREATE TABLE IF NOT EXISTS mikan_table (key text, value text, CONSTRAINT key PRIMARY KEY (key))" ) if err != nil { log.Fatalf("ςʔϒϧͷ࡞੒ʹࣦഊ͠·ͨ͠: %v", err ) }
12. τϥϯβΫγϣϯॲཧ   DB
    ΦϒδΣΫτͷ
    Begin()
    ϝιουͰ։࢝
     Tx
    ΦϒδΣΫτ͕ฦͬͯ͘ΔͷͰɺͦΕܦ༝ Ͱ
    Query()
    ΍
    Exec()
    ౳ΛݺͿ
     ్தͷૢ࡞͕ࣦഊͨ͠Β
    Rollback()
    ΛݺΜ ͰॲཧΛൈ͚Δ
     ࠷ޙʹ
    Commit()
    ΛݺͿ
13. ͘͝γϯϓϧͳྫ  tx, err := db.Begin( ) if err != nil { log.Fatalf("τϥϯβΫγϣϯͷ։࢝ʹࣦഊ͠·ͨ͠: %v", err ) } result, err := tx.Exec("INSERT INTO mikan_table (key, value) VALUES ($1, $2)", "ςετ", "ςετͰ͢" ) if err != nil { if err := tx.Rollback(); err != nil { log.Fatalf("σʔλͷ௥Ճʹࣦഊ͠ɺϩʔϧόοΫ΋ࣦഊ͠·ͨ͠: %v", err ) } log.Fatalf("σʔλͷ௥Ճʹࣦഊͨͨ͠ΊɺϩʔϧόοΫ͠·ͨ͠: %v", err ) } n, err := result.RowsAffected( ) if err != nil { if err := tx.Rollback(); err != nil { log.Fatalf("σʔλͷ௥Ճ݁Ռͷऔಘʹࣦഊ͠ɺϩʔϧόοΫ΋ࣦഊ͠·ͨ͠: %v", err ) } log.Fatalf("σʔλͷ௥Ճ݁Ռͷऔಘʹࣦഊͨͨ͠ΊɺϩʔϧόοΫ͠·ͨ͠: %v", err ) } fmt.Printf("Rows affected: %d\n", n ) if err := tx.Commit(); err != nil { log.Fatalf("τϥϯβΫγϣϯͷίϛοτʹࣦഊ͠·ͨ͠: %v", err ) } fmt.Println("τϥϯβΫγϣϯ׬ྃ") τϥϯβΫγϣϯͷ։࢝ ࣦഊͨ͠ΒϩʔϧόοΫ ࠷ޙʹίϛοτ
14. #JH2VFSZ 
15. #JH2VFSZ
    ΞΫηεํ๏  w ͦͷ
    ਪ঑
    w (PPHMF
    ެࣜϥΠϒϥϦ 
 DMPVEHPPHMFDPNHPCJHRVFSZ
    w ͦͷ
    ୅ସखஈ
    w EBUBCBTFTRM
    ༻υϥΠόʔ 
 HJUIVCDPNTPMDBUFTHPTRMCJHRVFSZ
    w ͲͪΒͷ৔߹΋
    ($1
    ͷೝূ৘ใͷൃߦ͕ඞཁ
16. ΫΤϦྫ  opts := option.WithCredentialsFile("credentials.json" ) client, err := bigquery.NewClient(context.Background(), "u-motion", opts ) if err != nil { log.Fatalf("ΫϥΠΞϯτͷॳظԽʹࣦഊ͠·ͨ͠: %v", err ) } defer client.Close( ) it, err := client.Query ( "SELECT farm_id, farm_name FROM `u-motion.prod.farm` LIMIT 10" , ).Read(context.Background() ) if err != nil { log.Fatalf("ΫΤϦʹࣦഊ͠·ͨ͠: %v", err ) } for { var farm struct { ID int `bigquery:"farm_id" ` Name string `bigquery:"farm_name" ` } if err := it.Next(&farm); err == iterator.Done { brea k } else if err != nil { log.Fatalf("ΫΤϦ݁ՌͷಡΈࠐΈʹࣦഊ͠·ͨ͠: %v", err ) } fmt.Printf("%4d %s\n", farm.ID, farm.Name ) }
17. ΫΤϦྫ  opts := option.WithCredentialsFile("credentials.json" ) client, err := bigquery.NewClient(context.Background(), "u-motion", opts ) if err != nil { log.Fatalf("ΫϥΠΞϯτͷॳظԽʹࣦഊ͠·ͨ͠: %v", err ) } defer client.Close( ) it, err := client.Query ( "SELECT farm_id, farm_name FROM `u-motion.prod.farm` LIMIT 10" , ).Read(context.Background() ) if err != nil { log.Fatalf("ΫΤϦʹࣦഊ͠·ͨ͠: %v", err ) } for { var farm struct { ID int `bigquery:"farm_id" ` Name string `bigquery:"farm_name" ` } if err := it.Next(&farm); err == iterator.Done { brea k } else if err != nil { log.Fatalf("ΫΤϦ݁ՌͷಡΈࠐΈʹࣦഊ͠·ͨ͠: %v", err ) } fmt.Printf("%4d %s\n", farm.ID, farm.Name ) } ೝূ৘ใ 1SPKFDU
    *% ϑΟʔϧυλάͰϚοϐϯάՄೳ
18. ͓΍ͦ͘͘  w ೝূ৘ใͷ࢖͍·Θ͠͸ઈରʹ͠ͳ͍͜ͱ
    w ྻ୯ҐͰ՝ۚ͞ΕΔͷͰ
    SELECT *
    ౳ͷૢ࡞͸ ߇͑Δ͜ͱ
    SELECT COUNT (*)
    ͸আ͘
    w ࣌ܥྻσʔλΛ֨ೲ͢Δςʔϒϧ͸͍͍ͨͯύʔ ςΟγϣϯԽ͞Ε͍ͯΔͷͰɺඞͣର৅ΧϥϜΛ ௐ΂ͯ
    WHERE
    ۟ʹؚΊΔ͜ͱ
19. %ZOBNP%# 
20. %ZOBNP%#
    ΞΫηεํ๏  w ͦͷ
    ਪ঑
    w BXTTELHP
    W 
 HJUIVCDPNBXTBXTTELHPW
    w ͦͷ
    ࣾ಺ίʔυͷେଟ਺͸͜Ε
    w BXTTELHP 
 HJUIVCDPNBXTBXTTELHP
    w ͲͪΒͷ৔߹΋
    BXT
    DMJ
    ͷࣄલઃఆ͕ඞཁ͕ͩɺୈճͷ
    -BNCEB
    ճͰઃఆࡁͷ͸ͣ
21. ΍ͬͯΈΑ͏
      package mai n import ( // import தུ.. . "github.com/aws/aws-sdk-go-v2/aws " "github.com/aws/aws-sdk-go-v2/config " "github.com/aws/aws-sdk-go-v2/service/dynamodb " ) func main() { cfg, err := config.LoadDefaultConfig(context.Background(), config.WithRegion("ap-northeast-1") ) if err != nil { log.Fatalf("AWS ೝূ৘ใΛಡΈࠐΊ·ͤΜͰͨ͠: %v", err ) } db := dynamodb.NewFromConfig(cfg) output, err := db.ListTables(context.Background(), &dynamodb.ListTablesInput{Limit: aws.Int32(50)}) if err != nil { log.Fatalf("ςʔϒϧҰཡͷऔಘʹࣦഊ͠·ͨ͠: %v", err ) } for _, name := range output.TableNames { fmt.Println(name ) } } Ϣʔβʔ໊ϑΥϧμEBZEZOBNPNBJOHP ౷߹λʔϛφϧͰʮgo mod tidyʯͱʮgo run .ʯΛ࣮ߦ͠·͢
22. ΍ͬͯΈΑ͏
      key, err := attributevalue.MarshalMap(struct{ ID string }{"99KqH_uPeE"} ) if err != nil { log.Fatalf("Ωʔͷߏஙʹࣦഊ͠·ͨ͠: %v", err ) } result, err := db.GetItem(context.Background(), &dynamodb.GetItemInput { TableName: aws.String("UMForceAccounts") , Key: key , } ) if err != nil { log.Fatalf("ΞΠςϜͷऔಘʹࣦഊ͠·ͨ͠: %v", err ) } if result.Item == nil { log.Fatal("ࢦఆ͞ΕͨΞΠςϜ͕͋Γ·ͤΜ" ) } ҎԼͷίʔυΛ
    NBJO
    ಺ͷ຤ඌʹ௥Ճ ͖͞΄Ͳͷίʔυʹ
    JNQPSU
    Λ௥Ճ "github.com/aws/aws-sdk-go-v2/feature/dynamodb/attributevalue"
 "github.com/aws/aws-sdk-go-v2/feature/dynamodb/expression" ౷߹λʔϛφϧͰʮgo mod tidyʯͱʮgo run .ʯΛ࣮ߦ͠·͢
23. ΍ͬͯΈΑ͏
      var account struct { ID strin g Name strin g State string `dynamodbav:"ContactState" ` } if err := attributevalue.UnmarshalMap(result.Item, &account); err != nil { log.Fatalf("Ϩίʔυͷղੳʹࣦഊ͠·ͨ͠: %v", err ) } fmt.Printf("ID: %s\n໊લ: %s\n౎ಓ෎ݝ: %s\n", account.ID, account.Name, account.State) ҎԼͷίʔυΛ
    NBJO
    ಺ͷ຤ඌʹ௥Ճ ౷߹λʔϛφϧͰʮgo mod tidyʯͱʮgo run .ʯΛ࣮ߦ͠·͢
24. εΩϟϯྫ  filter := expression.Name("SalesRegion").Equal(expression.Value("ؔ౦") ) projection := expression.NamesList(expression.Name("Name"), expression.Name("ContactState") ) expr, err := expression.NewBuilder().WithFilter(filter).WithProjection(projection).Build( ) if err != nil { log.Fatalf("ϑΟϧλʔͷߏஙʹࣦഊ͠·ͨ͠: %v", err ) } output, err := db.Scan(context.Background(), &dynamodb.ScanInput { TableName: aws.String("UMForceAccounts") , FilterExpression: expr.Filter() , ProjectionExpression: expr.Projection() , ExpressionAttributeNames: expr.Names() , ExpressionAttributeValues: expr.Values() , } ) if err != nil { log.Fatalf("ΫΤϦͷ࣮ߦʹࣦഊ͠·ͨ͠: %v", err ) }
 var accounts []struct { Name strin g State string `dynamodbav:"ContactState" ` } if err := attributevalue.UnmarshalListOfMaps(output.Items, &accounts); err != nil { log.Fatalf("ΫΤϦ݁Ռͷղੳʹࣦഊ͠·ͨ͠: %v", err ) }
25. εΩϟϯྫ  filter := expression.Name("SalesRegion").Equal(expression.Value("ؔ౦") ) projection := expression.NamesList(expression.Name("Name"), expression.Name("ContactState") ) expr, err := expression.NewBuilder().WithFilter(filter).WithProjection(projection).Build( ) if err != nil { log.Fatalf("ϑΟϧλʔͷߏஙʹࣦഊ͠·ͨ͠: %v", err ) } output, err := db.Scan(context.Background(), &dynamodb.ScanInput { TableName: aws.String("UMForceAccounts") , FilterExpression: expr.Filter() , ProjectionExpression: expr.Projection() , ExpressionAttributeNames: expr.Names() , ExpressionAttributeValues: expr.Values() , } ) if err != nil { log.Fatalf("ΫΤϦͷ࣮ߦʹࣦഊ͠·ͨ͠: %v", err ) }
 var accounts []struct { Name strin g State string `dynamodbav:"ContactState" ` } if err := attributevalue.UnmarshalListOfMaps(output.Items, &accounts); err != nil { log.Fatalf("ΫΤϦ݁Ռͷղੳʹࣦഊ͠·ͨ͠: %v", err ) } औಘ৚݅ͱऔಘ͢ΔଐੑΛઃఆ ࣗಈੜ੒͞Εͨࣜ΍ύϥϝʔλʔΛઃఆ ϑΟʔϧυλάͰϚοϐϯάΛΧελϚΠζ
26. %ZOBNP%#
    ͷ༷ʑͳૢ࡞  ૢ࡞ આ໌ ஫ҙ఺ (FU*UFN ࢦఆͨ͠ΞΠςϜΛͭऔಘ͢Δ  1VU*UFN ࢦఆͨ͠ΞΠςϜΛͭ౤ೖ͢Δ طʹ͋Ε͹ஔ͖׵͑ΒΕΔ 2VFSZ ΩʔΛࢦఆ͔ͯͭ͠ෳ਺ͷΞΠςϜΛऔಘ͢Δ Ωʔ΍ΠϯσοΫεͷઃܭ͕ॏཁ 4DBO શΞΠςϜΛᢞΊͯ݁ՌΛऔಘ͢Δ ΞΠςϜ਺͕ଟ͍ͱ͔ͳΓ஗͍ 6QEBUF ࢦఆͨ͠ΞΠςϜͷࢦఆͨ͠ଐੑΛมߋ͢Δ 1VU*UFN
    ΑΓ஗͍ %FMFUF ࢦఆͨ͠ΞΠςϜΛ࡟আ͢Δ  #BUDI(FU*UFN ෳ਺ͷ
    (FU*UFN
    ͷҰׅૢ࡞ͰεϐʔυΞοϓ ফඅΩϟύγςΟ͸มΘΒͳ͍ #BUDI1VU*UFN ෳ਺ͷ
    1VU*UFN
    ͷҰׅૢ࡞ͰεϐʔυΞοϓ ফඅΩϟύγςΟ͸มΘΒͳ͍
27. ࿅श 
28. ࿅श໰୊ w ࿅श՝୊
    
    <ϑΥϧμ໊
    EBZQTRM>
    w ຀৔໊Λ༩͑ΔͱҰகͨ͠຀৔ͷॅॴΛ౴͑ΔίϚϯυϥΠϯπʔϧ Λ࡞੒͍ͯͩ͘͠͞
    w ࿅श՝୊
    
    <ϑΥϧμ໊
    EBZEZOBNP>
    w ΞΧ΢ϯτ໊Λ༩͑ΔͱҰகͨ͠ΞΧ΢ϯτͷࣾ಺γεςϜͷϖʔδ Λϒϥ΢βͰ։͘ίϚϯυϥΠϯπʔϧΛ࡞੒͍ͯͩ͘͠͞
    w ώϯτ
    4DBO
    ૢ࡞ͷར༻ΛڐՄ͠·͢
    w ώϯτ
    ϓϩάϥϜ͔Βϒϥ΢βΛ։͘ํ๏ 
 exec.Command("rundll32", "url.dll,FileProtocolHandler", url).Start() 
29. ίʔυΛॻ͖ऴ͑ͨΒ w ʮϢʔβʔ໊QSBDUJDFʯϒϥϯνͰ࡞ۀ͍ͯ͠Δ͜ͱΛ֬ೝ͍ͯͩ͘͠͞
    
    w 74
    $PEF
    ͷҰ൪ࠨԼʹදࣔ͞Ε͍ͯ·͢
    w มߋΛεςʔδ͠ɺίϛοτ͍ͯͩ͘͠͞
    w 74
    $PEF
    ͷࠨϝχϡʔͷʮιʔε؅ཧʯ͔Βૢ࡞
    w ίʔυΛ
    (JU)VC
    ʹϓογϡ͍ͯͩ͘͠͞
    w 74
    $PEF
    ͷҰ൪ࠨԼͷϒϥϯν໊ͷ͙͢ӈʹ͋ΔΞΠίϯ͔Βૢ࡞
    ্ਤ
    w ϓϧϦΫΤετΛ࡞੒͍ͯͩ͘͠͞
    w ʮιʔε؅ཧʯͷ্෦ʹΞΠίϯ͕͋Γ·͢ 
30. ࣍ճ༧ࠂ w ࣍ճ͸ʮ(6*
    ΞϓϦέʔγϣϯ։ൃʯͰ͢
    w ෼͔Βͳ͔ͬͨͱ͜Ζ΍࣭໰ͳͲ͸ɺࣾ಺
    4MBDL
    HPQIFST
    νϟϯωϧʹ౤ߘ͍ͩ͘͞
    w ຊ೔ͷ಺༰ΛΑΓਂ͘ཧղ͍ͨ͠ํ͸ɺڭՊॻ ͷୈষઅ΍
    QLHHPEFW
    ʹ͋Δ
    "1*
    υΩϡϝ ϯτͳͲΛಡΜͰΈ͍ͯͩ͘͞