JAVA 프로젝트로 GCP BigQuery의 Select 쿼리 실행 해보기

Java로 GCP BigQuery의 dataset 을 가져와 봤으니,

데이터셋 안에 생성된 테이블의 데이터를 select query로 가져와본다.

 

■ 호출부

void contextLoads() throws Exception {
    System.out.println("############### start ###############");

    String projectId = "projectId ";
    String datasetName = "datasetName ";
    String tableName = "tableName ";

    // select
    String selectQueryString = "SELECT col01, col02, col03 "
                            + " FROM `" + projectId + "." + datasetName + "." + tableName + "`"
                            + " ORDER BY col01 ASC ";
    executeSelectQueryString(projectId, selectQueryString);

    System.out.println("############### end ###############");
}

 

■ 실행부

/**
 * BigQuery의 Select 문 수행
 * @param projectId
 * @param query
 * @throws InterruptedException
 * @throws IOException
 */
public static void executeSelectQueryString(String projectId, String query) throws InterruptedException, Exception {
    try {
        BigQuery bigQuery = getBigQuery(projectId);

        QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

        // Create a job ID so that we can safely retry.
        JobId jobId = JobId.of(UUID.randomUUID().toString());
        Job queryJob = bigQuery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

        // Wait for the query to complete.
        queryJob = queryJob.waitFor();

        // Check for errors
        if (queryJob == null) {
            throw new RuntimeException("Job no longer exists");
        } else if (queryJob.getStatus().getError() != null) {
            throw new RuntimeException(queryJob.getStatus().getError().toString());
        }

        // Get the results.
        TableResult result = queryJob.getQueryResults();

        // Print all pages of the results.
        for (FieldValueList row : result.iterateAll()) {
            String col01 = row.get("col01").getStringValue();
            String col02 = row.get("col02").getStringValue();
            String col03 = row.get("col03").getStringValue();
            System.out.printf("col01: %s \\tcol02 : %s \\tcol03: %s \\n", col01, col02, col03);
        }
    } catch ( BigQueryException e ) {
        System.out.println("Insert operation not performed \\n" + e.toString());
    }
}

 

■ 결과

############### start ###############
col01: test1     col02 : 10     col03: 2022-10-31T14:29:58 
col01: test1     col02 : 20     col03: 2022-10-31T14:29:58 
col01: test1     col02 : 30     col03: 2022-10-31T14:29:58 
col01: test1     col02 : 40     col03: 2022-10-31T14:29:58 
col01: test1     col02 : 50     col03: 2022-10-31T14:29:58 
                    .....
col01: test2     col02 : 99950     col03: 2022-10-31T14:29:58 
col01: test2     col02 : 99960     col03: 2022-10-31T14:29:58 
col01: test2     col02 : 99970     col03: 2022-10-31T14:29:58 
col01: test2     col02 : 99980     col03: 2022-10-31T14:29:58 
col01: test2     col02 : 99990     col03: 2022-10-31T14:29:58 
############### end ###############

 

테이블의 데이터를 정상적으로 조회하는것을 확인했다.