JAVA 프로젝트로 GCP BigQuery의 데이터 조작하기(Insert / Update)

 

 

GCP 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 ###############