Connect Amperity Bridge to Google BigQuery

Amperity Bridge for Google BigQuery is a first-class integration enables inbound data access to Amperity from Google BigQuery without copying data or scheduling ETL workloads.

What is BigQuery Sharing?

BigQuery Sharing publishes and shares datasets between organizations. Create an exchange, and then invite organizations to publish or subscribe to data in the exchange.

Tip

Amperity Bridge shares data directly with Google BigQuery. A bridge does not require a courier or a database export to be configured. A bridge can be configured to run automatically as part of any scheduled workflow.

Data types

Most Google BigQuery data types are supported by Amperity Bridge.

Important

If an upstream data type changes, edit the bridge in Amperity, accept the changes, and then save and sync the bridge.

Note

BYTES and RANGE data types are unsupported.

The following table describes how Google BigQuery data types map to Amperity data types.

Google BigQuery data type	Amperity data type
ARRAY An ordered list of zero or more elements of non-array values. Each element must be a Google BigQuery type that maps to a supported Amperity type.	Array An ordered list of zero or more elements of non-array values by field name and value. Fields within an Array must contain values for data types supported by Amperity.
BIGINT A numeric value and an alias for INT64. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values less than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345
BIGNUMERIC A decimal value with precision of 76. Important Amperity supports precision for BIGNUMERIC data types when they are less than or equal to 38. Precision that is greater than 38 is not supported.	Decimal A fixed point number, such as for prices or message sizes. The number of characters in the decimal value is configurable. For example: 1.50 (prices) 1874.380 (message sizes) 141.48042 (order subtotals)
BOOLEAN , BOOL A value that can be TRUE, FALSE, or NULL.	Boolean A value that can be TRUE, FALSE, or NULL.
BYTES Variable-length binary data.	Warning The Google BigQuery BYTES data type is unsupported. Exclude fields with BYTES data types from tables before sharing them with Amperity.
BYTEINT A numeric value and an alias for INT64. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values less than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345
DATE A Gregorian calendar date, independent of time zone.	Date An ISO-8601 compliant date value, such as a birthdate. For example: 2021-11-04
DATETIME A Gregorian date and time, as they might be displayed on a watch, independent of time zone.	Datetime ISO-8601 compliant date and time values, such as a purchase or transaction, the time at which data was last updated, or a campaign launch date. For example: Mon Nov 30 2020 16:00:00 GMT-0800 (Pacific Standard Time) Sat Sep 02 2017 14:36:19 GMT-0700 (Pacific Daylight Time)
DECIMAL (p,s) A decimal value with a precision of 38 digits.	Decimal A fixed point number, such as for prices or message sizes. The number of characters in the decimal value is configurable. For example: 1.50 (prices) 1874.380 (message sizes) 141.48042 (order subtotals)
FLOAT64 An approximate double precision numeric value.	Float A floating point number. For example: 3.14 3.14159
GEOGRAPHY A collection of points, linestrings, and polygons that represent a set or subset of the surface of the Earth.	String A string of characters.
INT A numeric value and an alias for INT64. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values less than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345
INT64 A numeric value up to 64 bits. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values less than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345
INTEGER A numeric value and an alias for INT64. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values less than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345
JSON Represents JSON, a lightweight data-interchange format.	String A string of characters.
NUMERIC (p,s) A decimal value with a precision of up to 38 digits.	Decimal A fixed point number, such as for prices or message sizes. The number of characters in the decimal value is configurable. For example: 1.50 (prices) 1874.380 (message sizes) 141.48042 (order subtotals)
RANGE A contiguous range between two dates, datetimes, or timestamps.	Warning The Google BigQuery RANGE data type is unsupported. Exclude fields with RANGE data types from tables before sharing them with Amperity.
SMALLINT A numeric value and an alias for INT64. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values less than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345
STRING Variable-length character data.	String A sequence of characters, such as first and last names, email addresses, physical addresses, UUIDs and other IDs, phone numbers, zip codes, product names, and descriptions. May be empty. For example: John Smith John Smith johnsmith @ domain.com 123 Main Street 206-555-1111 00002ac0-0915-3cb4-b7c7-5ee192b3bd49 ACME pants “A data source that pulls from an Amazon S3 bucket.”
STRUCT A container of ordered fields. Fields within a STRUCT must contain values for data types supported by Amperity.	Struct A container of ordered fields by name and type.
TIME A time of day, as might be displayed on a clock, independent of a specific date and time zone.	String A sequence of characters that represents the time of day.
TIMESTAMP An absolute point in time, independent of any time zone or convention, such as daylight saving time (DST).	Datetime ISO-8601 compliant date and time values, such as a purchase or transaction, the time at which data was last updated, or a campaign launch date. For example: Mon Nov 30 2020 16:00:00 GMT-0800 (Pacific Standard Time) Sat Sep 02 2017 14:36:19 GMT-0700 (Pacific Daylight Time)
TINYINT A numeric value and an alias for INT64. Note BIGINT, BYTEINT, INT, INTEGER, SMALLINT, and TINYINT data types are aliases for INT64 and represent values with fewer than 64 bits.	Integer A numeric value, such as the quantity of items purchased. For example: 1 12345

