Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DESAMIS Go Training Season 1 Day 11

DESAMIS Go Training Season 1 Day 11

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

Yutaka Kato

July 06, 2021
Tweet

More Decks by Yutaka Kato

Other Decks in Technology

Transcript

  1. ݚमܭը ճ ೔࣍ ༧ఆ λΠτϧ ୈճ  Ր  (Pͷجຊ

    ୈճ  Ր  ؀ڥߏஙͱ(JU (JU)VCͷجຊ ୈճ  Ր  جຊߏจ ୈճ  Ր  ߏ଄ମɺεϥΠεɺϚοϓ ୈճ  Ր  ඪ४ϥΠϒϥϦ͸΍Ί͙Γ ୈճ  Ր  ϞδϡʔϧγεςϜͱ֎෦ϥΠϒϥϦ ୈճ  Ր  )FSPLV(Pೖ໳ ୈճ  Ր  "84-BNCEB(Pೖ໳ ୈճ  Ր  ฒߦॲཧϓϩάϥϛϯά ୈճ  Ր  ୯ମςετٕ๏ ୈճ  Ր  σʔλϕʔεΞΫηε ୈճ  Ր  'ZOFʹΑΔ(6*ΞϓϦέʔγϣϯ։ൃ ୈճ  Ր  ࣾ಺(PϓϩδΣΫτͷίʔυղઆ 
  2. ୅දతͳυϥΠόʔ  %#.4 %SJWFS .Z42- HJUIVCDPNHPTRMESJWFSNZTRM 1PTUHSF42- HJUIVCDPNMJCQR 0SBDMF HJUIVCDPNNBUUOHPPDJ

    42-4FSWFS HJUIVCDPNEFOJTFOLPNHPNTTRMEC 42-JUF HJUIVCDPNNBUUOHPTRMJUF 8JLJ👉IUUQTHJUIVCDPNHPMBOHHPXJLJ42-%SJWFST
  3. ΍ͬͯΈΑ͏   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>ʯΛ࣮ߦ͠·͢
  4. ΍ͬͯΈΑ͏   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ͷԼʹɺҎԼͷίʔυΛ௥Ճ͠·͢  ౷߹λʔϛφϧͰઌ΄Ͳͱಉ͡ίϚϯυΛ࣮ߦ͠·͢
  5. ΍ͬͯΈΑ͏   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 *ʯ͸ةݥ
  6. ςʔϒϧͷ࡞੒  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 ) }
  7. ͘͝γϯϓϧͳྫ  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("τϥϯβΫγϣϯ׬ྃ") τϥϯβΫγϣϯͷ։࢝ ࣦഊͨ͠ΒϩʔϧόοΫ ࠷ޙʹίϛοτ
  8. #JH2VFSZΞΫηεํ๏  w ͦͷ ਪ঑  w (PPHMFެࣜϥΠϒϥϦ 
 DMPVEHPPHMFDPNHPCJHRVFSZ

    w ͦͷ ୅ସखஈ  w EBUBCBTFTRM༻υϥΠόʔ 
 HJUIVCDPNTPMDBUFTHPTRMCJHRVFSZ w ͲͪΒͷ৔߹΋($1ͷೝূ৘ใͷൃߦ͕ඞཁ
  9. ΫΤϦྫ  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 ) }
  10. ΫΤϦྫ  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*% ϑΟʔϧυλάͰϚοϐϯάՄೳ
  11. ͓΍ͦ͘͘  w ೝূ৘ใͷ࢖͍·Θ͠͸ઈରʹ͠ͳ͍͜ͱ w ྻ୯ҐͰ՝ۚ͞ΕΔͷͰSELECT *౳ͷૢ࡞͸ ߇͑Δ͜ͱ SELECT COUNT

    (*)͸আ͘  w ࣌ܥྻσʔλΛ֨ೲ͢Δςʔϒϧ͸͍͍ͨͯύʔ ςΟγϣϯԽ͞Ε͍ͯΔͷͰɺඞͣର৅ΧϥϜΛ ௐ΂ͯWHERE۟ʹؚΊΔ͜ͱ
  12. %ZOBNP%#ΞΫηεํ๏  w ͦͷ ਪ঑  w BXTTELHPW 
 HJUIVCDPNBXTBXTTELHPW

    w ͦͷ ࣾ಺ίʔυͷେଟ਺͸͜Ε  w BXTTELHP 
 HJUIVCDPNBXTBXTTELHP w ͲͪΒͷ৔߹΋BXTDMJͷࣄલઃఆ͕ඞཁ͕ͩɺୈճͷ -BNCEBճͰઃఆࡁͷ͸ͣ
  13. ΍ͬͯΈΑ͏   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 .ʯΛ࣮ߦ͠·͢
  14. ΍ͬͯΈΑ͏   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 .ʯΛ࣮ߦ͠·͢
  15. ΍ͬͯΈΑ͏   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 .ʯΛ࣮ߦ͠·͢
  16. εΩϟϯྫ  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 ) }
  17. εΩϟϯྫ  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 ) } औಘ৚݅ͱऔಘ͢ΔଐੑΛઃఆ ࣗಈੜ੒͞Εͨࣜ΍ύϥϝʔλʔΛઃఆ ϑΟʔϧυλάͰϚοϐϯάΛΧελϚΠζ
  18. %ZOBNP%#ͷ༷ʑͳૢ࡞  ૢ࡞ આ໌ ஫ҙ఺ (FU*UFN ࢦఆͨ͠ΞΠςϜΛͭऔಘ͢Δ  1VU*UFN ࢦఆͨ͠ΞΠςϜΛͭ౤ೖ͢Δ

    طʹ͋Ε͹ஔ͖׵͑ΒΕΔ 2VFSZ ΩʔΛࢦఆ͔ͯͭ͠ෳ਺ͷΞΠςϜΛऔಘ͢Δ Ωʔ΍ΠϯσοΫεͷઃܭ͕ॏཁ 4DBO શΞΠςϜΛᢞΊͯ݁ՌΛऔಘ͢Δ ΞΠςϜ਺͕ଟ͍ͱ͔ͳΓ஗͍ 6QEBUF ࢦఆͨ͠ΞΠςϜͷࢦఆͨ͠ଐੑΛมߋ͢Δ 1VU*UFNΑΓ஗͍ %FMFUF ࢦఆͨ͠ΞΠςϜΛ࡟আ͢Δ  #BUDI(FU*UFN ෳ਺ͷ(FU*UFNͷҰׅૢ࡞ͰεϐʔυΞοϓ ফඅΩϟύγςΟ͸มΘΒͳ͍ #BUDI1VU*UFN ෳ਺ͷ1VU*UFNͷҰׅૢ࡞ͰεϐʔυΞοϓ ফඅΩϟύγςΟ͸มΘΒͳ͍
  19. ࿅श໰୊ w ࿅श՝୊<ϑΥϧμ໊EBZQTRM> w ຀৔໊Λ༩͑ΔͱҰகͨ͠຀৔ͷॅॴΛ౴͑ΔίϚϯυϥΠϯπʔϧ Λ࡞੒͍ͯͩ͘͠͞ w ࿅श՝୊<ϑΥϧμ໊EBZEZOBNP> w ΞΧ΢ϯτ໊Λ༩͑ΔͱҰகͨ͠ΞΧ΢ϯτͷࣾ಺γεςϜͷϖʔδ

    Λϒϥ΢βͰ։͘ίϚϯυϥΠϯπʔϧΛ࡞੒͍ͯͩ͘͠͞ w ώϯτ 4DBOૢ࡞ͷར༻ΛڐՄ͠·͢ w ώϯτ ϓϩάϥϜ͔Βϒϥ΢βΛ։͘ํ๏ 
 exec.Command("rundll32", "url.dll,FileProtocolHandler", url).Start() 
  20. ίʔυΛॻ͖ऴ͑ͨΒ w ʮϢʔβʔ໊QSBDUJDFʯϒϥϯνͰ࡞ۀ͍ͯ͠Δ͜ͱΛ֬ೝ͍ͯͩ͘͠͞ w 74$PEFͷҰ൪ࠨԼʹදࣔ͞Ε͍ͯ·͢ w มߋΛεςʔδ͠ɺίϛοτ͍ͯͩ͘͠͞ w 74$PEFͷࠨϝχϡʔͷʮιʔε؅ཧʯ͔Βૢ࡞ w

    ίʔυΛ(JU)VCʹϓογϡ͍ͯͩ͘͠͞ w 74$PEFͷҰ൪ࠨԼͷϒϥϯν໊ͷ͙͢ӈʹ͋ΔΞΠίϯ͔Βૢ࡞ ্ਤ  w ϓϧϦΫΤετΛ࡞੒͍ͯͩ͘͠͞ w ʮιʔε؅ཧʯͷ্෦ʹΞΠίϯ͕͋Γ·͢