DA and BigQuery

Brought to you by Alexey Vasiliev, Railsware

BigQuery

BigQuery Pros

Serverless, it is easy to handle large size data
Reducing architecture costs
Optimal availability
Easy integration with tools like Data Studio and Google Analytics

BigQuery Pricing

Idle BigQuery only charges you $0.02 per month per GB stored (long-term storage - $0.01 per month per GB)
Queries (on-demand) - $5 per TB (first TB is free)
Streaming inserts - $0.010 per 200 MB
BigQuery Storage Write API - $0.025 per 1 GB (first 2 TB per month are free)

BigQuery Pricing (Flat-rate pricing)

Monthly flat-rate commitments - $2000 per month for 100 slots
Annual flat-rate commitments - $1700 per month for 100 slots
Flex slots - $4 per hour for 100 slots
Also exists Omni pricing model

DA automation

resource "google_bigquery_table" "t_user_agent_info" {
  dataset_id  = google_bigquery_dataset.datasources_redshift.dataset_id
  table_id    = "t_user_agent_info"

  schema = file("datasources_redshift/t_user_agent_info.json")
}

resource "google_bigquery_table" "v_users_with_failed_transactions" {
  dataset_id  = google_bigquery_dataset.datasources_stripe.dataset_id
  table_id    = "v_users_with_failed_transactions"
  description = "Failed transactions"
  view {
    query          = file(
      "datasources_stripe/v_users_with_failed_transactions.sql"
    )
    use_legacy_sql = false
  }
}

resource "google_bigquery_table" "gs_ga_sources" {
  dataset_id  = google_bigquery_dataset.datasources_gsheets.dataset_id
  table_id    = "gs_ga_sources"
  external_data_configuration {
    autodetect    = true
    # support Bigtable, Google Sheets, Drive, Google Storage (formats: Avro, Parquet, Orc, CSV, JSON)
    source_format = "GOOGLE_SHEETS"
    google_sheets_options {
      skip_leading_rows = 1
      range             = "custom_range"
    }
    source_uris = [
      "https://docs.google.com/spreadsheets/d/example/"
    ]
  }
}

Cloud SQL/Cloud Spanner federated queries

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

DataFlow

Unified stream and batch data processing
Apache Beam SDK
Dataflow templates
Horizontal autoscaling

DataFlow at Mailtrap

Two streaming DataFlow
Apache Beam SDK Template (Pub/Sub to BigQuery)
1 GB per day from all streams
Cost:
- 2 DataFlows: $5.07 per day * 30 ~= $152 per month
- Pub/sub ~= $5 per month

Serverless

Serverless Pros

Zero server management
Reducing architecture costs
Optimal availability
Eliminates idle capacity

Serverless Cons

Response latency
Limitations
Testing and debugging become more challenging
New security concerns
Are not built for long-running processes

Serverless Use Cases

Crawlers (with puppeteer)
API data collection
Transform data each hour/day/week/month
Data monitoring and anomaly detection
Use Google Scheduler for cron
IAM and Secret Manager to manage access and secrets

Mt-go-panda
("Red Panda")

Cloud Build + Cloud Run

Costs

