[A-00215] BigQueryあれこれ(BigQueryAPIの使い方など)

BigQueryにまつわるネタを書き溜めておきます。

・Procedual Language(standardSQL)を使ってみる

とりあえずテキトーなテストデータを作成するため、ループ処理で実行する処理です。

[
  {
    "name": "record_num",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": "",
    "fields": []
  },
  {
    "name": "name",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "",
    "fields": []
  },
  {
    "name": "age",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": "",
    "fields": []
  },
  {
    "name": "address",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": "",
    "fields": []
  }
]

DECLARE x INT64 DEFAULT 0;

LOOP
     SET x = x + 1;
     IF x > 100 THEN
        LEAVE;
     END IF;

     EXECUTE IMMEDIATE
      "INSERT INTO my_dataset.test_table1 VALUES(?,?,?,?)"
      USING x, "John", 14, "JAPAN";
END LOOP;

・BigQuery APIを使ってみる

単純にselectするだけのSQLを実行する処理です。

実行前にgcloudコマンドでプロジェクト設定などをしておいてください。

package example;

import java.util.UUID;

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FieldValueList;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class BqReadWriteBucket {
    public static void main(String[] args) throws Exception {
        BigQuery bigQuery = BigQueryOptions.getDefaultInstance().getService();
        QueryJobConfiguration queryConfig =
        QueryJobConfiguration.newBuilder(
            "SELECT * FROM my_dataset_listing.test_table1 ORDER BY record_num ASC"
        ).setUseLegacySql(false)
        .build();

        JobId jobId = JobId.of(UUID.randomUUID().toString());
        Job queryJob = bigQuery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

        queryJob = queryJob.waitFor();

        if (queryJob == null) {
            throw  new RuntimeException("Job is failed.");
        } else if (queryJob.getStatus().getError() != null) {
            throw new RuntimeException("Error :" + queryJob.getStatus().getError().toString());
        }

        TableResult result = queryJob.getQueryResults();

        for (FieldValueList row : result.iterateAll()) {
            System.out.println(row.get("name").getStringValue());
        }
    }
}

・Appendix

参考文献はこちら

https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language