How to import transfers from BigQuery (marketplace) to Supermetrics Hub

If you have historical data in BigQuery transfers created from the Google Marketplace version of BigQuery and are looking to start using BigQuery directly from the Supermetrics Hub, follow these steps to continue updating your historical data with the BigQuery transfer from the Supermetrics Hub.

If you have already completed the import on the Supermetrics Hub, follow these steps to move historical data and clean up your old transfer.

Instructions

Import your transfer to Supermetrics Hub

1. Go to the Supermetrics Hub.
2. Navigate to Data warehousing → Transfers.
3. You will see your BigQuery (marketplace) transfers on the list with the label DTS BigQuery. Click Import on the transfer you want to import.
   
   Alternatively, you can click the 3-dot menu on BigQuery (marketplace) transfers and select Import to Supermetrics Hub.
4. If you already have a BigQuery connection set up in the Supermetrics Hub, you can use that connection. Click Import Transfer to continue.
   
   • If you have an existing BigQuery connection on the Hub but would like to create a new one for this import, click here to create a new connection.
   • If you don't have a BigQuery connection on the Hub, you'll create one in the next step. Click Connect to BigQuery to continue.
5. Configure your transfer settings. Your account and table group selections will be automatically filled in.
   A default refresh window of 2 days will be used in your new transfer, but you can change that setting in this step. You can also add email addresses to receive alerts if something goes wrong. 
6. Click Import transfer.
7. During transfer import, a short backfill (today + yesterday) is created to check that the transfer works.

To complete the import, you need to move the historical data to Supermetrics Hub.

Move historical data and clean up

Once the transfer import is complete, you'll see the following dialog.

Follow the steps below to move your historical data and clean up your old transfer.

1. Go to Google Cloud console, and navigate to BigQuery Studio.
2. Verify that the data in the new dataset looks consistent and has the same columns as the historical data in the tables updated by the BigQuery (marketplace) transfers.
3. Copy the historical data from the BigQuery (marketplace) tables to the BigQuery (Hub) tables. The SQL script below merges the historical data from the BigQuery (marketplace) tables to the BigQuery (Hub) tables. The script removes overlapping dates while preserving the data in the partitioned table that the BigQuery (Hub) transfer updates. You'll need to repeat the script for each table in your transfer.

   INSERT INTO `bq_hub_dataset_name.bq_hub_table_name`
       SELECT * FROM `bq_marketplace_dataset_name.bq_marketplace_table_name_*`
           WHERE DATE NOT IN (
               SELECT DATE FROM `bq_hub_dataset_name.bq_hub_table_name`
           )

4. Disable your BigQuery (marketplace) transfers. In the future, when you see that the old data is migrated and the new BigQuery transfer from the Hub updates the merged dataset, you can remove all data from the old sharded tables following the instructions in this guide.
5. Point any downstream processes or visualization tools will need to the new dataset. If you are using the same dataset, make sure that you point your data visualization tool to the new tables.