BigQuery Warehouse Connector

​

Connect BigQuery

• Your GCP Project ID, which you can find in the URL of Google Cloud Console (https://console.cloud.google.com/bigquery?project=YOUR_GCP_PROJECT).
• Your unique Mixpanel service account ID, which is generated the first time you create a BigQuery connection in the Mixpanel UI (e.g. project-?????@mixpanel-warehouse-1.iam.gserviceaccount.com).
• A new, empty mixpanel dataset in your BigQuery instance (if you are using Mirror).

  CREATE SCHEMA `<gcp-project>`.`mixpanel`
    OPTIONS (
      description = 'Mixpanel connector staging dataset',
      location = '<same-as-the-tables-to-be-synced>',
    );

• roles/bigquery.jobUser - Allows Mixpanel to run BigQuery jobs to unload data.
  gcloud projects add-iam-policy-binding --member serviceAccount:<mixpanel-service-account> --role roles/bigquery.jobUser
  
• roles/bigquery.dataViewer on the datasets and/or tables to sync. Gives Mixpanel read-only access to the datasets.
  GRANT `roles/bigquery.dataViewer`
    ON SCHEMA `<gcp-project>`.`<dataset-to-be-synced>`
    TO "<mixpanel-service-account>"
  
• roles/bigquery.dataOwner on the mixpanel dataset. Gives Mixpanel read-write access to the mixpanel dataset.
  GRANT `roles/bigquery.dataOwner`
    ON SCHEMA `<gcp-project>`.`mixpanel`
    TO "<mixpanel-service-account>"
  

​

VPC Service Controls

​

Mirror Mode

• Mirror is not supported on views in BigQuery.
• If two rows in BigQuery are identical across all columns, the checksums Mirror computes for each row will be the same, and Mixpanel will consider them the same row, causing only one copy to appear in Mixpanel. We recommend ensuring that one of your columns is a unique row ID to avoid this.
• The table snapshots managed by Mixpanel are always created to expire after 21 days. This ensures that the snapshots are deleted even if Mixpanel loses access to them unexpectedly. Make sure that the sync does not go longer than 21 days without running, as each sync run needs access to the previous sync run’s snapshot (under normal conditions, Mirror maintains only one snapshot per sync and removes the older run’s snapshot as soon as it has been used by the subsequent sync run).

ALTER TABLE songs ADD COLUMN Tag STRING NULL;

ALTER TABLE <table> ADD COLUMN <column> STRING NULL;