Skip to content

Instantly share code, notes, and snippets.

@soundTricker
Created June 22, 2012 12:31
Show Gist options
  • Save soundTricker/2972480 to your computer and use it in GitHub Desktop.
Save soundTricker/2972480 to your computer and use it in GitHub Desktop.
#ajn21のスクリプト
function BigQueryLiveDemo() {
//6219749
var list = BigQuery.Projects.list();
Logger.log(list);
//プロジェクトIDの取得
var projectId = list.getProjects()[0].getId();
//Datasetsのインスタンス作成
var datasets = BigQuery
.newDataset()
.setDescription("ajnのデモ")
.setDatasetReference(
BigQuery.newDatasetReference()
.setDatasetId("ajn").setProjectId(projectId)
);
//インサート
datasets = BigQuery.Datasets.insert(datasets);
//テーブル作成
var table = BigQuery
.newTable()
.setTableReference(
BigQuery
.newTableReference()
.setDatasetId(datasets.getDatasetReference().getDatasetId())
.setProjectId(projectId)
.setTableId("githubranking")
)
.setFriendlyName("github-ranking")
.setSchema(
BigQuery
.newTableSchema()
.setFields(
[
Utilities.jsonParse(
BigQuery
.newTableFieldSchema()
.setType("string")
.setName("lang")
),
Utilities.jsonParse(
BigQuery
.newTableFieldSchema()
.setType("integer")
.setName("count")
)
]
)
);
//インサート
table = BigQuery.Tables.insert(table);
var queryString = "SELECT repository_language , count(repository_url) c FROM [publicdata:samples.github_timeline] group by repository_language order by c desc";
var job =
BigQuery
.newJob()
.setJobReference(
BigQuery
.newJobReference()
.setProjectId(projectId)
)
.setConfiguration(
BigQuery
.newJobConfiguration()
.setQuery(
BigQuery
.newJobConfigurationQuery()
.setQuery(queryString)
.setDestinationTable(table.getTableReference())
.setWriteDisposition("WRITE_TRUNCATE")
)
);
var result = BigQuery.Jobs.insert(job);
var count = 0;
while(true) {
var insertedJob = BigQuery.Jobs.get(projectId, result.getJobReference().getJobId());
if(insertedJob.getStatus().getState() == "DONE") {
break;
}
if(insertedJob.getStatus().getErrors() != null && insertedJob.getStatus().getErrors().length > 0) {
Logger.log(insertedJob);
throw new Error("なんかエラーだよ");
}
if(count > 1000) {
throw new Error("タイムアウト");
}
Logger.log(++count);
Utilities.sleep(1000);
}
var queryResult = BigQuery.Jobs.getQueryResults(projectId, insertedJob.getJobReference().getJobId());
// var queryResult = Jobs.query(projectId,queryString);
var list = [];
//2次元配列へ
for(var i = 0;i < queryResult.getRows().length; i++) {
var row = queryResult.getRows()[i];
list.push([
row.getF()[0].getV(),
row.getF()[1].getV()
]);
}
var sheet = SpreadsheetApp.getActive().insertSheet();
sheet.getRange(1,1,1,2).setValue([["Lang" , "Count"]]);
sheet.getRange(2, 1,list.length , 2).setValues(list);
//チャートの作成
var chart = sheet.newChart()
.addRange(sheet.getDataRange())
.setChartType(Charts.ChartType.BAR)
.setPosition(1, 1, 0, 0)
.build();
sheet.insertChart(chart);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment