GCP bigquery 연동

x·2023년 2월 7일
0

build.gradle

dependencies {
	
    // GCP
    implementation platform('com.google.cloud:libraries-bom:26.1.5')

    // Integrate with BigQuery
    implementation 'com.google.cloud:google-cloud-bigquery'
    compile group: 'org.springframework.cloud', name: 'spring-cloud-gcp-starter-bigquery', version: '1.2.5.RELEASE'
}

BigqueryTest.java

package kr.co.fitpet.mall.repository;

import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.*;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.io.*;
import java.nio.file.Path;
import java.nio.file.Paths;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class BigqueryRepositoryTest {

    String projectId = "projectId";
    String datasetName = "dataset";
    String tablePrefix = "`" + projectId + "." + datasetName + ".";

    @Test
    @DisplayName("빅쿼리 조회")
    void test_bigquery() throws IOException {
        String queryStatement = createQueryStatement();
        query(queryStatement);

        Integer num = 1;
        assertThat(num).isEqualTo(1);
    }

    String createQueryStatement() {
        return "SELECT COUNT(*)\n" +
        "FROM\n" +
        "(\n" +
        "  SELECT v_table.id as table_id\n" +
        "  FROM " + tablePrefix + "v_pet` as v_pet;\n"
        ;
    }

    public Iterable<FieldValueList> query(String query) {
        try {
            File file = new File("src/main/resources/credentials/gcp_key.json");
            String absolutePath = file.getAbsolutePath();
            Path keyPath = Paths.get(absolutePath);

            BigQuery bigquery = BigQueryOptions.newBuilder()
                .setCredentials(
                    ServiceAccountCredentials.fromStream(new FileInputStream(keyPath.toFile()))
                ).build().getService();

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

            TableResult results = bigquery.query(queryConfig);

            Iterable<FieldValueList> iterables = results.iterateAll();

            return iterables;
        } catch (BigQueryException | InterruptedException | IllegalArgumentException | IOException e) {
            System.out.println("Query not performed \n" + e.toString());
            return null;
        }
    }
}

0개의 댓글