Skip to content

Instantly share code, notes, and snippets.

@littleq0903
Forked from soundTricker/bigquery.js
Created January 19, 2014 15:34
Show Gist options
  • Save littleq0903/8506441 to your computer and use it in GitHub Desktop.
Save littleq0903/8506441 to your computer and use it in GitHub Desktop.
var projectId = ScriptProperties.getProperty("projectId");
function プロジェクトの取得() {
var list = BigQuery.Projects.list();
Logger.log(list);
}
function データセット作成_更新_削除() {
//削除
// BigQuery.Datasets.remove(projectId, "test");
//作成
var dataset = BigQuery.newDataset().setDescription("disp");
dataset
.setAccess(
[
BigQuery.newDatasetAccess()
.setUserByEmail(Session.getActiveUser().getEmail())
]
)
.setDatasetReference(
BigQuery.newDatasetReference()
.setDatasetId("test").setProjectId(projectId)
);
try {
dataset = BigQuery.Datasets.insert(dataset);
} catch(e) {
}
//取得
var dataset2 = BigQuery.Datasets.get(projectId, "test");
Logger.log(dataset2.getAccess());
//更新
var access = dataset2.getAccess();
access.push(
BigQuery.newDatasetAccess()
.setDomain(ScriptProperties.getProperty("domain")).setRole("READ")
);
dataset2
.setAccess(access)
.setDatasetReference(
BigQuery.newDatasetReference()
.setDatasetId("test")
.setProjectId(projectId)
)
.setDescription("dddd");
BigQuery.Datasets.update(dataset2);
Logger.log(dataset2);
//取得
dataset = BigQuery.Datasets.get(projectId, "test");
Logger.log(dataset);
try {
スキーマの作成_更新_削除();
} catch(e) {
Logger.log(e.message);
}
//削除
BigQuery.Datasets.remove(projectId, "test");
}
function スキーマの作成_更新_削除() {
//取得
try {
var t = BigQuery.Tables.get(projectId, "test", "w");
BigQuery.Tables.remove(projectId, "test", "w");
} catch(e) {
if(e.message.indexOf("Not Found") < 0) {
throw e;
}
}
//作成
var fileds = [];
//fileds.push({"name": "test" , "type": "STRING"}); //BigQuery.newTableFieldSchema()で作ると出来ない 多分json可する所がバグってる?
fileds.push(BigQuery.newTableFieldSchema().setName("test").setType("string"));//BigQuery.newTableFieldSchema()で作る場合はjsonParseする
var table = BigQuery.newTable()
.setId("w")
.setFriendlyName("w")
.setTableReference(
BigQuery.newTableReference()
.setDatasetId("test")
.setProjectId(projectId)
.setTableId("w")
)
.setSchema(
BigQuery.newTableSchema()
.setFields(
[
Utilities.jsonParse(BigQuery.newTableFieldSchema().setName("test").setType("string"))
]
)
);
Logger.log(table);
table = BigQuery.Tables.insert(table);
//取得
var t = BigQuery.Tables.get(projectId, "test", "w");
//更新
t.setDescription("descs");
var t = BigQuery.Tables.update(t);
try {
データのインサート_by_csvアップロード();
} catch(e) {
Logger.log(e.message);
}
//削除
//BigQuery.Tables.remove(projectId, "test", "w");
}
function データのインサート_by_csvアップロード() {
var oauth = UrlFetchApp.addOAuthService("bigQuery");
oauth.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fbigquery");
oauth.setConsumerKey("anonymous");
oauth.setConsumerSecret("anonymous");
oauth.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
oauth.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
var data =
'--xxx\n' +
'Content-Type: application/json; charset=UTF-8\n' + '\n';
var wikipediaTable = BigQuery.Tables.get(projectId, "test", "w");
Logger.log(wikipediaTable);
var loadJob = BigQuery.newJob()
.setConfiguration(
BigQuery.newJobConfiguration()
.setLoad(
BigQuery.newJobConfigurationLoad()
.setSchema(wikipediaTable.getSchema())
.setDestinationTable(wikipediaTable.getTableReference())
)
);
data += loadJob;
data += "\n--xxx\n" + 'Content-Type: application/octet-stream\n\n';
data += 't\n';
data += '--xxx--\n';
var option = {
contentType : 'multipart/related; boundary=xxx',
method : "post",
payload : data,
oAuthServiceName : "bigQuery",
oAuthUseToken : "always"
};
var resp = UrlFetchApp.fetch("https://www.googleapis.com/upload/bigquery/v2/projects/" + projectId +"/jobs?key=" + ScriptProperties.getProperty("key"),option);
Logger.log(resp.getContentText());
}
function データの操作_直() { with(BigQuery) {
var result = Jobs.query(projectId, "SELECT test FROM [test.w]");
Logger.log(result);
}}
function データの操作_非同期() {
var queryString = "SELECT repository_language , count(repository_url) c FROM [publicdata:samples.github_timeline] where repository_language != '' 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(BigQuery.newTableReference().setDatasetId("test").setTableId("new").setProjectId(projectId))
.setWriteDisposition("WRITE_TRUNCATE")
)
);
Logger.log(job);
var result = BigQuery.Jobs.insert(Utilities.jsonParse(job));
Logger.log(result);
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 {message : "なんかエラーだよ"};
}
Logger.log(++count);
Utilities.sleep(1000);
}
var queryResult = BigQuery.Jobs.getQueryResults(projectId, insertedJob.getJobReference().getJobId());
Logger.log(queryResult);
var list = [];
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().getSheetByName("result");
sheet.getRange(2, 1,list.length , 2).setValues(list);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment