Slide 1
Slide 1 text
(Pݚमୈظ
%":
:VUBLB,BUP
3%
%&4".*4
%&4".*4ࣾݚम
Slide 2
Slide 2 text
ݚमܭը
ճ ࣍ ༧ఆ
λΠτϧ
ୈճ Ր
(Pͷجຊ
ୈճ Ր
ڥߏஙͱ(JU
(JU)VCͷجຊ
ୈճ Ր
جຊߏจ
ୈճ Ր
ߏମɺεϥΠεɺϚοϓ
ୈճ Ր
ඪ४ϥΠϒϥϦΊ͙Γ
ୈճ Ր
ϞδϡʔϧγεςϜͱ֎෦ϥΠϒϥϦ
ୈճ Ր
)FSPLV(Pೖ
ୈճ Ր
"84-BNCEB(Pೖ
ୈճ Ր
ฒߦॲཧϓϩάϥϛϯά
ୈճ Ր
୯ମςετٕ๏
ୈճ Ր
σʔλϕʔεΞΫηε
ୈճ Ր
'ZOFʹΑΔ(6*ΞϓϦέʔγϣϯ։ൃ
ୈճ Ր
ࣾ(PϓϩδΣΫτͷίʔυղઆ
Slide 3
Slide 3 text
˞
લճͷ13ͷ
ղઆλΠϜ
Slide 4
Slide 4 text
ຊͷΰʔϧ
w 🚩ΰʔϧ
w 42-ͷଓɾσʔλऔಘํ๏Λशಘ͢Δ
w /P42-ͷଓɾσʔλऔಘํ๏Λशಘ͢Δ
w ❌ΰʔϧͰͳ͍
w ςʔϒϧઃܭɺੑೳվળɺ03.ͷར༻ɺFUD
Slide 5
Slide 5 text
EBUBCBTFTRM
Slide 6
Slide 6 text
EBUBCBTFTRM
w 42-Λ࣮ߦ͢ΔͨΊͷڞ௨ΠϯλϑΣʔεΛఏڙ͢Δඪ
४ϥΠϒϥϦ
w جຊతͳσʔλϕʔεଓͷཧ42-ΠϯδΣΫγϣ
ϯͷอޢͳͲΛఏڙ͢Δ
w ࣮ࡍͷσʔλϕʔεʹΞΫηε͢Δʹɺσʔλϕʔε
͝ͱʹ༻ҙ͞ΕͨυϥΠόʔͷ ϒϥϯΫ
Πϯϙʔτ͕
ඞཁ
import
(
"database/sql
"
_ "github.com/lib/pq
"
)
Slide 7
Slide 7 text
දతͳυϥΠόʔ
%#.4 %SJWFS
.Z42- HJUIVCDPNHPTRMESJWFSNZTRM
1PTUHSF42- HJUIVCDPNMJCQR
0SBDMF HJUIVCDPNNBUUOHPPDJ
42-4FSWFS HJUIVCDPNEFOJTFOLPNHPNTTRMEC
42-JUF HJUIVCDPNNBUUOHPTRMJUF
8JLJ👉IUUQTHJUIVCDPNHPMBOHHPXJLJ42-%SJWFST
Slide 8
Slide 8 text
ͬͯΈΑ͏
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>ʯΛ࣮ߦ͠·͢
Slide 9
Slide 9 text
ͬͯΈΑ͏
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ͷԼʹɺҎԼͷίʔυΛՃ͠·͢
౷߹λʔϛφϧͰઌ΄Ͳͱಉ͡ίϚϯυΛ࣮ߦ͠·͢
Slide 10
Slide 10 text
ͬͯΈΑ͏
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 *ʯةݥ
Slide 11
Slide 11 text
ςʔϒϧͷ࡞
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
)
}
Slide 12
Slide 12 text
τϥϯβΫγϣϯॲཧ
DBΦϒδΣΫτͷBegin()ϝιουͰ։࢝
TxΦϒδΣΫτ͕ฦͬͯ͘ΔͷͰɺͦΕܦ༝
ͰQuery()Exec()ΛݺͿ
్தͷૢ࡞͕ࣦഊͨ͠ΒRollback()ΛݺΜ
ͰॲཧΛൈ͚Δ
࠷ޙʹCommit()ΛݺͿ
Slide 13
Slide 13 text
͘͝γϯϓϧͳྫ
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("τϥϯβΫγϣϯྃ")
τϥϯβΫγϣϯͷ։࢝
ࣦഊͨ͠ΒϩʔϧόοΫ
࠷ޙʹίϛοτ
Slide 14
Slide 14 text
#JH2VFSZ
Slide 15
Slide 15 text
#JH2VFSZΞΫηεํ๏
w ͦͷ ਪ
w (PPHMFެࣜϥΠϒϥϦ
DMPVEHPPHMFDPNHPCJHRVFSZ
w ͦͷ ସखஈ
w EBUBCBTFTRM༻υϥΠόʔ
HJUIVCDPNTPMDBUFTHPTRMCJHRVFSZ
w ͲͪΒͷ߹($1ͷೝূใͷൃߦ͕ඞཁ
Slide 16
Slide 16 text
ΫΤϦྫ
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
)
}
Slide 17
Slide 17 text
ΫΤϦྫ
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*%
ϑΟʔϧυλάͰϚοϐϯάՄೳ
Slide 18
Slide 18 text
͓ͦ͘͘
w ೝূใͷ͍·Θ͠ઈରʹ͠ͳ͍͜ͱ
w ྻ୯ҐͰ՝ۚ͞ΕΔͷͰSELECT *ͷૢ࡞
߇͑Δ͜ͱ SELECT COUNT (*)আ͘
w ࣌ܥྻσʔλΛ֨ೲ͢Δςʔϒϧ͍͍ͨͯύʔ
ςΟγϣϯԽ͞Ε͍ͯΔͷͰɺඞͣରΧϥϜΛ
ௐͯWHERE۟ʹؚΊΔ͜ͱ
Slide 19
Slide 19 text
%ZOBNP%#
Slide 20
Slide 20 text
%ZOBNP%#ΞΫηεํ๏
w ͦͷ ਪ
w BXTTELHPW
HJUIVCDPNBXTBXTTELHPW
w ͦͷ ࣾίʔυͷେଟ͜Ε
w BXTTELHP
HJUIVCDPNBXTBXTTELHP
w ͲͪΒͷ߹BXTDMJͷࣄલઃఆ͕ඞཁ͕ͩɺୈճͷ
-BNCEBճͰઃఆࡁͷͣ
Slide 21
Slide 21 text
ͬͯΈΑ͏
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 .ʯΛ࣮ߦ͠·͢
Slide 22
Slide 22 text
ͬͯΈΑ͏
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 .ʯΛ࣮ߦ͠·͢
Slide 23
Slide 23 text
ͬͯΈΑ͏
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 .ʯΛ࣮ߦ͠·͢
Slide 24
Slide 24 text
εΩϟϯྫ
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
)
}
Slide 25
Slide 25 text
εΩϟϯྫ
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
)
}
औಘ݅ͱऔಘ͢ΔଐੑΛઃఆ
ࣗಈੜ͞ΕͨࣜύϥϝʔλʔΛઃఆ
ϑΟʔϧυλάͰϚοϐϯάΛΧελϚΠζ
Slide 26
Slide 26 text
%ZOBNP%#ͷ༷ʑͳૢ࡞
ૢ࡞ આ໌ ҙ
(FU*UFN ࢦఆͨ͠ΞΠςϜΛͭऔಘ͢Δ
1VU*UFN ࢦఆͨ͠ΞΠςϜΛͭೖ͢Δ طʹ͋Εஔ͖͑ΒΕΔ
2VFSZ ΩʔΛࢦఆ͔ͯͭ͠ෳͷΞΠςϜΛऔಘ͢Δ ΩʔΠϯσοΫεͷઃܭ͕ॏཁ
4DBO શΞΠςϜΛᢞΊͯ݁ՌΛऔಘ͢Δ ΞΠςϜ͕ଟ͍ͱ͔ͳΓ͍
6QEBUF ࢦఆͨ͠ΞΠςϜͷࢦఆͨ͠ଐੑΛมߋ͢Δ 1VU*UFNΑΓ͍
%FMFUF ࢦఆͨ͠ΞΠςϜΛআ͢Δ
#BUDI(FU*UFN ෳͷ(FU*UFNͷҰׅૢ࡞ͰεϐʔυΞοϓ ফඅΩϟύγςΟมΘΒͳ͍
#BUDI1VU*UFN ෳͷ1VU*UFNͷҰׅૢ࡞ͰεϐʔυΞοϓ ফඅΩϟύγςΟมΘΒͳ͍
Slide 27
Slide 27 text
࿅श
Slide 28
Slide 28 text
࿅श
w ࿅श՝<ϑΥϧμ໊EBZQTRM>
w ໊Λ༩͑ΔͱҰகͨ͠ͷॅॴΛ͑ΔίϚϯυϥΠϯπʔϧ
Λ࡞͍ͯͩ͘͠͞
w ࿅श՝<ϑΥϧμ໊EBZEZOBNP>
w ΞΧϯτ໊Λ༩͑ΔͱҰகͨ͠ΞΧϯτͷࣾγεςϜͷϖʔδ
ΛϒϥβͰ։͘ίϚϯυϥΠϯπʔϧΛ࡞͍ͯͩ͘͠͞
w ώϯτ
4DBOૢ࡞ͷར༻ΛڐՄ͠·͢
w ώϯτ
ϓϩάϥϜ͔ΒϒϥβΛ։͘ํ๏
exec.Command("rundll32", "url.dll,FileProtocolHandler", url).Start()
Slide 29
Slide 29 text
ίʔυΛॻ͖ऴ͑ͨΒ
w ʮϢʔβʔ໊QSBDUJDFʯϒϥϯνͰ࡞ۀ͍ͯ͠Δ͜ͱΛ֬ೝ͍ͯͩ͘͠͞
w 74$PEFͷҰ൪ࠨԼʹදࣔ͞Ε͍ͯ·͢
w มߋΛεςʔδ͠ɺίϛοτ͍ͯͩ͘͠͞
w 74$PEFͷࠨϝχϡʔͷʮιʔεཧʯ͔Βૢ࡞
w ίʔυΛ(JU)VCʹϓογϡ͍ͯͩ͘͠͞
w 74$PEFͷҰ൪ࠨԼͷϒϥϯν໊ͷ͙͢ӈʹ͋ΔΞΠίϯ͔Βૢ࡞ ্ਤ
w ϓϧϦΫΤετΛ࡞͍ͯͩ͘͠͞
w ʮιʔεཧʯͷ্෦ʹΞΠίϯ͕͋Γ·͢
Slide 30
Slide 30 text
࣍ճ༧ࠂ
w ࣍ճʮ(6*ΞϓϦέʔγϣϯ։ൃʯͰ͢
w ͔Βͳ͔ͬͨͱ͜Ζ࣭ͳͲɺࣾ
4MBDLHPQIFSTνϟϯωϧʹߘ͍ͩ͘͞
w ຊͷ༰ΛΑΓਂ͘ཧղ͍ͨ͠ํɺڭՊॻ
ͷୈষઅQLHHPEFWʹ͋Δ"1*υΩϡϝ
ϯτͳͲΛಡΜͰΈ͍ͯͩ͘͞