Save 37% off PRO during our Black Friday Sale! »

はじめての BQ GAS

7ebb44070077adbf77dececc7802e58b?s=47 Kazuyuki Honda
September 16, 2014

はじめての BQ GAS

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

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

7ebb44070077adbf77dececc7802e58b?s=128

Kazuyuki Honda

September 16, 2014
Tweet

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"QQT4DSJQU (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"QQT4DSJQU (PPHMF 4QSFBETIFFU .BJM ௅ઓத

  27. কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠ (PPHMF"QQT4DSJQU (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!