はじめての BQ GAS - Speaker Deck

はじめての BQ GAS

by Kazuyuki Honda

Slide 1

Slide 1 text

͸͡Ίͯͷ BQ GAS 2014/09/16 gcp ja night #28 ! @hakobera

Slide 2

Slide 2 text

Who am I ຊଟɹҰߦʢ΄Μͩɹ͔ͣΏ͖ʣ Twitter: @hakobera Work At: Quipper, Ltd ! ࠷ۙ͸ΠϯϑϥͬΆ͍͜ͱΛ΍ͬͯ·͕͢ɺ ຊۀ͸WebΞϓϦ։ൃऀ ࠷ۙ BigQuery Analytics ಡॻձΛओ࠵ͯ͠·͢

Slide 3

Slide 3 text

ࠓ೔͸ BQ GAS ͷ࿩ BQ = BigQuery ͷσʔλΛɺ GAS = Google Apps Script Λ࢖ͬͯͪΐͬͱศརʹѻ͏

Slide 4

Slide 4 text

Google Apps Script ? ͜͜ʹ஌Βͳ͍ਓ͸͍ͳ͍ͱ͸ࢥ͍·͕͢ɾɾɾ

Slide 5

Slide 5 text

Google Apps Script Google Docs ͷ؆қϚΫϩʢͩͬͨʣ ݴޠ͸ JavaScript (window ΦϒδΣΫτ͸ͳ͍ʣ Google Doc Ҏ֎ͷ Google ֤छαʔϏε࿈ܞ͕Ͱ͖Δ ࠷ۙɺGoogle Docs ͷ Addons ͕࡞ΕΔΑ͏ʹͳͬͨ ཁ͢Δʹ Google Doc ͷ VBA

Slide 6

Slide 6 text

BigQuery ଎ͯ҆ͯ͘͘SQLͬΆ͍ΫΤϦݴޠͰ ߏ଄ԽσʔλʹΞΫηεͰ͖Δ΍ͭ ! ৄࡉ͸ BigQuery Analytics ΛಡΜͰͶ དྷ೥ʢ2015೥ʣɺ೔ຊޠ༁΋ग़ΔΑ

Slide 7

Slide 7 text

BigQuery ͱ֎෦αʔϏεΛ GAS Ͱͭͳ͗͜Ήͱศར = BQ GAS

Slide 8

Slide 8 text

ͦͷલʹେલఏ αʔό͸Ͱ͖Δ͚ͩ ؅ཧͨ͘͠ͳ͍ओٛͰ͢ʂ

Slide 9

Slide 9 text

BigQuery ΋ GAS ΋ Managed Service Ͱ͋Δ ͭ·Γ No Own Server!

Slide 10

Slide 10 text

͍ͭͰʹݴ͏ͱ GAS͔Βͩͱͱʹ͔͘ ೝূָ͕ͩ ("4Λ࡞ͬͨϢʔβʹ #JH2VFSZ΁ΞΫηεݖݶΛ௥Ճ͢Δ͚ͩ

Slide 11

Slide 11 text

Կ͕Ͱ͖Δͷʁ

Slide 12

Slide 12 text

೔࣍ϨϙʔτΛ࡞੒ͯ͠ ϝʔϧʹఴ෇

Slide 13

Slide 13 text

কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF"QQT4DSJQU (PPHMF 4QSFBETIFFU .BJM ௅ઓத

Slide 14

Slide 14 text

೔࣍ϨϙʔτΛϝʔϧʹఴ෇ • Cron తʹ࢖͑ΔτϦΨʔ͕͋Δ ! ! • BigQuery ͷूܭ݁ՌΛಛఆγʔτʹग़ྗ͠ɺͦΕҎ ߱ͷूܭʗՄࢹԽ͸ Excel/SpreadSheet ʹ೚ͤΔ • Spreadsheet Λ PDF ʹม׵ͯ͠ఴ෇Ͱ͖Δ

Slide 15

Slide 15 text

ॏཁϙΠϯτ ඞཁҎ্ʹBigQuery, GAS Ͱ ूܭ͠ͳ͍͜ͱ Excel Ͱѻ͑Δσʔλྔ·Ͱམͱ͜͠Ήͷ͕ ΤϯδχΞͷ͓࢓ࣄͰ͢

Slide 16

Slide 16 text

BigQuery ͷΫΤϦΛ࣮ߦ function runQuery(projectId, query) { var request = { query: query + ';' }; var queryResults = BigQuery.Jobs.query(request, projectId); var jobId = queryResults.jobReference.jobId; var sleepTime = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTime); sleepTime *= 2; queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId); } return queryResults; } IUUQTHJTUHJUIVCDPNIBLPCFSBCFCBF