From Google BigQuery

A connection from Google BigQuery to Amperity requires configuration steps to be made in both Amperity and Google BigQuery.

1. Get details

2. Configure Google BigQuery

3. Add inbound bridge

Get details

Before you can create inbound sharing between Google BigQuery and Amperity you need to collect the following information.

	The individual who configures Amperity to share data with Google BigQuery must be assigned the Datagrid Administrator policy in Amperity or have the ability to configure a bridge for Google BigQuery in a sandbox.
	A Google Cloud account with the BigQuery API enabled .
	The user who creates the data exchange and listing in Google BigQuery must have permission to access to the Google Analytics Hub API. The following permissions are required: serviceUsage.services.get serviceUsage.services.list serviceUsage.services.enable Note roles/serviceusage.serviceUsageAdmin is a predefined IAM role that includes the permissions required to access the Analytics Hub API.
	The user who creates the data exchange and listing in Google BigQuery must have permission to create listings. The following permissions are required: bigquery.datasets.get bigquery.datasets.update Note roles/bigquery.dataOwner and roles/bigquery.admin are predefined IAM roles that include the permissions required to create listings in Google BigQuery.
	The following values are required to connect Amperity to a Google BigQuery data listing: Share link (for the data exchange) This value is available after creating the data exchange and data listing in Google BigQuery. Copy this value. It is required when adding the inbound share. https://console.cloud.google.com/bigquery/ \ analytics-hub/exchanges/projects/123456789012/ \ locations/<location>/dataExchanges/<exchange>/ \ listings/<listing> Subscriber Amperity generates an account identifer to use for activating sharing in Google BigQuery. Copy this value and use it to configure permissions to the data exchange. Add a principal using this value and assign it to the Analytics Hub Subscriber role. The account identifier generated by Amperity is similar to: amperity-bridge-<bridge-id>@ \ amperity.iam.gserviceaccount.com

Configure Google BigQuery

To configure Google BigQuery to sync data with Amperity do the following:

1. Create data exchange

2. Create data listing

3. Configure subscriber and share link

Create data exchange

A data exchange in Google BigQuery is private by default. Only users and groups that have access to the data exchange can view or subscribe to data listings within the data exchange.

Create a data exchange in Google BigQuery and then allow Amperity access to that data exchange.

To create a Google BigQuery data exchange

	Open the BigQuery Console to the Sharing page. On the Sharing page click Create exchange.
	In the Exchange configuration pane, do the following: Select the project and region for the data exchange. Enter a display name. Add your Amperity account locator. Note If you are configuring a new bridge, leave the Amperity account locator field blank. This will be added after the account identifier is created.
	Click Create exchange. Important Do not configure permissions for the data exchange. The value for the Add principal field must be the value that is generated by Amperity. Click Skip.

Create data listing

A data listing in Google BigQuery is a reference to a shared dataset that exists in a data exchange. A data listing is private by default.

A data listing within a private data exchange may be shared with Amperity.

To create a Google BigQuery data listing

	On the Sharing page click the name of the exchange. On the Listings tab for the exchange click Create listing.
	From the Resource type dropdown select “BigQuery Dataset”, and then select a dataset. Note Configuring data egress controls is optional.
	Configure a series of details for the data listing. Click Next. Required. Add a display name. Optional. Configure listing details, discoverability, and email logging. Click Next Optional. Configure the contact information for primary, provider, and publisher contacts.
	Click Publish. On the Listings tab, in the row for the listing that will be shared with Amperity, from the ellipses menu select Copy share link. Amperity generates a URL similar to: https://console.cloud.google.com/bigquery/analytics-hub/exchanges/projects/123456789012/locations/<location>/dataExchanges/<exchange>/listings/<listing> Configure this identifier in Google BigQuery as the subscriber for the data listing.

Configure subscriber and share link

A subscriber is allowed to view and access a data listing . Configure Amperity as the subscriber to the Google BigQuery data listing.

