はじめての 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. 2.

    Who am I ຊଟɹҰߦʢ΄Μͩɹ͔ͣΏ͖ʣ Twitter: @hakobera Work At: Quipper, Ltd

    ! ࠷ۙ͸ΠϯϑϥͬΆ͍͜ͱΛ΍ͬͯ·͕͢ɺ ຊۀ͸WebΞϓϦ։ൃऀ ࠷ۙ BigQuery Analytics ಡॻձΛओ࠵ͯ͠·͢
  2. 3.

    ࠓ೔͸ BQ GAS ͷ࿩ BQ = BigQuery ͷσʔλΛɺ GAS =

    Google Apps Script Λ࢖ͬͯͪΐͬͱศརʹѻ͏
  3. 5.

    Google Apps Script Google Docs ͷ؆қϚΫϩʢͩͬͨʣ ݴޠ͸ JavaScript (window ΦϒδΣΫτ͸ͳ͍ʣ

    Google Doc Ҏ֎ͷ Google ֤छαʔϏε࿈ܞ͕Ͱ͖Δ ࠷ۙɺGoogle Docs ͷ Addons ͕࡞ΕΔΑ͏ʹͳͬͨ ཁ͢Δʹ Google Doc ͷ VBA
  4. 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
  5. 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
  6. 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
  7. 24.
  8. 28.

    RDB ࿈ܞ • GAS ʹ JDBC Ϟδϡʔϧ͕͋ΓɺJDBC ઀ଓͰ ͖Δ RDB

    ͱ࿈ܞՄೳ • ઃఆ͕໘౗͍͘͞ͷ͕೉఺͕ͩɺCloudSQL Λ ࢖͏ͱଟগศར • RDB ʹೖΕ͓͚ͯ͹ޙͷ࿈ܞָ͕ͷ৔໘΋͋Δ
  9. 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
  10. 31.

    BQ, GAS ͷμϝͩͱࢥ͏ॴ • BQ ͷ؅ཧίϯιʔϧ͕ϚϧνΞΧ΢ϯτʹରԠ͍ͯ͠ͳ͍ • ࢓༷͕ಥવมΘͬͨΓɺಥવΤϥʔ͕ฦͬͯ͘ΔΑ͏ʹͳΔ ͜ͱ͕͋Δ •

    Google Docs ͷݖݶ΍όʔδϣϯ؅ཧ (git ࿈ܞ͍ͨ͠ʣ • ॲཧ଎౓͸ Google ͷؾ෼࣍ୈ ʹ ࣌ؒͷݟੵ΋Γ͕೉͍͠ • υΩϡϝϯτ͕গͳ͍ɻ೔ຊޠͷυΩϡϝϯτ͸جຊతʹ ৴༻Ͱ͖ͳ͍ʢݹ͍͜ͱ͕ଟ͍ʣ
  11. 33.
  12. 35.

    GAS ʹ͸2छྨ͋Δ ௒ਪ঑هࣄ: Google Apps Scriptͷ։ൃख๏·ͱΊ http://qiita.com/soundTricker/items/4d04c97c499b22886dfd • Container Bound

    Script • Spreadsheet΍SitesɺFormͳͲͷϝχϡʔ͔ΒʮεΫϦϓτΤ σΟλʯΛىಈͯ͠࡞੒͢ΔεΫϦϓτ • Standalone Script • Drive্ʹ௚઀ͭ͘ΔεΫϦϓτ • ؅ཧ͠΍͍͢ͷۀ຿Ͱ࢖͏ͳΒͪ͜Β͕Φεεϝ