はじめての BQ GAS

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

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

Transcript

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

   View Slide

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

   View Slide

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

   View Slide

4. Google Apps Script ?
   ͜͜ʹ஌Βͳ͍ਓ͸͍ͳ͍ͱ͸ࢥ͍·͕͢ɾɾɾ
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

9. BigQuery ΋ GAS ΋
   Managed Service Ͱ͋Δ
   ͭ·Γ
   No Own Server!
   

   View Slide

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

    View Slide

11. Կ͕Ͱ͖Δͷʁ
    

    View Slide

12. ೔࣍ϨϙʔτΛ࡞੒ͯ͠
    ϝʔϧʹఴ෇
    

    View Slide

13. কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠
    (PPHMF"QQT4DSJQU
    (PPHMF
    4QSFBETIFFU
    .BJM
    ௅ઓத
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

20. ৭ʑͱ૊Έ߹ΘͤΔͱ
    

    View Slide

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

    View Slide

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

    View Slide

23. One more thing…
    

    View Slide

24. ஫໨ʂ
    

    View Slide

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

    View Slide

26. কདྷతʹ͸͜Ε΋ͳ͍ͨ͘͠
    (PPHMF"QQT4DSJQU
    (PPHMF
    4QSFBETIFFU
    .BJM
    ௅ઓத
    

    View Slide

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

    View Slide

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

    View Slide

29. 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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

33. ͓͠·͍
    

    View Slide

34. ิ଍ࢿྉ
    

    View Slide

35. GAS ʹ͸2छྨ͋Δ
    ௒ਪ঑هࣄ: Google Apps Scriptͷ։ൃख๏·ͱΊ
    http://qiita.com/soundTricker/items/4d04c97c499b22886dfd
    • Container Bound Script
    • Spreadsheet΍SitesɺFormͳͲͷϝχϡʔ͔ΒʮεΫϦϓτΤ
    σΟλʯΛىಈͯ͠࡞੒͢ΔεΫϦϓτ
    • Standalone Script
    • Drive্ʹ௚઀ͭ͘ΔεΫϦϓτ
    • ؅ཧ͠΍͍͢ͷۀ຿Ͱ࢖͏ͳΒͪ͜Β͕Φεεϝ
    

    View Slide

36. ͍ɺ͍ͳ͍ɾɾɾ
    Standalone Script ͸ඪ४ͰΠϯετʔϧ͞Εͯͳ͍ʂ
    

    View Slide

37. ʮTDSJQUʯͰݕࡧ
    ʮ઀ଓʯΛΫϦοΫ
    

    View Slide

38. Ͱͨʂ
    Enjoy Standalone Script!
    

    View Slide