Google BigQuery Optimization Strategies -

-

i querying data google analytics premium using google bigquery. @ moment, have 1 single query use calculate metrics (like total visits or conversion rate). query contains several nested join clauses , nested selects. while querying 1 table getting error:

error: resources exceeded during query execution.

using group each , join each not seem solve issue.

one solution adopted in future involves extracting relevant data needed query , exporting separate table (which queried). strategy works in principle, have working prototype it.

however, explore additional optimization strategies query work on original table.

in presentation you might paying bigquery of them suggested, namely:

  • narrowing scan (already doing it)
  • using query cache (does not apply)

the book "google bigquery analytics" mentions adjusting query features, namely:

  • group clauses generating large number of distinct groups (already did this)
  • aggregation functions requiring memory proportional number of input values (probably not apply)
  • join operations generating greater number of outputs inputs (does not seem apply)

another alternative splitting query composing sub-queries, @ moment cannot opt strategy.

what else can optimize query?

why bigquery have errors?

bigquery shared , distributed resource , such expected jobs fail @ point in time. why solution retry job exponential backoff. golden rule, jobs should retried minimum of 5 times , long job not unable complete more 15 minutes service within sla [1].

what can causes?

i can think off 2 causes can affecting queries:

  1. data skewing [2]
  2. unoptimized queries

data skewing

regarding first situation, happens when data not evenly distributed. because inner mechanic of bigquery uses version of mapreduce means if have example music or video file millions of hits, workers doing data aggregation have resources exhausted while other workers won’t doing @ because aggregations videos or musics processing have little no hits.

if case, recommendation uniformly distribute data.

unoptimized queries

if don’t have access modifying data, solution optimize queries. optimized queries follow these general rules:

  • when using select, make sure select strictly columns need diminishes cardinality of requests (avoid using select * example)
  • avoid using order clauses on large sets of data
  • avoid using group clauses create barrier parallelism
  • avoid using joins these extremely heavy on worker's memory, , may cause resource starvation , resource errors (as in not enough memory).
  • avoid using analytical functions [3]
  • if possible, queries on partitioned tables [4].

following of these strategies should queries have less errors , improve overall running time.

additional

you can't understand bigquery unless understand mapreduce first. reason recommend have on hadoop tutorials, 1 in tutorialspoint:

for similar version of bigquery, open source (and less optimized in every single way) can check apache hive [4]. if understand why apache hive fails, understand why bigquery fails.

[1] https://cloud.google.com/bigquery/sla

[2] https://www.mathsisfun.com/data/skewness.html

[3] https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#analytic-functions

[4] https://cloud.google.com/bigquery/docs/partitioned-tables

[5] https://en.wikipedia.org/wiki/apache_hive


Comments

Post a Comment

Popular posts from this blog

javascript - gulp-nodemon - nodejs restart after file change - Error: listen EADDRINUSE events.js:85 -

-
i'm running nodejs 0.12.3 - gulp 3.10.8 , gulp-nodemon 2.0.3. gulpfile.js : gulp.task('serve', ['build'], function() { gulp.start('fb-flo'); nodemon({ script: paths.server, env: { 'node_env': 'development' }, // env: { 'node_env': 'production' }, execmap: { 'js': 'node_modules/babel/bin/babel-node --stage 1' //es7 cote server }, delay: '0ms', watch: ['src/shared/', 'src/server/'], ignore: ['src/shared/components'], tasks: function (changedfiles) { var tasks = []; changedfiles.foreach(function (file) { if (path.extname(file) === '.jsx' && !~tasks.indexof('bundlejs')) tasks.push('bundlejs'); if (path.extname(file) === '.less' && !~tasks.indexof('compileless')) tasks.push('compileless'); }); return tasks; } ...
Read more

Fatal Python error: Py_Initialize: unable to load the file system codec. ImportError: No module named 'encodings' -

-
i trying make simple python program opening list of webpages user manually download reports site. don't have previous experience preparing exe files.. , i'm in learning process python coding. of done on windows 7 x64 this python code: #!c:/python34/python.exe -u splinter import * import time import os import csv #---------------------------------- raporty = [] open('../raporty.csv', newline='') csvfile: contents = csv.reader(csvfile, delimiter=' ', quotechar='|') row in contents: r = ', '.join(row) r = r.replace(',','') raporty.append(r) #--not implemented yet zmienne = [] open('../zmienne.csv', newline='') csvfile: contents = csv.reader(csvfile, delimiter=' ', quotechar='|') row in contents: r = ', '.join(row) r = r.replace(',','') zmienne.append(r) print("start") browser = browser(...
Read more

javascript - oscilloscope of speaker input stops rendering after a few seconds -

-
the following script reads audio user's microphone , renders oscilloscope on html canvas. the source taken example of mozilla developer network: visualizations web audio api and here fiddle: http://jsfiddle.net/b7j8pktp/ mozgetusermedia (note: code has no fork mechanism different browsers: works firefox) it works fine few seconds , stops rendering. whereas works totally stable: http://mdn.github.io/voice-change-o-matic/ the problem can reduced following code. microphone activation icon (next the address bar in firefox) disappears after 5 seconds: navigator.mozgetusermedia({audio: true}, function() {}, function() {} ); ( http://jsfiddle.net/b7j8pktp/2/ ) this known bug in firefox. take stream getusermedia call , hook window so: navigator.mozgetusermedia({audio: true}, function(stream) { window.stream = stream; // rest of code }, function err() { // handle error }); hopefully can fixed soon. problem we're f...
Read more