CODEWITHSUNDEEP
amazon-web-servicesgoogle-cloud-platformgoogle-bigquerycloud
Varun THK7
Varun THK7

Reputation: 11

Can we change location from US to other region while reading data from Bigquery using Bigquery java library?

I am trying to read data from Bigquery using Bigquery java library.

My dataset is not in US location, so when i am giving my dataset name to library , it is throwing an error that dataset not found in US location because it searches by default in US location.

I have also tried giving the location using setLocation("asia-southeast1") but still it is finding in US location.

This is my code snippet:

val bigquery: BigQuery =BigQueryOptions.newBuilder().setLocation("asia-southeast1").build().getService
val query = "SELECT TO_JSON_STRING(t, true) AS json_row FROM "+dbName+"."+tableName+" AS t"
logger.info("Query is " + query)
val queryResult: QueryJobConfiguration = QueryJobConfiguration.newBuilder(query).build
val result: TableResult = bigquery.query(queryResult)

I am writing code in SCALA. As it uses same libraries as JAVA and JAVA is more popular, thats why I am asking this for JAVA.

Please help me to know that how I can change location from US to southeast.

Can I change something inside QueryJobConfiguration as i have searched a-lot but i am unable to find anything.

My only requirement is that I want final result as TableResult.

This is the exception being thrown

com.google.cloud.bigquery.BigQueryException: Not found: Dataset XXXXXXXX was not found in location US
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:106)
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.getQueryResults(HttpBigQueryRpc.java:584)
at com.google.cloud.bigquery.BigQueryImpl$34.call(BigQueryImpl.java:1203)
at com.google.cloud.bigquery.BigQueryImpl$34.call(BigQueryImpl.java:1198)
at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:105)
at com.google.cloud.RetryHelper.run(RetryHelper.java:76)
at com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:50)
at com.google.cloud.bigquery.BigQueryImpl.getQueryResults(BigQueryImpl.java:1197)
at com.google.cloud.bigquery.BigQueryImpl.getQueryResults(BigQueryImpl.java:1181)
at com.google.cloud.bigquery.Job$1.call(Job.java:329)
at com.google.cloud.bigquery.Job$1.call(Job.java:326)
at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:105)
at com.google.cloud.RetryHelper.run(RetryHelper.java:76)
at com.google.cloud.RetryHelper.poll(RetryHelper.java:64)
at com.google.cloud.bigquery.Job.waitForQueryResults(Job.java:325)
at com.google.cloud.bigquery.Job.getQueryResults(Job.java:291)
at com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1168)
...

Thanks in advance.

Upvotes: 1

Views: 4530

Answers (1)

Graham Polley
Graham Polley

Reputation: 14781

You shouldn't actually need to specify the location because BigQuery will infer it from the dataset being referenced in your query. See here.

When loading data, querying data, or exporting data, BigQuery determines the location to run the job based on the datasets referenced in the request. For example, if a query references a table in a dataset stored in the asia-northeast1 region, the query job will run in that region.

I just tested using the Java SDK on a dataset/table I created in asia-southeast1, and it worked without needing to explicitly specify the location.

enter image description here

enter image description here

If it's still not working for you by default (check the table you're referncing actually exists), then you can specify the location by setting it in the JobId and passing that to the overloaded method:

String query = "SELECT * FROM `grey-sort-challenge.asia_southeast1.a_table`;";
        QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query)
                .setUseLegacySql(Boolean.FALSE)
                .build();

        JobId id = JobId.newBuilder().setLocation("asia-southeast1")
                .setRandomJob()
                .build();
        try {
            for (FieldValueList row : BIGQUERY.query(queryConfig, id).iterateAll()) {
                for (FieldValue val : row) {
                    System.out.printf("%s,", val.toString());
                }
                System.out.printf("\n");
            }
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

Upvotes: 2

Related Questions