はじめに
BigQueryを扱う際、JSONに関する情報が少なく
非常に苦労したため、ノウハウを共有します。
尚、一部例示するコードはGASです。
またJSONはいわゆる一般的なオブジェクト配列を指します。
事前準備
検証に使うテーブルに、JSON型のカラムを用意してください。
また、サンプルコードを実行したい場合は、
GASのエディタを開いてBigQueryサービスを有効化しておいてください。
JSONカラムへのINSERT・UPDATE
まず、基本的なクエリの書き方はこんな感じです。
"UPDATE `projectId.datasetId.tableId`
SET カラム名 = JSON'ここにJSONstring'
WHERE id = '12345"
JSONstringに”JSON”を接頭する必要があります。
GASで作成する場合はシンプルにJSON.stringifyを使えばいいです。
"UPDATE `projectId.datasetId.tableId` " +
` SET カラム名 = JSON'${JSON.stringify(jsonData)}' WHERE id = '12345" `;
JSON形式でのloadジョブ登録
こちらは通常のクエリリクエストではなくloadジョブの方法となります。
例えばテーブルのカラムが「name(string),id(string),info(json)」だったとして、
以下のようなJSONを一気に取り込む事が可能です。
[
{
name:"山田太郎",
id:"0001",
info:[{
type:"parttime",
sex:"female",
tel:"111-1111-1111",
}]
},
{
name:"山田花子",
id:"0002",
info:[{
type:"fulltime",
sex:"male",
tel:"222-1111-1111",
}]
},
]
loadジョブのイメージは湧きましたでしょうか。
テーブルに初期データを一気に格納する際などに便利です。
さて、ではloadジョブの作成方法です。
上述のカラム構成とJSONを使うとして、以下のようなGASコードとなります。
このコードを実行するとJSONの内容が一括で登録されます。
尚、データ量の制約などもありますので、必要に応じて分割実行してください。
const json = 省略 上述のJSONがあるものとする;const schema = [
{name: "name", type: "STRING"},
{name: "id", type: "STRING"},
{name: "info", type: "STRING"}
];
const jobInfo = {
configuration: {
load: {
destinationTable: {
projectId: projectId,
datasetId: datasetId,
tableId: tableName
},
schema: {
fields: schema
},
sourceFormat: 'NEWLINE_DELIMITED_JSON'
}
}
};
const jsonl = json.map(x => JSON.stringify(x)).join('\n');
const blob = Utilities.newBlob(jsonl);
const job = BigQuery.Jobs.insert(jobInfo, dbSetting.projectId, blob);
最も重要なポイントは、JSONの加工ですね。
JSONLという改行区切JSON形式に変換しています。
ちなみにロードジョブの結果受取はこんな感じでいいかなと。
function getLoadJobResult(job){
const projectId = job.jobReference.projectId;
const jobId = job.jobReference.jobId;
let result = BigQuery.Jobs.get(projectId, jobId);
let count = 0;
while (result.status.state !== "DONE") {
console.log(result);
console.log("loadResponse待ち...")
if(count==10) throw(`GetLoadJobResult10回失敗${result}`);
Utilities.sleep(2000);
result = BigQuery.Jobs.get(projectId, jobId);
count++;
}
if(result.status.errors) console.log(result.status.errors);
return result;
}
前回記事で紹介したクラス内の待ち受けと一緒ですね。
さいごに
BigQueryにおけるJSON関連の扱いについて、理解は深まりましたでしょうか？
前回の記事ではGASでBigQueryを扱う際のクラス等も紹介しています。
ぜひあわせてお役立て頂けると幸いです。
