Step Functions と Lambda Function で組む Athena によるファイル処理 / serverless lightweight etl

AWS Serverless ͱϑΝΠϧ
Step Functions ͱ Lambda Function Ͱ૊Ή Athena ʹΑΔϑΝΠϧॲཧ
#serverlessjp

View Slide

ɹαʔόʔϨεͰϑΝΠϧ to ϑΝΠϧͷETL
ࠓ೔ͷ࿩

View Slide

View Slide

αʔόʔϨεͰETLͱ͍͑͹ AWS Glue ?

View Slide

View Slide

Amazon Athena Ͱσʔλநग़ʁ

View Slide

Θͨ͠ͷ"84(MVF΍"NB[PO"UIFOBͷୈҰҹ৅
"84ίϯιʔϧ͔Β࢖ͬͨ͜ͱ͋Δ໊͠લ΋஌ͬͯΔ
͚ͲɺΞϓϦέʔγϣϯʹ૊ΈࠐΉΠϝʔδ͕͋·ΓΘ
͔ͳ͍

View Slide

Θͨ͠ͷ"84(MVF΍"NB[PO"UIFOBͷୈҰҹ৅
αʔϏεͷ࣮ମ͕Α͘Θ͔ΒΜ
4ʢΩʔόϦϡʔͷʣετϨʔδ
%ZOBNP%#ɿ/P42- Ћ
ͷϑϧϚωʔδυσʔλϕʔε
"84"QQ4ZODɿ(SBQI2-"1*ͷήʔτ΢ΣΠ
4UFQ'VODUJPOTɿλεΫΛఆٛ͠ঢ়ଶભҠͱͯ͠δϣϒΛ૊Έཱͯ
"84(MVFɿͳΜ͔ม׵͢Δਓʁ
"NB[PO"UIFOBɿ4ͷσʔλʹରͯ͠ΫΤϦൃߦͯ͘͠ΕΔਓʁ

View Slide

ຊηογϣϯͷ໨త
"84(MVFͱ"NB[PO"UIFOBͷ͜ͱΛ஌ΓɺαʔόʔϨ
εΞϓϦέʔγϣϯʹ૊ΈࠐΉΠϝʔδΛ࣋ͬͯؼͬͯ΋Β͏
Ξϓϩʔνɿ
"84(MVFͱ"NB[PO"UIFOBͷ͜ͱΛ஌Δ
ϑΝΠϧUPϑΝΠϧͷ&5-ϢʔεέʔεΛߟ͑Δ
"84$%,ͰϦιʔεΛ࡞੒ͯ͠ΈΔ

View Slide

Ϋϥϝιͷ࿨ాͰ͢αʔόʔϨε޷͖Ͱ͢
࿨ా༞հ
$9ࣄۀຊ෦
αʔόʔϨεΤϯδχΞ
XBEEZ@V
༗ޮظݶ੾Ε

View Slide

$POUFOUT
"84(MVFͱ"NB[PO"UIFOBͷؔ܎
αϯϓϧϢʔεέʔεɿΠϕϯτ؅ཧαʔϏε
"84$%,ʹΑΔ&5-δϣϒ૊Έཱͯ

View Slide

"84(MVF
ߏ੒ཁૉ͸ɺσʔλΧλϩάͱαʔόʔϨε&5-ɺΦʔ
έετϨʔγϣϯͷ̏ͭ
ߏ੒ཁૉ
Ϧιʔε໊௨শ
ʢ$MPVE'PSNBUJPOΑΓʣ
σʔλΧλϩά "84(MVF5BCMF
αʔόʔϨε&5- "84(MVF+PC
ΦʔέετϨʔγϣϯ "84(MVF8PSLqPX
ࢀߟɿAWS CloudFormation ςϯϓϨʔτΛ࢖༻ͨ͠σʔλΧλϩάͷࣄલઃఆ - https://docs.aws.amazon.com/ja_jp/glue/latest/dg/populate-with-cloudformation-templates.html

View Slide

Glue Table

View Slide

"84(MVF5BCMF
"QBDIF)JWFޓ׵ͷϝλετΞ
΋͏গ͠Θ͔Γ΍͘͢ɿεΩʔϚఆ͚ٛͩΛ(MVFʹ΋ͨͤΒΕΔ
σʔλͷ࣮ମ͸ผʹ࣋ͨͤɺ(MVFͱͯ͠͸ఆ͚͓ٛͩ࣋ͬͯ͘
(MVF5BCMF͸͔͋ͨ΋εΩʔϚఆٛΛ΋ͬͨςʔϒϧͷΑ͏ʹѻ͑
Δ
ྫɿ"NB[PO"UIFOBͰΫΤϦͨ͠Γɺ(MVF+PCͰΧϥϜ໊͔Β
σʔλΛͱ͖ͬͯͨΓͰ͖Δ

View Slide

"84(MVF5BCMF
σʔλͷ࣮ମ 4ͳͲ
εΩʔϚఆٛͳͲͷϝλσʔ
λ
20190806 AWS Black Belt Online Seminar AWS Glue https://www.slideshare.net/AmazonWebServicesJapan/20190806-aws-black-belt-online-seminar-aws-glue?ref=https://aws.amazon.com/jp/blogs/news/webinar-bb-aws-glue-2019/

View Slide

Glue Job

View Slide

"84(MVF+PC
&5-ʢσʔλม׵ॲཧʣͷຊମ
σʔλΧλϩάʢ(MVF5BCMF
͔ΒσʔλΛநग़͠ɺϩδο
ΫΛద༻ͯ͠ɺผͷσʔλετΞʢλʔήοτʣʹग़ྗ͢Δ

View Slide

"84(MVF+PC
εΫϦϓτ͸1ZUIPO4IFMMͱ4QBSLͰબ୒
"84(MVF+PC 1ZUIPO4IFMM
"84(MVF+PC 4QBSL

ϝϞϦ (#PS(# (#
࣮ߦ࣌ؒ σϑΥϧτ࣌ؒ σϑΥϧτ࣌ؒ
ಉ࣮࣌ߦ਺ ΞΧ΢ϯτ಺Ͱ ΞΧ΢ϯτ಺Ͱ
՝ֹۚ
ੑೳͱ࣮ߦ࣌ؒϕʔεͷैྔ՝ۚ
ͨͩ͠গͳ͘ͱ΋෼ʹ੾Γ্͛
ىಈ࣌ؒ ਺ेඵ਺෼ ਺ेඵ਺෼
ݴޠ 1ZUIPO 1ZUIPOPS4DBMB
AWS GlueΛ࢖ͬͨ Serverless ETL ͷ࣮૷ύλʔϯ - Speaker Deck https://speakerdeck.com/seiichi1101/aws-gluewoshi-tuta-serverless-etl-falseshi-zhuang-patan-ac5553a5-f8e9-45fb-b093-4bcf0b113a48?slide=41

View Slide

Glue Workﬂow

View Slide

"84(MVF8PSLqPX
ΫϩʔϥͱδϣϒΛ࢖ͬͯҰ࿈ͷॲཧΛఆٛͰ͖Δ
wਐߦঢ়گΛ֬ೝͰ͖Δ
wෳ਺ͷδϣϒΛ૊Έ߹Θ
ͤΒΕΔ
w4UFQ'VODUJPOTͱࣅͯ
͍Δ͕ɺͪ͜Β͸(MVF
ͷΫϩʔϥ (MVF+PCͷ
ΈࢦఆՄೳ

View Slide

Amazon Athena ͱͷؔ܎

View Slide

View Slide

ެࣜτοϓʹ΋͏ॻ͍ͯΔ

View Slide

"84(MVF5BCMFͱ"NB[PO"UIFOB
4σʔλΧλϩάʹରͯ͠1SFTUPΫΤϦΛൃߦ

View Slide

ͳΜͱͳ͘Πϝʔδ͕༙͍ͨͰ͠ΐ͏͔
αʔϏεͷ࣮ମʹ࠶νϟϨϯδ
"84(MVFɿ(MVF5BCMF (MVF+PC (MVF8PSLqPXʹ෼ղ
Ͱ͖ɺͦΕͧΕࣗ༝ʹ૊Έ߹ΘͤΒΕΔ&5-αʔϏε
"NB[PO"UIFOBɿ4Λσʔλιʔεͱ͢Δ(MVF5BCMFʹ
ରͯ͠1SFTUPΫΤϦΛൃߦ͠ɺ݁ՌΛ4ʹग़ྗ͢ΔαʔϏ
ε

View Slide

$POUFOUT

View Slide

ϑΝΠϧΛѻ͍ͦ͏ͳγνϡΤʔγϣϯ
ՍۭͷΠϕϯτ؅ཧαʔϏε
(PPHMF'PSNͳͲͰηογϣϯΛެื͢Δʢ*%ϝΞυʣ
ಉ࣌ʹɺผͷ࢓૊ΈͰࢀՃνέοτΛൃߦ͢Δʢ*%ϝΞυʣ
͜ΕΒ͸$47ग़ྗՄೳ
νέοτ*%Λࢦఆ͢Δͱɺηογϣϯ৘ใΛऔಘͰ͖ΔΑ͏ͳ
"1*Λ࡞Γ͍ͨ

View Slide

ͳͥʁ
ෳ਺ͷαʔϏεͷ৘ใ͔ΒͻͱͭͷαʔϏεͷόοΫΤϯυ
ʹݟ͔͚ͤΔ࢓૊Έ͸ࠓޙ૿͑ͦ͏
ศརͳαʔϏε͕ͨ͘͞Μ
ͲΕ͔ҰͭͷαʔϏεΛ࢖͍ͬͯΕ͹ਓੜ͕׬݁͢Δͱ͍͏͜ͱ͸
গͳ͘ͳ͖ͬͯͦ͏
େ఍ͷαʔϏεͰ͸ɺσʔλΛ$47ͱͯ͠ग़ྗՄೳ
8FCIPPL &WFOU#SJEHFʜ͍Ζ͍Ζͳ࿈ܞํ๏͕͋Δ͕ɺϑΝΠϧ
Λೖྗͱͯ͠&5-͢Δधཁ΋ɺࠓޙ૿͑ͦ͏

View Slide

ηογϣϯ৘ใͷެืͱ$47ग़ྗ
.csv

View Slide

ࢀՃऀνέοτͷ$47ग़ྗ
λΠϜελϯϓ ໊͓લ ϝʔϧΞυϨε ॴଐ૊৫ ࢀՃ೔
ޕޙ(.5 ࿨ా༞հ XBEBZVTVLF!DMBTTNFUIPEKQ Ϋϥεϝιουגࣜձࣾ ೔
ޕޙ(.5 ৽Ҫ੣Ұ XBEBZVTVLF!DMBTTNFUIPEKQ Ϋϥεϝιουגࣜձࣾ ೔
ࢀՃ೔Λ͓બͼ͍ͩ͘͞ɻ νέοτ*% Ϣʔβʔ*%
೔໨೔໨೔໨ BFBCFCBEFCEFG
GEEBBDBFCBC
೔໨೔໨೔໨ FGFBBCFBBCF
ECED

View Slide

νέοτ*%͔Βηογϣϯ৘ใΛҾ͘
(&5IUUQTBQJNFFUVQQFSDPNTFTTJPO FWFOU*E
UJDLFU*EBFBCFCBEFCEFG
{
"name": "࿨ా ༞հ",
"title": "αʔόʔϨε x ϑΝΠϧɿAmazon Athena ͱ Step Functions Λ૊Έ߹ΘͤͯϑΝΠϧΛ݁߹͢Δ",
"description": "αʔόʔϨεͰϑΝΠϧΛѻ͏γʔϯ͕૿͖͑ͯ·ͨ͠ɻAWSͰอଘ͓ͯ͘͠৔ॴͱͯ͠S3͸༗ྗͰ͕͢ɺΞ
ϓϦέʔγϣϯͰར༻Ͱ͖Δܗࣜʹม׵͢Δํ๏ͱͯ͠ɺଟ͘ͷબ୒ࢶ͕͋Γ·͢ɻ\n͜ͷηογϣϯͰ͸ɺʮผʑͷαʔϏε
Ͱൃߦ͞ΕͨCSVϑΝΠϧΛJOIN͢Δʯͱ͍͏Ϣʔεέʔεʹର͠ɺAmazonAthenaΛ࢖ͬͨΞϓϩʔνΛ঺հ͠·͢ɻ͞Β
ʹɺҰ࿈ͷॲཧΛStepFunctionsʹ·ͱΊΔ͜ͱͰɺδϣϒͱͯ͠ఆٛ͢Δํ๏Λࣔ͠·͢ɻ"
}

View Slide

ཁ݅Λ֬ೝ
࠷ऴతʹ͸νέοτ*%͔Βɺηογϣϯσʔλ͕Ҿͬ
ுͬͯ͜ΒΕΕ͹ྑ͍
ืूظؒऴྃޙ͸$47ϑΝΠϧͷมߋ͸΄΅ͳ͍ͷͰɺϦΞϧ
λΠϜʹมߋΛ൓ө͢Δඞཁ͸ͳ͍
ͨͩ͠؅ཧը໘ͳͲ͔Β$47ϑΝΠϧΛΞοϓϩʔυ͢Δ͜ͱ
ͰมߋΛ൓өͰ͖ΔΑ͏ʹͯ͠΄͍͠ɻ͍͍ͩͨ෼͘Β͍Ͱ
Πϕϯτ͝ͱʹืू͢ΔͷͰɺΠϕϯτऴྃޙ͸ΞʔΧΠϒՄೳ

View Slide

Ͳ͏࣮ݱ͢Δ͔
3%#Ͱ͋Ε͹ͦΕͧΕͷςʔϒϧʹσʔλΛೖΕͯϦ
ΫΤετ࣌ʹ+0*/͢Ε͹ྑ͍͕ʜ
"84αʔόʔϨεͩͱҎԼύλʔϯ͍ͣΕ͔

View Slide

Ͳ͏࣮ݱ͢Δ͔
3%#Ͱ͋Ε͹ͦΕͧΕͷςʔϒϧʹσʔλΛೖΕͯϦ
ΫΤετ࣌ʹ+0*/͢Ε͹ྑ͍͕ʜ
"84αʔόʔϨεͩͱҎԼύλʔϯ͍ͣΕ͔
͜ΕΛ࢖͏
ɾϦΞϧλΠϜੑ͸ཁٻ͞Εͳ͍
ɾݹ͍ΠϕϯτͷΦϒδΣΫτ͸(MBDJFSߦ͖
ɾಡΈग़࣌͠ͷϩδοΫ͸࠷খݶʹ͍ͨ͠

View Slide

ΰʔϧɿJOINࡁΈͷS3 ΦϒδΣΫτΛ࡞Δ
S3 to S3 ͷϑΝΠϧ ETL

View Slide

"84(MVFʹΑΔ&5-Λࢥ͍ग़͢

View Slide

"84(MVFʹΑΔ&5-Λࢥ͍ग़͢
ɾσʔλΧλϩάΛ"NB[PO"UIFOBͰΫΤϦ͢Δ
ɾJOTFSUJOUPͰ+0*/͢Δ
ɾҰ࿈ͷྲྀΕΛ4UFQ'VODUJPOTͰ૊Ή
ʜͱ͍͏ϧʔτ΋͋Δ

View Slide

ͳͥผϧʔτΛݕ౼͢Δͷ͔ʁ
͜ͷن໛ͩͱ(MVF+PCͷىಈ࣌ؒͱ՝ֹ͕ۚωοΫ
"84(MVF+PC 1ZUIPO4IFMM
"84(MVF+PC 4QBSL

ϝϞϦ (#PS(# (#
࣮ߦ࣌ؒ σϑΥϧτ࣌ؒ σϑΥϧτ࣌ؒ
ಉ࣮࣌ߦ਺ ΞΧ΢ϯτ಺Ͱ ΞΧ΢ϯτ಺Ͱ
՝ֹۚ
ىಈ࣌ؒ ਺ेඵ਺෼ ਺ेඵ਺෼

View Slide

ͳͥผϧʔτΛݕ౼͢Δͷ͔ʁ
(MVF5BCMFͷΫΤϦ͸"UIFOBʹ೚࣮ͤͯߦ؀ڥΛ
-BNCEB'VODUJPOʹ͢Δͱ͍͏ख
"84(MVF+PC
1ZUIPO4IFMM

"84(MVF+PC 4QBSL

"84-BNCEB
ϝϞϦ (#PS(# (# .#
࣮ߦ࣌ؒ σϑΥϧτ࣌ؒ σϑΥϧτ࣌ؒ ࠷େ෼
ಉ࣮࣌ߦ਺ ΞΧ΢ϯτ಺Ͱ ΞΧ΢ϯτ಺Ͱ Ϧʔδϣϯຖʹ
՝ֹۚ ෼ʹ੾Γ্͛ ෼ʹ੾Γ্͛ ϛϦඵʹ੾Γ্͛
ىಈ࣌ؒ ਺ेඵ਺෼ ਺ेඵ਺෼ ਺ϛϦඵ਺ඵ
αϙʔτ͍ͯ͠ΔϥϯλΠϜ
ΧελϜϥϯλΠϜ

View Slide

ΞʔΩςΫνϟ֓؍
※࣮ࡍʹ͸ S3 ΦϒδΣΫτͷURLͳͲΛอଘ͓ͯ͘͠ DynamoDB ͕ඞཁ

View Slide

$POUFOUT

View Slide

"84$%,Λ࢖ͬͯ૊ΈཱͯΔྲྀΕͷҰ෦Λ঺հ
$%,ʹ͍ͭͯ͸ΫϥϝιͷΠϕϯτͰ΋ൃදͯ͠·͢
ʲొஃࢿྉʳAWS CDK Λ࢖ͬͨ αʔόʔϨεΞϓϦέʔγϣϯͷσϓϩΠํ๏ͱ࣮૷ྫΛ঺հ͠·ͨ͠ – Developes.IO 2020 CONNECT #devio2020 | Developers.IO https://dev.classmethod.jp/articles/production-deploy-serverless-aws-cdk/

View Slide

"84$%,*OGSBTUSVDUVSFBT$PEF
5ZQF4DSJQUͳͲͷίʔυ͔Β$MPVE'PSNBUJPOς
ϯϓϨʔτΛग़ྗɺσϓϩΠՄೳ
What is the AWS CDK? - AWS Cloud Development Kit (AWS CDK) https://docs.aws.amazon.com/cdk/latest/guide/home.html

View Slide

$%,αϯϓϧɿ4-BNCEB'VODUJPO

View Slide

"UIFOBͬͯ$47Ͱ΋ΫΤϦͰ͖ΔΜ͡Όʁ
ࠓճͷηογϣϯ֓ཁͷΑ͏ͳ$47σʔλ಺ʹվߦΛؚΉ
σʔλΛ͏·͘ύʔεͰ͖ͳ͔ͬͨ
ଞʹ΋೔ຊޠͷΧϥϜ໊ͩͱύʔςΟγϣϯ͕੾Εͳ͍ࣄ৅Λ֬ೝ
$47ͷจࣈίʔυ͕"UIFOBͰѻ͑ͳ͍4+*4ͩͬͨΓ͢Δ
$47ੜσʔλΛͦͷ··+0*/͢ΔͷͰ͸ͳ͘ɺҰ౓+40/-JOFT
ʹม׵͢ΔͳͲͨ͠΄͏͕Αͦ͞͏
͜͏͍ͬͨࡉ͔͍ॲཧ͕Ͱ͖Δͷ΋-BNCEB'VODUJPOͷڧΈͷͻ
ͱͭ

View Slide

export async function sessionConverter(
stack: cdk.Construct,
global: GlobalProps,
): Promise {
const rawCsvBucket = new s3.Bucket(stack, 'Raw', {
bucketName: global.getBucketName('raw', cdk.Stack.of(stack).account),
cors: [
{
allowedHeaders: ['*'],
allowedMethods: [HttpMethods.PUT],
allowedOrigins: ['*'],
},
],
});
const jsonLinesBucket = new s3.Bucket(stack, 'JsonLines', {
bucketName: global.getBucketName(
'json-lines',
cdk.Stack.of(stack).account,
),
});
const convertSessionCsvToJsonLinesFn = new lambda.Function(
stack,
'ConvertSessionCsvToJsonLines',
{
functionName: global.getFunctionName('ConvertSessionCsvToJsonLines'),
code: lambda.Code.fromAsset(NODE_LAMBDA_SRC_DIR),
handler:
'lambda/handlers/sf/csv-converter/convert-session-csv-to-json-handler.handler',
runtime: lambda.Runtime.NODEJS_12_X,
layers: [nodeModulesLayer],
timeout: Duration.minutes(15),
memorySize: 256,
environment: {
REGION: global.pm.region,
CSV_BUCKET_NAME: rawCsvBucket.bucketName,
JSON_LINES_BUCKET_NAME: jsonLinesBucket.bucketName,
},
},
);
rawCsvBucket.grantRead(convertSessionCsvToJsonLinesFn);
jsonLinesBucket.grantReadWrite(convertSessionCsvToJsonLinesFn);

View Slide

): Promise {
cors: [
{
},
],
});
'json-lines',
),
});
stack,
{
handler:
memorySize: 256,
environment: {
},
},
);
w $47ੜσʔλΛ౤ೖ͢Δ4
όέοτ
w QSFTJHOFEVSM͔ΒΞοϓ
ϩʔυͯ͠΋Β͏ͷͰ$034
ઃఆ

View Slide

): Promise {
cors: [
{
},
],
});
'json-lines',
),
});
stack,
{
handler:
memorySize: 256,
environment: {
},
},
);
w +40/-JOFTΛอଘ͓ͯ͘͠
όέοτ

View Slide

): Promise {
cors: [
{
},
],
});
'json-lines',
),
});
stack,
{
handler:
memorySize: 256,
environment: {
},
},
);
w ੜσʔλόέοτ͔Βϩʔυ
͠ɺม׵͠ɺ+40/-JOFTό
έοτʹ౤ೖ͢Δ-BNCEB
'VODUJPO

View Slide

$%,αϯϓϧɿ(MVF5BCMF"NB[PO"UIFOB

View Slide

$%,αϯϓϧɿ(MVF5BCMF"NB[PO"UIFOB
ɾ4σʔλιʔε(MVF5BCMF
ɾ-BNCEB'VODUJPO"NB[PO"UIFOB

View Slide

$%,αϯϓϧɿ4(MVF5BCMF
export async function eventGlueTableResource(
s3Resource: SlsGlueTableResourceInput,
): Promise {
const slsVirtualDataBase = new glue.Database(stack, 'SlsVirtualDatabase', {
databaseName: global.getGlueDatabaseName('slsVirtual_application'),
});
const sessionGlueTable = new glue.Table(stack, 'SessionTable', {
database: slsVirtualDataBase,
tableName: 'session',
columns: [
{
name: 'sessionId',
type: glue.Schema.STRING,
},
{
name: 'email',
},
{
name: 'title',
},
{
name: 'description',
},
{
name: 'organizationName',
},
{
name: 'name',
},
],
dataFormat: glue.DataFormat.JSON,
bucket: s3Resource.jsonLinesBucket,
s3Prefix: 'session/',
partitionKeys: [
{
name: 'eventId',
},
{
name: 'versionId',
},
],
});

View Slide

): Promise {
});
columns: [
{
name: 'sessionId',
},
{
name: 'email',
},
{
name: 'title',
},
{
},
{
},
{
name: 'name',
},
],
partitionKeys: [
{
name: 'eventId',
},
{
name: 'versionId',
},
],
});
w (MVF%BUBCBTFͱ(MVF
5BCMFΛ࡞੒

View Slide

): Promise {
});
columns: [
{
name: 'sessionId',
},
{
name: 'email',
},
{
name: 'title',
},
{
},
{
},
{
name: 'name',
},
],
partitionKeys: [
{
name: 'eventId',
},
{
name: 'versionId',
},
],
});
w σʔλιʔεͱͳΔ4ό
έοτΛࢦఆ

View Slide

const ticketGlueTable = new glue.Table(stack, 'TicketTable', {
tableName: 'ticket',
columns: [
{
name: 'email',
},
{
name: 'ticketId',
},
],
s3Prefix: 'ticket/',
partitionKeys: [
{
name: 'eventId',
},
{
name: 'versionId',
},
],
});
w νέοτ༻ͷ(MVF5BCMF΋
ಉ͡Α͏ʹ࡞੒

View Slide

const entryTable = new glue.Table(stack, 'EntryTable', {
tableName: 'entry',
columns: [
{
name: 'sessionId',
},
{
name: 'name',
},
{
name: 'title',
},
{
},
{
name: 'ticketId',
},
],
dataFormat: new glue.DataFormat({
inputFormat: glue.InputFormat.TEXT,
outputFormat: glue.OutputFormat.HIVE_IGNORE_KEY_TEXT,
serializationLibrary: glue.SerializationLibrary.HIVE_JSON,
}),
bucket: s3Resource.entryBucket,
s3Prefix: 'entry/',
partitionKeys: [
{
name: 'eventId',
},
],
w ࠷ޙʹɺ"UIFOBͰ+0*/͠
ͨσʔλΛ౤ೖ͢Δ༻ͷ
(MVF5BCMF΋༻ҙ͓ͯ͘͠

View Slide

+0*/͢Δ-BNCEB'VODUJPOͷίʔυ͸͜Μͳײ͡
export async function handler(
event: JoinRequest,
context?: LambdaContext,
): Promise {
const param: DocumentClient.GetItemInput = {
TableName: EventDynamodbTableName,
Key: {
id: event.eventId,
},
};
const res = await dynamo.get(param).promise();
const sessionVersionId = res.Item!.sessionJsonLinesInfo.versionId;
const ticketVersionId = res.Item!.ticketJsonLinesInfo.versionId;
try {
const param = {
ResultConfiguration: {
OutputLocation: `s3://${AthenaOutputBucketName}/`,
},
QueryExecutionContext: {
Database: SlsVirtualGlueDataBaseName,
},
QueryString: [
`insert into ${EntryGlueTableName}`,
'select session.sessionId,',
'ticket.ticketId,',
'session.name,',
'session.organizationName,',
'session.title,',
'session.description,',
'ticket.email', // lower case for partition key
`from ${SessionGlueTableName} session inner join ${TicketGlueTableName} ticket on session.email = ticket.email`,
`where session.versionId = '${sessionVersionId}' and ticket.versionId = '${ticketVersionId}'`,
].join(' '),
};
console.log('insert into param', param);
const execution = await athena.startQueryExecution(param).promise();
console.log(execution);
return {
executionId: execution.QueryExecutionId!,
};
} catch (e) {
console.log(e);
throw e;
}
}

View Slide

event: JoinRequest,
): Promise {
Key: {
id: event.eventId,
},
};
try {
const param = {
},
},
QueryString: [
'ticket.ticketId,',
'session.name,',
'session.title,',
].join(' '),
};
return {
};
} catch (e) {
console.log(e);
throw e;
}
}
-BNCEB'VODUJPOͷίʔυ͸͜Μͳײ͡
w ϝΞυͰ+0*/
w +0*/ޙͷσʔλΛ&OUSZ
ςʔϒϧʹ౤ೖ

View Slide

event: JoinRequest,
): Promise {
Key: {
id: event.eventId,
},
};
try {
const param = {
},
},
QueryString: [
'ticket.ticketId,',
'session.name,',
'session.title,',
].join(' '),
};
return {
};
} catch (e) {
console.log(e);
throw e;
}
}
-BNCEB'VODUJPOͷίʔυ͸͜Μͳײ͡
w "UIFOBΫΤϦΛ࣮ߦ

View Slide

ίʔυαϯϓϧɿ44FMFDU

View Slide

async function createS3SelectStream(
request: EntryRequest,
expression: string,
): Promise> {
const source$ = new Subject();
const content = await s3
.selectObjectContent({
Bucket: request.bucket,
Key: request.key,
InputSerialization: {
JSON: { Type: 'LINES' },
CompressionType: 'GZIP',
},
OutputSerialization: { JSON: { RecordDelimiter: '\n' } },
Expression: expression,
ExpressionType: 'SQL',
})
.promise();
const contentStream: any = content.Payload!;
const filterRecordsOnly = new stream.Transform({
objectMode: true,
transform(
chunk: any,
encoding: string,
done: stream.TransformCallback,
): void {
if (chunk.Records) {
this.push(chunk.Records.Payload);
} else if (chunk.Stats) {
console.log(
`Processed ${chunk.Stats.Details.BytesProcessed} bytes`,
);
} else if (chunk.End) {
console.log('SelectObjectContent completed');
}
done();
},
});
const readLine = readline.createInterface(
contentStream.pipe(filterRecordsOnly),
);
readLine
.on('line', (line: any) => {
// console.log('line', line);
source$.next(JSON.parse(line) as T);
})
.on('close', () => {
console.log('end');
source$.complete();
44FMFDUͷ-BNCEB'VODUJPOίʔυ

View Slide

expression: string,
): Promise> {
Key: request.key,
},
})
.promise();
objectMode: true,
transform(
chunk: any,
encoding: string,
): void {
console.log(
);
}
done();
},
});
);
readLine
})
.on('close', () => {
console.log('end');
source$.complete();
w TFMFDUGSPNTPCKFDUPCKXIFSFPCKUJDLFUJE
b`

View Slide

expression: string,
): Promise> {
Key: request.key,
},
})
.promise();
objectMode: true,
transform(
chunk: any,
encoding: string,
): void {
console.log(
);
}
done();
},
});
);
readLine
})
.on('close', () => {
console.log('end');
source$.complete();
w 44FMFDU͸݁ՌΛಠࣗͷ/PEFKTετϦʔϜͰฦ͢ͷͰ
͕Μ͹ͬͯऔಘ͍ͯ͠Δ༷ࢠ

View Slide

ίʔυαϯϓϧͷ·ͱΊ
-BNCEB'VODUJPOΛ࢖ͬͨ&5-Ͱ"84$%,Λ࢖
͍૊ΈཱͯΔαϯϓϧΛࣔͨ͠
"84ߏ੒ਤ͔Β$%,ίʔυʹམͱͨ͢Ίʹ͸ɺ֤αʔϏεͷ
࣮ମΛ஌Δ͜ͱ͕ۙಓ
ཁ݅ʹΑͬͯϚον͢ΔαʔϏε͸ҟͳΔɻ߹க͢ΔαʔϏε
͕ͳʹ͔ɺࣾ಺ͰɺαʔόʔϨείϛϡχςΟͰٞ࿦͠·͠ΐ
͏

View Slide

·ͱΊ
ϑΝΠϧΛ+0*/͢ΔϢʔεέʔεʹରͯ͠ɺαʔόʔϨ
εͰϥΠτͳ&5-Λߦͬͨ
ෳ਺ͷαʔϏεͷσʔλΛ૊Έ߹Θͤͯɺͻͱͭͷ݁ՌΛಘ
Δͱ͍͏γνϡΤʔγϣϯʹૺ۰ͨ͠ͱ͖ʹɺࠓճͷΑ͏ʹ
͍ͬͨΜ4ϑΝΠϧ΁ग़ྗɺͦͷޙ"UIFOBͰ+0*/ͯ͠4
4FMFDUͰΫΤϦ͢Δͱ͍͏ྲྀΕ͕࢖͑Δ͜ͱΛࢥ͍ग़ͯ͠΋Β
͑Ε͹޾͍Ͱ͢

View Slide

ࢀߟॻ੶ɾར༻ͨ͠πʔϧ
w DODGXHTFSWFSMFTT$/$'4FSWFSMFTT8(IUUQTHJUIVCDPNDODG
XHTFSWFSMFTT
w "84$%,IUUQTHJUIVCDPNBXTBXTDEL
w DNXBEBZVTVLFUTBTDELIUUQTHJUIVCDPNDNXBEBZVTVLF
UTBTDEL

View Slide

Step Functions と Lambda Function で組む Athena によるファイル処理 / serverless lightweight etl

Step Functions と Lambda Function で組む Athena によるファイル処理 / serverless lightweight etl

Yusuke Wada

More Decks by Yusuke Wada

Other Decks in Technology

Featured

Transcript