はじめての BQ GAS

gcpja night #28 での発表資料です。

サンプルコードはこちら:
https://gist.github.com/hakobera/537b35971e3b698ae083

Transcript

1. ͸͡Ίͯͷ BQ GAS 2014/09/16 gcp ja night #28 ! @hakobera
2. Who am I ຊଟɹҰߦʢ΄Μͩɹ͔ͣΏ͖ʣ Twitter: @hakobera Work At: Quipper, Ltd ! ࠷ۙ͸ΠϯϑϥͬΆ͍͜ͱΛ΍ͬͯ·͕͢ɺ ຊۀ͸WebΞϓϦ։ൃऀ ࠷ۙ BigQuery Analytics ಡॻձΛओ࠵ͯ͠·͢
3. ࠓ೔͸ BQ GAS ͷ࿩ BQ = BigQuery ͷσʔλΛɺ GAS = Google Apps Script Λ࢖ͬͯͪΐͬͱศརʹѻ͏
4. Google Apps Script ? ͜͜ʹ஌Βͳ͍ਓ͸͍ͳ͍ͱ͸ࢥ͍·͕͢ɾɾɾ
5. Google Apps Script Google Docs ͷ؆қϚΫϩʢͩͬͨʣ ݴޠ͸ JavaScript (window ΦϒδΣΫτ͸ͳ͍ʣ Google Doc Ҏ֎ͷ Google ֤छαʔϏε࿈ܞ͕Ͱ͖Δ ࠷ۙɺGoogle Docs ͷ Addons ͕࡞ΕΔΑ͏ʹͳͬͨ ཁ͢Δʹ Google Doc ͷ VBA
6. BigQuery ଎ͯ҆ͯ͘͘SQLͬΆ͍ΫΤϦݴޠͰ ߏ଄ԽσʔλʹΞΫηεͰ͖Δ΍ͭ ! ৄࡉ͸ BigQuery Analytics ΛಡΜͰͶ དྷ೥ʢ2015೥ʣɺ೔ຊޠ༁΋ग़ΔΑ
7. BigQuery ͱ֎෦αʔϏεΛ GAS Ͱͭͳ͗͜Ήͱศར = BQ GAS
8. ͦͷલʹେલఏ αʔό͸Ͱ͖Δ͚ͩ ؅ཧͨ͘͠ͳ͍ओٛͰ͢ʂ
9. BigQuery ΋ GAS ΋ Managed Service Ͱ͋Δ ͭ·Γ No Own Server!
10. ͍ͭͰʹݴ͏ͱ GAS͔Βͩͱͱʹ͔͘ ೝূָ͕ͩ ("4Λ࡞ͬͨϢʔβʹ
    #JH2VFSZ΁ΞΫηεݖݶΛ௥Ճ͢Δ͚ͩ
11. Կ͕Ͱ͖Δͷʁ
12. ೔࣍ϨϙʔτΛ࡞੒ͯ͠ ϝʔϧʹఴ෇
13. কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF
    "QQT
    4DSJQU (PPHMF
    4QSFBETIFFU .BJM ௅ઓத
14. ೔࣍ϨϙʔτΛϝʔϧʹఴ෇ • Cron తʹ࢖͑ΔτϦΨʔ͕͋Δ ! ! • BigQuery ͷूܭ݁ՌΛಛఆγʔτʹग़ྗ͠ɺͦΕҎ ߱ͷूܭʗՄࢹԽ͸ Excel/SpreadSheet ʹ೚ͤΔ • Spreadsheet Λ PDF ʹม׵ͯ͠ఴ෇Ͱ͖Δ
15. ॏཁϙΠϯτ ඞཁҎ্ʹBigQuery, GAS Ͱ ूܭ͠ͳ͍͜ͱ Excel Ͱѻ͑Δσʔλྔ·Ͱམͱ͜͠Ήͷ͕ ΤϯδχΞͷ͓࢓ࣄͰ͢
16. 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
17. 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
18. 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(‘staff@example.com’, ‘Daily Report’, report); IUUQTHJTUHJUIVCDPNIBLPCFSBCFCBF
19. (PPHMF
    "QQT
    ͩͱ
    %PDT
    ͷ
    63-
    ΛຊจʹؚΊ͓ͯ͘ͱఴ෇ѻ͍ʹ΋ͳΔ
20. ৭ʑͱ૊Έ߹ΘͤΔͱ
21. ೔࣍Ϩϙʔτఆٛγʔτʂ
22. ΤϯδχΞରԠͳ͠ʹ ೔࣍όονΛ૊ΊΔ
23. One more thing…
24. ஫໨ʂ
25. ϩάղੳ݁ՌΛRDBʹ֨ೲ ͱ͔΋Ͱ͖ͪΌ͏ʂ
26. কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF
    "QQT
    4DSJQU (PPHMF
    4QSFBETIFFU .BJM ௅ઓத
27. কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF
    "QQT
    4DSJQU (PPHMF
    4QSFBETIFFU .BJM %BTICPBSE
    &YDFM
    ͳͲ ௅ઓத
28. RDB ࿈ܞ • GAS ʹ JDBC Ϟδϡʔϧ͕͋ΓɺJDBC ઀ଓͰ ͖Δ RDB ͱ࿈ܞՄೳ • ઃఆ͕໘౗͍͘͞ͷ͕೉఺͕ͩɺCloudSQL Λ ࢖͏ͱଟগศར • RDB ʹೖΕ͓͚ͯ͹ޙͷ࿈ܞָ͕ͷ৔໘΋͋Δ
29. RDB ࿈ܞ: ίʔυྫ var conn = Jdbc.getConnection( “jdbc:mysql://<host>:<port>/<instance>", "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
30. ʢେࣄͳ͜ͱͳͷͰ΋͏Ұ౓ʣ BigQuery ΋ GAS ΋ Managed Service Ͱ͋Δ ͭ·Γ No Own Server!
31. BQ, GAS ͷμϝͩͱࢥ͏ॴ • BQ ͷ؅ཧίϯιʔϧ͕ϚϧνΞΧ΢ϯτʹରԠ͍ͯ͠ͳ͍ • ࢓༷͕ಥવมΘͬͨΓɺಥવΤϥʔ͕ฦͬͯ͘ΔΑ͏ʹͳΔ ͜ͱ͕͋Δ • Google Docs ͷݖݶ΍όʔδϣϯ؅ཧ (git ࿈ܞ͍ͨ͠ʣ • ॲཧ଎౓͸ Google ͷؾ෼࣍ୈ ʹ ࣌ؒͷݟੵ΋Γ͕೉͍͠ • υΩϡϝϯτ͕গͳ͍ɻ೔ຊޠͷυΩϡϝϯτ͸جຊతʹ ৴༻Ͱ͖ͳ͍ʢݹ͍͜ͱ͕ଟ͍ʣ
32. ʢେࣄͳ͜ͱͳͷͰ΋͏Ұ౓ʣ BigQuery ΋ GAS ΋ Managed Service Ͱ͋Δ ͭ·Γ No Own Server!
33. ͓͠·͍
34. ิ଍ࢿྉ
35. GAS ʹ͸2छྨ͋Δ ௒ਪ঑هࣄ: Google Apps Scriptͷ։ൃख๏·ͱΊ http://qiita.com/soundTricker/items/4d04c97c499b22886dfd • Container Bound Script • Spreadsheet΍SitesɺFormͳͲͷϝχϡʔ͔ΒʮεΫϦϓτΤ σΟλʯΛىಈͯ͠࡞੒͢ΔεΫϦϓτ • Standalone Script • Drive্ʹ௚઀ͭ͘ΔεΫϦϓτ • ؅ཧ͠΍͍͢ͷۀ຿Ͱ࢖͏ͳΒͪ͜Β͕Φεεϝ
36. ͍ɺ͍ͳ͍ɾɾɾ Standalone Script ͸ඪ४ͰΠϯετʔϧ͞Εͯͳ͍ʂ
37. ʮTDSJQUʯͰݕࡧ ʮ઀ଓʯΛΫϦοΫ
38. Ͱͨʂ Enjoy Standalone Script!