JAVA 프로젝트로 GCP BigQuery의 INSERT/UPDATE 쿼리 실행 해보기

 

JAVA 프로젝트로 GCP BigQuery의 Select 쿼리 날려보기

 

BigQuery의 데이터를 조회 해 봤으니,

 

이제는 BigQuery에 쿼리문을 통해 데이터를 INSERT/UPDATE 한다.

■ 호출부

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

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

    String bgDateTimeNow = ZonedDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "T"
            + ZonedDateTime.now().format(DateTimeFormatter.ofPattern("HH:mm:ss"));

    /*
        insert data (1건)
        api 호출 history log를 저장할때 수행 ex) request
    */
    QueryJobConfiguration insertQueryConfig =
            QueryJobConfiguration.newBuilder(
                            "INSERT INTO `" + projectId + "." + datasetName + "." + tableName + "` "
                                    + "( "
                                    + "  col01 ,  "
                                    + "  col02 ,  "
                                    + "  col03    "
                                    + ") VALUES ( "
                                    + "  @col01 , "
                                    + "  @col02 , "
                                    + "  @col03 "
                                    + ") ")
                    .addNamedParameter("col01", QueryParameterValue.string("1"))
                    .addNamedParameter("col02", QueryParameterValue.int64(11))
                    .addNamedParameter("col03", QueryParameterValue.string(bgDateTimeNow))
                    .setUseLegacySql(false)
                    .build();
    Boolean insertResult =  executeDmlQueryConfig(projectId, insertQueryConfig);
    System.out.println("Insert ==> " + insertResult);

	/*
        update data
        api 호출 history log를 저장할때 수행 ex) response
    */
    QueryJobConfiguration updateQueryConfig =
            QueryJobConfiguration.newBuilder(
                            "   UPDATE `" + projectId + "." + datasetName + "." + tableName + "` "
                                    + " SET "
                                    + "   col02 = @col02 "
                                    + " , col03 = @col03 "
                                    + " WHERE col01 = @col01 " )
                    .addNamedParameter("col01", QueryParameterValue.string("Z"))
                    .addNamedParameter("col02", QueryParameterValue.int64(707))
                    .addNamedParameter("col03", QueryParameterValue.string(bgDateTimeNow))
                    .setUseLegacySql(false)
                    .build();
    Boolean updateResult =  executeDmlQueryConfig(projectId, updateQueryConfig);
    System.out.println("Update ==> " + updateResult);

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

■ 실행부

/**
 * BigQuery의 DML 수행
 * @param projectId
 * @param queryConfig
 * @throws InterruptedException
 * @throws IOException
 */
public static Boolean executeDmlQueryConfig(String projectId, QueryJobConfiguration queryConfig)  throws InterruptedException, Exception {
    try {
        BigQuery bigQuery = getBigQuery(projectId);

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

        // Wait for the query to complete.
        try {
            queryJob = queryJob.waitFor();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        // Check for errors
        if ( queryJob == null ) {
            throw new RuntimeException("Job no longer exists");
        } else if ( queryJob.getStatus().getError() != null ) {
            // You can also look at queryJob.getStatus().getExecutionErrors() for all
            // errors, not just the latest one.
            throw new RuntimeException(queryJob.getStatus().getError().toString());
        }
        if ( queryJob.getStatus().getState().toString() == "DONE" && queryJob.getStatus().getError() == null ) {
            QueryResponse response = bigQuery.getQueryResults(jobId);
            return true;
        } else {
            return false;
        }
    } catch ( BigQueryException e ) {
        System.out.println("Insert operation not performed \\n" + e.toString());
        return false;
    }
}

결과

############### start ###############
Insert ==> true
Update ==> true
############### end ###############