Upgrade to Pro — share decks privately, control downloads, hide ads and more …

はじめての BQ GAS

Kazuyuki Honda
September 16, 2014

はじめての BQ GAS

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

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

Kazuyuki Honda

September 16, 2014
Tweet

More Decks by Kazuyuki Honda

Other Decks in Technology

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(‘[email protected]’, ‘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