Slide 17

Slide 17 text

BigQuery ͷΫΤϦ࣮ߦ݁ՌΛ Spreadsheet ʹอଘ function saveQueryResults(queryResults, sheet) { var headers = queryResults.schema.fields.map(function(field) { return field.name; }); sheet.appendRow(headers); var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } sheet.getRange(2, 1, rows.length, headers.length).setValues(data); } IUUQTHJTUHJUIVCDPNIBLPCFSBCFCBF

Slide 18

Slide 18 text

Spreadsheet Λϝʔϧʹఴ෇ function sendMail(recipients, subject, doc) { var attachment = doc.getAs('application/pdf'); var body = doc.getUrl(); MailApp.sendEmail(recipients, subject, body, { noReply: true, attachments: attachment }); } ! var report = createDailyReport(); sendMail(‘[email protected]’, ‘Daily Report’, report); IUUQTHJTUHJUIVCDPNIBLPCFSBCFCBF

Slide 19

Slide 19 text

(PPHMF"QQTͩͱ%PDTͷ63-ΛຊจʹؚΊ͓ͯ͘ͱఴ෇ѻ͍ʹ΋ͳΔ

Slide 20

Slide 20 text

৭ʑͱ૊Έ߹ΘͤΔͱ

Slide 21

Slide 21 text

೔࣍Ϩϙʔτఆٛγʔτʂ

Slide 22

Slide 22 text

ΤϯδχΞରԠͳ͠ʹ ೔࣍όονΛ૊ΊΔ

Slide 23

Slide 23 text

One more thing…

Slide 24

Slide 24 text

஫໨ʂ

Slide 25

Slide 25 text

ϩάղੳ݁ՌΛRDBʹ֨ೲ ͱ͔΋Ͱ͖ͪΌ͏ʂ

Slide 26

Slide 26 text

কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF"QQT4DSJQU (PPHMF 4QSFBETIFFU .BJM ௅ઓத

Slide 27

Slide 27 text

কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF"QQT4DSJQU (PPHMF 4QSFBETIFFU .BJM %BTICPBSE &YDFMͳͲ ௅ઓத

Slide 28

Slide 28 text

RDB ࿈ܞ • GAS ʹ JDBC Ϟδϡʔϧ͕͋ΓɺJDBC ઀ଓͰ ͖Δ RDB ͱ࿈ܞՄೳ • ઃఆ͕໘౗͍͘͞ͷ͕೉఺͕ͩɺCloudSQL Λ ࢖͏ͱଟগศར • RDB ʹೖΕ͓͚ͯ͹ޙͷ࿈ܞָ͕ͷ৔໘΋͋Δ

Slide 29

Slide 29 text

RDB ࿈ܞ: ίʔυྫ var conn = Jdbc.getConnection( “jdbc:mysql://:/", "user", "password"); ! conn.setAutoCommit(false); var stmt = conn.prepareStatement( "insert into person (lname,fname) values (?,?)"); var start = new Date(); for (var i = 0; i < 5000; i++) { stmt.setObject(1, 'firstName' + i); stmt.setObject(2, 'lastName' + i); stmt.addBatch(); } var res = stmt.executeBatch(); conn.commit(); ! conn.close(); IUUQTEFWFMPQFSTHPPHMFDPNBQQTTDSJQUSFGFSFODFKECDKECDDPOOFDUJPO

Slide 30

Slide 30 text

ʢେࣄͳ͜ͱͳͷͰ΋͏Ұ౓ʣ BigQuery ΋ GAS ΋ Managed Service Ͱ͋Δ ͭ·Γ No Own Server!

Slide 31

Slide 31 text

BQ, GAS ͷμϝͩͱࢥ͏ॴ • BQ ͷ؅ཧίϯιʔϧ͕ϚϧνΞΧ΢ϯτʹରԠ͍ͯ͠ͳ͍ • ࢓༷͕ಥવมΘͬͨΓɺಥવΤϥʔ͕ฦͬͯ͘ΔΑ͏ʹͳΔ ͜ͱ͕͋Δ • Google Docs ͷݖݶ΍όʔδϣϯ؅ཧ (git ࿈ܞ͍ͨ͠ʣ • ॲཧ଎౓͸ Google ͷؾ෼࣍ୈ ʹ ࣌ؒͷݟੵ΋Γ͕೉͍͠ • υΩϡϝϯτ͕গͳ͍ɻ೔ຊޠͷυΩϡϝϯτ͸جຊతʹ ৴༻Ͱ͖ͳ͍ʢݹ͍͜ͱ͕ଟ͍ʣ

Slide 32

Slide 32 text