A share link allows a subscriber to connect to a data listing. Copy the share link for the data listing in Google BigQuery and configure that link in Amperity.

To configure a Google BigQuery subscriber and share link

	On the Sharing page in the row for the exchange that will share data with Amperity select Set permissions from the ellipses menu. Click Add principal Paste the Amperity account identifier into the New principal field and set the role to be Analytics Hub Subscriber . Click Save This returns you to the Sharing page.
	On the Sharing page in the row for the exchange that will share data with Amperity select Copy share link from the ellipses menu. Important This value required when adding the inbound share.

Add inbound bridge

Configure an inbound bridge to connect Google BigQuery to Amperity.

To add an inbound bridge for Google BigQuery

	Open the Sources page. Under Inbound shares click Add bridge. Choose Google BigQuery. This opens the Add bridge dialog box. Add a name and description for the bridge or select an existing bridge, and then click Confirm.
	Add the share link that was copied from the Google BigQuery data exchange. Amperity generates the subscriber link after the share link has been configured. The share link is a URL similar to: https://console.cloud.google.com/bigquery/analytics-hub/exchanges/projects/123456789012/locations/<location>/dataExchanges/<exchange>/listings/<listing> Configure this identifier in Google BigQuery as the subscriber for the data listing.
	Use the Select tables dialog box to select any combination of schemas and tables to be connected to Amperity. If you select a schema, all tables in that schema will be connected. Any new tables added later need to be manually added to the connection. When finished, click Next. This opens the Domain table mapping dialog box.
	Map the tables that are connected from Google BigQuery to domain tables in Amperity. Tables that are connected with Amperity are added as domain tables. The names of connected tables must be unique among all domain tables. Primary keys are not assigned. Semantic tags are not applied. Tip Use a custom domain table to assign primary keys, apply semantic tags, and shape data within connected tables to support any of your Amperity workflows. When finished, click Save and sync. This will start a workflow that connects data from Google BigQuery to Amperity and creates the mapped domain table names. You can manually sync tables that are connected with Amperity using the Sync option from the Actions menu for the inbound bridge.

To Google BigQuery

A connection between Amperity and Google BigQuery requires configuration steps to be made in both Amperity and Google BigQuery.

Note

Configuration state for an outbound bridge is not copied from production to a sandbox. An outbound bridge must be configured within a sandbox.

1. Verify subscriber details

2. Add bridge

3. Select tables to share

4. Configure recipients

5. Subscribe to datasets

6. Verify table sharing

Verify subscriber details

Verify that IAM principals are configured in Google BigQuery.

IAM principals must be authorized to the following roles:

• Google BigQuery User

• Analytics Hub Subscription Owner

IAM principals must have the following permissions:

• View and subscribe to the data exchange

• View and subscribe to the listing

Add outbound bridge

Configure an outbound bridge to connect Amperity with Google BigQuery.

To add an outbound bridge

	Open the Destinations page. Select the Outbound shares tab, and then click Add bridge. Choose Google BigQuery. This opens the Create bridge dialog box.
	Add a name and description for the bridge or select an existing bridge. Click Next, after which you will select the tables to be shared with Google BigQuery.

Select tables to share

You can configure Amperity to share any combination of schemas and tables that are available from the Customer 360 page.

To select schemas and tables to share

	After you have configured the settings for the bridge, click Next to open the Select tables dialog box.
	You may select any combination of schemas and tables. If you select a schema, all tables in that schema will be shared, including all changes made to all tables in that schema. When finished, click Save.

Configure recipients

Authorize specific IAM principals configured in your brand’s Google Cloud environment.

Principals are authorized to subscribe to the shared schema Amperity connects to Google BigQuery. There are four types of recipients that can be configured: Service acounts, Users, Groups, and Domains.

Note

You may click Next and skip this step without authorizing principals. As long as this bridge exists in Amperity the list of configured recipients may be edited to authorize new principals or to revoke authorization for existing principals.

The next steps are done in Google BigQuery.

Subscribe to datasets

After tables are shared and recipients are configured you must finish setup in Google BigQuery. Click the Share link button, which redirects you to the listing in the Google Cloud console. All authorized principals who will access data in the share must subscribe to the dataset .

Verify table sharing

Verify that the tables shared from Amperity are available from a database in Google BigQuery.

To verify that tables were shared from Amperity to Google BigQuery

	From the Google Cloud console, open Solutions, All products, and then under Analytics click BigQuery or navigate directly to https://console.cloud.google.com/bigquery .
	Use the Explorer to browse the data collection. Find the tables shared from Amperity and verify the data within them.