Redshift to S3 - free
S3 Data transfer = $0.09 per GB
GCP Storage Transfer Service = $0.0125 per GB
Summary: $0.1025 per GB transfered data, AWS charge 87% from sum :(

Small tables
(less 1 Gb)

Saving costs and optimisations

Use clustered or partitioned tables
Table expiration allow you to remove automatically old data
If you have a table that is not edited for 90 consecutive days, the price of storage for that table automatically drops by 50 percent to $0.01 per GB, per month. This is the same cost as Cloud Storage Nearline
Avoid "SELECT *" - query only the columns that you need
Sample data using preview options
Before running queries, preview them to estimate costs ("dry_run" in clients)
Limit query costs by restricting the number of bytes billed

Do not use LIMIT to control costs in non-clustered tables
Create a dashboard to view your billing data so you can make adjustments to your BigQuery usage. Also consider streaming your audit logs to BigQuery so you can analyze usage patterns
Materialize query results in stages
Using cached query results (if possible)
Do queries optimisations

BigQuery Scheduled Queries

resource "google_bigquery_data_transfer_config" "st_messages_per_day" {
  display_name   = "st_messages_per_day"
  location       = "us"
  data_source_id = "scheduled_query"
  schedule       = "every day 01:00"

  destination_dataset_id = google_bigquery_dataset.datasources_tracking.dataset_id

  params = {
    destination_table_name_template = "st_messages_per_day"
    write_disposition               = "WRITE_APPEND"
    query = file("datasources_tracking/st_messages_per_day.sql")
  }
}

GCP Logs Router

Serverless Pixel Tracking

resource "google_bigquery_routine" "pt_urldecode" {
  dataset_id   = google_bigquery_dataset.serverless_tracking_pixel.dataset_id
  routine_id   = "PT_URLDECODE"
  routine_type = "SCALAR_FUNCTION"
  description  = "Function decode encoded url data"
  language     = "SQL"
  arguments {
    name = "url"
    data_type = jsonencode({
      typeKind = "STRING"
    })
  }
  return_type = jsonencode({
    typeKind = "STRING"
  })
  definition_body = trimspace(chomp(file("${path.module}/serverless_tracking_pixel/pt_urldecode.sql")))
}

PT_URLDECODE function

(
  SELECT SAFE_CONVERT_BYTES_TO_STRING(
    ARRAY_TO_STRING(ARRAY_AGG(
        IF(STARTS_WITH(y, '%'), FROM_HEX(SUBSTR(y, 2)), CAST(y AS BYTES)) ORDER BY i
      ), b''))
  FROM UNNEST(REGEXP_EXTRACT_ALL(url, r"%[0-9a-fA-F]{2}|[^%]+")) AS y WITH OFFSET AS i
)

Example

SELECT ...
  `serverless_pixel.PT_URLDECODE`(REGEXP_EXTRACT(httpRequest.requestUrl, "ec=([^&]+)")) AS category,
  `serverless_pixel.PT_URLDECODE`(REGEXP_EXTRACT(httpRequest.requestUrl, "el=([^&]+)")) AS label,
  `serverless_pixel.PT_URLDECODE`(REGEXP_EXTRACT(httpRequest.requestUrl, "eld=([^&]+)")) AS label_data,

resource "google_bigquery_routine" "pt_decrypt" {
  dataset_id        = google_bigquery_dataset.serverless_tracking_pixel.dataset_id
  routine_id        = "PT_DESCRYPT"
  routine_type      = "SCALAR_FUNCTION"
  description       = "Function decrypt AES-256-GCM values"
  language          = "JAVASCRIPT"
  determinism_level = "DETERMINISTIC"
  arguments {
    name          = "encodedStr"
    argument_kind = "FIXED_TYPE"
    data_type = jsonencode({
      typeKind = "STRING"
    })
  }
  return_type = jsonencode({
    typeKind = "STRING"
  })
  definition_body = trimspace(chomp(templatefile("${path.module}/serverless_tracking_pixel/pt_decrypt.js.tpl", {
    secret_key = var.serverless_secret_key
  })))
  imported_libraries = [
    "gs://.../file.js"
  ]
}

if (!encodedStr) {
  return '';
}
const [version, encoded, iv, authTag] = encodedStr.split('--');
if (!version || !encoded || !iv || !authTag) {
  return '';
}
try {
  const decipher = forge.cipher.createDecipher(
    'AES-GCM',
    forge.util.createBuffer(forge.util.decode64("${secret_key}"))
  );
  decipher.start({
    iv: forge.util.createBuffer(forge.util.decode64(iv)),
    additionalData: '',
    tagLength: 128,
    tag: forge.util.createBuffer(forge.util.decode64(authTag))
  });
  decipher.update(forge.util.createBuffer(forge.util.decode64(encoded)));
  const pass = decipher.finish();

  if (pass) {
    return decipher.output.data;
  } else {
    return JSON.stringify({decrypt_error: 'invalid_data'});
  }
} catch(e) {
  return JSON.stringify({decrypt_error: JSON.stringify(e)});
}

Sensitive info in terraform

variable "serverless_secret_key" {
  type      = string
  sensitive = true
}

Pass in Github Actions as ENV variable

TF_VAR_serverless_secret_key: ${{ secrets.SERVERLESS_SECRET_KEY }}

Costs

Cloud Load Balancing - $18.33 per month
Cloud CDN (depend from region), Mailtrap: $8 per month
GCP Logs - $0.5 per GB, but internal logs is free
Summary: $27 per month for custom analytic data in BiqQuery

BQVis

Mailtrap Campaign System

Available in BigQuery

BigQuery ML supports the following types of models:
- Linear regression
- Logistic regression
- K-means
- Tensorflow
- other

Available in BigQuery

BigQuery Data Transfer Service:
- AWS S3
- Cloud Storage
- Google Ads
- Campaign Manager (formerly known as DoubleClick Campaign Manager)
- other
Migrating data from Amazon Redshift

Available in BigQuery

BigQuery BI Engine:
- BI Engine provides sub-second query response time with minimal load times and improved concurrency for data stored in BigQuery (best for visualization, used under the hood by Data Studio)
- Performs in-place analysis (no need ETL)

Conclusion

<Thank You!> Questions?

Contact information

web: leopard.in.ua
github: le0pard
twitter: @leopard_me

DA and BigQuery

BigQuery

BigQuery Pros

BigQuery Pricing

BigQuery Pricing (Flat-rate pricing)

DA automation

Cloud SQL/Cloud Spanner federated queries

DataFlow

DataFlow

DataFlow at Mailtrap

Serverless

Serverless Pros

Serverless Cons

Serverless Use Cases

Mt-go-panda ("Red Panda")

Cloud Build + Cloud Run

Costs

Small tables(less 1 Gb)

Saving costs and optimisations

BigQuery Scheduled Queries

GCP Logs Router

Serverless Pixel Tracking

PT_URLDECODE function

Example

Sensitive info in terraform

Pass in Github Actions as ENV variable

Costs

BQVis

Mailtrap Campaign System

Available in BigQuery

Available in BigQuery

Available in BigQuery

Conclusion

<Thank You!> Questions?

Contact information

Mt-go-panda
("Red Panda")

Small tables
(less 1 Gb)