CODEWITHSUNDEEP
androidgoogle-bigqueryfirebase-analytics
Saumil Vaghela
Saumil Vaghela

Reputation: 55

BigQuery takes too long to load data from Android

BigQuery query call takes too long to load data. It takes around 7-8 seconds to get the result the same result takes 1 second on BigQuery Google Cloud Platform.

I have tried the same as per the document for google cloud BigQuery library. https://cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries

InputStream is = 
mContext.getAssets().open("service_account.json");
BigQuery bigquery = BigQueryOptions.newBuilder()
.setProjectId("uniorder-prod")
.setCredentials(ServiceAccountCredentials.fromStream(is))
.build().getService();

QueryJobConfiguration queryConfig = 
QueryJobConfiguration.newBuilder("standard sql query")
.setUseLegacySql(false)
.build();

JobId jobId = JobId.of(UUID.randomUUID().toString());
Job queryJob = bigquery
                    .create(JobInfo
                            .newBuilder(queryConfig)
                            .setJobId(jobId).build());
queryJob = queryJob.waitFor();

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

QueryResponse response = bigquery.getQueryResults(jobId);
TableResult result = queryJob.getQueryResults();

//Current query execution time is 7-8 second
//Expected query execution time is 1 or less than 1 second

//My SQL BigQuery
SELECT
  EXTRACT(DATE
  FROM
    TIMESTAMP(param2.value.string_value)) AS date,
  SUM(param3.value.double_value) AS total_price
FROM
  `uniorder-prod.analytics_200255431.events_*`,
  UNNEST(event_params) AS param1,
  UNNEST(event_params) AS param2,
  UNNEST(event_params) AS param3
WHERE
  event_name = "total_consumption_res"
  AND param1.key = "user_id"
  AND param1.value.int_value = 118
  AND param2.key = "timestamp"
  AND param3.key = "total_price"
  AND _TABLE_SUFFIX BETWEEN '20190601'
  AND '20190630'
GROUP BY
  date
ORDER BY
  date ASC

Upvotes: 0

Views: 354

Answers (1)

Pawel Czuczwara
Pawel Czuczwara

Reputation: 1530

Running a query on BigQuery

queryJob = queryJob.waitFor();

is different to pulling the results of that query back over the wire and into your application

QueryResponse response = bigquery.getQueryResults(jobId);

You're first running the query and then fetching the results.

You can reduce query time, by removing above lines.

Your network/internet speed has no bearing on the performance of your query in BigQuery. BigQuery is a multi-tenanted architecture and you share compute resources with other users. If you want low latency responses, you're using the wrong tool. I'd consider something like CloudSQL or Datastore instead.

Upvotes: 0

Related Questions